ASP.NET DataAdapter Commands
The DataAdapter in ADO.NET plays a crucial role in retrieving data from a data source and populating tables within a DataSet. It acts as a bridge between the data source and the DataSet, facilitating the transfer of data between the two.
When using a DataAdapter, the primary purpose is to retrieve data from the data source and fill the tables within a DataSet. The DataAdapter achieves this through its Fill() method, which retrieves data from the data source and populates the corresponding tables within the DataSet. This allows the data in the data source to be accessed and manipulated in a disconnected manner within the DataSet.
DataAdapter.Insert
VB.Net
Dim adapter As New SqlDataAdapter()
adapter.InsertCommand = New SqlCommand(insertSql, connection)
adapter.InsertCommand.ExecuteNonQuery()
C#
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.InsertCommand = new SqlCommand(insertSql, connection);
adapter.InsertCommand.ExecuteNonQuery();
In addition to retrieving data, the DataAdapter also provides functionality to resolve changes made to the DataSet back to the data source. This is accomplished through the use of the InsertCommand, UpdateCommand, and DeleteCommand properties of the DataAdapter. These properties are Command objects that manage the respective update operations (insert, update, and delete) in the data source based on modifications made to the data within the DataSet. When changes are made to the DataSet, the DataAdapter utilizes these command objects to translate and execute the appropriate SQL statements or stored procedures in the data source, ensuring that the changes are reflected accurately.
DataAdapter.UpdateCommand
VB.Net
Dim adapter As New SqlDataAdapter()
adapter.UpdateCommand = New SqlCommand(updateSql, connection)
adapter.UpdateCommand.ExecuteNonQuery()
C#
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.UpdateCommand = new SqlCommand(updateSql, connection);
adapter.UpdateCommand.ExecuteNonQuery();
By utilizing the InsertCommand, UpdateCommand, and DeleteCommand properties, developers have fine-grained control over how modifications to the data in the DataSet are synchronized with the data source. This allows for robust and efficient data management, enabling updates, inserts, and deletions to be applied back to the data source in a controlled and consistent manner.
DataAdapter.DeleteCommand
VB.Net
Dim adapter As New SqlDataAdapter()
adapter.DeleteCommand = New SqlCommand(deleteSql, connection)
adapter.DeleteCommand.ExecuteNonQuery()
C#
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.DeleteCommand = new SqlCommand(deleteSql, connection);
adapter.DeleteCommand.ExecuteNonQuery();
The following ASP.NET program shows how to insert data in the Data Source using SqlDataAdapter and SqlCommand object. Open a connection to the Data Source with the help of SqlConnection object and create a SqlCommand object with insert SQL statement, and assign the SqlCommand to the SqlDataAdapters InsertCommand.
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: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 insertSqll = "insert into discounts values('New Discont',8042,5000,5000,5.25)";
try
{
connection.Open();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.InsertCommand = new SqlCommand(insertSqll, connection);
adapter.InsertCommand.ExecuteNonQuery();
connection.Close();
Label1.Text = "data Inserted !! ";
}
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
connectionString = ConfigurationManager.ConnectionStrings("SQLDbConnection").ToString
connection = New SqlConnection(connectionString)
Dim insertSqll As String = "insert into discounts values('New Discont',8042,3000,3000,5.25)"
Try
connection.Open()
Dim adapter As New SqlDataAdapter()
adapter.InsertCommand = New SqlCommand(insertSqll, connection)
adapter.InsertCommand.ExecuteNonQuery()
connection.Close()
Label1.Text = "data Inserted !! "
Catch ex As Exception
Label1.Text = "Error in execution " & ex.ToString
End Try
End Sub
End Class
Conclusion
The DataAdapter in ADO.NET is a critical component for retrieving data from a data source and populating tables within a DataSet. It also provides the necessary mechanisms to resolve changes made to the DataSet back to the data source using the InsertCommand, UpdateCommand, and DeleteCommand properties. This allows for effective synchronization of data between the DataSet and the data source, ensuring data consistency and accuracy.