ADO.NET DataTables

The DataTable class is a fundamental component of ADO.NET. It represents an in-memory collection of data organized into rows and columns, similar to a table in a relational database. DataTable objects are often used to store and manipulate data retrieved from a data source, such as a SQL Server database, or to represent data that is constructed entirely in memory.

Key Features of DataTable

• Schema Definition: You can define the structure of a DataTable by adding DataColumn objects to its Columns collection. Each DataColumn specifies the name, data type, and other properties of a column.
• Row Management: Data is stored in DataRow objects, which are added to the Rows collection of a DataTable.
• Constraints: You can enforce data integrity by adding constraints, such as UniqueConstraint and ForeignKeyConstraint, to the Constraints collection.
• Relationships: You can define relationships between DataTable objects within a DataSet using DataRelation objects.
• Data View: The DefaultView property provides a DataView object, which allows you to sort, filter, and navigate the rows of the DataTable.
• XML Support: DataTable objects have built-in support for reading and writing data to and from XML.

Creating and Populating a DataTable

You can create a DataTable programmatically or by loading data from a data source. Here's a basic example of creating a DataTable in C#:

using System;
using System.Data;

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

        // Define columns
        DataColumn idColumn = new DataColumn("ProductID", typeof(int));
        idColumn.AutoIncrement = true; // Auto-increment the ID
        idColumn.AutoIncrementSeed = 1;
        idColumn.AutoIncrementStep = 1;
        idColumn.ReadOnly = true;

        DataColumn nameColumn = new DataColumn("ProductName", typeof(string));
        nameColumn.AllowDBNull = false; // Cannot be null

        DataColumn priceColumn = new DataColumn("Price", typeof(decimal));
        priceColumn.DefaultValue = 0.00m; // Default value

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

        // Add primary key constraint
        productsTable.PrimaryKey = new DataColumn[] { idColumn };

        // Add rows
        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);

        // Display data
        Console.WriteLine("Products Table:");
        foreach (DataRow row in productsTable.Rows)
        {
            Console.WriteLine($"ID: {row["ProductID"]}, Name: {row["ProductName"]}, Price: {row["Price"]}");
        }

        // Example of accessing data through DataView
        DataView dv = productsTable.DefaultView;
        dv.RowFilter = "Price > 100"; // Filter for prices greater than 100
        Console.WriteLine("\nProducts with Price > 100:");
        foreach (DataRowView rowView in dv)
        {
            Console.WriteLine($"ID: {rowView["ProductID"]}, Name: {rowView["ProductName"]}, Price: {rowView["Price"]}");
        }
    }
}
            

Key Properties and Methods

Property/Method	Description
Columns	Gets a DataColumnCollection that contains the table's column definitions.
Rows	Gets a DataRowCollection that contains the table's rows.
PrimaryKey	Gets or sets an array of DataColumn objects that represent the primary key of the table.
TableName	Gets or sets the name of the table.
DefaultView	Gets a DataView object that provides a sortable and filterable view of the table's data.
NewRow()	Creates a new DataRow with the same schema as the DataTable.
Load(IDataReader reader)	Populates a DataTable from a DataReader.
AcceptChanges()	Commits all changes made to the rows since the last call to AcceptChanges().
RejectChanges()	Rejects all changes made to the rows since the last call to AcceptChanges().