/// <summary>
/// 判断主键是否有异动
/// </summary>
/// <param name="tableName"></param>
/// <param name="pks"></param>
/// <returns></returns>
private bool IsPkChange(string tableName, DataColumn[] pks)
{
bool change = false;
LibDataAccess dataAccess = new LibDataAccess();
HashSet <string> dbPks = new HashSet <string>();
using (IDataReader reader = dataAccess.ExecuteDataReader(string.Format("select cu.COLUMN_NAME from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' and au.table_name = '{0}'", tableName)))
{
while (reader.Read())
{
string colName = reader["COLUMN_NAME"].ToString();
dbPks.Add(colName);
}
}
if (pks.Length == dbPks.Count)
{
foreach (var item in pks)
{
if (!dbPks.Contains(item.ColumnName))
{
change = true;
break;
}
}
}
else
{
change = true;
}
//主键是否变更
return(change);
}
/// <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);
}
/// <summary>
/// 获取表的索引
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
private Dictionary <string, DBIndexInfo> GetDBIndex(string tableName)
{
Dictionary <string, DBIndexInfo> dic = new Dictionary <string, DBIndexInfo>();
string sql = string.Format("select t.INDEX_NAME as IndexName,t.DESCEND as IsDesc,t.COLUMN_NAME as ColumnName,i.uniqueness as IsUnique from user_ind_columns t,user_indexes i where t.index_name = i.index_name and " +
"t.table_name='{0}'", tableName);
string pkIndex = string.Format("PK_{0}", tableName);
LibDataAccess dataAccess = new LibDataAccess();
using (IDataReader reader = dataAccess.ExecuteDataReader(sql))
{
while (reader.Read())
{
string idxName = reader["IndexName"].ToString();
if (string.Compare(pkIndex, idxName, true) == 0)
{
continue;
}
bool isUnique = Convert.ToString(reader["IsUnique"]) == "UNIQUE";
IndexOrderWay orderWay = Convert.ToString(reader["IsDesc"]) == "ASC" ? IndexOrderWay.ASC : IndexOrderWay.DESC;
string colName = reader["ColumnName"].ToString();
if (!dic.ContainsKey(idxName))
{
DBIndexInfo indexs = new DBIndexInfo();
indexs.IsUnique = isUnique;
dic.Add(idxName, indexs);
}
DBIndexInfo dbIndexs = dic[idxName];
DBIndexField indexFiled = new DBIndexField(colName, orderWay);
dbIndexs.IndexFields.Add(indexFiled);
}
}
return(dic);
}
/// <summary>
/// 判断主键是否有异动
/// </summary>
/// <param name="tableName"></param>
/// <param name="pks"></param>
/// <returns></returns>
private bool IsPkChange(string tableName, string[] pks)
{
bool change = false;
HashSet <string> dbPks = new HashSet <string>();
LibDataAccess dataAccess = new LibDataAccess();
using (IDataReader reader = dataAccess.ExecuteDataReader(string.Format("EXEC sp_pkeys @table_name='{0}'", tableName)))
{
while (reader.Read())
{
string colName = reader["COLUMN_NAME"].ToString();
dbPks.Add(colName);
}
}
if (pks.Length == dbPks.Count)
{
foreach (var item in pks)
{
if (!dbPks.Contains(item))
{
change = true;
break;
}
}
}
else
{
change = true;
}
//主键是否变更
return(change);
}
/// <summary>
/// 获取表的索引
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
private Dictionary <string, DBIndexInfo> GetDBIndex(string tableName)
{
Dictionary <string, DBIndexInfo> dic = new Dictionary <string, DBIndexInfo>();
string sql = string.Format("SELECT idx.name as IndexName,idx.is_unique as IsUnique,idxCol.is_descending_key as IsDesc,col.name as ColumnName " +
"FROM sys.indexes idx JOIN sys.index_columns idxCol " +
"ON (idx.object_id = idxCol.object_id " +
"AND idx.index_id = idxCol.index_id) " +
"JOIN sys.tables tab ON (idx.object_id = tab.object_id) " +
"JOIN sys.columns col ON (idx.object_id = col.object_id " +
"AND idxCol.column_id = col.column_id) " +
"where tab.name='{0}' and idx.is_primary_key<>1 " +
"order by idx.index_id ", tableName);
LibDataAccess dataAccess = new LibDataAccess();
using (IDataReader reader = dataAccess.ExecuteDataReader(sql))
{
while (reader.Read())
{
string idxName = reader["IndexName"].ToString();
bool isUnique = Convert.ToBoolean(reader["IsUnique"]);
IndexOrderWay orderWay = Convert.ToBoolean(reader["IsDesc"]) ? IndexOrderWay.DESC : IndexOrderWay.ASC;
string colName = reader["ColumnName"].ToString();
if (!dic.ContainsKey(idxName))
{
DBIndexInfo indexs = new DBIndexInfo();
indexs.IsUnique = isUnique;
dic.Add(idxName, indexs);
}
DBIndexInfo dbIndexs = dic[idxName];
DBIndexField indexFiled = new DBIndexField(colName, orderWay);
dbIndexs.IndexFields.Add(indexFiled);
}
}
return(dic);
}
/// <summary>
/// Drop the table from the database.
/// </summary>
/// <param name="tableName"></param>
public void DropTable(string tableName)
{
string checkDelete = GetDropTable(tableName, false);
LibDataAccess dataAccess = new LibDataAccess();
dataAccess.ExecuteNonQuery(checkDelete);
}
public void CreateTable(TableSchema schema)
{
StringBuilder pkStr = new StringBuilder();
foreach (string item in schema.PRIMARY_KEY)
{
pkStr.AppendFormat("[{0}] ASC,", item);
}
StringBuilder columnStr = new StringBuilder();
StringBuilder defaultValueStr = new StringBuilder();
StringBuilder indexStr = new StringBuilder();
foreach (ColumnSchema item in schema.ColumnSchemaList)
{
columnStr.AppendLine(string.Format("{0},", item.ColumnTypeStr));
if (!string.IsNullOrEmpty(item.DefaultValueStr))
{
defaultValueStr.AppendLine(string.Format("ALTER TABLE [dbo].[{0}] ADD {1} FOR [{2}]", schema.Name, item.DefaultValueStr, item.Name));
}
}
foreach (string item in schema.DBIndexStr)
{
indexStr.AppendLine(item);
}
if (columnStr.Length > 0)
{
columnStr.Remove(columnStr.Length - 1, 1);
}
if (pkStr.Length > 0)
{
pkStr.Remove(pkStr.Length - 1, 1);
}
StringBuilder strBuilder = new StringBuilder();
//strBuilder.AppendLine(string.Format("USE [{0}]", InitialCatalog));
strBuilder.AppendLine("SET ANSI_NULLS ON ");
strBuilder.AppendLine("SET QUOTED_IDENTIFIER ON ");
strBuilder.AppendLine("SET ANSI_PADDING ON ");
strBuilder.AppendLine(string.Format("CREATE TABLE [dbo].[{0}]({1}", schema.Name, columnStr));
strBuilder.AppendLine(string.Format("CONSTRAINT [PK_{0}] PRIMARY KEY CLUSTERED ", schema.Name));
strBuilder.AppendLine(string.Format("({0}) ", pkStr));//主键
strBuilder.AppendLine("WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF," +
" ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]");
strBuilder.AppendLine("SET ANSI_PADDING OFF ");
//添加默认值
strBuilder.Append(defaultValueStr.ToString());
//添加索引
strBuilder.Append(indexStr);
LibDataAccess dataAccess = new LibDataAccess();
dataAccess.ExecuteNonQuery(strBuilder.ToString());
}
/// <summary>
/// Drop the table from the database.
/// </summary>
/// <param name="tableName"></param>
public void DropTable(string tableName)
{
string drop = string.Format(@"declare num number;
begin
select count(TABLE_NAME) into num FROM all_tables WHERE TABLE_NAME = '{0}';
if num>0 then
execute immediate 'drop table {0}';
end if;
end;", tableName);
LibDataAccess dataAccess = new LibDataAccess();
dataAccess.ExecuteNonQuery(drop);
}
private Dictionary <string, DbFieldInfo> GetDbFieldInfo(LibDataAccess dataAccess, string tableName)
{
Dictionary <string, DbFieldInfo> dic = new Dictionary <string, DbFieldInfo>();
using (IDataReader reader = dataAccess.ExecuteDataReader(string.Format("select column_name,data_default,char_length from all_tab_columns where table_name='{0}'", tableName)))
{
do
{
while (reader.Read())
{
if (!dic.ContainsKey(LibSysUtils.ToString(reader[0])))
{
DbFieldInfo info = new DbFieldInfo(LibSysUtils.ToString(reader[1]), LibSysUtils.ToInt32(reader[2]));
dic.Add(LibSysUtils.ToString(reader[0]), info);
}
}
} while (reader.NextResult());
}
return(dic);
}
public void CreateTable(DataTable table)
{
string tableName = table.TableName;
StringBuilder builder = new StringBuilder();
builder.AppendFormat("CREATE TABLE {0}(", tableName);
foreach (DataColumn col in table.Columns)
{
FieldType fieldType = col.ExtendedProperties.ContainsKey(FieldProperty.FieldType) ? (FieldType)col.ExtendedProperties[FieldProperty.FieldType] : FieldType.None;
if (fieldType == FieldType.None)
{
builder.AppendFormat("{0},", GetFieldInfo(col));
}
}
string pkStr = GetPkStr(table);
builder.AppendLine(string.Format("constraints PK_{0} primary key({1})", tableName, pkStr));
builder.Append(")");
LibDataAccess dataAccess = new LibDataAccess();
dataAccess.ExecuteNonQuery(builder.ToString());
List <string> sqlList = new List <string>();
DBIndexCollection dbIndexes = table.ExtendedProperties.ContainsKey(TableProperty.DBIndex) ? (DBIndexCollection)table.ExtendedProperties[TableProperty.DBIndex] : null;
if (dbIndexes != null)
{
foreach (DBIndex item in dbIndexes)
{
sqlList.Add(CreateIndexSql(item, tableName));
}
}
if (sqlList.Count > 0)
{
foreach (var sql in sqlList)
{
dataAccess.ExecuteNonQuery(sql);
}
}
}
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();
}
}
}
}
}
