MSDN Documentation

Working with DataSets in ADO.NET

A DataSet object represents an in-memory cache of data that can be used to store and retrieve data from a data source. It is a collection of DataTable objects, which in turn contain rows and columns of data. DataSet objects are particularly useful for working with disconnected data, where the application is not continuously connected to the data source.

What is a DataSet?

A DataSet is a fully featured in-memory relational database. It can hold multiple tables, views, and relationships between tables. Key features include:

Creating and Populating a DataSet

You can create a DataSet and populate it using a DataAdapter. The DataAdapter acts as a bridge between the DataSet and the data source, handling the retrieval and updating of data.


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

// ...

// Assume connectionString and sqlQuery are defined
string connectionString = "Your_Connection_String_Here";
string sqlQuery = "SELECT CustomerID, CompanyName, ContactName FROM Customers";

DataSet customerDataSet = new DataSet("CustomersDataset");

using (SqlConnection connection = new SqlConnection(connectionString))
{
    using (SqlDataAdapter adapter = new SqlDataAdapter(sqlQuery, connection))
    {
        // Fill the DataSet
        adapter.Fill(customerDataSet, "CustomersTable");
    }
}

// Now customerDataSet contains data in the CustomersTable DataTable
Console.WriteLine($"Number of tables in DataSet: {customerDataSet.Tables.Count}");
Console.WriteLine($"Number of rows in CustomersTable: {customerDataSet.Tables["CustomersTable"].Rows.Count}");

Accessing Data in a DataSet

Once a DataSet is populated, you can access its data through its Tables collection. Each element in this collection is a DataTable object.


// Assuming customerDataSet is already populated as shown above

if (customerDataSet.Tables.Contains("CustomersTable"))
{
    DataTable customersTable = customerDataSet.Tables["CustomersTable"];

    Console.WriteLine("\nCustomer Data:");
    foreach (DataRow row in customersTable.Rows)
    {
        Console.WriteLine($"ID: {row["CustomerID"]}, Name: {row["CompanyName"]}, Contact: {row["ContactName"]}");
    }
}

Working with DataTables and DataRows

Each DataTable within a DataSet has a collection of DataRow objects. You can iterate through these rows, access individual column values, and even modify them.

Adding a New Row


DataTable customersTable = customerDataSet.Tables["CustomersTable"];
DataRow newRow = customersTable.NewRow(); // Create a new row based on table schema

newRow["CustomerID"] = "NEWID";
newRow["CompanyName"] = "New Corp";
newRow["ContactName"] = "Jane Doe";

customersTable.Rows.Add(newRow); // Add the new row to the table
Console.WriteLine($"\nAdded a new row. Total rows: {customersTable.Rows.Count}");

Modifying a Row


// Find a specific row (e.g., by CustomerID)
DataRow rowToModify = customersTable.Rows.Find("NEWID"); // Requires CustomerID to be a primary key

if (rowToModify != null)
{
    rowToModify["ContactName"] = "John Smith";
    rowToModify.AcceptChanges(); // Mark changes as accepted
    Console.WriteLine($"\nModified row with ID NEWID. New Contact Name: {rowToModify["ContactName"]}");
}

Deleting a Row


// Find the row to delete
DataRow rowToDelete = customersTable.Rows.Find("NEWID");

if (rowToDelete != null)
{
    rowToDelete.Delete(); // Mark the row for deletion
    // To permanently remove, you might use AcceptChanges on the DataSet later,
    // or if updating the database, the DataAdapter would handle this.
    Console.WriteLine($"\nMarked row with ID NEWID for deletion.");
}

Note: When working with DataSets and DataAdapters, the AcceptChanges() method is important for tracking changes. Calling it commits pending changes, while calling it on a row marked for deletion removes it from the table.

Relationships between DataTables

DataSet supports defining relationships between tables, similar to foreign key constraints in a relational database. This allows you to navigate from a row in one table to related rows in another.


// Assume you also have an OrdersTable in your DataSet
// You would create a DataRelation like this:
DataColumn parentColumn = customerDataSet.Tables["CustomersTable"].Columns["CustomerID"];
DataColumn childColumn = customerDataSet.Tables["OrdersTable"].Columns["CustomerID"];

DataRelation relation = new DataRelation("CustomerOrders", parentColumn, childColumn);
customerDataSet.Relations.Add(relation);

// Now you can navigate:
DataTable customersTable = customerDataSet.Tables["CustomersTable"];
DataTable ordersTable = customerDataSet.Tables["OrdersTable"];

// For a specific customer row:
DataRow customerRow = customersTable.Rows[0]; // Example: Get the first customer
DataRow[] relatedOrders = customerRow.GetChildRows(relation);

Console.WriteLine($"\nOrders for {customerRow["CompanyName"]}:");
foreach (DataRow orderRow in relatedOrders)
{
    Console.WriteLine($"- OrderID: {orderRow["OrderID"]}, OrderDate: {orderRow["OrderDate"]}");
}

Tip: While DataSet is powerful for disconnected scenarios and complex in-memory data manipulation, for simpler scenarios or when tight integration with the data source is required, consider using a DataReader directly or exploring ORMs like Entity Framework.

Updating the Data Source

The real power of DataSet combined with DataAdapter is the ability to update the underlying data source with changes made in the DataSet. The DataAdapter.Update() method handles this, issuing appropriate INSERT, UPDATE, or DELETE statements based on the row state.


// Assuming adapter is already configured for the CustomersTable
// and changes have been made to the DataSet (new rows added, rows modified, etc.)

// Call Update to send changes back to the database
// The number of rows affected will be returned
int rowsAffected = adapter.Update(customerDataSet, "CustomersTable");
Console.WriteLine($"\nUpdated {rowsAffected} rows in the database.");

This process often involves configuring the InsertCommand, UpdateCommand, and DeleteCommand properties of the DataAdapter.