Bind Data to GridView Control using DataSet in Asp.Net C# and Vb.Net

A DataSet represents a set DataTable objects. Its like a mini database that does not need an active connection with the database. A dataset object has many benefits. However, in this post I’ll share with you a simple example on how to bind data to a GridView control using DataSet in Asp.Net. The examples are in C# and VB.

The GridView in this example is paging enabled. The data binding of the GridView control will be done using code behind procedures.

The Markup and GridView

<!DOCTYPE html>
<html>
<head>
    <title>Bind Data to GridView using DataSet</title>
    <style>
        .Grid { 
            width: auto;
            margin: 5px 0 10px 0;
            background-color: #FFF;
            border: solid 1px #525252;
        }
        .Grid td {
            font:inherit;
            padding:2px 5px;
            border:solid 1px #C1C1C1;
            color:#333;
            text-align:center;
            text-transform:none;
        }
    </style>
</head>
<body>
    <form id="frmGridViewDemo" runat="server">
        <div>
            <asp:GridView ID="GridView"
                CssClass="Grid"
                runat="server"
                AllowPaging PageSize="5"
                OnPageIndexChanging="GridView_PageIndexChanging">
               
                <HeaderStyle BackColor="#989898" ForeColor="white" />
            </asp:GridView>
        </div>
    </form>
</body>
</html>

Code Behind (C#)

using System;
using System.Data;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            BindGridView();
        }
    }

    protected void GridView_PageIndexChanging(object sender, System.Web.UI.WebControls.GridViewPageEventArgs e)
    {
        // GRIDVIEW PAGING.
        GridView.PageIndex = e.NewPageIndex;
        BindGridView();
    }

    private void BindGridView()
    {
        // SET THE CONNECTION STRING.
        string sCon = "Data Source=DNA;Persist Security Info=False;Integrated Security=SSPI;" +
            "Initial Catalog=DNA_Classified;User Id=sa;Password=demo;Connect Timeout=30;";

        using (SqlConnection con = new SqlConnection(sCon))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT *FROM dbo.Books"))
            {
                SqlDataAdapter sda = new SqlDataAdapter();
                try
                {
                    cmd.Connection = con;
                    con.Open();
                    sda.SelectCommand = cmd;

                    DataSet ds = new DataSet();
                    sda.Fill(ds);

                    // BIND DATABASE WITH THE GRIDVIEW.
                    GridView.DataSource = ds;
                    GridView.DataBind();
                }
                catch (Exception ex)
                {
                    //
                }
            }
        }
    }
}

The DataSet class is defined in the System.Data namespace in the .Net framework. Therefore, you need to add the namespace in the beginning of the code.

When the form loads, I am calling a private procedure that will create a connection to an SQL Server database and table. Once I have successfully set the connection, I am declaring and initializing a DataSet object.

DataSet ds = new DataSet();

The DataSet object is then filled with data using the SqlDataAdapter() class. This class represents a set of data commands and connection that are used to fill the DataSet.

sda.Fill(ds);

The DataSet object now has the data extracted from the database table. Now, you can bind it with GridView control.

GridView.DataSource = ds;
GridView.DataBind();

Code Behind (Vb)

Option Explicit On
Imports System.Data                        ' FOR "DataSet".
Imports System.Data.SqlClient

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub frmGridViewDemo_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles frmGridViewDemo.Load
        If Not IsPostBack Then
            BindGridView()
        End If
    End Sub

    Protected Sub GridView_PageIndexChanging(sender As Object, e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles GridView.PageIndexChanging
        ' GRIDVIEW PAGING.
        GridView.PageIndex = e.NewPageIndex
        BindGridView()
    End Sub

    Private Sub BindGridView()
        ' SET THE CONNECTION STRING.
        Dim sCon As String = "Data Source=DNA;Persist Security Info=False;Integrated Security=SSPI;" & _
            "Initial Catalog=DNA_Classified;User Id=sa;Password=demo;Connect Timeout=30;"

        Using con As SqlConnection = New SqlConnection(sCon)
            Using cmd As SqlCommand = New SqlCommand("SELECT *FROM dbo.Books")

                Dim sda As SqlDataAdapter = New SqlDataAdapter
                Try
                    cmd.Connection = con : con.Open()
                    sda.SelectCommand = cmd

                    Dim ds As DataSet = New DataSet
                    sda.Fill(ds)

                    ' BIND DATABASE WITH THE GRIDVIEW.
                    GridView.DataSource = ds
                    GridView.DataBind()
                Catch ex As Exception
                    '
                End Try
            End Using
        End Using
    End Sub
End Class

Also Read: How to Bind data to a GridView Control using SqlDataSource in Asp.Net

Conclusion

The DataSet is a core component in the .Net framework. It has its benefits and some limitations. I’ll not discuss everything about dataset here in this post. However, it is useful when you want to share data across multiple pages (using Session) without repeatedly setting a connection to the database. You can cache the data by the storing it in a dataset.

That’s it. Thanks for reading. ☺

← PreviousNext →

Bind Data to GridView Control using DataSet in Asp.Net C# and Vb.Net

Tools you can use