public List <DBIndexInfo> GetIndexs(string Server, string DB, string Table)
{
var Indexs = new List <DBIndexInfo>();
var sql = string.Format(@"SELECT a.name AS IndexName ,a.type_desc AS IndexDes ,a.is_primary_key AS IsPrimaryKey,a.Is_Unique as IsUnique,
( SELECT d.name + ','
FROM sys.index_columns c
INNER JOIN sys.columns d ON c.object_id = d.object_id
AND c.column_id = d.column_id
WHERE c.index_id = a.index_id
AND c.object_id = a.object_id
FOR
XML PATH('')
) AS ColumnNames
FROM sys.indexes a
INNER JOIN sys.tables b ON a.object_id = b.object_id
WHERE b.name ='{0}'", Table);
var dt = new DbContext(Server, DB).Query(sql);
foreach (DataRow row in dt.Rows)
{
var indexInfo = new DBIndexInfo();
indexInfo.IndexName = row["IndexName"].ToString();
indexInfo.ColumnNames = row["ColumnNames"].ToString();
indexInfo.IndexDes = row["IndexDes"].ToString();
// indexInfo.IsPrimaryKey = Convert.ToBoolean(row["IsPrimaryKey"]) ? "√" : "";
indexInfo.IsPrimaryKey = Convert.ToBoolean(row["IsPrimaryKey"]) ? true : false;
indexInfo.IsUnique = Convert.ToBoolean(row["IsUnique"]) ? true : false;
Indexs.Add(indexInfo);
}
return(Indexs);
}
/// <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>
/// <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 void CompareDBIndex(string tableName, DBIndexCollection modelIndexs,
List <string> addIndexs, List <string> deleteIndexs)
{
Dictionary <string, DBIndexInfo> dbIndexs = GetDBIndex(tableName);
if (modelIndexs != null)
{
foreach (DBIndex item in modelIndexs)
{
string indexName = item.Name.ToUpper();
if (dbIndexs.ContainsKey(indexName))
{
//已存在的索引，判断有否修改
DBIndexInfo dbIndexInfo = dbIndexs[indexName];
dbIndexInfo.IsExist = true;
bool hasChange = false;
if (dbIndexInfo.IndexFields.Count != item.DbIndexFields.Count ||
dbIndexInfo.IsUnique != item.IsUnique)
{
hasChange = true;
}
else
{
for (int i = 0; i < item.DbIndexFields.Count; i++)
{
if (string.Compare(item.DbIndexFields[i].Name, dbIndexInfo.IndexFields[i].Name, true) != 0 ||
item.DbIndexFields[i].IndexOrderWay != dbIndexInfo.IndexFields[i].IndexOrderWay)
{
hasChange = true;
break;
}
}
}
if (hasChange)
{
//索引变更，则先删除，再新增
deleteIndexs.Add(string.Format("drop index {0}", item.Name));
addIndexs.Add(CreateIndexSql(item, tableName));
}
}
else
{
//新增的索引
addIndexs.Add(CreateIndexSql(item, tableName));
}
}
}
foreach (KeyValuePair <string, DBIndexInfo> item in dbIndexs)
{
if (!item.Value.IsExist)
{
//模型已不存在次索引，需要删除
deleteIndexs.Add(string.Format("drop index {0}", item.Key));
}
}
}
