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


    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.

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!

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