ASP.NET DataAdapter
DataAdapter serves as a bridge between a DataSet and SQL Server for retrieving and saving data. We can use SqlDataAdapter Object in combination with Dataset Object. DataAdapter provides this combination by mapping Fill method, which changes the data in the DataSet to match the data in the data source, and Update, which changes 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);
The SqlDataAdapter Object and DataSet objects are combine to perform both data access and data manipulation operations in the SQL Server Database. When the user perform the SQL operations like Select , Insert etc. in the data containing in the Dataset Object , it wont directly affect the Database, until the user invoke the Update method in the SqlDataAdapter.
The DataAdapter can perform Select , Insert , Update and Delete SQL operations in the Data Source. The SelectCommand property of the DataAdapter is a Command Object that retrieves data from the data source. Other command properties of the DataAdapter are Command objects that manage updates to the data in the data source according to 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
Click the following links to see full source code
C# Source Code
VB.NET Source Code