Linq中使用存储过程作为结果集（转）-猿圈-程序猿的知识社区

标签：com http blog style class div img code log c tar

存储过程

在我们编写程序中，往往需要一些存储过程，在LINQ to SQL中怎么使用呢？也许比原来的更简单些。下面我们以NORTHWND.MDF数据库中自带的几个存储过程来理解一下。

1.标量返回

在数据库中，有名为Customers Count By Region的存储过程。该存储过程返回顾客所在"WA"区域的数量。

ALTER PROCEDURE [dbo].[NonRowset]
    (@param1 NVARCHAR(15))
AS
BEGIN
    SET NOCOUNT ON;
     DECLARE @count int
     SELECT @count = COUNT(*)FROM Customers 
     WHERECustomers.Region = @Param1
     RETURN @count
END

我们只要把这个存储过程拖到O/R设计器内，它自动生成了以下代码段：

[Function(Name = "dbo.[Customers Count By Region]")]
public int Customers_Count_By_Region([Parameter
(DbType = "NVarChar(15)")] string param1)
{
    IExecuteResult result = this.ExecuteMethodCall(this,
    ((MethodInfo)(MethodInfo.GetCurrentMethod())), param1);
    return ((int)(result.ReturnValue));
}

我们需要时，直接调用就可以了，例如：

int count = db.CustomersCountByRegion("WA");
Console.WriteLine(count);

语句描述：这个实例使用存储过程返回在“WA”地区的客户数。

2.单一结果集

从数据库中返回行集合，并包含用于筛选结果的输入参数。当我们执行返回行集合的存储过程时，会用到结果类，它存储从存储过程中返回的结果。

下面的示例表示一个存储过程，该存储过程返回客户行并使用输入参数来仅返回将“London”列为客户城市的那些行的固定几列。

ALTER PROCEDURE [dbo].[Customers By City]
     -- Add the parameters for the stored procedure here
     (@param1 NVARCHAR(20))
AS
BEGIN
     -- SET NOCOUNT ON added to prevent extra result sets from
     -- interfering with SELECT statements.
     SET NOCOUNT ON;
     SELECT CustomerID, ContactName, CompanyName, City from 
     Customers as c where c.City=@param1
END

拖到O/R设计器内，它自动生成了以下代码段：

[Function(Name="dbo.[Customers By City]")]
public ISingleResult<Customers_By_CityResult> Customers_By_City(
[Parameter(DbType="NVarChar(20)")] string param1)
{
    IExecuteResult result = this.ExecuteMethodCall(this, (
    (MethodInfo)(MethodInfo.GetCurrentMethod())), param1);
    return ((ISingleResult<Customers_By_CityResult>)
    (result.ReturnValue));
}

我们用下面的代码调用：

ISingleResult<Customers_By_CityResult> result =
 db.Customers_By_City("London");
foreach (Customers_By_CityResult cust in result)
{
    Console.WriteLine("CustID={0}; City={1}", cust.CustomerID,
        cust.City);
}

语句描述：这个实例使用存储过程返回在伦敦的客户的 CustomerID和City。

3.多个可能形状的单一结果集

当存储过程可以返回多个结果形状时，返回类型无法强类型化为单个投影形状。尽管 LINQ to SQL 可以生成所有可能的投影类型，但它无法获知将以何种顺序返回它们。 ResultTypeAttribute 属性适用于返回多个结果类型的存储过程，用以指定该过程可以返回的类型的集合。

在下面的 SQL 代码示例中，结果形状取决于输入（param1 = 1或param1 = 2）。我们不知道先返回哪个投影。

ALTER PROCEDURE [dbo].[SingleRowset_MultiShape]
     -- Add the parameters for the stored procedure here
     (@param1 int )
AS
BEGIN
     -- SET NOCOUNT ON added to prevent extra result sets from
     -- interfering with SELECT statements.
     SET NOCOUNT ON;
     if(@param1 = 1)
     SELECT * from Customers as c where c.Region = ‘WA‘
     else if (@param1 = 2)
     SELECT CustomerID, ContactName, CompanyName from 
     Customers as c where c.Region = ‘WA‘
END

拖到O/R设计器内，它自动生成了以下代码段：

