By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,827 Members | 2,140 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,827 IT Pros & Developers. It's quick & easy.

Help converting .xml feed to SQL

P: 49
Hi everyone,
I am currently working on a project that pulls data from sql and displays it in an html file. This is working fine, but I am trying to make it a little more fancy using this cool source code found here:

http://mattberseth.com/blog/2007/12/...gest_styl.html

In the source code, it is pulling dummy data from an .xml file that stores all of the data. I would like to change the source of the data to a table in my sql 2005 (my real data). Can someone help me make the necessary changes to pull my data from SQL? Sorry for my beginner questions. All help is appreciated!

Here is my C# code that references the .xml file;


Expand|Select|Wrap|Line Numbers
  1. using System;
  2. using System.ComponentModel;
  3. using System.Configuration;
  4. using System.Data;
  5. using System.IO;
  6. using System.Web;
  7.  
  8. /// <summary>
  9. /// Summary description for CustomerDataObject
  10. /// </summary>
  11. [DataObject(true)]
  12. public class CustomersDataObject
  13. {
  14.     /// <summary>
  15.     /// 
  16.     /// </summary>
  17.     private DataSet _customers;
  18.  
  19.     /// <summary>
  20.     /// 
  21.     /// </summary>
  22.     public CustomersDataObject()
  23.     {
  24.         this._customers = HttpContext.Current.Session["Customers"] as DataSet;
  25.  
  26.         if (this._customers == null)
  27.         {
  28.             this._customers = new DataSet();
  29.             this._customers.ReadXml(HttpContext.Current.Server.MapPath(@"App_Data\customers.xml"));
  30.  
  31.             HttpContext.Current.Session["Customers"] = this._customers;
  32.         }
  33.     }
  34.  
  35.     /// <summary>
  36.     /// 
  37.     /// </summary>
  38.     public DataTable CustomerTable
  39.     {
  40.         get { return this._customers.Tables["customers"]; }
  41.     }
  42.  
  43.     /// <summary>
  44.     /// 
  45.     /// </summary>
  46.     /// <returns></returns>
  47.     public DataView Select(string propertyName, string propertyValue)
  48.     {
  49.         EnumerableRowCollection<DataRow> query = null;
  50.         if (string.IsNullOrEmpty(propertyName) || string.IsNullOrEmpty(propertyValue))
  51.         {
  52.             query =
  53.                 from customer in this.CustomerTable.AsEnumerable()
  54.                 select customer;
  55.         }
  56.         else
  57.         {
  58.             query =
  59.                 from customer in this.CustomerTable.AsEnumerable()
  60.                 where customer.Field<string>(propertyName).Equals(propertyValue, StringComparison.CurrentCultureIgnoreCase)
  61.                 select customer;
  62.         }
  63.  
  64.         return query.AsDataView();
  65.     }
  66.  
  67.     /// <summary>
  68.     /// 
  69.     /// </summary>
  70.     /// <param name="columnName"></param>
  71.     /// <param name="count"></param>
  72.     /// <param name="prefix"></param>
  73.     /// <returns></returns>
  74.     public string[] GetCompletionList(string columnName, string prefix, int count)
  75.     {
  76.         //  find all of the rows that have values that start with
  77.         //  the provided prefix
  78.         EnumerableRowCollection<DataRow> query =
  79.             from customer in this.CustomerTable.AsEnumerable()
  80.             where customer.Field<string>(columnName).ToLower().StartsWith(prefix.ToLower())
  81.             select customer;
  82.  
  83.         DataView view = query.AsDataView();
  84.  
  85.         //  only return distinct values
  86.         System.Collections.Generic.List<string> items = new System.Collections.Generic.List<string>();
  87.         #region Distinct
  88.         for (int i = 0; i < count && i < view.Count; i++)
  89.         {
  90.             string value = view[i][columnName].ToString();
  91.             if (!items.Contains(value))
  92.             {
  93.                 items.Add(value);
  94.             }
  95.         }
  96.         #endregion
  97.  
  98.         //  return the items
  99.         return items.ToArray(); 
  100.     }
  101. }
Aug 28 '09 #1
Share this Question
Share on Google+
1 Reply


ck9663
Expert 2.5K+
P: 2,878
Actually it's more of a C# question than a SQL Server question. Find that portion that you connect your code to an xml file and try replacing that with a DSN-Less connection to a SQL server. I would think the SELECT statement would be the same.

Good luck!!!

--- CK
Aug 29 '09 #2

Post your reply

Sign in to post your reply or Sign up for a free account.