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