Column Methods

Column methods provide comprehensive functionality for managing table columns across all supported database providers. These methods handle column creation, modification, introspection, and deletion operations.

Column Existence Checking
- DoesColumnExistAsync - Check if column exists in table
Column Creation
- CreateColumnIfNotExistsAsync (DmColumn) - Add column from DmColumn model
- CreateColumnIfNotExistsAsync (Parameters) - Create column with individual parameters
Column Discovery
- GetColumnNamesAsync - Get list of column names with filtering
- GetColumnsAsync - Get complete column models with structure
- GetColumnAsync - Get single column model by name
Column Modification
- RenameColumnIfExistsAsync - Rename existing column
- AlterColumnIfExistsAsync - Change a column's type and nullability
Column Deletion
- DropColumnIfExistsAsync - Drop column permanently

Column Existence Checking

DoesColumnExistAsync

Check if a column exists in a specific table.

csharp

// Check if column exists
bool exists = await connection.DoesColumnExistAsync("app", "app_employees", "title");

if (exists)
{
    Console.WriteLine("Column 'title' already exists");
}
else
{
    Console.WriteLine("Column 'title' does not exist");
    await connection.CreateColumnIfNotExistsAsync(titleColumn);
}


// With transaction and cancellation
using var transaction = connection.BeginTransaction();
bool exists = await connection.DoesColumnExistAsync(
    "app", 
    "app_employees", 
    "title",
    tx: transaction,
    cancellationToken: cancellationToken
);

Parameters:

schemaName - Schema containing the table
tableName - Name of the table containing the column
columnName - Name of the column to check
tx (optional) - Database transaction
cancellationToken (optional) - Cancellation token

Returns: bool - true if column exists, false otherwise

Column Creation

CreateColumnIfNotExistsAsync (DmColumn)

Add a column only if it doesn't already exist using a DmColumn model.

csharp

// Create column if it doesn't exist
bool created = await connection.CreateColumnIfNotExistsAsync(column);

if (created)
{
    Console.WriteLine("Column 'Title' was created");
}
else
{
    Console.WriteLine("Column 'Title' already existed");
}

Parameters:

column - DmColumn model defining the column structure (includes SchemaName and TableName)
tx (optional) - Database transaction
cancellationToken (optional) - Cancellation token

Returns: bool - true if column was created, false if it already existed

CreateColumnIfNotExistsAsync (Individual Parameters)

Create a column using individual parameters for maximum flexibility and convenience.

csharp

// Simple column with basic parameters
bool created = await connection.CreateColumnIfNotExistsAsync(
    schemaName: "app",
    tableName: "app_employees", 
    columnName: "manager_id",
    dotnetType: typeof(Guid),
    isNullable: false
);

// Advanced column with all options
bool created = await connection.CreateColumnIfNotExistsAsync(
    schemaName: "app",
    tableName: "app_employees",
    columnName: "manager_id",
    dotnetType: typeof(Guid),
    providerDataType: null, // Let DapperMatic determine optimal SQL type
    length: null,
    precision: null,
    scale: null,
    checkExpression: null,
    defaultExpression: "NEWID()", // SQL Server default
    isNullable: false,
    isPrimaryKey: false,
    isAutoIncrement: false,
    isUnique: false,
    isIndexed: true, // Create index on this column
    isForeignKey: true,
    referencedTableName: "app_managers",
    referencedColumnName: "id",
    onDelete: DmForeignKeyAction.Cascade,
    onUpdate: DmForeignKeyAction.NoAction,
    tx: transaction,
    cancellationToken: cancellationToken
);

// Decimal column with precision and scale
bool salaryCreated = await connection.CreateColumnIfNotExistsAsync(
    "hr", 
    "employees", 
    "salary",
    typeof(decimal),
    precision: 10,
    scale: 2,
    isNullable: true,
    defaultExpression: "0.00"
);

