Introduction:
In this article i will explain how to bind gridview using DataSet 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>
In the code behind file(.aspx.cs) of your asp.net
website write the code as
C#.Net Code to bind gridview using SqlDataAdapter,
SqlCommand, DataSet and Stored procedure
First include the following namespaces
First include the following namespaces
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
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;
SqlDataAdapter adp = new SqlDataAdapter(cmd);
adp.Fill(ds);
if (ds.Tables[0].Rows.Count > 0)
{
EmpGridView.DataSource = ds;
EmpGridView.DataBind();
}
else
{
EmpGridView.DataSource=null;
EmpGridView.DataBind();
}
}
catch(Exception ex)
{
Response.Write("Error Occured: " + ex.ToString());
}
finally
{
ds.Clear();
ds.Dispose();
}
}
VB.Net Code to bind gridview using SqlDataAdapter,
SqlCommand, DataSet 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("GET_EMP_DATA", con)
cmd.CommandType = CommandType.StoredProcedure
Dim adp As New SqlDataAdapter(cmd)
adp.Fill(ds)
If ds.Tables(0).Rows.Count > 0 Then
EmpGridView.DataSource = ds
EmpGridView.DataBind()
Else
EmpGridView.DataSource = Nothing
EmpGridView.DataBind()
End If
Catch ex As Exception
Response.Write("Error Occured: " & ex.ToString())
Finally
ds.Clear()
ds.Dispose()
End Try
0 comments:
Post a Comment