75用Managed Code创建存储过程和用户自定义函数.docx
- 文档编号:25270345
- 上传时间:2023-06-06
- 格式:DOCX
- 页数:44
- 大小:573.93KB
75用Managed Code创建存储过程和用户自定义函数.docx
《75用Managed Code创建存储过程和用户自定义函数.docx》由会员分享,可在线阅读,更多相关《75用Managed Code创建存储过程和用户自定义函数.docx(44页珍藏版)》请在冰豆网上搜索。
75用ManagedCode创建存储过程和用户自定义函数
导言:
数据库,比如Microsoft’sSQLServer2005使用Transact-StructuredQueryLanguage(T-SQL)来插入、修改、检索数据.绝大多数数据库系统都包含constructs来对一系列的SQLstatements进行分组,这些statements可以作为单独的单元来执行.存储过程就是一个例子,另一个例子是用户自定义函数(UDFs),我们将在第9步进行详细的探讨.
SQL是设计来处理一系列数据的.SELECT,UPDATE,和DELETEstatements适用于相应表的所有记录,且通过WHERE字句来进行筛选.也有很多的特性被设计来一次处理一条记录,或操作标量数据(scalardata).比如CURSORs允许一次遍历所有的记录.字符串操作功能,比如LEFT,CHARINDEX,以及PATINDEX用来处理标量数据.SQL也包含了控制流声明,比如IF和WHILE.
在MicrosoftSQLServer2005之前,存储过程和用户自定义函数UDFs只能当做一个T-SQLstatements集来创建,而SQLServer2005设计时包含CommonLanguageRuntime(CLR)。
因此,对一个SQLServer2005数据库里的存储过程和用户定义方法,我们可以用managedcode来进行创建。
那就也说你可以在一个C#类里创建一个存储过程或用户定义函数.这样一来我们就可以在.NETFramework或你自己定义的类里面使用这些存储过程或方法.
在本文我们将考察如何创建存储过程和用户定义函数,以及如何将它们整合进数据库Northwind.让我们开始吧。
注意:
管理数据库对象(Manageddatabaseobjects)与SQL数据库里包含的相对应的数据库对象比较起来有一些优势,主要体现在:
使用的语言更丰富、熟悉;可以使用现有的代码和逻辑.但是在处理那些并不包含很多逻辑的一系列数据时,其效率可能要低一些.关与managedcode相较T-SQL而言的优势,请参阅文章《AdvantagesofUsingManagedCodetoCreateDatabaseObjects》(
第一步:
将Northwind数据库移出App_Data文件夹
本教程到目前为止使用的是放在App_Data文件夹里的MicrosoftSQLServer2005Express版本的数据库.
然而在本教程,我们将Northwind数据库移出App_Data文件夹,再使用一个被注册为SQLServer2005Express版本数据库的实例。
虽然我们不移出的话也可以执行本文的这些步骤,不过将其注册为一个SQLServer2005Express版本数据库的实例的话,这些步骤就要简单的多了。
本文下载代码里包含2个数据库文件:
NORTHWND.MDF和NORTHWND_log.LDF,将它们放在一个叫DataFiles的文件夹里,先关闭VisualStudio,再将NORTHWND.MDF和NORTHWND_log.LDF文件从根目录的App_Data文件夹里移出到根目录以外的其它文件夹里。
完成后,我们需要将Northwind数据库注册为SQLServer2005Express版本数据库的实例。
为此我们要用到SQLServerManagementStudio.如果你没有安装的话,可以在此下载并安装:
(
打开SQLServerManagementStudio,如图1所示,ManagementStudio首先问我们连接什么服务器,在servername里键入“localhost/SQLExpress”,在Authentication下拉列表里选“WindowsAuthentication”。
点Connect.
图1:
连接恰当的数据库实例
一旦连接后,ObjectExplorer窗口将会把SQLServer2005Express版本数据库实例的信息显示出来,比如databases,securityinformation,managementoptions等.
我们需要将DataFiles文件夹里的Northwind数据库作为SQLServer2005Express版本数据库实例。
在Databases文件夹里右键单击,选“Attach”项。
这将打开AttachDatabases对话框.点Add按钮,找到NORTHWND.MDF文件,再点OK。
这样,你的屏幕看起来和图2差不多:
图2:
连接到合适的数据库实例
注意:
当通过ManagementStudio连接到SQLServer2005Express版本的数据库实例时,AttachDatabases对话框不允许你浏览用户私人文件目录(userprofiledirectories),比如MyDocuments.因此,一定要将NORTHWND.MDF和NORTHWND_log.LDF文件放在一个非用户私人文件目录里.
点OK完成后,AttachDatabases对话框将会关闭,ObjectExplorer将会将新添加的数据库列出来。
问题来了,它的名字可能是这样的:
9FE54661B32FDD967F51D71D0D5145CC_LINEARTICLES/DATATUTORIALS/VOLUME3/CSHARP/73/ASPNET_DATA_TUTORIAL_75_CS/APP_DATA/NORTHWND.MDF,我们将其重命名为“Northwind”,方法是在该数据库右键单击,选“Rename”.
图3:
将数据库重命名为“Northwind”
第二步:
在VisualStudio里创建一个新的解决方案和SQLServerProject
要在SQLServer2005里创建管理存储过程或用户定义函数,我们要在一个类里用C#代码写这些存储过程和用户定义函数。
一旦写完后,我们需要将该类编译为一个.dll文件,将该编译文件注册到一个SQLServer数据库,然后在数据库里创建一个存储过程或用户定义函数以指向编译文件里对应的方法。
这些步骤要手工完成。
我们可以在文本编辑器里写代码,在命令行里用C#编译器(csc.exe)对其进行编译;用CREATEASSEMBLY命令或从ManagementStudio里将其注册到数据库.添加存储过程或用户定义函数的方法类似.幸运的是VisualStudio的Professional和TeamSystems版本包含一个SQLServerProject类型,它可以自动的完成这些工作.在本文,我们将使用SQLServerProject类型来创建一个管理存储过程和用户定义函数。
注意:
如果你使用的是VisualStudio的VisualWebDeveloper或Standard版本,你就要手动完成了。
在Step13,我们将详细介绍手动完成的细节.我们鼓励你从Steps2看到Steps12,再看Step13.因为Steps2到Steps12包含了很重要的SQLServer配置说明,不管你用的是什么版本.
打开VisualStudio.从File菜单,选NewProject以打开NewProject对话框(见图4).点到Database工程类型,在右边的Templates列表,选择创建一个新的SQLServerProject.我将其命名为ManagedDatabaseConstructs并放在一个名为Tutorial75的解决方案里.
图4:
创建一个新的SQLServerProject
在NewProject对话框里点OK按钮,创建该解决方案和SQLServerProject.
一个SQLServerProject依赖于一个具体的数据库.因此,接下来我们要指定该信息。
如图5所示,NewDatabaseReference对话框里指向了Northwind数据库,也就是我们在第一步里注册的SQLServer2005Express版本数据库实例.
图5:
将SQLServerProject与Northwind数据库联系起来
为了对我们将要在本工程创建的管理存储过程和用户自定义函数进行调试,我们需要激活SQL/CLR调试支持.无论什么时候将一个SQLServerProject与新的数据库联系起来时(就像我们在图5做的那样),VisualStudio会询问我们是否激活SQL/CLR调试(如图6),选Yes.
图6:
激活SQL/CLR调试
此时,这个新的SQLServerProject已经添加到解决方案里了。
其包含一个TestScripts文件夹,文件夹里是一个Test.sql文件。
它用来对本工程添加的管理数据库对象进行调试,我们将在第12步考察调试.
我们现在可以对该工程添加新的管理存储过程和用户自定义函数.不过在此之前,我们要将现有的web应用程序包含进解决方案。
在File菜单里选Add项,再选ExistingWebSite.浏览到相应的文件夹,点OK.如图7所示,这将更新解决方案以包含2个工程:
即website和名为ManagedDatabaseConstructs的SQLServerProject.
图7:
该解决方案现在包含2个工程
Web.config文件里的NORTHWNDConnectionString值当前引用的是App_Data文件夹里的NORTHWND.MDF。
由于我们已经将其从App_Data文件夹移出了,其注册为SQLServer2005Express版本数据库实例,因此我们需要相应的更新NORTHWNDConnectionString值。
打开Web.config文件,改动NORTHWNDConnectionString值,像这样:
“DataSource=localhost/SQLExpress;InitialCatalog=Northwind;IntegratedSecurity=True”.
完成后,你的Web.config文件的
"DataSource=localhost/SQLExpress;InitialCatalog=Northwind; IntegratedSecurity=True;Pooling=false" providerName="System.Data.SqlClient"/> 注意: 就像在上一章探讨的一样,当从一个客户端程序——比如一个ASP.NETwebsite,调试一个SQLServer对象时,我们需要关闭连接池。 上面的连接字符串里我们关闭了连接池(“Pooling=false”).如果你不打算从ASP.NETwebsite调试管理存储过程和用户自定义函数的话,激活连接池. 第三步: 创建一个ManagedStoredProcedure 要向Northwind数据库添加一个管理存储过程的话,我们首先要创建一个存储过程作为该SQLServerProject里的一个方法。 从解决资源管理器里,右键单击ManagedDatabaseConstructs工程,选“添加新项”,这将展示AddNewItem对话框,其列出了可以添加到该工程的各种管理数据库对象的类型,如图8所示,包括storedprocedures、User-DefinedFunctions等.我们来创建一个存储过程,用来简单的将那些处于discontinued状态的产品返回,将该存储过程文件命名为GetDiscontinuedProducts.cs. 图8: 添加一个新的存储过程,名为GetDiscontinuedProducts.cs 这将创建一个新的C#class类文件,如下: usingSystem; usingSystem.Data; usingSystem.Data.SqlClient; usingSystem.Data.SqlTypes; usingMicrosoft.SqlServer.Server; publicpartialclassStoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] publicstaticvoidGetDiscontinuedProducts() { //Putyourcodehere } }; 我们注意到该存储过程作为一个static方法来执行,且位于一个名为StoredProcedures的部分类(partialclass)文件之内.此外,该GetDiscontinuedProducts方法有一个SqlProcedure特性,这就标明了该方法是一个存储过程. 下面的代码创建了一个SqlCommand对象,设其CommandText为一个SELECT查询,以返回Productstable表里所有Discontinued列为1的记录.它然后执行该命令并将结果返回给客户端程序.添加这些代码到GetDiscontinuedProducts方法. //Createthecommand SqlCommandmyCommand=newSqlCommand(); myCommand.CommandText= @"SELECTProductID,ProductName,SupplierID,CategoryID, QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder, ReorderLevel,Discontinued FROMProducts WHEREDiscontinued=1"; //Executethecommandandsendbacktheresults SqlContext.Pipe.ExecuteAndSend(myCommand); 所有的管理数据库对象都可以使用SqlContext对象,该对象展示“调用者”(caller)的内容;而SqlContext又可以通过其Pipe属性来访问一个SqlPipe对象,该对象用来在SQLServer数据库和调用程序之间传递信息;而ExecuteAndSend方法,就像其名字暗示的那样,执行传入的SqlCommand对象,并将结果返回给客户端程序. 注意: 管理数据库对象最适合做这种存储过程和用户定义函数——使用procedurallogic逻辑而不是set-basedlogic逻辑.所谓Procedurallogic逻辑包括处理一系列一行行(onarow-by-rowbasis)的数据或者处理标量数据(scalardata).然而,我们刚刚创建的GetDiscontinuedProducts方法,并未使用Procedurallogic逻辑。 其实该方法最理想的是作为一个T-SQL存储过程来执行.之所以作为一个管理存储过程来执行,是为了示范创建和配置管理存储过程所必要的步骤. 步骤4: 配置ManagedStoredProcedure 代码完成后我们准备将其配置给Northwind数据库.“Deploy”项执行的具体步骤我们将在第13步讲明白。 进入解决资源管理器,在ManagedDatabaseConstructs工程名上右键单击,选“Deploy”项,然而,可能会出现如下的错误消息: “Incorrectsyntaxnear'EXTERNAL'.Youmayneedtosetthecompatibilitylevelofthecurrentdatabasetoahighervaluetoenablethisfeature.Seehelpforthestoredproceduresp_dbcmptlevel.” 该出错信息发生试图将编译文件注册到Northwind数据库时.为了将一个编译文件注册到一个SQLServer2005数据库,该数据库的compatibilitylevel必须设置为90.默认下,一个新的SQLServer2005数据库的compatibilitylevel为90.而MicrosoftSQLServer2000使用的数据库的默认compatibilitylevel为80.由于使用的Northwind数据库最初是一个MicrosoftSQLServer2000数据库,其compatibilitylevel被设置为80,因此需要设置为90以便于进行注册.要更新数据库的compatibilitylevel,在ManagementStudio里打开一个NewQuery窗口,输入: execsp_dbcmptlevel'Northwind',90点击工具栏上的执行图标以运行上述查询. 图9: 更新Northwind数据库的CompatibilityLevel 更新后重新部署该SQLServerProject,这次应该不会出错了.返回SQLServerManagementStudio,在ObjectExplorer里的Northwind数据库上右键单击,选“刷新”.接下来,找到Programmability文件夹,再展开Assemblies文件夹。 如图10所示,Northwind数据库现在包含了一个由ManagedDatabaseConstructs工程生成的编译文件. 图10: 该ManagedDatabaseConstructs编译文件现在注册到Northwind数据库 也来打开StoredProcedures文件夹。 你将会看到名为GetDiscontinuedProducts的存储过程。 该存储过程是在部署的时候创建的,它指向ManagedDatabaseConstructs编译文件里的GetDiscontinuedProducts方法.当执行GetDiscontinuedProducts存储过程时,它反过来执行GetDiscontinuedProducts方法.由于它是一个管理存储过程(managedstoredprocedure),不能通过ManagementStudio来对其进行编辑(因此,在存储过程名旁边有一个锁的图标) 图11: GetDiscontinuedProducts存储过程展示在StoredProcedures文件夹 还有一个障碍哟克服: 该数据库被配置为阻止执行managedcode.我们来做一个实验。 打开一个newquery窗口,执行GetDiscontinuedProducts存储过程.你将会收到如下的错误信息: “Executionofusercodeinthe.NETFrameworkisdisabled.Enable‘clrenabled’configurationoption.” 让我们检查Northwind数据库的配置信息,在查询窗口键入并运行命令“execsp_configure”。 其显示“clrenabled”目前设置为0. 图12: “clrenabled”目前设置为0. 我们注意到每条配置(如图12)都有4个值: “minimum”、“maximum”、“config”、“run”值.要更新“clrenabled”配置的“config”值,执行如下的命令: execsp_configure'clrenabled',1 如果你再运行“execsp_configure”的话,你将看到上述声明将“clrenabled”配置的“config”值设为1,而“run”值仍然为0.因此,我们需要执行RECONFIGURE命令,它将会把“run”值设为目前的“config”值.在查询窗口输入“RECONFIGURE”,再点工具栏上的执行图标.如果你运行“execsp_configure”的话,现在你可以看到“clrenabled”配置的“config”和“run”值都为1. 完成“clrenabled”配置后,我们准备运行GetDiscontinuedProducts存储过程.在查询窗口键入并运行命令“execGetDiscontinuedProducts”.调用该存储过程将导致执行GetDiscontinuedProducts方法里相应的managedcode.代码发出一个SELECT查询并返回所有处于discontinued状况的产品,并将数据返回给调用程序——具体到本例,就是SQLServerManagementStudio.ManagementStudio将接收到的数据展示在Results窗口. 图13: GetDiscontinuedProducts存储过程返回所有处于Discontinued状态的产品 第五步: 创建接收输入参数的ManagedStoredProcedures 我们在本教程创建的很多查询和存储过程都使用参数.比如,在第67章,我们创建了一个名为GetProductsByCategoryID的存储过程,它接收一个名为@CategoryID的输入参数.该存储过程返回那些其CategoryID值与@CategoryID吻合的产品. 要创建接收输入参数的managedstoredprocedure,仅仅在定义方法时指定这些参数即可.我们来做个演示,在ManagedDatabaseConstructs工程里添加一个名为GetProductsWithPriceLessThan的另一个managedstoredprocedure.该managedstoredprocedure接收一个指定了价格的参数,返回所有的其UnitPrice列低于参数值的产品. 我们来进行添加.在ManagedDatabaseConstructs工程名上右键单击,选“添加新存储过程”.将文件命名为GetProductsWithPriceLessThan.cs.就像我们在图3看到的那样,这将创建一个新的C#class类文件. 更新GetProductsWithPriceLessThan方法以使其接收一个名叫price的SqlMoney类型的输入参数.代码如下:
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 75用Managed Code创建存储过程和用户自定义函数 75 Managed Code 创建 存储 过程 用户 自定义 函数