Stored Procedure Multiple-Table Output With LINQ and ASP.NET MVC

Question

Quite often our systems call stored procedures which output multiple tables worth of results. Previously we used XML outputs to get each table and relate them correctly using XSLT. If I were using ASP.NET MVC with LINQ calling a stored procedure, how do I get each of the tables and then output the data as necessary?

Answer 1

There's an article here about LINQ to SQL and stored procedures, especially the section 'Handling Multiple Result Shapes from SPROCs': LINQ to SQL - Retrieving Data Using Stored Procedures.

Is that useful in your case?

Otherwise, not using LINQ to SQL, maybe use SqlDataReader's NextResult to go through the results, for example:

IList<Employee> employees = new List<Employee>();
IList<Customer> customers = new List<Customer>();
using (SqlConnection connection = new SqlConnection
    (Properties.Settings.Default.NorthwindConnectionString))
using (SqlCommand command = new SqlCommand
    ("GetEmployeesAndCustomers", connection))
{
    command.CommandType = CommandType.StoredProcedure;
    connection.Open();
    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            Employee e = new Employee{EmployeeID = (int)reader["EmployeeID"]};
            employees.Add(e);
        }
        reader.NextResult();
        while (reader.Read())
        {
            Customer c = new Customer{CustomerID = (string)reader["CustomerID"]};
            customers.Add(c);
        }
    }
}

---

Edit: Example of how to handle custom data combinations that are not easily fit into domain model objects; in this case retrieving orders along with the customers for the orders:

namespace Company.Application.ViewModel
{
    public class CustomerOrder
    {
        public string CustomerID { get; set; }
        public string CustomerName { get; set; }
        public int OrderID { get; set; }
        public DateTime? OrderDate { get; set; }
    }
}
namespace Company.Application.Repository
{
    public class CustomerOrderRepository
    {
        public IList<CustomerOrder> GetCustomerOrders()
        {
            NorthwindDataContext db = new NorthwindDataContext();
            var custorders = from customer in db.Customers
                             join order in db.Orders
                             on customer.CustomerID equals order.CustomerID
                             select new CustomerOrder
                             {
                                 CustomerID = customer.CustomerID,
                                 CustomerName = customer.CompanyName,
                                 OrderID = order.OrderID,
                                 OrderDate = order.OrderDate
                             };
            return custorders.ToList();
        }
    }
}

Inspiration for this: In the chapter about NerdDinner, Scott Guthrie talks about creating custom 'ViewModel' objects to hold data from for example joins that are not easily fit into the domain model objects.

Answer 2

Hi again Kezzer -> this article here explains everything. This is the same article which i linked, in your previous SO question.

Answer 3

1. List item df
2. List item
3. List item