In my previous tutorials, I’d explained difference between executereader executenonquery and executescalar, export gridview data to word excel text pdf, gridview inline insert update delete and other more cracking tutorials on Gridivew, Asp.net, JavaScript, jQuery here.
Now here in this tutorial, I’ll explain how you can get data from database and then bind gridview using ado.net executereader with example in asp.net c# as well as vb.net.
To explain further, we need to create database table to read data and bind retrieved resultset to gridview, so simply execute following script to sql query editor to create database table and then add few records manually or download complete example code with script at the end of the page.
Here is the script to create “SubjectDetails” table:
(
[SubjectId] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[SubjectName] [nvarchar](100) NULL
)
Bind GridView using ExecuteReader in Asp.net
Following is the complete HTML Markup code for your .aspx page:
<head id=”Head1″ runat=”server”>
<title>Webblgosforyou.com | Bind asp.net gridview with executereader and add paging
example</title>
</head>
<body>
<form id=”form1″ runat=”server”>
<div>
<table>
<tr>
<td colspan=”3″>
<h4>Bind asp.net gridview using executereader example</h4>
</td>
</tr>
<tr>
<td colspan=”3″>
<asp:GridView ID=”gvSubjectDetails” runat=”server” AutoGenerateColumns=”false”
DataKeyNames=”SubjectID”>
<HeaderStyle Font-Bold=”true” BackColor=”#ff6600″ BorderColor=”#f5f5f5″
ForeColor=”White” Height=”30″ />
<Columns>
<asp:BoundField DataField=”SubjectID” HeaderText=”Subject Id”
ItemStyle-Width=”150px” />
<asp:BoundField DataField=”SubjectName” HeaderText=”Subject Name”
ItemStyle-Width=”200px” />
</Columns>
</asp:GridView>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
Now check the below sample code snippet to bind asp.net gridview control with executereader.
Bind Gridview Control In C#
Now add the following namespace that is required to connect with sql server:
After that add the following code to .aspx.cs page:
public string strConn = @”Data Source=datasource;Integrated Security=true;Initial Catalog=yourDB”;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGridviewFileData();
}
}
//bind subject details to gridview
private void BindGridviewFileData()
{
try
{
using (SqlConnection sqlConn = new SqlConnection(strConn))
{
using (SqlCommand sqlCmd = new SqlCommand())
{
sqlCmd.CommandText = “SELECT * FROM SubjectDetails”;
sqlCmd.Connection = sqlConn;
sqlConn.Open();
SqlDataReader objDataReader = sqlCmd.ExecuteReader();
gvSubjectDetails.DataSource = objDataReader;
gvSubjectDetails.DataBind();
sqlConn.Close();
}
}
}
catch { }
}
Bind Gridview Control In Vb.net
Now add the following namespace that is required to connect with sql server:
After that add the following code to .aspx.vb page:
Public Shared strConn As String =
“Data Source=datasource;Integrated Security=true;Initial Catalog=yourDB”
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
If Not IsPostBack Then
BindGridviewFileData()
End If
End Sub
‘bind subject details to gridview
Private Sub BindGridviewFileData()
Try
Using sqlConn As New SqlConnection(strConn)
Using sqlCmd As New SqlCommand()
sqlCmd.CommandText = “SELECT * FROM SubjectDetails”
sqlCmd.Connection = sqlConn
sqlConn.Open()
Dim objDataReader As SqlDataReader = sqlCmd.ExecuteReader()
gvSubjectDetails.DataSource = objDataReader
gvSubjectDetails.DataBind()
sqlConn.Close()
End Using
End Using
Catch
End Try
End Sub
Example Result
