将原始sql作为字典返回(return raw sql as dictionary)

将原始sql作为字典返回(return raw sql as dictionary)

各位大家好我正在尝试编写可以使用（字符串）tableName获取选择表信息的代码，但是当我尝试将值放到Dictionary时出现错误。 PS：我已经生成了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; }

Hello everyone I'm trying to write code that can get select table information with (string)tableName, but i get error when i try to put value to Dictionary. P.S : I have generated EF DB model.

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 DataTables在这里会更容易，因为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; }

Using ADO.NET DataTables will be easier here, as EF is used for strongly typing data. Since you are not too worried about the data types coming back, a DataTable will be easier to navigate.

Here is an examples of this:

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