What is a DataSet?

A DataSet is a collection of zero or more DataTable objects. Each DataTable represents a single table of data, similar to a table in a relational database. The DataSet can also contain a collection of DataRelation objects, which define relationships between tables, and Constraint objects, which enforce data integrity rules.

Key characteristics of a DataSet include:

• In-Memory Cache: It holds data in memory, enabling offline operations and reducing the need for constant database interaction.
• Relational Structure: It can store complex relational data, including primary keys, foreign keys, and unique constraints.
• Data Manipulation: Supports operations like adding, deleting, modifying, sorting, and filtering data.
• Schema Support: Can define and store the schema (structure) of the data, including column types, constraints, and relationships.
• XML Integration: Easily serialize and deserialize to and from XML, facilitating data exchange.

Core Components of a DataSet

A DataSet is comprised of several related objects:

DataTable

Represents a single table of data. It contains columns (DataColumn objects) defining the schema and rows (DataRow objects) holding the actual data.

DataColumn

Defines a column in a DataTable, including its data type, name, and constraints.

DataRow

Represents a single record or row in a DataTable. You access individual column values within a row.

DataRelation

Defines a relationship between two tables in the DataSet, typically based on matching primary and foreign key columns.

Constraint

Enforces data integrity rules, such as UniqueConstraint (ensures values in a column or set of columns are unique) and ForeignKeyConstraint (enforces referential integrity between tables).

Creating and Populating a DataSet

You can create a DataSet programmatically or populate it using a DataAdapter. When using a DataAdapter, the Fill method populates the DataSet with data from a data source.

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

public class DataSetExample
{
    public static void Main(string[] args)
    {
        string connectionString = "Server=myServer;Database=myDatabase;Integrated Security=True;";
        string query = "SELECT CustomerID, CompanyName, ContactName FROM Customers";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlDataAdapter adapter = new SqlDataAdapter(query, connection);
            DataSet customerDataSet = new DataSet();

            try
            {
                connection.Open();
                adapter.Fill(customerDataSet, "Customers"); // Fills the DataSet with data into a table named "Customers"

                Console.WriteLine("DataSet populated successfully!");

                // Accessing data
                DataTable customersTable = customerDataSet.Tables["Customers"];
                foreach (DataRow row in customersTable.Rows)
                {
                    Console.WriteLine($"ID: {row["CustomerID"]}, Name: {row["CompanyName"]}, Contact: {row["ContactName"]}");
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error: {ex.Message}");
            }
        }
    }
}
                        

Working with Data in a DataSet

Once populated, you can navigate and manipulate the data within the DataSet.

Accessing Tables and Rows

Tables are accessed via the Tables collection of the DataSet:

DataTable ordersTable = myDataSet.Tables["Orders"];
DataRow firstOrder = ordersTable.Rows[0];
string orderId = firstOrder["OrderID"].ToString();
                    

Filtering and Sorting

You can filter and sort data using DataView objects. A DataView provides a dynamic view of the data, allowing you to apply filters, sort orders, and group rows.

DataView view = new DataView(myDataSet.Tables["Products"]);
view.RowFilter = "UnitPrice > 50";
view.Sort = "ProductName ASC";

// Now iterate through the view
foreach (DataRowView rowView in view)
{
    Console.WriteLine($"{rowView["ProductName"]} - ${rowView["UnitPrice"]}");
}
                    

Modifying Data

You can directly modify DataRow objects. Changes are tracked by the DataSet, allowing for later synchronization with the data source.

DataRow rowToUpdate = myDataSet.Tables["Products"].Rows.Find("CHAI"); // Assuming "ProductID" is the primary key
if (rowToUpdate != null)
{
    rowToUpdate["UnitsInStock"] = 50;
    rowToUpdate["IsDiscontinued"] = true;
}
                    

Adding and Deleting Rows

// Adding a new row
DataTable dt = myDataSet.Tables["Categories"];
DataRow newRow = dt.NewRow();
newRow["CategoryName"] = "New Gadgets";
newRow["Description"] = "Latest electronic gadgets.";
dt.Rows.Add(newRow);

// Deleting a row
DataRow rowToDelete = dt.Rows[0];
rowToDelete.Delete(); // Marks the row for deletion
                    

Data Relations

DataRelation objects are crucial for representing the connections between tables. This allows you to easily navigate from a parent row to its child rows, or vice-versa.

// Assuming customersDataSet contains "Customers" and "Orders" tables
DataTable customersTable = customersDataSet.Tables["Customers"];
DataTable ordersTable = customersDataSet.Tables["Orders"];

// Define columns for the relation
DataColumn parentColumn = customersTable.Columns["CustomerID"];
DataColumn childColumn = ordersTable.Columns["CustomerID"];

// Create the relation
DataRelation relation = new DataRelation("CustomerOrders", parentColumn, childColumn);
customersDataSet.Relations.Add(relation);

// Now you can navigate:
// Get the first customer row
DataRow customerRow = customersDataSet.Tables["Customers"].Rows[0];

// Get the child orders for this customer
DataRow[] orderRows = customerRow.GetChildRows("CustomerOrders");

foreach (DataRow orderRow in orderRows)
{
    Console.WriteLine($"  Order ID: {orderRow["OrderID"]}, Order Date: {orderRow["OrderDate"]}");
}
                        

Accepting and Rejecting Changes

The DataSet tracks changes made to rows. You can use methods like AcceptChanges() to mark all changes as resolved, or RejectChanges() to revert all pending changes.

// After making modifications...
myDataSet.AcceptChanges(); // Commits all changes
// or
myDataSet.RejectChanges(); // Reverts all changes
                    

When to Use DataSet

DataSet is highly versatile but can have performance implications due to its in-memory nature. Consider using it when:

• You need to work with data offline or disconnected from the data source.
• You need to manipulate data from multiple, related tables in a single object.
• You require rich data manipulation capabilities like sorting, filtering, and grouping.
• You need to compare different versions of data (e.g., before and after changes).
• Data volume is manageable within the available memory.