How to return dynamic object from SQL query

asp.net-web-api c# dbcontext entity-framework sql

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.

1
1
8/25/2014 9:40:43 PM

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);
}
7
8/26/2014 3:05:33 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow