DataTable Class

The DataTable class represents an in-memory collection of data that can be accessed as a collection of rows and columns. It is a core component of ADO.NET, providing a structured way to manage tabular data independent of any data source.

Key Features of DataTable

Tabular Data Representation: Stores data in a table format with rows and columns.
Independent Data Management: Can be populated from various data sources or created programmatically.
Schema Definition: Allows definition of columns (DataColumn) with specific data types and constraints.
Row Management: Supports adding, deleting, and modifying rows (DataRow).
Data Views: Provides DataView objects for filtering, sorting, and navigating the data.
Relationship Support: Can define relationships between tables.
Constraints: Enforces data integrity through constraints like unique keys and foreign keys.

Creating and Populating a DataTable

You can create a DataTable programmatically or by using a DataAdapter to fill it from a data source.

Programmatic Creation:


using System.Data;

// Create a DataTable
DataTable productsTable = new DataTable("Products");

// Define columns
DataColumn idColumn = new DataColumn("ProductID", typeof(int));
idColumn.AutoIncrement = true;
idColumn.AutoIncrementSeed = 1;
idColumn.AutoIncrementStep = 1;
idColumn.AllowDBNull = false;

DataColumn nameColumn = new DataColumn("ProductName", typeof(string));
nameColumn.MaxLength = 100;
nameColumn.AllowDBNull = false;

DataColumn priceColumn = new DataColumn("Price", typeof(decimal));
priceColumn.AllowDBNull = false;
priceColumn.DefaultValue = 0;

// Add columns to the DataTable
productsTable.Columns.Add(idColumn);
productsTable.Columns.Add(nameColumn);
productsTable.Columns.Add(priceColumn);

// Add a primary key constraint
productsTable.PrimaryKey = new DataColumn[] { productsTable.Columns["ProductID"] };

// Add rows to the DataTable
DataRow row1 = productsTable.NewRow();
row1["ProductName"] = "Laptop";
row1["Price"] = 1200.50m;
productsTable.Rows.Add(row1);

DataRow row2 = productsTable.NewRow();
row2["ProductName"] = "Keyboard";
row2["Price"] = 75.00m;
productsTable.Rows.Add(row2);

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

Using DataAdapter to Populate:

A DataAdapter (e.g., SqlDataAdapter, OleDbDataAdapter) acts as a bridge between a DataSet (which can contain one or more DataTable objects) and a data source. The Fill method populates a DataTable.

Note: When using a DataAdapter, the DataTable's schema (columns, data types) is often inferred from the data source.

Accessing and Manipulating Data

You can access data within a DataTable using its Rows collection and iterating through DataRow objects.


// Accessing specific row and column
Console.WriteLine($"Product Name: {productsTable.Rows[0]["ProductName"]}");
Console.WriteLine($"Product Price: {productsTable.Rows[0]["Price"]}");

// Iterating through rows
Console.WriteLine("\nAll Products:");
foreach (DataRow row in productsTable.Rows)
{
    Console.WriteLine($"ID: {row["ProductID"]}, Name: {row["ProductName"]}, Price: {row["Price"]}");
}

// Updating a row
DataRow rowToUpdate = productsTable.Rows.Find(1); // Find row by primary key
if (rowToUpdate != null)
{
    rowToUpdate["Price"] = 1150.75m;
    Console.WriteLine($"Updated price for ProductID 1.");
}

// Deleting a row
DataRow rowToDelete = productsTable.Rows.Find(2);
if (rowToDelete != null)
{
    rowToDelete.Delete();
    Console.WriteLine($"Marked ProductID 2 for deletion.");
    // To actually remove deleted rows (e.g., before saving changes), you'd use AcceptChanges() or specific methods.
}

DataTableSchema and Constraints

The schema of a DataTable is defined by its DataColumn objects. Constraints ensure the integrity of the data within the table.

Common Constraints:

UniqueConstraint: Ensures that values in a specified column or set of columns are unique.
ForeignKeyConstraint: Establishes a parent-child relationship between two tables, enforcing referential integrity.
CheckConstraint: Enforces a rule on the values entered into a column.

The primary key is a special type of UniqueConstraint that identifies each row uniquely.

Using DataView

A DataView provides a dynamic view of the data in a DataTable. It allows you to filter, sort, and search the data without modifying the underlying DataTable.


// Create a DataView
DataView view = new DataView(productsTable);

// Set sorting
view.Sort = "Price DESC";

// Apply filtering
view.RowFilter = "Price > 100";

Console.WriteLine("\nProducts more expensive than 100 (sorted by Price DESC):");
foreach (DataRowView rowView in view)
{
    Console.WriteLine($"Name: {rowView["ProductName"]}, Price: {rowView["Price"]}");
}

Tip: DataView is highly efficient for presenting data in user interfaces like grids or lists, as it can handle large datasets with sophisticated sorting and filtering.

DataTable vs. DataSet

While DataTable manages a single table of data, a DataSet is a collection of zero or more DataTable objects, along with DataRelation and Constraint objects. Use DataTable when you need to work with a single table of data, and DataSet when you need to manage multiple related tables.

Summary

The DataTable class is a fundamental building block for data manipulation in ADO.NET. It provides a robust and flexible mechanism for working with in-memory tabular data, enabling efficient data access, management, and presentation.