Question
Because the query is so dynamic, there are times when a storeprocdure returns a collection but I am unsure of the object structure.
One search can produce the following results:
Id | Location | MarketSegment | ... n columns
and another may come back
Id | Sales Rep | Location | Region | ... n columns
As you can see from the code below, I'm just returning a "object." How do I make this work even though I know it won't?
using (DbContext db = new Context())
{
var items = db.Database.SqlQuery<object>(
"SP @Param1, @Param2",
new SqlParameter("Param1", ped),
new SqlParameter("Param2", 25)
).ToList();
return Request.CreateResponse<List<object>>(HttpStatusCode.OK, items);
}
EDIT:
I'm not sure if displaying the SP will be helpful in any way, unless I can better explain it.
Every column is displayed as a Custom Field. The amount of Custom Fields that users can create is n. As a result, if you run the SP for User1 and he has 5 custom fields, each of those custom fields will be represented in columns, but if User2 only has 3 custom fields, only those 3 columns will be displayed. The name and quantity of custom fields are something over which I have no control.
Popular Answer
Zzz-5-Zzz cannot be used for custom fields.
Creates a raw SQL query that will return elements of the given generic type. The type can be any type that has properties that match the names of the columns returned from the query, or can be a simple primitive type. - MSDN
But you may accomplish it by using ExecuteReader.
using (var db = new Context())
{
db.Database.Connection.Open();
var cmd = db.Database.Connection.CreateCommand();
cmd.CommandText = "SP @Param1, @Param2";
cmd.Parameters.Add(new SqlParameter("Param1", ped));
cmd.Parameters.Add(new SqlParameter("Param2", 25));
List<List<object>> items = new List<List<object>>();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
var item = new List<Object>();
items.Add(item);
for (int i = 0; i < reader.FieldCount ; i++)
item.Add(reader[i]);
}
return Request.CreateResponse<List<object>>(HttpStatusCode.OK, items);
}
Related Questions
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
