在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有所帮助。