How to bind gridview using SqlDataAdapter , DataSet and Stored procedure in Asp.net ~ Asp.Net,C#.Net,VB.Net,MVC,jQuery,JavaScipt,AJAX,WCF,Sql Server example

Introduction: In previous articles i explained How to bind gridview using SqlDataAdapter, DataSet and query in Asp.net and How to pass parameter to stored procedure using SqlDataAdapter and check login in asp.net and How to bind gridview using SqlDataAdapter, SqlCommand, DataSet and Stored procedure in Asp.net.There are number of ways in asp.net to bind GridView with data. In this article i have explained how to bind GridView using SqlDataAdapter , DataSet and Stored procedure in Asp.net.

Click on image to enlarge

Create a Database e.g. "MyDataBase" and a table under that DataBase in Sql Server and name it "EMPLOYEE" as shown in figure:

Click on image to enlarge

Note: EMP_ID column is set to Primary key and Identity specification is set to yes with Identity increment and Identity seed equal to 1. Insert some data in this table that you want to show in the Gridview.

Create a stored procedure in sql server as:

CREATE PROCEDURE GET_EMP_DATA

BEGIN

SELECT * FROM EMPLOYEE

END

Now in web.config file add the connectionstring under <configuration> tag :

Add a GridView control in design page of your asp.net website under <BODY> tag

<legend>Gridview Binding Example</legend>

<asp:GridView ID="EmpGridView" runat="server" AutoGenerateColumns="False" CellPadding="4" ForeColor="#333333" GridLines="None">

<asp:BoundField DataField="EMP_NAME" HeaderText="Name" />

<asp:BoundField DataField="DEPT" HeaderText="Department" />

<asp:BoundField DataField="SALARY" HeaderText="Salary" />

</Columns>

</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 , DataSet and Stored procedure in Asp.net

First include the following namespaces

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

then write the code as:

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

BindEmpGrid();

}

private void BindEmpGrid()

{

DataSet ds = new DataSet();

try

{

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["EmpCon"].ConnectionString);

SqlDataAdapter adp = new SqlDataAdapter("GET_EMP_DATA", con);

adp.SelectCommand.CommandType = CommandType.StoredProcedure;

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 , DataSet and Stored procedure in Asp.net

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 ds As New DataSet()

Try

Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("EmpCon").ConnectionString)

Dim adp As New SqlDataAdapter("GET_EMP_DATA", con)

adp.SelectCommand.CommandType = CommandType.StoredProcedure

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

End Sub

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..

How to bind gridview using SqlDataAdapter , DataSet and Stored procedure in Asp.net

Related Post