[Function(Name="dbo.[Whole Or Partial Customers Set]")]
public ISingleResult<Whole_Or_Partial_Customers_SetResult> 
Whole_Or_Partial_Customers_Set([Parameter(DbType="Int")] 
System.Nullable<int> param1)
{
    IExecuteResult result = this.ExecuteMethodCall(this, 
    ((MethodInfo)(MethodInfo.GetCurrentMethod())), param1);
    return ((ISingleResult<Whole_Or_Partial_Customers_SetResult>)
    (result.ReturnValue));
}

但是，VS2008会把多结果集存储过程识别为单结果集的存储过程，默认生成的代码我们要手动修改一下，要求返回多个结果集，像这样：

[Function(Name="dbo.[Whole Or Partial Customers Set]")]
[ResultType(typeof(WholeCustomersSetResult))]
[ResultType(typeof(PartialCustomersSetResult))]
public IMultipleResults Whole_Or_Partial_Customers_Set([Parameter
(DbType="Int")] System.Nullable<int> param1)
{
    IExecuteResult result = this.ExecuteMethodCall(this, 
    ((MethodInfo)(MethodInfo.GetCurrentMethod())), param1);
    return ((IMultipleResults)(result.ReturnValue));
}

我们分别定义了两个分部类，用于指定返回的类型。WholeCustomersSetResult类如下：（点击展开）

代码在这里展开

public partial class WholeCustomersSetResult
{
    private string _CustomerID;
    private string _CompanyName;
    private string _ContactName;
    private string _ContactTitle;
    private string _Address;
    private string _City;
    private string _Region;
    private string _PostalCode;
    private string _Country;
    private string _Phone;
    private string _Fax;
    public WholeCustomersSetResult()
    {
    }
    [Column(Storage = "_CustomerID", DbType = "NChar(5)")]
    public string CustomerID
    {
        get { return this._CustomerID; }
        set
        {
            if ((this._CustomerID != value))
                this._CustomerID = value;
        }
    }
    [Column(Storage = "_CompanyName", DbType = "NVarChar(40)")]
    public string CompanyName
    {
        get { return this._CompanyName; }
        set
        {
            if ((this._CompanyName != value))
                this._CompanyName = value;
        }
    }
    [Column(Storage = "_ContactName", DbType = "NVarChar(30)")]
    public string ContactName
    {
        get { return this._ContactName; }
        set
        {
            if ((this._ContactName != value))
                this._ContactName = value;
        }
    }
    [Column(Storage = "_ContactTitle", DbType = "NVarChar(30)")]
    public string ContactTitle
    {
        get { return this._ContactTitle; }
        set
        {
            if ((this._ContactTitle != value))
                this._ContactTitle = value;
        }
    }
    [Column(Storage = "_Address", DbType = "NVarChar(60)")]
    public string Address
    {
        get { return this._Address; }
        set
        {
            if ((this._Address != value))
                this._Address = value;
        }
    }
    [Column(Storage = "_City", DbType = "NVarChar(15)")]
    public string City
    {
        get { return this._City; }
        set
        {
            if ((this._City != value))
                this._City = value;
        }
    }
    [Column(Storage = "_Region", DbType = "NVarChar(15)")]
    public string Region
    {
        get { return this._Region; }
        set
        {
            if ((this._Region != value))
                this._Region = value;
        }
    }
    [Column(Storage = "_PostalCode", DbType = "NVarChar(10)")]
    public string PostalCode
    {
        get { return this._PostalCode; }
        set
        {
            if ((this._PostalCode != value))
                this._PostalCode = value;
        }
    }
    [Column(Storage = "_Country", DbType = "NVarChar(15)")]
    public string Country
    {
        get { return this._Country; }
        set
        {
            if ((this._Country != value))
                this._Country = value;
        }
    }
    [Column(Storage = "_Phone", DbType = "NVarChar(24)")]
    public string Phone
    {
        get { return this._Phone; }
        set
        {
            if ((this._Phone != value))
                this._Phone = value;
        }
    }
    [Column(Storage = "_Fax", DbType = "NVarChar(24)")]
    public string Fax
    {
        get { return this._Fax; }
        set
        {
            if ((this._Fax != value))
                this._Fax = value;
        }
    }
}

