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