DataColumn Objects in ADO.NET - MSDN Documentation

Understanding DataColumn Objects in ADO.NET

The DataColumn object represents a column in a DataTable. It defines the schema for a column, including its name, data type, and constraints. DataColumn objects are fundamental to structuring data within ADO.NET.

Key Properties of DataColumn

Each DataColumn object exposes several important properties:

ColumnName: A unique string identifier for the column within its parent DataTable.
DataType: Specifies the .NET Framework type of the data stored in the column (e.g., System.Int32, System.String, System.DateTime).
AllowDBNull: A boolean value indicating whether the column can contain null values. Defaults to true.
Unique: A boolean value indicating whether values in this column must be unique. Defaults to false.
MaxLength: Specifies the maximum length for string-based data types.
DefaultValue: The default value for a new row in this column.
Expression: Allows you to define a filter, sort expression, or aggregate calculation for the column.
Table: A reference to the DataTable that contains this column.

Creating and Adding DataColumns

You can create DataColumn objects programmatically and add them to a DataTable. Here's a C# example:


using System;
using System.Data;

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

// Create DataColumn objects
DataColumn idColumn = new DataColumn("CustomerID", typeof(int));
idColumn.AutoIncrement = true;
idColumn.AutoIncrementSeed = 1;
idColumn.AutoIncrementStep = 1;
idColumn.AllowDBNull = false;
idColumn.Unique = true;

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

DataColumn emailColumn = new DataColumn("Email", typeof(string));
emailColumn.MaxLength = 255;

DataColumn createdDateColumn = new DataColumn("CreatedDate", typeof(DateTime));
createdDateColumn.DefaultValue = DateTime.Now;

// Add columns to the DataTable
customerTable.Columns.Add(idColumn);
customerTable.Columns.Add(nameColumn);
customerTable.Columns.Add(emailColumn);
customerTable.Columns.Add(createdDateColumn);

// You can also add columns with initial properties in one step
customerTable.Columns.Add("IsActive", typeof(bool), "true"); // Example with default value expression

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

Using Expressions

The Expression property is particularly powerful. It can be used for:

Filtering: To filter rows from a DataTable or DataView.
Sorting: To sort rows.
Calculations: To perform aggregate functions or compute values based on other columns.

Example: Calculating a Full Name

C# Example:


DataTable employeeTable = new DataTable("Employees");
employeeTable.Columns.Add("FirstName", typeof(string));
employeeTable.Columns.Add("LastName", typeof(string));

// Add a computed column for Full Name
DataColumn fullNameColumn = new DataColumn("FullName", typeof(string));
fullNameColumn.Expression = "FirstName + ' ' + LastName";
employeeTable.Columns.Add(fullNameColumn);

employeeTable.Rows.Add("John", "Doe");
employeeTable.Rows.Add("Jane", "Smith");

foreach (DataRow row in employeeTable.Rows)
{
    Console.WriteLine(row["FullName"]);
}

Example: Filtering Data

C# Example:


// Assuming customerTable from previous example exists and has data
customerTable.Rows.Add(1, "Alice Wonderland", "alice@example.com", DateTime.Now);
customerTable.Rows.Add(2, "Bob The Builder", "bob@example.com", DateTime.Now.AddDays(-5));

// Filter for customers whose name starts with 'A'
DataRow[] foundRows = customerTable.Select("CustomerName LIKE 'A%'");

Console.WriteLine("\nCustomers starting with 'A':");
foreach (DataRow row in foundRows)
{
    Console.WriteLine($"- {row["CustomerName"]}");
}

Constraints

DataColumn objects are involved in defining constraints, such as:

UniqueConstraint: Ensures that values in a column or set of columns are unique.
ForeignKeyConstraint: Establishes relationships between tables.
Unique property of DataColumn directly enforces uniqueness for that column.

By mastering the DataColumn object, you gain fine-grained control over the structure and integrity of your data within ADO.NET.