ADO.NET DataSets - MSDN Documentation

DataSets in ADO.NET

A DataSet is an in-memory representation of data. It is a collection of DataTable objects, which in turn contain DataRow objects representing rows of data. DataSets are a core component of ADO.NET and provide a powerful way to work with disconnected data. They are particularly useful for scenarios where you need to manipulate data without a constant connection to the database, such as in applications with user interfaces.

Key Features of a DataSet

In-memory cache: It holds data and schema information.
Disconnected architecture: It can operate independently of the data source.
Multiple tables: A DataSet can contain multiple DataTable objects, enabling the representation of complex relational data.
Relationships: You can define relationships between DataTables within a DataSet, mimicking database foreign key constraints.
Constraints: Supports primary keys, unique constraints, and foreign key constraints to ensure data integrity.
Xml support: Can be easily serialized to and deserialized from XML, facilitating data exchange.

When to Use DataSets

When you need to work with data offline.
When you need to manipulate data in memory, such as performing client-side filtering, sorting, or aggregation.
When you need to represent complex data structures involving multiple related tables.
When interoperating with other systems using XML.

Creating and Populating a DataSet

You can create a DataSet programmatically or have it populated by a DataAdapter.

Programmatic Creation

You can manually create tables and add them to a DataSet.


using System.Data;

// Create a new DataSet
DataSet myDataSet = new DataSet("SampleDataSet");

// Create a DataTable for Customers
DataTable customersTable = new DataTable("Customers");
customersTable.Columns.Add("CustomerID", typeof(int));
customersTable.Columns.Add("CompanyName", typeof(string));
customersTable.PrimaryKey = new DataColumn[] { customersTable.Columns["CustomerID"] };

// Create a DataTable for Orders
DataTable ordersTable = new DataTable("Orders");
ordersTable.Columns.Add("OrderID", typeof(int));
ordersTable.Columns.Add("CustomerID", typeof(int));
ordersTable.Columns.Add("OrderDate", typeof(DateTime));
ordersTable.PrimaryKey = new DataColumn[] { ordersTable.Columns["OrderID"] };

// Add tables to the DataSet
myDataSet.Tables.Add(customersTable);
myDataSet.Tables.Add(ordersTable);

// Add some data (simplified for example)
customersTable.Rows.Add(1, "Contoso Ltd.");
customersTable.Rows.Add(2, "Fabrikam Inc.");

ordersTable.Rows.Add(101, 1, new DateTime(2023, 10, 26));
ordersTable.Rows.Add(102, 2, new DateTime(2023, 10, 27));

// Add a relationship (optional but good practice)
DataRelation relation = new DataRelation("CustOrders",
    myDataSet.Tables["Customers"].Columns["CustomerID"],
    myDataSet.Tables["Orders"].Columns["CustomerID"]);
myDataSet.Relations.Add(relation);

Console.WriteLine("DataSet created and populated.");

Populating with a DataAdapter

The most common way to populate a DataSet is by using a DataAdapter (like SqlDataAdapter or OleDbDataAdapter) and its Fill method.

Example using SqlDataAdapter


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

string connectionString = "Your_Connection_String_Here";
string sqlQuery = "SELECT CustomerID, CompanyName FROM Customers; SELECT OrderID, CustomerID, OrderDate FROM Orders;";

using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlDataAdapter adapter = new SqlDataAdapter(sqlQuery, connection);
    DataSet dataSet = new DataSet("CompanyData");

    // The Fill method automatically creates DataTables if they don't exist
    // and names them based on the query or default names if multiple queries are used.
    adapter.Fill(dataSet);

    // You can specify table names:
    // adapter.Fill(dataSet, "Customers");
    // adapter.Fill(dataSet, "Orders");

    Console.WriteLine($"DataSet populated with {dataSet.Tables.Count} tables.");
    Console.WriteLine($"Customers table has {dataSet.Tables["Customers"].Rows.Count} rows.");
}

Accessing Data within a DataSet

You can access tables, rows, and columns by name or index.


// Assuming 'myDataSet' is already populated as shown above

// Accessing a specific table
DataTable customersTable = myDataSet.Tables["Customers"];

// Iterating through rows
foreach (DataRow row in customersTable.Rows)
{
    int customerId = (int)row["CustomerID"];
    string companyName = row["CompanyName"].ToString();
    Console.WriteLine($"ID: {customerId}, Name: {companyName}");
}

// Accessing data from a related table
if (myDataSet.Relations.Contains("CustOrders"))
{
    DataRelation rel = myDataSet.Relations["CustOrders"];
    foreach (DataRow customerRow in myDataSet.Tables["Customers"].Rows)
    {
        DataRow[] orderRows = customerRow.GetChildRows(rel);
        Console.WriteLine($"Customer {customerRow["CompanyName"]} has {orderRows.Length} orders.");
    }
}

Modifying Data and Updating the Data Source

Changes made to a DataSet (adding, deleting, or modifying rows) are tracked. You can then use the DataAdapter's Update method to persist these changes back to the database.

Updating Data


// Assuming 'myDataSet' and 'adapter' are set up and populated

// Example: Modify a company name
DataRow rowToUpdate = myDataSet.Tables["Customers"].Rows.Find(1); // Find row by primary key
if (rowToUpdate != null)
{
    rowToUpdate["CompanyName"] = "Contoso Corporation";
    rowToUpdate.AcceptChanges(); // Mark changes as accepted for this row
}

// Example: Add a new order
DataTable ordersTable = myDataSet.Tables["Orders"];
DataRow newOrderRow = ordersTable.NewRow();
newOrderRow["OrderID"] = 103;
newOrderRow["CustomerID"] = 1;
newOrderRow["OrderDate"] = DateTime.Now;
ordersTable.Rows.Add(newOrderRow);

// You would typically have INSERT, UPDATE, DELETE commands configured on the DataAdapter
// Example (conceptual, requires SQL INSERT statement setup on adapter):
// adapter.InsertCommand = new SqlCommand("INSERT INTO Orders (OrderID, CustomerID, OrderDate) VALUES (@OrderID, @CustomerID, @OrderDate)", connection);
// adapter.InsertCommand.Parameters.Add("@OrderID", SqlDbType.Int, 4, "OrderID");
// ... add other parameters

// Persist changes to the database
// adapter.Update(myDataSet.Tables["Customers"]); // Update changes in Customers table
// adapter.Update(myDataSet.Tables["Orders"]);   // Update changes in Orders table

// Or update multiple tables if the adapter's UpdateCommand, InsertCommand, DeleteCommand are set up correctly
// adapter.Update(myDataSet);

Console.WriteLine("Changes staged. Update command would send them to DB.");

DataSet vs. DataTable

A DataSet is a collection of DataTable objects. A DataTable represents a single table of data with rows and columns. While you can work directly with DataTables, using a DataSet is beneficial when dealing with multiple related tables or when you need the full capabilities of the DataSet class.

Considerations

Memory Usage: Large DataSets can consume significant memory. For very large datasets, consider streaming data or using other ADO.NET features like DataReader.
Concurrency: In multi-threaded applications, careful handling of DataSet modifications is required to avoid race conditions.
Data Type Handling: Ensure proper mapping of database data types to .NET types.

DataSets remain a robust and versatile tool for managing data within ADO.NET, offering a flexible in-memory data model for a wide range of applications.