ASP.NET 2.0中的操作数据七十四:用托管代码创建存储过程和用户定义函数(下一部分)

步骤8:从表示层调用托管存储过程。

在扩展数据访问层和业务逻辑层以支持调用两个托管存储过程(GetDiscontinuedProducts和GetProductsWithPriceLessThan)之后，我们可以在ASP.NET页面中显示这些存储过程的结果。

在AdvancedDAL文件夹中打开ManagedFunctionsAndSprocs.aspx页，将GrIDView控件从工具箱拖到设计器中，并将其id设置为DiscontinuedProducts。在其智能标记中绑定一个名为中断产品数据源的ObjectDataSource控件，并将其设置为调用ProductsBLLWithSprocs类类的get中断产品方法。

图20:调用ProductsBLLWithSprocs类类。

图21:在SELECT标记中调用GetDiscontinuedProducts方法。

因为我们只需要显示产品信息，所以在“更新”、“插入”和“删除”选项卡中选择“(无)”。单击“完成”完成配置。完成后，Visual Studio将自动为ProductsDataTable表的列添加BoundField列或CheckBoxField列。除“产品名称”和“已停产”之外的所有列都将被删除。这样，GridView和ObjectDataSource的声明代码看起来类似于以下内容：

asp3360 GridView ID=' continuated products ' runat=' server ' AutoGenerateColumns=' False ' DataKeyNames=' ProductID ' DataSourceID=' continuated productsdata so source ' Columns asp: boundfield data field=' product name ' header text=' product name ' sort expression=' product name '/asp3360 checkbox field data field=' continuated ' header text=' continuated ' sort expression=' continuated '/Columns/asp:0登录时，ObjectDataSource控件将调用ProductsBLLWithSprocs类类的GetDiscontinuedProducts方法。正如我们在第7步中看到的，该方法还调用了DAL层的productdatatableclass类的getcontinuous products方法，该方法还调用了存储过程getcontinuous products。存储过程返回那些处于“停产”状态的产品。存储过程返回的结果被填充到DAL层的ProductsDataTable中，然后返回到BLL，再返回到表示层，并绑定到GridView控件进行显示。

图22:“列出了停产的产品。

我们可以继续加强实践，比如在页面上放置一个TextBox控件和一个GridView控件。在文本框控件中输入一个数字，GridView控件调用ProductsBLLWithSprocs类的GetProductsWithPriceLessThan来显示价格低于该数字的产品。

第九步：创建并调用T-SQL UDF。

用户定义函数——，缩写为UDF，是一种数据库对象，类似于编程语言中的函数定义。类似于C#中的函数，UDF可以包含一系列输入参数并返回特定类型的值。UDF可以返回标量数据，如字符串、整数等。要么返回表格数据。让我们从标量数据类型开始，快速检查这两种类型的UDF。

下面的UDF用来计算特定产品的总价。它有三个输入参数：——单位价格、单位库存、停产。它返回货币类型的值。它通过单位价格乘以库存单位获得总价。如果处于停产状态，总价将减半。

CREATE FUNCTION UDF _ ComputeInventoryValue(@单价货币，@UnitsInStock smallint，@不连续位)作为begin DECLARE @ Value返回货币十进制SET @Value=ISNULL(@UnitPrice，0) * ISNULL(@UnitsInStock，0)IF @ uncontined=1 SET @ Value=@ Value * 0.5 RETURN @ Value end将该南非民主统一战线(联合民主阵线)添加到数据库后，我们打开管理工作室，打开可编程序性文件夹，再打开功能文件夹，再打开标量值函数文件夹，就可以看到该UDF .我们可以在一个挑选查询里这样来使用：

选择产品标识、产品名称、dbo.udf_ComputeInventoryValue(单价、单位库存、停产)作为库存价值来自产品按库存排序值表名我已经将该udf_ComputeInventoryValue用户函数添加到了北风数据库。图23就是在管理工作室里调用上述挑选查询得到的输出结果。

图23:列出了每个产品的总价

南非民主统一战线(联合民主阵线)也可以返回表列数据。比如，我们可以创建一个南非民主统一战线(联合民主阵线)返回属于某个种类的所有产品：

创建函数dbo。UDF _ getproductbycategoryID(@ categoryid int)RETURNS TABLEASRETURN(选择产品id、产品名称、供应商id、CategoryID、数量类型rUnit、单价、单位库存、单位订单、重组级别、续自产品，其中CategoryID=@ CategoryID)该udf _ GetProductsByCategoryID用户函数接受一个@CategoryID输入参数，返回挑选查询的结果。一旦创建之后，该南非民主统一战线(联合民主阵线)就可以在挑选查询的FROM(或JOIN)之句里引用。下面的示例返回饮料类所属的每个产品的产品标识，产品名称，类别标识值：

