IDbTableManager.EditTable Method

IIS 7.0

Modifies a table in a database.

Namespace:  Microsoft.Web.Management.DatabaseManager
Assembly:  Microsoft.Web.Management.DatabaseManager (in Microsoft.Web.Management.DatabaseManager.dll)

void EditTable(
	string connectionString,
	string schema,
	TableInfo tableInfo
)

Parameters

connectionString
Type: System.String
The connection string for the database.
schema
Type: System.String
The schema name for the table.
Note   If schema is empty, the default schema name will be used.
tableInfo
Type: Microsoft.Web.Management.DatabaseManager.TableInfo
A TableInfo object that contains the list of information about the table and columns.

All database providers that implement the IDbTableManager interface must also implement the EditTable method, which the database manager will use to modify the columns, indexes, and relationships for a table.

Notes for Implementers

If your provider does not support editing tables, you can use the following code sample to raise a not-implemented exception:

public void EditTable(string connectionString, string schema, TableInfo tableInfo)
{
   throw new NotImplementedException();
}
NoteNote:

See the ALTER TABLE (Transact-SQL) topic for more information about the ALTER TABLE SQL statement.

The following code sample implements the EditTable method for an OLEDB connection by using the TableInfo object that the database manager provides.

NoteNote:

This example also provides helper methods that perform additional database tasks.



// Modify a table in the database.
public void EditTable(
    string connectionString,
    string schema,
    TableInfo tableInfo)
{
    // Test if the table has changes.
    if (tableInfo.HasChanges == true)
    {
        // Test if the table is marked for deletion.
        if (tableInfo.IsMarkedForDelete == true)
        {
            // Drop the table.
            DropTable(connectionString, schema, tableInfo);
        }
        else
        {
            // Create a connection to the database.
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                // Open the connection to the database.
                connection.Open();
                // Begin a transaction.
                OleDbTransaction transaction = connection.BeginTransaction();
                try
                {
                    // Update the table information.
                    UpdateColumns(connection, transaction, schema, tableInfo);
                    UpdateIndexes(connection, transaction, schema, tableInfo);
                    UpdateForeignKeys(connection, transaction, schema, tableInfo);
                    // Commit the transaction.
                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    // Roll back the transaction.
                    transaction.Rollback();
                    // Raise an exception if an error occurs.
                    throw new ProviderException("ALTER TABLE Exception:\n\n" + ex.Message);
                }
                finally
                {
                    // Close the database connection.
                    connection.Close();
                }
            }
        }
    }
}

private void UpdateColumns(
    OleDbConnection connection,
    OleDbTransaction transaction,
    string schema,
    TableInfo tableInfo)
{
    List<string> deleteColumnList = new List<string>();
    List<Column> renameColumnList = new List<Column>();
    ArrayList modifyColumnList = new ArrayList();
    List<Column> addList = new List<Column>();
    ArrayList dropDefaultConstraint = new ArrayList();
    List<Column> modifyDefaultConstraint = new List<Column>();

    foreach (Column column in tableInfo.Columns)
    {
        if (column.IsMarkedForDelete)
        {
            if (!column.IsNew)
            {
                deleteColumnList.Add(column.OriginalName);
            }
        }
        else if (column.IsNew)
        {
            addList.Add(column);
        }
        else
        {
            if (column.HasChanges(ColumnChanges.Name) &&
                !String.IsNullOrEmpty(column.OriginalName))
            {
                renameColumnList.Add(column);
            }
            else if (column.HasChanges(ColumnChanges.AllowNull) ||
                     column.HasChanges(ColumnChanges.Type) ||
                     column.HasChanges(ColumnChanges.Length))
            {
                modifyColumnList.Add(column);
            }

            if (column.HasChanges(ColumnChanges.DefaultValue))
            {
                if (String.IsNullOrEmpty(column.DefaultValue))
                {
                    // Drop the default constraint.
                    dropDefaultConstraint.Add(column);
                }
                else
                {
                    // Drop the default constraint.
                    dropDefaultConstraint.Add(column);
                    // Modify the default constraint.
                    modifyDefaultConstraint.Add(column);
                }
            }
        }
    }

    // Test if there are columns to delete.
    if (deleteColumnList.Count > 0)
    {
        // Delete any columns.
        DropColumns(connection, transaction, tableInfo.Name, schema, deleteColumnList.ToArray());
    }

    // Test if there are columns to rename.
    if (renameColumnList.Count > 0)
    {
        // Rename any columns.
        RenameColumns(connection, transaction, tableInfo.Name, schema, renameColumnList);
    }

    // Test if there are columns to alter.
    foreach (Column column in modifyColumnList)
    {
        // Alter any columns.
        AlterColumn(connection, transaction, tableInfo.Name, schema, column);
    }

    // Loop through any column constraints to delete.
    foreach (Column column in dropDefaultConstraint)
    {
        // Drop each column constraint.
        DropDefaultConstraint(connection, transaction, tableInfo.Name, schema, column);
    }

    // Add the default constraints.
    AddDefaultConstraints(connection, transaction, tableInfo.Name, schema, modifyDefaultConstraint);

    // Test if there are columns to add.
    if (addList.Count > 0)
    {
        // Add the columns.
        AddColumns(connection, transaction, tableInfo.Name, schema, addList);
    }
}

