/// <summary>
/// Permanently removes this column from the table.
/// </summary>
/// <remarks>
/// If there are dependencies on this column other than a primary key or a default, the operation may fail.
/// </remarks>
public void Remove()
{
// Remove the primary key if this column is in it
bool inPrimaryKey = this.ColumnInformation.Key;
ArrayList keyColumns = new ArrayList();
if (inPrimaryKey)
{
NativeMethods.IKeys keys = Table.dmoTable.GetKeys();
for (int i = 0; i < keys.GetCount(); i++)
{
// Find the primary key
if (keys.Item(i + 1).GetType() == NativeMethods.SQLDMO_KEY_TYPE.SQLDMOKey_Primary)
{
// First we have to keep a list of columns so we can undo the operation
NativeMethods.INames columnNames = keys.Item(i + 1).GetKeyColumns();
for (int j = 0; j < columnNames.GetCount(); j++)
{
keyColumns.Add(columnNames.Item(j + 1));
}
// Remove the primary key completely
// NOTE: This is what SQL Server Enterprise Manager does
keys.Remove(i + 1);
break;
}
}
}
// Remove default
string defaultValue = this.dmoColumn.GetDRIDefault().GetText();
this.dmoColumn.GetDRIDefault().Remove();
// Permanently delete this column
try {
dmoColumn.Remove();
}
catch {
// Undo operations and rethrow the exception
// Set default back
this.dmoColumn.GetDRIDefault().SetText(defaultValue);
// If necessary, create primary key and add original columns to it
if (inPrimaryKey)
{
NativeMethods.IKey primaryKey = (NativeMethods.IKey) new NativeMethods.Key();
primaryKey.SetType(NativeMethods.SQLDMO_KEY_TYPE.SQLDMOKey_Primary);
NativeMethods.INames columnNames = primaryKey.GetKeyColumns();
for (int i = 0; i < keyColumns.Count; i++)
{
columnNames.Add(columnInfo.Name);
}
table.dmoTable.GetKeys().Add(primaryKey);
}
throw;
}
}
/// <summary>
/// Adds a new column to the table with specified column information.
/// </summary>
/// <param name="columnInfo">
/// The column information for the column to add.
/// </param>
/// <returns>
/// If the operation succeeded, the return value is the column created.
/// </returns>
public SqlColumn Add(SqlColumnInformation columnInfo)
{
// Do some basic error checking - leave the rest up to DMO
if (columnInfo.Name == null || columnInfo.Name.Length == 0)
{
throw new ArgumentException(SR.GetString("SqlColumnCollection_MustHaveValidName"));
}
if (columnInfo.DataType == null || columnInfo.DataType.Length == 0)
{
throw new ArgumentException(SR.GetString("SqlColumnCollection_MustHaveValidDataType"));
}
if (this[columnInfo.Name] != null)
{
throw new ArgumentException(String.Format(SR.GetString("SqlColumnCollection_NameAlreadyExists"), columnInfo.Name));
}
// Create new DMO column
NativeMethods.IColumn dmoColumn = (NativeMethods.IColumn) new NativeMethods.Column();
dmoColumn.SetName(columnInfo.Name);
dmoColumn.SetDatatype(columnInfo.DataType);
dmoColumn.SetLength(columnInfo.Size);
dmoColumn.SetAllowNulls(columnInfo.Nulls);
dmoColumn.SetNumericPrecision(columnInfo.Precision);
dmoColumn.SetNumericScale(columnInfo.Scale);
dmoColumn.SetIdentity(columnInfo.Identity);
dmoColumn.SetIdentitySeed(columnInfo.IdentitySeed);
dmoColumn.SetIdentityIncrement(columnInfo.IdentityIncrement);
dmoColumn.SetIsRowGuidCol(columnInfo.IsRowGuid);
dmoColumn.GetDRIDefault().SetText(columnInfo.DefaultValue);
// Physically add the column
table.dmoTable.BeginAlter();
table.dmoTable.GetColumns().Add(dmoColumn);
table.dmoTable.DoAlter();
// If this column is to be included in the primary key, do some stuff
if (columnInfo.Key)
{
// Find out if there is a primary key...
NativeMethods.IKey primaryKey = null;
NativeMethods.IKeys keys = table.dmoTable.GetKeys();
for (int i = 0; i < keys.GetCount(); i++)
{
if (keys.Item(i + 1).GetType() == NativeMethods.SQLDMO_KEY_TYPE.SQLDMOKey_Primary)
{
primaryKey = keys.Item(i + 1);
break;
}
}
if (primaryKey != null)
{
// If there is a primary key, just add the column to the list of columns
}
else
{
// If there is no primary key, create new primary key and add thie column as the only column
primaryKey = (NativeMethods.IKey) new NativeMethods.Key();
primaryKey.SetType(NativeMethods.SQLDMO_KEY_TYPE.SQLDMOKey_Primary);
primaryKey.GetKeyColumns().Add(columnInfo.Name);
table.dmoTable.GetKeys().Add(primaryKey);
}
}
// Read the data back out
columnInfo = new SqlColumnInformation(dmoColumn.GetInPrimaryKey(), dmoColumn.GetIdentity(), dmoColumn.GetName(), dmoColumn.GetDatatype(), dmoColumn.GetLength(), dmoColumn.GetNumericScale(), dmoColumn.GetNumericPrecision(), dmoColumn.GetAllowNulls(), dmoColumn.GetDRIDefault().GetText(), dmoColumn.GetIdentitySeed(), dmoColumn.GetIdentityIncrement(), dmoColumn.GetIsRowGuidCol());
SqlColumn column = new SqlColumn(columnInfo);
// Set internal properties
column.dmoColumn = dmoColumn;
column.table = this.table;
// Add to private list
columns.Add(column);
return(column);
}
/// <summary>
/// Adds a new table to the database with specified columns.
/// </summary>
/// <param name="name">
/// The name of the table to create.
/// </param>
/// <param name="columnInfos">
/// An array of at least one SqlColumnInformation object with the column information filled out.
/// </param>
/// <returns>
/// If the operation succeeded, the return value is the table created.
/// </returns>
public SqlTable Add(string name, SqlColumnInformation[] columnInfos)
{
if (name == null || name.Length == 0)
{
throw new ArgumentException(SR.GetString("SqlTableCollection_MustHaveValidName"));
}
if (this[name] != null)
{
throw new ArgumentException(String.Format(SR.GetString("SqlTableCollection_NameAlreadyExists"), name));
}
if (columnInfos == null || columnInfos.Length == 0)
{
throw new ArgumentException(SR.GetString("SqlTableCollection_AtLeastOneColumn"));
}
// Create new table
NativeMethods.ITable dmoTable = (NativeMethods.ITable) new NativeMethods.Table();
dmoTable.SetName(name);
// No need to clear out keys since this is a new table
// Create new primary key with list of columns
NativeMethods.IKey key = (NativeMethods.IKey) new NativeMethods.Key();
key.SetType(NativeMethods.SQLDMO_KEY_TYPE.SQLDMOKey_Primary);
// Add columns to table
for (int i = 0; i < columnInfos.Length; i++)
{
NativeMethods.IColumn dmoColumn = (NativeMethods.IColumn) new NativeMethods.Column();
dmoColumn.SetName(columnInfos[i].Name);
dmoColumn.SetDatatype(columnInfos[i].DataType);
dmoColumn.SetLength(columnInfos[i].Size);
dmoColumn.SetAllowNulls(columnInfos[i].Nulls);
dmoColumn.SetNumericPrecision(columnInfos[i].Precision);
dmoColumn.SetNumericScale(columnInfos[i].Scale);
dmoColumn.SetIdentity(columnInfos[i].Identity);
dmoColumn.SetIdentitySeed(columnInfos[i].IdentitySeed);
dmoColumn.SetIdentityIncrement(columnInfos[i].IdentityIncrement);
dmoColumn.SetIsRowGuidCol(columnInfos[i].IsRowGuid);
// According to SQL Server Books Online, a name for this default will be generated automatically
dmoColumn.GetDRIDefault().SetText(columnInfos[i].DefaultValue);
// Add the column on the DMO side
dmoTable.GetColumns().Add(dmoColumn);
// If this column is in the primary key, add it to the primary key column list
if (columnInfos[i].Key)
{
key.GetKeyColumns().Add(columnInfos[i].Name);
}
}
// If there is anything in the primary key, add it
if (key.GetKeyColumns().GetCount() > 0)
{
dmoTable.GetKeys().Add(key);
}
// Add table to database
database.dmoDatabase.GetTables().Add(dmoTable);
database.Server.Databases.Refresh();
this.Refresh();
return(this[name]);
}
