ADO.NET DataTable - MSDN Documentation

DataTable Class

The DataTable class represents a table of data in memory. It is a core component of ADO.NET, providing a versatile way to work with data without a constant connection to a database. A DataTable can be used independently or as part of a DataSet.

Key Features and Concepts

In-Memory Data Representation: Stores data in a structured format similar to a database table with rows and columns.
Schema Definition: Allows you to define columns (DataColumn objects) with specific data types, constraints, and properties.
Row Management: Supports adding, deleting, updating, and retrieving rows (DataRow objects).
Data Binding: Can be easily bound to UI controls like grids and list boxes.
Data Relations: Can be related to other DataTable objects within a DataSet to represent complex data structures.
Constraints: Supports primary keys, unique constraints, and foreign key constraints to maintain data integrity.
Data Views: DataView objects can be created from a DataTable to filter, sort, and navigate data.

Common Operations

Creating a DataTable and Adding Columns


using System.Data;

// Create a new DataTable
DataTable customersTable = new DataTable("Customers");

// Add columns
DataColumn idColumn = new DataColumn("CustomerID", typeof(int));
idColumn.AutoIncrement = true;
idColumn.AutoIncrementSeed = 1;
idColumn.AutoIncrementStep = 1;
customersTable.Columns.Add(idColumn);

customersTable.Columns.Add("FirstName", typeof(string));
customersTable.Columns.Add("LastName", typeof(string));
customersTable.Columns.Add("Email", typeof(string));

// Set primary key
customersTable.PrimaryKey = new DataColumn[] { customersTable.Columns["CustomerID"] };

Adding Rows to a DataTable


// Create a new row
DataRow newRow = customersTable.NewRow();
newRow["FirstName"] = "John";
newRow["LastName"] = "Doe";
newRow["Email"] = "john.doe@example.com";
customersTable.Rows.Add(newRow);

// Add another row directly
customersTable.Rows.Add(null, "Jane", "Smith", "jane.smith@example.com");

Accessing and Iterating Through Rows


Console.WriteLine("Customers:");
foreach (DataRow row in customersTable.Rows)
{
    Console.WriteLine($"ID: {row["CustomerID"]}, Name: {row["FirstName"]} {row["LastName"]}, Email: {row["Email"]}");
}

Filtering Data using DataView


// Create a DataView for filtering
DataView dataView = new DataView(customersTable);

// Filter for customers whose last name starts with "S"
dataView.RowFilter = "LastName LIKE 'S%'";

Console.WriteLine("\nCustomers with last name starting with 'S':");
foreach (DataRowView rowView in dataView)
{
    Console.WriteLine($"ID: {rowView["CustomerID"]}, Name: {rowView["FirstName"]} {rowView["LastName"]}");
}

Updating and Deleting Rows


// Find a row by primary key
DataRow rowToUpdate = customersTable.Rows.Find(1);
if (rowToUpdate != null)
{
    rowToUpdate["Email"] = "john.doe.updated@example.com";
    rowToUpdate.AcceptChanges(); // Persist the change
}

// Find a row to delete
DataRow rowToDelete = customersTable.Rows.Find(2);
if (rowToDelete != null)
{
    rowToDelete.Delete(); // Mark for deletion
    customersTable.AcceptChanges(); // Commit the deletion
}