public static List <SQLForeignKeyRelation> LoadForeignKeysForColumn(int tableID, int columnID, string connectionString, ref List <SQLTable> databaseTables) { List <SQLForeignKeyRelation> foreignKeys = new List <SQLForeignKeyRelation>(); string selectStatement = "Select constraint_object_id, constraint_column_id, parent_object_id, parent_column_id, referenced_object_id, referenced_column_id from sys.foreign_key_columns where referenced_object_id = " + tableID + " and referenced_column_id = " + columnID; SqlDataReader dataReader = SQLDataServer.ExecuteSQLStringReturnDataReader(selectStatement, connectionString); while (dataReader.Read()) { SQLForeignKeyRelation foreignKey = new SQLForeignKeyRelation(); foreignKey.DatabaseTables = databaseTables; foreignKey.parentObjectID = Convert.ToInt32(dataReader["parent_object_id"]); foreignKey.parentColumnID = Convert.ToInt32(dataReader["parent_column_id"]); foreignKey.referencedObjectID = Convert.ToInt32(dataReader["referenced_object_id"]); foreignKey.referencedColumnID = Convert.ToInt32(dataReader["referenced_column_id"]); foreignKeys.Add(foreignKey); } dataReader.Close(); return(foreignKeys); }
public static List <SQLTableColumn> LoadColumnsForTable(string tableName, string connectionString, SQLTable parentTable, ref List <SQLTable> databaseTables) { List <SQLTableColumn> columns = new List <SQLTableColumn>(); string selectStatement; selectStatement = "select COLUMN_NAME, TABLE_NAME, ORDINAL_POSITION, IS_NULLABLE, CHARACTER_MAXIMUM_LENGTH, DATA_TYPE, NUMERIC_PRECISION, NUMERIC_SCALE, DATETIME_PRECISION from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + tableName + "'"; SqlDataReader dataReader = SQLDataServer.ExecuteSQLStringReturnDataReader(selectStatement, connectionString); while (dataReader.Read()) { SQLTableColumn column = new SQLTableColumn(); column.Name = Convert.ToString(dataReader["COLUMN_NAME"]); column.TableName = Convert.ToString(dataReader["TABLE_NAME"]); column.OrdinalPosition = Convert.ToInt32(dataReader["ORDINAL_POSITION"]); column.Nullable = DBBooleanValues.ReturnBooleanFromYesOrNo(dataReader["IS_NULLABLE"]); column.DataType = Convert.ToString(dataReader["DATA_TYPE"]); column.MaximumLength = DBNullReturnValues.Return0(dataReader["CHARACTER_MAXIMUM_LENGTH"]); column.NumericPrecision = DBNullReturnValues.Return0(dataReader["NUMERIC_PRECISION"]); column.NumericScale = DBNullReturnValues.Return0(dataReader["NUMERIC_SCALE"]); column.DateTimePrecision = DBNullReturnValues.Return0(dataReader["DATETIME_PRECISION"]); column.ParentTable = parentTable; if (column.OrdinalPosition == 1) { column.PrimaryKey = true; } column.ForeignKeys = SQLForeignKeyRelation.LoadForeignKeysForColumn(column.ParentTable.id, column.OrdinalPosition, connectionString, ref databaseTables); columns.Add(column); } dataReader.Close(); return(columns); }
public static List <SQLTable> LoadTables(string connectionString) { List <SQLTable> tables = new List <SQLTable>(); SqlDataReader dataReader; dataReader = SQLDataServer.ExecuteSQLStringReturnDataReader("select name, object_id from sys.tables where name <> 'sysdiagrams' and name not like 'aspnet_%'", connectionString); while (dataReader.Read()) { SQLTable table = new SQLTable(); table.Name = Convert.ToString(dataReader["name"]); table.id = Convert.ToInt32(dataReader["object_id"]); table.Columns = SQLTableColumn.LoadColumnsForTable(table.Name, connectionString, table, ref tables); tables.Add(table); } dataReader.Close(); return(tables); }