75-用Managed Code创建存储过程和用户自定义函数

导言：
数据库，比如Microsoft‟s SQL Server 2005使用Transact-Structured Query Language (T-SQL)来插入、修改、检索数据.绝大多数数据库系统都包含constructs来对一系列的SQL statements进行分组，这些statements可以作为单独的单元来执行.存储过程就是一个例子，另一个例子是用户自定义函数(UDFs), 我们将在第9步进行详细的探讨.
SQL是设计来处理一系列数据的. SELECT,UPDATE,和DELETE statements适用于相应表的所有记录，且通过WHERE字句来进行筛选.也有很多的特性被设计来一次处理一条记录，或操作标量数据(scalar data).比如CURSORs允许一次遍历所有的记录.字符串操作功能，比如LEFT, CHARINDEX, 以及PATINDEX用来处理标量数据.SQL也包含了控制流声明，比如IF和WHILE.
在Microsoft SQL Server 2005之前，存储过程和用户自定义函数UDFs只能当做一个
T-SQL statements集来创建，而SQL Server 2005设计时包含Common Language Runtime (CLR)。

因此，对一个SQL Server 2005数据库里的存储过程和用户定义方法，我们可以用managed code来进行创建。

那就也说你可以在一个C#类里创建一个存储过程或用户定义函数.这样一来我们就可以在.NET Framework或你自己定义的类里面使用这些存储过程或方法.
在本文我们将考察如何创建存储过程和用户定义函数，以及如何将它们整合进数据库Northwind.让我们开始吧。

注意：
管理数据库对象(Managed database objects)与SQL数据库里包含的相对应的数据库对象比较起来有一些优势，主要体现在：使用的语言更丰富、熟悉；可以使用现有的代码和逻辑.但是在处理那些并不包含很多逻辑的一系列数据时，其效率可能要低一些.关与managed code相较T-SQL而言的优势，请参阅文章《Advantages of Using Managed Code to Create Database Objects》(/en-us/library/k2e1fb36(VS.80).aspx)
第一步：将Northwind数据库移出App_Data文件夹
本教程到目前为止使用的是放在App_Data文件夹里的Microsoft SQL Server 2005 Express版本的数据库.
然而在本教程，我们将Northwind数据库移出App_Data文件夹，再使用一个被注册为SQL Server 2005 Express版本数据库的实例。

虽然我们不移出的话也可以执行本文的这些步骤，
不过将其注册为一个SQL Server 2005 Express版本数据库的实例的话，这些步骤就要简单的多了。

本文下载代码里包含2个数据库文件:NORTHWND.MDF 和NORTHWND_log.LDF，将它们放在一个叫DataFiles的文件夹里，先关闭Visual Studio，再将NORTHWND.MDF 和NORTHWND_log.LDF文件从根目录的App_Data文件夹里移出到根目录以外的其它文件夹里。

完成后，我们需要将Northwind数据库注册为SQL Server 2005 Express版本数据库的实例。

为此我们要用到SQL Server Management Studio.如果你没有安装的话，可以在此下载并安装：
(/downloads/details.aspx?displaylang=en&FamilyID=C243A5AE -4BD1-4E3D-94B8-5A0F62BF7796)。

打开SQL Server Management Studio，如图1所示，Management Studio首先问我们连接什么服务器，在server name里键入“localhost/SQLExpress” ，在Authentication下拉列表里选“Windows Authentication” 。

点Connect.
图1：连接恰当的数据库实例
一旦连接后，Object Explorer窗口将会把SQL Server 2005 Express版本数据库实例的信息显示出来，比如databases, security information, management options等.
我们需要将DataFiles文件夹里的Northwind数据库作为SQL Server 2005 Express版本数据库实例。

在Databases文件夹里右键单击，选“Attach”项。

这将打开Attach Databases
对话框.点Add 按钮，找到NORTHWND.MDF文件，再点OK。

这样，你的屏幕看起来和图2差不多：
图2：连接到合适的数据库实例
注意：
当通过Management Studio连接到SQL Server 2005 Express版本的数据库实例时，Attach Databases对话框不允许你浏览用户私人文件目录(user profile directories),比如My Documents.因此，一定要将NORTHWND.MDF 和NORTHWND_log.LDF文件放在一个非用户私人文件目录里.
点OK完成后，Attach Databases对话框将会关闭，Object Explorer将会将新添加的数据库列出来。

