public string PKToSqlString(SPrimaryKey pk)
{
string talbename = pk.table.name;
List <string> pkcloses = new List <string>();
foreach (var a in pk.columns)
{
pkcloses.Add(a.name);
}
//bool hasclusteredindex = pk.table.indexes.Count(x => x.isclustered) == 1;
string template = " primary key ({0})";
string s = string.Format(template, string.Join(",", pkcloses));
return(s);
}
private string PKToSqlStringOutter(SPrimaryKey pk)
{
string talbename = pk.table.name;
List <string> pkcloses = new List <string>();
foreach (var a in pk.columns)
{
pkcloses.Add(a.name);
}
//bool hasclusteredindex = pk.table.indexes.Count(x => x.isclustered) == 1;
string template = " alter table {0} add constraint PK_{0} primary key({1});";
string s = string.Format(template, pk.table.name, string.Join(",", pkcloses));
return(s);
}
private SPrimaryKey GetPrimaryKey(DbConn dbconn, string tableid, List <SColumn> columns)
{
string sql1 = " select name,object_id,parent_object_id from sys.key_constraints where type='PK' and parent_object_id=@objid";
DataTable tbpks = dbconn.SqlToDataTable(sql1, new { objid = tableid });
if (tbpks.Rows.Count == 0)
{
return(null);
}
if (tbpks.Rows.Count > 1)
{
throw new Exception("非法表");
}
SPrimaryKey pk = new SPrimaryKey();
pk.name = tbpks.Rows[0]["name"].ToString();
string sql2 = @" select IC.object_id,IC.column_id,IC.index_column_id,IC.index_id,AC.name as columnname,KC.name from sys.index_columns IC
left join sys.all_columns AC on IC.object_id=AC.object_id and IC.column_id=AC.column_id
left join sys.indexes ID on IC.object_id=ID.object_id and IC.index_id=ID.index_id
left join sys.key_constraints KC on ID.name=KC.name and KC.type='PK'
where KC.parent_object_id=@objid";
DataTable tbpkcols = dbconn.SqlToDataTable(sql2, new { objid = tableid });
if (tbpkcols.Rows.Count == 0)
{
return(null);
}
foreach (DataRow dr in tbpkcols.Rows)
{
string cn = dr["columnname"].ToString();
var c = columns.FirstOrDefault(x => x.name == cn);
if (c == null)
{
throw new Exception(cn + "不在表中");
}
c.primarykey = pk;
c.isprimarykey = true;
pk.columns.Add(c);
pk.table = c.table;
}
return(pk);
}
