ADO.NET Data Collections - MSDN Documentation

Understanding Data Collections in ADO.NET

ADO.NET provides a rich set of classes for managing data in your applications. A core component of this is the ability to efficiently store and manipulate collections of data. These collections are fundamental for working with datasets, data tables, and individual data rows.

This section delves into the primary data collection types available in ADO.NET, their properties, methods, and how to effectively utilize them for data retrieval, manipulation, and storage.

Key Data Collection Classes

DataTable

Represents an in-memory cache of data. It is the primary object used to store a single table of data, consisting of rows and columns.

System.Data.DataTable

Key features:

Defines schema through Columns and Constraints.
Holds Rows of data.
Supports data manipulation (Add, Edit, Delete).
Can be filtered and sorted.

                        
// Example: Creating and populating a DataTable
var dataTable = new DataTable("Customers");
dataTable.Columns.Add("CustomerID", typeof(int));
dataTable.Columns.Add("CustomerName", typeof(string));

dataTable.Rows.Add(1, "Alfreds Futterkiste");
dataTable.Rows.Add(2, "Ana Trujillo Emparedados y helados");
dataTable.Rows.Add(3, "Antonio Moreno Taquería");

Console.WriteLine($"Table '{dataTable.TableName}' created with {dataTable.Rows.Count} rows.");
                        
                    

DataRow

Represents a single row of data within a DataTable. It provides access to the values in each column for that specific row.

System.Data.DataRow

Key features:

Access column values by index or name.
Track row state (Added, Modified, Deleted, Unchanged).
Commit or reject changes.

                        
// Example: Accessing DataRow values
if (dataTable.Rows.Count > 0)
{
    DataRow firstRow = dataTable.Rows[0];
    int customerId = (int)firstRow["CustomerID"];
    string customerName = firstRow["CustomerName"].ToString();
    Console.WriteLine($"First customer: ID={customerId}, Name={customerName}");
}
                        
                    

DataColumn

Represents a column in a DataTable. It defines the schema for a particular data field, including its name, data type, and constraints.

System.Data.DataColumn

Key features:

Defines column name, data type, and default value.
Supports primary keys, unique constraints, and foreign key constraints.
Can have auto-increment properties.

DataSet

Represents a complete set of data, including zero or more DataTable objects. It's used to hold related tables and their relationships.

System.Data.DataSet

Key features:

Collection of DataTables.
Supports relationships between tables (DataRelations).
Can be strongly typed.

                        
// Example: Creating a DataSet with multiple DataTables
var dataSet = new DataSet("CompanyData");

// Add the Customers DataTable (already created above)
dataSet.Tables.Add(dataTable);

// Create and add an Orders DataTable
var ordersTable = new DataTable("Orders");
ordersTable.Columns.Add("OrderID", typeof(int));
ordersTable.Columns.Add("CustomerID", typeof(int));
ordersTable.Columns.Add("OrderDate", typeof(DateTime));
dataSet.Tables.Add(ordersTable);

// Add a relationship
dataSet.Relations.Add("CustOrders", dataSet.Tables["Customers"].Columns["CustomerID"], dataSet.Tables["Orders"].Columns["CustomerID"]);

Console.WriteLine($"DataSet '{dataSet.DataSetName}' created with {dataSet.Tables.Count} tables.");
                        
                    

Working with DataRow States

Each DataRow maintains a state that indicates its current status. This is crucial for managing changes and synchronizing data with a database.

Added: The row has been added to the table.
Modified: The row's data has been changed since it was loaded or accepted.
Deleted: The row has been marked for deletion.
Unchanged: The row has not been modified since it was loaded or accepted.

You can access the row state using the RowState property and change it using methods like AcceptChanges() and RejectChanges().

Data Views

A DataView provides a dynamic, sorted, and filtered view of a DataTable. It does not contain data itself but rather acts as a flexible interface to the underlying data.

Key features:

Sorting data by one or more columns.
Filtering data based on specified criteria.
Maintaining current row pointers.
Efficient for UI binding.

                    
// Example: Creating a DataView to filter customers
DataView view = new DataView(dataTable);
view.RowFilter = "CustomerID > 1";
view.Sort = "CustomerName ASC";

Console.WriteLine("Customers with ID > 1 (sorted by name):");
foreach (DataRowView rowView in view)
{
    Console.WriteLine($"- {rowView["CustomerName"]}");
}
                    
                