问题来了，它的名字可能是这样的：
9FE54661B32FDD967F51D71D0D5145CC_LINE
ARTICLES/DATATUTORIALS/VOLUME
3/CSHARP/73/ASPNET_DATA_TUTORIAL_75_CS/APP_DATA/NORTHWND.MDF，我们将其重命名为“Northwind”，方法是在该数据库右键单击，选“Rename”.
图3：将数据库重命名为“Northwind”
第二步：在Visual Studio里创建一个新的解决方案和SQL Server Project
要在SQL Server 2005里创建管理存储过程或用户定义函数，我们要在一个类里用C# 代码写这些存储过程和用户定义函数。

一旦写完后，我们需要将该类编译为一个 .dll文件，将该编译文件注册到一个SQL Server 数据库，然后在数据库里创建一个存储过程或用户定义函数以指向编译文件里对应的方法。

这些步骤要手工完成。

我们可以在文本编辑器里写代码，在命令行里用C#编译器(csc.exe)对其进行编译；用CREATE ASSEMBLY 命令或从Management Studio里将其注册到数据库.添加存储过程或用户定义函数的方法类似.幸运
的是Visual Studio的Professional 和Team Systems 版本包含一个SQL Server Project 类型，它可以自动的完成这些工作.在本文，我们将使用SQL Server Project类型来创建一个管理存储过程和用户定义函数。

注意：
如果你使用的是Visual Studio的Visual Web Developer 或Standard版本，你就要手动完成了。

在Step 13，我们将详细介绍手动完成的细节.我们鼓励你从Steps 2 看到Steps 12 ，再看Step 13.因为Steps 2 到Steps 12包含了很重要的SQL Server 配置说明，不管你用的是什么版本.
打开Visual Studio. 从File 菜单，选New Project以打开New Project对话框(见图4).点到Database工程类型，在右边的Templates列表，选择创建一个新的SQL Server Project.
我将其命名为ManagedDatabaseConstructs并放在一个名为Tutorial75的解决方案里.
图4：创建一个新的SQL Server Project
在New Project对话框里点OK按钮，创建该解决方案和SQL Server Project.
一个SQL Server Project依赖于一个具体的数据库.因此，接下来我们要指定该信息。

如图5所示，New Database Reference对话框里指向了Northwind数据库，也就是我们在第一步里注册的SQL Server 2005 Express版本数据库实例.
图5：将SQL Server Project与Northwind数据库联系起来
为了对我们将要在本工程创建的管理存储过程和用户自定义函数进行调试，我们需要激活SQL/CLR调试支持.无论什么时候将一个SQL Server Project与新的数据库联系起来时(就像我们在图5做的那样)，Visual Studio会询问我们是否激活SQL/CLR调试(如图6)，选Yes.
图6：激活SQL/CLR调试
此时，这个新的SQL Server Project已经添加到解决方案里了。

其包含一个Test Scripts 文件夹，文件夹里是一个Test.sql文件。

它用来对本工程添加的管理数据库对象进行调试，我们将在第12步考察调试.
我们现在可以对该工程添加新的管理存储过程和用户自定义函数.不过在此之前，我们要将现有的web应用程序包含进解决方案。

在File 菜单里选Add项，再选Existing Web Site.浏览到相应的文件夹，点OK.如图7所示，这将更新解决方案以包含2个工程：即website 和名为ManagedDatabaseConstructs的SQL Server Project.
图7：该解决方案现在包含2个工程
Web.config文件里的NORTHWNDConnectionString值当前引用的是App_Data文件夹里的NORTHWND.MDF。

由于我们已经将其从App_Data文件夹移出了，其注册为SQL Server 2005 Express版本数据库实例，因此我们需要相应的更新NORTHWNDConnectionString 值。

打开Web.config文件，改动NORTHWNDConnectionString值，像这样：“Data Source=localhost/SQLExpress;Initial Catalog=Northwind;Integrated Security=True”.
完成后，你的Web.config文件的<connectionStrings>节点看起来和下面的差不多：
<connectionStrings>
<add name="NORTHWNDConnectionString" connectionString=
"Data Source=localhost/SQLExpress;Initial Catalog=Northwind;
Integrated Security=True;Pooling=false"
providerName="System.Data.SqlClient" />
</connectionStrings>
注意：
就像在上一章探讨的一样，当从一个客户端程序——比如一个 website，调试一个SQL Server对象时，我们需要关闭连接池。