private void DropColumns(
    OleDbConnection connection,
    OleDbTransaction transaction,
    string tableName,
    string schema,
    string[] columns)
{
    // Create a new string builder for the SQL statement.
    StringBuilder alterTableText = new StringBuilder();
    alterTableText.Append("ALTER TABLE ");
    alterTableText.Append(EscapeName(tableName));
    alterTableText.Append(" DROP COLUMN ");

    bool addComma = false;
    foreach (string column in columns)
    {
        if (addComma == true)
        {
            alterTableText.Append(',');
        }
        addComma = true;

        alterTableText.Append(EscapeName(column));
    }

    OleDbCommand command = connection.CreateCommand();
    command.CommandText = alterTableText.ToString();
    command.Transaction = transaction;
    command.ExecuteNonQuery();
}

private void RenameColumns(
    OleDbConnection connection,
    OleDbTransaction transaction,
    string tableName,
    string schema,
    List<Column> columns)
{
    foreach (Column column in columns)
    {
        // Create a new string builder for the SQL statement.
        StringBuilder alterTableText = new StringBuilder();
        alterTableText.Append("ALTER TABLE ");
        alterTableText.Append(EscapeName(tableName));
        alterTableText.Append(" CHANGE COLUMN ");
        alterTableText.Append(EscapeName(column.OriginalName));
        alterTableText.Append(" ");
        alterTableText.Append(EscapeName(column.Name));
        alterTableText.Append(" " + column.TypeInfo.Name);

        if (column.Length > 0)
        {
            alterTableText.Append("(" + column.Length.ToString() + ")");
        }

        if (column.IsIdentity)
        {
            alterTableText.Append(" NOT NULL AUTO_INCREMENT PRIMARY KEY");
        }
        else
        {
            if (!column.AllowNulls)
            {
                alterTableText.Append(" NOT NULL");
            }
            else
            {
                alterTableText.Append(" NULL");
            }
        }

        OleDbCommand command = connection.CreateCommand();
        command.CommandText = alterTableText.ToString();
        command.Transaction = transaction;
        command.ExecuteNonQuery();
    }
}

private void AlterColumn(
    OleDbConnection connection,
    OleDbTransaction transaction,
    string tableName,
    string schema,
    Column column)
{
    // Create a new string builder for the SQL statement.
    StringBuilder alterTableText = new StringBuilder();

    alterTableText.Append("ALTER TABLE ");
    alterTableText.Append(EscapeName(tableName));
    alterTableText.Append(" ALTER COLUMN ");
    alterTableText.Append(EscapeName(column.Name));
    alterTableText.Append(" " + column.TypeInfo.Name);

    if (column.Length > 0)
    {
        alterTableText.Append("(" + column.Length.ToString() + ")");
    }

    if (column.IsIdentity)
    {
        alterTableText.Append(" NOT NULL AUTO_INCREMENT PRIMARY KEY");
    }
    else
    {
        if (!column.AllowNulls)
        {
            alterTableText.Append(" NOT NULL");
        }
        else
        {
            alterTableText.Append(" NULL");
        }
    }

    OleDbCommand command = connection.CreateCommand();
    command.CommandText = alterTableText.ToString();
    command.Transaction = transaction;
    command.ExecuteNonQuery();
}

