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

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为

</ElementType>

</Function>

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

</ElementType>

</ElementType>

</Function>

仔细比较他们的区别哦。“好像名字不一样呢”。晕倒。看主要的。第一个只有一个ElementType子项，而第二个有2个。这个地方其实可以说是OR designer的一个bug。要想修改这个bug，需要更改一个设计，而推动更改设计，比较麻烦。但并不是不能改。如果你认为这个真的很需要，而且对你很重要，你更喜欢用or designer的话，我建议你去下面的社区发帖子。
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=123&SiteID=1
要求更改此处的问题。因为，我已经无力推动他们修复该bug，ms更乐意听来自客户的声音，说不定你会成功的哦，还有奖励的哦。
这个sprocs准确的影射代码为

[Function(Name="dbo.Get Customer And Orders")]

[ResultType(typeof(GetCustomerAndOrdersResult1))]

[ResultType(typeof(GetCustomerAndOrdersResult2))]

public IMultipleResults GetCustomerAndOrders([Parameter(Name="CustomerID", DbType="NChar(5)")] string customerID)

{

IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), customerID);

return ((IMultipleResults)(result.ReturnValue));

}

对于MultipleResultSets的sprocs，大家更关注如何取其返回结果。其实很简单，按顺序，一个rowset，一个rowset的取。这个顺序和你sprocs里的顺序是一样的。
IMultipleResults result = db.GetCustomerAndOrders("SEVES");
IEnumerable<CustomerResultSet> customer = result.GetResult<CustomerResultSet>();
IEnumerable<OrdersResultSet> orders = result.GetResult<OrdersResultSet>();
如果，你很贪心，再加一行的话，
IEnumerable<CustomerResultSet> customer = result.GetResult<CustomerResultSet>();
报错咯，越界了。没有那么多，你问要也不给。

3，OutParameters
似乎没有什么好讲的，很简单，当作ref 的函数参数输出的。其也只是在生成的函数里加了这么一句
outParameter = ((System.Nullable<int>)(result.GetParameterValue(1))); 调用result.GetParameterValue方法，大家要记住这个哦。

4，Return Value
呀，把return value丢那里了。的确，Linq曾舍弃过return value.后来在qa的坚持下，dev们决定保留了它。但是，需要你自己去更改code，才能获得。我们可以从下面这个sprocs上获得灵感。

CREATE PROCEDURE [dbo].[CustOrderTotal]

@CustomerID nchar(5),

@TotalSales money OUTPUT

SELECT @TotalSales = SUM(OD.UNITPRICE*(1-OD.DISCOUNT) * OD.QUANTITY)

FROM ORDERS O, "ORDER DETAILS" OD

where O.CUSTOMERID = @CustomerID AND O.ORDERID = OD.ORDERID

其影射的code为

[Function(Name="dbo.CustOrderTotal")]

public int CustOrderTotal([Parameter(Name="CustomerID", DbType="NChar(5)")] string customerID, [Parameter(Name="TotalSales", DbType="Money")] ref System.Nullable<decimal> totalSales)

{

IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), customerID, totalSales);

totalSales = ((System.Nullable<decimal>)(result.GetParameterValue(1)));

return ((int)(result.ReturnValue));

}

因为该sprocs并没有rowset返回，其最后只剩返回值了。其是将result.RenturnValue强制转化为int型，以得到sprocs的整形返回值。那对于SingleResultSet和MultipleResultSets的，该如何获取它的返回值呢？那只有自己动手，修改code了。就是自己强制转换，而后通过out 参数输出。比如用第一个sprocs

[Function(Name = "dbo.[Customers By City]")]

public ISingleResult<Customers_By_CityResult1> Customers_By_City([Parameter(DbType = "NVarChar(20)")] string param1)

{

IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), param1);

return ((ISingleResult<Customers_By_CityResult1>)(result.ReturnValue));

}

public ISingleResult<Customers_By_CityResult1> Customers_By_City2(string para, out int returnValue)

{

ISingleResult<Customers_By_CityResult1> result = this.Customers_By_City(para);

returnValue = (int)result.ReturnValue;

return ((ISingleResult<Customers_By_CityResult1>)result);

}

测试一下

DataClasses1DataContext db = new DataClasses1DataContext();

db.Log = Console.Out;

int returnValue = -1;

var q = db.Customers_By_City2("London",out returnValue);

Console.WriteLine(returnValue);

