How to Asp.Net Dataset

A DataSet in ADO.NET is a powerful in-memory representation of data that encompasses rows, columns, primary keys, constraints, and relationships with other DataTable objects. It provides a consistent and relational programming model, independent of the underlying data source.


When working with a DataSet, it is common to use it in conjunction with the SqlDataAdapter class. The DataAdapter serves as a bridge between the DataSet and the data source, allowing for the building and filling of each DataTable within the DataSet with data.


To populate DataTables within the DataSet, the SqlDataAdapter object is utilized. The DataAdapter provides methods such as Fill() that retrieve data from the data source and populate the corresponding DataTables in the DataSet. This allows for efficient retrieval and storage of data in a disconnected manner, providing greater flexibility and performance.

Dim adapter As New SqlDataAdapter(sql, connection) adapter.Fill(ds)
SqlDataAdapter adapter = new SqlDataAdapter(sql,connection ); adapter.Fill(ds);

By using the DataAdapter to populate DataTables, developers can leverage the features and functionality provided by the DataSet, such as primary keys, constraints, and relationships between tables. This enables the creation of a comprehensive and structured representation of data, facilitating efficient data management and manipulation.

The combination of the DataSet and SqlDataAdapter provides a powerful framework for working with data in a consistent and relational manner. It allows for seamless integration between the data source and the in-memory representation of data, enabling developers to perform complex operations and queries on the DataSet while maintaining data integrity.

The following ASP.NET program retrieve publishers name from publisher table using a Dataset.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" ""> <html xmlns=""> <head id="Head1" runat="server"> <title>Untitled Page</title> </head> <body> <form id="form1" runat="server"> <div> <asp:Button ID="Button1" runat="server" Text="Button" onclick="Button1_Click" /> <br /> <asp:ListBox ID="ListBox1" runat="server"></asp:ListBox> <br /> <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label> </div> </form> </body> </html>
Full Source | C#
using System; using System.Data ; using System.Data.SqlClient ; using System.Configuration; public partial class _Default : System.Web.UI.Page { protected void Button1_Click(object sender, EventArgs e) { string connectionString = ConfigurationManager.ConnectionStrings["SQLDbConnection"].ToString(); SqlConnection connection = new SqlConnection(connectionString); DataSet ds = new DataSet (); string sql = "select pub_name from publishers"; try { connection.Open(); SqlDataAdapter adapter = new SqlDataAdapter(sql,connection ); adapter.Fill(ds); for (int i = 0;i<ds.Tables[0].Rows.Count -1;i++) { ListBox1.Items.Add(ds.Tables[0].Rows[i].ItemArray[0].ToString ()); } connection.Close(); } catch (Exception ex) { Label1.Text = "Error in execution " + ex.ToString(); } } }
Full Source | VB.NET
Imports System.Data Imports System.Data.SqlClient Imports System.Configuration Partial Class _Default Inherits System.Web.UI.Page Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click Dim connectionString As String Dim connection As SqlConnection Dim ds As New DataSet Dim i As Integer connectionString = ConfigurationManager.ConnectionStrings("SQLDbConnection").ToString connection = New SqlConnection(connectionString) Dim sql As String = "select pub_name from publishers" Try connection.Open() Dim adapter As New SqlDataAdapter(sql, connection) adapter.Fill(ds) For i = 0 To ds.Tables(0).Rows.Count - 1 ListBox1.Items.Add(ds.Tables(0).Rows(i).Item(0)) Next connection.Close() Catch ex As Exception Label1.Text = "Error in execution " & ex.ToString End Try End Sub End Class


The DataSet in ADO.NET is a versatile container that holds data in a consistent relational model. By using the SqlDataAdapter class, DataTables within the DataSet can be populated with data from the data source. This combination allows for efficient data retrieval, storage, and manipulation, providing a robust framework for working with data in a disconnected environment.