asp.net-mvc 使用 LINQ 和 ASP.NET MVC 存储过程多表输出

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

提问by Kezzer

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?

我们的系统经常调用存储过程，这些过程输出多个表的结果。以前我们使用 XML 输出来获取每个表并使用 XSLT 正确关联它们。如果我使用 ASP.NET MVC 和 LINQ 调用存储过程，我如何获取每个表，然后根据需要输出数据？

回答by Ole Lynge

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.

这里有一篇关于 LINQ to SQL 和存储过程的文章，特别是“处理 SPROC 中的多个结果形状”部分： LINQ to SQL - 使用存储过程检索数据。

Is that useful in your case?

这对你的情况有用吗？

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

否则，不使用 LINQ to SQL，可能使用SqlDataReader'sNextResult来查看结果，例如：

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 chapterabout 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.

灵感来源：在关于NerdDinner的一章中，Scott Guthrie 谈到了创建自定义“ViewModel”对象来保存数据，例如不容易适应域模型对象的连接。

回答by Pure.Krome

this article hereexplains everything. This is the same article which i linked, in your previous SO question.

这篇文章在这里解释了一切。这是我在您之前的 SO 问题中链接的同一篇文章。