private void InitPublicTables(IEnumerable<ITableFilter> tableFilters, IEnumerable<ITableFilter> columnsToGenerate) { { var sqlBuffer = new List<string>(); var bindBuffer = new List<object>(); foreach (ITableFilter filter in tableFilters) filter.InitSQL("t", sqlBuffer, bindBuffer); using ( SqlDataRecordExtended rs = _conn.ExecuteDataReader( @" select table_name from INFORMATION_SCHEMA.TABLES t where t.TABLE_TYPE = 'BASE TABLE' " + StringUtil.Agg(" AND ", sqlBuffer.ToArray()), bindBuffer.ToArray()) ) { while (rs.Read()) { string tableName = rs.GetString("table_name"); var currentTable = new Table(tableName); Tables.Add(currentTable.TableName, currentTable); } } } { var sqlBuffer = new List<string>(); var bindBuffer = new List<object>(); foreach (ITableFilter filter in tableFilters) filter.InitSQL("t", sqlBuffer, bindBuffer); foreach (ITableFilter filter in columnsToGenerate) filter.InitSQL("c", sqlBuffer, bindBuffer); using ( SqlDataRecordExtended rs2 = _conn.ExecuteDataReader( @" select c.TABLE_NAME , c.COLUMN_NAME , c.DATA_TYPE, c.CHARACTER_MAXIMUM_LENGTH as Size, c.IS_NULLABLE as IsNullable, c.COLUMN_DEFAULT as DefaultValue, c.ordinal_position , t.TABLE_TYPE as TableType from INFORMATION_SCHEMA.COLUMNS c inner join INFORMATION_SCHEMA.TABLES t on c.TABLE_NAME = t.TABLE_NAME where t.TABLE_TYPE = 'BASE TABLE' " + StringUtil.Agg(" AND ", sqlBuffer.ToArray()), bindBuffer.ToArray()) ) { while (rs2.Read()) { var attr = new Column( rs2.GetString("COLUMN_NAME"), rs2.GetString("DATA_TYPE"), (short) rs2.GetInt32("ordinal_position"), rs2.GetString("TABLE_NAME"), !GetBoolean(rs2.GetString("IsNullable"))); Columns.Add(attr.GetKey(), attr); } } } foreach (var curr in Tables) { GetPublicTableAttributesCached(curr.Value.TableName, curr.Value.Columns); GetTableConstraintsCached(curr.Value.TableName, curr.Value); } // namespace }
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 }