选择来自dbo的ProductID、ProductName、categoryid。UDF _ GetProductByCategoryid(1)我已经将该udf _ GetProductsByCategoryID用户函数添加到北风数据库。图24显示的是在管理工作室运行上述挑选查询的结果。返回表列数据的南非民主统一战线(联合民主阵线)放在表值函数文件夹里。

图24:饮料类产品的产品标识，产品名称，类别标识都列出来了

注意：关于创建和使用南非民主统一战线(联合民主阵线)的更多详情，请参阅文章《Intro to User-Defined Functions》 和《dvantages and Drawbacks of User-Defined Functions》

第十步：创建一个管理的南非民主统一战线(United Democratic Front)

上面示例里创建的udf_ComputeInventoryValue和udf _ GetProductsByCategoryID用户函数都是数据库备份与还原数据库对象SQL Server 2005同样支持管理UDF，我们可以将其添加到ManagedDatabaseConstructs工程，就像在第三和第五步做的那样。在这一步，我们将用托管代码执行udf_ComputeInventoryValue用户函数。

在解决资源管理器里右键单击，选择"添加新项目",在对话框里选用户定义的函数模板，将新南非民主统一战线(联合民主阵线)文件命名为UDF _ ComputeInventoryValue _ managed。cs .

图25:向ManagedDatabaseConstructs工程添加一个管理的南非民主统一战线(United Democratic Front)

该用户定义的函数模板将创建一个名为用户定义的功能的部分类别类，同时还有一个方法，该方法的名字与类文件的名字一样（就本例而言，为UDF _ ComputeInventoryValue _ Managed)。该方法有一个sqlfunfunction特性，这就标明了该方法是一个管理UDF .

