在我们编写程序中,往往需要一些存储过程,LINQ to SQL存储过程中怎么使用呢?也许比原来的更简单些。本文以NORTHWND.MDF数据库中自带的几个存储过程来理解一下。
本文将从5个方面来学习LINQ to SQL存储进程,它们其中有LINQ to SQL存储进程之标量回来、LINQ to SQL存储进程之单一成果集等等。
在咱们编写程序中,往往需求一些存储进程,LINQ to SQL存储进程中怎样运用呢?或许比本来的更简略些。下面咱们以NORTHWND.MDF数据库中自带的几个存储进程来了解一下。
1.LINQ to SQL存储进程之标量回来
在数据库中,有名为Customers Count By Region的存储进程。该存储进程回来顾客地点"WA"区域的数量。
- ALTERPROCEDURE[dbo].[NonRowset]
- (@param1NVARCHAR(15))
- AS
- BEGIN
- SETNOCOUNTON;
- DECLARE@countint
- SELECT@count=COUNT(*)FROMCustomers
- WHERECustomers.Region=@Param1
- RETURN@count
END咱们只要把这个存储进程拖到O/R规划器内,它主动生成了以下代码段:
- [Function(Name="dbo.[CustomersCountByRegion]")]
- publicintCustomers_Count_By_Region([Parameter
- (DbType="NVarChar(15)")]stringparam1)
- {
- IExecuteResultresult=this.ExecuteMethodCall(this,
- ((MethodInfo)(MethodInfo.GetCurrentMethod())),param1);
- return((int)(result.ReturnValue));
咱们需求时,直接调用就能够了,例如:
- intcount=db.CustomersCountByRegion("WA");
- Console.WriteLine(count);
句子描绘:这个实例运用存储进程回来在“WA”区域的客户数。
2.LINQ to SQL存储进程之单一成果集
从数据库中回来行调集,并包括用于挑选成果的输入参数。 当咱们履行回来行调集的存储进程时,会用到成果类,它存储从存储进程中回来的成果。
下面的示例表明一个存储进程,该存储进程回来客户行并运用输入参数来仅回来将“London”列为客户城市的那些行的固定几列。
- ALTERPROCEDURE[dbo].[CustomersByCity]
- --Addtheparametersforthestoredprocedurehere
- (@param1NVARCHAR(20))
- AS
- BEGIN
- --SETNOCOUNTONaddedtopreventextraresultsetsfrom
- --interferingwithSELECTstatements.
- SETNOCOUNTON;
- SELECTCustomerID,ContactName,CompanyName,Cityfrom
- Customersascwherec.City=@param1
END拖到O/R规划器内,它主动生成了以下代码段:
- [Function(Name="dbo.[CustomersByCity]")]
- publicISingleResultCustomers_By_City(
- [Parameter(DbType="NVarChar(20)")]stringparam1)
- {
- IExecuteResultresult=this.ExecuteMethodCall(this,(
- (MethodInfo)(MethodInfo.GetCurrentMethod())),param1);
- return((ISingleResult)
- (result.ReturnValue));
- }
咱们用下面的代码调用:
- ISingleResultresult=
- db.Customers_By_City("London");
- foreach(Customers_By_CityResultcustinresult)
- {
- Console.WriteLine("CustID={0};City={1}",cust.CustomerID,
- cust.City);
- }
句子描绘:这个实例运用存储进程回来在伦敦的客户的 CustomerID和City。
3.LINQ to SQL存储进程之多个或许形状的单一成果集
当存储进程能够回来多个成果形状时,回来类型无法强类型化为单个投影形状。虽然 LINQ to SQL 能够生成一切或许的投影类型,但它无法获悉将以何种次序回来它们。 ResultTypeAttribute 特点适用于回来多个成果类型的存储进程,用以指定该进程能够回来的类型的调集。
鄙人面的 SQL 代码示例中,成果形状取决于输入(param1 = 1或param1 = 2)。咱们不知道先回来哪个投影。
- ALTERPROCEDURE[dbo].[SingleRowset_MultiShape]
- --Addtheparametersforthestoredprocedurehere
- (@param1int)
- AS
- BEGIN
- --SETNOCOUNTONaddedtopreventextraresultsetsfrom
- --interferingwithSELECTstatements.
- SETNOCOUNTON;
- if(@param1=1)
- SELECT*fromCustomersascwherec.Region='WA'
- elseif(@param1=2)
- SELECTCustomerID,ContactName,CompanyNamefrom
- Customersascwherec.Region='WA'
END拖到O/R规划器内,它主动生成了以下代码段:
- [Function(Name="dbo.[WholeOrPartialCustomersSet]")]
- publicISingleResult
- Whole_Or_Partial_Customers_Set([Parameter(DbType="Int")]
- System.Nullableparam1)
- {
- IExecuteResultresult=this.ExecuteMethodCall(this,
- ((MethodInfo)(MethodInfo.GetCurrentMethod())),param1);
- return((ISingleResult)
- (result.ReturnValue));
- }
可是,VS2008会把多成果集存储进程识别为单成果集的存储进程,默许生成的代码咱们要手动修正一下,要求回来多个成果集,像这样:
- [Function(Name="dbo.[WholeOrPartialCustomersSet]")]
- [ResultType(typeof(WholeCustomersSetResult))]
- [ResultType(typeof(PartialCustomersSetResult))]
- publicIMultipleResultsWhole_Or_Partial_Customers_Set([Parameter
- (DbType="Int")]System.Nullableparam1)
- {
- IExecuteResultresult=this.ExecuteMethodCall(this,
- ((MethodInfo)(MethodInfo.GetCurrentMethod())),param1);
- return((IMultipleResults)(result.ReturnValue));
- }
咱们别离界说了两个分部类,用于指定回来的类型。WholeCustomersSetResult类 如下:(点击打开)
代码在这里打开
- publicpartialclassWholeCustomersSetResult
- {
- privatestring_CustomerID;
- privatestring_CompanyName;
- privatestring_ContactName;
- privatestring_ContactTitle;
- privatestring_Address;
- privatestring_City;
- privatestring_Region;
- privatestring_PostalCode;
- privatestring_Country;
- privatestring_Phone;
- privatestring_Fax;
- publicWholeCustomersSetResult()
- {
- }
- [Column(Storage="_CustomerID",DbType="NChar(5)")]
- publicstringCustomerID
- {
- get{returnthis._CustomerID;}
- set
- {
- if((this._CustomerID!=value))
- this._CustomerID=value;
- }
- }
- [Column(Storage="_CompanyName",DbType="NVarChar(40)")]
- publicstringCompanyName
- {
- get{returnthis._CompanyName;}
- set
- {
- if((this._CompanyName!=value))
- this._CompanyName=value;
- }
- }
- [Column(Storage="_ContactName",DbType="NVarChar(30)")]
- publicstringContactName
- {
- get{returnthis._ContactName;}
- set
- {
- if((this._ContactName!=value))
- this._ContactName=value;
- }
- }
- [Column(Storage="_ContactTitle",DbType="NVarChar(30)")]
- publicstringContactTitle
- {
- get{returnthis._ContactTitle;}
- set
- {
- if((this._ContactTitle!=value))
- this._ContactTitle=value;
- }
- }
- [Column(Storage="_Address",DbType="NVarChar(60)")]
- publicstringAddress
- {
- get{returnthis._Address;}
- set
- {
- if((this._Address!=value))
- this._Address=value;
- }
- }
- [Column(Storage="_City",DbType="NVarChar(15)")]
- publicstringCity
- {
- get{returnthis._City;}
- set
- {
- if((this._City!=value))
- this._City=value;
- }
- }
- [Column(Storage="_Region",DbType="NVarChar(15)")]
- publicstringRegion
- {
- get{returnthis._Region;}
- set
- {
- if((this._Region!=value))
- this._Region=value;
- }
- }
- [Column(Storage="_PostalCode",DbType="NVarChar(10)")]
- publicstringPostalCode
- {
- get{returnthis._PostalCode;}
- set
- {
- if((this._PostalCode!=value))
- this._PostalCode=value;
- }
- }
- [Column(Storage="_Country",DbType="NVarChar(15)")]
- publicstringCountry
- {
- get{returnthis._Country;}
- set
- {
- if((this._Country!=value))
- this._Country=value;
- }
- }
- [Column(Storage="_Phone",DbType="NVarChar(24)")]
- publicstringPhone
- {
- get{returnthis._Phone;}
- set
- {
- if((this._Phone!=value))
- this._Phone=value;
- }
- }
- [Column(Storage="_Fax",DbType="NVarChar(24)")]
- publicstringFax
- {
- get{returnthis._Fax;}
- set
- {
- if((this._Fax!=value))
- this._Fax=value;
- }
- }
- }
PartialCustomersSetResult类 如下:(点击打开)
代码在这里打开
0
- [Function(Name="dbo.[CustomersCountByRegion]")]
- publicintCustomers_Count_By_Region([Parameter
- (DbType="NVarChar(15)")]stringparam1)
- {
- IExecuteResultresult=this.ExecuteMethodCall(this,
- ((MethodInfo)(MethodInfo.GetCurrentMethod())),param1);
- return((int)(result.ReturnValue));
知优网 » 深度分析LINQ to SQL存储进程(LINQ to SQL)