private void DropDefaultConstraint(
    OleDbConnection connection,
    OleDbTransaction transaction,
    string tableName,
    string schema,
    Column column)
{
    StringBuilder builder = new StringBuilder();
    builder.Append("ALTER TABLE ");
    builder.Append(EscapeName(tableName));
    builder.Append(" ALTER COLUMN ");
    builder.Append(EscapeName(column.Name));
    builder.Append(" DROP DEFAULT");
    OleDbCommand command = connection.CreateCommand();
    command.CommandText = builder.ToString();
    command.Transaction = transaction;
    command.ExecuteNonQuery();
}

private void AddDefaultConstraints(
    OleDbConnection connection,
    OleDbTransaction transaction,
    string tableName,
    string schema,
    IList<Column> columns)
{
    foreach (Column column in columns)
    {
        if (!String.IsNullOrEmpty(column.DefaultValue))
        {
            // Create a new string builder for the SQL statement.
            StringBuilder AlterTableText = new StringBuilder();
            AlterTableText.Append("ALTER TABLE ");
            AlterTableText.Append(EscapeName(tableName));
            AlterTableText.Append(" ALTER COLUMN ");
            AlterTableText.Append(EscapeName(column.Name));
            AlterTableText.Append(" SET DEFAULT ");

            if (IsStringType(column.TypeInfo))
            {
                AlterTableText.Append("'");
                AlterTableText.Append(column.DefaultValue);
                AlterTableText.Append("'");
            }
            else
            {
                AlterTableText.Append(column.DefaultValue);
            }

            OleDbCommand command = connection.CreateCommand();
            command.CommandText = AlterTableText.ToString();
            command.Transaction = transaction;
            command.ExecuteNonQuery();
        }
    }
}

// Add columns to a table.
private void AddColumns(
    OleDbConnection connection,
    OleDbTransaction transaction,
    string tableName,
    string schema,
    IList<Column> columns)
{
    // Create a new string builder for the SQL statement.
    StringBuilder alterTableText = new StringBuilder();
    alterTableText.Append("ALTER TABLE ");
    alterTableText.Append(EscapeName(tableName));
    alterTableText.Append(" ADD ");
    alterTableText.Append(BuildColumnClause(columns));

    OleDbCommand command = connection.CreateCommand();
    command.CommandText = alterTableText.ToString();
    command.Transaction = transaction;
    command.ExecuteNonQuery();

    AddDefaultConstraints(connection, transaction, tableName, schema, columns);
}

private static string BuildColumnClause(
    IList<Column> columns)
{
    // Create a new string builder for the SQL statement.
    StringBuilder columnClause = new StringBuilder();

    bool addComma = false;
    foreach (Column column in columns)
    {
        if (addComma == true)
        {
            columnClause.Append(',');
        }

        addComma = true;

        columnClause.Append(EscapeName(column.Name));
        columnClause.Append(" " + column.TypeInfo.Name);

        if (column.Length > 0)
        {
            columnClause.Append("(" + column.Length.ToString() + ")");
        }

        if (column.IsIdentity)
        {
            columnClause.Append(" NOT NULL AUTO_INCREMENT PRIMARY KEY");
        }
        else
        {
            if (!column.AllowNulls)
            {
                columnClause.Append(" NOT NULL");
            }
            else
            {
                columnClause.Append(" NULL");
            }
        }
    }

    return columnClause.ToString();
}

private void UpdateIndexes(
    OleDbConnection connection,
    OleDbTransaction transaction,
    string schema,
    TableInfo tableInfo)
{
    List<Index> deleteList = new List<Index>();
    List<Index> addList = new List<Index>();

    foreach (Index index in tableInfo.Indexes)
    {
        if (index.IsMarkedForDelete)
        {
            if (!index.IsNew)
            {
                deleteList.Add(index);
            }
        }
        else if (index.IsNew)
        {
            addList.Add(index);
        }
        else if (!index.Name.Equals(index.OriginalName,StringComparison.Ordinal))
        {
            // Delete and re-add to rename the index.
            deleteList.Add(index);
            addList.Add(index);
        }
    }
    // Test if there are indices to delete.
    if (deleteList.Count > 0)
    {
        DropIndexes(connection, transaction, tableInfo.Name, schema, deleteList);
    }
    // Test if there are indices to add.
    if (addList.Count > 0)
    {
        AddIndexes(connection, transaction, tableInfo.Name, schema, addList);
    }
}