PartialCustomersSetResult类如下：（点击展开）

代码在这里展开

public partial class PartialCustomersSetResult
{
    private string _CustomerID;
    private string _ContactName;
    private string _CompanyName;
    public PartialCustomersSetResult()
    {
    }
    [Column(Storage = "_CustomerID", DbType = "NChar(5)")]
    public string CustomerID
    {
        get { return this._CustomerID; }
        set
        {
            if ((this._CustomerID != value))
                this._CustomerID = value;
        }
    }
    [Column(Storage = "_ContactName", DbType = "NVarChar(30)")]
    public string ContactName
    {
        get { return this._ContactName; }
        set
        {
            if ((this._ContactName != value))
                this._ContactName = value;
        }
    }
    [Column(Storage = "_CompanyName", DbType = "NVarChar(40)")]
    public string CompanyName
    {
        get { return this._CompanyName; }
        set
        {
            if ((this._CompanyName != value))
                this._CompanyName = value;
        }
    }
}

这样就可以使用了，下面代码直接调用，分别返回各自的结果集合。

//返回全部Customer结果集
IMultipleResults result = db.Whole_Or_Partial_Customers_Set(1);
IEnumerable<WholeCustomersSetResult> shape1 =
 result.GetResult<WholeCustomersSetResult>();
foreach (WholeCustomersSetResult compName in shape1)
{
    Console.WriteLine(compName.CompanyName);
}
//返回部分Customer结果集
result = db.Whole_Or_Partial_Customers_Set(2);
IEnumerable<PartialCustomersSetResult> shape2 =
 result.GetResult<PartialCustomersSetResult>();
foreach (PartialCustomersSetResult con in shape2)
{
    Console.WriteLine(con.ContactName);
}

语句描述：这个实例使用存储过程返回“WA”地区中的一组客户。返回的结果集形状取决于传入的参数。如果参数等于 1，则返回所有客户属性。如果参数等于 2，则返回ContactName属性。

4.多个结果集

这种存储过程可以生成多个结果形状，但我们已经知道结果的返回顺序。

下面是一个按顺序返回多个结果集的存储过程Get Customer And Orders。返回顾客ID为"SEVES"的顾客和他们所有的订单。

ALTER PROCEDURE [dbo].[Get Customer And Orders]
(@CustomerID nchar(5))
    -- Add the parameters for the stored procedure here
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    SELECT * FROM Customers AS c WHERE c.CustomerID = @CustomerID  
    SELECT * FROM Orders AS o WHERE o.CustomerID = @CustomerID
END

拖到设计器代码如下：

[Function(Name="dbo.[Get Customer And Orders]")]
public ISingleResult<Get_Customer_And_OrdersResult>
Get_Customer_And_Orders([Parameter(Name="CustomerID",
DbType="NChar(5)")] string customerID)
{
     IExecuteResult result = this.ExecuteMethodCall(this,
     ((MethodInfo)(MethodInfo.GetCurrentMethod())), customerID);
     return ((ISingleResult<Get_Customer_And_OrdersResult>)
     (result.ReturnValue));
}

同样，我们要修改自动生成的代码：

[Function(Name="dbo.[Get Customer And Orders]")]
[ResultType(typeof(CustomerResultSet))]
[ResultType(typeof(OrdersResultSet))]
public IMultipleResults Get_Customer_And_Orders
([Parameter(Name="CustomerID",DbType="NChar(5)")]
string customerID)
{
    IExecuteResult result = this.ExecuteMethodCall(this,
    ((MethodInfo)(MethodInfo.GetCurrentMethod())), customerID);
    return ((IMultipleResults)(result.ReturnValue));
}

同样，自己手写类，让其存储过程返回各自的结果集。

CustomerResultSet类

代码在这里展开

public partial class CustomerResultSet
{

