Help converting .xml feed to SQL

[jrod11]

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/creating_a_google_suggest_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;

using System;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.IO;
using System.Web;

/// <summary>
/// Summary description for CustomerDataObject
/// </summary>
[DataObject(true)]
public class CustomersDataObject
{
    /// <summary>
    /// 
    /// </summary>
    private DataSet _customers;

    /// <summary>
    /// 
    /// </summary>
    public CustomersDataObject()
	{
        this._customers = HttpContext.Current.Session["Customers"] as DataSet;

        if (this._customers == null)
        {
            this._customers = new DataSet();
            this._customers.ReadXml(HttpContext.Current.Server.MapPath(@"App_Data\customers.xml"));

            HttpContext.Current.Session["Customers"] = this._customers;
        }
	}

    /// <summary>
    /// 
    /// </summary>
    public DataTable CustomerTable
    {
        get { return this._customers.Tables["customers"]; }
    }

    /// <summary>
    /// 
    /// </summary>
    /// <returns></returns>
    public DataView Select(string propertyName, string propertyValue)
    {
        EnumerableRowCollection<DataRow> query = null;
        if (string.IsNullOrEmpty(propertyName) || string.IsNullOrEmpty(propertyValue))
        {
            query =
                from customer in this.CustomerTable.AsEnumerable()
                select customer;
        }
        else
        {
            query =
                from customer in this.CustomerTable.AsEnumerable()
                where customer.Field<string>(propertyName).Equals(propertyValue, StringComparison.CurrentCultureIgnoreCase)
                select customer;
        }

        return query.AsDataView();
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="columnName"></param>
    /// <param name="count"></param>
    /// <param name="prefix"></param>
    /// <returns></returns>
    public string[] GetCompletionList(string columnName, string prefix, int count)
    {
        //  find all of the rows that have values that start with
        //  the provided prefix
        EnumerableRowCollection<DataRow> query =
            from customer in this.CustomerTable.AsEnumerable()
            where customer.Field<string>(columnName).ToLower().StartsWith(prefix.ToLower())
            select customer;

        DataView view = query.AsDataView();

        //  only return distinct values
        System.Collections.Generic.List<string> items = new System.Collections.Generic.List<string>();
        #region Distinct
        for (int i = 0; i < count && i < view.Count; i++)
        {
            string value = view[i][columnName].ToString();
            if (!items.Contains(value))
            {
                items.Add(value);
            }
        }
        #endregion

        //  return the items
        return items.ToArray(); 
    }
}

[ck9663]

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