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);
}