使用系统；使用系统。数据；使用系统数据。程序使用系统数据。SqlTypes使用微软。服务器；公共分部类用户定义的功能{[微软SqlServer。服务器公共静态SqL字符串UDF _ ComputeInventoryValue _ Managed(){//将代码放在这里返回新的SqL字符串(' Hello ')；}}该udf_ComputeInventoryValue方法目前返回一个语句对象，且不接受任何的输入参数。我们将对其进行更新以包含3个参数——单价，单位库存，和停产，并返回一个方法对象。该方法用到逻辑与上面的数据库备份与还原类型的udf_ComputeInventoryValue用户函数的一样。

【微软。公共静态SqlMoney UDF _ ComputeInventoryValue _ Managed(SqlMoney UnitPrice，SqlInt16 UnitsInStock，SqlBoolean已停产){ SqlMoney inventory value=0；if(！单价。是空的！UnitsInStock。IsNull) {清单值=单价*单价库存；if(已停产==true)inventory value=inventory value * new SqlMoney(0.5)；}返回清单值；}我们注意到，UDF方法的输入参数是它们对应的SQL类型：UnitPrice为SqlMoney，UnitsInStock为SqlInt16，undiscounted为SqlBoolean。这些类型反映了Products表中定义的这些列的类型：UnitPrice是货币，UnitsInStock是smallint，undiscounted是bit。

首先，代码创建一个名为inventoryValue的SqlMoney类型的实例，并赋值为0。因为Products表允许UnitsInPrice和UnitsInStock列的值为NULL，所以我们首先通过SqlMoney对象的IsNull属性检查这两列是否包含NULL值。如果这两列的值不为空，则库存值的值通过单位价格乘以单位库存获得，如果停产为真，则库存值的值减半。

注意：由于SqlMoney对象只允许两个SqlMoney实例相乘，它不允许一个SqlMoney实例乘以一个文字浮点，所以我们使用一个值为0.5的SqlMoney实例乘以代码中的清单值。

步骤11:配置托管UDF。

现在我们已经创建了一个托管UDF，我们将把它配置到北风数据库。正如我们在第四步中看到的，在解决方案资源管理器中，右键单击项目名称并选择“部署”。

完成后，返回到SQL Server管理工作室并刷新标量值函数文件夹。您将看到两个实体：步骤9中创建的udf。dbo . udf computeinventoryvalue _ managed ——我们在步骤10中刚刚创建的托管UDF。

测试托管UDF，并在管理工作室中执行以下查询：

选择ProductID，ProductName，dbo . udf_ComputeInventoryValue _ Managed(UnitPrice，UnitsInStock，Discontinued)作为按inventoryvaluedesc排序的InventoryValue产品该命令使用UDF ComputeInventoryValue _ Managed函数，而不是UDF _ ComputeInventoryValue函数，但输出结果是相同的，因此可以查看图23中的截图。

步骤12:调试托管数据库对象。

在第72章中，我们讨论了通过Visual Studio调试SQL Server的三种模式：直接数据库调试、应用程序调试和通过SQL Server Project调试。直接数据库模式无法调试托管数据库对象。但是，它可以从客户端程序和SQL Server项目中进行调试。为了使调试正常工作，SQL Server 2005数据库要求必须允许SQL/CLR调试。请记住，当我们第一次创建ManagedDatabaseConstructs项目时，Visual Studio会询问我们是否要激活SQL/CLR调试(参见步骤2中的图6)。我们可以在服务器资源管理器窗口中右键单击数据库来修改配置。

图26:确保数据库激活了SQL/CLR调试。

假设我们想要调试getproductwithspricelshanstored过程。我们必须首先在getproductwithspricelshanmethod的代码中设置断点。

图27:在GetProductsWithPriceLessThan方法中设置断点。

首先，我们检查从SQL Server项目调试托管数据库对象。

我们的解决方案资源管理器包含两个项目，——托管数据库构建SQL Server项目和我们的网站。为了从SQL server项目进行调试，在调试时，我们需要指导Visual Studio打开托管数据库构造SQL Server项目。右键单击解决方案资源管理器中的托管数据库构造项目，并选择“设置为启动项目”项目。

当从调试器打开ManagedDatabaseConstructs项目时，它会执行Test.sql文件的SQL语句，该文件位于Test Scripts文件夹中。例如，要测试getproductwithtpricelssthan存储过程，请用以下语句替换Test.sql文件的内容，该语句调用getproductwithtpricelssthan存储过程，输入参数@CategoryID为14.95:

将上述脚本键入Test.sql文件后，单击调试菜单中的“开始调试”项，或按F5或工具栏上的绿色图标开始调试。这将在资源管理器中构建项目。托管数据库对象被配置为北风数据库，然后执行Test.sql脚本。这时会遇到一个断点，所以我们可以输入GetProductsWithPriceLessThan方法，检查输入参数的值，等等。

图28:在GetProductsWithPriceLessThan方法中遇到断点。

为了从客户端程序调试SQL数据库对象，必须将数据库配置为支持应用程序调试。在服务器资源管理器中，右键单击数据库，并确保选择了“应用程序调试”。此外，我们需要将ASP.NET应用程序与SQL调试器结合起来，并关闭连接池。这些步骤在第74章的步骤2中有详细讨论。

配置完ASP.NET应用程序和数据库后，将ASP.NET网站设置为启动方案。如果您登录到一个调用设置了断点的托管对象的页面，程序会遇到断点并切换到调试器，您可以在那里输入代码，如图28所示。

步骤13:手动编译和配置托管数据库对象。

使用SQL Server项目，我们可以轻松地创建、编译和配置托管数据库对象。不幸的是，只有Visual Studio的专业版和团队系统版可以使用SQL Server项目。如果您正在使用Visual Web Developer或Standard Edition，并计划使用托管数据库对象，则需要手动创建和配置它们。这将包括四个步骤：

1.创建一个文件来存储托管数据库对象的源代码。

2.编译对象。

3.将编译后的文件注册到SQL Server 2005数据库。

4.在SQL Server中创建一个数据库对象，并指向编译文件中相应的方法。

为了便于演示，我们将创建一个新的托管存储过程，并返回那些单价值高于指定值的产品。在您的计算机上创建一个名为getproductwithspricegretterthan . cs的新文件，并键入以下代码(您可以使用Visual Studio、记事本或任何文本编辑器):

使用系统；使用系统。数据；使用系统。Data . SqlClient使用系统。Data . SqlTypes使用微软。服务器；公共分部类StoredProcedures{ [Microsoft。SQL server . server . sqlprocedure]public static void getproductwithspricegreaterthan(SqlMoney price){//Create命令SqlCommand my command=new SqlCommand()；我的命令。CommandText=@ '选择产品标识、产品名称、供应商标识、类别标识、数量类型标识、单价、单位库存、单位订单、重新订购级别、停产自产品，其中单价@最小价格'；我的命令。参数. AddWithValue('@MinPrice '，price)；//执行命令并将结果发送回SqlContext。管道. executandsend(my command)；}}这些代码与我们在第5步中创建的GetProductSwithPriceressThan方法的代码非常相似。唯一的区别是：不同的方法名称、不同的WHERE语句以及查询中使用的不同参数名称。返回GetProductSwithPriceressThan方法。WHERE语句是“WHERE UnitPrice @MaxPrice”，而在这里，getproductswithpricegraterthan方法中的代码是“WHERE UnitPrice @MinPrice”。

现在我们需要编译这个类。导航到您在命令行中存储文件getproductwithspricegreaterthan . cs的根目录，并使用C#编译器(csc.exe)对其进行编译：

如果包含csc.exe的文件夹不在系统路径中，您必须完全引用它的路径，% Windows%/Microsoft。NET/Framework/Version/，例如：

c :/WINDOWs/微软。NET/Framework/v 2 . 0 . 50727/CSC . exe/t : library/out : manual createdbobobjects . dll getproductwithspricegreaterthan . cs

图29:编译getproductwithpricegreaterthan . cs文件。

/t标记指定将C#类编译成DLL(而不是可执行文件)。/out标记指定编译文件的名称。

注意：除了编译getproductwithepricegreaterthan之外。有了csclass类的命令行，我们还可以使用Visual C# Express Edition或者创建一个单独的类库项目。在Visual Studio标准版中。Ren Jacob Lauritsen为我们提供了一个Visual C# Express Edition项目，其中包含GetProductSwithPriceGreater存储过程，以及UDF在步骤3、5和10中创建的两个托管存储过程。此外，它还包含添加相应数据库对象所需的T-SQL命令。

在编译完代码之后，我们需要在SQL Server 2005数据库中注册它。我们可以使用T-SQL，使用命令CREATE ASSEMBLY，或者使用SQL Server管理工作室。我们来看看使用管理工作室的情况。

在Management Studio中，展开Northwind数据库中的“可编程性”文件夹，其中有一个“程序集”文件夹。右键单击文件夹并选择“新建程序集”。这将打开“新建程序集”对话框(见图30)，单击“浏览”按钮，选择我们刚刚编译的ManuallyCreatedDBObjects.dll文件，然后单击“确定”完成添加。您应该能够在对象资源管理器中看到ManuallyCreatedDBObjects.dll文件。

图30:向数据库添加ManuallyCreatedDBObjects.dll。

图31:ManuallyCreatedDBObjects.dll显示在对象浏览器中。

完成后，我们将把一个存储过程与编译文件中的getproductswithpricegraterthan方法相关联。为此，打开一个新的查询窗口并执行以下脚本：

创建过程[dbo]。[getproductwithspricegreaterthan](@ price[numeric](18，0))，执行方式为调用外部名称[manuallycreatedbobobjects]。[StoredProcedures]。[getProductsWithPriceGreaterThan]GO将在Northwind数据库中创建一个名为etproductwithtpricegreaterthan的新存储过程，并将其与getproductswithpricegeatherthan方法(属于编译文件ManuallyCreatedDBObjects)相关联。

执行脚本后，刷新对象资源管理器中的存储过程文件夹。您将看到一个新的存储过程，—— getproducts，其价格大于，旁边有一个锁定图标。测试存储过程，并在查询窗口中键入和执行以下脚本：

执行GetProductWithPriceGreaterthan 24.95

如图32所示，上面的命令显示了那些价格高于24.95的产品。

图32:对象资源管理器中的显示列表。

结论：

Microsoft SQL Server 2005集成了公共语言运行时(CLR)，它允许托管代码创建数据库对象。过去，我们只能使用T-SQL来创建数据库对象，但现在我们可以使用。NET编程语言。例如C#。在本文中，我们创建了两个托管存储过程和一个托管用户定义函数。

Visual Studio的SQL Server项目类型可以轻松创建、编译和配置托管数据库对象，并且还支持各种调试。不幸的是，SQL Server项目类型仅在Visual Studio的专业版和团队系统版中可用。对于Visual Web Developer或Standard Edition的用户，这些步骤应该手动完成，就像我们在步骤13中看到的那样。

编程快乐！

作者简介

Scott Mitchell，本系列教程的作者，也是关于ASP/ASP的六本书的作者。NET，是4GuysFromRolla.com的创始人，自1998年以来一直使用微软的网络技术。你可以点击查看所有教程《[翻译]Scott Mitchell 的ASP.NET 2.0数据教程》，希望能帮助你学习ASP.NET。