上面的连接字符串里我们关闭了连接池(“Pooling=false”). 如果你不打算从 website调试管理存储过程和用户自定义函数的话，激活连接池.
第三步：创建一个Managed Stored Procedure
要向Northwind数据库添加一个管理存储过程的话，我们首先要创建一个存储过程作为该SQL Server Project里的一个方法。

从解决资源管理器里，右键单击ManagedDatabaseConstructs工程，选“添加新项”，这将展示Add New Item 对话框，其列出了可以添加到该工程的各种管理数据库对象的类型，如图8所示，包括stored procedures 、User-Defined Functions等.我们来创建一个存储过程，用来简单的将那些处于discontinued状态的产品返回，将该存储过程文件命名为GetDiscontinuedProducts.cs.
图8：添加一个新的存储过程，名为GetDiscontinuedProducts.cs
这将创建一个新的C# class类文件，如下：
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 GetDiscontinuedProducts()
{
// Put your code here
}
};
我们注意到该存储过程作为一个static方法来执行，且位于一个名为StoredProcedures的部分类(partial class)文件之内. 此外，该GetDiscontinuedProducts方法有一个SqlProcedure特性, 这就标明了该方法是一个存储过程.
下面的代码创建了一个SqlCommand对象，设其CommandText为一个SELECT查询，以返回Products table表里所有Discontinued列为1的记录.它然后执行该命令并将结果返回给客户端程序.添加这些代码到GetDiscontinuedProducts方法.
// Create the command
SqlCommand myCommand = new SqlCommand();
mandText =
@"SELECT ProductID, ProductName, SupplierID, CategoryID,
QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,
ReorderLevel, Discontinued
FROM Products
WHERE Discontinued = 1";
// Execute the command and send back the results
SqlContext.Pipe.ExecuteAndSend(myCommand);
所有的管理数据库对象都可以使用SqlContext对象，该对象展示“调用者”(caller)的内容；而SqlContext又可以通过其Pipe属性来访问一个SqlPipe对象，该对象用来在SQL Server 数据库和调用程序之间传递信息；而ExecuteAndSend方法，就像其名字暗示的那样，执行传入的SqlCommand对象，并将结果返回给客户端程序.
注意：
管理数据库对象最适合做这种存储过程和用户定义函数——使用procedural logic逻辑而不是set-based logic逻辑.所谓Procedural logic逻辑包括处理一系列一行行(on a row-by-row basis)的数据或者处理标量数据(scalar data).然而，我们刚刚创建的GetDiscontinuedProducts方法，并未使用Procedural logic逻辑。

其实该方法最理想的是作为一个T-SQL存储过程来执行.之所以作为一个管理存储过程来执行，是为了示范创建和配置管理存储过程所必要的步骤.
步骤4：配置Managed Stored Procedure
代码完成后我们准备将其配置给Northwind数据库.“Deploy”项执行的具体步骤我们将在第13步讲明白。

进入解决资源管理器，在ManagedDatabaseConstructs工程名上右键单击，选“Deploy”项，然而，可能会出现如下的错误消息：“Incorrect syntax near 'EXTERNAL'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel.”
该出错信息发生试图将编译文件注册到Northwind数据库时.为了将一个编译文件注册到一个SQL Server 2005数据库，该数据库的compatibility level必须设置为90.默认下，一个新的SQL Server 2005数据库的compatibility level为90.而Microsoft SQL Server 2000使用的数据库的默认compatibility level为80.由于使用的Northwind数据库最初是一个Microsoft SQL Server 2000数据库，其compatibility level被设置为80，因此需要设置为90以便于进行注册.要更新数据库的compatibility level，在Management Studio里打开一个New Query窗口，输入：exec sp_dbcmptlevel 'Northwind', 90点击工具栏上的执行图标以运行上述查询.
图9：更新Northwind数据库的Compatibility Level
更新后重新部署该SQL Server Project，这次应该不会出错了.返回SQL Server Management Studio，在Object Explorer里的Northwind 数据库上右键单击，选“刷新”.接下来，找到Programmability文件夹，再展开Assemblies文件夹。

如图10所示，Northwind 数据库现在包含了一个由ManagedDatabaseConstructs工程生成的编译文件.
图10：该ManagedDatabaseConstructs编译文件现在注册到Northwind数据库
也来打开Stored Procedures文件夹。

你将会看到名为GetDiscontinuedProducts的存储过程。

