private IEnumerable ExecuteQuery(Type type, string command)
{
Console.WriteLine(command);
var columns = ColumnMapper.ContainsKey(type) ? ColumnMapper[type] : null;
var listType = typeof(List <>).MakeGenericType(type);
var addMethod = listType.GetMethod("Add");
var list = (IEnumerable)Activator.CreateInstance(listType);
using (var cmd = new SqlCommand(command, _connection))
{
cmd.Transaction = _transaction;
var reader = cmd.ExecuteReader();
while (reader.Read())
{
var dict = new Dictionary <string, object>();
for (var i = 0; i < reader.FieldCount; i++)
{
dict.Add(reader.GetName(i), reader.GetValue(i));
}
var instance = MapType(type, dict, columns);
addMethod.Invoke(list, new[] { instance });
}
reader.Close();
}
return(list);
}
protected override IEnumerable <ConstraintInfo> GetForeignReferences(TableAttribute table)
{
using (_connection = OpenConnection())
{
lock (_connection)
{
_transaction = null;
var cmd = @"SELECT p.name as TableName, ps.name as TableSchema, fk.name as ConstraintName, cc.name as TargetColumn, pc.name as SourceColumn
FROM sys.foreign_key_columns AS r
INNER JOIN sys.tables AS c ON c.object_id = r.referenced_object_id
INNER JOIN sys.schemas AS cs ON cs.schema_id = c.schema_id
INNER JOIN sys.objects AS fk ON fk.object_id = r.constraint_object_id
INNER JOIN sys.tables AS p ON p.object_id = r.parent_object_id
INNER JOIN sys.schemas AS ps ON ps.schema_id = p.schema_id
INNER JOIN sys.columns AS cc ON cc.column_id = r.referenced_column_id AND cc.object_id = c.object_id
INNER JOIN sys.columns AS pc ON pc.column_id = r.parent_column_id AND pc.object_id = p.object_id
WHERE c.name = @tableName and cs.name = @tableSchema";
if (!ColumnMapper.ContainsKey(typeof(ConstraintInfo)))
{
ColumnMapper.Map <ConstraintInfo>();
}
return((IEnumerable <ConstraintInfo>)ExecuteQueryInner(typeof(ConstraintInfo), cmd,
new[]
{
new SqlParameter("@tableName", table.Name),
new SqlParameter("@tableSchema", table.Schema)
}));
}
}
}
protected override IEnumerable <ConstraintInfo> GetForeignReferences(TableAttribute table)
{
var cmd = string.Format(@"SELECT p.name as TableName, ps.name as TableSchema, fk.name as ConstraintName, cc.name as TargetColumn, pc.name as SourceColumn
FROM sys.foreign_key_columns AS r
INNER JOIN sys.tables AS c ON c.object_id = r.referenced_object_id
INNER JOIN sys.schemas AS cs ON cs.schema_id = c.schema_id
INNER JOIN sys.objects AS fk ON fk.object_id = r.constraint_object_id
INNER JOIN sys.tables AS p ON p.object_id = r.parent_object_id
INNER JOIN sys.schemas AS ps ON ps.schema_id = p.schema_id
INNER JOIN sys.columns AS cc ON cc.column_id = r.referenced_column_id AND cc.object_id = c.object_id
INNER JOIN sys.columns AS pc ON pc.column_id = r.parent_column_id AND pc.object_id = p.object_id
WHERE c.name = '{0}' and cs.name = '{1}'", table.Name, table.Schema);
if (!ColumnMapper.ContainsKey(typeof(ConstraintInfo)))
{
ColumnMapper.Map <ConstraintInfo>();
}
return(ExecuteQuery <ConstraintInfo>(cmd));
}
private IEnumerable ExecuteQueryInner(Type type, string command, params SqlParameter[] args)
{
Debug.WriteLine(command);
var columns = ColumnMapper.ContainsKey(type) ? ColumnMapper[type] : null;
var listType = typeof(List <>).MakeGenericType(type);
var addMethod = listType.GetMethod("Add");
var list = (IEnumerable)Activator.CreateInstance(listType);
try
{
using (var cmd = new SqlCommand(command, _connection))
{
foreach (var parameter in args)
{
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter(parameter.Name, parameter.Value));
}
cmd.Transaction = _transaction;
var reader = cmd.ExecuteReader();
while (reader.Read())
{
var dict = new Dictionary <string, object>();
for (var i = 0; i < reader.FieldCount; i++)
{
dict.Add(reader.GetName(i), reader.GetValue(i));
}
var instance = MapType(type, dict, columns);
addMethod.Invoke(list, new[] { instance });
}
reader.Close();
}
return(list);
}
catch (Exception ex)
{
Debugger.Break();
throw new Exception("SQL command failed: " + command, ex);
}
}
