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