// String column with length constraint
bool emailCreated = await connection.CreateColumnIfNotExistsAsync(
    "app", 
    "users", 
    "email",
    typeof(string),
    length: 255,
    isNullable: false,
    isUnique: true,
    checkExpression: "email LIKE '%@%'" // Basic email validation
);

Parameters:

schemaName - Schema containing the table
tableName - Name of the table to add column to
columnName - Name of the column to create
dotnetType - .NET type for the column
providerDataType (optional) - Provider-specific SQL type (auto-detected if null)
length (optional) - Maximum length for string/binary types
precision (optional) - Precision for decimal types
scale (optional) - Scale for decimal types
checkExpression (optional) - Check constraint expression
defaultExpression (optional) - Default value expression
isNullable (optional) - Whether column allows NULL values (default: true)
isPrimaryKey (optional) - Whether column is part of primary key (default: false)
isAutoIncrement (optional) - Whether column auto-increments (default: false)
isUnique (optional) - Whether column has unique constraint (default: false)
isUnicode (optional) - Whether column supports unicode characters (default: false)
isIndexed (optional) - Whether to create index on column (default: false)
isForeignKey (optional) - Whether column is a foreign key (default: false)
referencedTableName (optional) - Referenced table for foreign key
referencedColumnName (optional) - Referenced column for foreign key
onDelete (optional) - Foreign key delete action
onUpdate (optional) - Foreign key update action
tx (optional) - Database transaction
cancellationToken (optional) - Cancellation token

Returns: bool - true if column was created, false if it already existed

Column Discovery

GetColumnNamesAsync

Retrieve a list of column names from a table, with optional filtering.

csharp

// Get all column names
List<string> allColumns = await connection.GetColumnNamesAsync("app", "app_employees");
foreach (string columnName in allColumns)
{
    Console.WriteLine($"Found column: {columnName}");
}

// Get column names with wildcard filter
List<string> titleColumns = await connection.GetColumnNamesAsync("app", "app_employees", columnNameFilter: "*title*");
// Finds: title, job_title, title_code, etc.

// Get columns with pattern matching
List<string> idColumns = await connection.GetColumnNamesAsync("app", "app_employees", columnNameFilter: "*_id");
// Finds: employee_id, manager_id, department_id, etc.

Parameters:

schemaName - Schema containing the table
tableName - Name of the table to search
columnNameFilter (optional) - Wildcard pattern to filter column names (* = any characters, ? = single character)
tx (optional) - Database transaction
cancellationToken (optional) - Cancellation token

Returns: List<string> - List of matching column names

GetColumnsAsync

Retrieve complete DmColumn models for table columns.

csharp

// Get all columns with full structure information
List<DmColumn> columns = await connection.GetColumnsAsync("app", "app_employees");

foreach (var column in columns)
{
    Console.WriteLine($"Column: {column.ColumnName}");
    Console.WriteLine($"  Type: {column.DotnetType?.Name}");
    Console.WriteLine($"  Nullable: {column.IsNullable}");
    Console.WriteLine($"  Max Length: {column.Length?.ToString() ?? "N/A"}");
    Console.WriteLine($"  Auto Increment: {column.IsAutoIncrement}");
    Console.WriteLine($"  Default: {column.DefaultExpression ?? "None"}");
}

// Get specific columns with pattern
List<DmColumn> auditColumns = await connection.GetColumnsAsync("app", "app_employees", columnNameFilter: "*_date");

// With transaction
using var transaction = connection.BeginTransaction();
List<DmColumn> columns = await connection.GetColumnsAsync("app", "app_employees", tx: transaction);

Parameters:

schemaName - Schema containing the table
tableName - Name of the table to search
columnNameFilter (optional) - Wildcard pattern to filter column names
tx (optional) - Database transaction
cancellationToken (optional) - Cancellation token

Returns: List<DmColumn> - List of complete DmColumn models

GetColumnAsync

Retrieve a single DmColumn model for a specific column.

csharp

