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