Part 4 - Tables Creation in SQL Server using SMO
In this installation of my blog series I’ll cover defining, creating and persisting SQL scripts for tables based on our CodeGeneratorPropertyList using SQL Server Management Objects (SMO).
The script for creating tables and then saving the SQL script for the table creation is relatively straightforward. We go through each of the properties in our property list and if it is a primitive type (i.e. not a class or class list) then we add that property as a field in our table. Once complete we create the table and then save the script that was created for the table into a .sql file. The TableManager class used to complete this is as follows, the comments have been added to show the interesting parts of the code:
public class TableManager { private const string SQL_FILE_EXTENSION = ".sql"; private const string EXTENDED_DESCRIPTION_IDENTIFIER = "MS_Description"; public static void CreateTable(string Namespace, string ClassName, string Prefix, bool UsePrefix, CodeGeneratorPropertyList PropertyList, string FolderName, bool PersistToDatabase, Database SelectedDatabase) { string newPrefix = string.Empty; if (UsePrefix) { if (Prefix.Length == 0) { newPrefix = Namespace; } else { newPrefix = Prefix; } } string tableName = GetTableName(newPrefix, ClassName); string fileName = string.Format("{0}\\{1}{2}", FolderName, tableName, SQL_FILE_EXTENSION); string tableScriptContent = string.Empty; Server server = SelectedDatabase.Parent; if (PersistToDatabase) // Executiuon mode determines persistence to database { server.ConnectionContext.SqlExecutionModes = SqlExecutionModes.ExecuteAndCaptureSql; } else { server.ConnectionContext.SqlExecutionModes = SqlExecutionModes.CaptureSql; } // Creating New Table Table tbl = new Table(SelectedDatabase, tableName); // Go through all of the properties in the property list foreach (CodeGeneratorProperty property in PropertyList) { // Ensure that the property isn’t a class or class list if (CommonHelper.IsValidDatabaseDataType(property)) { Column col = new Column(tbl, property.PropertyName, CommonHelper.GetDataType(property)); // Make the primary key an Id column and give it a PK index if (property.IsPrimaryKey) { // Add Constraint if (property.DataType.Equals("Guid")) { col.AddDefaultConstraint(GetDefaultConstraintName(property.PropertyName)).Text = "newid()"; } else if (property.DataType.Equals("int")) { col.Identity = true; col.IdentityIncrement = 1; col.IdentitySeed = 1; } // Create a PK Index on Table Index idx = new Index(tbl, GetPrimaryKeyName(property.PropertyName)); idx.IndexKeyType = IndexKeyType.DriPrimaryKey; idx.IndexedColumns.Add(new IndexedColumn(idx, property.PropertyName)); tbl.Indexes.Add(idx); col.Nullable = false; } else { col.Nullable = !property.IsRequired; } // Add the description ExtendedProperty desc = new ExtendedProperty(col, EXTENDED_DESCRIPTION_IDENTIFIER, property.Description); col.ExtendedProperties.Add(desc); tbl.Columns.Add(col); } } // Attempt to Drop the Table and add that drop code to the beginning of the script content string tableScriptContent = AttemptTableDrop(SelectedDatabase, tableName); // Create Table - if SqlExecutionModes is CaptureSql then the table // is not actually created on the database tbl.Create(); // Get Script StringCollection commands = server.ConnectionContext.CapturedSql.Text; foreach (string s in commands) { tableScriptContent += string.Format("{0}\n", s); } CodeGenerationHelper.WriteFile(fileName, tableScriptContent); } private static string AttemptTableDrop(Database SelectedDatabase, string TableName) { string procedureContent = string.Format("IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{0}]') AND
type in (N'U')){1}", TableName, CodeGenerationHelper.GetEndOfLine()); procedureContent += string.Format("DROP TABLE [dbo].[{0}]", TableName) +
CodeGenerationHelper.GetEndOfLine(); procedureContent += CodeGenerationHelper.GetEndOfLine(); SelectedDatabase.ExecuteNonQuery(procedureContent); return procedureContent; } private static string GetPrimaryKeyName(string PropertyName) { return string.Format("PK_{0}", PropertyName); } private static string GetDefaultConstraintName(string PropertyName) { return string.Format("DF_{0}", PropertyName); } private static string GetTableName(string Prefix, string ClassName) { string newPrefix = string.Empty; if (Prefix.Length > 0) { newPrefix = string.Format("{0}_", Prefix); } return string.Format("{0}{1}", newPrefix, ClassName); }
}
That wraps it up for this installation, next we’ll look at defining, creating and persisting SQL scripts for stored procedures based on our CodeGeneratorPropertyList using SQL Server Management Objects (SMO). This takes an entirely different approach than table generation. Where tables are created and then the script is generated from the execution, stored procedures require us to create the sql script and then execute it against the server.