当前位置 : 主页 > 网络编程 > net编程 >

在ASP.NET 2.0中操作数据之七十四:用Managed Code创建存储过程和用户自定义函数

来源:互联网 收集:自由互联 发布时间:2022-04-19
第八步:从表现层调用Managed Stored Procedures 当对数据访问层和业务逻辑层进行扩充以支持调用GetDiscontinuedProducts 和 GetProductsWithPriceLessThan这2种managed stored procedures后,我们可以在一个ASP

第八步:从表现层调用Managed Stored Procedures

  当对数据访问层和业务逻辑层进行扩充以支持调用GetDiscontinuedProducts 和 GetProductsWithPriceLessThan这2种managed stored procedures后,我们可以在一个ASP.NET页面里展示这些存储过程的结果了.

  打开AdvancedDAL文件夹里的ManagedFunctionsAndSprocs.aspx页面,从工具箱拖一个GridView控件到设计器,设其ID为DiscontinuedProducts,在其智能标签里绑定到一个名为DiscontinuedProductsDataSource的ObjectDataSource控件,设置其调用ProductsBLLWithSprocs class类的GetDiscontinuedProducts方法.

http://img.558idc.com/uploadfile/allimg/net/2016052009411333.png
图20:调用ProductsBLLWithSprocs Class类

http://img.558idc.com/uploadfile/allimg/net/2016052009411334.png
图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控件展现出来.

http://img.558idc.com/uploadfile/allimg/net/2016052009411335.png
图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查询得到的输出结果.

http://img.558idc.com/uploadfile/allimg/net/2016052009411336.png
图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文件夹里.

http://img.558idc.com/uploadfile/allimg/net/2016052009411337.png
图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.

http://img.558idc.com/uploadfile/allimg/net/2016052009411338.png
图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窗口里在数据库上右键单击,以对该配置进行修改.

http://img.558idc.com/uploadfile/allimg/net/2016052009411339.png
图26:确保数据库激活SQL/CLR调试

  设想我们想调试GetProductsWithPriceLessThan存储过程.我们首先要在GetProductsWithPriceLessThan方法的代码里设置断点.

http://img.558idc.com/uploadfile/allimg/net/2016052009411340.png
图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方法,检查输入参数的值等等.

http://img.558idc.com/uploadfile/allimg/net/2016052009411341.png
图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

http://img.558idc.com/uploadfile/allimg/net/2016052009411442.png
图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文件.

http://img.558idc.com/uploadfile/allimg/net/2016052009411443.png
图30:将ManuallyCreatedDBObjects.dll添加到数据库

http://img.558idc.com/uploadfile/allimg/net/2016052009411444.png
图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的产品展示出来.

http://img.558idc.com/uploadfile/allimg/net/2016052009411445.png
图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有所帮助。

上一篇:aspnet_regiis.exe命令使用方法
下一篇:没有了
网友评论