Bind Gridview/DataGrid with DataReader in ASP.net using C# or VB.net :

Dec 28, 2011 By Anil Gaikwad

Binding a large amount of data to a datagrid will take more time with Dataset or DataTable, to avoid the time delay of binding large amount of data we can use DataReader instead of DataSet or DataTable.By Default , Datareader is fast compared to DataSet or DataTable. As it uses forward read-only method to fetch data from Database so it is faster as compared to dataset and datatable.

To Bind Grid with DataReader you need to follow following steps:


  • Create a Sqlcommand object and pass name of the stored procedure to it.
Dim oCommand As SqlCommand oCommand.CommandText = "Name_of_stored_Procedure"
  • Add input output parameters with value to the sqlcommand depending on the stored procedure paranmeters
oCommand.Parameters.Add(New SqlParameter("@TestParam", Data.SqlDbType.VarChar,10)).Value = "value"
  • Then you can execute that Procedure using Executereader() and Bind datareader to Gridview/Datagrid.
SqlDataReader reader = oCommand.ExecuteReader(); GridView1.DataSource = reader; GridView1.DataBind();

Complete Code


C#.net Example:

SqlConnection oConnection = new SqlConnection("Connection_String"); oConnection.Open(); SqlCommand oCommand = new SqlCommand(); oCommand.Connection = oConnection; oCommand.CommandText = "Name_of_stored_Procedure" oCommand.CommandTimeout = "Command_Timeout"; oCommand.CommandType = CommandType.StoredProcedure; oCommand.Parameters.Add(New SqlParameter("@TestParam", Data.SqlDbType.VarChar, 10)).Value = "value"; SqlDataReader reader = oCommand.ExecuteReader(); oConnection.Close(); GridView1.DataSource = reader; GridView1.DataBind(); OR SqlConnection oConnection = new SqlConnection("Connection_String"); oConnection.Open(); SqlCommand oCommand = new SqlCommand("select * from customer",oConnection); SqlDataReader reader = oCommand.ExecuteReader(); oConnection.Close(); GridView1.DataSource = reader; GridView1.DataBind();

VB.net Example:

Dim oConnection As SqlConnection = New SqlConnection("Connection_String") oConnection.Open() Dim oCommand As SqlCommand = New SqlCommand() oCommand.Connection = oConnection oCommand.CommandText = "Name_of_stored_Procedure" oCommand.CommandTimeout = "Command_Timeout" oCommand.CommandType = CommandType.StoredProcedure oCommand.Parameters.Add(New SqlParameter("@TestParam", Data.SqlDbType.VarChar, 10)).Value = "value" SqlDataReader reader = oCommand.ExecuteReader(); oConnection.Close() GridView1.DataSource = reader GridView1.DataBind() OR Dim oConnection As SqlConnection = New SqlConnection("Connection_String") oConnection.Open() Dim oCommand As SqlCommand = New SqlCommand("select * from customer",oConnection) SqlDataReader reader = oCommand.ExecuteReader(); oConnection.Close() GridView1.DataSource = reader GridView1.DataBind()