469,647 Members | 1,682 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,647 developers. It's quick & easy.

Help converting .xml feed to SQL

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
1 2218
ck9663
2,878 Expert 2GB
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.

Similar topics

4 posts views Thread by Kevin T. Ryan | last post: by
17 posts views Thread by mickjames | last post: by
4 posts views Thread by Chris | last post: by
reply views Thread by Jon | last post: by
1 post views Thread by shotokan99 | last post: by
2 posts views Thread by =?Utf-8?B?Vmlua2k=?= | last post: by
9 posts views Thread by bhass | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.