DataColumn and DataRow in ADO.NET

The DataColumn and DataRow classes are fundamental to ADO.NET's System.Data namespace, forming the building blocks of a DataTable. They represent the columns and rows of a table, respectively, providing a structured way to manage in-memory relational data.

The DataColumn Class

A DataColumn object defines the schema of a column within a DataTable. It specifies properties such as the column's name, data type, whether it can be null, and its default value. Each DataColumn belongs to a DataTable.

Key Properties of DataColumn:

ColumnName: A string that identifies the column. This name must be unique within the DataTable.
DataType: A System.Type object specifying the data type of the values stored in the column (e.g., typeof(int), typeof(string), typeof(DateTime)).
AllowDBNull: A boolean indicating whether the column can contain null values.
Unique: A boolean indicating whether all values in the column must be unique.
MaxLength: The maximum length for string-based data types.
DefaultValue: The default value for the column if no value is explicitly provided for a new row.
AutoIncrement: A boolean indicating whether the column automatically generates sequential values.

Creating and Adding a DataColumn:


using System;
using System.Data;

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

// Create DataColumns
DataColumn idColumn = new DataColumn();
idColumn.DataType = typeof(int);
idColumn.ColumnName = "CustomerID";
idColumn.Unique = true;
idColumn.AutoIncrement = true;
idColumn.AutoIncrementSeed = 1;
idColumn.AutoIncrementStep = 1;

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

DataColumn orderDateColumn = new DataColumn("OrderDate", typeof(DateTime));
orderDateColumn.AllowDBNull = true;

// Add columns to the DataTable
dataTable.Columns.Add(idColumn);
dataTable.Columns.Add(nameColumn);
dataTable.Columns.Add(orderDateColumn);

Console.WriteLine($"DataTable '{dataTable.TableName}' created with columns:");
foreach (DataColumn col in dataTable.Columns)
{
    Console.WriteLine($"- {col.ColumnName} ({col.DataType.Name})");
}

The DataRow Class

A DataRow object represents a single record or row of data within a DataTable. Each DataRow holds values for each of the DataColumns defined in its associated DataTable.

Key Features of DataRow:

Accessing Values: Values can be accessed using the column name (as a string or DataColumn object) or by column index.
Row State: A DataRow has a state (e.g., Added, Modified, Deleted, Detached) indicating its status within the DataTable.
Row Version: For concurrency control and auditing, a DataRow can have different versions (Current and Original).

Creating and Populating DataRow:


// Assuming dataTable is already created as above

// Create a new DataRow
DataRow newRow = dataTable.NewRow();

// Populate the row (use column names)
newRow["CustomerName"] = "Alice Smith";
newRow["OrderDate"] = DateTime.Now;

// Add the row to the DataTable
dataTable.Rows.Add(newRow);

// Create another row using column index
DataRow anotherRow = dataTable.NewRow();
anotherRow[1] = "Bob Johnson"; // Index 1 for CustomerName
anotherRow[2] = new DateTime(2023, 10, 26); // Index 2 for OrderDate
dataTable.Rows.Add(anotherRow);

Console.WriteLine($"\nData in '{dataTable.TableName}':");
foreach (DataRow row in dataTable.Rows)
{
    Console.WriteLine($"ID: {row["CustomerID"]}, Name: {row["CustomerName"]}, Date: {row["OrderDate"]}");
}

// Example of modifying a row
if (dataTable.Rows.Count > 0)
{
    DataRow firstRow = dataTable.Rows[0];
    firstRow["CustomerName"] = "Alice Williams";
    firstRow.AcceptChanges(); // Mark changes as accepted
    Console.WriteLine("\nModified first row.");
}

Relationship Between DataColumn and DataRow

The DataColumn objects define the structure and constraints for the DataRow objects. A DataTable acts as the container, holding a collection of DataColumns (its schema) and a collection of DataRows (its data).

When you create a DataRow using DataTable.NewRow(), it automatically conforms to the schema defined by the DataTable's DataColumns. Attempting to assign a value of an incorrect type or violating constraints (like uniqueness or non-nullability) will result in an exception.

Common Operations:

Adding Rows: Use DataTable.Rows.Add(DataRow).
Retrieving Rows: Iterate through DataTable.Rows or use methods like Find() or Select().
Updating Rows: Access a specific DataRow and modify its column values.
Deleting Rows: Use DataRow.Delete() or DataTable.Rows.Remove(DataRow).
Accepting/Rejecting Changes: Use DataTable.AcceptChanges() or DataTable.RejectChanges() to manage the state of rows after modifications.

Understanding DataColumn and DataRow is crucial for effective data manipulation within ADO.NET applications, enabling robust and efficient handling of data retrieved from or destined for various data sources.