七十三用ManagedCode创建存储过程和用户自定义函数(上部分)

在ASP.NET2.0中操作数据之七十三：用ManagedCode创建存储过程和用户自定义函数(上部分)作者：heker2007字体：[增加减小]类型：转载时间：2016-05-20我要评论MicrosoftSQLServer2005整合了CommonLanguageRuntime(CLR),它允许用managedcode来创建数据库对象，本文主要介绍了如何使用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》((VS.80).aspx)第一步：将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.如果你没有安装的话，可以在此下载并安装：(=en&FamilyID=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796)。打开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文件的connectionStrings节点看起来和下面的差不多：?123456connectionStringsaddname=NORTHWNDConnectionStringconnectionString=DataSource=localhost/SQLExpress;InitialCatalog=Northwind;IntegratedSecurity=True;Pooling=falseproviderName=System.Data.SqlClient//connectionStrings注意：就像在上一章探讨的一样，当从一个客户端程序——比如一个ASP.NETwebsite，调试一个SQLServer对象时，我们需要关闭连接池。上面的连接字符串里我们关闭了连接池(“Pooling=false”).如果你不打算从ASP.NETwebsite调试管理存储过程和用户自定义函数的话，激活连接池.第三步：创建一个ManagedStoredProcedure要向Northwind数据库添加一个管理存储过程的话，我们首先要创建一个存储过程作为该SQLServerProject里的一个方法。从解决资源管理器里，右键单击ManagedDatabas