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