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 bool TypeExists(string name, DbConnectionProvider connectionProvider) { var strComm = string.Format("Select * From [sys].[types] Where [name] = '{0}Type';", name); return(connectionProvider.ExecuteLines(strComm) > 0); }
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 bool TableExists(ISchemaTable table, DbConnectionProvider connectionProvider) { var str = string.Format("Select * From [sys].[tables] Where [name] = '{0}'", table.Name); return(connectionProvider.ExecuteLines(str) > 0); }
private static bool ViewExists(IEntitySchema view, DbConnectionProvider connectionProvider) { var strComm = string.Format("Select * From [sys].[views] Where [object_id] = Object_Id('[v_{0}]');", view.Name); return(connectionProvider.ExecuteLines(strComm) > 0); }
public static bool CheckProcedureChanged(IEntitySchema view, ProcedureUsage usage, 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 InvalidOperationException("DbProvider key:" + view.ConnectKey + ", read only!"); } if (ProcedureExists(view, usage, connectionProvider)) { var commandText = GetProcedureCommand(view, ProcedureOperate.Alter, usage); connectionProvider.ExecuteNonQuery(commandText); } else { var commandText = GetProcedureCommand(view, ProcedureOperate.Create, usage); connectionProvider.ExecuteNonQuery(commandText); } return(true); }
private static bool ProcedureExists(IEntitySchema view, ProcedureUsage usage, DbConnectionProvider connectionProvider) { var proceuredName = usage == ProcedureUsage.Insert || usage == ProcedureUsage.Update ? string.Format("{0}{1}", usage.ToString(), view.Name) : string.Format("{0}{1}Type", usage.ToString().TrimStart("Type".ToArray()), view.Name); var strComm = string.Format("Select * From sysobjects Where [xtype] = 'p' And [name] = '{0}'", proceuredName); return(connectionProvider.ExecuteLines(strComm) > 0); }