Convert a DataReader to DataTable in ASP.NET

Posted by: Suprotim Agarwal , on 4/18/2008, in Category ASP.NET
Views: 328625
Abstract: In this article, we will explore how to convert a datareader to a DataTable using two approaches. We will first see how to do the conversion using the Load() method of the DataTable. Alternatively, we will also explore converting the DataReader to DataTable manually.
Convert a DataReader to DataTable in ASP.NET
 
A DataReader is a read-only forward-only way of reading data. It is quiet fast when compared to fetching data using a DataSet. Infact internally, a DataSet uses a DataReader to populate itself. However at times, we need the best of both worlds. A dataset/datatable is extremely handy when it comes to binding it to a control like a GridView. So to make use of both the DataReader and DataTable in the same solution, we can fetch the data using a DataReader and then convert it to a DataTable and bind it to the control. In this article, we will explore how to do the conversion using two approaches; the first one, a direct method by using the DataTable.Load() and the second one, by manually converting a DataReader to a DataTable.
Step 1: Create a new ASP.NET application. Drag and drop two GridView controls to the page. We will fetch data from a DataReader into a DataTable and bind the DataTable to the GridView’s. Before moving ahead, add a web.config file to the project and add the following element.
      <connectionStrings>
            <addname="NorthwindConn"connectionString="Data Source=(local); Initial Catalog=Northwind; Integrated Security=true;"/>
      </connectionStrings>
Step 2: Let us first see how to convert a DataReader to a DataTable using the easy way out. DataTable in ADO.NET 2.0 contains a Load() method which enables the DataTable to be filled using a IDataReader. This method is quiet handy when you need to quickly create a DataTable, without using a DataAdapter!! Let us see how.
C#
   private void ConvertDateReadertoTableUsingLoad()
    {
        SqlConnection conn = null;
        try
        {
            string connString = ConfigurationManager.ConnectionStrings["NorthwindConn"].ConnectionString;
            conn = new SqlConnection(connString);
            string query = "SELECT * FROM Customers";
            SqlCommand cmd = new SqlCommand(query, conn);
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            DataTable dt = new DataTable();
            dt.Load(dr);
            GridView1.DataSource = dt;
            GridView1.DataBind();
           
        }
        catch (SqlException ex)
        {
            // handle error
        }
        catch (Exception ex)
        {
            // handle error
        }
        finally
        {
            conn.Close();
        }
    }
VB.NET
   Private Sub ConvertDateReadertoTableUsingLoad()
            Dim conn As SqlConnection = Nothing
            Try
                  Dim connString As String = ConfigurationManager.ConnectionStrings("NorthwindConn").ConnectionString
                  conn = New SqlConnection(connString)
                  Dim query As String = "SELECT * FROM Customers"
                  Dim cmd As SqlCommand = New SqlCommand(query, conn)
                  conn.Open()
                  Dim dr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
                  Dim dt As DataTable = New DataTable()
                  dt.Load(dr)
                  GridView1.DataSource = dt
                  GridView1.DataBind()
 
            Catch ex As SqlException
                  ' handle error
            Catch ex As Exception
                  ' handle error
            Finally
                  conn.Close()
            End Try
   End Sub
Note 1: If there is some existing data in the DataTable, the data coming from the DataReader is merged with the existing rows.
Note 2: If you need a DataReader back from a DataTable, use the DataTable.CreateDataReader() method.
Step 3: The method shown in the Step 2 was the easy way out. However, if for some reason(if you are not using ADO.NET 2.0), you would want to convert a DataReader to a DataTable ‘manually’, here’s the code. In the code below, a DataTable schema is created first using the GetSchemaTable() method of DataReader. The GetSchemaTable() returns a DataTable describing the column metadata of the IDataReader. Once done, we loop through the rows of the schema table and create a DataColumn object and set its properties. This DataColumn is also added to the List<> collection. We then read rows from the DataReader and populate the DataTable.
C#
   private void ConvertDataReaderToTableManually()
    {
        SqlConnection conn = null;
        try
        {
            string connString = ConfigurationManager.ConnectionStrings["NorthwindConn"].ConnectionString;
            conn = new SqlConnection(connString);
            string query = "SELECT * FROM Customers";
            SqlCommand cmd = new SqlCommand(query, conn);
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            DataTable dtSchema = dr.GetSchemaTable();
            DataTable dt = new DataTable();
            // You can also use an ArrayList instead of List<>
            List<DataColumn> listCols = new List<DataColumn>();
           
            if (dtSchema != null)
            {
                foreach (DataRow drow in dtSchema.Rows)
                {
                    string columnName = System.Convert.ToString(drow["ColumnName"]);
                    DataColumn column = new DataColumn(columnName, (Type)(drow["DataType"]));
                    column.Unique = (bool)drow["IsUnique"];
                    column.AllowDBNull = (bool)drow["AllowDBNull"];
                    column.AutoIncrement = (bool)drow["IsAutoIncrement"];
                    listCols.Add(column);
                    dt.Columns.Add(column);
                }
            }
 
            // Read rows from DataReader and populate the DataTable
            while (dr.Read())
            {
                DataRow dataRow = dt.NewRow();
                for (int i = 0; i < listCols.Count; i++)
                {
                    dataRow[((DataColumn)listCols[i])] = dr[i];
                }
                dt.Rows.Add(dataRow);
            }
            GridView2.DataSource = dt;
            GridView2.DataBind();
        }
        catch (SqlException ex)
        {
            // handle error
        }
        catch (Exception ex)
        {
            // handle error
        }
        finally
        {
            conn.Close();
        }
 
    }
