private ArrayList DiscoverForeignKeyColumns(SqlEntityElement sqlentity, ConstraintElement constraint, SqlConnection connection)
{
ArrayList list = new ArrayList();
String s = "select 1 ORDINAL_POSITION, c.name COLUMN_NAME, rc.name FOREIGN_COLUMN ";
s += "from syscolumns c ";
s += "left join sysreferences r on c.id = r.fkeyid and c.colid=r.fkey1 and r.constid=object_id('" + constraint.Name + "') ";
s += "left join syscolumns rc on rc.id=r.rkeyid and rc.colid=r.rkey1 ";
s += "where c.id = object_id('" + sqlentity.Name + "') and r.constid is not null ";
DataTable table = new DataTable();
String sql = s;
for (int i = 2; i <= 16; i++)
{
sql += " union ";
sql += s.Replace("1", i.ToString());
}
SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);
adapter.Fill(table);
foreach (DataRow row in table.Rows)
{
ColumnElement column = new ColumnElement();
column.Name = row["COLUMN_NAME"].ToString();
column.ForeignColumn = row["FOREIGN_COLUMN"].ToString();
list.Add(column);
}
return(list);
}
private ArrayList DiscoverConstraints(SqlEntityElement sqlentity, SqlConnection connection)
{
ArrayList list = new ArrayList();
DataTable table = new DataTable();
String sql = "SELECT c.*, coalesce((i.status & 16),0) ClusteredIndex, coalesce(rt.TABLE_NAME,'') ForeignEntity, coalesce(cc.CHECK_CLAUSE, '') CheckClause ";
sql += "FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS c ";
sql += "left join sysindexes i on c.CONSTRAINT_NAME=i.name ";
sql += "left join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS r on c.CONSTRAINT_NAME=r.CONSTRAINT_NAME ";
sql += "left join INFORMATION_SCHEMA.TABLE_CONSTRAINTS rt on r.UNIQUE_CONSTRAINT_NAME=rt.CONSTRAINT_NAME ";
sql += "left join INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc on c.CONSTRAINT_NAME=cc.CONSTRAINT_NAME ";
sql += "where c.table_name='" + sqlentity.Name + "' ";
SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);
adapter.Fill(table);
foreach (DataRow row in table.Rows)
{
ConstraintElement constraint = new ConstraintElement();
constraint.Name = row["CONSTRAINT_NAME"].ToString();
constraint.Type = row["CONSTRAINT_TYPE"].ToString();
constraint.Clustered = Int32.Parse(row["ClusteredIndex"].ToString()) != 0;
// constraint.ForeignEntity = row["ForeignEntity"].ToString();
constraint.CheckClause = row["CheckClause"].ToString();
if (constraint.Type.ToUpper().Equals("PRIMARY KEY") || constraint.Type.ToUpper().Equals("UNIQUE"))
{
constraint.Columns = DiscoverPrimaryKeyColumns(sqlentity, constraint, connection);
}
if (constraint.Type.ToUpper().Equals("FOREIGN KEY"))
{
constraint.Columns = DiscoverForeignKeyColumns(sqlentity, constraint, connection);
}
list.Add(constraint);
}
return(list);
}
private ArrayList DiscoverPrimaryKeyColumns(SqlEntityElement sqlentity, ConstraintElement constraint, SqlConnection connection)
{
ArrayList list = new ArrayList();
DataTable table = new DataTable();
String sql = "SELECT * ";
sql += "FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE ";
sql += "where table_name='" + sqlentity.Name + "' and constraint_name='" + constraint.Name + "' ";
sql += "order by ORDINAL_POSITION ";
SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);
adapter.Fill(table);
foreach (DataRow row in table.Rows)
{
ColumnElement column = new ColumnElement();
column.Name = row["COLUMN_NAME"].ToString();
list.Add(column);
}
return(list);
}
public ElementSetConstraintElement(ConstraintElement constraintElement)
{
Element = constraintElement;
}
public AllExceptConstraintElement(ConstraintElement constraintElement)
{
Element= constraintElement;
}
public NormalConstraintElement(ConstraintElement constraintElement)
{
Element = constraintElement;
}
public NormalConstraintElement(ConstraintElement constraintElement)
{
Element = constraintElement;
}
public ElementSetConstraintElement(ConstraintElement constraintElement)
{
Element = constraintElement;
}
public void Add(ConstraintElement element)
{
Element = element;
}
public AllExceptConstraintElement(ConstraintElement constraintElement)
{
Element = constraintElement;
}