该存储过程是在部署的时候创建的，它指向ManagedDatabaseConstructs编译文件里的GetDiscontinuedProducts方法.当执行GetDiscontinuedProducts存储过程时，它反过来执行GetDiscontinuedProducts方法.由于它是一个管理存储过程(managed stored procedure),不能通过Management Studio来对其进行编辑(因此，在存储过程名旁边有一个锁的图标)
图11：GetDiscontinuedProducts存储过程展示在Stored Procedures文件夹
还有一个障碍哟克服：该数据库被配置为阻止执行managed code.我们来做一个实验。

打开一个new query窗口，执行GetDiscontinuedProducts存储过程.你将会收到如下的错误信息：“Execution of user code in the .NET Framework is disabled. Enable …clr
enabled‟configuration option.”
让我们检查Northwind数据库的配置信息，在查询窗口键入并运行命令“exec sp_configure”。

其显示“clr enabled”目前设置为0.
图12：“clr enabled”目前设置为0.
我们注意到每条配置(如图12)都有4个值：“minimum”、“maximum”、“config”、“run” 值.要更新“clr enabled”配置的“config”值，执行如下的命令：exec sp_configure 'clr enabled', 1
如果你再运行“exec sp_configure”的话，你将看到上述声明将“clr enabled”配置的“config”值设为1，而“run”值仍然为0.因此，我们需要执行RECONFIGURE命令,它将会把“ run”值设为目前的“config”值.在查询窗口输入“RECONFIGURE”，再点工具栏上的执行图标.如果你运行“exec sp_configure”的话，现在你可以看到“clr enabled”配置的“config” 和“run”值都为1.
完成“clr enabled”配置后，我们准备运行GetDiscontinuedProducts存储过程.在查询窗口键入并运行命令“exec GetDiscontinuedProducts”.调用该存储过程将导致执行GetDiscontinuedProducts方法里相应的managed code.代码发出一个SELECT查询并返回所有处于discontinued状况的产品，并将数据返回给调用程序——具体到本例，就是SQL Server Management Studio.Management Studio将接收到的数据展示在Results窗口.
图13：GetDiscontinuedProducts存储过程返回所有处于Discontinued状态的产品
第五步：创建接收输入参数的Managed Stored Procedures
我们在本教程创建的很多查询和存储过程都使用参数.比如，在第67章，我们创建了一个名为GetProductsByCategoryID的存储过程，它接收一个名为@CategoryID的输入参数.该存储过程返回那些其CategoryID值与@CategoryID吻合的产品.
要创建接收输入参数的managed stored procedure，仅仅在定义方法时指定这些参数即可.我们来做个演示，在ManagedDatabaseConstructs工程里添加一个名为GetProductsWithPriceLessThan的另一个managed stored procedure.该managed stored procedure接收一个指定了价格的参数，返回所有的其UnitPrice列低于参数值的产品.
我们来进行添加.在ManagedDatabaseConstructs工程名上右键单击，选“添加新存储过程”.将文件命名为GetProductsWithPriceLessThan.cs.就像我们在图3看到的那样，这将创建一个新的C# class类文件.
更新GetProductsWithPriceLessThan方法以使其接收一个名叫price的SqlMoney类型的输入参数.代码如下：
[Microsoft.SqlServer.Server.SqlProcedure]
public static void GetProductsWithPriceLessThan(SqlMoney price)
{
// Create the command
SqlCommand myCommand = new SqlCommand();
mandText =
@"SELECT ProductID, ProductName, SupplierID, CategoryID,
QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,
ReorderLevel, Discontinued
FROM Products
WHERE UnitPrice < @MaxPrice";
myCommand.Parameters.AddWithValue("@MaxPrice", price);
// Execute the command and send back the results
SqlContext.Pipe.ExecuteAndSend(myCommand);
}
GetProductsWithPriceLessThan方法的生命代码和我们在第三步创建的GetDiscontinuedProducts方法的声明代码很相似.唯一的不同在于GetProductsWithPriceLessThan方法接收一个输入参数(price), 且SqlCommand的查询里也包含了一个参数(@MaxPrice).
完成代码添加后，重新部署SQL Server Project.接下来，返回到SQL Server Management Studio并刷新Stored Procedures文件夹.你将会看到一个新的查询——GetProductsWithPriceLessThan.从查询窗口，键入并执行命令“exec GetProductsWithPriceLessThan 25”，它会将所有价格低于25的产品显示出来，如图14所示.
图14：价格低于25的产品都显示出来
第六步：从数据访问层调用Managed Stored Procedure
此时，我们已经向ManagedDatabaseConstructs工程添加了GetDiscontinuedProducts 和GetProductsWithPriceLessThan这2个managed stored procedures，并把它们注册到Northwind SQL Server数据库.我们同样从SQL Server Management调用它们(见图13和14)。

