建站
咨詢
售后
建站咨詢
新聞中心
這里有您想知道的互聯(lián)網(wǎng)營銷解決方案
深度剖析LINQtoSQL存儲過程
本文將從5個方面來學(xué)習(xí)LINQ to SQL存儲過程，它們其中有LINQ to SQL存儲過程之標(biāo)量返回、LINQ to SQL存儲過程之單一結(jié)果集等等。
創(chuàng)新互聯(lián)專業(yè)為企業(yè)提供江華網(wǎng)站建設(shè)、江華做網(wǎng)站、江華網(wǎng)站設(shè)計、江華網(wǎng)站制作等企業(yè)網(wǎng)站建設(shè)、網(wǎng)頁設(shè)計與制作、江華企業(yè)網(wǎng)站模板建站服務(wù)，10年江華做網(wǎng)站經(jīng)驗，不只是建網(wǎng)站，更提供有價值的思路和整體網(wǎng)絡(luò)服務(wù)。
在我們編寫程序中，往往需要一些存儲過程，LINQ to SQL存儲過程中怎么使用呢？也許比原來的更簡單些。下面我們以NORTHWND.MDF數(shù)據(jù)庫中自帶的幾個存儲過程來理解一下。
1.LINQ to SQL存儲過程之標(biāo)量返回
在數(shù)據(jù)庫中，有名為Customers Count By Region的存儲過程。該存儲過程返回顧客所在"WA"區(qū)域的數(shù)量。
- 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設(shè)計器內(nèi)，它自動生成了以下代碼段：
- [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));
我們需要時，直接調(diào)用就可以了，例如：
- int count = db.CustomersCountByRegion("WA");
- Console.WriteLine(count);
語句描述：這個實例使用存儲過程返回在“WA”地區(qū)的客戶數(shù)。
2.LINQ to SQL存儲過程之單一結(jié)果集
從數(shù)據(jù)庫中返回行集合，并包含用于篩選結(jié)果的輸入?yún)?shù)。 當(dāng)我們執(zhí)行返回行集合的存儲過程時，會用到結(jié)果類，它存儲從存儲過程中返回的結(jié)果。
下面的示例表示一個存儲過程，該存儲過程返回客戶行并使用輸入?yún)?shù)來僅返回將“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設(shè)計器內(nèi)，它自動生成了以下代碼段：
- [Function(Name="dbo.[Customers By City]")]
- public ISingleResult Customers_By_City(
- [Parameter(DbType="NVarChar(20)")] string param1)
- {
- IExecuteResult result = this.ExecuteMethodCall(this, (
- (MethodInfo)(MethodInfo.GetCurrentMethod())), param1);
- return ((ISingleResult )
- (result.ReturnValue));
- }
我們用下面的代碼調(diào)用：
- ISingleResult 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存儲過程之多個可能形狀的單一結(jié)果集
當(dāng)存儲過程可以返回多個結(jié)果形狀時，返回類型無法強(qiáng)類型化為單個投影形狀。盡管 LINQ to SQL 可以生成所有可能的投影類型，但它無法獲知將以何種順序返回它們。 ResultTypeAttribute 屬性適用于返回多個結(jié)果類型的存儲過程，用以指定該過程可以返回的類型的集合。
在下面的 SQL 代碼示例中，結(jié)果形狀取決于輸入（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設(shè)計器內(nèi)，它自動生成了以下代碼段：
- [Function(Name="dbo.[Whole Or Partial Customers Set]")]
- public ISingleResult
- Whole_Or_Partial_Customers_Set([Parameter(DbType="Int")]
- System.Nullable
param1) - {
- IExecuteResult result = this.ExecuteMethodCall(this,
- ((MethodInfo)(MethodInfo.GetCurrentMethod())), param1);
- return ((ISingleResult )
- (result.ReturnValue));
- }
但是，VS2008會把多結(jié)果集存儲過程識別為單結(jié)果集的存儲過程，默認(rèn)生成的代碼我們要手動修改一下，要求返回多個結(jié)果集，像這樣：
- [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
param1) - {
- IExecuteResult result = this.ExecuteMethodCall(this,
- ((MethodInfo)(MethodInfo.GetCurrentMethod())), param1);
- return ((IMultipleResults)(result.ReturnValue));
- }
我們分別定義了兩個分部類，用于指定返回的類型。WholeCustomersSetResult類 如下：（點(diǎn)擊展開）
代碼在這里展開
- 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類 如下：（點(diǎn)擊展開）
代碼在這里展開
- 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;
- }
- }
- }
這樣就可以使用了，下面代碼直接調(diào)用，分別返回各自的結(jié)果集合。
- //返回全部Customer結(jié)果集
- IMultipleResults result = db.Whole_Or_Partial_Customers_Set(1);
- IEnumerable shape1 =
- result.GetResult ();
- foreach (WholeCustomersSetResult compName in shape1)
- {
- Console.WriteLine(compName.CompanyName);
- }
- //返回部分Customer結(jié)果集
- result = db.Whole_Or_Partial_Customers_Set(2);
- IEnumerable shape2 =
- result.GetResult ();
- foreach (PartialCustomersSetResult con in shape2)
- {
- Console.WriteLine(con.ContactName);
- }
語句描述：這個實例使用存儲過程返回“WA”地區(qū)中的一組客戶。返回的結(jié)果集形狀取決于傳入的參數(shù)。如果參數(shù)等于 1，則返回所有客戶屬性。如果參數(shù)等于 2，則返回ContactName屬性。
4.LINQ to SQL存儲過程之多個結(jié)果集
這種存儲過程可以生成多個結(jié)果形狀，但我們已經(jīng)知道結(jié)果的返回順序。
下面是一個按順序返回多個結(jié)果集的存儲過程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拖到設(shè)計器代碼如下：
- [Function(Name="dbo.[Get Customer And Orders]")]
- public ISingleResult
- Get_Customer_And_Orders([Parameter(Name="CustomerID",
- DbType="NChar(5)")] string customerID)
- {
- IExecuteResult result = this.ExecuteMethodCall(this,
- ((MethodInfo)(MethodInfo.GetCurrentMethod())), customerID);
- return ((ISingleResult )
- (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));
- }
同樣，自己手寫類，讓其存儲過程返回各自的結(jié)果集。
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
_OrderID; - private string _CustomerID;
- private System.Nullable
_EmployeeID; - private System.Nullable _OrderDate;
- private System.Nullable _RequiredDate;
- private System.Nullable _ShippedDate;
- private System.Nullable
_ShipVia; - private System.Nullable
_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
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
EmployeeID - {
- get { return this._EmployeeID; }
- set
- {
- if ((this._EmployeeID != value))
- this._EmployeeID = value;
- }
- }
- [Column(Storage = "_OrderDate", DbType = "DateTime")]
- public System.Nullable OrderDate
- {
- get { return this._OrderDate; }
- set
- {
- if ((this._OrderDate != value))
- this._OrderDate = value;
- }
- }
- [Column(Storage = "_RequiredDate", DbType = "DateTime")]
- public System.Nullable RequiredDate
- &
當(dāng)前標(biāo)題：深度剖析LINQtoSQL存儲過程
網(wǎng)頁網(wǎng)址：http://fisionsoft.com.cn/article/dhjshcj.html
