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