MSDN Documentation

Tables and Rows in ADO.NET

The DataTable class is a core component of ADO.NET, representing an in-memory table of data. It is part of the System.Data namespace and is designed to hold a collection of DataRow objects, along with a schema that defines the structure of the data.

The DataTable Class

A DataTable object can be used independently or as part of a DataSet. It provides a rich set of properties and methods for managing data, including:

• Columns: A collection of DataColumn objects that define the schema of the table.
• Rows: A collection of DataRow objects that contain the actual data.
• Constraints: A collection of rules (like primary keys and unique constraints) that ensure data integrity.
• PrimaryKey: An array of DataColumn objects that form the primary key for the table.
• Select(): A method to query the rows in the table using a filter expression and sort expression.
• AcceptChanges(): Commits all changes made to the rows since the last call to this method.
• RejectChanges(): Reverts all changes made to the rows since the last call to AcceptChanges().

The DataRow Class

Each row in a DataTable is represented by a DataRow object. This object holds the data for a single record and allows access to individual column values.

Key features of a DataRow include:

• ItemArray: An array containing the values of all columns in the row.
• Item[columnName] or Item[columnIndex]: Allows access to a specific column's value by name or index.
• RowState: Indicates the current state of the row (e.g., Added, Modified, Deleted, Unchanged).
• HasVersion(): Checks if a specific version of the row data exists.

Example: Creating and Populating a DataTable

Here's a C# example demonstrating how to create a simple DataTable, add columns, and then add some rows:

using System;
using System.Data;

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

        // Create DataColumns and add them to the DataTable
        DataColumn idColumn = new DataColumn("CustomerID", typeof(int));
        idColumn.AutoIncrement = true; // Automatically assign values
        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 the primary key
        customersTable.PrimaryKey = new DataColumn[] { customersTable.Columns["CustomerID"] };

        // Create DataRows and add them to the DataTable
        DataRow row1 = customersTable.NewRow();
        row1["FirstName"] = "John";
        row1["LastName"] = "Doe";
        row1["Email"] = "john.doe@example.com";
        customersTable.Rows.Add(row1);

        DataRow row2 = customersTable.NewRow();
        row2.SetField("FirstName", "Jane"); // Alternative way to set field
        row2["LastName"] = "Smith";
        row2["Email"] = "jane.smith@example.com";
        customersTable.Rows.Add(row2);

        // Add a third row directly
        customersTable.Rows.Add(null, "Peter", "Jones", "peter.jones@example.com");

        // Displaying the table data
        Console.WriteLine("--- Customer Data ---");
        foreach (DataRow row in customersTable.Rows)
        {
            Console.WriteLine($"ID: {row["CustomerID"]}, Name: {row["FirstName"]} {row["LastName"]}, Email: {row["Email"]}");
        }

        // Accessing a specific row
        Console.WriteLine("\n--- Accessing Row with ID 2 ---");
        DataRow specificRow = customersTable.Rows.Find(2); // Requires primary key to be set
        if (specificRow != null)
        {
            Console.WriteLine($"Found: {specificRow["FirstName"]} {specificRow["LastName"]}");
        }

        // Modifying a row
        Console.WriteLine("\n--- Modifying Row with ID 1 ---");
        DataRow rowToModify = customersTable.Rows.Find(1);
        if (rowToModify != null)
        {
            rowToModify["Email"] = "john.doe.updated@example.com";
            Console.WriteLine($"Updated Email for ID 1: {rowToModify["Email"]}");
        }

        // Deleting a row
        Console.WriteLine("\n--- Deleting Row with ID 3 ---");
        DataRow rowToDelete = customersTable.Rows.Find(3);
        if (rowToDelete != null)
        {
            rowToDelete.Delete();
            Console.WriteLine("Row with ID 3 marked for deletion.");
        }

        // Committing changes
        customersTable.AcceptChanges();
        Console.WriteLine("\n--- Data after AcceptChanges() ---");
        foreach (DataRow row in customersTable.Rows)
        {
            Console.WriteLine($"ID: {row["CustomerID"]}, Name: {row["FirstName"]} {row["LastName"]}, Email: {row["Email"]} (State: {row.RowState})");
        }
    }
}
            

Working with DataRow States

When data is manipulated, DataRow objects maintain a state to track changes. This state is crucial for operations like batch updates and conflict resolution.

You can filter rows based on their state using the Select() method:

// Get all modified rows
DataRow[] modifiedRows = customersTable.Select(null, null, DataViewRowState.ModifiedCurrent);

// Get all added rows
DataRow[] addedRows = customersTable.Select(null, null, DataViewRowState.Added);
            

Conclusion

DataTable and DataRow provide a powerful, flexible, and efficient way to manage data in memory within ADO.NET applications. Understanding their capabilities is fundamental to building robust data-driven applications.