LINQ to SQL语句(20)之存储过程

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属性。

[第1页][第2页][第3页][第4页]

标签：LINQ LINQ to SQL

LINQ to SQL语句(20)之存储过程

系列文章导航：

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

推荐链接

热门文章

最新文章

最新新闻

热门新闻