DataTable Class

The System.Data.DataTable class represents an in-memory collection of data that resembles a table in a relational database. It is a core component of ADO.NET and is used to store and manipulate data independently of any data source.

Understanding the DataTable Structure

A DataTable is composed of two main parts:

• Schema: Defined by DataColumn objects. Each column has a name, data type, and can have properties like allowing null values, being a primary key, etc.
• Data: Stored in DataRow objects. Each row contains values for each column defined in the schema.

DataColumns

DataColumn objects define the structure of the DataTable. You can create them programmatically or they can be inferred from a data source.

// Creating a DataTable and adding columns
DataTable customerTable = new DataTable("Customers");

DataColumn idColumn = new DataColumn("CustomerID", typeof(int));
idColumn.AutoIncrement = true;
idColumn.AutoIncrementSeed = 1;
idColumn.AutoIncrementStep = 1;
idColumn.AllowDBNull = false;

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

DataColumn emailColumn = new DataColumn("Email", typeof(string));

customerTable.Columns.Add(idColumn);
customerTable.Columns.Add(nameColumn);
customerTable.Columns.Add(emailColumn);

// Setting a primary key
customerTable.PrimaryKey = new DataColumn[] { customerTable.Columns["CustomerID"] };
            

DataRows

DataRow objects hold the actual data for each record. You create new rows and populate them with values.

// Adding rows to the DataTable
DataRow newRow = customerTable.NewRow();
newRow["CustomerName"] = "Alice Smith";
newRow["Email"] = "alice.smith@example.com";
customerTable.Rows.Add(newRow);

newRow = customerTable.NewRow();
newRow["CustomerName"] = "Bob Johnson";
newRow["Email"] = "bob.johnson@example.com";
customerTable.Rows.Add(newRow);
            

Working with Data in a DataTable

Retrieving Data

You can iterate through the rows of a DataTable to access the data.

// Iterating through rows
foreach (DataRow row in customerTable.Rows)
{
    Console.WriteLine($"ID: {row["CustomerID"]}, Name: {row["CustomerName"]}, Email: {row["Email"]}");
}
            

Finding Rows

The Select method allows you to filter rows using a filter expression.

// Finding rows with a specific name
DataRow[] foundRows = customerTable.Select("CustomerName = 'Alice Smith'");
if (foundRows.Length > 0)
{
    Console.WriteLine($"Found: {foundRows[0]["CustomerName"]}");
}
            

Updating, Deleting, and Adding Rows

DataTable supports standard data manipulation operations.

// Updating a row
DataRow rowToUpdate = customerTable.Rows.Find(1); // Assumes CustomerID = 1 exists
if (rowToUpdate != null)
{
    rowToUpdate["Email"] = "alice.s@newdomain.com";
    rowToUpdate.SetModified(); // Mark as modified for tracking
}

// Deleting a row
DataRow rowToDelete = customerTable.Rows.Find(2); // Assumes CustomerID = 2 exists
if (rowToDelete != null)
{
    rowToDelete.Delete(); // Mark for deletion
}

// Adding a new row (shown previously with NewRow() and Add())
            

DataTable vs. Dataset

A DataSet is a collection of one or more DataTable objects, along with DataRelation objects that define relationships between tables. A DataTable represents a single table.

Common Scenarios

• Caching data from a database to reduce round trips.
• Performing complex data manipulations offline.
• Working with XML data.
• Enabling data binding in UI elements (e.g., grids).