为了使我们的应用程序能调用这些managed stored procedures,因此我们需要将它们添加到体系的数据访问层和业务逻辑层.在这一步，我们向类型化数据集NorthwindWithSprocs的ProductsTableAdapter添加2个新的方法.在第七步，我们将添加相应的方法到业务逻辑层.
在Visual Studio里打开类型化数据集NorthwindWithSprocs，向ProductsTableAdapter添加一个名为GetDiscontinuedProducts的方法.
注意：
由于我们已经将Northwind数据库移出了App_Data文件夹，所以我们应该在Web.config 文件里对连接字符串进行相应的更新。

在第二步我们探讨了更新Web.config文件里的NORTHWNDConnectionString的值.如果你忘记了更新的话，当你试图向TableAdapter添加新方法的时候，你将看到这样的错误信息：“Failed to add query. Unable to find connection …NORTHWNDConnectionString‟ for object …Web.config‟”.要克服这个问题，需要更新Web.config文件的NORTHWNDConnectionString值.就像在第二步探讨的那样，再重新向TableAdapter添加新方法，这次就不会出错了.
添加新方法的时候，TableAdapter查询配置向导首先询问我们如何访问数据库。

由于我们已经创建并注册了GetDiscontinuedProducts存储过程，因此选“Use existing stored procedure”项，点Next.
图15：选“Use existing stored procedure” 项
接下来要我们选要调用的存储过程。

在左边下拉列表里选存储过程GetDiscontinuedProducts.
图16：选GetDiscontinuedProducts存储过程
由于GetDiscontinuedProducts将返回一系列的记录行，我们选第一项(“Tabular data”) ，再点Next.
图17: 选“Tabular Data” 项
最后，向导要我们为方法命名，选中这2项并分别命名为FillByDiscontinued 和GetDiscontinuedProducts.点Finish完成向导.
图18：将方法命名为FillByDiscontinued 和GetDiscontinuedProducts
采用同样的方法，为名为GetProductsWithPriceLessThan的managed stored procedure 在ProductsTableAdapter里添加FillByPriceLessThan和GetProductsWithPriceLessThan 这2个方法.
图19显示的是为GetDiscontinuedProducts 和GetProductsWithPriceLessThan这2个managed stored procedures在ProductsTableAdapter里添加各种方法后的界面.
图19: ProductsTableAdapter包含了新添加的方法
第七步：向业务逻辑层添加相应的方法
现在我们已经更新了数据访问层，我们还需要向业务逻辑层添加相应的方法.向ProductsBLLWithSprocs class类添加如下的2种方法：
[ponentModel.DataObjectMethodAttribute
(ponentModel.DataObjectMethodType.Select, false)]
public NorthwindWithSprocs.ProductsDataTable GetDiscontinuedProducts() {
return Adapter.GetDiscontinuedProducts();
}
[ponentModel.DataObjectMethodAttribute
(ponentModel.DataObjectMethodType.Select, false)]
public NorthwindWithSprocs.ProductsDataTable
GetProductsWithPriceLessThan(decimal priceLessThan)
{
return Adapter.GetProductsWithPriceLessThan(priceLessThan);
}
这2种方法都简单的调用数据访问层对应的方法，并返回ProductsDataTable实例.而2种方法上面附加的DataObjectMethodAttribute标记的作用是将方法包含到ObjectDataSource控件的配置数据源向导的SELECT标签里的下拉列表项里.
第八步：从表现层调用Managed Stored Procedures
当对数据访问层和业务逻辑层进行扩充以支持调用GetDiscontinuedProducts 和GetProductsWithPriceLessThan这2种managed stored procedures后，我们可以在一个页面里展示这些存储过程的结果了.
打开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
在第74章我们探讨了通过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”项。

另外，我们还要将应用程序与SQL Debugger结合起来，而且关闭连接池.这些步骤我们在第74章的第2步里详细探讨过了.
一旦你配置完应用程序和数据库.设置 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();
mandText =
@"SELECT ProductID, ProductName, SupplierID, CategoryID,
QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,
ReorderLevel, Discontinued
FROM Products
WHERE UnitPrice > @MinPrice";
myCommand.Parameters.AddWithValue("@MinPrice", price);。