question

StrosalaIoan-2782 avatar image
0 Votes"
StrosalaIoan-2782 asked StrosalaIoan-2782 commented

asp.net mvc expand table using stored procedure

Hi. I have two table Customer and Order which are viewed in the following order: in the index view which is paginated with PagedList.Mvc, appears Customer and in the details page view the order. For the index page I have a stored procedure to fill table and the details I have another stored procedure which fill the order table.

I followed the example from this link: https://www.aspsnippets.com/Articles/Implement-Nested-Grid-GridView-in-ASPNet-MVC.aspx, if I use Sql raw we achieved this expansion.

But I want to get this result through using two stored procedure executed by Dapper.
Please help me if possible to get the same result using storage procedures.

Thank you.

dotnet-aspnet-mvc
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

YijingSun-MSFT avatar image
0 Votes"
YijingSun-MSFT answered StrosalaIoan-2782 commented

Hi @StrosalaIoan-2782 ,
I found you post the same problem and your codes on the stackoverflow. As far as I think,you could execute two stored procedures and then use QueryMultiple method to map the sql . Just like this:

 public ActionResult Index()    
         {   
             using (IDbConnection conn = new SqlConnection(@"xxx"))    
             {    
                string sql = @"exec getCustomer;    
                              exec getOrder;";    
                 using (var multi = conn.QueryMultiple(sql))    
                 {  
                     var t1 = multi.Read<Customer>().ToList();    
                     var t2 = multi.Read<Order>().ToList();   
                 }    
                 return View();    
             }    
         }

Best regards,
Yijing Sun


If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our  documentation  to enable e-mail notifications if you want to receive the related email notification for this thread.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Dear Yijing Sun. I ran your code and in view I get the following error:"Object reference not set to an instance of an object".
Here is the view code:

         @using MVCTest.Models
             @model IEnumerable<MVCTest.Models.CustomerModel>
             @{
                
             }
         @foreach (CustomerModel customer in Model)
     {
     <tr>
     <td>
     <img src="~/Images/plus.png" />
     <div style="display:none">
     <table cellpadding="0" cellspacing="0" class="ChildGrid">
     <tr>
     <th>OrderID</th>
     <th>OrderDate</th>
     </tr>
     <td>@customer.CustomerID</td>
     <td>@customer.ContactName</td>
     </tr>
    
 }
 </table>

Best regards. Thank you for your support




0 Votes 0 ·

Hi @StrosalaIoan-2782 ,
As far as I think,since QueryMultiple could execute two stored procedures, but if you want to show the details of the order about the customer, you need to pass parameter to contact customer and order. You could use Query method. Or you could join two tables in one stored procedure. You could refer to below codes:
123879-new-text-document-2.txt
Best regards,
Yijing Sun

0 Votes 0 ·

Dear Yijing Sun. I followed your code and it works perfectly.
Best regards. Thank you for your support.

0 Votes 0 ·
CesareVesdani-8655 avatar image
0 Votes"
CesareVesdani-8655 answered

I do not know how to help you on this issue.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.