SQL存储过程在NET数据库中的应用

1、sql存储过程在net数据库中的应用刖g存储过程（stored procedure）是一组为了完成特定功能的sql语句集，经编译后存储 在数据库屮。用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行 它。存储过程是数据库小的一个重要对象，任何一个设计良好的数据库应用程序都应该用到 存储过程。总的来说，存储过程具有以下一些优点：存储过程允许标准组件式编程存储过程能够实现较快的执行速度存储过程能够减少网络流罐存储过程可被作为一种安全机制来充分利用本文作者将向大家介绍.net数据库应用程序中存储过程的应用，以及如何将它打 ado.net中的sqldataadapter対象、data

2、set対象等结合使用以提高.net数据库应用程序 的总休性能。二. 系统要求：开发丁具：visualstudio.net数据库管理系统：sql server 2000 （其中包含了示例程序所用到的pubs数据库）三. 创建一个简单的存储过程：这里我将向大家介绍如何运用visual studio.net ide来创建存储过程。运用visual studio.net ide创建存储过程是非常容易和直观的，你只要在服务器资源管理器小导向到 pubs数据库并展开节点，就会发现包括存储过程在内的各种数据库对象。在存储过程节点上点击右键便可弹岀一个菜单，其屮包含了“新建存储过程”的命令。新 建一个存储过程

3、示，ide屮的代码编辑窗口便出现如下所示的代码模板：create procedure dbo.storcdproccdurc 1 parameter 1 datatype = default value,parameter2 datatype output)*/as/* set nocount on */return上面的代码模板符合简化的创建存储过程的语法规则，完整的语法规则如下:create proc parameter datatype with recompile i encryption i recompile , encryption as sql_statement限于篇幅，各个

