Linq To Sql进阶系列（五）Store Procedure篇

最新推荐文章于 2024-08-01 15:33:13 发布

泡面之夏

最新推荐文章于 2024-08-01 15:33:13 发布

阅读量174

点赞数

分类专栏： SQL DotNet 文章标签： linq sql function object 测试存储

DotNet 同时被 2 个专栏收录

22 篇文章 0 订阅

订阅专栏

SQL

13 篇文章 0 订阅

订阅专栏

Store Procedure，存储过程。也是被别人写过的东西。我习惯性先看别人都写了点啥，然后才开始想看看自己还要写点啥。那就先谈谈它与udf的区别吧。

在Linq To Sql进阶系列（四）User Define Function篇中，我们提到了两者的差别。比如Store Procedure支持多个rowset的，而udf不行。他们还有一些其他的差别。Store Procedure只能返回整型，而udf可以是其他类型，比如char等，除个别类型外，比如imager类型，是不可以做为udf的返回类型的。Store Procedure支持Out Parameter而udf没有。

1， SingleResultSet
我们先来看这个sprocs.

CREATE PROCEDURE [ dbo ] . [ Customers By City ]

-- Add the parameters for the stored procedure here

( @param1 NVARCHAR ( 20 ))

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

其生成的code如下。

[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));

}

这里Customers_By_CityResult是这个sprocs的影射类。但你可以在OR Designer里调整。如图，

选中该函数后，右击属性。就可以使用其他影射类。但是Linq会对返回的rowset做检查，如果发现返回结果和影射不匹配它会报错。而且一旦更改了，当你需要改回去的时候，你只能在Designer中删掉此sprocs，然后重新拖过来。
调用它很简单，就当作一个函数，但是，这里和普通的linq语句不一样的地方是，它不是延迟加载的。

DataClasses1DataContext db = new DataClasses1DataContext();

db.Log = Console.Out;

var q = db.Customers_By_City( " London " );

正因它不是延迟加载的，所以，linq可以对他进行简单的内联操作，比如

DataClasses1DataContext db = new DataClasses1DataContext();

db.Log = Console.Out;

var q = from c in db.Customers_By_City( " London " )

orderby c.City

select c;

注意的时，这里是Linq To Object而不是Linq To Sql。

2， MultipleResultSets
看下面的例子

CREATE PROCEDURE [ dbo ] . [ Get Customer And Orders ] ( @CustomerID nchar ( 5 ))

-- Add the parameters for the stored procedure here

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

使用OR designer对其影射，其dbml为

< Function Name ="dbo.[Get Customer And Orders]" Method ="Get_Customer_And_Orders" >

< Parameter Name ="CustomerID" Parameter ="customerID" Type ="System.String" DbType ="NChar(5)" />

< ElementType Name ="Get_Customer_And_OrdersResult" >

< Column Name ="CustomerID" Type ="System.String" DbType ="NChar(5) NOT NULL" CanBeNull ="false" />

< Column Name ="CompanyName" Type ="System.String" DbType ="NVarChar(40) NOT NULL" CanBeNull ="false" />

< Column Name ="ContactName" Type ="System.String" DbType ="NVarChar(30)" CanBeNull ="true" />

< Column Name ="ContactTitle" Type ="System.String" DbType ="NVarChar(30)" CanBeNull ="true" />

< Column Name ="Address" Type ="System.String" DbType ="NVarChar(60)" CanBeNull ="true" />

< Column Name ="City" Type ="System.String" DbType ="NVarChar(15)" CanBeNull ="true" />

< Column Name ="Region" Type ="System.String" DbType ="NVarChar(15)" CanBeNull ="true" />

< Column Name ="PostalCode" Type ="System.String" DbType ="NVarChar(10)" CanBeNull ="true" />

< Column Name ="Country" Type ="System.String" DbType ="NVarChar(15)" CanBeNull ="true" />

< Column Name ="Phone" Type ="System.String" DbType ="NVarChar(24)" CanBeNull ="true" />

< Column Name ="Fax" Type ="System.String" DbType ="NVarChar(24)" CanBeNull ="true" />

</ ElementType >

</ Function >

用sqlmetal对它做影射，生成dbml为

< Function Name ="dbo.Get Customer And Orders" Method ="GetCustomerAndOrders" >

< Parameter Name ="CustomerID" Parameter ="customerID" Type ="System.String" DbType ="NChar(5)" />

< ElementType Name ="GetCustomerAndOrdersResult1" >

< Column Name ="CustomerID" Type ="System.String" DbType ="NChar(5)" CanBeNull ="true" />

< Column Name ="CompanyName" Type ="System.String" DbType ="NVarChar(40)" CanBeNull ="true" />