Linq to sql 接收存储过程返回的多个结果集

浏览数：45 / 时间：2015年06月12日

故事前提。。。。。。。。。。

一、返回顺序结果集

存储过程实例

CREATE PROCEDURE MultipleResultTypesSequentially
AS
select * from products
select * from customers

修改vs生成的存储过程代码

[Function(Name="dbo.MultipleResultTypesSequentially")]
    [ResultType(typeof(MultipleResultTypesSequentiallyResult1))]
    [ResultType(typeof(MultipleResultTypesSequentiallyResult2))]
    public IMultipleResults MultipleResultTypesSequentially()
    {
        IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
        return ((IMultipleResults)(result.ReturnValue));
    }

调用存储过程

IMultipleResults sprocResults =
    db.MultipleResultTypesSequentially();

// First read products.
foreach (Product prod in sprocResults.GetResult<Product>())
{
    Console.WriteLine(prod.ProductID);
}

// Next read customers.
foreach (Customer cust in sprocResults.GetResult<Customer>())
{
    Console.WriteLine(cust.CustomerID);
}

二、多个结果返回集（如不通参数返回不通类型结果集）

存储过程实例

CREATE PROCEDURE VariableResultShapes(@shape int)
AS
if(@shape = 1)
    select CustomerID, ContactTitle, CompanyName from customers
else if(@shape = 2)
    select OrderID, ShipName from orders

C# 存储过程代码

[Function(Name="dbo.VariableResultShapes")]
    [ResultType(typeof(VariableResultShapesResult1))]
    [ResultType(typeof(VariableResultShapesResult2))]
    public IMultipleResults VariableResultShapes([Parameter(DbType="Int")] System.Nullable<int> shape)
    {
        IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), shape);
        return ((IMultipleResults)(result.ReturnValue));
    }

存储过程调用

IMultipleResults result = db.VariableResultShapes(1);

// Iterate through the list and write results (the company names)
// to the console.
foreach(VariableResultShapesResult1 compName in
    result.GetResult<VariableResultShapesResult1>())
{
    Console.WriteLine(compName.CompanyName);
}

// Pause to view company names; press Enter to continue.
Console.ReadLine();

// Assign the results of the procedure with an argument
// of (2) to local variable ‘result‘.
IMultipleResults result2 = db.VariableResultShapes(2);

// Iterate through the list and write results (the order IDs)
// to the console.
foreach (VariableResultShapesResult2 ord in
    result2.GetResult<VariableResultShapesResult2>())
{
    Console.WriteLine(ord.OrderID);
}