Thursday, 5 September 2013

How to bind gridview using SqlDataAdapter, DataTable and Stored procedure in Sql server using ASP.NET,VB.NET/C#.NET

Hi friends,in this article I will explain about How to bind gridview using SqlDataAdapter, DataTable and Stored procedure in Sql server.
Create a Database e.g. "Aspdotnet-roja" and a table under that DataBase in Sql Server and name it "EMP" as shown in figure:-


And create the stored procedure as display_emp_data  to display the emp data as shown below.
CREATE PROCEDURE display_EMP_DATA                      
AS
BEGIN              
   SELECT * FROM EMP           
END
And open the .aspx page and drag and drop the Gridview.
In we.config file write the following code to create the connection string in the <connectionStrings>
Tag.
<connectionStrings>
    <add name="con" connectionString="database=Aspdotnet-Roja;uid=sa;password=123;" />
</connectionStrings>

And imports below namespaces
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Add a GridView control in design page of your asp.net website under <BODY> tag
<asp:GridView ID="Gridview1" runat="server" AllowSorting="True"
        AutoGenerateColumns="False" BorderColor="#000099" BorderStyle="Double" >
             <AlternatingRowStyle BackColor="White" />
       <Columns> 
        <asp:BoundField DataField="EMP_id"  HeaderText="Emp_ID" />
        <asp:BoundField DataField="empname"  HeaderText="Emp_Name" />
        <asp:BoundField DataField="SALARY"  HeaderText="Salary" /> 
      </Columns>
            <EditRowStyle BackColor="#FF99FF" />
         <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
         <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
         <PagerStyle BackColor="#FF99FF" ForeColor="White" HorizontalAlign="Center" />
         <RowStyle BackColor="#CCFFFF" />
         <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
         <SortedAscendingCellStyle BackColor="#F5F7FB" />
         <SortedAscendingHeaderStyle BackColor="#6D95E1" />
         <SortedDescendingCellStyle BackColor="#E9EBEF" />
         <SortedDescendingHeaderStyle BackColor="#4870BE" />
 </asp:GridView>
And write the following code in code behind.
In VB.NET:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Partial Class _Default
    Inherits System.Web.UI.Page
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            Binddata()
        End If
    End Sub
    Private Sub Binddata()
   Dim dt As New DataTable()
        Try
            Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("Con").ConnectionString)

            Dim adp As New SqlDataAdapter("display_EMP_DATA", con)
            adp.SelectCommand.CommandType = CommandType.StoredProcedure
            adp.Fill(dt)

            If dt.Rows.Count > 0 Then
                GridView1.DataSource = dt
                GridView1.DataBind()
            Else
                GridView1.DataSource = Nothing
                GridView1.DataBind()
            End If
        Catch ex As Exception
            Response.Write("Error Occured: " & ex.ToString())
        Finally
            dt.Clear()
            dt.Dispose()
        End Try
    End Sub
End Class
In C#.NET:
using Microsoft.VisualBasic;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Data.SqlClient;
using System.Configuration;

partial class _Default : System.Web.UI.Page
{
       protected void Page_Load(object sender, System.EventArgs e)
       {
              if (!IsPostBack) {
                     Binddata();
              }
       }
       private void Binddata()
       {
              DataTable dt = new DataTable();
              try {
                     SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings("Con").ConnectionString);

                     SqlDataAdapter adp = new SqlDataAdapter("display_EMP_DATA", con);
                     adp.SelectCommand.CommandType = CommandType.StoredProcedure;
                     adp.Fill(dt);

                     if (dt.Rows.Count > 0) {
                           GridView1.DataSource = dt;
                           GridView1.DataBind();
                     } else {
                           GridView1.DataSource = null;
                            GridView1.DataBind();
                     }
              } catch (Exception ex) {
                     Response.Write("Error Occured: " + ex.ToString());
              } finally {
                     dt.Clear();
                     dt.Dispose();
              }
       }
    Public _Default()
       {
              Load += Page_Load;
       }
}



Save and run the page it will like as shown in the below figure.
I think you like my blog why are waiting following me on facebook fan page Aspdotnet-roja

No comments:

Post a Comment

© 2012-2014 Aspdotnet-Kishore.blogspot.com. All Rights Reserved.
The content is copyrighted to Kishore and may not be reproduced on other websites without permission from the owner.