ADO.NET provides a rich set of components for working with data from a data source. Among the most fundamental are DataAdapter and DataSet. These two objects work in tandem to enable disconnected data access, a powerful pattern for building responsive and scalable applications.
A DataSet is an in-memory representation of data. It is a collection of DataTable objects, which in turn contain DataRow objects and DataColumn objects. Think of a DataSet as a workbook with multiple worksheets (DataTables), where each worksheet holds tabular data.
DataTable: Represents a single table of data. It has columns (DataColumns) and rows (DataRows).DataColumn: Defines a column in a DataTable, including its name, data type, and constraints.DataRow: Represents a single record or row within a DataTable.
DataSet objects are particularly useful for scenarios where you need to:
While a DataSet holds data, a DataAdapter is the bridge between the DataSet and the data source. Its primary role is to fill a DataSet with data and to propagate changes made in the DataSet back to the data source.
DataAdapter objects abstract the details of data retrieval and modification. Different providers in ADO.NET offer specific implementations of DataAdapter, such as:
SqlDataAdapter: For SQL Server.OleDbDataAdapter: For OLE DB compliant data sources (including Access, Excel, etc.).OracleDataAdapter: For Oracle databases.
A DataAdapter typically has four core commands (which are themselves IDbCommand objects):
SelectCommand: Retrieves data from the data source.InsertCommand: Inserts new records into the data source.UpdateCommand: Modifies existing records in the data source.DeleteCommand: Deletes records from the data source.
The combination of DataSet and DataAdapter facilitates the disconnected data access model. This model involves the following steps:
DataAdapter's Fill() method to populate a DataSet.
DataSet.
DataSet (e.g., add, edit, delete rows).
DataAdapter's Update() method to synchronize the changes in the DataSet back to the data source.
Update() method of a DataAdapter automatically handles the generation of appropriate SQL `INSERT`, `UPDATE`, and `DELETE` statements based on the row state (`Added`, `Modified`, `Deleted`) within the DataTable.
Here's a simplified C# example demonstrating how to use SqlDataAdapter and DataSet to retrieve and display data from a SQL Server database.
using System;
using System.Data;
using System.Data.SqlClient;
public class DataAccessExample
{
public static void Main(string[] args)
{
string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";
string query = "SELECT CustomerID, CompanyName, ContactName FROM Customers;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlDataAdapter adapter = new SqlDataAdapter(query, connection);
DataSet dataSet = new DataSet();
try
{
connection.Open();
adapter.Fill(dataSet, "Customers"); // Fill the DataSet with a DataTable named "Customers"
Console.WriteLine("Customer Data:");
foreach (DataRow row in dataSet.Tables["Customers"].Rows)
{
Console.WriteLine($"ID: {row["CustomerID"]}, Name: {row["CompanyName"]}, Contact: {row["ContactName"]}");
}
}
catch (SqlException ex)
{
Console.WriteLine($"An error occurred: {ex.Message}");
}
finally
{
// Connection is closed automatically by the 'using' statement
}
}
}
}