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/...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

 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

Help converting .xml feed to SQL

re: Help converting .xml feed to SQL

Similar Microsoft SQL Server bytes

/bytes/development

/bytes/it

/bytes/about