ASP.NET DataAdapter
The DataAdapter in ADO.NET acts as a bridge between a DataSet object and a SQL Server database, facilitating the retrieval and saving of data. It provides a seamless integration between the DataSet and the database by utilizing the SqlDataAdapter object.
DataAdapter with DataSet object
By combining the DataAdapter with a DataSet object, developers can perform both data access and data manipulation operations. The Fill method of the DataAdapter is used to populate the DataSet with data from the database, ensuring that the data in the DataSet matches the data in the data source. On the other hand, the Update method of the DataAdapter is used to update the data in the data source to match the data in the DataSet.
VB.Net
Dim adapter As New SqlDataAdapter(sql, connection)
adapter.Fill(ds)
C#
SqlDataAdapter adapter = new SqlDataAdapter(sql,connection );
adapter.Fill(ds);
When working with the DataAdapter, the user can perform SQL operations such as Select, Insert, Update, and Delete on the data contained within the DataSet. However, these operations do not directly affect the database until the Update method of the DataAdapter is invoked. This ensures that any changes made to the data in the DataSet are reflected in the database.
SelectCommand
The DataAdapter offers the capability to perform various SQL operations in the data source. The SelectCommand property of the DataAdapter is a Command Object that retrieves data from the data source based on the specified SQL statement or stored procedure. The other command properties of the DataAdapter, such as InsertCommand, UpdateCommand, and DeleteCommand, are Command Objects that manage the corresponding update operations in the data source based on modifications made to the data in the DataSet.
The following ASP.NET program shows a select operation using SqlDataAdapter.
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
The DataAdapter in ADO.NET serves as a crucial component for data retrieval and manipulation between a DataSet and a SQL Server database. It enables the execution of SQL operations and manages the synchronization of data between the DataSet and the data source. By utilizing the SelectCommand and other command properties, developers can retrieve, insert, update, and delete data from the data source using the DataAdapter and DataSet combination.