public static string SQLGenerateCreateTable(this TableTemplate template, string tableName)
{
SqlServerMigrationSqlGenerator gen = new SqlServerMigrationSqlGenerator();
var operations = new List <MigrationOperation>();
var table = new CreateTableOperation(tableName);
table.PrimaryKey = new AddPrimaryKeyOperation();
foreach (var field in template.Fields)
{
PrimitiveTypeKind typeKind;
if (field.Type == TableTemplateFieldType.Int)
{
typeKind = PrimitiveTypeKind.Int32;
}
else if (field.Type == TableTemplateFieldType.String)
{
typeKind = PrimitiveTypeKind.String;
}
else
{
typeKind = PrimitiveTypeKind.String;
}
var column = new ColumnModel(typeKind);
column.Name = field.Name;
if (column.Type == PrimitiveTypeKind.String)
{
column.MaxLength = field.Length;
}
if (field.IsKey)
{
table.PrimaryKey.Columns.Add(field.Name);
}
if (field.Name.ToLower() == "sys_id")
{
column.IsIdentity = true;
}
table.Columns.Add(column);
}
operations.Add(table);
var sql = gen.Generate(operations, "2008").FirstOrDefault();
return(sql.Sql);
}
internal static string GetMigrationSql(DbMigration dbMigration, SqlConnection sqlConnection)
{
StringBuilder sql = new StringBuilder();
var prop = dbMigration.GetType().GetProperty("Operations", BindingFlags.NonPublic | BindingFlags.Instance);
if (prop != null)
{
IEnumerable <MigrationOperation> operations = prop.GetValue(dbMigration) as IEnumerable <MigrationOperation>;
foreach (var operation in operations)
{
if (operation is AddForeignKeyOperation && (( AddForeignKeyOperation )operation).PrincipalColumns.Count == 0)
{
// In Rock, the principal column should always be the Id. This isn't always the case . . . .
(( AddForeignKeyOperation )operation).PrincipalColumns.Add("Id");
}
}
var generator = new SqlServerMigrationSqlGenerator();
var statements = generator.Generate(operations, sqlConnection.ServerVersion.AsInteger() > 10 ? "2008" : "2005");
foreach (MigrationStatement item in statements)
{
if (item.Sql.StartsWith("CREATE TABLE"))
{
//So the way this works is ROCK makes our table for us.
//But table builder just tacks on the Primary Key constraint
//So remove most of the table text and change it to an alter table instead
var start = item.Sql.IndexOf("CREATE TABLE") + 12;
var end = item.Sql.IndexOf("(");
var tableName = item.Sql.Substring(start, end - start);
var constraint = item.Sql.Substring(item.Sql.IndexOf("CONSTRAINT"));
constraint = constraint.Substring(0, constraint.Length - 1);
sql.Append(string.Format($"ALTER TABLE {tableName} ADD {constraint};"));
}
else
{
sql.Append(item.Sql + ";");
}
}
}
return(sql.ToString());
}
internal string GetMigrationSql(SqlConnection sqlConnection)
{
StringBuilder sql = new StringBuilder();
var prop = this.GetType().GetProperty("Operations", BindingFlags.NonPublic | BindingFlags.Instance);
if (prop != null)
{
IEnumerable <MigrationOperation> operations = prop.GetValue(this) as IEnumerable <MigrationOperation>;
foreach (var operation in operations)
{
if (operation is AddForeignKeyOperation && (( AddForeignKeyOperation )operation).PrincipalColumns.Count == 0)
{
(( AddForeignKeyOperation )operation).PrincipalColumns.Add("Id");
}
}
var generator = new SqlServerMigrationSqlGenerator();
var statements = generator.Generate(operations, sqlConnection.ServerVersion.AsInteger() > 10 ? "2008" : "2005");
foreach (MigrationStatement item in statements)
{
sql.Append(item.Sql);
}
}
return(sql.ToString());
}
