Home  »  Code Snippets  »  ASP.Net  »

Convert a DataReader to DataTable in ASP.NET

 Posted By : Manoranjan SahooPosted Date : 23/06/2011 12:32:12 PM Category : ASP.Net Points : 5

When working with Datareaders one of the problems i sometimes come across is binding these to gridview or any 3rd party controls. Some controls will not bind to a DataReader but nearly all controls bind to a DataTable. A DataReader is a read-only or forward-only way of reading data. It is quiet fast when compared to fetching data using a DataSet. A dataset/datatable is extremely handy when it comes to binding it to a control like a GridView or any 3rd party controls. 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 convert datareader to datatable by using the DataTable.Load() method.

Step 1: Create a new ASP.NET application. add one GridView controls to the page. Now, We will fetch data from database using a DataReader and then convert it into a DataTable and then bind the DataTable to the GridView.

Step 2: Now we are going to write the below code to fetch the data from database and then convert it to DataTable, so that we can bind it easily to the GridView.

Code:


void ConvertDateReaderToDataTable()
{
    SqlConnection conn;
    DataTable dt = new DataTable();
    try
    {
        conn = new SqlConnection("connString");//here put your connection string
        string sqlquery = "SELECT * FROM ProductTable";
        SqlCommand cmd = new SqlCommand(sqlquery, conn);
        if (conn.State == ConnectionState.Closed) { conn.Open(); }
        SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        dt.Load(dr);//this method will load the dr to the datatable.
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }
    catch (Exception ex)
    {
        // handle error
    }
    finally
    {
        conn.Close();//finally close the connection
    }
}


In the above code we used inbuilt Load() method of DataTable. But we can load manually using loop.

Download Source Code in text file from Below link :

http://uploading.com/files/9df1532c/ConvertDataReaderToDataTable.zip/

Responses
No responses found for this thread. Be the first to respond this thread.
Post Reply
Login to post Response.