/// <summary>
///
/// </summary>
/// <param name="database"></param>
/// <param name="table"></param>
void dropTableIndex(EntityDB.IDatabaseService database, string table, IndexInfo[] dontDelIndexes)
{
table = table.ToLower();
List <string> toDelIndexes = new List <string>();
using (var sp_helpResult = database.SelectDataSet("sp_help [" + table + "]"))
{
foreach (var dtable in sp_helpResult.Tables)
{
if (dtable.Columns.Any(m => m.ColumnName == "index_keys"))
{
foreach (WayDataRow drow in dtable.Rows)
{
string existColumnString = drow["index_keys"].ToString();
string indexName = drow["index_name"].ToString();
string index_description = drow["index_description"].ToString();
if (index_description.Contains("primary key") == false)
{
if (dontDelIndexes.Count(m => string.Equals(m.Name, indexName, StringComparison.CurrentCultureIgnoreCase)) == 0)
{
toDelIndexes.Add(indexName);
}
}
}
break;
}
}
}
foreach (string indexName in toDelIndexes)
{
database.ExecSqlString("drop index " + indexName + " on [" + table + "]");
}
}
void deletecolumn(EntityDB.IDatabaseService database, string table, string column)
{
table = table.ToLower();
column = column.ToLower();
#region delete
using (var sp_helpResult = database.SelectDataSet("sp_help [" + table + "]"))
{
foreach (var dtable in sp_helpResult.Tables)
{
if (dtable.Columns.Any(m => m.ColumnName == "constraint_name"))
{
var query = dtable.Rows.Where(m => m["constraint_keys"].ToSafeString().ToLower() == column.ToLower());
if (query.Count() > 0)
{
database.ExecSqlString("alter table [" + table + "] drop constraint " + query.First()["constraint_name"]);
}
break;
}
}
//删除默认值
foreach (var dtable in sp_helpResult.Tables)
{
if (dtable.Columns.Any(m => m.ColumnName == "constraint_name"))
{
var query = from m in dtable.Rows
where ((string)m["constraint_type"]).ToLower().EndsWith(" " + column.ToLower()) && ((string)m["constraint_type"]).ToLower().StartsWith("default on ")
select m;
if (query.Count() > 0)
{
database.ExecSqlString("alter table [" + table + "] drop constraint " + query.First()["constraint_name"]);
}
break;
}
}
}
#endregion
database.ExecSqlString("ALTER TABLE [" + table + "] DROP COLUMN [" + column + "]");
}
/// <summary>
/// 删除table的所有包含字段的索引
/// </summary>
/// <param name="database"></param>
/// <param name="table"></param>
/// <param name="columnNames">包含的字段</param>
void dropTableAllUniqueIndexWithColumns(EntityDB.IDatabaseService database, string table, List <string> columnNames)
{
table = table.ToLower();
List <string> toDelIndexes = new List <string>();
using (var sp_helpResult = database.SelectDataSet("sp_help [" + table + "]"))
{
foreach (var dtable in sp_helpResult.Tables)
{
if (dtable.Columns.Any(m => m.ColumnName == "index_keys"))
{
foreach (var drow in dtable.Rows)
{
string existColumnString = drow["index_keys"].ToString();
string indexName = drow["index_name"].ToString();
if (indexName.StartsWith("IDX_"))
{
foreach (string column in columnNames)
{
if (Regex.IsMatch(existColumnString, @"\b(" + column + @")\b", RegexOptions.IgnoreCase))
{
toDelIndexes.Add(indexName);
break;
}
}
}
}
break;
}
}
}
foreach (string indexName in toDelIndexes)
{
database.ExecSqlString("drop index " + indexName + " on [" + table + "]");
}
}
/// <summary>
/// 返回没有变化的索引
/// </summary>
/// <param name="database"></param>
/// <param name="tablename"></param>
/// <param name="idxConfigs"></param>
/// <returns></returns>
List <IndexInfo> checkIfIdxChanged(EntityDB.IDatabaseService database, string tablename, IndexInfo[] idxConfigs)
{
tablename = tablename.ToLower();
List <IndexInfo> result = new List <IndexInfo>();
List <IndexInfo> existKeys = new List <IndexInfo>();
using (var sp_helpResult = database.SelectDataSet("sp_help [" + tablename + "]"))
{
foreach (var dtable in sp_helpResult.Tables)
{
if (dtable.Columns.Any(m => m.ColumnName == "index_keys"))
{
foreach (WayDataRow drow in dtable.Rows)
{
string existColumnString = drow["index_keys"].ToString();
string indexName = drow["index_name"].ToString();
string index_description = drow["index_description"].ToString();
if (index_description.Contains("primary key") == false)
{
//去除空格
string flag = existColumnString.Split(',').ToSplitString();
string dbname = flag.Split(',').OrderBy(m => m).ToArray().ToSplitString().ToLower();
//再排序，不要在去除空格之前排序
existKeys.Add(new IndexInfo
{
Name = indexName,
IsUnique = index_description.Contains("unique"),
IsClustered = index_description.Contains("clustered") && !index_description.Contains("nonclustered"),
ColumnNames = new string[] { dbname },
});
}
else
{
if (idxConfigs.Count(m => m.IsClustered) > 0)
{
//去除主键
database.ExecSqlString("alter table [" + tablename + "] drop constraint " + indexName);
//设为主键
string flag = existColumnString.Split(',').ToSplitString();
string ppname = flag.Split(',').OrderBy(m => m).ToArray().ToSplitString(",", "[{0}]").ToLower();
database.ExecSqlString("alter table [" + tablename + "] add constraint " + indexName + " primary key NONCLUSTERED (" + ppname + ")");
}
}
}
break;
}
}
}
foreach (var nowConfigItem in idxConfigs)
{
string myname = nowConfigItem.ColumnNames.OrderBy(m => m).ToArray().ToSplitString().ToLower();
var fined = existKeys.FirstOrDefault(m => m.IsUnique == nowConfigItem.IsUnique && m.IsClustered == nowConfigItem.IsClustered && m.ColumnNames[0].ToLower() == myname);
if (fined != null)
{
nowConfigItem.Name = fined.Name;
result.Add(nowConfigItem);
}
}
return(result);
}