/// <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]); }