返回原始 SQL 作为字典
本文关键字:字典 SQL 原始 返回 | 更新日期: 2023-09-27 18:36:07
大家好,我正在尝试编写可以使用(字符串)tableName获取选择表信息的代码,但是当我尝试将值放入字典时出现错误。P.S:我已经生成了EF DB模型。
public Dictionary<string, List<object>> GetTableInformation(string tableName, FinkonaDatabaseType type)
{
Dictionary<string, List<object>> _returnableDictionary = new Dictionary<string, List<object>>();
PropertyInfo prop = optimumEntities.GetType().GetProperty(tableName);
Type tableType = prop.PropertyType.GenericTypeArguments[0];
var items = optimumEntities.Database.SqlQuery(tableType, "SELECT * FROM " + tableName);
foreach (var item in items)
{
foreach (PropertyInfo info in item.GetType().GetProperties())
{
if (!_returnableDictionary.ContainsKey(info.Name))
{
_returnableDictionary.Add(info.Name, new List<object>());
}
_returnableDictionary[info.Name].Add(info.GetValue(info, null));
// System.Reflection.TargetException, Object does not match target type.
}
}
return _returnableDictionary;
}
在这里
使用 ADO.NET 数据表会更容易,因为EF用于强类型数据。由于您不太担心返回的数据类型,因此 DataTable 将更易于导航。
下面是一个示例:
public Dictionary<string, List<object>> GetTableInformation(string tableName, FinkonaDatabaseType type)
{
var sqlText = "SELECT * from " + tableName;
DataTable dt = new DataTable();
// Use DataTables to extract the whole table in one hit
using(SqlDataAdapter da = new SqlDataAdapter(sqlText, optimumEntities.Database.ConnectionString)
{
da.Fill(dt);
}
var tableData = new Dictionary<string, List<object>>();
// Go through all columns, retrieving their names and populating the rows
foreach(DataColumn dc in dt.Columns)
{
string columnName = dc.Name;
rowData = new List<object>();
tableData.Add(columnName, rowData);
foreach(DataRow dr in dt.Rows)
{
rowData.Add(dr[columnName]);
}
}
return tableData;
}