Working with DataSets

The DataSet object is a core component of ADO.NET, providing an in-memory representation of data. It can hold multiple tables, relationships between them, and constraints. This section details how to effectively work with DataSet objects.

Creating and Populating a DataSet

You can create a DataSet object programmatically. Population typically involves using a DataAdapter to fill it from a data source.

C# Example:


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

// Assuming you have a connection string and SQL query
string connectionString = "Your_Connection_String";
string query = "SELECT CustomerID, CompanyName FROM Customers";

DataSet dataSet = new DataSet();

using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlDataAdapter adapter = new SqlDataAdapter(query, connection);
    adapter.Fill(dataSet, "Customers"); // Fill the DataSet with data into a table named "Customers"
}

// Now dataSet.Tables["Customers"] contains the data.

Accessing Data within a DataSet

A DataSet can contain multiple DataTable objects. You can access these tables by name or index.

Each DataTable contains rows and columns. You can iterate through rows and access individual cell values.

C# Example: Accessing Data


if (dataSet.Tables.Contains("Customers"))
{
    DataTable customersTable = dataSet.Tables["Customers"];

    foreach (DataRow row in customersTable.Rows)
    {
        // Access by column name
        string customerId = row["CustomerID"].ToString();
        string companyName = row["CompanyName"].ToString();

        Console.WriteLine($"ID: {customerId}, Company: {companyName}");

        // Access by column index (less readable, use with caution)
        // string customerIdByIndex = row[0].ToString();
    }
}

Modifying Data within a DataSet

You can add, update, and delete rows directly within a DataTable in memory. These changes can later be synchronized with the data source using a DataAdapter.

Adding a New Row:

C# Example: Adding a Row


if (dataSet.Tables.Contains("Customers"))
{
    DataTable customersTable = dataSet.Tables["Customers"];
    DataRow newRow = customersTable.NewRow();

    newRow["CustomerID"] = "NEWCUST";
    newRow["CompanyName"] = "New Company Ltd.";
    // Set other column values as needed

    customersTable.Rows.Add(newRow);
}

Updating a Row:

Find the row you want to update and modify its values.

C# Example: Updating a Row


if (dataSet.Tables.Contains("Customers"))
{
    DataTable customersTable = dataSet.Tables["Customers"];
    DataRow[] foundRows = customersTable.Select("CustomerID = 'ALFKI'"); // Example: Find row with CustomerID 'ALFKI'

    if (foundRows.Length > 0)
    {
        foundRows[0]["CompanyName"] = "Updated Company Name";
    }
}

Deleting a Row:

C# Example: Deleting a Row


if (dataSet.Tables.Contains("Customers"))
{
    DataTable customersTable = dataSet.Tables["Customers"];
    DataRow[] rowsToDelete = customersTable.Select("CustomerID = 'DELETEID'");

    foreach (DataRow row in rowsToDelete)
    {
        row.Delete(); // Mark the row for deletion
    }
}

Working with Relations and Constraints

DataSet objects support defining relationships between tables (e.g., foreign keys) and enforcing constraints (e.g., unique keys, foreign key constraints). This allows you to mimic relational database structures in memory.

For more details on defining and using relations and constraints, refer to the Data Relations and Constraints sections.

Accepting and Rejecting Changes

After making modifications to a DataSet, you can manage the state of these changes.

AcceptChanges(): Commits all current and deleted rows. Rows that were deleted are removed from the tables. Row states are reset to Unchanged.
RejectChanges(): Reverts all changes made since the last AcceptChanges() call. Deleted rows are restored, and modified or added rows are reverted to their original state.

Merging DataSets

You can merge the contents of one DataSet into another using the Merge() method. This is useful for combining data from different sources or updates.

C# Example: Merging DataSets


DataSet ds1 = new DataSet("Set1");
// Populate ds1...

DataSet ds2 = new DataSet("Set2");
// Populate ds2 with data that might overlap or extend ds1...

ds1.Merge(ds2); // Merge ds2 into ds1

The Merge() method has options to control how conflicts are handled.

Serializing and Deserializing DataSets

DataSet objects can be serialized into formats like XML or binary. This is crucial for transferring data across application boundaries, such as between a web server and a client, or for persistence.

Use the WriteXml() and ReadXml() methods for XML serialization, and WriteXmlSchema() and ReadXmlSchema() for schema serialization.

C# Example: XML Serialization


// Assuming 'dataSet' is populated

// Save DataSet to an XML file
dataSet.WriteXml("myDataSet.xml");

// Save DataSet schema to an XML file
dataSet.WriteXmlSchema("myDataSet.xsd");

// To read back:
DataSet loadedDataSet = new DataSet();
loadedDataSet.ReadXml("myDataSet.xml");
loadedDataSet.ReadXmlSchema("myDataSet.xsd"); // Optional, if you need the schema

For binary serialization, use RemotingFormat property and methods like BinaryWrite and BinaryRead (though XML is more common for ADO.NET DataSets).

Key Properties and Methods

Member	Description
`Tables`	Collection of `DataTable` objects within the `DataSet`.
`Relations`	Collection of `DataRelation` objects.
`EnforceConstraints`	Boolean indicating whether constraints are enforced.
`HasErrors`	Boolean indicating if any rows in any table have errors.
`AcceptChanges()`	Commits changes.
`RejectChanges()`	Rejects changes.
`Merge(DataSet)`	Merges another `DataSet` into this one.
`WriteXml()`	Serializes the `DataSet` to XML.
`ReadXml()`	Deserializes the `DataSet` from XML.