MSDN Documentation

Data Columns in ADO.NET

A DataColumn object represents a column in a DataTable. It defines the schema of the column, including its name, data type, and constraints. Understanding and manipulating DataColumn objects is fundamental to working with data in ADO.NET.

Creating DataColumns

You can create a DataColumn using its constructor. The most common constructor takes the column name and its data type as arguments.

using System.Data;

// ...

DataTable myTable = new DataTable("Customers");

DataColumn idColumn = new DataColumn("CustomerID", typeof(int));
DataColumn nameColumn = new DataColumn("CustomerName", typeof(string));
DataColumn orderDateColumn = new DataColumn("OrderDate", typeof(DateTime));

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

Properties of DataColumn

DataColumn objects have numerous properties that define their behavior and characteristics:

Constraints

Columns can be involved in constraints to enforce data integrity:

• Primary Key: A DataColumn can be designated as part of the primary key for a DataTable. This ensures uniqueness and provides a way to quickly access rows.
• Unique Constraint: Ensures that all values within a column are unique, even if it's not the primary key.
• Foreign Key Constraint: Relates a column in one table to a column in another table, enforcing referential integrity.

Setting a Primary Key Column

// Assuming 'myTable' and 'idColumn' are already defined
myTable.PrimaryKey = new DataColumn[] { idColumn };
            

Expressions

The Expression property allows you to define calculated columns or filter rows based on conditions. This is a powerful feature for data manipulation within the DataTable.

Example: Creating a Calculated Column

DataTable productsTable = new DataTable("Products");
productsTable.Columns.Add("Price", typeof(decimal));
productsTable.Columns.Add("TaxRate", typeof(decimal));
productsTable.Columns.Add("TotalPrice", typeof(decimal));

// Set the Expression for the TotalPrice column
productsTable.Columns["TotalPrice"].Expression = "Price * (1 + TaxRate)";

// Add some data
productsTable.Rows.Add(100.00m, 0.05m);
productsTable.Rows.Add(250.50m, 0.08m);

// Accessing the calculated value
Console.WriteLine(productsTable.Rows[0]["TotalPrice"]); // Output: 105.00
            

Working with Data Types

It's crucial to choose appropriate DataType values for your columns. ADO.NET supports a wide range of .NET Framework types. Using the correct type ensures data integrity and efficient storage.

You can retrieve the DataType of a column using:

Type columnDataType = myTable.Columns["CustomerID"].DataType;
Console.WriteLine(columnDataType.Name); // Output: Int32
            

Column Modification and Deletion

You can modify existing column properties or remove columns from a DataTable.

// Modify a property
myTable.Columns["CustomerName"].MaxLength = 100;

// Remove a column
if (myTable.Columns.Contains("OrderDate"))
{
    myTable.Columns.Remove("OrderDate");
}