ADO.NET DataSet Objects

The DataSet object in ADO.NET is a memory-resident representation of data. It is an in-memory cache of a database, allowing you to access and manipulate data without being connected to the data source. A DataSet can hold multiple tables, along with relationships and constraints between them, making it a powerful tool for working with disconnected data.

Key Components of a DataSet

A DataSet is composed of several key elements:

Creating and Populating a DataSet

You can create a DataSet in code and then populate it using a DataAdapter, or you can create it programmatically by adding DataTable objects to its Tables collection.

Example: Populating a DataSet with a DataAdapter


using System;
using System.Data;
using System.Data.SqlClient;

// Assume connectionString and query are defined elsewhere
string connectionString = "Your_Connection_String";
string query = "SELECT CustomerID, CompanyName, ContactName FROM Customers";

DataSet customerDataSet = new DataSet("CustomersDataSet");

using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlDataAdapter adapter = new SqlDataAdapter(query, connection);
    adapter.Fill(customerDataSet, "Customers"); // Fills the DataSet and names the DataTable "Customers"
}

// Now customerDataSet contains the data from the Customers table

Working with Data in a DataSet

Once a DataSet is populated, you can access its tables, rows, and columns.

Accessing Tables


DataTable customersTable = customerDataSet.Tables["Customers"];
// Or by index
DataTable firstTable = customerDataSet.Tables[0];

Iterating Through Rows


foreach (DataRow row in customersTable.Rows)
{
    string customerID = row["CustomerID"].ToString();
    string companyName = row["CompanyName"].ToString();
    Console.WriteLine($"ID: {customerID}, Company: {companyName}");
}

Adding and Modifying Rows

You can create new rows, modify existing ones, and delete rows within a DataTable.


// Add a new row
DataRow newRow = customersTable.NewRow();
newRow["CustomerID"] = "NEWID";
newRow["CompanyName"] = "New Company Inc.";
newRow["ContactName"] = "John Doe";
customersTable.Rows.Add(newRow);

// Modify an existing row (assuming a row with CustomerID "ALFKI" exists)
DataRow existingRow = customersTable.Select("CustomerID = 'ALFKI'")[0];
existingRow["ContactName"] = "Alfred Schmidt (Updated)";

Accepting and Rejecting Changes

The DataSet tracks changes made to its data. You can use AcceptChanges() to commit all pending changes or RejectChanges() to discard them.


// Commit all changes made since the last AcceptChanges or Load
customerDataSet.AcceptChanges();

// Discard all pending changes
customerDataSet.RejectChanges();

Merging DataSets

You can merge the contents of one DataSet into another using the Merge() method.


DataSet anotherDataSet = new DataSet();
// Populate anotherDataSet...

customerDataSet.Merge(anotherDataSet);

Serialization

DataSet objects can be serialized to formats like XML, which is useful for transferring data across application domains or over networks.

Advantages of DataSet

Considerations

The DataSet object is a cornerstone of ADO.NET for managing structured data in memory. Understanding its capabilities and components is crucial for efficient data access in .NET applications.

Next: DataTable Objects