Introduction:
In this article we learn how to bind gridview using Datatable 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>
If Not IsPostBack Then
BindEmpGrid()
End If
End Sub
Private Sub BindEmpGrid()
Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("EmpCon").ConnectionString)
Dim dt As New DataTable()
Try
Dim cmd As New SqlCommand("GET_EMP_DATA", con)
cmd.CommandType = CommandType.StoredProcedure
Dim adp As New SqlDataAdapter(cmd)
adp.Fill(dt)
If dt.Rows.Count > 0 Then
EmpGridView.DataSource = dt
EmpGridView.DataBind()
Else
EmpGridView.DataSource = Nothing
EmpGridView.DataBind()
End If
Catch ex As Exception
Response.Write("Error Occured: " & ex.ToString())
Finally
dt.Clear()
dt.Dispose()
End Try
End Sub
- 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, DataTable 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);
DataTable dt = new DataTable();
try
{
SqlCommand cmd = new SqlCommand("GET_EMP_DATA", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter adp = new SqlDataAdapter(cmd);
adp.Fill(dt);
if (dt.Rows.Count > 0)
{
EmpGridView.DataSource = dt;
EmpGridView.DataBind();
}
else
{
EmpGridView.DataSource=null;
EmpGridView.DataBind();
}
}
catch(Exception ex)
{
Response.Write("Error Occured: " + ex.ToString());
}
finally
{
dt.Clear();
dt.Dispose();
}
}
VB.Net Code to bind gridview using SqlDataAdapter, SqlCommand, DataTable and Stored procedure
First import the following namespaces
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
then write code as:
- In the code behind file(.aspx.vb) of your asp.net website write the code as
If Not IsPostBack Then
BindEmpGrid()
End If
End Sub
Private Sub BindEmpGrid()
Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("EmpCon").ConnectionString)
Dim dt As New DataTable()
Try
Dim cmd As New SqlCommand("GET_EMP_DATA", con)
cmd.CommandType = CommandType.StoredProcedure
Dim adp As New SqlDataAdapter(cmd)
adp.Fill(dt)
If dt.Rows.Count > 0 Then
EmpGridView.DataSource = dt
EmpGridView.DataBind()
Else
EmpGridView.DataSource = Nothing
EmpGridView.DataBind()
End If
Catch ex As Exception
Response.Write("Error Occured: " & ex.ToString())
Finally
dt.Clear()
dt.Dispose()
End Try
End Sub
0 comments:
Post a Comment