VB.NET
 Private Sub ConvertDataReaderToTableManually()
            Dim conn As SqlConnection = Nothing
            Try
                  Dim connString As String = ConfigurationManager.ConnectionStrings("NorthwindConn").ConnectionString
                  conn = New SqlConnection(connString)
                  Dim query As String = "SELECT * FROM Customers"
                  Dim cmd As SqlCommand = New SqlCommand(query, conn)
                  conn.Open()
                  Dim dr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
                  Dim dtSchema As DataTable = dr.GetSchemaTable()
                  Dim dt As DataTable = New DataTable()
                  ' You can also use an ArrayList instead of List<>
                  Dim listCols As List(Of DataColumn) = New List(Of DataColumn)()
 
                  If Not dtSchema Is Nothing Then
                        For Each drow As DataRow In dtSchema.Rows
                              Dim columnName As String = System.Convert.ToString(drow("ColumnName"))
                              Dim column As DataColumn = New DataColumn(columnName, CType(drow("DataType"), Type))
                              column.Unique = CBool(drow("IsUnique"))
                              column.AllowDBNull = CBool(drow("AllowDBNull"))
                              column.AutoIncrement = CBool(drow("IsAutoIncrement"))
                              listCols.Add(column)
                              dt.Columns.Add(column)
                        Next drow
                  End If
 
                  ' Read rows from DataReader and populate the DataTable
                  Do While dr.Read()
                        Dim dataRow As DataRow = dt.NewRow()
                        For i As Integer = 0 To listCols.Count - 1
                              dataRow((CType(listCols(i), DataColumn))) = dr(i)
                        Next i
                        dt.Rows.Add(dataRow)
                  Loop
                  GridView2.DataSource = dt
                  GridView2.DataBind()
            Catch ex As SqlException
                  ' handle error
            Catch ex As Exception
                  ' handle error
            Finally
                  conn.Close()
            End Try
 
 
Step 4: Call the two methods on the PageLoad()
C#
    protected void Page_Load(object sender, EventArgs e)
    {
        ConvertDateReadertoTableUsingLoad();
        ConvertDataReaderToTableManually();
    }
VB.NET
      Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
            ConvertDateReadertoTableUsingLoad()
            ConvertDataReaderToTableManually()
      End Sub
The DataTable.Load(IDataReader) is extremely handy when you want to quickly bind the data coming from a DataReader to a control like the GridView. The DataTable.Load() method has three overloads. We have explored one of them. I would encourage you to explore the other two over here.
I hope this article was useful and I thank you for viewing it.
If you liked the article,  Subscribe to my RSS Feed.
Give a +1 to this article if you think it was well written. Thanks!
Recommended Articles
Suprotim Agarwal, ASP.NET Architecture MVP, MCSD, MCAD, MCDBA, MCSE, is the CEO of A2Z Knowledge Visuals Pvt. He primarily works as an Architect Consultant and provides consultancy on how to design and develop .NET centric database solutions.

Suprotim is the founder and primary contributor to DotNetCurry, DNC .NET Magazine, SQLServerCurry and DevCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls. and is authoring another one at The Absolutely Awesome jQuery CookBook.

Follow him on twitter @suprotimagarwal


Page copy protected against web site content infringement by Copyscape


User Feedback
Comment posted by DotNetYuppie on Sunday, April 20, 2008 8:02 PM
You mentioned that the datareader is faster -- is this still the case if you're using the datatable's Load() method?  What performance advantage do you achieve if you do a datareader that loads into a datatable over simply loading with a datatable?
Comment posted by yacoobreddyg on Tuesday, April 22, 2008 9:12 AM
very nice coding
Comment posted by zz on Wednesday, April 23, 2008 6:27 AM
ddd
Comment posted by Keith Patrick on Thursday, May 8, 2008 4:18 PM
DotNetYuppie: DT.Load can result in OutOfMemoryExceptions if you are loading a large data from the reader and need to chunk the row processing. The Load overload that lets you supply an error handler can account for the chunking in some cases, but not all.
Comment posted by manish on Saturday, May 17, 2008 3:59 AM
very helpful
Comment posted by shay Jacoby on Thursday, May 29, 2008 5:39 PM
here are 2 useful extention methods to convert dr to datatable or to dataset:

// <copyright file="IDataReaderExtensions.cs" company="s-online">
//     Copyright (c) Shay Jacoby. All rights reserved.
// </copyright>

namespace Extensions
{
    #region usings
    using System.Data;
    #endregion

