/// <summary>
/// Generate Static Data Migration Script for the list of Tables.
/// </summary>
/// <param name="option"></param>
/// <param name="tables">The list of Table Name</param>
public virtual void Generate(MergeScriptOption option = MergeScriptOption.Default, Action<string> updateStatus = null, params string[] tables)
{
if (tables.Length == 0) throw new ArgumentNullException(nameof(tables));
if (!Directory.Exists(this.OutputFolder))
Directory.CreateDirectory(this.OutputFolder);
var builder = new StringBuilder();
AddHeader(builder);
foreach (var tb in SortTablesByDependence(tables))
{
if (updateStatus != null) updateStatus(tb);
var script = this.Generate(tb, option);
if (string.IsNullOrEmpty(script)) continue;
try
{
var fileName = string.Format(DefaultOutputFileName, tb.Replace("[", string.Empty).Replace("]", string.Empty).Replace(" ", string.Empty));
builder.AppendFormat(":r .\\{0}", fileName).Append(Environment.NewLine);
File.WriteAllText(Path.Combine(this.OutputFolder, fileName), script);
}
catch (Exception)
{
// ignored
}
}
//Write Execute Script.
File.WriteAllText(Path.Combine(this.OutputFolder, DefaultOutputExecuteFileName), builder.ToString());
}
/// <summary>
/// Generate Static Data Migration Script for the list of Tables.
/// </summary>
/// <param name="option"></param>
/// <param name="tables">The list of Table Name</param>
public virtual void Generate(MergeScriptOption option = MergeScriptOption.Default, Action <string> updateStatus = null, params string[] tables)
{
if (tables.Length == 0)
{
throw new ArgumentNullException(nameof(tables));
}
if (!Directory.Exists(this.OutputFolder))
{
Directory.CreateDirectory(this.OutputFolder);
}
var builder = new StringBuilder();
AddHeader(builder);
foreach (var tb in SortTablesByDependence(tables))
{
if (updateStatus != null)
{
updateStatus(tb);
}
var script = this.Generate(tb, option);
if (string.IsNullOrEmpty(script))
{
continue;
}
try
{
var fileName = string.Format(DefaultOutputFileName, tb.Replace("[", string.Empty).Replace("]", string.Empty).Replace(" ", string.Empty));
builder.AppendFormat(":r .\\{0}", fileName).Append(Environment.NewLine);
File.WriteAllText(Path.Combine(this.OutputFolder, fileName), script);
}
catch (Exception)
{
// ignored
}
}
//Write Execute Script.
File.WriteAllText(Path.Combine(this.OutputFolder, DefaultOutputExecuteFileName), builder.ToString());
}
/// <summary>
/// Generate Static Data Migration Script From DataTable
/// </summary>
/// <param name="table"></param>
/// <param name="option"></param>
/// <returns></returns>
public virtual string Generate(DataTable table, MergeScriptOption option = MergeScriptOption.Default)
{
if (table == null || table.Rows.Count == 0) return null;
var tableName = Common.GetSqlName(table.TableName);
var isIdentity = IsTableIdentity(tableName);
var builder = new StringBuilder();
AddHeader(builder);
builder.AppendFormat("PRINT N'Merging static data to {0}';", tableName).Append(Environment.NewLine)
.Append("GO").Append(Environment.NewLine).Append(Environment.NewLine);
if (isIdentity)
{
builder.Append("BEGIN TRY").Append(Environment.NewLine)
.Append($"SET IDENTITY_INSERT {tableName} ON").Append(Environment.NewLine)
.Append("END TRY").Append(Environment.NewLine)
.Append("BEGIN CATCH").Append(Environment.NewLine)
.Append("END CATCH").Append(Environment.NewLine)
.Append("GO").Append(Environment.NewLine).Append(Environment.NewLine);
}
builder.Append($"MERGE INTO {tableName} AS Target")
.Append(Environment.NewLine).Append("USING( VALUES").Append(Environment.NewLine);
for (var i = 0; i < table.Rows.Count; i++)
{
var row = table.Rows[i];
var strValues = string.Join(",", BuildSqlValueList(row.ItemArray));
builder.Append("(").Append(strValues).Append(")");
if (i < table.Rows.Count - 1) builder.Append(",");
builder.Append(Environment.NewLine);
}
var columns = BuildSqlFieldList(table.Columns.Cast<DataColumn>().Select(c => c.ColumnName).ToArray());
builder.AppendFormat(")AS Source({0})", string.Join(",", columns))
.Append(Environment.NewLine)
.Append("ON ");
var primaryKeys = string.Join(" AND ",
table.PrimaryKey.Select(p => $"Target.[{p.ColumnName}] = Source.[{p.ColumnName}]"));
builder.Append(primaryKeys).Append(Environment.NewLine);
var setColumns = string.Join("," + Environment.NewLine,
table.Columns.Cast<DataColumn>().Where(c => !table.PrimaryKey.Contains(c)).Select(c => $"[{c.ColumnName}] = Source.[{c.ColumnName}]"));
if (option.HasFlag(MergeScriptOption.Update)
&& !string.IsNullOrWhiteSpace(setColumns))
{
builder.Append("WHEN MATCHED THEN").Append(Environment.NewLine)
.Append("UPDATE SET ")
.Append(setColumns).Append(Environment.NewLine);
}
if (option.HasFlag(MergeScriptOption.Insert))
{
builder.Append("WHEN NOT MATCHED BY TARGET THEN").Append(Environment.NewLine)
.AppendFormat("INSERT({0})", columns).Append(Environment.NewLine)
.AppendFormat("VALUES({0})", columns).Append(Environment.NewLine);
}
if (option.HasFlag(MergeScriptOption.Delete))
{
builder.Append("WHEN NOT MATCHED BY SOURCE THEN").Append(Environment.NewLine)
.Append("DELETE");
}
builder.Append(";").Append(Environment.NewLine)
.Append("GO").Append(Environment.NewLine).Append(Environment.NewLine);
if (isIdentity)
{
builder.Append("BEGIN TRY").Append(Environment.NewLine)
.Append($"SET IDENTITY_INSERT {tableName} OFF").Append(Environment.NewLine)
.Append("END TRY").Append(Environment.NewLine)
.Append("BEGIN CATCH").Append(Environment.NewLine)
.Append("END CATCH").Append(Environment.NewLine)
.Append("GO").Append(Environment.NewLine).Append(Environment.NewLine);
}
builder.AppendFormat("PRINT N'Completed merge static data to {0}';", table).Append(Environment.NewLine).Append(Environment.NewLine);
return builder.ToString();
}
public virtual Task GenerateAsync(MergeScriptOption option = MergeScriptOption.Default, Action<string> updateStatus = null, params string[] tables)
=> Task.Run(() => Generate(option, updateStatus, tables));
public virtual Task GenerateAsync(MergeScriptOption option = MergeScriptOption.Default, params string[] tables)
=> Task.Run(() => Generate(option, tables));
public virtual void Generate(MergeScriptOption option = MergeScriptOption.Default, params string[] tables)
=> Generate(option, null, tables);
/// <summary>
/// Generate Static Data Migration Script for all Tables in DB.
/// </summary>
/// <param name="option"></param>
public virtual void GenerateAll(MergeScriptOption option = MergeScriptOption.Default)
{
var tables = SchemaInfo.Tables.Select(t => t.Name.FullName).ToArray();
Generate(option, tables);
}
/// <summary>
/// Generate the Static Data Migration Script for particular table.
/// </summary>
/// <param name="table"></param>
/// <param name="option"></param>
/// <returns></returns>
protected virtual string Generate(string table, MergeScriptOption option = MergeScriptOption.Default)
{
using (var data = this.GetDataTable(table))
return this.Generate(data, option);
}
public virtual void Generate(MergeScriptOption option = MergeScriptOption.Default, params string[] tables) => Generate(option, null, tables);
/// <summary>
/// Generate Static Data Migration Script for all Tables in DB.
/// </summary>
/// <param name="option"></param>
public virtual void GenerateAll(MergeScriptOption option = MergeScriptOption.Default)
{
var tables = SchemaInfo.Tables.Select(t => t.Name.FullName).ToArray();
Generate(option, tables);
}
/// <summary>
/// Generate the Static Data Migration Script for particular table.
/// </summary>
/// <param name="table"></param>
/// <param name="option"></param>
/// <returns></returns>
protected virtual string Generate(string table, MergeScriptOption option = MergeScriptOption.Default)
{
using (var data = this.GetDataTable(table))
return(this.Generate(data, option));
}
/// <summary>
/// Generate Static Data Migration Script From DataTable
/// </summary>
/// <param name="table"></param>
/// <param name="option"></param>
/// <returns></returns>
public virtual string Generate(DataTable table, MergeScriptOption option = MergeScriptOption.Default)
{
if (table == null || table.Rows.Count == 0)
{
return(null);
}
var tableName = Common.GetSqlName(table.TableName);
var isIdentity = IsTableIdentity(tableName);
var builder = new StringBuilder();
AddHeader(builder);
builder.AppendFormat("PRINT N'Merging static data to {0}';", tableName).Append(Environment.NewLine)
.Append("GO").Append(Environment.NewLine).Append(Environment.NewLine);
if (isIdentity)
{
builder.Append("BEGIN TRY").Append(Environment.NewLine)
.Append($"SET IDENTITY_INSERT {tableName} ON").Append(Environment.NewLine)
.Append("END TRY").Append(Environment.NewLine)
.Append("BEGIN CATCH").Append(Environment.NewLine)
.Append("END CATCH").Append(Environment.NewLine)
.Append("GO").Append(Environment.NewLine).Append(Environment.NewLine);
}
builder.Append($"MERGE INTO {tableName} AS Target")
.Append(Environment.NewLine).Append("USING( VALUES").Append(Environment.NewLine);
for (var i = 0; i < table.Rows.Count; i++)
{
var row = table.Rows[i];
var strValues = string.Join(",", BuildSqlValueList(row.ItemArray));
builder.Append("(").Append(strValues).Append(")");
if (i < table.Rows.Count - 1)
{
builder.Append(",");
}
builder.Append(Environment.NewLine);
}
var columns = BuildSqlFieldList(table.Columns.Cast <DataColumn>().Select(c => c.ColumnName).ToArray());
builder.AppendFormat(")AS Source({0})", string.Join(",", columns))
.Append(Environment.NewLine)
.Append("ON ");
var primaryKeys = string.Join(" AND ",
table.PrimaryKey.Select(p => $"Target.[{p.ColumnName}] = Source.[{p.ColumnName}]"));
builder.Append(primaryKeys).Append(Environment.NewLine);
var setColumns = string.Join("," + Environment.NewLine,
table.Columns.Cast <DataColumn>().Where(c => !table.PrimaryKey.Contains(c)).Select(c => $"[{c.ColumnName}] = Source.[{c.ColumnName}]"));
if (option.HasFlag(MergeScriptOption.Update) &&
!string.IsNullOrWhiteSpace(setColumns))
{
builder.Append("WHEN MATCHED THEN").Append(Environment.NewLine)
.Append("UPDATE SET ")
.Append(setColumns).Append(Environment.NewLine);
}
if (option.HasFlag(MergeScriptOption.Insert))
{
builder.Append("WHEN NOT MATCHED BY TARGET THEN").Append(Environment.NewLine)
.AppendFormat("INSERT({0})", columns).Append(Environment.NewLine)
.AppendFormat("VALUES({0})", columns).Append(Environment.NewLine);
}
if (option.HasFlag(MergeScriptOption.Delete))
{
builder.Append("WHEN NOT MATCHED BY SOURCE THEN").Append(Environment.NewLine)
.Append("DELETE");
}
builder.Append(";").Append(Environment.NewLine)
.Append("GO").Append(Environment.NewLine).Append(Environment.NewLine);
if (isIdentity)
{
builder.Append("BEGIN TRY").Append(Environment.NewLine)
.Append($"SET IDENTITY_INSERT {tableName} OFF").Append(Environment.NewLine)
.Append("END TRY").Append(Environment.NewLine)
.Append("BEGIN CATCH").Append(Environment.NewLine)
.Append("END CATCH").Append(Environment.NewLine)
.Append("GO").Append(Environment.NewLine).Append(Environment.NewLine);
}
builder.AppendFormat("PRINT N'Completed merge static data to {0}';", table).Append(Environment.NewLine).Append(Environment.NewLine);
return(builder.ToString());
}
public virtual Task GenerateAsync(MergeScriptOption option = MergeScriptOption.Default, Action <string> updateStatus = null, params string[] tables) => Task.Run(() => Generate(option, updateStatus, tables));
public virtual Task GenerateAsync(MergeScriptOption option = MergeScriptOption.Default, params string[] tables) => Task.Run(() => Generate(option, tables));
