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 void Add(ConstraintElement element) { Element = element; }
public AllExceptConstraintElement(ConstraintElement constraintElement) { Element = constraintElement; }