    public static class IDataReaderExtensions
    {


        /// <summary>
        /// DataReader To DataTable
        /// </summary>
        /// <param name="reader">input IDataReader</param>
        /// <returns></returns>
        public static DataTable ToDataTable(this IDataReader reader)
        {
            var table = new DataTable();
            var fieldCount = reader.FieldCount;
            for (var i = 0; i < fieldCount; i++)
                table.Columns.Add(reader.GetName(i), reader.GetFieldType(i));

            table.BeginLoadData();
            var values = new object[fieldCount];
            while (reader.Read())
            {
                reader.GetValues(values);
                table.LoadDataRow(values, true);
            }
            table.EndLoadData();

            return table;
        }


        /// <summary>
        /// DataReader To DataSet
        /// </summary>
        /// <param name="reader">input IDataReader</param>
        /// <returns></returns>
        public static DataSet ToDataSet(this IDataReader reader)
        {
            var table = new DataTable();
            var fieldCount = reader.FieldCount;
            for (var i = 0; i < fieldCount; i++)
                table.Columns.Add(reader.GetName(i), reader.GetFieldType(i));

            table.BeginLoadData();
            var values = new object[fieldCount];
            while (reader.Read())
            {
                reader.GetValues(values);
                table.LoadDataRow(values, true);
            }
            table.EndLoadData();


            var ds = new DataSet();
            ds.Tables.Add(table);
            return ds;
        }



    }
}
Comment posted by bhupesh on Thursday, June 26, 2008 7:00 AM
   convert dr to datatable or dataset.
Comment posted by Maulik on Wednesday, July 9, 2008 9:12 AM
Peter Bomberg has posted a neat solution without involving any loooping code http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=628
Comment posted by Suprotim Agarwal on Wednesday, July 9, 2008 11:05 PM
Maulik: Thanks for that link. I will check it out.
Comment posted by santosh kumar on Thursday, August 21, 2008 2:53 AM
this is great work done,

but which one is load data faster Step2 or Step3.

santosh kumar
http://www.operativesystems.com
Comment posted by Suprotim Agarwal on Thursday, August 21, 2008 9:22 PM
Santosh: A quick way to check that is to note the time before the operation and just after the function ends. Then take the time difference :)
Comment posted by mouhammed on Monday, December 15, 2008 3:45 AM
thanks
Comment posted by Arifur Rahman on Friday, February 27, 2009 7:26 PM
This post is really helpful. It help me very much. Thanks to the author for his nice article.
Comment posted by ppo on Wednesday, March 18, 2009 10:51 PM
Thanks u so much for your post
Comment posted by lemiffe on Monday, March 1, 2010 7:12 AM
Great article, thanks!
Comment posted by ♥ Ananda ♥ on Sunday, April 25, 2010 8:23 AM
this examples were really superb and very helpful.... i really wanted this type of examples...! thanks a lot.....!! keep it up :)
cheers!! :) :=)
Comment posted by Rajvir Singh Thakur on Friday, June 25, 2010 1:18 PM
Thanks dear for this code.its very helpful.
Comment posted by john on Tuesday, October 19, 2010 9:43 PM
I find that this is the best way to load it http://www.itjungles.com/dotnet/function-to-load-datatable-from-sqldatareader.
Comment posted by Gaurav Balyan on Thursday, January 5, 2012 3:20 AM
Thanks For the Valuable Tutorial
Comment posted by Benjamin on Sunday, January 22, 2012 2:10 PM
A greate job Suprotim! I am planning to use this code however my case is slightly different. The dr returned from db consists of 6 rows where each row has to look differently: first row as integers(e,g 11,593), second row money($364,584.67), third again integer and the rest of rows money.
Can you suggest how to achive it? Thank you.
Comment posted by Dave on Friday, May 18, 2012 4:44 AM
When I databind the gridview I get the following error "Type 'System.Web.UI.WebControls.ListItem' in Assembly 'System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' is not marked as serializable." any ideas?
Comment posted by Dave on Friday, May 18, 2012 4:39 PM
When I databind the gridview I get the following error "Type 'System.Web.UI.WebControls.ListItem' in Assembly 'System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' is not marked as serializable." any ideas?
Comment posted by Santoshi on Friday, September 28, 2012 6:22 AM
helpful
Comment posted by asaaaaasa on Friday, November 2, 2012 7:07 AM
asasaaaa
Comment posted by Ashish on Tuesday, January 8, 2013 6:55 AM
try this
http://aashishdynamic.wordpress.com/2011/08/03/filter-datatable-in-asp-net-datatable-select/
Comment posted by santhosh on Thursday, January 31, 2013 3:59 AM
thank u.....
Comment posted by David on Friday, July 19, 2013 8:00 PM
Good piece, saved me a bit of time
Comment posted by Thank you so much on Friday, January 31, 2014 1:12 PM
Thanks a lot for this... just what I spent a day looking for.

Post your comment
Name:  
E-mail: (Will not be displayed)
Comment:
Insert Cancel