Introduction:
In this article we learn how to bind gridview using DataReader and Stored procedure in Asp.net and Sql server.
Note: EMP_ID column is set to Primary key and Identity specification is set to yes .
Create a stored procedure in sql server as:
CREATE PROCEDURE GET_EMP_DATA
AS
BEGIN
SELECT * FROM EMPLOYEE
END
- Now in web.config file add the connectionstring under <configuration> tag :
<connectionStrings>
<add name="EmpCon" connectionString="Data Source=localhost;Initial Catalog=test;Integrated Security=True"/>
</connectionStrings>
<add name="EmpCon" connectionString="Data Source=localhost;Initial Catalog=test;Integrated Security=True"/>
</connectionStrings>
Add a GridView control in design page of your asp.net website under <BODY> tag
<fieldset style="width:230px;">
<legend>Bind Gridview With Database</legend>
<asp:GridView ID="EmpGridView" runat="server" AutoGenerateColumns="False"
CellPadding="4" ForeColor="Black" BackColor="#CCCCCC" BorderColor="#999999"
BorderStyle="Solid" BorderWidth="3px" CellSpacing="2" Width="223px">
<Columns>
<asp:BoundField DataField="empname" HeaderText="Name" />
<asp:BoundField DataField="salary" HeaderText="Salary" />
</Columns>
<FooterStyle BackColor="#CCCCCC" />
<HeaderStyle BackColor="Black" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#CCCCCC" ForeColor="Black" HorizontalAlign="Left" />
<RowStyle BackColor="White" />
<SelectedRowStyle BackColor="#000099" Font-Bold="True" ForeColor="White" />
<SortedAscendingCellStyle BackColor="#F1F1F1" />
<SortedAscendingHeaderStyle BackColor="#808080" />
<SortedDescendingCellStyle BackColor="#CAC9C9" />
<SortedDescendingHeaderStyle BackColor="#383838" />
</asp:GridView>
</fieldset>
VB.Net Code to bind gridview using DataReader, SqlCommand and Stored procedure
- In the code behind file(.aspx.cs) of your asp.net website write the code as
C#.Net Code
to bind gridview using DataReader, SqlCommand and Stored procedure
First include the following namespaces
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
then write code as:
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindEmpGrid();
}
}
private void BindEmpGrid()
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["EmpCon"].ConnectionString);
DataSet ds = new DataSet();
try
{
SqlCommand cmd = new SqlCommand("GET_EMP_DATA", con);
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
EmpGridView.DataSource = dr;
EmpGridView.DataBind();
}
else
{
EmpGridView.DataSource=null;
EmpGridView.DataBind();
}
}
catch(Exception ex)
{
Response.Write("Error Occured: " + ex.ToString());
}
finally
{
con.Close();
}
}
VB.Net Code to bind gridview using DataReader, SqlCommand and Stored procedure
First import the following namespaces
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
then write code as:
Protected Sub Page_Load(sender As Object, e As EventArgs)
If Not IsPostBack Then
BindEmpGrid()
End If
End Sub
Private Sub BindEmpGrid()
Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("EmpCon").ConnectionString)
Dim ds As New DataSet()
Try
Dim cmd As New SqlCommand("SELECT * FROM EMPLOYEE", con)
con.Open()
Dim dr As SqlDataReader = cmd.ExecuteReader()
If dr.HasRows Then
EmpGridView.DataSource = dr
EmpGridView.DataBind()
Else
EmpGridView.DataSource = Nothing
EmpGridView.DataBind()
End If
Catch ex As Exception
Response.Write("Error Occured: " & ex.ToString())
Finally
con.Close()
End Try
End Sub
0 comments:
Post a Comment