private void DropIndexes(
    OleDbConnection connection,
    OleDbTransaction transaction,
    string tableName,
    string schema,
    IList<Index> deleteList)
{
    foreach (Index index in deleteList)
    {
        StringBuilder sql = new StringBuilder();
        if (index.IndexType == IndexType.PrimaryKey)
        {
            sql.Append("ALTER TABLE ");
            sql.Append(EscapeName(tableName));
            sql.Append(" DROP PRIMARY KEY");
        }
        else
        {
            sql.Append("DROP INDEX " + EscapeName(index.Name));
            sql.Append(" ON " + EscapeName(tableName));
        }
        OleDbCommand command = connection.CreateCommand();
        command.CommandText = sql.ToString();
        command.Transaction = transaction;
        command.ExecuteNonQuery();
    }
}

private void AddIndexes(
    OleDbConnection connection,
    OleDbTransaction transaction,
    string tableName,
    string schema,
    IList<Index> indexes)
{
    foreach (Index index in indexes)
    {
        if (index.IndexType == IndexType.PrimaryKey)
        {
            AddPrimaryKey(connection, transaction, tableName, schema, index);
        }
        else
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("CREATE ");
            if (index.Unique)
            {
                sql.Append("UNIQUE ");
            }

            sql.Append("INDEX " + EscapeName(index.Name) + " ON " + EscapeName(tableName));
            sql.Append("(");
            bool addComma = false;
            foreach (IndexColumn column in index.Columns)
            {
                if (addComma == true)
                {
                    sql.Append(", ");
                }
                addComma = true;
                sql.Append(EscapeName(column.Name));
            }
            sql.Append(")");

            OleDbCommand command = connection.CreateCommand();
            command.CommandText = sql.ToString();
            command.Transaction = transaction;
            command.ExecuteNonQuery();
        }
    }
}

private void AddPrimaryKey(
    OleDbConnection connection,
    OleDbTransaction transaction,
    string tableName,
    string schema,
    Index index)
{
    StringBuilder sql = new StringBuilder();

    sql.Append("ALTER TABLE ");
    sql.Append(EscapeName(tableName));
    sql.Append(" ADD PRIMARY KEY");

    sql.Append("(");
    bool addComma = false;
    foreach (IndexColumn column in index.Columns)
    {
        if (addComma == true)
        {
            sql.Append(", ");
        }
        addComma = true;
        sql.Append(EscapeName(column.Name));
    }
    sql.Append(")");

    OleDbCommand command = connection.CreateCommand();
    command.CommandText = sql.ToString();
    command.Transaction = transaction;
    command.ExecuteNonQuery();
}

private void UpdateForeignKeys(
    OleDbConnection connection,
    OleDbTransaction transaction,
    string schema,
    TableInfo tableInfo)
{
    List<string> deleteList = new List<string>();
    List<ForeignKey> addList = new List<ForeignKey>();

    foreach (ForeignKey fk in tableInfo.ForeignKeys)
    {
        if (fk.IsMarkedForDelete)
        {
            if (!fk.IsNew)
            {
                deleteList.Add(fk.Name);
            }
        }
        else if (fk.IsNew)
        {
            addList.Add(fk);
        }
        else if (!fk.Name.Equals(fk.OriginalName, StringComparison.Ordinal))
        {
            deleteList.Add(fk.Name);
            addList.Add(fk);
        }
    }

    if (deleteList.Count > 0)
    {
        DropForeignKeys(connection, transaction, tableInfo.Name, schema, deleteList.ToArray());
    }

    if (addList.Count > 0)
    {
        AddForeignKeys(connection, transaction, addList);
    }
}