// Get specific column structure
DmColumn? column = await connection.GetColumnAsync("app", "app_employees", "email");

if (column != null)
{
    Console.WriteLine($"Column '{column.ColumnName}' details:");
    Console.WriteLine($"  Type: {column.DotnetType?.Name}");
    Console.WriteLine($"  Max Length: {column.Length}");
    Console.WriteLine($"  Nullable: {column.IsNullable}");
    Console.WriteLine($"  Unique: {column.IsUnique}");
    
    if (!string.IsNullOrEmpty(column.DefaultExpression))
    {
        Console.WriteLine($"  Default: {column.DefaultExpression}");
    }
    
    if (!string.IsNullOrEmpty(column.CheckExpression))
    {
        Console.WriteLine($"  Check: {column.CheckExpression}");
    }
}
else
{
    Console.WriteLine("Column not found");
}

Parameters:

schemaName - Schema containing the table
tableName - Name of the table containing the column
columnName - Name of the column to retrieve
tx (optional) - Database transaction
cancellationToken (optional) - Cancellation token

Returns: DmColumn? - Complete column model, or null if column doesn't exist

Column Modification

RenameColumnIfExistsAsync

Rename an existing column.

csharp

// Rename column only if it exists
bool renamed = await connection.RenameColumnIfExistsAsync("app", "app_employees", "title", "job_title");

if (renamed)
{
    Console.WriteLine("Column was renamed successfully");
}
else
{
    Console.WriteLine("Column did not exist");
}

// With transaction
using var transaction = connection.BeginTransaction();
bool renamed = await connection.RenameColumnIfExistsAsync(
    "app", 
    "app_employees", 
    "old_column_name", 
    "new_column_name", 
    tx: transaction
);

Parameters:

schemaName - Schema containing the table
tableName - Name of the table containing the column
columnName - Current name of the column
newColumnName - New name for the column
tx (optional) - Database transaction
cancellationToken (optional) - Cancellation token

Returns: bool - true if column was renamed, false if it didn't exist

AlterColumnIfExistsAsync

Change an existing column's data type and nullability. Everything else about the column is kept: its collation, default and comment, and on MySQL and MariaDB its generated expression.

csharp

// Lengthen a column and allow nulls
bool altered = await connection.AlterColumnIfExistsAsync(
    "app", "app_employees", "title", "varchar(200)", isNullable: true);

// Or describe the column and let DapperMatic render the type, as CREATE TABLE would
bool altered = await connection.AlterColumnIfExistsAsync(
    new DmColumn("app", "app_employees", "salary", typeof(decimal), precision: 12, scale: 2));

// Or bring a column back in line with the model class it is mapped from
bool altered = await connection.AlterColumnIfExistsAsync<Employee>(e => e.Salary);

Parameters:

schemaName - Schema containing the table
tableName - Name of the table containing the column
columnName - Name of the column
sqlType - The new data type, including any length, precision and scale, for example varchar(200)
isNullable - Whether the column allows nulls afterwards
tx (optional) - Database transaction
cancellationToken (optional) - Cancellation token

Returns: bool - true if the column exists and now has the given type and nullability, false if the table or column doesn't exist

Existing values are not checked

Shortening a column or making it NOT NULL fails on data that doesn't fit. Some providers also refuse to alter a column that something depends on: PostgreSQL won't change the type of a column a view uses, and SQL Server won't change the precision of an indexed decimal column. For changes that can't lose data, use reconciliation with safe alterations, which applies only those and reports the rest.

How each provider keeps the rest of the column:

Provider	Statement	What is kept, and how
SQL Server	`ALTER COLUMN`	The collation is restated, because `ALTER COLUMN` would otherwise reset it to the database default. Defaults survive on their own.
PostgreSQL	`ALTER COLUMN ... TYPE`, `SET` / `DROP NOT NULL`	Only the clauses that change are issued, so changing nullability alone works even when a view uses the column. A non-default collation is restated.
MySQL / MariaDB	`MODIFY COLUMN`	The full definition is taken from `SHOW CREATE TABLE` with only the type and nullability replaced, so the character set, collation, default, `ON UPDATE`, comment and generated expression are kept.
SQLite	Table recreation	See SQLite.