    private string _CustomerID;
    private string _CompanyName;
    private string _ContactName;
    private string _ContactTitle;
    private string _Address;
    private string _City;
    private string _Region;
    private string _PostalCode;
    private string _Country;
    private string _Phone;
    private string _Fax;
    public CustomerResultSet()
    {
    }
    [Column(Storage = "_CustomerID", DbType = "NChar(5)")]
    public string CustomerID
    {
        get { return this._CustomerID; }
        set
        {
            if ((this._CustomerID != value))
                this._CustomerID = value;
        }
    }
    [Column(Storage = "_CompanyName", DbType = "NVarChar(40)")]
    public string CompanyName
    {
        get { return this._CompanyName; }
        set
        {
            if ((this._CompanyName != value))
                this._CompanyName = value;
        }
    }
    [Column(Storage = "_ContactName", DbType = "NVarChar(30)")]
    public string ContactName
    {
        get { return this._ContactName; }
        set
        {
            if ((this._ContactName != value))
                this._ContactName = value;
        }
    }
    [Column(Storage = "_ContactTitle", DbType = "NVarChar(30)")]
    public string ContactTitle
    {
        get { return this._ContactTitle; }
        set
        {
            if ((this._ContactTitle != value))
                this._ContactTitle = value;
        }
    }
    [Column(Storage = "_Address", DbType = "NVarChar(60)")]
    public string Address
    {
        get { return this._Address; }
        set
        {
            if ((this._Address != value))
                this._Address = value;
        }
    }
    [Column(Storage = "_City", DbType = "NVarChar(15)")]
    public string City
    {
        get { return this._City; }
        set
        {
            if ((this._City != value))
                this._City = value;
        }
    }
    [Column(Storage = "_Region", DbType = "NVarChar(15)")]
    public string Region
    {
        get { return this._Region; }
        set
        {
            if ((this._Region != value))
                this._Region = value;
        }
    }
    [Column(Storage = "_PostalCode", DbType = "NVarChar(10)")]
    public string PostalCode
    {
        get { return this._PostalCode; }
        set
        {
            if ((this._PostalCode != value))
                this._PostalCode = value;
        }
    }
    [Column(Storage = "_Country", DbType = "NVarChar(15)")]
    public string Country
    {
        get { return this._Country; }
        set
        {
            if ((this._Country != value))
                this._Country = value;
        }
    }
    [Column(Storage = "_Phone", DbType = "NVarChar(24)")]
    public string Phone
    {
        get { return this._Phone; }
        set
        {
            if ((this._Phone != value))
                this._Phone = value;
        }
    }

    [Column(Storage = "_Fax", DbType = "NVarChar(24)")]
    public string Fax
    {
        get { return this._Fax; }
        set
        {
            if ((this._Fax != value))
                this._Fax = value;
        }
    }
}

OrdersResultSet类

代码在这里展开

