private void GetTableConstraintsCached(string tablename, Table currentTable)
{
#region referential constraints
using (
SqlDataRecordExtended rs2 = _conn.ExecuteDataReader(
@"
SELECT
con.CONSTRAINT_NAME,
con.UNIQUE_CONSTRAINT_NAME , --con.*,
tcon.table_name tcon_table_name,
pkcon.table_name pkcon_table_name,
con_col.column_name con_col_column_name,
pkcon_col.column_name pkcon_col_column_name
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS con
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tcon ON con.CONSTRAINT_NAME = tcon.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS pkcon ON
con.UNIQUE_CONSTRAINT_NAME = pkcon.CONSTRAINT_NAME
AND pkcon.CONSTRAINT_TYPE = 'PRIMARY KEY'
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE con_col ON con_col.CONSTRAINT_NAME = con.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE pkcon_col ON pkcon_col.CONSTRAINT_NAME = pkcon.CONSTRAINT_NAME
WHERE tcon.table_name = @id
",
new {id = tablename}))
{
ForeignKeyConstraint currentConstraint = null;
while (rs2.Read())
{
string constraintName1 = rs2.GetString("CONSTRAINT_NAME");
#region if nec. create constraint (class)
if (currentConstraint == null || currentConstraint.ConstraintName != constraintName1)
{
currentConstraint = new ForeignKeyConstraint(constraintName1, currentTable.TableName);
ForeignKeys.Add(currentConstraint);
}
#endregion
#region constraint keys
var key = new ColumnKey(
rs2.GetString("tcon_table_name"),
rs2.GetString("con_col_column_name"));
var fkey = new ColumnKey(
rs2.GetString("pkcon_table_name"),
rs2.GetString("pkcon_col_column_name"));
currentConstraint.ConstraintKeys.Add(new ForeignKeyColumn(key, fkey));
#endregion
}
}
#endregion
#region primary keys
using (SqlDataRecordExtended rs2 = _conn.ExecuteDataReader(
@"
SELECT
pkcon.constraint_name,
pkcon.table_name,
pkcon_col.column_name
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS pkcon
join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE pkcon_col on pkcon_col.CONSTRAINT_NAME = pkcon.CONSTRAINT_NAME
/*join INFORMATION_SCHEMA.COLUMNS pkcon_colc on
pkcon_colc.table_name = pkcon_col.table_name
and pkcon_colc.COLUMN_NAME = pkcon_col.COLUMN_NAME*/
WHERE
-- con.UNIQUE_CONSTRAINT_NAME = pkcon.CONSTRAINT_NAME
pkcon.CONSTRAINT_TYPE = 'PRIMARY KEY'
AND pkcon.table_name = @id
",
new {id = tablename}))
{
//_conn.ExecuteDataReader("sp_jgljsdklfjsdkljf", new List<object>(){
// "param1", 1,
// "param2", 2,
// "param3", 3});
PrimaryKeyConstraint currentConstraint = null;
while (rs2.Read())
{
string conoid = rs2.GetString("CONSTRAINT_NAME");
#region if nec. create constraint (class)
if (currentConstraint == null || currentConstraint.ConstraintName != conoid)
{
string constraintName = rs2.GetString("CONSTRAINT_NAME");
currentConstraint = new PrimaryKeyConstraint(constraintName, tablename);
if (currentTable.PrimaryKey != null) throw new Exception("multiple primary keys??");
currentTable.PrimaryKey = currentConstraint;
}
#endregion
#region constraint keys
currentConstraint.ConstraintKeys.Add(new ColumnKey(
rs2.GetString("table_name"),
rs2.GetString("column_name")));
#endregion
}
}
#endregion
#region unique
#if (true)
{
using (SqlDataRecordExtended rs2 = _conn.ExecuteDataReader(
@"
select con.CONSTRAINT_NAME,con.UNIQUE_CONSTRAINT_NAME , --con.*,
tcon.table_name tcon_table_name,
pkcon.table_name pkcon_table_name,
con_col.column_name con_col_column_name,
pkcon_col.column_name pkcon_col_column_name
--con_colc.ORDINAL_POSITION con_colc_ORDINAL_POSITION,
--pkcon_colc.ORDINAL_POSITION pkcon_colc_ORDINAL_POSITION
from
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS con
join INFORMATION_SCHEMA.TABLE_CONSTRAINTS tcon on con.CONSTRAINT_NAME = tcon.CONSTRAINT_NAME
join INFORMATION_SCHEMA.TABLE_CONSTRAINTS pkcon on
con.UNIQUE_CONSTRAINT_NAME = pkcon.CONSTRAINT_NAME
and pkcon.CONSTRAINT_TYPE = 'PRIMARY KEY'
join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE con_col on con_col.CONSTRAINT_NAME = con.CONSTRAINT_NAME
join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE pkcon_col on pkcon_col.CONSTRAINT_NAME = pkcon.CONSTRAINT_NAME
/*join INFORMATION_SCHEMA.COLUMNS con_colc on
con_colc.table_name = con_col.table_name
and con_colc.COLUMN_NAME = con_col.COLUMN_NAME
join INFORMATION_SCHEMA.COLUMNS pkcon_colc on
pkcon_colc.table_name = pkcon_col.table_name
and pkcon_colc.COLUMN_NAME = pkcon_col.COLUMN_NAME*/
where tcon.table_name = @id
",
new {id = tablename}))
{
Model.UniqueConstraint currentConstraint = null;
while (rs2.Read())
{
string conoid = rs2.GetString("CONSTRAINT_NAME");
if (currentConstraint == null || currentConstraint.ConstraintName != conoid)
{
string ConstraintName = rs2.GetString("CONSTRAINT_NAME");
currentConstraint = new UniqueConstraint(ConstraintName, tablename);
UniqueConstraints.Add(currentConstraint);
}
currentConstraint.ConstraintKeys.Add(new ColumnKey(rs2.GetString("tcon_table_name"),
rs2.GetString("con_col_column_name")));
}
}
}
#endif
#endregion
}
private bool TryGetPrimaryKeyConstraint(IDictionary<string,Table> Tables,ColumnKey attr, out PrimaryKeyConstraint primaryKey)
{
primaryKey = Tables[attr.TableName].PrimaryKey.ConstraintKeys.Contains(attr)
? Tables[attr.TableName].PrimaryKey
: null;
return primaryKey != null;
}