Column Deletion

DropColumnIfExistsAsync

Remove a column from a table permanently.

csharp

// Drop column only if it exists
bool dropped = await connection.DropColumnIfExistsAsync("app", "app_employees", "old_column");

if (dropped)
{
    Console.WriteLine("Column was dropped successfully");
}
else
{
    Console.WriteLine("Column did not exist");
}

// Batch drop multiple columns
var columnsToRemove = new[] { "temp_field", "legacy_status", "unused_flag" };
foreach (var columnName in columnsToRemove)
{
    bool wasDropped = await connection.DropColumnIfExistsAsync("app", "app_employees", columnName);
    Console.WriteLine($"Column '{columnName}': {(wasDropped ? "Dropped" : "Not found")}");
}

Parameters:

schemaName - Schema containing the table
tableName - Name of the table containing the column
columnName - Name of the column to drop
tx (optional) - Database transaction
cancellationToken (optional) - Cancellation token

Returns: bool - true if column was dropped, false if it didn't exist

Practical Examples

Column Migration Pattern

csharp

public async Task MigrateColumnDataTypeAsync(IDbConnection connection, 
    string schema, string tableName, string columnName, Type newType)
{
    using var transaction = connection.BeginTransaction();
    try
    {
        // Get current column structure
        var currentColumn = await connection.GetColumnAsync(schema, tableName, columnName, tx: transaction);
        if (currentColumn == null)
        {
            throw new InvalidOperationException($"Column {columnName} does not exist");
        }
        
        // Create a temporary column with the new type, allowing nulls while the data is copied
        var tempColumnName = $"{columnName}_temp_{DateTime.UtcNow:yyyyMMddHHmmss}";
        var tempColumn = new DmColumn(schema, tableName, tempColumnName, newType, isNullable: true);

        await connection.CreateColumnIfNotExistsAsync(tempColumn, tx: transaction);

        // Copy and convert data (implementation depends on data types)
        await CopyAndConvertColumnDataAsync(connection, schema, tableName,
            columnName, tempColumnName, currentColumn.DotnetType, newType, transaction);

        // Drop old column
        await connection.DropColumnIfExistsAsync(schema, tableName, columnName, tx: transaction);

        // Rename temp column to original name
        await connection.RenameColumnIfExistsAsync(schema, tableName, tempColumnName, columnName, tx: transaction);

        // Restore the original nullability now that every row has a value
        await connection.AlterColumnIfExistsAsync(
            new DmColumn(schema, tableName, columnName, newType, isNullable: currentColumn.IsNullable),
            tx: transaction);

        transaction.Commit();
    }
    catch
    {
        transaction.Rollback();
        throw;
    }
}

Bulk Column Operations

csharp

public async Task AddAuditColumnsAsync(IDbConnection connection, string schema, string tableName)
{
    var auditColumns = new[]
    {
        new DmColumn("CreatedAt", typeof(DateTime))
        {
            SchemaName = schema,
            TableName = tableName,
            IsNullable = false,
            DefaultExpression = "GETUTCDATE()"
        },
        new DmColumn("CreatedBy", typeof(string))
        {
            SchemaName = schema,
            TableName = tableName,
            Length = 100,
            IsNullable = false,
            DefaultExpression = "SYSTEM_USER"
        },
        new DmColumn("ModifiedAt", typeof(DateTime))
        {
            SchemaName = schema,
            TableName = tableName,
            IsNullable = true
        },
        new DmColumn("ModifiedBy", typeof(string))
        {
            SchemaName = schema,
            TableName = tableName,
            Length = 100,
            IsNullable = true
        }
    };
    
    using var transaction = connection.BeginTransaction();
    try
    {
        foreach (var column in auditColumns)
        {
            bool created = await connection.CreateColumnIfNotExistsAsync(
                column, tx: transaction);
                
            Console.WriteLine($"Audit column '{column.ColumnName}': {(created ? "Added" : "Already exists")}");
        }
        
        transaction.Commit();
    }
    catch
    {
        transaction.Rollback();
        throw;
    }
}

