Thursday, 5 September 2013

How to bind data to Gridview using SqlDataAdapter, SqlCommand, DataSet and Stored procedure in ASP.NET using VB.NET/C#.NET

Hi friends,in this article I will explain about How to bind data to Gridview using SqlDataAdapter, SqlCommand, DataSet and Stored procedure in ASP.NET.
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="empgrid" 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 con As New SqlConnection(ConfigurationManager.ConnectionStrings("con").ConnectionString)
        Dim ds As New DataSet()
        Try
            Dim cmd As New SqlCommand("display_EMP_DATA", con)
            cmd.CommandType = CommandType.StoredProcedure
            Dim adp As New SqlDataAdapter(cmd)

            adp.Fill(ds)
            If ds.Tables(0).Rows.Count > 0 Then
                empgrid.DataSource = ds
                empgrid.DataBind()
            Else
                empgrid.DataSource = Nothing
                empgrid.DataBind()
            End If
        Catch ex As Exception
            Response.Write("Error Occured: " & ex.ToString())
        Finally
            ds.Clear()
            ds.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()
       {
              SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings("con").ConnectionString);
              DataSet ds = new DataSet();
              try {
                     SqlCommand cmd = new SqlCommand("display_EMP_DATA", con);
                     cmd.CommandType = CommandType.StoredProcedure;
                     SqlDataAdapter adp = new SqlDataAdapter(cmd);

                     adp.Fill(ds);
                     if (ds.Tables[0].Rows.Count > 0) {
                           empgrid.DataSource = ds;
                           empgrid.DataBind();
                     } else {
                           empgrid.DataSource = null;
                           empgrid.DataBind();
                     }
              } catch (Exception ex) {
                     Response.Write("Error Occured: " + ex.ToString());
              } finally {
                     ds.Clear();
                     ds.Dispose();
              }
       }
    Public _Default()
       {
              Load += Page_Load;
       }
}

Save and run the page it will like as shown in the below figure.
Happy Reading ........If you like my blog Aspdotnet-Roja then why are you waiting like my blog through facebook 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.