« Commerce Server 2002 and XP | Main | On the Certification Road »

December 19, 2005

From DataReader to DataSet

Often you need to transform your DataReader into a DataSet. If that sounds weird or why should I just use SqlDataAdapter, the main problem is that I'm using strongly typed DataSet. SqlDataAdapter doesn't like the strongly typed data set. If your stored procedure is returning multiple tables, it will force it to be stored in Table1, Table2 ...

So a quick play with SqlDataReader gives me what I wanted. Here is the code snippet: 
public void ExecuteQuery(DataTable[] dataTables)
{
    try
    {
        _cmd.Connection.Open();
        SqlDataReader sdr = _cmd.ExecuteReader();

        int tableNum = 0;
        while (sdr.Read())
        {
            DataRow dr = dataTables[tableNum].NewRow();
            for (int i = 0; i < sdr.FieldCount; i++)
            {
                string name = sdr.GetName(i);
                dr[name] = sdr.GetValue(i);
            }
            dataTables[tableNum].Rows.Add(dr);            
        }
        while (sdr.NextResult())
        {
            tableNum++;
            while (sdr.Read())
            {
                DataRow dr = dataTables[tableNum].NewRow();
                for (int i = 0; i < sdr.FieldCount; i++)
                {
                    string name = sdr.GetName(i);
                    dr[name] = sdr.GetValue(i);
                }
                dataTables[tableNum].Rows.Add(dr);    
            }
        }
    }
    finally
    {
        _cmd.Connection.Close();
    }
}


To the method you simply pass the order of the DataTable with the order of the SELECT statement in your stored procedure.

Posted by vhadiant at December 19, 2005 06:16 PM





Trackback Pings

TrackBack URL for this entry:
http://www.hadianto.net/mov32/mt-tb.cgi/141