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:
CREATE TABLE [dbo].[SubjectDetails]
(
[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:
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>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:
using System.Data.SqlClient;
After that add the following code to .aspx.cs page:
//specify your connection string here..
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:
Imports System.Data.SqlClient
After that add the following code to .aspx.vb page:
'specify your connection string here..'
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
Download Sample Code
Git Repo
That’s it! I hope this tutorial explains what you were looking for. I’ve provided step-by-step guide on how to bind GridView using ExecuteReader in .NET.
Let me know in comment below if you’ve any questions or quries regarding same. I would be happy to provide my feedback on it as soon as possible.
