public SqlScriptingResult GenerateSqlScripts(SqlScriptingInput input)
{
var result = new SqlScriptingResult();
HandleTables(result, input.Tables, input.RootFolder);
HandleTextObjects(result, input.TextObjects, input.RootFolder);
return result;
}
public SqlDeployScriptResult GenerateDeployScript(SqlScriptingInput input)
{
var result = new SqlDeployScriptResult();
var tables = new StringBuilder(string.Empty);
foreach (SqlTable table in input.Tables)
{
// Ignore "tSQLt" schema.
if (!table.Schema.Equals("tSQLt", System.StringComparison.InvariantCultureIgnoreCase))
{
string name = string.Format("{0}.{1}", table.Schema, table.Name);
string groupName = "Tables";
tables.AppendLine(string.Format(@"{0} ""{1}""", "call :runCmd", Path.Combine(groupName, name)));
}
}
result.Tables = tables.ToString();
var schemas = new StringBuilder(string.Empty);
foreach (string schema in input.Schemas)
{
schemas.AppendLine(Path.Combine("Schemas", schema));
schemas.AppendLine(string.Format(@"{0} ""{1}""", "call :runCmd", Path.Combine("Schemas", schema)));
}
result.Schemas = result.ToString();
var functions = new StringBuilder(string.Empty);
var views = new StringBuilder(string.Empty);
var storedProcedures = new StringBuilder(string.Empty);
foreach (SqlTextObject textObject in input.TextObjects)
{
// Ignore "tSQLt" schema.
if (!textObject.Schema.Equals("tSQLt", System.StringComparison.InvariantCultureIgnoreCase))
{
string name = string.Format("{0}.{1}", textObject.Schema, textObject.Name);
string groupName = textObject.GroupName;
switch (groupName.ToLower())
{
case "functions":
functions.AppendLine(string.Format(@"{0} ""{1}""", "call :runCmd", Path.Combine(groupName, name)));
break;
case "views":
views.AppendLine(string.Format(@"{0} ""{1}""", "call :runCmd", Path.Combine(groupName, name)));
break;
case "storedprocedures":
storedProcedures.AppendLine(string.Format(@"{0} ""{1}""", "call :runCmd", Path.Combine(groupName, name)));
break;
}
}
}
result.Functions = functions.ToString();
result.Views = views.ToString();
result.StoredProcedures = storedProcedures.ToString();
return result;
}
/// <summary>
/// Maps SMO objects to Dtos.
/// </summary>
public SqlScriptingInput GetSqlScriptingInput(string serverName, string databaseName, string userName = null, string password = null)
{
var result = new SqlScriptingInput();
var server = GetServer(serverName, userName, password);
try
{
server.ConnectionContext.Connect();
Database database = server.Databases[databaseName];
MapSmoTablesToSqlScriptDtos(database.Tables.Cast<Table>().Where(x => x.IsSystemObject == false), result);
MapSmoSchemasToSqlScriptDtos(database.Schemas.Cast<Schema>().Where(x => x.IsSystemObject == false), result);
MapSmoTextObjectsToSqlScriptDtos(database.StoredProcedures.Cast<StoredProcedure>().Where(x => x.IsSystemObject == false), "procedure", "StoredProcedures", result);
MapSmoTextObjectsToSqlScriptDtos(database.UserDefinedFunctions.Cast<UserDefinedFunction>().Where(x => x.IsSystemObject == false), "function", "Functions", result);
MapSmoTextObjectsToSqlScriptDtos(database.Views.Cast<View>().Where(x => x.IsSystemObject == false), "view", "Views", result);
}
finally
{
server.ConnectionContext.Disconnect();
}
return result;
}
public void MapSmoTablesToSqlScriptDtos(IEnumerable<Table> tables, SqlScriptingInput input)
{
foreach (Table table in tables)
{
var sqlTable = new SqlTable
{
Name = table.Name,
Schema = table.Schema
};
foreach (Column column in table.Columns)
{
DataType dataType = column.DataType;
var sqlColumn = new SqlColumn
{
DataTypeMaximumLength = dataType.MaximumLength,
DataTypeName = dataType.Name,
DataTypeNumericPrecision = dataType.NumericPrecision,
DataTypeNumericScale = dataType.NumericScale,
IdentityIncrement = column.IdentityIncrement,
IdentitySeed = column.IdentitySeed,
IsDefault = (column.DefaultConstraint != null),
IsForeignKey = (table.ForeignKeys != null && table.ForeignKeys.Count > 0),
IsIdentity = column.Identity,
IsNullable = column.Nullable,
IsPrimaryKey = (column.InPrimaryKey),
Name = column.Name
};
sqlTable.Columns.Add(sqlColumn);
}
foreach (ForeignKey fk in table.ForeignKeys)
{
ForeignKeyColumn fkc = fk.Columns[0];
// TODO: this is not a correct mapping, must be fixed!
var sqlForeignKey = new SqlForeignKey
{
ReferencedSchemaName = fk.ReferencedTableSchema,
ReferencedTableName = fk.ReferencedTable,
ColumnName = fkc.Name,
ReferencedColumnName = fkc.ReferencedColumn
};
sqlTable.ForeignKeys.Add(sqlForeignKey);
}
input.Tables.Add(sqlTable);
}
}
public void MapSmoTextObjectsToSqlScriptDtos(IEnumerable<object> textObjects, string typeName, string groupName, SqlScriptingInput input)
{
foreach (object textObject in textObjects)
{
ScriptSchemaObjectBase textObjectAsScriptSchemaObjectBase = (ScriptSchemaObjectBase)textObject;
ITextObject textObjectAsTextObject = (ITextObject)textObject;
var sqlTextObject = new SqlTextObject
{
GroupName = groupName,
Name = textObjectAsScriptSchemaObjectBase.Name,
Schema = textObjectAsScriptSchemaObjectBase.Schema,
TextBody = textObjectAsTextObject.TextBody,
TextHeader = textObjectAsTextObject.TextHeader,
TypeName = typeName
};
input.TextObjects.Add(sqlTextObject);
}
}
public void MapSmoSchemasToSqlScriptDtos(IEnumerable<Schema> schemas, SqlScriptingInput input)
{
foreach (Schema schema in schemas)
{
input.Schemas.Add(schema.Name);
}
}