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