// Clean up temporary columns
public async Task CleanupTempColumnsAsync(IDbConnection connection, string schema, string tableName)
{
    var allColumns = await connection.GetColumnNamesAsync(schema, tableName);
    var tempColumns = allColumns.Where(c => 
        c.Contains("temp_") || 
        c.StartsWith("tmp_") || 
        c.EndsWith("_backup")).ToList();
    
    Console.WriteLine($"Found {tempColumns.Count} temporary columns to clean up");
    
    foreach (var columnName in tempColumns)
    {
        bool dropped = await connection.DropColumnIfExistsAsync(schema, tableName, columnName);
        if (dropped)
        {
            Console.WriteLine($"Cleaned up column: {columnName}");
        }
    }
}

Column Validation and Analysis

csharp

public async Task AnalyzeColumnUsageAsync(IDbConnection connection, string schema, string tableName)
{
    var columns = await connection.GetColumnsAsync(schema, tableName);
    
    Console.WriteLine($"Column analysis for {schema}.{tableName}:");
    Console.WriteLine($"Total columns: {columns.Count}");
    
    var nullableColumns = columns.Where(c => c.IsNullable).ToList();
    var uniqueColumns = columns.Where(c => c.IsUnique).ToList();
    var autoIncrementColumns = columns.Where(c => c.IsAutoIncrement).ToList();
    var columnsWithDefaults = columns.Where(c => !string.IsNullOrEmpty(c.DefaultExpression)).ToList();
    
    Console.WriteLine($"Nullable columns: {nullableColumns.Count}");
    Console.WriteLine($"Unique columns: {uniqueColumns.Count}");
    Console.WriteLine($"Auto-increment columns: {autoIncrementColumns.Count}");
    Console.WriteLine($"Columns with defaults: {columnsWithDefaults.Count}");
    
    // Group by data type
    var typeGroups = columns.GroupBy(c => c.DotnetType?.Name).OrderBy(g => g.Key);
    Console.WriteLine("\nColumns by data type:");
    foreach (var group in typeGroups)
    {
        Console.WriteLine($"  {group.Key}: {group.Count()} columns");
        foreach (var column in group.Take(3))
        {
            Console.WriteLine($"    - {column.ColumnName}");
        }
        if (group.Count() > 3)
        {
            Console.WriteLine($"    ... and {group.Count() - 3} more");
        }
    }
}

// Validate column constraints
public async Task ValidateColumnConstraintsAsync(IDbConnection connection, 
    string schema, string tableName)
{
    var columns = await connection.GetColumnsAsync(schema, tableName);
    var issues = new List<string>();
    
    foreach (var column in columns)
    {
        // Check for potential issues
        if (column.DotnetType == typeof(string) && column.Length == null)
        {
            issues.Add($"Column '{column.ColumnName}' has no length limit");
        }
        
        if (column.DotnetType == typeof(decimal) && (column.Precision == null || column.Scale == null))
        {
            issues.Add($"Column '{column.ColumnName}' has no precision/scale specified");
        }
        
        if (column.ColumnName.Contains(" ") || column.ColumnName.Contains("-"))
        {
            issues.Add($"Column '{column.ColumnName}' contains spaces or hyphens");
        }
        
        if (column.IsNullable && column.IsAutoIncrement)
        {
            issues.Add($"Column '{column.ColumnName}' is auto-increment but nullable");
        }
    }
    
    if (issues.Any())
    {
        Console.WriteLine($"Found {issues.Count} potential issues:");
        foreach (var issue in issues)
        {
            Console.WriteLine($"  - {issue}");
        }
    }
    else
    {
        Console.WriteLine("No column constraint issues found");
    }
}

