DataTable Overview

The DataTable object is a core component of ADO.NET that represents an in-memory cache of data. It is highly versatile and can be used independently or as part of a DataSet. A DataTable is structured like a single database table, containing rows and columns.

Key Components of a DataTable

A DataTable is composed of the following key elements:

• Columns (DataColumn): Define the structure of the table, specifying the name, data type, and constraints for each column.
• Rows (DataRow): Represent individual records within the table, with values for each column.
• Constraints: Rules applied to the data within the table, such as primary keys and unique constraints, to maintain data integrity.
• Relations: Define relationships between the current DataTable and other DataTable objects, typically within a DataSet.

Creating and Populating a DataTable

You can create a DataTable programmatically and populate it with data. Here's a simple C# example:

using System;
using System.Data;

public class DataTableExample
{
    public static void Main(string[] args)
    {
        // Create a new DataTable
        DataTable dataTable = new DataTable("Customers");

        // Define columns
        DataColumn idColumn = new DataColumn("CustomerID", typeof(int));
        idColumn.PrimaryKey = true; // Set as primary key
        dataTable.Columns.Add(idColumn);

        dataTable.Columns.Add("FirstName", typeof(string));
        dataTable.Columns.Add("LastName", typeof(string));
        dataTable.Columns.Add("OrderCount", typeof(int));

        // Add rows
        DataRow row1 = dataTable.NewRow();
        row1["CustomerID"] = 1;
        row1["FirstName"] = "Alice";
        row1["LastName"] = "Smith";
        row1["OrderCount"] = 5;
        dataTable.Rows.Add(row1);

        DataRow row2 = dataTable.NewRow();
        row2["CustomerID"] = 2;
        row2["FirstName"] = "Bob";
        row2["LastName"] = "Johnson";
        row2["OrderCount"] = 12;
        dataTable.Rows.Add(row2);

        // Display data
        Console.WriteLine("DataTable: " + dataTable.TableName);
        foreach (DataRow row in dataTable.Rows)
        {
            Console.WriteLine($"ID: {row["CustomerID"]}, Name: {row["FirstName"]} {row["LastName"]}, Orders: {row["OrderCount"]}");
        }
    }
}
                

Working with DataRows

You can access and modify data within a DataRow using its index or column name. The RowState property indicates the current state of a row (e.g., Added, Modified, Deleted, Unchanged).

DataTable vs. DataSet

While a DataTable represents a single table, a DataSet can contain multiple DataTable objects, along with relationships between them. This makes DataSet suitable for holding complex, relational data retrieved from a database.

Common Operations

• Adding Rows: Use DataTable.NewRow() to create a new row and then add it to the Rows collection.
• Finding Rows: Use the Select() method with a filter expression or iterate through the Rows collection.
• Updating Rows: Access a row by index or using Find() and modify its column values.
• Deleting Rows: Call the Delete() method on a DataRow.

Example: Finding Rows

DataRow[] foundRows = dataTable.Select("OrderCount > 8");
foreach (DataRow dr in foundRows)
{
    Console.WriteLine($"Found: {dr["FirstName"]} {dr["LastName"]} with {dr["OrderCount"]} orders.");
}
                

Conclusion

The DataTable object is a fundamental data structure in ADO.NET for managing in-memory data. Understanding its components and how to manipulate it is crucial for effective data access and manipulation in .NET applications.