欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2024-03-31 10:08:40
第八步:从表现层调用managed stored procedures   当对数据访问层和业务逻辑层进行扩充以支持调用getdiscontinuedproducts 和...

第八步:从表现层调用managed stored procedures

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

  打开advanceddal文件夹里的managedfunctionsandsprocs.aspx页面,从工具箱拖一个gridview控件到设计器,设其id为discontinuedproducts,在其智能标签里绑定到一个名为discontinuedproductsdatasource的objectdatasource控件,设置其调用productsbllwithsprocs class类的getdiscontinuedproducts方法.

在ASP.NET 2.0中操作数据之七十四:用Managed Code创建存储过程和用户自定义函数(下部分)
图20:调用productsbllwithsprocs class类

在ASP.NET 2.0中操作数据之七十四:用Managed Code创建存储过程和用户自定义函数(下部分)
图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控件展现出来.

在ASP.NET 2.0中操作数据之七十四:用Managed Code创建存储过程和用户自定义函数(下部分)
图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查询得到的输出结果.

在ASP.NET 2.0中操作数据之七十四:用Managed Code创建存储过程和用户自定义函数(下部分)
图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文件夹里.

在ASP.NET 2.0中操作数据之七十四:用Managed Code创建存储过程和用户自定义函数(下部分)
图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.

在ASP.NET 2.0中操作数据之七十四:用Managed Code创建存储过程和用户自定义函数(下部分)
图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窗口里在数据库上右键单击,以对该配置进行修改.

在ASP.NET 2.0中操作数据之七十四:用Managed Code创建存储过程和用户自定义函数(下部分)
图26:确保数据库激活sql/clr调试

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

在ASP.NET 2.0中操作数据之七十四:用Managed Code创建存储过程和用户自定义函数(下部分)
图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方法,检查输入参数的值等等.

在ASP.NET 2.0中操作数据之七十四:用Managed Code创建存储过程和用户自定义函数(下部分)
图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

在ASP.NET 2.0中操作数据之七十四:用Managed Code创建存储过程和用户自定义函数(下部分)
图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文件.

在ASP.NET 2.0中操作数据之七十四:用Managed Code创建存储过程和用户自定义函数(下部分)
图30:将manuallycreateddbobjects.dll添加到数据库

在ASP.NET 2.0中操作数据之七十四:用Managed Code创建存储过程和用户自定义函数(下部分)
图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的产品展示出来.

在ASP.NET 2.0中操作数据之七十四:用Managed Code创建存储过程和用户自定义函数(下部分)
图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有所帮助。

相关标签: SQL 自定义函数