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

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”及其所有订单。

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

标签：LINQ LINQ to SQL

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

系列文章导航：

4.多个结果集

推荐链接

热门文章

最新文章

最新新闻

热门新闻