How to Bind Load Populate GridView Data from SQL Server Database Table using SqlDataAdapter DataTable in Asp.Net C# VB.Net

This is a simple article for Asp.Net beginners. Here i will describe how one can connect to SQL Srever Database using Asp.Net C# or VB.Net & then retrieve table data & bind this data into the Asp.Net GridView Control using SqlDataAdapter and DataTable.

How to Bind Load Populat GridView Data from SQL Server Database Table using SqlDataAdapter DataTable in Asp.Net C# VB.Net

To do the Example First Create a Database Table:

CREATE TABLE [dbo].[tblStock](
	[BrandName] [varchar](max) NULL,
	[CategoryName] [varchar](max) NULL,
	[ProductName] [varchar](max) NULL,
	[LogicalQuantity] [bigint] NULL,
	[PhysicalQuantity] [bigint] NULL,
	[QuarentineQuantity] [bigint] NULL

Now Insert Some Data:

INSERT INTO tblStock VALUES('Kohinoor','Soap','Tibbet Handwash',55781,55780,1)
INSERT INTO tblStock VALUES('Kohinoor','Soap','Tibbet Soap',38786,38780,6)
INSERT INTO tblStock VALUES('Kohinoor','ToothPaste','Tibbet Clear',34503,34500,3)
INSERT INTO tblStock VALUES('Proctor & Gamble','Bubble','',43809,43800,9)
INSERT INTO tblStock VALUES('Telenor','Mobile','Data SIM',43876,43870,6)
INSERT INTO tblStock VALUES('Telenor','Mobile','GP Modem',23907,23900,7)
INSERT INTO tblStock VALUES('Uniliver','Soap','Lux',19806,19800,6)
INSERT INTO tblStock VALUES('Uniliver','TeleProducts','Brittle',20703,20700,3)
INSERT INTO tblStock VALUES('Uniliver','ToothPaste','Close Up',16755,16750,5)

Now modify the Web.Config File to Connect to Database:

    <add name="DBConnection" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=TESTDB;Trusted_Connection=yes;" providerName="System.Data.SqlClient"/>
    <!--<add name="BONConnection" connectionString="Data;Initial Catalog=DBNAME;User Id=UserName;Password=YourPassword;" providerName="System.Data.SqlClient" />-->

Note: If you use windows authentication then use the first key. Otherwise use second key line. Update server name, Database name, User name, Password as per your settings.

Now Map the GridView Bound Column to DataBase Table Column:

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" HeaderStyle-BackColor="CornflowerBlue" HeaderStyle-Font-Bold="true" HeaderStyle-ForeColor="White" CellPadding="5">
            <asp:BoundField DataField="BrandName" HeaderText="Brand Name" />
            <asp:BoundField DataField="CategoryName" HeaderText="Category Name" />
            <asp:BoundField DataField="ProductName" HeaderText="Product Name" />
            <asp:BoundField DataField="LogicalQuantity" HeaderText="Logical" />
            <asp:BoundField DataField="PhysicalQuantity" HeaderText="Physical" />
            <asp:BoundField DataField="QuarentineQuantity" HeaderText="Quarentine" />

Now Bind/Load Data into GridView using SqlDataReader:
C# Code:

    protected void Page_Load(object sender, EventArgs e)
        if (!IsPostBack)
            DataTable dt;
            String SQL = "SELECT * FROM tblStock";

            string sConstr = ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
            using (SqlConnection conn = new SqlConnection(sConstr))
                using (SqlCommand comm = new SqlCommand(SQL, conn))
                    using (SqlDataAdapter da = new SqlDataAdapter(comm))
                        dt = new DataTable("tbl");

            GridView1.DataSource = dt;

Note: Don’t forget to add “System.Data”, “System.Data.SqlClient”, “System.Configuration” namespaces.

VB.Net Code:

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            Dim dt As DataTable
            Dim SQL As [String] = "SELECT * FROM tblStock"

            Dim sConstr As String = ConfigurationManager.ConnectionStrings("DBConnection").ConnectionString
            Using conn As New SqlConnection(sConstr)
                Using comm As New SqlCommand(SQL, conn)
                    Using da As New SqlDataAdapter(comm)
                        dt = New DataTable("tbl")
                    End Using
                End Using
            End Using

            GridView1.DataSource = dt
        End If

    End Sub

Note: Don’t forget to add “System.Data”, “System.Data.SqlClient”, “System.Configuration” namespaces.

Now run the project. hope you are getting Sql Server Database Table Data into your GridView.

Download Code Example C#        Download Code Example VB.Net

Posted in .Net,, C#, Gridview, VB.Net

Leave a Reply

Your email address will not be published. Required fields are marked *