public ForeignKey()
{
this.Name = "";
// this._FKTable = null;
// this._PKTable = null;
this.ColumnPairs = new ColumnPairCollection();
}
private static string GenerateRows(ITableSchema schema, TableReader reader)
{
var table = reader.Table;
TableDataClause script = new TableDataClause(schema);
StringBuilder builder = new StringBuilder();
foreach (DataRow row in table.Rows)
{
var pair = new ColumnPairCollection(row);
builder.Append(script.INSERT(pair)).AppendLine();
}
if (table.Rows.Count > 0)
{
builder.AppendLine(SqlScript.GO);
}
return(builder.ToString());
}
private void AddForeignKeys(SqlConnection con)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_fkeys";
cmd.Parameters.Add("@fktable_name", SqlDbType.VarChar);
cmd.Parameters.Add("@fktable_owner", SqlDbType.VarChar);
foreach (Table objTable in this._Tables)
{
cmd.Parameters["@fktable_name"].Value = objTable.Name;
cmd.Parameters["@fktable_owner"].Value = "dbo";
SqlDataReader dr = cmd.ExecuteReader();
int oFkName = dr.GetOrdinal("FK_NAME");
int oPkTableName = dr.GetOrdinal("PKTABLE_NAME");
int oFkColumnName = dr.GetOrdinal("FKCOLUMN_NAME");
int oPkColumnName = dr.GetOrdinal("PKCOLUMN_NAME");
// int oKeySeq = dr.GetOrdinal("KEY_SEQ");
if (dr.HasRows)
{
string ForeignKeyName = "";
ForeignKey objForeignKey;
Table objPKTable = null;
ColumnPairCollection colColumnPairs = null;
while (dr.Read())
{
if (dr.GetString(oFkName) != ForeignKeyName)
{
if (!string.IsNullOrEmpty(ForeignKeyName))
{
objForeignKey = new ForeignKey();
objForeignKey.Name = ForeignKeyName;
objForeignKey.FKTable = objTable;
objForeignKey.PKTable = objPKTable;
objForeignKey.ColumnPairs = colColumnPairs;
objTable.OutgoingForeignKeys.Add(objForeignKey);
objPKTable.IncomingForeignKeys.Add(objForeignKey);
// Add foreign key to column outgoing foreign keys collection.
foreach (ColumnPair objColumnPair in colColumnPairs)
{
objColumnPair.FKColumn.OutgoingForeignKeys.Add(objForeignKey);
}
}
ForeignKeyName = dr.GetString(oFkName);
// TODO:1 Fix situation when PKTable cannot be found due to security.
objPKTable = this._Tables[dr.GetString(oPkTableName)];
colColumnPairs = new ColumnPairCollection();
}
ColumnPair objColumnPair2 = new ColumnPair(objTable.Columns[dr.GetString(oFkColumnName)], objPKTable.Columns[dr.GetString(oPkColumnName)]);
colColumnPairs.Add(objColumnPair2);
}
// Add final foreign key.
objForeignKey = new ForeignKey();
objForeignKey.Name = ForeignKeyName;
objForeignKey.FKTable = objTable;
objForeignKey.PKTable = objPKTable;
objForeignKey.ColumnPairs = colColumnPairs;
objTable.OutgoingForeignKeys.Add(objForeignKey);
objPKTable.IncomingForeignKeys.Add(objForeignKey);
// Add foreign key to column outgoing foreign keys collection.
foreach (ColumnPair objColumnPair in colColumnPairs)
{
objColumnPair.FKColumn.OutgoingForeignKeys.Add(objForeignKey);
}
}
dr.Close();
}
}
