public static string GenerateInsertIntoScript(string sourceTableName, IList <SqlColumnInfo> sourceColumnInfos, string destinationTableName)
{
if (sourceColumnInfos == null)
{
throw new ArgumentNullException("sourceColumnInfos");
}
if (destinationTableName == null)
{
throw new ArgumentNullException("destinationTableName");
}
if (sourceColumnInfos.Count == 0)
{
return(null);
}
// Exclude timestamp column
List <SqlColumnInfo> columnInfos = sourceColumnInfos.Where(p => p.Type != SqlColumnType.@timestamp).ToList();
List <SqlColumnInfo> primaryKeyColumnInfos = columnInfos.Where(p => p.IsPrimaryKey).ToList();
List <SqlColumnInfo> nonPrimaryKeyColumnInfos = columnInfos.Where(p => !p.IsPrimaryKey).ToList();
StringBuilder builder = new StringBuilder();
builder.AppendLine($"INSERT INTO [{destinationTableName}](");
for (int i = 0; i < columnInfos.Count; i++)
{
SqlColumnInfo columnInfo = columnInfos[i];
builder.Append($" [{columnInfo.Name}]");
if (i < columnInfos.Count - 1)
{
builder.AppendLine(",");
}
else
{
builder.AppendLine(")");
}
}
builder.AppendLine("SELECT");
for (int i = 0; i < columnInfos.Count; i++)
{
SqlColumnInfo columnInfo = columnInfos[i];
builder.Append($" [{columnInfo.Name}]");
if (i < columnInfos.Count - 1)
{
builder.AppendLine(",");
}
else
{
builder.AppendLine("");
}
}
builder.AppendLine($"FROM [{sourceTableName}]");
return(builder.ToString());
}
public static SqlColumnInfo[] GetColumnInfos(SqlConnection connection, string tableName)
{
bool isTempTable = tableName.StartsWith("#");
// Get Primary key
List <string> primaryKeys = new List <string>();
if (!isTempTable)
{
using (SqlCommand loadPrimaryKeysCommand = connection.CreateCommand())
{
loadPrimaryKeysCommand.CommandText = SqlScriptHelper.GenerateLoadTablePrimaryKeyScript(tableName);
using (SqlDataReader reader = loadPrimaryKeysCommand.ExecuteReader())
{
while (reader.Read())
{
primaryKeys.Add(Convert.ToString(reader[0]));
}
}
}
}
// Get Column Info
List <SqlColumnInfo> columnInfos = new List <SqlColumnInfo>();
using (SqlCommand loadColumnInfosCommand = connection.CreateCommand())
{
if (!isTempTable)
{
loadColumnInfosCommand.CommandText = SqlScriptHelper.GenerateLoadTableColumnsScript(tableName);
}
else
{
loadColumnInfosCommand.CommandText = SqlScriptHelper.GenerateLoadTempTableColumnsScript(tableName);
}
using (SqlDataReader reader = loadColumnInfosCommand.ExecuteReader())
{
while (reader.Read())
{
SqlColumnInfo columnInfo = new SqlColumnInfo();
columnInfo.Name = Convert.ToString(reader["Name"]);
columnInfo.Type = (SqlColumnType)Enum.Parse(typeof(SqlColumnType), Convert.ToString(reader["Type"]));
switch (columnInfo.Type)
{
// None
case SqlColumnType.@image:
case SqlColumnType.@text:
case SqlColumnType.@uniqueidentifier:
case SqlColumnType.@date:
case SqlColumnType.@tinyint:
case SqlColumnType.@smallint:
case SqlColumnType.@int:
case SqlColumnType.@smalldatetime:
case SqlColumnType.@real:
case SqlColumnType.@money:
case SqlColumnType.@datetime:
case SqlColumnType.@float:
case SqlColumnType.@sql_variant:
case SqlColumnType.@ntext:
case SqlColumnType.@bit:
case SqlColumnType.@smallmoney:
case SqlColumnType.@bigint:
case SqlColumnType.@hierarchyid:
case SqlColumnType.@geometry:
case SqlColumnType.@geography:
case SqlColumnType.@timestamp:
case SqlColumnType.@xml:
break;
// percious & scale
case SqlColumnType.@decimal:
case SqlColumnType.@numeric:
{
columnInfo.Precision = Convert.ToInt32(reader["Precision"]);
columnInfo.Scale = Convert.ToInt32(reader["Scale"]);
}
break;
// Scale
case SqlColumnType.@time:
case SqlColumnType.@datetime2:
case SqlColumnType.@datetimeoffset:
{
columnInfo.Scale = Convert.ToInt32(reader["Scale"]);
}
break;
// Unicode char
case SqlColumnType.@nvarchar:
case SqlColumnType.@nchar:
{
columnInfo.Length = Convert.ToInt32(reader["Length"]);
if (columnInfo.Length > 0)
{
columnInfo.Length /= 2;
}
}
break;
// Ansi char
case SqlColumnType.@varchar:
case SqlColumnType.@char:
{
columnInfo.Length = Convert.ToInt32(reader["Length"]);
}
break;
// Binary
case SqlColumnType.@varbinary:
case SqlColumnType.@binary:
{
columnInfo.Length = Convert.ToInt32(reader["Length"]);
}
break;
// Other
case SqlColumnType.@sysname:
break;
default:
break;
}
columnInfo.IsPrimaryKey = primaryKeys.Any(p => p.Equals(Convert.ToString(reader["Name"]), StringComparison.OrdinalIgnoreCase));
columnInfo.Nullable = Convert.ToBoolean(reader["Nullable"]);
columnInfo.Collation = !Convert.IsDBNull(reader["Collation"]) ? Convert.ToString(reader["Collation"]) : null;
columnInfos.Add(columnInfo);
}
}
}
return(columnInfos.ToArray());
}
public static string GenerateCreateTableScript(string tableName, IList <SqlColumnInfo> columnInfos, bool createPrimaryKey, bool createIfNotExists = false)
{
StringBuilder builder = new StringBuilder();
if (createIfNotExists)
{
builder.Append($"IF NOT EXISTS (SELECT 1 FROM sysobjects WHERE [name] = '{tableName}' AND [xtype] = 'U') ");
}
builder.AppendLine($"CREATE TABLE [{tableName}](");
for (int i = 0; i < columnInfos.Count; i++)
{
SqlColumnInfo columnInfo = columnInfos[i];
builder.Append(" ");
builder.Append($"[{columnInfo.Name}]");
builder.Append(" ");
builder.Append($"[{columnInfo.Type}]");
// Length & Precision & Scale
if (columnInfo.Length.HasValue || columnInfo.Precision.HasValue || columnInfo.Scale.HasValue)
{
builder.Append("(");
List <string> segments = new List <string>();
if (columnInfo.Length.HasValue)
{
segments.Add((columnInfo.Length > 0) ? columnInfo.Length.ToString() : "MAX");
}
if (columnInfo.Precision.HasValue)
{
segments.Add(columnInfo.Precision.ToString());
}
if (columnInfo.Scale.HasValue)
{
segments.Add(columnInfo.Scale.ToString());
}
builder.Append(string.Join(", ", segments.ToArray()));
builder.Append(")");
}
// Collation
if (columnInfo.Collation != null)
{
builder.Append(" COLLATE ");
builder.Append(columnInfo.Collation);
}
// Nullable
builder.Append(" ");
if (createPrimaryKey)
{
builder.Append(columnInfo.Nullable ? "NULL" : "NOT NULL");
}
else
{
builder.Append("NULL");
}
if (i < columnInfos.Count - 1)
{
builder.AppendLine(",");
}
}
// Primarykey
if (createPrimaryKey)
{
SqlColumnInfo[] primaryKeyColumnInfos = columnInfos.Where(p => p.IsPrimaryKey).ToArray();
if (primaryKeyColumnInfos.Length > 0)
{
builder.AppendLine(",");
builder.Append(" ");
string keylist = string.Join(",", primaryKeyColumnInfos.Select(p => $"[{p.Name}]"));
builder.Append($"CONSTRAINT [PK_{tableName}] PRIMARY KEY ({keylist})");
}
}
builder.Append(")");
return(builder.ToString());
}
public static string GenerateUpdateFormScript(string sourceTableName, IList <SqlColumnInfo> sourceColumnInfos, string destinationTableName)
{
if (sourceTableName == null)
{
throw new ArgumentNullException("sourceTableName");
}
if (sourceColumnInfos == null)
{
throw new ArgumentNullException("sourceColumnInfos");
}
if (destinationTableName == null)
{
throw new ArgumentNullException("destinationTableName");
}
if (sourceColumnInfos.Count == 0)
{
return(null);
}
// Exclude timestamp column
List <SqlColumnInfo> columnInfos = sourceColumnInfos.Where(p => p.Type != SqlColumnType.@timestamp).ToList();
List <SqlColumnInfo> primaryKeyColumnInfos = columnInfos.Where(p => p.IsPrimaryKey).ToList();
List <SqlColumnInfo> nonPrimaryKeyColumnInfos = columnInfos.Where(p => !p.IsPrimaryKey).ToList();
StringBuilder builder = new StringBuilder();
// UPDATE
// [Destination]
// SET
builder.AppendLine("UPDATE");
builder.AppendLine($" [{destinationTableName}]");
builder.AppendLine("SET");
// [Destination].N1 = [Source].N1,
// [Destination].N2 = [Source].N2,
// ....
// [Destination].N* = [Source].N*
for (int i = 0; i < nonPrimaryKeyColumnInfos.Count; i++)
{
SqlColumnInfo nonPrimaryKeyColumnInfo = nonPrimaryKeyColumnInfos[i];
builder.Append($" [{destinationTableName}].[{nonPrimaryKeyColumnInfo.Name}] = [{sourceTableName}].[{nonPrimaryKeyColumnInfo.Name}]");
if (i < nonPrimaryKeyColumnInfos.Count - 1)
{
builder.AppendLine(",");
}
else
{
builder.AppendLine();
}
}
// FROM
// [Source]
// WHERE
builder.AppendLine("FROM");
builder.AppendLine($" [{sourceTableName}]");
builder.AppendLine("WHERE");
// [Destination].K1 = [Source].K1 AND [Destination].K2 = [Source].K2 AND .... [Destination].K* = [Source].K*
for (int i = 0; i < primaryKeyColumnInfos.Count; i++)
{
SqlColumnInfo primaryKeyColumnInfo = primaryKeyColumnInfos[i];
builder.Append($" [{destinationTableName}].[{primaryKeyColumnInfo.Name}] = [{sourceTableName}].[{primaryKeyColumnInfo.Name}]");
if (i < primaryKeyColumnInfos.Count - 1)
{
builder.Append(" AND ");
}
else
{
builder.AppendLine();
}
}
return(builder.ToString());
}
public static string GenerateMergeScript(string sourceTableName, IList <SqlColumnInfo> sourceColumnInfos, string destinationTableName)
{
if (sourceColumnInfos == null)
{
throw new ArgumentNullException("sourceColumnInfos");
}
if (destinationTableName == null)
{
throw new ArgumentNullException("destinationTableName");
}
if (sourceColumnInfos.Count == 0)
{
return(null);
}
// Exclude timestamp column
List <SqlColumnInfo> columnInfos = sourceColumnInfos.Where(p => p.Type != SqlColumnType.@timestamp).ToList();
List <SqlColumnInfo> primaryKeyColumnInfos = columnInfos.Where(p => p.IsPrimaryKey).ToList();
List <SqlColumnInfo> nonPrimaryKeyColumnInfos = columnInfos.Where(p => !p.IsPrimaryKey).ToList();
StringBuilder builder = new StringBuilder();
// Merge
builder.AppendLine($"MERGE [{destinationTableName}] AS [Destination]");
// Using
if (sourceTableName != null)
{
builder.AppendLine($"USING [{sourceTableName}] AS [Source]");
}
else
{
string parameterList = string.Join(", ", columnInfos.Select(p => "@{p.Name}"));
string columnList = string.Join(", ", columnInfos.Select(p => "[{p.Name}]"));
builder.AppendLine($"USING (SELECT {parameterList}) AS [Source]({columnList})");
}
// On
builder.Append("ON (");
for (int i = 0; i < primaryKeyColumnInfos.Count; i++)
{
SqlColumnInfo primaryKeyColumnInfo = primaryKeyColumnInfos[i];
builder.Append($"[Source].[{primaryKeyColumnInfo.Name}] = [Destination].[{primaryKeyColumnInfo.Name}]");
if (i < primaryKeyColumnInfos.Count - 1)
{
builder.Append(" AND ");
}
else
{
builder.Append(")");
}
}
builder.AppendLine();
// match
builder.AppendLine("WHEN matched THEN");
builder.AppendLine(" UPDATE SET");
for (int i = 0; i < nonPrimaryKeyColumnInfos.Count; i++)
{
SqlColumnInfo nonPrimaryKeyColumnInfo = nonPrimaryKeyColumnInfos[i];
#if HealthCareCDR // HealthCare special threatment: remove FirstInsertDateTime which for first insert only
if (nonPrimaryKeyColumnInfo.Name.Equals("FirstInsertDateTime", StringComparison.OrdinalIgnoreCase))
{
continue;
}
#endif
builder.Append($" [{nonPrimaryKeyColumnInfo.Name}] = [Source].[{nonPrimaryKeyColumnInfo.Name}]");
if (i < nonPrimaryKeyColumnInfos.Count - 1)
{
builder.AppendLine(",");
}
else
{
builder.AppendLine();
}
}
// not match
builder.AppendLine("WHEN not matched THEN");
builder.AppendLine(" INSERT(");
for (int i = 0; i < columnInfos.Count; i++)
{
SqlColumnInfo columnInfo = columnInfos[i];
builder.Append($" [{columnInfo.Name}]");
if (i < columnInfos.Count - 1)
{
builder.AppendLine(",");
}
else
{
builder.AppendLine(")");
}
}
builder.AppendLine(" VALUES(");
for (int i = 0; i < columnInfos.Count; i++)
{
SqlColumnInfo columnInfo = columnInfos[i];
builder.Append($" [Source].[{columnInfo.Name}]");
if (i < columnInfos.Count - 1)
{
builder.AppendLine(",");
}
else
{
builder.AppendLine(");");
}
}
return(builder.ToString());
}
