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