Beginning .Net : Fill DataTable from a DataReader with C# Examples and VB.Net Examples

You can also fill DataTable with DataReader with the help of "Load" method of DataTable.
This is very useful for .Net Beginners.
We can do this using ExecuteReader method of SqlCommand class.

Here is example of this.
In this example we get  all product_master table records and fill in data table object.
Here we do not close connection manually because we specify "CommandBehavior.CloseConnection" this behavior. This will automatically close connection after executing ExecuteReader command.

ASPX Code :

 <asp:GridView ID="gvProducts" runat="server">
 </asp:GridView>

C# Examples :

        //Create Data Table object, That hold records
        DataTable objTable = new DataTable();

        SqlConnection objConn = new SqlConnection();
        objConn.ConnectionString = @"Data Source=.\SQLEXPRESS;" +
                                   "Initial Catalog=TempDatabase;" +
                                   "User ID=sa;Password=sa;";  

        SqlCommand objcmd = new SqlCommand();
        objcmd.CommandText = "SELECT  * FROM product_master";
        objcmd.CommandType = CommandType.Text;
        objcmd.Connection = objConn;
        objcmd.Connection.Open();

        //Get sql data reader object
        SqlDataReader objReader= objcmd.ExecuteReader(CommandBehavior.CloseConnection);

        //Load SqlDataReder object into DataTable object
        objTable.Load(objReader);
                
        gvProducts.DataSource = objTable.DefaultView;
        gvProducts.DataBind();

        
        objcmd.Dispose();
        objConn.Dispose();

VB.net Example :

       'Create Data Table object, That hold records
        Dim objTable As New DataTable()

        Dim objConn As New SqlConnection()
        objConn.ConnectionString = "Data Source=.\SQLEXPRESS;" & _
                                   "Initial Catalog=TempDatabase;" & _
                                   "User ID=sa;Password=sa;"

        Dim objcmd As New SqlCommand()
        objcmd.CommandText = "SELECT  * FROM product_master"
        objcmd.CommandType = CommandType.Text
        objcmd.Connection = objConn
        objcmd.Connection.Open()

        'Get sql data reader object
        Dim objReader As SqlDataReader = objcmd.ExecuteReader(CommandBehavior.CloseConnection)

        'Load SqlDataReder object into DataTable object
        objTable.Load(objReader)

        gvProducts.DataSource = objTable.DefaultView
        gvProducts.DataBind()


        objcmd.Dispose()
        objConn.Dispose()

You can also get a DataTableReader from DataTable object, You can do this with the help of CreateDataReader method of Data Table class.

Output :