How to bind gridview using SqlDataAdapter, DataTable and Stored procedure in Sql server


In this article i have tried to explain how to bind GridView using SqlDataAdapter, DataTable and Stored procedure in Sql server in asp.net

Implementation: Let's create a demo web application to see it in action.
  • Create a Database e.g. "MyDataBase" and a table under that DataBase in Sql Server and name it "EMPLOYEE" as  shown in figure:

Binding gridview example in asp.net

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                       
AS
BEGIN               
                SELECT * FROM EMPLOYEE                
END
  • Now in web.config file add the connectionstring under <configuration> tag :
<connectionStrings>
    <add name="EmpCon" connectionString="Data Source=LALIT;Initial Catalog=MyDataBase;Integrated Security=True"/>
  </connectionStrings>

  • Add a GridView control in design page of your asp.net website under <BODY> tag
<fieldset style="width:230px;">
            <legend>Gridview Binding Example</legend>
            <asp:GridView ID="EmpGridView" runat="server" AutoGenerateColumns="False" CellPadding="4" ForeColor="#333333" GridLines="None">    
         <AlternatingRowStyle BackColor="White" />
       <Columns>  
        <asp:BoundField DataField="EMP_NAME"  HeaderText="Name" />
        <asp:BoundField DataField="DEPT"  HeaderText="Department" />
        <asp:BoundField DataField="SALARY"  HeaderText="Salary" />  
      </Columns>
         <EditRowStyle BackColor="#2461BF" />
         <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
         <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
         <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
         <RowStyle BackColor="#EFF3FB" />
         <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
         <SortedAscendingCellStyle BackColor="#F5F7FB" />
         <SortedAscendingHeaderStyle BackColor="#6D95E1" />
         <SortedDescendingCellStyle BackColor="#E9EBEF" />
         <SortedDescendingHeaderStyle BackColor="#4870BE" />
 </asp:GridView>
        </fieldset>
  • In the code behind file(.aspx.cs)  of your asp.net website write the code as 
Asp.Net C# Code

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()
    {
        DataTable dt = new DataTable();
        try
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["EmpCon"].ConnectionString);
            
            SqlDataAdapter adp = new SqlDataAdapter("GET_EMP_DATA", con);
            adp.SelectCommand.CommandType = CommandType.StoredProcedure;
            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();
        }     
    }

In the code behind file(.aspx.vb)  of your asp.net website write the code as

Asp.Net VB Code

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 dt As New DataTable()
                    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(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

Now over to you:
" I hope you have got How to bind Gridview and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates."
Previous
Next Post »

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..