在ASP.NET 2.0中操作数据之七十四：用Managed Code创建存储过程和用户自定义函数(下部分)
第八步：从表现层调用managed stored procedures
当对数据访问层和业务逻辑层进行扩充以支持调用getdiscontinuedproducts 和 getproductswithpricelessthan这2种managed stored procedures后，我们可以在一个asp.net页面里展示这些存储过程的结果了.
打开advanceddal文件夹里的managedfunctionsandsprocs.aspx页面，从工具箱拖一个gridview控件到设计器，设其id为discontinuedproducts，在其智能标签里绑定到一个名为discontinuedproductsdatasource的objectdatasource控件，设置其调用productsbllwithsprocs class类的getdiscontinuedproducts方法.
图20：调用productsbllwithsprocs class类
图21：在select标签里调用getdiscontinuedproducts方法
由于我们只需要展示产品信息，在update, insert,和delete标签里选 “(none)”，再点finish完成配置.完成后visual studio会为productsdatatable表的列自动的添加boundfield列 或 checkboxfield列. 将除productname和discontinued以外的列全部删除.这样你的gridview 和 objectdatasource的声明代码看起来和下面的差不多：
<asp:gridview id="discontinuedproducts" runat="server"
autogeneratecolumns="false" datakeynames="productid"
datasourceid="discontinuedproductsdatasource">
<columns>
<asp:boundfield datafield="productname" headertext="productname"
sortexpression="productname" />
<asp:checkboxfield datafield="discontinued" headertext="discontinued"
sortexpression="discontinued" />
</columns>
</asp:gridview>
<asp:objectdatasource id="discontinuedproductsdatasource" runat="server" oldvaluesparameterformatstring="original_{0}" selectmethod="getdiscontinuedproducts" typename="productsbllwithsprocs"> </asp:objectdatasource>
花点时间在浏览器里登录该页面。当登录时，objectdatasource控件将调用productsbllwithsprocs class类的 getdiscontinuedproducts方法.就像我们在第七步看到的那样，该方法又调用dal层的productsdatatable class类的getdiscontinuedproducts方法，该方法又调用存储过程getdiscontinuedproducts.该存储过程返回那些处于“discontinued”状态的产品. 存储过程返回的结果填充到dal层的一个productsdatatable，进而返回给bll,再返回给表现层并绑定到一个gridview控件展现出来.
图22：“discontinued”的产品被列出来了
我们可以继续加强练习，比如在页面上再放置一个textbox控件和一个gridview控件。在textbox控件里输入一个数，而gridview控件调用productsbllwithsprocs class类的getproductswithpricelessthan方法将价格低于该数的产品展示出来.
第九步：创建并调用t-sql udfs
用户自定义函数——简称udf,是一种数据库对象，与编程语言里的函数定义很相仿.与c#里面的函数类似，udf可以包含一系列的输入参数并返回一个特定类型的值.一个udf要么返回标量数据(scalar data)——比如一个string, 一个integer等等；要么返回一个表列数据(tabular data).让我们先快速的考察一下这2种类型的udf,先从标量数据类型开始.
下面的udf用于计算某个特定产品的总价.其有3个输入参数——unitprice, unitsinstock,discontinued.其返回一个money类型的值.它通过以unitprice乘以unitsinstock来得到总价，如是处于“discontinued”状态，则总价减半.
create function udf_computeinventoryvalue ( @unitprice money, @unitsinstock smallint, @discontinued bit ) returns money as begin declare @value decimal set @value = isnull(@unitprice, 0) * isnull(@unitsinstock, 0) if @discontinued = 1 set @value = @value * 0.5 return @value end
将该udf添加到数据库后，我们打开management studio，打开programmability文件夹，再打开functions文件夹，再打开scalar-value functions文件夹，就可以看到该udf.我们可以在一个select查询里这样来使用：
select productid, productname, dbo.udf_computeinventoryvalue (unitprice, unitsinstock, discontinued) as inventoryvalue from products order by inventoryvalue desc
我已经将该udf_computeinventoryvalue用户函数添加到了northwind数据库。图23就是在management studio里调用上述select查询得到的输出结果.
图23：列出了每个产品的总价
udf也可以返回表列数据.比如，我们可以创建一个udf返回属于某个category的所有产品：
create function dbo.udf_getproductsbycategoryid ( @categoryid int ) returns table as return ( select productid, productname, supplierid, categoryid, quantityperunit, unitprice, unitsinstock, unitsonorder, reorderlevel, discontinued from products where categoryid = @categoryid )
该udf_getproductsbycategoryid用户函数接受一个@categoryid输入参数，返回select查询的结果.一旦创建之后，该udf就可以在select查询的from (或 join)之句里引用.下面的示例返回饮料类所属的每个产品的productid, productname,categoryid值：
select productid, productname, categoryid from dbo.udf_getproductsbycategoryid(1)
我已经将该udf_getproductsbycategoryid用户函数添加到northwind数据库。图24显示的是在management studio运行上述select查询的结果.返回表列数据的udf放在table-value functions文件夹里.
图24：饮料类产品的productid, productname,categoryid都列出来了
注意：关于创建和使用udf的更多详情，请参阅文章《intro to user-defined functions》和《dvantages and drawbacks of user-defined functions》
第十步：创建一个managed udf
上面示例里创建的udf_computeinventoryvalue和 udf_getproductsbycategoryid用户函数都是t-sql数据库对象.sql server 2005同样支持managed udf,我们可以将其添加到manageddatabaseconstructs工程，就像在第三和第五步做的那样.在这一步，我们将用managed code执行udf_computeinventoryvalue用户函数.
在解决资源管理器里右键单击，选择“add a new item”，在对话框里选user-defined function模板，将新udf文件命名为udf_computeinventoryvalue_managed.cs.
图25：向manageddatabaseconstructs工程添加一个managed udf
该user-defined function模板将创建一个名为userdefinedfunctions的partial class类，同时还有一个方法，该方法的名字与类文件的名字一样(就本例而言，为udf_computeinventoryvalue_managed)。该方法有一个sqlfunction特性, 这就标明了该方法是一个managed udf.
using system;
using system.data;
using system.data.sqlclient;
using system.data.sqltypes;
using microsoft.sqlserver.server;
public partial class userdefinedfunctions
{
[microsoft.sqlserver.server.sqlfunction]
public static sqlstring udf_computeinventoryvalue_managed()
{
// put your code here
return new sqlstring("hello");
}
}
该udf_computeinventoryvalue方法目前返回一个sqlstring对象，且不接受任何的输入参数.我们将对其进行更新以包含3个参数——unitprice, unitsinstock,和discontinued，并返回一个sqlmoney对象.该方法用到逻辑与上面的t-sql类型的udf_computeinventoryvalue用户函数的一样.
[microsoft.sqlserver.server.sqlfunction]
public static sqlmoney udf_computeinventoryvalue_managed
(sqlmoney unitprice, sqlint16 unitsinstock, sqlboolean discontinued)
{
sqlmoney inventoryvalue = 0;
if (!unitprice.isnull && !unitsinstock.isnull)
{
inventoryvalue = unitprice * unitsinstock;
if (discontinued == true)
inventoryvalue = inventoryvalue * new sqlmoney(0.5);
}
return inventoryvalue；
}
我们注意到udf方法的输入参数就是其对应的sql类型：unitprice的类型为sqlmoney、unitsinstock的类型为sqlint16、discontinued的类型为sqlboolean.这些类型反映了这些列在products表里定义的类型：unitprice列的类型为money、unitsinstock列的类型为smallint、discontinued列的类型为bit.
代码首先创建了一个sqlmoney类型的名为inventoryvalue的实例，并赋值为0.由于products表允许unitsinprice 和 unitsinstock列的值为null，因此我们首先通过sqlmoney对象的isnull属性来检查这2列是否包null值。如果这2列的值都不为null，那么unitprice乘以unitsinstock就得到了inventoryvalue的值，另外如果discontinued为true的话，inventoryvalue的值减半.
注意：由于sqlmoney对象只允许2个sqlmoney实例相乘，它不允许一个sqlmoney实例与一浮点数(literal floating-point)相乘，所以在代码里我们用一个值为0.5的sqlmoney实例与inventoryvalue相乘.
第11步骤：配置managed udf
现在我们已经创建了一个managed udf，我们将把它配置给northwind数据库.就像我们在第四步看到的那样，在解决资源管理器里，在工程名上右键单击选“deploy”.
完成后，返回到sql server management studio，刷新scalar-valued functions文件夹.你就会看到2个实体：
.dbo.udf_computeinventoryvalue——在第九步创建的t-sql udf
.dbo.udf computeinventoryvalue_managed——我们在第10步刚刚创建的managed udf
对该managed udf进行测试，在management studio里执行如下的查询：
select productid, productname, dbo.udf_computeinventoryvalue_managed( unitprice, unitsinstock, discontinued ) as inventoryvalue from products order by inventoryvalue desc
该命令使用的是udf computeinventoryvalue_managed函数而不是udf_computeinventoryvalue函数，但是输出结果都一样，可以查看图23的截屏.
第12步：调试managed database objects
在第72章我们探讨了通过visual studio调试sql server的3种模式：直接数据库调试、应用程序调试、通过sql server project调试.managed database objects不能用直接数据库模式调试，不过可以从一个客户端程序和sql server project来调试.为了使调试正常工作，sql server 2005 数据库要求必须允许sql/clr调试.记得当我们最初创建manageddatabaseconstructs工程时，visual studio询问我们是否激活sql/clr调试(见第2步的图6).我们可以在server explorer窗口里在数据库上右键单击，以对该配置进行修改.
图26：确保数据库激活sql/clr调试
设想我们想调试getproductswithpricelessthan存储过程.我们首先要在getproductswithpricelessthan方法的代码里设置断点.
图27：在getproductswithpricelessthan方法里设置断点
首先我们考察从sql server project里调试managed database objects.
由于我们的解决资源管理器里包含2个工程——manageddatabaseconstructs sql server project以及我们的website.为了从sql server project进行调试，当调试时我们需要引导visual studio开启manageddatabaseconstructs sql server project.在解决资源管理器里的manageddatabaseconstructs project上点击右键，选“set as startup project”项.
当从调试器打开manageddatabaseconstructs project时，它执行test.sql文件的sql statements，该文件位于test scripts文件夹.比如，要测试getproductswithpricelessthan存储过程的话，将test.sql文件的内容替换为下面的statement,这些statement调用getproductswithpricelessthan存储过程，其输入参数@categoryid的值为14.95：
exec getproductswithpricelessthan 14.95
一旦将上面的脚本键入test.sql文件，点debug菜单里的“start debugging”项，或按f5或是工具栏上的绿色图标启动调试.这将在资源管理器里构建工程，将该managed database objects配置给northwind数据库，然后执行test.sql脚本.此时，将会遇到断点，我们可以进入getproductswithpricelessthan方法，检查输入参数的值等等.
图28：碰到getproductswithpricelessthan方法里的断点
为了从客户端程序调试一个sql database object，数据库务必要配置为支持应用程序调试.在服务器资源管理器里，在数据库上右键单击，确保选中“application debugging”项。另外，我们还要将asp.net应用程序与sql debugger结合起来，而且关闭连接池.这些步骤我们在第74章的第2步里详细探讨过了.
一旦你配置完asp.net应用程序和数据库.设置asp.net website为启动方案.如果你登录一个调用设置了断点的managed objects的页面的话，该程序就会碰到断点，并转换到调试器，在调试器里你可以进入代码，就像图28那样.
第13步：手动编译并配置managed database objects
使用sql server projects，我们可以很容易的创建、编译、配置managed database objects.不过遗憾的是，只有在visual studio的professional 和 team systems这2个版本才可以使用sql server projects.如果你使用的是visual web developer 或 standard edition版本，并且打算使用managed database objects的话,你需要手动创建并配置它们.这将包括4个步骤：
1.创建一个文件来存放managed database object的源代码
2.将object进行编译
3.将编译文件注册到sql server 2005数据库
4.在sql server里创建一个数据库对象，并指向编译文件里的相应的方法
为便于演示，我们将创建一个新的managed stored procedure，返回那些unitprice值高于指定值的产品.在你的电脑上创建一个名为getproductswithpricegreaterthan.cs 的新文件，并键入如下的代码(你可以使用visual studio, notepad或任何的文本编辑器来进行)：
using system;
using system.data;
using system.data.sqlclient;
using system.data.sqltypes;
using microsoft.sqlserver.server;
public partial class storedprocedures
{
[microsoft.sqlserver.server.sqlprocedure]
public static void getproductswithpricegreaterthan(sqlmoney price)
{
// create the command
sqlcommand mycommand = new sqlcommand();
mycommand.commandtext =
@"select productid, productname, supplierid, categoryid,
quantityperunit, unitprice, unitsinstock, unitsonorder,
reorderlevel, discontinued
from products
where unitprice > @minprice";
mycommand.parameters.addwithvalue("@minprice", price);
// execute the command and send back the results
sqlcontext.pipe.executeandsend(mycommand);
}
}
这些代码与我们在第五步创建的getproductswithpricelessthan方法的代码很相似.唯一的不同在于：方法名不同、where字句不同、以及查询使用的参数名不同.返回到getproductswithpricelessthan方法，其where字句为“where unitprice < @maxprice”. 而在这里，getproductswithpricegreaterthan方法里，代码为“where unitprice > @minprice”.
我们现在需要将该类进行编译.在命令行里导航到你存放getproductswithpricegreaterthan.cs文件的根目录，并使用c#编译器(csc.exe)来进行编译：
csc.exe /t:library /out:manuallycreateddbobjects.dll getproductswithpricegreaterthan.cs
如果包含csc.exe的文件夹没有位于系统路径，那你将必须完全引用其路径，%windows%/microsoft.net/framework/version/,比如：
c:/windows/microsoft.net/framework/v2.0.50727/csc.exe /t:library /out:manuallycreateddbobjects.dll getproductswithpricegreaterthan.cs
图29：对getproductswithpricegreaterthan.cs文件进行编译
其中，/t标记指定将c# class类编译为一个dll(而不是可执行文件)。而 /out标记指定了编译后文件的名称.
注意：除了用命令行来编译getproductswithpricegreaterthan.cs class类外，我们还可以使用visual c# express edition或在visual studio standard edition版里创建一个单独的class library project.s?ren jacob lauritsen为我们提供了一个visual c# express edition project，它包含了getproductswithpricegreaterthan存储过程，以及我们在第3、5和10步里创建的那2个managed stored procedures 和 udf.此外还包含了添加相应数据库对象必需的t-sql commands.
将代码编译完后，我们需要将其注册到sql server 2005数据库.可以通过t-sql，使用命令create assembly，或通过sql server management studio. 我们来看使用management studio的情况.
在management studio里，展开northwind数据库里的programmability文件夹，其内有一个assemblies文件夹。在该文件夹上右键单击，选“new assembly”.这将开启new assembly对话框(见图30)，点击browse按钮，选择我们刚刚编译的manuallycreateddbobjects.dll文件，再点ok完成添加.在object explorer里你应该可以看到manuallycreateddbobjects.dll文件.
图30：将manuallycreateddbobjects.dll添加到数据库
图31：manuallycreateddbobjects.dll展示在object explorer里
完成后，我们要将一个存储过程与编译文件里的getproductswithpricegreaterthan方法联系起来.为此，打开一个new query窗口，执行下面的脚本：
create procedure [dbo].[getproductswithpricegreaterthan] ( @price [numeric](18, 0) ) with execute as caller as external name [manuallycreateddbobjects].[storedprocedures].[getproductswithpricegreaterthan] go
这将在northwind数据库里创建一个名为etproductswithpricegreaterthan的新存储过程，并将其与getproductswithpricegreaterthan方法联系起来(该方法属于编译文件manuallycreateddbobjects)
执行完脚本后，在object explorer里刷新stored procedures文件夹。你将看到一个新的存储过程——getproductswithpricegreaterthan，在该存储过程旁边有一个锁的图标.测试该存储过程，在查询窗口键入并执行如下的脚本：
exec getproductswithpricegreaterthan 24.95
如图32所示，上述命令将那些价格高于24.95的产品展示出来.
图32：在对象资源管理器的显示的列表
结语：
microsoft sql server 2005整合了common language runtime (clr),它允许用managed code来创建数据库对象.在以前，要创建数据库对象，我们只能使用t-sql, 但是现在我们可以使用.net编程语言，比如c#来进行创建. 在本文我们创建了2个managed stored procedures以及一个managed user-defined function.
visual studio的sql server project类型可以很容易的创建、编译、配置managed database objects，此外还支持多种调试.遗憾的是，sql server project类型只有在visual studio的professional 和 team systems版本才有。对于visual web developer 或standard版的用户而言，要手工完成这些步骤，就想我们在第13步看到的那样.
祝编程快乐！
作者简介
本系列教程作者 scott mitchell，著有六本asp/asp.net方面的书，是4guysfromrolla.com的创始人，自1998年以来一直应用 微软web技术。大家可以点击查看全部教程《[翻译]scott mitchell 的asp.net 2.0数据教程》，希望对大家的学习asp.net有所帮助。