Working with DataSets

A DataSet is an in-memory representation of data. It is a collection of DataTables, DataRelations, and Constraints. The DataSet object is designed to provide a rich set of features for working with data, independent of the data source. This makes it particularly useful for disconnected data scenarios, where you retrieve data from a data source, work with it in memory, and then potentially update the data source.

Key Concepts

DataTable

A DataTable represents a single table of data in memory. It contains a collection of DataRows and DataColumns, along with constraints and relationships.

DataColumns: Define the schema of the table, including column name, data type, and nullability.
DataRows: Represent individual records within the table.

DataRelations

DataRelations allow you to define relationships between tables within a DataSet, similar to foreign key relationships in a relational database. This enables you to navigate between related data.

Constraints

Constraints, such as UniqueConstraint and ForeignKeyConstraint, are used to enforce data integrity within the DataSet, ensuring data accuracy and consistency.

Creating and Populating a DataSet

You can create a DataSet programmatically and populate it by using a DataAdapter.

Example: Populating a DataSet


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

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

using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlDataAdapter adapter = new SqlDataAdapter(query, connection);
    DataSet dataSet = new DataSet("MyCustomers"); // Name the DataSet
    
    connection.Open();
    adapter.Fill(dataSet, "Customers"); // Fill the DataSet with a DataTable named "Customers"
    connection.Close();

    // Now you can work with dataSet.Tables["Customers"]
    foreach (DataRow row in dataSet.Tables["Customers"].Rows)
    {
        Console.WriteLine($"ID: {row["CustomerID"]}, Name: {row["CompanyName"]}");
    }
}

Working with Data in a DataSet

Accessing Data

You can access tables, rows, and columns within a DataSet using their names or indexes.

Example: Accessing Data


// Assuming 'dataSet' is already populated with a "Customers" table
DataTable customersTable = dataSet.Tables["Customers"];

// Accessing a specific row and column
DataRow firstRow = customersTable.Rows[0];
string companyName = firstRow["CompanyName"].ToString();

// Iterating through rows
foreach (DataRow row in customersTable.Rows)
{
    Console.WriteLine($"Contact: {row["ContactName"]}");
}

Modifying Data

You can add, edit, and delete rows in a DataTable.

Example: Modifying Data


DataTable customersTable = dataSet.Tables["Customers"];

// Adding a new row
DataRow newRow = customersTable.NewRow();
newRow["CustomerID"] = "NEWCUST";
newRow["CompanyName"] = "New Corp";
newRow["ContactName"] = "Jane Doe";
customersTable.Rows.Add(newRow);

// Modifying an existing row
DataRow rowToModify = customersTable.Rows[0];
rowToModify["ContactName"] = "Updated Contact";

// Deleting a row (e.g., by index or by finding it)
// customersTable.Rows.RemoveAt(rowIndex);

Accepting and Rejecting Changes

When you make changes to a DataSet, these changes are tracked. You can then either AcceptChanges() to make the current state the original state, or RejectChanges() to revert to the last accepted state.

Important Note

When using DataAdapters to update a data source, the adapter typically only sends rows that have been added, modified, or deleted (based on their RowState).

RowState Property

Each DataRow has a RowState property that indicates its current status:

Unchanged: The row has not been modified since it was loaded or last accepted.
Added: The row has been added to the table.
Modified: The row has been changed since it was loaded.
Deleted: The row has been deleted from the table.

Conclusion

DataSets are powerful objects for managing data in memory, especially in disconnected applications. Understanding their structure, population, and modification capabilities is crucial for effective ADO.NET development.

ADO.NET Docs