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.
DataSet
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.
Fill()
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.
VB.Net
Dim adapter As New SqlDataAdapter(sql, connection)
adapter.Fill(ds)
C#
SqlDataAdapter adapter = new SqlDataAdapter(sql,connection );
adapter.Fill(ds);
Developers are able to use DataAdapter for population DataTables but then the other capabilities of DataSet itself are easier to take advantage of reliability, key constraints, and relationships between tables. This can be done through the creation of a complete, well-structured database that is used with ease to achieve 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.
Default.aspx
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<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
Conclusion
ADONET’s DataSet is a flexible container that is unified around the ordinary relations data model. SqlDataAdapter class lets you fill DataTables that are maintained in the DataSet with data from the data source. This is composed of three functions in this order; retrieve, store and then manipulate data.The framework is robust as it assists in performing tasks with data on a disconnected mode.