/// <summary>
/// 检测数据库中对象是否存在
/// </summary>
/// <param name="name">对象名称</param>
/// <param name="type">对象类别</param>
/// <returns></returns>
public bool ExistsObject(string name, DBObjectType type)
{
LibDataAccess dataAccess = new LibDataAccess();
string sql = "";
switch (type)
{
case DBObjectType.Table:
sql = string.Format(@"select count(TABLE_NAME) FROM all_tables WHERE TABLE_NAME = '{0}'",
name);
break;
case DBObjectType.Column:
string[] nameArrar = name.Split('.');
sql = string.Format(@"select column_name from user_tab_columns where table_name=upper('{0}') and column_name=upper('{0}')", nameArrar[0], nameArrar[1]);
break;
default:
break;
}
string _count = dataAccess.ExecuteScalar(sql).ToString();
return(_count == "0" ? false : true);
}
/// <summary>
/// 检测数据库中对象是否存在
/// </summary>
/// <param name="name">对象名称</param>
/// <param name="type">对象类别</param>
/// <returns></returns>
public bool ExistsObject(string name, DBObjectType type)
{
LibDataAccess dataAccess = new LibDataAccess();
string sql = "";
switch (type)
{
case DBObjectType.Table:
sql = string.Format(@"SELECT name FROM sys.objects WHERE object_id = OBJECT_ID(N'[{0}]')
AND type in (N'U')", name);
break;
case DBObjectType.Column:
string[] nameArrar = name.Split('.');
sql = string.Format(@"SELECT name FROM sys.syscolumns WHERE name='{0}' and id=(SELECT MAX(id) FROM sys.objects WHERE object_id = OBJECT_ID(N'[{1}]')
AND type in (N'U'))", nameArrar[1], nameArrar[0]);
break;
default:
break;
}
string _name = dataAccess.ExecuteScalar(sql) as string;
return(string.IsNullOrEmpty(_name) ? false : true);
}
public void UpdateTable(DataTable table, bool isDelete)
{
LibDataAccess dataAccess = new LibDataAccess();
decimal count = (decimal)dataAccess.ExecuteScalar(string.Format("SELECT COUNT(*) FROM all_tables WHERE TABLE_NAME = {0}", LibStringBuilder.GetQuotString(table.TableName)));
if (count == 0)
{
CreateTable(table);
}
else
{
DataTable dtSchema = null;
using (DbConnection conn = dataAccess.CreateConnection())
{
conn.Open();
dtSchema = conn.GetSchema("Columns", new string[] { null, table.TableName });
}
if (dtSchema != null)
{
Dictionary <string, DbFieldInfo> defaultDic = GetDbFieldInfo(dataAccess, table.TableName);
//对删除的非聚集索引进行处理
StringBuilder tempBuilder = new StringBuilder();
List <string> deleteIndexList = new List <string>();
List <string> addIndexList = new List <string>();
DBIndexCollection dbIndex = table.ExtendedProperties[TableProperty.DBIndex] as DBIndexCollection;
CompareDBIndex(table.TableName, dbIndex, addIndexList, deleteIndexList);
if (deleteIndexList.Count > 0)
{
foreach (var item in deleteIndexList)
{
dataAccess.ExecuteNonQuery(item);
}
}
//再对列进行处理
Dictionary <string, bool> dic = new Dictionary <string, bool>();
foreach (DataColumn col in table.Columns)
{
FieldType fieldType = FieldType.None;
if (col.ExtendedProperties.ContainsKey(FieldProperty.FieldType))
{
fieldType = (FieldType)col.ExtendedProperties[FieldProperty.FieldType];
}
if (fieldType == FieldType.None)
{
dic.Add(col.ColumnName, false);
}
}
foreach (DataRow row in dtSchema.Rows)
{
string columnName = row["COLUMN_NAME"].ToString();
bool isFind = false;
foreach (DataColumn col in table.Columns)
{
if (!dic.ContainsKey(col.ColumnName))
{
continue;
}
if (string.Compare(col.ColumnName, columnName, true) == 0)
{
StringBuilder strBuilder = new StringBuilder();
string dataType = row["DATATYPE"].ToString();
bool hasDiff = HasFieldChanged(col, dataType, defaultDic);
if (hasDiff)
{
tempBuilder.AppendLine(string.Format("execute immediate 'alter table {0} modify({1})';", table.TableName, GetFieldInfo(col, true)));
}
isFind = true;
dic[col.ColumnName] = true;
break;
}
}
if (isDelete && !isFind)
{
tempBuilder.AppendLine(string.Format("execute immediate 'alter table {0} drop column {1}';", table.TableName, columnName));
}
}
foreach (var item in dic)
{
if (!item.Value)
{
DataColumn col = table.Columns[item.Key];
tempBuilder.AppendLine(string.Format("execute immediate 'alter table {0} add({1})';", table.TableName, GetFieldInfo(col, true)));
}
}
if (tempBuilder.Length > 0)
{
StringBuilder testBuild = new StringBuilder();
testBuild.AppendLine("begin");
testBuild.Append(tempBuilder.ToString());
testBuild.AppendLine("end;");
dataAccess.ExecuteNonQuery(testBuild.ToString());
}
//如果主键异动，先删除聚集索引
bool isPkChange = IsPkChange(table.TableName, table.PrimaryKey);
//先执行对于具有唯一性约束的字段的数据更新处理Sql
if (string.IsNullOrEmpty(this.UniqueDataSql) == false && (isPkChange || addIndexList.Count > 0))
{
dataAccess.ExecuteNonQuery(this.UniqueDataSql);
}
//对主键的进行标识
if (isPkChange)
{
string pkStr = GetPkStr(table);
if (!string.IsNullOrEmpty(LibSysUtils.ToString(dataAccess.ExecuteScalar(string.Format("select constraint_name from dba_constraints where constraint_name = 'PK_{0}'", table.TableName)))))
{
dataAccess.ExecuteNonQuery(string.Format("alter table {0} drop constraint PK_{0}", table.TableName));
}
//一般情况下如果删除主键约束，索引会自动删除。但是目前有存在未删除的情况，所以确保删除
if (!string.IsNullOrEmpty(LibSysUtils.ToString(dataAccess.ExecuteScalar(string.Format("select * from user_ind_columns where INDEX_NAME='PK_{0}'", table.TableName)))))
{
dataAccess.ExecuteNonQuery(string.Format("drop index PK_{0}", table.TableName));
}
dataAccess.ExecuteNonQuery(string.Format("alter table {0} add constraint PK_{0} primary key ({1})", table.TableName, pkStr));
}
//对表的新增非聚集索引进行处理
foreach (var item in addIndexList)
{
dataAccess.ExecuteNonQuery(item);
}
}
}
}
public void UpdateTable(TableSchema schema, bool isDelete)
{
DataTable table = null;
LibDataAccess dataAccess = new LibDataAccess();
int count = (int)dataAccess.ExecuteScalar(string.Format("select count(*) from sysobjects where id = object_id({0})", LibStringBuilder.GetQuotString(schema.Name)));
if (count == 0)
{
CreateTable(schema);
}
else
{
using (DbConnection conn = dataAccess.CreateConnection())
{
conn.Open();
table = conn.GetSchema("Columns", new string[] { null, null, schema.Name, null });
}
if (table != null)
{
List <string> sql = new List <string>();
List <string> sqlSecond = new List <string>();//第二批执行的sql语句，一般在数据列加完以后执行唯一约束、主键等设置
//如果主键异动，先删除聚集索引
bool isPkChange = IsPkChange(schema.Name, schema.PRIMARY_KEY);
if (isPkChange)
{
sql.Add(GetDropPkConstraintSql(schema.Name));
}
//对删除的非聚集索引进行处理
List <string> deleteIndexList = new List <string>();
List <string> addIndexList = new List <string>();
CompareDBIndex(schema.Name, schema.DBIndexs, addIndexList, deleteIndexList);
foreach (var item in deleteIndexList)
{
sql.Add(item);
}
//再对列进行处理
Dictionary <string, bool> dic = new Dictionary <string, bool>(StringComparer.OrdinalIgnoreCase);
foreach (ColumnSchema col in schema.ColumnSchemaList)
{
dic.Add(col.Name, false);
}
foreach (DataRow row in table.Rows)
{
string columnName = row["COLUMN_NAME"].ToString();
string dataType = row["DATA_TYPE"].ToString();
int size = LibSysUtils.ToInt32(row["CHARACTER_MAXIMUM_LENGTH"]);
int digit = LibSysUtils.ToInt32(row["NUMERIC_SCALE"]);
string defualtValue = LibSysUtils.ToString(row["COLUMN_DEFAULT"]);
bool isFind = false;
foreach (ColumnSchema col in schema.ColumnSchemaList)
{
if (string.Compare(col.Name, columnName, true) == 0)
{
StringBuilder strBuilder = new StringBuilder();
if (string.Compare(col.ColumnType, dataType, true) != 0 ||
(col.Size > 0 && col.Size != size) || col.Digit != digit)
{
foreach (var item in schema.PRIMARY_KEY)
{
if (string.Compare(col.Name, item, true) == 0)
{
if (!isPkChange)
{
isPkChange = true;
sql.Add(GetDropPkConstraintSql(schema.Name));
}
break;
}
}
//删除默认值约束
strBuilder.Append(GetDropDefaultValueCheckSql(schema.Name, columnName));
//修改字段
strBuilder.AppendLine("ALTER TABLE [dbo].[{0}] ALTER column {1} ");
//新建默认值约束
strBuilder.AppendFormat("ALTER TABLE [dbo].[{0}] ADD {1} FOR [{2}]", schema.Name, col.DefaultValueStr, columnName);
sql.Add(string.Format(strBuilder.ToString(), schema.Name, col.ColumnTypeStr));
}
else if (string.Compare(string.Format(col.DefaultValue), defualtValue) != 0)
{
foreach (var item in schema.PRIMARY_KEY)
{
if (string.Compare(col.Name, item, true) == 0)
{
if (!isPkChange)
{
isPkChange = true;
sql.Add(GetDropPkConstraintSql(schema.Name));
}
break;
}
}
//删除默认值约束
strBuilder.Append(GetDropDefaultValueCheckSql(schema.Name, columnName));
//新建默认值约束
strBuilder.AppendFormat("ALTER TABLE [dbo].[{0}] ADD {1} FOR [{2}]", schema.Name, col.DefaultValueStr, columnName);
sql.Add(string.Format(strBuilder.ToString(), schema.Name, columnName));
}
isFind = true;
dic[columnName] = true;
break;
}
}
if (isDelete && !isFind)
{
//原先存在。现在不存在，则考虑删除
//有默认约束依赖该字段，先删除默认约束，再删除字段
//删除默认值约束
StringBuilder strBuilder = new StringBuilder();
strBuilder.Append(GetDropDefaultValueCheckSql(schema.Name, columnName));
//删除字段
strBuilder.AppendLine(string.Format("ALTER TABLE [dbo].[{0}] DROP [{1}] {2}", schema.Name, columnName));
sql.Add(strBuilder.ToString());
}
}
foreach (var item in dic)
{
if (!item.Value)
{
StringBuilder strBuilder = new StringBuilder();
ColumnSchema col = schema.ColumnSchemaList.Find(c => c.Name == item.Key);
//新增列
strBuilder.AppendLine(string.Format("ALTER TABLE [dbo].[{0}] ADD {1}", schema.Name, col.ColumnTypeStr));
//新增列默认值约束
strBuilder.Append(col.DefaultValueStr);
sql.Add(strBuilder.ToString());
}
}
//第二批执行的是索引、主键、唯一约束等
//先添加对于具有唯一性约束的字段的数据更新处理Sql，以便其先执行
if (string.IsNullOrEmpty(this.UniqueDataSql) == false && (isPkChange || addIndexList.Count > 0))
{
sqlSecond.Add(this.UniqueDataSql);
}
//对主键的进行标识
if (isPkChange)
{
sqlSecond.Add(GetAddPkConstraintSql(schema.Name, schema.PRIMARY_KEY));
}
//对表的新增非聚集索引进行处理
foreach (var item in addIndexList)
{
sqlSecond.Add(item);
}
if (sql.Count > 0 || sqlSecond.Count > 0)
{
StringBuilder sqlBuilder = new StringBuilder();
foreach (var item in sql)
{
sqlBuilder.Append(item);
}
StringBuilder sqlBuilderSecond = new StringBuilder();
foreach (var item in sqlSecond)
{
sqlBuilderSecond.Append(item);
}
//提交更新
LibDBTransaction tran = dataAccess.BeginTransaction();
try
{
if (sqlBuilder.Length > 0)
{
dataAccess.ExecuteNonQuery(sqlBuilder.ToString());
}
//第二批执行的是索引、主键、唯一约束等
if (sqlBuilderSecond.Length > 0)
{
dataAccess.ExecuteNonQuery(sqlBuilderSecond.ToString());
}
tran.Commit();
}
catch (Exception exp)
{
LibCommUtils.AddOutput(@"Update", string.Format("升级数据表异常。\r\nsql:{0}\r\n异常:{1}\r\nStackTrace:{2}",
sqlBuilder.ToString(), exp.Message, exp.StackTrace));
tran.Rollback();
}
}
}
}
}