Dynamic Column Creation

csharp

public async Task CreateColumnsFromMetadataAsync(IDbConnection connection, 
    string schema, string tableName, Dictionary<string, Type> columnMetadata)
{
    using var transaction = connection.BeginTransaction();
    try
    {
        foreach (var (columnName, dataType) in columnMetadata)
        {
            // Determine optimal column properties based on type
            var column = CreateOptimalColumn(columnName, dataType, schema, tableName);

            bool created = await connection.CreateColumnIfNotExistsAsync(
                column, tx: transaction);
                
            Console.WriteLine($"Column '{columnName}': {(created ? "Created" : "Already exists")}");
        }
        
        transaction.Commit();
    }
    catch
    {
        transaction.Rollback();
        throw;
    }
}

private DmColumn CreateOptimalColumn(string columnName, Type dataType, string schema, string tableName)
{
    var column = new DmColumn(columnName, dataType)
    {
        SchemaName = schema,
        TableName = tableName
    };
    
    // Apply sensible defaults based on naming conventions and types
    if (columnName.ToLower().EndsWith("id"))
    {
        column.IsNullable = false;
        if (dataType == typeof(int))
        {
            column.IsAutoIncrement = columnName.ToLower() == "id";
        }
    }
    
    if (columnName.ToLower().Contains("email"))
    {
        column.Length = 255;
        column.IsNullable = false;
    }
    
    if (columnName.ToLower().Contains("name"))
    {
        column.Length = dataType == typeof(string) ? 100 : null;
        column.IsNullable = false;
    }
    
    if (columnName.ToLower().EndsWith("date") || columnName.ToLower().EndsWith("time"))
    {
        column.IsNullable = true;
    }
    
    if (dataType == typeof(decimal))
    {
        column.Precision = 18;
        column.Scale = 2;
    }
    
    return column;
}

Column methods provide essential functionality for evolving your database schema over time, supporting everything from simple column additions to complex data type migrations across all supported database providers.

Schema & Database

Constraints

Indexes

Column Methods

Quick Navigation

Column Existence Checking

DoesColumnExistAsync

Column Creation

CreateColumnIfNotExistsAsync (DmColumn)

CreateColumnIfNotExistsAsync (Individual Parameters)

Column Discovery

GetColumnNamesAsync

GetColumnsAsync

GetColumnAsync

Column Modification

RenameColumnIfExistsAsync

AlterColumnIfExistsAsync

Column Deletion

DropColumnIfExistsAsync

Practical Examples

Column Migration Pattern

Bulk Column Operations

Column Validation and Analysis

Dynamic Column Creation

Column Methods ​

Quick Navigation ​

Column Existence Checking ​

DoesColumnExistAsync ​

Column Creation ​

CreateColumnIfNotExistsAsync (DmColumn) ​

CreateColumnIfNotExistsAsync (Individual Parameters) ​

Column Discovery ​

GetColumnNamesAsync ​

GetColumnsAsync ​

GetColumnAsync ​

Column Modification ​

RenameColumnIfExistsAsync ​

AlterColumnIfExistsAsync ​

Column Deletion ​

DropColumnIfExistsAsync ​

Practical Examples ​

Column Migration Pattern ​

Bulk Column Operations ​

Column Validation and Analysis ​

Dynamic Column Creation ​

Column Methods

Quick Navigation

Column Existence Checking

DoesColumnExistAsync

Column Creation

CreateColumnIfNotExistsAsync (DmColumn)

CreateColumnIfNotExistsAsync (Individual Parameters)

Column Discovery

GetColumnNamesAsync

GetColumnsAsync

GetColumnAsync

Column Modification

RenameColumnIfExistsAsync

AlterColumnIfExistsAsync

Column Deletion

DropColumnIfExistsAsync

Practical Examples

Column Migration Pattern

Bulk Column Operations

Column Validation and Analysis

Dynamic Column Creation