private ISuccessOrErrors CheckSqlColumn(SqlColumnInfo sqlCol, SqlColumnInfo colToCheck, string combinedName)
{
var status = new SuccessOrErrors();
if (sqlCol.SqlTypeName != colToCheck.SqlTypeName)
{
status.AddSingleError(
"Column Type: SQL column {0}.{1} type does not match EF. '{2}' db type = {3}, '{4}' db type = {5}.",
combinedName, sqlCol.ColumnName,
_refDatabaseName, sqlCol.SqlTypeName,
_toBeCheckDatabaseName, colToCheck.SqlTypeName);
}
if (sqlCol.IsNullable != colToCheck.IsNullable)
{
status.AddSingleError(
"Column Nullable: SQL column {0}.{1} nullablity does not match. '{2}' db is {3}NULL, '{4}' db is {5}NULL.",
combinedName, sqlCol.ColumnName,
_refDatabaseName, sqlCol.IsNullable ? "" : "NOT ",
_toBeCheckDatabaseName, colToCheck.IsNullable ? "" : "NOT ");
}
if (sqlCol.MaxLength != colToCheck.MaxLength)
{
status.AddSingleError(
"Column MaxLength: SQL column {0}.{1} MaxLength does not match. '{2}' db MaxLength = {2}, '{4}' db MaxLength = {5}.",
combinedName, sqlCol.ColumnName,
_refDatabaseName, sqlCol.MaxLength,
_toBeCheckDatabaseName, colToCheck.MaxLength);
}
if (sqlCol.IsPrimaryKey != colToCheck.IsPrimaryKey)
{
status.AddSingleError(
"Primary Key: The SQL column {0}.{1} primary key settings don't match. '{2}' db says is {3}a key, '{4}' db says it is {5}a key.",
combinedName, sqlCol.ColumnName,
_refDatabaseName, sqlCol.IsPrimaryKey ? "" : "NOT ",
_toBeCheckDatabaseName, colToCheck.IsPrimaryKey ? "" : "NOT ");
}
else if (sqlCol.IsPrimaryKey && sqlCol.PrimaryKeyOrder != colToCheck.PrimaryKeyOrder)
{
status.AddSingleError(
"Key Order: The SQL column {0}.{1} primary key order does not match. '{2}' db order = {3}, '{4}' db order = {5}.",
combinedName, sqlCol.ColumnName,
_refDatabaseName, sqlCol.PrimaryKeyOrder,
_toBeCheckDatabaseName, colToCheck.PrimaryKeyOrder);
}
return(status);
}
private ISuccessOrErrors CheckMaxLength(SqlColumnInfo sqlCol, EfColumnInfo clrCol, string combinedName)
{
var status = SuccessOrErrors.Success("All ok");
if (sqlCol.MaxLength == -1 && clrCol.MaxLength != -1)
{
//SQL is at max length, but EF isn't
status.AddWarning(
"MaxLength: The SQL {0} column {1}.{2}, type {3}, is at max length, but EF length is at {4}.",
_sqlDbRefString, combinedName, clrCol.SqlColumnName, clrCol.ClrColumnType, clrCol.MaxLength);
}
else if (sqlCol.MaxLength != clrCol.MaxLength)
{
status.AddSingleError(
"MaxLength: The SQL {0} column {1}.{2}, type {3}, length does not match EF. SQL length = {4}, EF length = {5}.",
_sqlDbRefString, combinedName, clrCol.SqlColumnName, clrCol.ClrColumnType,
sqlCol.SqlTypeName.EfLengthIdHalfThis() ? sqlCol.MaxLength / 2 : sqlCol.MaxLength,
sqlCol.SqlTypeName.EfLengthIdHalfThis() ? clrCol.MaxLength / 2 : clrCol.MaxLength);
}
return(status);
}
private ISuccessOrErrors CheckColumn(SqlColumnInfo sqlCol, EfColumnInfo clrCol, string combinedName)
{
var status = new SuccessOrErrors();
if (sqlCol.SqlTypeName != clrCol.SqlTypeName)
{
status.AddSingleError(
"Column Type: The SQL {0} column {1}.{2} type does not match EF. SQL type = {3}, EF type = {4}.",
_sqlDbRefString, combinedName, clrCol.SqlColumnName, sqlCol.SqlTypeName, clrCol.SqlTypeName);
}
if (sqlCol.IsNullable != clrCol.IsNullable)
{
status.AddSingleError(
"Column Nullable: SQL {0} column {1}.{2} nullablity does not match. SQL is {3}NULL, EF is {4}NULL.",
_sqlDbRefString, combinedName, clrCol.SqlColumnName,
sqlCol.IsNullable ? "" : "NOT ",
clrCol.IsNullable ? "" : "NOT ");
}
if (sqlCol.IsPrimaryKey != clrCol.IsPrimaryKey)
{
status.AddSingleError(
"Primary Key: The SQL {0} column {1}.{2} primary key settings don't match. SQL says it is {3}a key, EF says it is {4}a key.",
_sqlDbRefString, combinedName, clrCol.SqlColumnName,
sqlCol.IsPrimaryKey ? "" : "NOT ",
clrCol.IsPrimaryKey ? "" : "NOT ");
}
else if (sqlCol.IsPrimaryKey && sqlCol.PrimaryKeyOrder != clrCol.PrimaryKeyOrder)
{
status.AddSingleError(
"Primary Key Order: The SQL {0} column {1}.{2} primary key order does not match. SQL order = {3}, EF order = {4}.",
_sqlDbRefString, combinedName, clrCol.SqlColumnName,
sqlCol.PrimaryKeyOrder, clrCol.PrimaryKeyOrder);
}
return(status.Combine(CheckMaxLength(sqlCol, clrCol, combinedName)));
}
private string GenerateScript(SyncDatabaseExecuteConfig executeConfig)
{
StringBuilder builder = new StringBuilder();
using (SqlConnection connection = new SqlConnection(executeConfig.ConnectionString))
{
connection.Open();
// Clear script
if (executeConfig.ClearSQL != null)
{
builder.AppendLine(executeConfig.ClearSQL);
}
foreach (DataTable table in this._dataSet.Tables)
{
string tableName = table.TableName;
if (SyncDatabaseConfig.DefaultSheetName.Equals(tableName, StringComparison.OrdinalIgnoreCase))
{
continue;
}
SqlColumnInfo[] allColumnInfos = SqlDatabaseHelper.GetColumnInfos(connection, tableName);
// Filter available columns
List <SqlColumnInfo> usedColumnInfos = new List <SqlColumnInfo>();
List <int> usedColumnIndices = new List <int>();
for (int columnIndex = 0; columnIndex < table.Columns.Count; columnIndex++)
{
string columnName = table.Columns[columnIndex].ColumnName;
SqlColumnInfo columnInfo = allColumnInfos.FirstOrDefault(p => p.Name.Equals(columnName, StringComparison.OrdinalIgnoreCase));
if (columnInfo != null)
{
usedColumnInfos.Add(columnInfo);
usedColumnIndices.Add(columnIndex);
}
}
string template = null;
switch (executeConfig.Mode)
{
case SyncDatabaseMode.Insert:
template = SqlScriptHelper.GenerateInsertScript(usedColumnInfos, tableName);
break;
case SyncDatabaseMode.InsertNotExists:
template = SqlScriptHelper.GenerateInsertIfNotExistsScript(usedColumnInfos, tableName);
break;
case SyncDatabaseMode.Merge:
template = SqlScriptHelper.GenerateMergeScript(null, usedColumnInfos, tableName);
break;
}
builder.AppendLine($"-- {tableName}");
builder.AppendLine($"IF EXISTS (SELECT 1 FROM sys.identity_columns WHERE object_id = object_id('{tableName}')) SET IDENTITY_INSERT [{tableName}] ON");
foreach (DataRow row in table.Rows)
{
string command = template;
for (int i = 0; i < usedColumnIndices.Count; i++)
{
int columnIndex = usedColumnIndices[i];
string value = (string)row[columnIndex];
string text = string.Empty;
SqlColumnInfo columnInfo = usedColumnInfos[i];
if (value == "NULL" || string.IsNullOrEmpty(value))
{
switch (columnInfo.Type)
{
case SqlColumnType.@text:
case SqlColumnType.@ntext:
case SqlColumnType.@varchar:
case SqlColumnType.@char:
case SqlColumnType.@nvarchar:
case SqlColumnType.@nchar:
case SqlColumnType.@xml:
if (columnInfo.Nullable)
{
text = "null";
}
else
{
text = "''";
}
break;
case SqlColumnType.@tinyint:
case SqlColumnType.@smallint:
case SqlColumnType.@int:
case SqlColumnType.@real:
case SqlColumnType.@money:
case SqlColumnType.@float:
case SqlColumnType.@bit:
case SqlColumnType.@decimal:
case SqlColumnType.@numeric:
case SqlColumnType.@smallmoney:
case SqlColumnType.@bigint:
if (columnInfo.Nullable)
{
text = "null";
}
else
{
text = "0";
}
break;
case SqlColumnType.@date:
case SqlColumnType.@datetime2:
case SqlColumnType.@time:
case SqlColumnType.@datetimeoffset:
case SqlColumnType.@smalldatetime:
case SqlColumnType.@datetime:
text = "null";
break;
case SqlColumnType.@image:
case SqlColumnType.@varbinary:
case SqlColumnType.@binary:
if (columnInfo.Nullable)
{
text = "null";
}
else
{
text = "0x";
}
break;
case SqlColumnType.@uniqueidentifier:
if (columnInfo.Nullable)
{
text = "null";
}
else
{
text = "'00000000-0000-0000-0000-000000000000'";
}
break;
default:
throw new NotSupportedException();
}
}
else
{
switch (columnInfo.Type)
{
case SqlColumnType.@text:
case SqlColumnType.@varchar:
case SqlColumnType.@char:
text = $"'{value}'";
break;
case SqlColumnType.@ntext:
case SqlColumnType.@nvarchar:
case SqlColumnType.@nchar:
case SqlColumnType.@xml:
text = $"N'{value}'";
break;
case SqlColumnType.@bit:
text = $"{Convert.ToInt32(Convert.ToBoolean(value))}";
break;
case SqlColumnType.@tinyint:
case SqlColumnType.@smallint:
case SqlColumnType.@int:
case SqlColumnType.@real:
case SqlColumnType.@money:
case SqlColumnType.@float:
case SqlColumnType.@decimal:
case SqlColumnType.@numeric:
case SqlColumnType.@smallmoney:
case SqlColumnType.@bigint:
text = $"{value}";
break;
case SqlColumnType.@date:
case SqlColumnType.@datetime2:
case SqlColumnType.@time:
case SqlColumnType.@datetimeoffset:
case SqlColumnType.@smalldatetime:
case SqlColumnType.@datetime:
text = $"'{value}'";
break;
case SqlColumnType.@image:
case SqlColumnType.@varbinary:
case SqlColumnType.@binary:
text = $"{value}";
break;
case SqlColumnType.@uniqueidentifier:
text = $"'{value}'";
break;
default:
throw new NotSupportedException();
}
}
command = Regex.Replace(command, $@"@{columnInfo.Name}\b", (m) =>
{
return(text);
});
}
builder.AppendLine(command);
}
builder.AppendLine($"IF EXISTS (SELECT 1 FROM sys.identity_columns WHERE object_id = object_id('{tableName}')) SET IDENTITY_INSERT [{tableName}] OFF");
}
}
return(builder.ToString());
}
