Convert a DataReader to DataTable in ASP.NET

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
     }
 }