也可以使用result.GetParameterValue方法获取返回值。只是linq会检查影射函数的参数个数。这个使用起来比较麻烦。可以这么使用，比如，有一个sprocs，有2个参数，其中有一个out的参数。这个out的参数，可以不做任何操作。在影射后，修改其code。这两个参数的位标是从0开始，依次递增。其code本来为outParameter = ((System.Nullable<int>)(result.GetParameterValue(1))); 将其索引修改位为2，就是return value了。再大了又该抛了。

ps：招聘，我就趁机打个广告吧．呵呵。赴微软测试工程师
1，计算机或软件，相关专业，本科或硕士以上。
2， 2年及2年以上工作经验。
3，英语，听说读写流利。
4，工作认真负责，学习能力强。
5，精通c#1.1及c#2.0版本语言，对c#3.0有了解。
6，精通算法。
7，有Ado.Net 和Asp.Net的经验。技术好。

有意者投简历到guoan@126.com 谢谢

相关文章：
ps：
C# 3.0 入门系列（一）
C# 3.0入门系列（二）
C# 3.0入门系列（三）
C# 3.0入门系列（四）-之Select操作
C#3.0入门系列（五）-之Where操作
C#3.0入门系列（六）-之OrderBy操作
C#3.0入门系列（七）--之OR工具介绍
C#3.0入门系列（八）-之GroupBy操作
C#3.0入门系列（九）-之GroupBy操作
C#3.0入门系列（十）-之Join操作
C#3.0入门系列（十一）-之In, Like操作
C#3.0入门系列（十二）-Lambda表达式中Lifting

Linq To Sql进阶系列（一）-从映射讲起
Linq To Sql进阶系列（二）M:M关系
Linq To Sql进阶系列（三）CUD和Log
Linq To Sql进阶系列（四）User Define Function篇
Linq To Sql进阶系列（五）Store Procedure篇

posted on 2007-08-16 20:38 Tom Song 阅读(4511) 评论(11) 编辑收藏网摘所属分类: C# 3.0 Linq To Sql

Feedback

#1楼 2007-08-16 22:41 XP主题[未注册用户]

学习了！！！
回复引用

#2楼 2007-08-17 08:41 万恶的验证码[未注册用户]

您的文章给了我很大帮助！谢谢！
回复引用

#3楼 2007-08-17 08:48 迭戈

可惜只工作一年。英语也不流利。不然也Try一下。楼主也没有说哪个地方的
回复引用查看

#4楼 2007-09-07 01:20 小菲[未注册用户]

请教一下：
Rg.Entities.sys_privileges sp = spdc.sys_privileges.First(c => c.resource_id == resourceid && c.operation_id == operationid);
结果错误提示：序列不包含任何元素
如何处理好呢？
回复引用

#5楼 2007-09-07 12:16 小菲[未注册用户]

我发现一个问题，不是算不算小bug:
ALTER PROCEDURE get_only_parents_by_menu(@id int)
AS
create table #tmp_sys_menus(id int);
while exists(select * from sys_menus where id=@id)
begin
insert into #tmp_sys_menus(id) select id from sys_menus where id=@id;
select @id=parent_id from sys_menus where id=@id;
end
select * from sys_menus where id in(select id from #tmp_sys_menus)

这样的存储过程：
Linq To Sql就不能自动影射到正确的类型(表),怀疑是影射最后一个from对应的表
回复引用

#6楼 [楼主] 2007-09-07 13:42 宋国安

@小菲
第一个问题，就是因为你的结果为空的，使用FirstOrDefault，此时为空会返回null值，而不是抛异常。

第二个问题，linq 还不支持临时表。
回复引用查看

#7楼 2007-09-07 15:20 小菲[未注册用户]

不支持临时表？那我的存储过程如何是好？
大概什么时间可以支持到啊
我把项目都转到linq to sql来做了，以前用IBatisNet的
回复引用

#8楼 [楼主] 2007-09-07 22:42 宋国安

@小菲
虽然不支持临时表，但是，你一样可以使用手工影射的办法,把这个sp影射成dbml,然后，再转化为code.
你可以模仿着本文的做法，自己修改dbml. Run-time 是不会检查你是不是临时表的．它只检查返回的结果是不是匹配。
回复引用查看

#9楼 2007-09-19 00:55 小菲[未注册用户]

另外ISingleResult<T>能进行分页吗？
ISingleResult<T> r = ...
r.Skip(1).Take(1)
这样？
回复引用

#10楼 [楼主] 2007-09-21 23:24 宋国安

@小菲
不可以．这里，dlinq无法把skip和take的语句塞入存储过程里，但是，你可以先把这个存储过程的结果ToList()然后，再.Skip(1).Take(1)
即：r.ToList().Skip(1).Take(1);

这里是Linq To Object 的概念。不再是Linq To Sql.
回复引用查看