public int GetKeyWeight(ForeignKeyConstraint keyConstraint) { var table = this.Tables[keyConstraint.FromTableName()]; if ( keyConstraint.ConstraintKeys.Any( p => !table.Columns.First(atr => atr.ColumnName == p.From.ColumnName).NotNullConstraint)) using ( SqlDataRecordExtended rs2 = _conn.ExecuteDataReader( string.Format(@" SELECT COUNT(*) _count FROM [{0}] WHERE ({1})", keyConstraint.FromTableName(), string.Join(" AND ", keyConstraint.ConstraintKeys.Select( c => c.From.ColumnName + " IS NOT NULL ").ToArray())), new {}) ) { if (rs2.Read()) return rs2.GetInt32("_count"); return 0; } else { return GetTableCount(table.TableName); } }
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 }