4、参数的含义在此就不多作介绍了，冇兴趣的读者町以参考冇关sql server 2000数据库管理系统的资料。卜面我对该代码模板中的各个语法成分略作介绍。create procedure声明创建一 个存储过程，示面跟着该存储过程的名称。“/*厂屮的成分是该存储过程的参数，可包 括输入参数和输出参数。as关键字后而的内容是该存储过程的主体部分，其小是任何数量 和类型的包含在存储过程屮的sql语句。return关键字表明存储过程结束并能返冋整型 状态值给调用者。下面我们就來创建一个简单的不带参数的存储过程并运用create procedure dbo.up_getpublisherlnfoassele

5、ct pub_id, pub_name, city, state, countryfrom publishersreturn创建以上存储过程后，保存之。保存完毕，与该存储过程相对应的节点就会出现在服务 器资源管理器中。同时请注意代码编辑窗口中的create关键字变为alter关键字了， 该关键字是用于更改任何现有的存储过程的。要运行上述存储过程，只要点击其节点并在右 键弹出菜单中选择“运行存储过程”，运行的结果图示如卞：四. 创建一个带参数的存储过程：以上我们创建了一个简单的不带参数的存储过程，而在实际的应用中往往会用到很多带 有参数的存储过程。带有参数的存储过程一般是用丁更新数据或是插入数据

6、的。下面我们可 以运用同样的操作方法创建一个带参数的存储过程：create procedure dbo.up_updatepublisherinfo(pubchar (4),pub_name varchar (40),city varchar (20), state char (2),country varchar (30)asupdate publishersset pub_name = pub_name, city = city, state = state,country = countrywhere ( pub_id = pub_id )return在上而的创建存储过程的代码中，我们通

7、过在名称前添加一个标志来声明存储过程 的局部变量一参数，同时还声明了各个参数的类型，确定了各个参数的方向值，也即表明该 参数是输入型的还是输出型的或者是输入输出型的或者是返冋值型的。用户通过相应的存储 过程名称以及正确冇效的参数便可调用该存储过程了。还冇，你町以通过运用output关 键字在参数屮添加输出型的参数，具体方法请参考上面的语法规则。输出型的参数能返回给 调用者相关的信息。上血的存储过程能更新publishers表中相应出版商的信息。你可以通过点击该存储过程 的节点，在右键弹出菜单中选择“运行存储过程”来执行它。一旦执行，ide中便弹出一个输 入岀版商信息的对话框(如图3所示)。在该

8、对话框屮填入正确有效的更新信息，注意pubjd 的值在原來的表中必须存在，然后点击“确定”按钮便可更新数据了。五. 创建简单存储过程的数据库应用程序：卜-面我们就运用上述的不带参数的存储过程來一个数据库应用程序，其屮还用到了 ado.net 屮的 sqldataadapter 对象以及 dataset 对象。其屮的 sqldataadapter 对象作为 sql server数据库和dataset对象的桥梁将两者联系在一起。sqldataadapter对彖包含了两个常 用的方法：fill ()方法和update ()方法。其中的fill ()方法能从数据库中获取相应数 据并填充到dataset

9、对象中，而update ()方法顾名思义就是更新数据集的意思了。在调用 fill ()方法以前，我们必须设直好sqldataadapter对象的selectcommand 性，该屈性具 实是一个sqlcommand对象。selectcommand属性中包含有效的sql语句，并能据此从数 据库中获取相应数据并填充到dataset对象中。首先，我们创建一个windows forms应用程序，编程语言为c#。在visual studio.net 中创建一个新的项目后，给该项目添加一个新的类一publishers类，该类封装了连接到后台 数据库并获取数据集对象的业务逻辑。步骤如下：1添加必要的命名空间

10、引用:using system.data.sqlclient;2. 给该类添加如下一些必要的变量：private sqlconncction cnpubs;private sqlcommand cmdpubs; private sqldataadapter dapubs;private dataset dspubs;3. 在该类的构造函数屮完成连接后台数据库，获取sqldataadapter对象等业务逻辑： public publishcrs()try /创建一个数据库连接对象 cnpubs = new sqlconncction( nscrvcr=localhost;intcgratcd s

11、ccurity=truc;databasc=pubsh);/创建一个sqlcommand对象，并指明其命令类型为存储过程 cmdpubs = new sqlcommand();cmdpubs.connection = cnpubs; cmdpubs.commandtype = commandtype.storedprocedure; cmdpubs.commandtext = nup_getpublisherinfon;/创建一个sqldataadapter对象，设定其selectcommand属性为上血的sqlcommand对象 dapubs = new sqldataadapter();d

12、apubs.selectcommand = cmdpubs;创建一个dataset对象dspubs = new datasct();catch( exception ) 4. 最后为该类提供一个getpublisherlnfo ()方法，该方法sqldataadapter対象填充 dataset对彖并返回填充后的dataset对彖，方法如卜(值得注意的是：sqldataadapter对彖 会隐式地打开数据库连接外在获取数据示隐式地关闭连接，这就是说dataset对象是工作在 非连接模式下的。而当你显式地打开数据库连接并获取数据后，sqldataadapter对象并不会 将该连接关闭)： pub

13、lic dataset getpublisherinfo()/调用sqldataadapter对象的fill ()方法并返回数据集对象dapubs.fill( dspubs);return dspubs;完成publishers类的设计后，我们给主窗体添加一个datagrid控件并用它來显示dataset 对象中的数据。首先给主窗体类添加如下成员变量：private publishers pubs;private dataset ds;之后，修改主窗体类的构造函数如下：public form 1 ()/ windows窗体设计器支持所必需的/lnitializecomponent();/ to

14、do:在initializecomponent调用后添加任何构造函数代码/ pubs = new publishcrs();ds = pubs.getpublisherinfo(); datagrid 1 .datasource = ds.tables;这样该应用程序一启动主窗体的datagrid控件中便显示了运用上述不带参数的存储过 程从pubs数据库中获取的相应数据，程序运行图示如下：六. 创建带参数的存储过程的数据库应用程序：上而我们创建了一个不带参数的存储过程的应用程序，下而我们就来创建一个更加复杂 的数据库应用程序。在实际的数据库应用中，我们往往需要获取数据并更新、插入或删除数 据，

15、这时我们就需要用到带冇参数的存储过程了，同时在运用sqldataadapter对象时，我们 会调用它的update ()方法。该update ()方法会自动根据dataset对象中的datatable对 彖内各条记录的变化情况完成相应操作。sqldataadapter对彖述包含了 updatecommand、 insertcommand> deletecommand 等属性，这些属性其实都是 sqlcommand 对象。update () 方法会根据操作的类型选用相应的属性。在运用带有参数的存储过程建立数据库应用程序时，我们一般都婆用到sqlparameter 类，该类封装了各种与sql

16、参数和关的属性和方法。英中的属性包括了 parametername, sqldbtype, direction, size, value, sourcecolumn 以及sourceversion等。其中 parametername, sqldbtype, direction, size等属性是用于匹配存储过程中定义的参数的。比如下面定义的 sqlparameter对彖就是川來匹配前面定义的up_updatcpublisherinfo存储过程中的“pub_id" 参数的。sqlparameter updparam = new sqlparameter( "pub_id&qu

17、ot;, sqldbtype.char, 4 );在上面的定义屮，虽然direction属性没有明确地给出，但是它的默认值为input,所以 也就满足了我们的需要。而如果一，个sqlparameter对象的direction属性为inputoutput或 output或retumvalue,那么其direction属性就必须明确地说明了，比如下面的代码就明确 地声明了一个sqlparameter对彖的direction属性为output。oparam.directio n = parameterdirection.output;其屮的sourcecolumn属性是用于匹配一个datatable

18、对象屮的datacolumn对象的，这 种匹配能在调用update ()方法更新datatable对象时隐式地导入所需的sqlparameter对象。 如果在定义吋没有卅明该属性，那么你必须在代码中显式地说明sqlparameter对彖的 sourcecolumn 属性。其屮的sourceversion屈性的默认值是datarow对象相应字段屮的当前值，也就是要更 新到数据库中的值。当然，sourceversion属性也可以指向datarow对象相应字段中的原始 值，也即从数据库中获取的初始值。在数据库事务处理系统中，数据的同步性问题非常重要, 下面我们来建立一个能检测数据同步性的存储过程。c

19、reate procedure dbo.up_updatepublishername(pub_id char(4),pub_namc varchar(40), original_pub_name varchar(40)asif exists(select pub_idfrom publisherswhere (pub_id = pub_id) and (pub_name = original_pub_name)beginupdate publishers set pub_name = pub_namewhere (pub_id = pub_id)endreturn接着，我们在上面的应用程序屮调

20、用该存储过程以更新发行商的名称。首先，在原有应 用程序的基础上完善其业务逻辑类一publishers类：1. 添加一个新的sqlcommand对象，该对象能作为sqldataadapter对象的 updatecommand属性被使用:private sqlcommand cmdupdpubs;2. 更新该类的构造函数publishers ()函数，添加以下内容：创建另一个sqlcommand对象，该对象引用更新发行商名称的存储过程 cmdupdpubs = new sqlcommand();cmdupdpubs.connection = cnpubs; cmdupdpubs.commandty

21、pe = commandtype.storedprocedure;cmdupdpubs.commandtcxt = nup_updatcpublishcrnamcn;/为上面的sqlcommand对彖添加必要的参数cmdupdpubs.parameters.add( "pub_id", sqldbtype.char, 4, "pub_id");cmdupdpubs.parameters.add( "pub_name", sqldbtype.varchar, 40, upub_namem); sqlparameter updparam = new sqlparameter(moriginal_pub_name", sqldbtype.varchar, 40, "pub_namem);updparam. source version = datarow version.original; cmdupdpubs.parameters.add( updparam);3. 指定sqldataadapter对象的updatecommand屈性为上面定义的sqlcommand对象： dapubs.upda