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