public partial class OrdersResultSet
{
    private System.Nullable<int> _OrderID;
    private string _CustomerID;
    private System.Nullable<int> _EmployeeID;
    private System.Nullable<System.DateTime> _OrderDate;
    private System.Nullable<System.DateTime> _RequiredDate;
    private System.Nullable<System.DateTime> _ShippedDate;
    private System.Nullable<int> _ShipVia;
    private System.Nullable<decimal> _Freight;
    private string _ShipName;
    private string _ShipAddress;
    private string _ShipCity;
    private string _ShipRegion;
    private string _ShipPostalCode;
    private string _ShipCountry;
    public OrdersResultSet()
    {
    }
    [Column(Storage = "_OrderID", DbType = "Int")]
    public System.Nullable<int> OrderID
    {
        get { return this._OrderID; }
        set
        {
            if ((this._OrderID != value))
                this._OrderID = value;
        }
    }
    [Column(Storage = "_CustomerID", DbType = "NChar(5)")]
    public string CustomerID
    {
        get { return this._CustomerID; }
        set
        {
            if ((this._CustomerID != value))
                this._CustomerID = value;
        }
    }
    [Column(Storage = "_EmployeeID", DbType = "Int")]
    public System.Nullable<int> EmployeeID
    {
        get { return this._EmployeeID; }
        set
        {
            if ((this._EmployeeID != value))
                this._EmployeeID = value;
        }
    }
    [Column(Storage = "_OrderDate", DbType = "DateTime")]
    public System.Nullable<System.DateTime> OrderDate
    {
        get { return this._OrderDate; }
        set
        {
            if ((this._OrderDate != value))
                this._OrderDate = value;
        }
    }
    [Column(Storage = "_RequiredDate", DbType = "DateTime")]
    public System.Nullable<System.DateTime> RequiredDate
    {
        get { return this._RequiredDate; }
        set
        {
            if ((this._RequiredDate != value))
                this._RequiredDate = value;
        }
    }
    [Column(Storage = "_ShippedDate", DbType = "DateTime")]
    public System.Nullable<System.DateTime> ShippedDate
    {
        get { return this._ShippedDate; }
        set
        {
            if ((this._ShippedDate != value))
                this._ShippedDate = value;
        }
    }
    [Column(Storage = "_ShipVia", DbType = "Int")]
    public System.Nullable<int> ShipVia
    {
        get { return this._ShipVia; }
        set
        {
            if ((this._ShipVia != value))
                this._ShipVia = value;
        }
    }
    [Column(Storage = "_Freight", DbType = "Money")]
    public System.Nullable<decimal> Freight
    {
        get { return this._Freight; }
        set
        {
            if ((this._Freight != value))
                this._Freight = value;
        }
    }
    [Column(Storage = "_ShipName", DbType = "NVarChar(40)")]
    public string ShipName
    {
        get { return this._ShipName; }
        set
        {
            if ((this._ShipName != value))
                this._ShipName = value;
        }
    }
    [Column(Storage = "_ShipAddress", DbType = "NVarChar(60)")]
    public string ShipAddress
    {
        get { return this._ShipAddress; }
        set
        {
            if ((this._ShipAddress != value))
                this._ShipAddress = value;
        }
    }
    [Column(Storage = "_ShipCity", DbType = "NVarChar(15)")]
    public string ShipCity
    {
        get { return this._ShipCity; }
        set
        {
            if ((this._ShipCity != value))
                this._ShipCity = value;
        }
    }
    [Column(Storage = "_ShipRegion", DbType = "NVarChar(15)")]
    public string ShipRegion
    {
        get { return this._ShipRegion; }
        set
        {
            if ((this._ShipRegion != value))
                this._ShipRegion = value;
        }
    }
    [Column(Storage = "_ShipPostalCode", DbType = "NVarChar(10)")]
    public string ShipPostalCode
    {
        get { return this._ShipPostalCode; }
        set
        {
            if ((this._ShipPostalCode != value))
                this._ShipPostalCode = value;
        }
    }

    [Column(Storage = "_ShipCountry", DbType = "NVarChar(15)")]
    public string ShipCountry
    {
        get { return this._ShipCountry; }
        set
        {
            if ((this._ShipCountry != value))
                this._ShipCountry = value;
        }
    }
}

这时，只要调用就可以了。

IMultipleResults result = db.Get_Customer_And_Orders("SEVES");
//返回Customer结果集
IEnumerable<CustomerResultSet> customer = 
result.GetResult<CustomerResultSet>();
//返回Orders结果集
IEnumerable<OrdersResultSet> orders = 
 result.GetResult<OrdersResultSet>();
//在这里，我们读取CustomerResultSet中的数据
foreach (CustomerResultSet cust in customer)
{
    Console.WriteLine(cust.CustomerID);
}

语句描述：这个实例使用存储过程返回客户“SEVES”及其所有订单。

5.带输出参数

LINQ to SQL 将输出参数映射到引用参数，并且对于值类型，它将参数声明为可以为 null。

下面的示例带有单个输入参数（客户 ID）并返回一个输出参数（该客户的总销售额）。

ALTER PROCEDURE [dbo].[CustOrderTotal] 
@CustomerID nchar(5),
@TotalSales money OUTPUT
AS
SELECT @TotalSales = SUM(OD.UNITPRICE*(1-OD.DISCOUNT) * OD.QUANTITY)
FROM ORDERS O, "ORDER DETAILS" OD
where O.CUSTOMERID = @CustomerID AND O.ORDERID = OD.ORDERID

把这个存储过程拖到设计器中，图片如下：

其生成代码如下：

