/// <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);
}
public DBIndexField(string name, IndexOrderWay indexOrderWay)
{
this._name = name;
this._indexOrderWay = indexOrderWay;
}