In MVC 4 and EF 5 i want to run dynamic query.
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
Row1...
Row2..
Appreciate any suggestion.
You could use a raw SQL query because EF doesn't support that:
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>
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).
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;
}
Finally i made is using TypeBuilder option suggested by "Mortalus" and ExpandoObject object. It has little performance overhead right now.
Take Typebuilder code from "Mortalus" answer then i made code according to my requirement as below.
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.
Context.Database.SqlQuery(Type, sqlStatement, null); - Type == ? thanksI 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 TypeBuilder is described in details in the post I have attached.
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.
Unfortunately, EF won't materialize objects unless it knows their Type.
If this is really necessary for you, I think your best bet would be to fall back to ADO.NET and DataTable.
Similarly post by Darin Dimitrov, but it returns 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
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.
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;
}
}
}
}
These approaches seemed a bit complicated to me, at least in my situation. All I needed was to return a DataTable so it could be previewed. Meaning every value could be a string.
So I created two classes:
public class DynamicResultModel
{
public DynamicResultColumnModel headers = new();
public List<DynamicResultColumnModel> rows = new();
}
public class DynamicResultColumnModel
{
public List<string> columns = new();
}
Then created a simple helper class to basically serialize and deserialize a DataTable into and out of a DynamicResultModel
public static DynamicResultModel DataTableToDynamic(DataTable dtData)
{
DynamicResultModel result = new();
DynamicResultColumnModel headers = new();
foreach (DataColumn col in dtData.Columns)
{
result.headers.columns.Add(col.ColumnName);
}
foreach (DataRow row in dtData.Rows)
{
DynamicResultColumnModel rowData = new();
foreach (var item in row.ItemArray)
{
rowData.columns.Add(item.ToString());
}
result.rows.Add(rowData);
}
return result;
}
The only caveat was that I could not return a DynamicResultModel from my controller, I had to serialize it into a string first. Regardless, this worked for me and saved me a lot of time.
So my API endpoint definition was
Task<ApiResponse<string>>
Instead of
Task<ApiResponse<DynamicResponseModel>>
