C# ASP.NET MVC 中的数据库上下文和返回动态结果集
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15732340/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Database context and Return Dynamic Result Set in ASP.NET MVC
提问by Dips
In MVC 4 and EF 5 i want to run dynamic query.
在 MVC 4 和 EF 5 中，我想运行动态查询。
var returndata = Context.Database.SqlQuery(Type, strsql, null);
i don't know, how many fields it will return and name. Out of this result i want to make table structure that will display on view.
我不知道，它将返回多少个字段和名称。出于这个结果，我想制作将在视图中显示的表结构。
Question : What should i passed as Type?
问题：我应该传递什么类型？
my query return below result:
我的查询返回以下结果：
Field 1, Field 2, Field 3, Field 4, Field 5
场 1、场 2、场 3、场 4、场 5
Row1...
第 1 行...
Row2..
行2..
Appreciate any suggestion.
感谢任何建议。
采纳答案by Dips
Finally i made is using TypeBuilder option suggested by "Mortalus" and ExpandoObject object. It has little performance overhead right now.
最后，我使用“Mortalus”和 ExpandoObject 对象建议的 TypeBuilder 选项。它现在几乎没有性能开销。
Take Typebuilder code from "Mortalus" answer then i made code according to my requirement as below.
从“Mortalus”答案中获取 Typebuilder 代码，然后我根据我的要求编写了代码，如下所示。
List<Dictionary<string, object>> expandolist = new List<Dictionary<string, object>>();
foreach (var item in returndata)
{
IDictionary<string, object> expando = new ExpandoObject();
foreach (PropertyDescriptor propertyDescriptor in TypeDescriptor.GetProperties(item))
{
var obj = propertyDescriptor.GetValue(item);
expando.Add(propertyDescriptor.Name, obj);
}
expandolist.Add(new Dictionary<string, object>(expando));
}
return expandolist;
so now, I have "Dictionary" object from dynamic object. and using it you can work easily at design time rather then wait until runtime using "dynamic" object.
所以现在，我有来自动态对象的“字典”对象。并使用它您可以在设计时轻松工作，而不是等到运行时使用“动态”对象。
回答by Darin Dimitrov
You could use a raw SQL query because EF doesn't support that:
您可以使用原始 SQL 查询，因为 EF 不支持：
private static IEnumerable<object[]> Read(DbDataReader reader)
{
while (reader.Read())
{
var values = new List<object>();
for (int i = 0; i < reader.FieldCount; i++)
{
values.Add(reader.GetValue(i));
}
yield return values.ToArray();
}
}
and then:
进而：
public ActionResult Index()
{
using (var ctx = new UsersContext())
using (var cmd = ctx.Database.Connection.CreateCommand())
{
ctx.Database.Connection.Open();
cmd.CommandText = "SELECT * FROM UserProfile";
using (var reader = cmd.ExecuteReader())
{
var model = Read(reader).ToList();
return View(model);
}
}
}
and finally in your view:
最后在你看来：
@model IEnumerable<object[]>
<table>
<tbody>
@foreach (var row in Model)
{
<tr>
@foreach (var column in row)
{
<td>@column</td>
}
</tr>
}
</tbody>
</table>
回答by Rob G
Without knowing anything about the type that is returned, I think you might be out of luck.
在不知道返回的类型的情况下，我认为您可能不走运。
If you know what patterns it might fall under, you could use some try { } catch () { }'s on interfaces that match those parameters on your otherwise dynamic query, but that seems like it might be a bit painful.
如果您知道它可能属于什么模式，则可以在try { } catch () { }与其他动态查询中的这些参数匹配的接口上使用 some ，但这似乎有点痛苦。
回答by Nicholas Butler
Unfortunately, EF won't materialize objects unless it knows their Type.
不幸的是，EF 不会物化对象，除非它知道它们的Type.
If this is really necessary for you, I think your best bet would be to fall back to ADO.NETand DataTable.
如果这对你来说真的有必要，我认为你最好的选择是回到ADO.NET和DataTable。
回答by Mortalus
I have recently stumbled upon this example:
我最近偶然发现了这个例子：
I haven't had the time to test it myself but it seems that it is possible with some additional work to construct the dynamic type.
我没有时间自己测试它，但似乎可以通过一些额外的工作来构造动态类型。
In short you would want to do something like this:
简而言之，你会想要做这样的事情：
TypeBuilder builder = Program.CreateTypeBuilder(
"MyDynamicAssembly", "MyModule", "MyType");
Program.CreateAutoImplementedProperty(builder, "name", typeof(string));
Program.CreateAutoImplementedProperty(builder, "type", typeof(string));
Program.CreateAutoImplementedProperty(builder, "id", typeof(int));
Type resultType = builder.CreateType();
dynamic queryResult = context.Database.SqlQuery(
resultType, "SELECT * FROM sys.sysobjects");
Where TypeBuilderis described in details in the post I have attached.
在TypeBuilder我附上的帖子中详细描述了哪里。
回答by Sel
Similarly post by Darin Dimitrov, but it returns DataTable
Darin Dimitrov 的类似帖子，但它返回 DataTable
public DataTable QueryToTable(Entities db, string queryText, SqlParameter[] parametes)
{
using ( DbDataAdapter adapter = new SqlDataAdapter())
{
adapter.SelectCommand = db.Database.Connection.CreateCommand();
adapter.SelectCommand.CommandText = queryText;
if (parametes != null)
adapter.SelectCommand.Parameters.AddRange(parametes);
DataTable table = new DataTable();
adapter.Fill(table);
return table;
}
}
Use
用
SqlParameter[] parametes = new[]
{
new SqlParameter("date_from", dateFrom)
};
DataTable tab = QueryToTable(new Entities(),
"Select * From SomeTable Where ADate >= @date_from", parametes);
Example for MS SQL Server
MS SQL Server 示例
回答by Petr Voborník
This method loads data from SQL select (with parameters) to the list of rows, where each row is the dictionary of columns (the key is the column name).
该方法将SQL select（带参数）中的数据加载到行列表中，其中每一行是列的字典（键是列名）。
private static List<Dictionary<string, object>> LoadData(string sqlSelect, params object[] sqlParameters)
{
var table = new List<Dictionary<string, object>>();
using (var ctx = new DbEntities())
{
ctx.Database.Connection.Open();
using (var cmd = ctx.Database.Connection.CreateCommand())
{
cmd.CommandText = sqlSelect;
foreach (var param in sqlParameters)
cmd.Parameters.Add(param);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var row = new Dictionary<string, object>();
for (int i = 0; i < reader.FieldCount; i++)
row[reader.GetName(i)] = reader[i];
table.Add(row);
}
}
}
}
return table;
}
回答by Stephen Himes
Adding to Petr Voborník's answer, dynamic query, I add dynamic insert of ResultSet, my application takes the dynamic query of all tables of the entire database, a chunk at a time and then inserts the dynamic results into a remote database, using Always Encrypted (omitted here). Passing a sb command and parameter object.
添加到 Petr Voborník 的答案，动态查询，我添加了 ResultSet 的动态插入，我的应用程序对整个数据库的所有表进行动态查询，一次一个块，然后将动态结果插入到远程数据库中，使用 Always Encrypted (此处省略）。传递 sb 命令和参数对象。
public void StoreData(DbContext dbContext, Dictionary<string, string> columnInfo, List<Dictionary<string, object>> multiInsertObj, string tableName)
{
_ctx = dbContext;
_columnInfo = columnInfo;
var sb = new StringBuilder();
sb.Append(BuildSqlCommand(tableName, columnInfo, multiInsertObj.Count));
ExecuteSqlCommand(sb, GetParamsObject(columnInfo, multiInsertObj));
}
private static StringBuilder BuildSqlCommand(string tableName, Dictionary<string, string> variableInfo, int variableCount)
{
//Build sql command
var sb = new StringBuilder();
sb.Append("INSERT INTO dbo." + tableName + "(");
foreach (var variable in variableInfo)
{
sb.Append(variable.Key);
sb.Append(", ");
}
sb.Append("SystemNumber, ");
sb.Remove(sb.Length - 2, 2).Append(") VALUES ");
for (var i = 0; i < variableCount; i++)
{
sb.Append("(");
foreach (var name in variableInfo.Keys)
{
sb.Append("@" + name + "_" + i + ",");
}
sb.Append("@SystemNumber" + "_" + i + ",");
sb.Remove(sb.Length - 1, 1).Append("),");
}
sb.Remove(sb.Length - 1, 1);
return sb;
}
private static object[] GetParamsObject(Dictionary<string, string> columnInfo, List<Dictionary<string, object>> multiInsertObj)
{
var variableCount = multiInsertObj.Count;
var rowCount = multiInsertObj[0].Keys.Count;
var objectLength = (rowCount + 1) * variableCount;
var variableDataTypes = columnInfo.Values.ToList();
var paramObj = new object[objectLength];
var j = 0;
var i = 0;
foreach (var row in multiInsertObj)
{
var k = 0;
foreach (var data in row)
{
var sb = new StringBuilder();
sb.Append("@");
sb.Append(data.Key);
sb.Append("_" + i);
paramObj[j] = new SqlParameter(sb.ToString(), SetSqlDataType(variableDataTypes[k])) { Direction = Input, Value = data.Value };
j++;
k++;
}
paramObj[j] = new SqlParameter(("@SystemNumber" + "_" + i), SetSqlDataType("int")) { Direction = Input, Value = _systemNumber };
i++;
j++;
}
return paramObj;
}
private static void ExecuteSqlCommand(StringBuilder sb, params object[] sqlParameters)
{
using (_ctx)
{
_ctx.Database.Connection.Open();
using (var cmd = _ctx.Database.Connection.CreateCommand())
{
cmd.CommandText = sb.ToString();
foreach (var param in sqlParameters)
cmd.Parameters.Add(param);
try
{
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
}
}
