public static string GenerateTemplate(ITableSchema schema, SqlScriptType type, bool ifExists) { TableName tableName = schema.TableName; TableClause script = new TableClause(schema); switch (type) { case SqlScriptType.INSERT: return(script.INSERT(schema.Columns)); case SqlScriptType.SELECT: return(script.SELECT(schema.Columns)); case SqlScriptType.UPDATE: return(script.UPDATE(schema.Columns)); case SqlScriptType.INSERT_OR_UPDATE: return(script.INSERT_OR_UPDATE(schema.Columns)); case SqlScriptType.DELETE: return(new Dependency(tableName.DatabaseName).DELETE(tableName) + script.DELETE(schema.Columns)); case SqlScriptType.DROP: return(new Dependency(tableName.DatabaseName).DROP_TABLE(tableName, ifExists) + script.DROP_TABLE(ifExists)); } return(null); }
/// <summary> /// Generates a Transact-SQL command batch that can be used to re-create the SQL stored procedure. /// </summary> /// <param name="scriptType"> /// A SqlScriptType indicating what to include in the script. /// </param> /// <returns> /// A string containing a Transact-SQL command batch that can be used to re-create the SQL stored procedure. /// </returns> /// <remarks> /// The valid SqlScriptType values are: Create, Drop, Comments. /// </remarks> public string Script(SqlScriptType scriptType) { int dmoScriptType = 0; if ((scriptType & SqlScriptType.Create) == SqlScriptType.Create) { dmoScriptType |= NativeMethods.SQLDMO_SCRIPT_TYPE.SQLDMOScript_Default | NativeMethods.SQLDMO_SCRIPT_TYPE.SQLDMOScript_OwnerQualify; } if ((scriptType & SqlScriptType.Drop) == SqlScriptType.Drop) { dmoScriptType |= NativeMethods.SQLDMO_SCRIPT_TYPE.SQLDMOScript_Drops; } if ((scriptType & SqlScriptType.Comments) == SqlScriptType.Comments) { dmoScriptType |= NativeMethods.SQLDMO_SCRIPT_TYPE.SQLDMOScript_IncludeHeaders; } if ((scriptType & SqlScriptType.Permissions) == SqlScriptType.Permissions) { dmoScriptType |= NativeMethods.SQLDMO_SCRIPT_TYPE.SQLDMOScript_ObjectPermissions; } return(dmoStoredProcedure.Script(dmoScriptType, null, 0)); }
public static int GenerateRows(SqlScriptType type, StreamWriter writer, ITableSchema schema, Locator where, SqlScriptGenerationOption option, IProgress <int> progress) { SqlScriptGeneration gen = new SqlScriptGeneration(type, schema) { Where = where, Option = option, }; return(gen.Generate(writer, progress)); }
/// <summary> /// Generates a Transact-SQL command batch that can be used to re-create the SQL table. /// </summary> /// <param name="scriptType"> /// A SqlScriptType indicating what to include in the script. /// </param> /// <returns> /// A string containing a Transact-SQL command batch that can be used to re-create the SQL table. /// </returns> /// <remarks> /// The valid SqlScriptType values are: Create, Drop, Comments, Defaults, PrimaryKey, ForeignKeys, UniqueKeys, Checks, Indexes. /// </remarks> public string ScriptSchema(SqlScriptType scriptType) { int dmoScriptType = 0; if ((scriptType & SqlScriptType.Create) == SqlScriptType.Create) { dmoScriptType |= NativeMethods.SQLDMO_SCRIPT_TYPE.SQLDMOScript_Default | NativeMethods.SQLDMO_SCRIPT_TYPE.SQLDMOScript_NoDRI; } if ((scriptType & SqlScriptType.Defaults) == SqlScriptType.Defaults) { dmoScriptType |= NativeMethods.SQLDMO_SCRIPT_TYPE.SQLDMOScript_DRI_Defaults | NativeMethods.SQLDMO_SCRIPT_TYPE.SQLDMOScript_DRIWithNoCheck; } if ((scriptType & SqlScriptType.PrimaryKey) == SqlScriptType.PrimaryKey) { dmoScriptType |= NativeMethods.SQLDMO_SCRIPT_TYPE.SQLDMOScript_DRI_PrimaryKey | NativeMethods.SQLDMO_SCRIPT_TYPE.SQLDMOScript_DRIWithNoCheck; } if ((scriptType & SqlScriptType.Checks) == SqlScriptType.Checks) { dmoScriptType |= NativeMethods.SQLDMO_SCRIPT_TYPE.SQLDMOScript_DRI_Checks | NativeMethods.SQLDMO_SCRIPT_TYPE.SQLDMOScript_DRIWithNoCheck; } if ((scriptType & SqlScriptType.ForeignKeys) == SqlScriptType.ForeignKeys) { dmoScriptType |= NativeMethods.SQLDMO_SCRIPT_TYPE.SQLDMOScript_DRI_ForeignKeys; } if ((scriptType & SqlScriptType.UniqueKeys) == SqlScriptType.UniqueKeys) { dmoScriptType |= NativeMethods.SQLDMO_SCRIPT_TYPE.SQLDMOScript_DRI_UniqueKeys; } if ((scriptType & SqlScriptType.Indexes) == SqlScriptType.Indexes) { dmoScriptType |= NativeMethods.SQLDMO_SCRIPT_TYPE.SQLDMOScript_DRIIndexes; } if ((scriptType & SqlScriptType.Drop) == SqlScriptType.Drop) { dmoScriptType |= NativeMethods.SQLDMO_SCRIPT_TYPE.SQLDMOScript_Drops; } if ((scriptType & SqlScriptType.Comments) == SqlScriptType.Comments) { dmoScriptType |= NativeMethods.SQLDMO_SCRIPT_TYPE.SQLDMOScript_IncludeHeaders; } return(dmoTable.Script(dmoScriptType, null, null, 0)); }
public void ExportScud(SqlScriptType type) { if (tname != null) { using (var writer = SqlFileName.CreateStreamWriter(cmd.Append)) { string sql = Compare.GenerateTemplate(new TableSchema(tname), type, cmd.HasIfExists); cout.WriteLine(sql); writer.WriteLine(sql); } } else { cerr.WriteLine("warning: table is not selected"); } }
/// <summary> /// Generates a Transact-SQL command batch that can be used to re-create the SQL database. /// </summary> /// <param name="scriptType"> /// A SqlScriptType indicating what to include in the script. /// </param> /// <returns> /// A string containing a Transact-SQL command batch that can be used to re-create the SQL database. /// </returns> /// <remarks> /// The valid SqlScriptType values are: Create, Drop, Comments. /// </remarks> public string Script(SqlScriptType scriptType) { int dmoScriptType = 0; if ((scriptType & SqlScriptType.Create) == SqlScriptType.Create) { dmoScriptType |= NativeMethods.SQLDMO_SCRIPT_TYPE.SQLDMOScript_Default; } if ((scriptType & SqlScriptType.Drop) == SqlScriptType.Drop) { dmoScriptType |= NativeMethods.SQLDMO_SCRIPT_TYPE.SQLDMOScript_Drops; } if ((scriptType & SqlScriptType.Comments) == SqlScriptType.Comments) { dmoScriptType |= NativeMethods.SQLDMO_SCRIPT_TYPE.SQLDMOScript_IncludeHeaders; } return(dmoDatabase.Script(dmoScriptType, null, 0)); }
/// <summary> /// Generates a Transact-SQL command batch that can be used to re-create the data in the SQL table. /// </summary> /// <param name="scriptType"> /// A SqlScriptType indicating what to include in the script. /// </param> /// <returns> /// A string containing a Transact-SQL command batch that can be used to re-create the data in the SQL table. /// </returns> /// <remarks> /// The valid SqlScriptType values are: Comments. /// </remarks> public string ScriptData(SqlScriptType scriptType) { // Grab data from table DataTable[] tables = Database.Query("select * from [" + name + "]"); DataTable table = tables[0]; StringBuilder sb = new StringBuilder(); if ((scriptType & SqlScriptType.Comments) == SqlScriptType.Comments) { sb.Append(String.Format(SR.GetString("SqlTable_ExportComment") + "\r\n", name)); } // If necessary, turn on identity insert bool hasIdentity = false; string[] columnNamesArray = new string[this.Columns.Count]; for (int i = 0; i < this.Columns.Count; i++) { columnNamesArray[i] = "[" + this.Columns[i].ColumnInformation.Name + "]"; if (this.Columns[i].ColumnInformation.Identity) { hasIdentity = true; } } string columnNames = String.Join(", ", columnNamesArray); if (hasIdentity) { sb.Append("SET identity_insert [" + this.Name + "] on\r\n\r\n"); } // Go through each row for (int i = 0; i < table.Rows.Count; i++) { object[] cols = table.Rows[i].ItemArray; sb.Append(String.Format("INSERT [{0}] ({1}) VALUES (", name, columnNames)); // And through each column within the row for (int j = 0; j < cols.Length; j++) { if (j > 0) { sb.Append(", "); } System.Type dataType = table.Columns[j].DataType; // If null, print null, otherwise output data based on type if (table.Rows[i].IsNull(j)) { // Database Null is just NULL sb.Append("NULL"); } else if (dataType == typeof(System.Int32) || dataType == typeof(System.Int16) || dataType == typeof(System.Decimal) || dataType == typeof(System.Single)) { // Numeric datatypes we just emit as-is sb.Append(cols[j]); } else if (dataType == typeof(System.DateTime) || dataType == typeof(System.String)) { // Strings and date/time's get quoted // Escape single quotes in strings (replace with two single quotes) sb.Append(String.Format("'{0}'", cols[j].ToString().Replace("'", "''"))); } else if (dataType == typeof(System.Boolean)) { // Booleans are false=0 and true=1 if ((System.Boolean)cols[j]) { sb.Append("1"); } else { sb.Append("0"); } } else if (dataType == typeof(System.Byte[])) { // Byte arrays are in the form 0x0123456789ABCDEF System.Byte[] array = (System.Byte[])cols[j]; sb.Append("0x"); for (int a = 0; a < array.Length; a++) { sb.Append(array[a].ToString("X")); } } else { // Default is to call ToString() and quote it // Escape single quotes in strings (replace with two single quotes) sb.Append(String.Format("'{0}'", cols[j].ToString().Replace("'", "''"))); } } sb.Append(")\r\n"); // Stick in a GO statement to make batches smaller //if ((i + 1) % 10 == 0) // sb.Append("GO\r\n"); } //sb.Append("GO\r\n\r\n"); // If we turned on identity insert, turn it off now if (hasIdentity) { sb.Append("SET identity_insert [" + this.Name + "] off\r\nGO\r\n"); } return(sb.ToString()); }
public void ExportInsertOrUpdateData(SqlScriptType type) { var option = new SqlScriptGenerationOption { HasIfExists = cmd.HasIfExists, InsertWithoutColumns = cmd.Has("no-columns"), }; if (tname != null) { var node = mgr.GetCurrentNode <Locator>(); int count = 0; using (var writer = SqlFileName.CreateStreamWriter(cmd.Append)) { //cout.WriteLine($"start to generate {tname} script to file: \"{SqlFileName}\""); Locator locator = null; string WHERE = ""; if (node != null) { locator = mgr.GetCombinedLocator(node); WHERE = $" WHERE {locator}"; } long cnt = tname.GetTableRowCount(locator); count = Tools.ForceLongToInteger(cnt); using (var progress = new ProgressBar { Count = count }) { count = Compare.GenerateRows(type, writer, new TableSchema(tname), locator, option, progress); } cout.WriteLine($"{type} clauses (SELECT * FROM {tname}{WHERE}) generated to \"{SqlFileName}\", Done on rows({cnt})"); } } else if (dname != null) { //cout.WriteLine($"start to generate {dname} script to file: \"{SqlFileName}\""); using (var writer = SqlFileName.CreateStreamWriter(cmd.Append)) { var md = new MatchedDatabase(dname, cmd); TableName[] tnames = md.TableNames(); if (tnames.Length > 5 && !cin.YesOrNo($"Are you sure to export {tnames.Length} tables on {dname} (y/n)?")) { return; } CancelableWork.CanCancel(cts => { foreach (var tn in tnames) { if (cts.IsCancellationRequested) { return; } long cnt = tn.GetTableRowCount(); if (cnt > cfg.MaxRows) { if (!cin.YesOrNo($"Are you sure to export {cnt} rows on {tn.ShortName} (y/n)?")) { cout.WriteLine("\n{0,10} skipped", tn.ShortName); continue; } } int count = Tools.ForceLongToInteger(cnt); using (var progress = new ProgressBar { Count = count }) { count = Compare.GenerateRows(type, writer, new TableSchema(tn), null, option, progress); } cout.WriteLine($"{count,10} row(s) generated on {tn.ShortName}"); } cout.WriteLine($"completed to generate {type} clauses to \"{SqlFileName}\""); }); } } else { cerr.WriteLine("warning: table or database is not selected"); } }
public SqlScriptGeneration(SqlScriptType type, ITableSchema schema) { this.type = type; this.schema = schema; this.script = new TableDataClause(schema); }
/// <summary> /// Generates a Transact-SQL command batch that can be used to re-create the data in the SQL table. /// </summary> /// <param name="scriptType"> /// A SqlScriptType indicating what to include in the script. /// </param> /// <returns> /// A string containing a Transact-SQL command batch that can be used to re-create the data in the SQL table. /// </returns> /// <remarks> /// The valid SqlScriptType values are: Comments. /// </remarks> public string ScriptData(SqlScriptType scriptType) { // Grab data from table DataTable[] tables = Database.Query("select * from [" + name + "]"); DataTable table = tables[0]; StringBuilder sb = new StringBuilder(); if ((scriptType & SqlScriptType.Comments) == SqlScriptType.Comments) { sb.Append(String.Format(SR.GetString("SqlTable_ExportComment") + "\r\n", name)); } List <string> columnNamesList = new List <string>(); // use the datatable column names, the SqlColumns class is // EXTREMELY slow at grabbing column information for (int idx = 0; idx < table.Columns.Count; ++idx) { // skip over timestamp columns (what about BLOBs?) if (table.Columns[idx].DataType != typeof(Byte[])) { columnNamesList.Add(table.Columns[idx].ColumnName); } } string[] columnNamesArray = columnNamesList.ToArray(); string columnNames = String.Join(", ", columnNamesArray); sb.Append("IF (SELECT IDENT_SEED('" + this.Name + "')) IS NOT NULL\r\n\t"); sb.Append("SET identity_insert [" + this.Name + "] on\r\n\r\n"); // Go through each row for (int i = 0; i < table.Rows.Count; i++) { object[] cols = table.Rows[i].ItemArray; sb.Append(String.Format("INSERT [{0}] ({1}) VALUES (", name, columnNames)); // And through each column within the row for (int j = 0; j < cols.Length; j++) { Type dataType = table.Columns[j].DataType; if (dataType == typeof(System.Byte[])) { continue; } if (j > 0) { sb.Append(", "); } // If null, print null, otherwise output data based on type if (table.Rows[i].IsNull(j)) { // Database Null is just NULL sb.Append("NULL"); } else if (dataType == typeof(System.Int32) || dataType == typeof(System.Int16) || dataType == typeof(System.Decimal) || dataType == typeof(System.Single)) { // Numeric datatypes we just emit as-is sb.Append(cols[j]); } else if (dataType == typeof(System.DateTime) || dataType == typeof(System.String)) { // Strings and date/time's get quoted // Escape single quotes in strings (replace with two single quotes) sb.Append(String.Format("'{0}'", cols[j].ToString().Replace("'", "''"))); } else if (dataType == typeof(System.Boolean)) { // Booleans are false=0 and true=1 if ((System.Boolean)cols[j]) { sb.Append("1"); } else { sb.Append("0"); } } // else if (dataType == typeof(System.Byte[])) { // // Byte arrays are in the form 0x0123456789ABCDEF // System.Byte[] array = (System.Byte[])cols[j]; // sb.Append("0x"); // for (int a = 0; a < array.Length; a++) // sb.Append(array[a].ToString("X")); // } else { // Default is to call ToString() and quote it // Escape single quotes in strings (replace with two single quotes) sb.Append(String.Format("'{0}'", cols[j].ToString().Replace("'", "''"))); } } sb.Append(")\r\n\r\n"); } //sb.Append("GO\r\n\r\n"); // If we turned on identity insert, turn it off now sb.Append("IF (SELECT IDENT_SEED('" + this.Name + "')) IS NOT NULL\r\n\t"); sb.Append("SET identity_insert [" + this.Name + "] off\r\nGO\r\n"); return(sb.ToString()); }
public void ExportDataToScripts(string query, string _DataDirectory, DataScriptType dataScriptType, SqlScriptType sqlScriptType, bool includeSoftDependencies, bool excludeUnwantedDependencies, bool autoMerge, string environmentName) { Dictionary <string, ExtractedTable> results = ExportDataFromDatabase(query, includeSoftDependencies, excludeUnwantedDependencies); string dataScriptDirectory = string.Empty; if (dataScriptType == DataScriptType.Static) { dataScriptDirectory = Path.Combine(_DataDirectory, "Static"); } else if (dataScriptType == DataScriptType.Test) { dataScriptDirectory = Path.Combine(_DataDirectory, "Test"); } if (!Directory.Exists(dataScriptDirectory)) { Directory.CreateDirectory(dataScriptDirectory); } // Check for existing script files if autoMerge not set if (!autoMerge) { List <string> existingScripts = new List <string>(); foreach (string tableName in results.Keys) { string scriptName = $"{tableName}.sql"; if (File.Exists(Path.Combine(dataScriptDirectory, scriptName))) { existingScripts.Add(scriptName); } } if (existingScripts.Count > 0) { throw new System.Exception($"{sqlScriptType.ToString()} script already exists for: {string.Join(", ", existingScripts)}. Enable auto merge to merge the new data with the existing data."); } } // Cleanse data DataCleanser dataCleanser = new DataCleanser(); dataCleanser.ReplaceImagePathFields(results.Values.Select(et => et.DataTable).ToList()); // Create scripts for each table foreach (string tableName in results.Keys) { FileInfo scriptFileInfo = new FileInfo(Path.Combine(dataScriptDirectory, $"{tableName}.sql")); // If script already exists, merge if (scriptFileInfo.Exists) { DataTable scriptDataTable = null; if (sqlScriptType == SqlScriptType.Insert) { scriptDataTable = SqlDataScriptHelper.NewDataTableFromInsertScript(scriptFileInfo.FullName, DataParser.GetXmlSchemaFromDataTable(results[tableName].DataTable)); } else if (sqlScriptType == SqlScriptType.Merge) { // todo throw new System.Exception("Not implemented"); } results[tableName].DataTable.Merge(scriptDataTable); } string script = null; if (sqlScriptType == SqlScriptType.Insert) { script = SqlDataScriptHelper.NewInsertScriptFromDataTable(results[tableName].TableName.Schema, results[tableName].TableName.Name, results[tableName].DataTable, results[tableName].PrimaryKeyColumnNames); } else if (sqlScriptType == SqlScriptType.Merge) { script = SqlDataScriptHelper.NewMergeScriptFromDataTable(results[tableName].TableName.Schema, results[tableName].TableName.Name, results[tableName].DataTable, false, true, environmentName, results[tableName].PrimaryKeyColumnNames); } SqlDataScriptHelper.WriteScriptToFile(scriptFileInfo.Name, script, dataScriptDirectory, true); } string sqlCmdScript = SqlDataScriptHelper.NewSqlCmdScript(dataScriptDirectory, dataScriptType); string sqlCmdScriptName = string.Empty; if (dataScriptType == DataScriptType.Static) { sqlCmdScriptName = "StaticData.sql"; } else if (dataScriptType == DataScriptType.Test) { sqlCmdScriptName = "TestData.sql"; } SqlDataScriptHelper.WriteScriptToFile(sqlCmdScriptName, sqlCmdScript, _DataDirectory, true); }
public void ExportScud(SqlScriptType type) { if (tname != null) { using (var writer = fileName.NewStreamWriter()) { string sql = Compare.GenerateTemplate(new TableSchema(tname), type); stdio.WriteLine(sql); writer.WriteLine(sql); } } else { stdio.ErrorFormat("warning: table is not selected"); } }
public static string GenerateTemplate(TableSchema schema, SqlScriptType type) { TableName tableName = schema.TableName; TableClause script = new TableClause(schema); switch (type) { case SqlScriptType.INSERT: return script.INSERT(schema.Columns); case SqlScriptType.SELECT: return script.SELECT(schema.Columns); case SqlScriptType.UPDATE: return script.UPDATE(schema.Columns); case SqlScriptType.INSERT_OR_UPDATE: return script.INSERT_OR_UPDATE(schema.Columns); case SqlScriptType.DELETE: return new Dependency(tableName.DatabaseName).DELETE(tableName) + script.DELETE(schema.Columns); } return null; }