List <DbRelationDefinition> IDbManagerTable.GetTableRelations()
{
var dmRelations = GetTableRelations();
if (dmRelations == null || dmRelations.Rows.Count == 0)
{
return(null);
}
List <DbRelationDefinition> relations = new List <DbRelationDefinition>();
foreach (var dmRow in dmRelations.Rows)
{
DbRelationDefinition relationDefinition = new DbRelationDefinition();
relationDefinition.ForeignKey = (string)dmRow["ForeignKey"];
relationDefinition.ColumnName = (string)dmRow["ColumnName"];
relationDefinition.ReferenceColumnName = (string)dmRow["ReferenceColumnName"];
relationDefinition.ReferenceTableName = (string)dmRow["ReferenceTableName"];
relationDefinition.TableName = (string)dmRow["TableName"];
relations.Add(relationDefinition);
}
return(relations);
}
internal async Task <IEnumerable <DbRelationDefinition> > GetRelationsAsync(DbConnection connection, DbTransaction transaction)
{
var schema = SqlManagementUtils.GetUnquotedSqlSchemaName(tableName);
var relations = new List <DbRelationDefinition>();
var tableRelations = await SqlManagementUtils.GetRelationsForTableAsync((SqlConnection)connection, (SqlTransaction)transaction, this.tableName.ToString(), schema).ConfigureAwait(false);
if (tableRelations != null && tableRelations.Rows.Count > 0)
{
foreach (var fk in tableRelations.Rows.GroupBy(row =>
new
{
Name = (string)row["ForeignKey"],
TableName = (string)row["TableName"],
SchemaName = (string)row["SchemaName"] == "dbo" ? "" : (string)row["SchemaName"],
ReferenceTableName = (string)row["ReferenceTableName"],
ReferenceSchemaName = (string)row["ReferenceSchemaName"] == "dbo" ? "" : (string)row["ReferenceSchemaName"],
}))
{
var relationDefinition = new DbRelationDefinition()
{
ForeignKey = fk.Key.Name,
TableName = fk.Key.TableName,
SchemaName = fk.Key.SchemaName,
ReferenceTableName = fk.Key.ReferenceTableName,
ReferenceSchemaName = fk.Key.ReferenceSchemaName,
};
relationDefinition.Columns.AddRange(fk.Select(dmRow =>
new DbRelationColumnDefinition
{
KeyColumnName = (string)dmRow["ColumnName"],
ReferenceColumnName = (string)dmRow["ReferenceColumnName"],
Order = (int)dmRow["ForeignKeyOrder"]
}));
relations.Add(relationDefinition);
}
}
return(relations.OrderBy(t => t.ForeignKey).ToArray());
}
public async Task <IEnumerable <DbRelationDefinition> > GetRelationsAsync(DbConnection connection, DbTransaction transaction)
{
var relations = new List <DbRelationDefinition>();
var commandRelations = @"
SELECT
ke.CONSTRAINT_NAME as ForeignKey,
ke.POSITION_IN_UNIQUE_CONSTRAINT as ForeignKeyOrder,
ke.referenced_table_name as ReferenceTableName,
ke.REFERENCED_COLUMN_NAME as ReferenceColumnName,
ke.table_name TableName,
ke.COLUMN_NAME ColumnName
FROM
information_schema.KEY_COLUMN_USAGE ke
WHERE
ke.referenced_table_name IS NOT NULL
and ke.table_schema = schema()
AND ke.table_name = @tableName
ORDER BY
ke.referenced_table_name;";
var relationsList = new SyncTable(tableName.Unquoted().ToString());
var command = connection.CreateCommand();
command.Connection = connection;
command.Transaction = transaction;
command.CommandText = commandRelations;
var parameter = command.CreateParameter();
parameter.ParameterName = "@tableName";
parameter.Value = tableName.Unquoted().ToString();
command.Parameters.Add(parameter);
bool alreadyOpened = connection.State == ConnectionState.Open;
if (!alreadyOpened)
{
await connection.OpenAsync().ConfigureAwait(false);
}
using (var reader = await command.ExecuteReaderAsync().ConfigureAwait(false))
{
relationsList.Load(reader);
}
if (!alreadyOpened)
{
connection.Close();
}
if (relationsList != null && relationsList.Rows.Count > 0)
{
foreach (var fk in relationsList.Rows.GroupBy(row =>
new { Name = (string)row["ForeignKey"], TableName = (string)row["TableName"], ReferenceTableName = (string)row["ReferenceTableName"] }))
{
var relationDefinition = new DbRelationDefinition()
{
ForeignKey = fk.Key.Name,
TableName = fk.Key.TableName,
ReferenceTableName = fk.Key.ReferenceTableName,
};
relationDefinition.Columns.AddRange(fk.Select(dmRow =>
new DbRelationColumnDefinition
{
KeyColumnName = (string)dmRow["ColumnName"],
ReferenceColumnName = (string)dmRow["ReferenceColumnName"],
Order = Convert.ToInt32(dmRow["ForeignKeyOrder"])
}));
relations.Add(relationDefinition);
}
}
return(relations.OrderBy(t => t.ForeignKey).ToArray());
}
