Convert a DataReader to DataTable in ASP.NET

Posted by: Suprotim Agarwal , on 4/18/2008, in Category ASP.NET
Views: 553251
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.

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


    End Sub

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.

This article has been editorially reviewed by Suprotim Agarwal.

Absolutely Awesome Book on C# and .NET

C# and .NET have been around for a very long time, but their constant growth means there’s always more to learn.

We at DotNetCurry are very excited to announce The Absolutely Awesome Book on C# and .NET. This is a 500 pages concise technical eBook available in PDF, ePub (iPad), and Mobi (Kindle).

Organized around concepts, this Book aims to provide a concise, yet solid foundation in C# and .NET, covering C# 6.0, C# 7.0 and .NET Core, with chapters on the latest .NET Core 3.0, .NET Standard and C# 8.0 (final release) too. Use these concepts to deepen your existing knowledge of C# and .NET, to have a solid grasp of the latest in C# and .NET OR to crack your next .NET Interview.

Click here to Explore the Table of Contents or Download Sample Chapters!

What Others Are Reading!
Was this article worth reading? Share it with fellow developers too. Thanks!
Share on LinkedIn
Share on Google+

Author
Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigious Microsoft MVP award for Sixteen consecutive years. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that offers Digital Marketing and Branding services to businesses, both in a start-up and enterprise environment.

Get in touch with him on Twitter @suprotimagarwal or at LinkedIn



Page copy protected against web site content infringement 	by Copyscape




Feedback - Leave us some adulation, criticism and everything in between!
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.
Comment posted by snjkncs on Tuesday, May 5, 2015 5:01 AM
ncsnc