public static bool CheckViewChanged(IEntitySchema view, DbConnectionProvider connectionProvider)
{
if (view == null)
{
throw new ArgumentNullException(nameof(view));
}
if (connectionProvider == null)
{
throw new ArgumentNullException(nameof(connectionProvider));
}
if (connectionProvider.AccessLevel == AccessLevel.ReadOnly)
{
throw new ArgumentException("DbProvider key:" + view.ConnectKey + ", read only!");
}
var hasChanged = false;
if (view.Attributes.HasFlag(EntitySchemaAttributes.CreateView))
{
if (!ViewExists(view, connectionProvider))
{
var commandText = GetViewCommand(view, ProcedureOperate.Create);
connectionProvider.ExecuteNonQuery(commandText);
hasChanged = true;
}
else if (view.Attributes.HasFlag(EntitySchemaAttributes.AlterView))
{
var dbColumns = GetDbColumnList(connectionProvider, string.Format("v_{0}", view.Name));
var schemaColumns = view.Columns;
if (!dbColumns.All(p => schemaColumns.Any(t =>
p.Name.Equals(t.Name, StringComparison.CurrentCultureIgnoreCase) &&
p.DbType.Equals(MsSqlHelper.GetDbTypeString(t), StringComparison.CurrentCultureIgnoreCase))) ||
!schemaColumns.All(p => dbColumns.Any(t =>
p.Name.Equals(t.Name, StringComparison.CurrentCultureIgnoreCase) &&
MsSqlHelper.GetDbTypeString(p).Equals(t.DbType, StringComparison.CurrentCultureIgnoreCase))))
{
var commandText = GetViewCommand(view, ProcedureOperate.Alter);
connectionProvider.ExecuteNonQuery(commandText);
hasChanged = true;
}
}
}
return(hasChanged);
}
private static string GetColumnCommand(ISchemaTable table, ISchemaColumn column)
{
var builder = new StringBuilder();
builder.AppendFormat("[{0}] {1}", column.Name, MsSqlHelper.GetDbTypeString(column));
if (column.IsIdentity)
{
builder.AppendFormat(" Identity({0}, {1})", column.IdentitySeed, column.Increment);
}
if (column.IsPrimary || !column.IsNullable)
{
builder.Append(" Not");
}
builder.Append(" Null");
if (!string.IsNullOrEmpty(column.DefaultValue))
{
builder.AppendFormat(" Constraint [DF_{0}_{1}] Default ({2})", table.Name, column.Name, column.DefaultValue);
}
return(builder.ToString());
}
public static string GetCreateTypeCommand(IEntitySchema view)
{
if (view == null)
{
throw new ArgumentNullException(nameof(view));
}
var columns = view.Columns;
if (columns.Count(p => p.IsPrimary) != 1)
{
throw new ArgumentException("primary error!");
}
var builder = new StringBuilder();
builder.AppendFormat("Create Type [dbo].[{0}Type] As Table", view.Name);
builder.AppendLine();
builder.AppendLine("(");
foreach (var column in columns.OrderByDescending(p => p.IsPrimary).ThenBy(p => p.Order))
{
if (column.Mode == ColumnMode.ReadOnly)
{
continue;
}
builder.AppendFormat(" [{0}] {1}", column.Name, MsSqlHelper.GetDbTypeString(column));
if (column.IsPrimary || !column.IsNullable)
{
builder.Append(" Not");
}
builder.AppendLine(" Null,");
}
var primaryColumn = columns.First(p => p.IsPrimary);
builder.AppendFormat(" Primary Key ([{0}])", primaryColumn.Name);
builder.AppendLine();
builder.AppendLine(")");
return(builder.ToString());
}
public static bool CheckTableChanged(ISchemaTable table, DbConnectionProvider connectionProvider)
{
if (table == null)
{
throw new ArgumentNullException(nameof(table));
}
if (connectionProvider == null)
{
throw new ArgumentNullException(nameof(connectionProvider));
}
if (connectionProvider.AccessLevel == AccessLevel.ReadOnly)
{
throw new InvalidOperationException("DbProvider key:" + table.Schema.ConnectKey + ", read only!");
}
bool hasChanged = false;
if (table.Schema.Attributes.HasFlag(EntitySchemaAttributes.CreateTable))
{
if (TableExists(table, connectionProvider))
{
if (table.Schema.Attributes.HasFlag(EntitySchemaAttributes.CreateColumn) ||
table.Schema.Attributes.HasFlag(EntitySchemaAttributes.AlterColumn) ||
table.Schema.Attributes.HasFlag(EntitySchemaAttributes.DropColumn))
{
var dbColumns = GetDbColumnList(connectionProvider, table.Name);
var columns = table.Columns;
if (table.Schema.Attributes.HasFlag(EntitySchemaAttributes.DropColumn))
{
foreach (var dbColumn in dbColumns)
{
if (!columns.Any(p => p.Name.Equals(dbColumn.Name, StringComparison.CurrentCultureIgnoreCase)))
{
var commandText = string.Format("Alter Table [{0}] Drop Column [{1}]",
table.Name, dbColumn.Name);
connectionProvider.ExecuteNonQuery(commandText);
hasChanged = true;
}
}
}
foreach (var column in columns)
{
var dbColumn = dbColumns.FirstOrDefault(p => p.Name.Equals(column.Name, StringComparison.CurrentCultureIgnoreCase));
if (dbColumn == null)
{
if (table.Schema.Attributes.HasFlag(EntitySchemaAttributes.CreateColumn))
{
var commandText = string.Format("Alter Table [{0}] Add [{1}] {2}",
table.Name, column.Name, MsSqlHelper.GetDbTypeString(column));
connectionProvider.ExecuteNonQuery(commandText);
hasChanged = true;
}
}
else if (!dbColumn.DbType.Equals(MsSqlHelper.GetDbTypeString(column), StringComparison.CurrentCultureIgnoreCase) && table.Schema.Attributes.HasFlag(EntitySchemaAttributes.AlterColumn))
{
var commandText = string.Format("Alter Table [{0}] Alter Column [{1}] {2}",
table.Name, column.Name, MsSqlHelper.GetDbTypeString(column));
connectionProvider.ExecuteNonQuery(commandText);
hasChanged = true;
}
}
}
}
else
{
var commandText = GetCreateTableCommand(table);
connectionProvider.ExecuteNonQuery(commandText);
hasChanged = true;
}
}
return(hasChanged);
}
private static string GetProcedureUpdateCommand(ProcedureOperate operate, IEntitySchema view)
{
var builder = new StringBuilder();
builder.AppendFormat("{0} Procedure [dbo].[Update{1}]", operate.ToString(), view.Name);
builder.AppendLine();
var viewColumns = view.Columns;
var validColumns = viewColumns.Where(p => p.Mode != ColumnMode.ReadOnly).ToList();
if (validColumns.Count(p => p.IsPrimary) != 1)
{
throw new ArgumentException("view primary error!");
}
var primaryColumn = validColumns.First(p => p.IsPrimary);
validColumns.RemoveAll(p => !p.IsPrimary && p.IsIdentity);
if (validColumns.Count > 1)
{
var firstColumn = validColumns.First();
builder.AppendFormat(" @{0} {1}", firstColumn.Name, MsSqlHelper.GetDbTypeString(firstColumn));
if (!string.IsNullOrEmpty(firstColumn.DefaultValue))
{
builder.AppendFormat(" = {0}", firstColumn.DefaultValue);
}
}
for (int i = 1; i < validColumns.Count; i++)
{
var column = validColumns[i];
builder.AppendLine(",");
builder.AppendFormat(" @{0} {1}", column.Name, MsSqlHelper.GetDbTypeString(column));
if (!string.IsNullOrEmpty(column.DefaultValue))
{
builder.AppendFormat(" = {0}", column.DefaultValue);
}
}
builder.AppendLine();
builder.AppendLine("As");
builder.AppendLine("Begin");
builder.AppendLine(" Set NoCount ON;");
builder.AppendLine();
var builderColumn = new StringBuilder();
validColumns.Remove(primaryColumn);
var groupColumnList = validColumns.Where(p => !p.IsPrimary).GroupBy(p => p.Table).ToList();
foreach (var groupColumn in groupColumnList)
{
var tableColumnList = groupColumn.ToList();
if (tableColumnList.Count > 0)
{
var firstColumn = tableColumnList.First();
builderColumn.AppendFormat("[{0}] = @{0}", firstColumn.Name);
}
for (int i = 1; i < tableColumnList.Count; i++)
{
var column = tableColumnList[i];
builderColumn.AppendLine();
builderColumn.AppendFormat(" ,[{0}] = @{0}", column.Name);
}
builder.AppendFormat(" Update [dbo].[{0}]", groupColumn.Key);
builder.AppendLine();
builder.Append(" Set ");
builder.AppendLine(builderColumn.ToString());
builder.AppendFormat(" Where [{0}] = @{0};", primaryColumn.Name);
builder.AppendLine();
builder.AppendLine();
builderColumn.Clear();
}
builder.AppendLine("End");
return(builder.ToString());
}
private static string GetProcedureInsertCommand(ProcedureOperate operate, IEntitySchema view)
{
var builder = new StringBuilder();
builder.AppendFormat("{0} Procedure [dbo].[Insert{1}]", operate.ToString(), view.Name);
builder.AppendLine();
var viewColumns = view.Columns;
var viewColumnList = viewColumns.Where(p => p.Mode != ColumnMode.ReadOnly && !p.IsIdentity).ToList();
if (viewColumnList.Count > 1)
{
var firstColumn = viewColumnList.First();
builder.AppendFormat(" @{0} {1}", firstColumn.Name, MsSqlHelper.GetDbTypeString(firstColumn));
if (!string.IsNullOrEmpty(firstColumn.DefaultValue))
{
builder.AppendFormat(" = {0}", firstColumn.DefaultValue);
}
}
for (int i = 1; i < viewColumnList.Count; i++)
{
var column = viewColumnList[i];
builder.AppendLine(",");
builder.AppendFormat(" @{0} {1}", column.Name, MsSqlHelper.GetDbTypeString(column));
if (!string.IsNullOrEmpty(column.DefaultValue))
{
builder.AppendFormat(" = {0}", column.DefaultValue);
}
}
builder.AppendLine();
builder.AppendLine("As");
builder.AppendLine("Begin");
builder.AppendLine(" Set NoCount ON;");
builder.AppendLine();
var builderParam = new StringBuilder();
var builderColumn = new StringBuilder();
foreach (var table in view.Tables)
{
var aryTableColumn = table.Columns;
var tableColumnList = aryTableColumn.Where(p => p.CanRead && p.Mode != ColumnMode.ReadOnly && !p.IsIdentity).ToList();
if (tableColumnList.Count > 0)
{
var firstColumn = tableColumnList.First();
builderColumn.AppendFormat("[{0}]", firstColumn.Name);
builderParam.AppendFormat("@{0}", firstColumn.Name);
}
for (int i = 1; i < tableColumnList.Count; i++)
{
var column = tableColumnList[i];
builderColumn.AppendLine();
builderColumn.AppendFormat(" ,[{0}]", column.Name);
builderParam.AppendLine();
builderParam.AppendFormat(" ,@{0}", column.Name);
}
builder.AppendFormat(" Insert Into [dbo].[{0}]", table.Name);
builder.AppendLine();
builder.Append(" (");
builder.AppendLine(builderColumn.ToString());
builder.AppendLine(" ) Values");
builder.Append(" (");
builder.AppendLine(builderParam.ToString());
builder.AppendLine(" );");
builder.AppendLine();
builderColumn.Clear();
builderParam.Clear();
}
builder.AppendLine("End");
return(builder.ToString());
}
static void ColumnFormatter(DbConnectionProvider provider, ISchemaColumn column, StringBuilder builder)
{
builder.AppendFormat(" {0} {1}", provider.NormalizeSymbol(column.Name), MsSqlHelper.GetDbTypeString(column));
if (column.IsPrimary)
{
builder.Append(" Not Null");
}
else
{
builder.Append(" Null");
}
}
