How to execute a stored procedure returning a pivot table in Web API

Question

How to execute a stored procedure returning a pivot table and return data as JSON in a API. I want to display its data on a web page using API. What I'm getting is just a list of objects but not the data in it. My procedure is returning 983 records of table rows in it... But column change according to passed parameters So for one kind of parameter i can have 20 column buy for the other i can have just 1 By executing procedure using

    var ls = db.Database.SqlQuery<dynamic>("exec [Sp_StoreItemParty] 'Amount','01-Apr-2020', '06-Sep-2020','', '', '', ''").ToList();
    return Json(ls, JsonRequestBehavior.AllowGet);

How to execute a stored procedure returning a pivot table and return it as Web API in JSON format . All i get is a list of objects but none of the objects can further be accessed or further displayed in Postman on On Firefox.

CodingYoshi · Accepted Answer · 2020-09-06T13:13:15.133

SqlQuery works only with known types, not dynamic. You can

Either create a class that represents your result record and use that. Or

Use a data reader as shown here and copy pasted below.

public static IEnumerable<dynamic> CollectionFromSql(this DbContext dbContext, string Sql, Dictionary<string, object> Parameters)
{
    using (var cmd = dbContext.Database.GetDbConnection().CreateCommand())
    {
        cmd.CommandText = Sql;
        if (cmd.Connection.State != ConnectionState.Open)
            cmd.Connection.Open();

        foreach (KeyValuePair<string, object> param in Parameters)
        {
            DbParameter dbParameter = cmd.CreateParameter();
            dbParameter.ParameterName = param.Key;
            dbParameter.Value = param.Value;
            cmd.Parameters.Add(dbParameter);
        }

        //var retObject = new List<dynamic>();
        using (var dataReader = cmd.ExecuteReader())
        {

            while (dataReader.Read())
            {
                var dataRow = GetDataRow(dataReader);
                yield return dataRow ;

            }
        }


    }
}

private static dynamic GetDataRow(DbDataReader dataReader)
{
    var dataRow = new ExpandoObject() as IDictionary<string, object>;
    for (var fieldCount = 0; fieldCount < dataReader.FieldCount; fieldCount++)
        dataRow.Add(dataReader.GetName(fieldCount), dataReader[fieldCount]);
    return dataRow;
}

Usage:

List<dynamic> MyList = MyDbContext.CollectionFromSql("SELECT * FROM \"User\" WHERE UserID = @UserID",
    new Dictionary<string, object> { { "@UserID", 1 } }).ToList();

score 0 · Answer 2 · answered Sep 06 '20 at 12:56

because your object is dynamic object you have to resolve the structure of the object in runtime by reflection.

Then when you have the name and the value of each property of the object you can manually build your json.

Type myType = myObject.GetType();
IList<PropertyInfo> props = new List<PropertyInfo>(myType.GetProperties());

foreach (PropertyInfo prop in props)
{
    String propName = prop.name;
    object propValue = prop.GetValue(myObject, null);

    // Do something with propValue
}

small tip: Reflection can be very efficient and damned complex logic. if you can change the stored procedure to return fixed subset of column it will faster and simpler.

How to execute a stored procedure returning a pivot table in Web API

2 Answers2