[Function(Name="dbo.CustOrderTotal")]
public int CustOrderTotal(
[Parameter(Name="CustomerID", DbType="NChar(5)")]string customerID,
[Parameter(Name="TotalSales", DbType="Money")]
  ref System.Nullable<decimal> totalSales)
{
    IExecuteResult result = this.ExecuteMethodCall(this,
    ((MethodInfo)(MethodInfo.GetCurrentMethod())),
    customerID, totalSales);
    totalSales = ((System.Nullable<decimal>)
    (result.GetParameterValue(1)));
    return ((int)(result.ReturnValue));
}

我们使用下面的语句调用此存储过程：注意：输出参数是按引用传递的，以支持参数为“in/out”的方案。在这种情况下，参数仅为“out”。

decimal? totalSales = 0;
string customerID = "ALFKI";
db.CustOrderTotal(customerID, ref totalSales);
Console.WriteLine("Total Sales for Customer ‘{0}‘ = {1:C}", 
customerID, totalSales);

语句描述：这个实例使用返回 Out 参数的存储过程。

好了，就说到这里了，其增删改操作同理。相信大家通过这5个实例理解了存储过程。

Linq中使用存储过程作为结果集（转）,布布扣,bubuko.com

Linq中使用存储过程作为结果集（转）

菜单 学习猿地 - LMONKEY

开通学习猿地VIP

尊享10项VIP特权 持续新增

知识通关挑战

打卡带练！告别无效练习

接私单赚外块

VIP优先接，累计金额超百万

学习猿地私房课免费学

大厂实战课仅对VIP开放

你的一对一导师

每月可免费咨询大牛30次

领取更多软件工程师实用特权

Java开发工程师

何以解忧，唯有 Java！

Python开发工程师

人生苦短我要学Python！

PHP开发工程师

PHP是世界上最好的编程语言!

GO开发工程师

想优雅的写程序，赶紧GO!

大数据开发工程师

弄大数据，就是在搞革命!

前端开发工程师

不仅最好，而且最全!

UI开发工程师

这个世界从来不缺少美，缺少你来创造美!

Linux运维工程师

不只是说说而已！

WEB前端1+X

不仅为考证，轻松做开发

计算机二级（C语言）

一切只为考证！

猿工手册

各种工具精挑细选

猿材料

各种工具精挑细选

猿代码

各种工具精挑细选

猿著课件

各种工具精挑细选

知识题库

知识闯关节节高，刷题涨知识！

阶段练习

单元测试知识学习状况秒掌握！

期末考试

期末测试，学习情况即刻知道！

面试题库

最新的企业技术人员招聘真题练习！

Java专区

2948篇 ｜ 24.5万人浏览

Python专区

2593篇 ｜ 19.5万人浏览

大数据专区

2948篇 ｜ 18.5万人浏览

PHP专区

3953篇 ｜ 27.5万人浏览

Go专区

1382篇 ｜ 12.5万人浏览

Web前端专区

1453篇 ｜ 31.5万人浏览

Linux云计算

1230篇 ｜ 4.5万人浏览

其他专区

53232篇文章 ｜ 11.5万人浏览

搜索

历史记录 清除记录

近期热搜

项目开发全程实录（电商EW_Shop）

8675 人 1年前

轻松学会Laravel-项目篇（商城API）

2022 人 1年前

Python数据分析2.0-金融

325 人 1年前

Python办公自动化2.0

241 人 1年前

使用账号登录

启用更安全省心的  微信登录

Java开发工程师

何以解忧，唯有 Java！

Python开发工程师

菜单学习猿地 - LMONKEY

尊享10项VIP特权持续新增

2948篇｜ 24.5万人浏览

2593篇｜ 19.5万人浏览

2948篇｜ 18.5万人浏览

3953篇｜ 27.5万人浏览

1382篇｜ 12.5万人浏览

1453篇｜ 31.5万人浏览

1230篇｜ 4.5万人浏览

53232篇文章｜ 11.5万人浏览

历史记录清除记录

2948篇｜ 24.5万人浏览

2593篇｜ 19.5万人浏览

2948篇｜ 18.5万人浏览

3953篇｜ 27.5万人浏览

1382篇｜ 12.5万人浏览

1453篇｜ 31.5万人浏览

1230篇｜ 4.5万人浏览

53232篇文章｜ 11.5万人浏览