How to bind gridview using SqlDataAdapter, DataTable and query in ASp.net

Gridview Binding example in asp.net
Click on image to enlarge
Introduction: In previous articles i explained How to bind gridview using SqlDataAdapter, SqlCommand, DataTable and Stored procedure 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, DataTable and Stored procedure in Sql serverThere are multiple ways to bind GridView control with data in asp.net. In this article i will explain how to bind GridView using SqlDataAdapter, DataTable and inline query in Asp.net.
  • 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
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.
  • Now in web.config file add the  connectionstring under <configuration> tag :
<connectionStrings>
    <add name="EmpCon" connectionString="Data Source=LocalServer;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:
C#.Net Code to bind gridview using SqlDataAdapter, DataTable and query in Asp.net

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("Select * from EMPLOYEE", con);
            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, DataTable and query 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 dt As New DataTable()
 Try
  Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("EMpCon").ConnectionString)
  Dim adp As New SqlDataAdapter("Select * from EMPLOYEE", con)
  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


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