private void DropForeignKeys(
    OleDbConnection connection,
    OleDbTransaction transaction,
    string tableName,
    string schema,
    string[] fkNames)
{
    StringBuilder sqlString = new StringBuilder();
    sqlString.Append("ALTER TABLE ");
    sqlString.Append(EscapeName(tableName));
    sqlString.Append(" DROP FOREIGN KEY ");
    foreach (string fkName in fkNames)
    {
        string sql = sqlString.ToString() + EscapeName(fkName);

        OleDbCommand command = connection.CreateCommand();
        command.CommandText = sql;
        command.Transaction = transaction;
        command.ExecuteNonQuery();
    }
}

private void AddForeignKeys(
    OleDbConnection connection,
    OleDbTransaction transaction,
    IList<ForeignKey> foreignKeys)
{
    foreach (ForeignKey fk in foreignKeys)
    {
        StringBuilder sql = new StringBuilder();
        sql.Append("ALTER TABLE ");
        sql.Append(EscapeName(fk.FKTableName));
        sql.Append(" ADD CONSTRAINT ");
        sql.Append(EscapeName(fk.Name));
        sql.Append(" FOREIGN KEY ");
        sql.Append("(");
        bool addComma = false;
        foreach (ForeignKeyColumn column in fk.FKColumns)
        {
            if (addComma == true)
            {
                sql.Append(", ");
            }
            addComma = true;
            sql.Append(EscapeName(column.Name));
        }
        sql.Append(")");
        sql.Append(" REFERENCES " + EscapeName(fk.PKTableName) + " ");
        sql.Append("(");
        addComma = false;
        foreach (ForeignKeyColumn column in fk.PKColumns)
        {
            if (addComma == true)
            {
                sql.Append(", ");
            }
            addComma = true;
            sql.Append(EscapeName(column.Name));
        }
        sql.Append(")");
        sql.Append(" ON UPDATE NO ACTION ON DELETE NO ACTION");

        OleDbCommand command = connection.CreateCommand();
        command.CommandText = sql.ToString();
        command.Transaction = transaction;
        command.ExecuteNonQuery();
    }
}



...



        public void DropTable(string connectionString, string schema, TableInfo tableInfo)
        {
            // Create a connection to the database.
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                // Open the connection to the database.
                connection.Open();
                // Begin a transaction.
                OleDbTransaction transaction = connection.BeginTransaction();
                try
                {
                    // Create the DROP TABLE SQL statement.
                    string dropStatement = String.Format("DROP TABLE {0}", EscapeName(tableInfo.Name));
                    // Create an OLEDB command object.
                    using (OleDbCommand command = new OleDbCommand(dropStatement, connection))
                    {
                        // Specify the transaction.
                        command.Transaction = transaction;
                        // Run the SQL statement.
                        command.ExecuteNonQuery();
                        // Commit the transaction.
                        transaction.Commit();
                    }
                }
                catch (Exception ex)
                {
                    // Roll back the transaction.
                    transaction.Rollback();
                    // Raise an exception if an error occurs.
                    throw new ProviderException("DROP TABLE Exception:\n\n" + ex.Message);
                }
                finally
                {
                    // Close the database connection.
                    connection.Close();
                }
            }
        }



...



        internal static string EscapeName(string name)
        {
            // Test if the value is already escaped.
            if (name.StartsWith("[", StringComparison.Ordinal) && name.EndsWith("]", StringComparison.Ordinal))
            {
                // Return the string with no changes.
                return name;
            }
            // Create a new string builder for the escaped string.
            StringBuilder escapedName = new StringBuilder();
            // Prepend the string with an opening bracket.
            escapedName.Append('[');
            // Loop through the string.
            for (int i = 0; i < name.Length; i++)
            {
                char c = name[i];
                if (c == ']')
                {
                    escapedName.Append(']');
                }
                escapedName.Append(c);
            }
            // Append the string with an opening bracket.
            escapedName.Append(']');
            // Return the escaped string.
            return escapedName.ToString();
        }



...



        // Determine if a database type is a string type.
        private bool IsStringType(TypeInfo typeInfo)
        {
            return (String.Compare(typeInfo.Name, "Text", true) == 0);
        }



Show: