Calling two stored procedures and combining results into a single model within one controller

Question

I am trying to call multiple stored procedures from one controller in my MVC5 web application. I then combine the results of the two procedures into one model.

public ActionResult ItemDetails(string itemNo, string custNo)
{
    SqlParameter[] param =
   {
     new SqlParameter("@itemNo",itemNo),
     new SqlParameter("@custNo", custNo)
    };

    var sqlData = itemDb.Database.SqlQuery<ItemDetailsModel>("ItemDetail @itemNo, @custNo", param).ToList();

    var whsID = new SqlParameter("@warehouse", sqlData?[0].WhsID);

    var sqlData2 = itemDb.Database.SqlQuery<ItemDetailsModel>("ItemPoLookup @itemNo, @warehouse", new SqlParameter("@itemNo", itemNo), whsID).ToList();

    sqlData.AddRange(sqlData2);

    return View(sqlData);
}

Is it possible to make this happen while keeping the models separate? Should I do two get requests to my web server, one for each stored procedure, instead of combining this all into one? And if so advice on returning multiple models to the cshtml view would be appreciated.

You better create ViewModel including two models you get from your stored procedure; send that ViewModel to your Controller; You can look at [this question](http://stackoverflow.com/questions/14496568/show-multiple-models-in-a-single-view-using-viewmodel-in-razor-mvc3-with-only-d) or [this one](http://www.codeproject.com/Articles/687061/Multiple-Models-in-a-View-in-ASP-NET-MVC-MVC) — techspider, Aug 11 '16 at 15:52

score 1 · Answer 1 · answered Aug 11 '16 at 16:01

if you dont want to create an other ViewModel and want the results seperate, use this (you may not get intellisense while using this model in view)

dynamic viewModel = new ExpandoObject();
viewModel.sqlData = sqlData;
viewModel.sqlData2 = sqlData2;

return View(viewModel);

score 1 · Accepted Answer · answered Aug 11 '16 at 16:09

1

Pass it like

List<List<<ItemDetailsModel>>

or

Dictionary<string, List<ItemDetailsModel>>`

If you are using Dictionary<TKey, TValue> you can pass a string to identify the two stored procedures.

answered Aug 11 '16 at 16:09

naveen

53,448
46
161
251

Calling two stored procedures and combining results into a single model within one controller

2 Answers2