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.

Dim adapter As New SqlDataAdapter(sql, connection) adapter.Fill(ds)
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.


<!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

Click the following links to see full source code

C# Source Code
VB.NET Source Code