1、ASPNet学习之常用SQL存储过程ASP.Net学习之常用SQL存储过程 在ASP.Net项目中使用存储过程,首先可以提高数据库的安全性，其次可以提高运行SQL代码运行的速度，在大型项目中一般是必不可少的。VisualStudio.Net为SQL的存储过程提供了强大的支持，您既可以通过visual来新建存储过程，也可以直接在SqlServer的查询分析器中运行，还可以通过企业管理器创建，使用起来也非常方便。大家一直都误认为SQL存储过程是一个比较“高深”的技术，其实掌握一般的语法是没有什么大问题的，而我们在使用存储教程中也主要是增删减的操作，学会使用一般的T-SQL就很容易上手了。我们先来看
2、一下在Sql-server中是如何创建一个存储过程的吧，我们可以使用SQL命令语句创建，也可以通过SQLserver中的企业管理器来创建，但其实都是离不开自己写语句的，当然系统存储过程我们就不用去动它了（存储过程分为系统存储过程，本地存储过程，临时存储过程，远程存储过程，扩展存储过程），而本地存儲過程就是我們自己編寫的存储过程，其实也叫用户存储过程。当创建存储过程时需要确定存储过程的三个组成部分所有的输入参数以及传给调用者的输出参数被执行的针对数据库的操作语句包括调用其它存储过程的语句返回给调用者的状态值以指明调用是成功还是失败一、存储过程创建1.用企业管理器来创建存储过程;我们先打开企业管理
3、器，找到我们要创建存储过程的数据库，如图一:我们可以看到，在数据库里面有一个存储过程的项目，我们要用到的就是它了，选中存储过程这项，我们可以看到数据库中里面本身就有很多存储过程存在的了，不过这些都是数据库本身自带的，我们可以看到他的类型是系统（如图二），如果我们自己创建的存储过程，类型就是用户了.在控制树的左边，我们选中存储过程后单击鼠标右键，可以看到有一个“创建存储过程的选项”选中这项，就会出现新的窗口了，这个窗口就是用来写存储过程的了在实际运用过程中，我们所创建的存储过程并不是想像中的这么复杂，而上面的这些参数也不是都要用上，但一般我们在用查询分析器创建存储过程前，都会通过下面这条语句来查
4、询一下，在数据库中是否已经存在相同命名的存储过程,如果存在的话，则先删除。Ifexists(selectnamefromsysobjectswherename=存储过程名andtype=p)Dropprocedure存储过程名Go在这里，我们也就知道了如何删除一个存储过程了，就是用dropprocedure关键字+存储过程名.我们先列举几个常见的存储过程：a.没有使用参数的存储过程/*用途：查询所有的公司名录德仔创建于2006-3-29*/createprocedurecom_selectasselect*fromCompanyGOb.有参数的存储过程/*选择对应的admin创建者:德仔创建日
5、期:2006-4-20*/createprocedureadmin_selectadminusernamechar(50),adminpasswordchar(50)asselect*fromsuperadminwhereAdmin_Name=adminusernameandAdmin_Password=adminpasswordGOc.在该存储过程中使用了OUTPUT保留字有返回值的存储过程createproceduresalequastor_idchar4,sumsmallintoutputasselectord_num,ord_date,payterms,title_id,qtyfrom
6、saleswherestor_id=stor_idselectsum=sumqtyfromsaleswherestor_id=stor_idgo上面的几个存储过程是基本的存储过程，同时我们可以看到在存储过程中注释是用/*注释*/形式.我们下一次再讲讲存储过程在net中的使用吧-二、存储过程使用篇1.在SQL中执行执行已创建的存储过程使用EXECUTE命令其语法如下EXECUTEreturn_statur=procedure_name;number|procedure_name_varparameter=value|variableOUTPUT|DEFAULT,nWITHRECOMPILE各参数
7、的含义如下return_status是可选的整型变量用来存储存储过程向调用者返回的值procedure_name_var是一变量名用来代表存储过程的名字其它参数据和保留字的含义与CREATEPROCEDURE中介绍的一样例如我们有一个存储过程名为student_list_info要执行,在查询分析器中你只要写Executestudent_list_infoGo就可以了如果存储过程中包含有返回值的存储过程，那我们就必须指定参数值.看下面这个例子此例摘自SQLserver程序员指南一书createproceduresalequastor_idchar4,sumsmallintoutputassel
8、ectord_num,ord_date,payterms,title_id,qtyfromsaleswherestor_id=stor_idselectsum=sumqtyfromsaleswherestor_id=stor_idgo要执行此存储过程，则我们要指定参数sort_id，sum的参数值.declaretotalquasmallintexecutesalequa7131,totalquaoutputiftotalqua50andtotalqua100select销售信息=销售等级为1销售量为+rtrimcasttotalquaasvarchar20运行结果为ord_numord_da
9、tepaytermstitle_idqty-N9140081994-09-1400:00:00.000Net30PS209120N9140141994-09-1400:00:00.000Net30MC302125P3087a1993-05-2900:00:00.000Net60PS137220P3087a1993-05-2900:00:00.000Net60PS210625P3087a1993-05-2900:00:00.000Net60PS333315P3087a1993-05-2900:00:00.000Net60PS7777256rowsaffected销售信息-销售等级为1销售量为13
10、02.在ASP.NET中使用存储过程要在ASP.Net(这里以c#为说明)中使用存储过程，首先要查看一下页面中是否引用了System.Data.Sqlclient;当然数据库连接是必不可少的。我们知，一般我们在Asp.Net中调用数据的步骤是这样的：新建一个数据库连接对象(一般用SqlConnection)用Open()方法打开我们要操作的数据库创建一个SqlCommand或SqlDataAdapter对象对SQL命令或存储过程用ExecuteNonQuery()方法或ExecuteReader()方法进行执行数据操作读取或输入数据至数据库用Close()方法关闭连接.由此可知,在使用存储过程
11、前，我们要用SqlCommand对象或SqlDataAdapter对象使填充DataSet或共它在运用存储过程中有很大的作用.但其运用的方法是跟在Net中直接执行Sql语句区别并不是很大的，我们可以通过例子来说明是乍样调用存储过程的.(1)采用SqlCommand对象程序代码：stringspid=Request.QueryStringsupplyid.Trim();SqlConnectionconndb=newSqlConnection(System.Configuration.ConfigurationSettings.AppSettingsconn);conndb.Open();SqlC
12、ommandstrselect=newSqlCommand(supplyinfo_select_supplyid,conndb);strselect.CommandType=CommandType.StoredProcedure;strselect.Parameters.Add(supply_ID,spid);SqlDataReaderreader=strselect.ExecuteReader();if(reader.Read()LblId.Text=readerSupply_Id.ToString().Trim();LblTitle.Text=readerSupply_Subject.To
13、String().Trim();LblBigclass.Text=readerSupply_CatID.ToString().Trim();LblDesc.Text=readerSupply_Details.ToString().Trim();LblPurType.Text=readerSupply_PurchaseType.ToString().Trim();if(int.Parse(readerSupply_Ischecked.ToString().Trim()=1)LblIschk.Text=已通过审核;elseLblIschk.Text=没有通过审核;if(int.Parse(read
14、erSupply_Isrcmd.ToString().Trim()=1)LblIsrcmd.Text=已设置为推荐;elseLblIsrcmd.Text=没有设置为推荐;switch(readerSupply_Reader_Level.ToString().Trim()case0:LblLevel.Text=设置所有人都可以看到此信息;break;case1:LblLevel.Text=设置注册会员可以看到此信息;break;case2:LblLevel.Text=设置VIP会员可以看到此信息;break;由上可以看到，利用SqlCommand对象调用存储过程的关键语句是:SqlCommand
15、strselect=newSqlCommand(supplyinfo_select_supplyid,conndb);strselect.CommandType=CommandType.StoredProcedure;strselect.Parameters.Add(supply_ID,spid);简单解释：声明一个SqlCommand对像,通过SqlCommand调用存储过程supplyinfo_select_supplyid,同时包含了一个输入参数supply_id,其值是变量spid,同时通过ExecuteReader()方法，查询数据相关的数据，通过label控件，将数据显示出来.(2
16、)采用SqlDataAdapter对象程序代码：privatevoidbuycatalog()SqlConnectionconndb=newSqlConnection(System.Configuration.ConfigurationSettings.AppSettingsconn);conndb.Open();SqlDataAdapterstrselect=newSqlDataAdapter(productclass,conndb);strselect.SelectCommand.CommandType=CommandType.StoredProcedure;DataSetds=newDa
17、taSet();strselect.Fill(ds);DlstBuycatalog.DataSource=ds;DlstBuycatalog.DataKeyField=PdtCat_ID;DlstBuycatalog.DataBind();conndb.Close();以上这个方法，就是通过SqlDataAdapter对像调用了SQL中存储过程productclass，通过DataSet将数据填充在ds中,同时指定DataList控件DlstBuycatalog的数据源是ds,主键是PdtCat_Id,最后再重新绑定Datalist控件.由这个方法我们可以看到用SqlDataAdapter调用
18、存储过程中的关键是：SqlDataAdapterstrselect=newSqlDataAdapter(productclass,conndb);strselect.SelectCommand.CommandType=CommandType.StoredProcedure;当存储过程中有参数时，我们又应该乍样做呢？其实这个跟SqlCommand的差不多，我们只要再加一句Strselect.SelectCommand.Parameter.Add(“pdt_name”,txtpdtname.Text();就可以了，其中pdt_name是在存储过程中声明的参数变量名，而txtpdtname.text
19、()是在.net中赋于变量pdt_name的值了。认真看一下下面这个存储过程就很清楚了:由上面我们可以知道在调用存储过程中，最关键的对象是Command对象,这个对象可以通过ExecuteReader()方法执行数据查询，还可以返回一个单一值的查询，还可以通过ExecuteScalar()方法进行相关的数据统计，还可以通过ExecuteNonQuery()方法进行数据更新，增删改的执行操作，而在执行这些SQL操作时，往往是与相关的控件DataGrid，DataList,Repeat控件结合使用的.(3)常用的一些存储过程例子以下是自己在最近所做的一个项目中所用到的一些存储过程，可能由于自己水平
20、有限，有些写得不是很规范，不过大部分都实现到我想要的结果了，这些存储过程都可以正常执行,把这些发出来给大家(数据库因保密请见谅)，希望对大家用用，同时希望指正其中的错误，谢谢。(1)选择所有的记录程序代码：/*作者：德仔用途：查询sellinfo里所有的记录日期：2006-3-23*/createproceduresellinfo_selectasselect*fromsellinfoGO(2)删除指定的ID记录程序代码：/*作者：德仔用途：删除sellinfo里由输入参数sell_id指定的ID记录日期：2006-3-23*/CREATEPROCEDUREsellinfo_deletesel
21、l_idbigintasdeletefromsellinfowheresell_id=sell_idGO(3)更新所对应的记录程序代码：/*作者：德仔用途：修改相对应的小类名日期：2006-4-5*/createprocedureprosmallclass_update_idsmallidint,smallnamechar(50)asupdateProductCatssetPdtCat_Name=smallnamewherePdtCat_id=smallidGO(4)验证登陆程序代码：/*作者：德仔用途：通过得到的user_nameuser_password验证登陆日期：2006-3-21*/
22、CREATEprocedureuser_loginuser_namevarchar(50),user_passwordvarchar(50)asselect*fromusercompanywhereUser_Name=User_NameandUser_Pwd=User_Passwordifrowcount0beginupdateuserssetuser_LoginTimes=user_LoginTimes+1whereUser_Name=User_NameandUser_Pwd=User_PasswordendGO(5)密码修改程序代码：/*作者：德仔用途：先查到user的密码，再修改新密码日
23、期：2006-3-23*/createprocedureuser_pwduser_namevarchar(30),user_oldpwdvarchar(30),user_newpwdvarchar(30),iOutputintoutputasifexists(select*fromuserswhereUser_Name=user_nameanduser_pwd=user_oldpwd)beginupdateuserssetuser_pwd=user_newpwdwhereUser_Name=user_nameanduser_pwd=user_oldpwdsetiOutput=1endelses
24、etioutput=-1GO(6)增加新记录程序代码：/*作者：德仔用途：添加一条新留言日期：2006-4-8*/CREATEproceduregb_addgbusernamechar(50),gbusermemberidchar(50),gbuseremailchar(50),gbusersubjectchar(50),gbusercontentchar(1500)asinsertgb(gbusername,gbusermemberid,gbuseremail,gbsubject,gbcontent)values(gbusername,gbusermemberid,gbuseremail,gbusersubject,gbusercontent)GO(7)统计数据程序代码：/*作者：德仔用途：用来统计站上所有的信息总数,包括新闻,产品,公司,等的总数日期：2006-3-23*/CREATEproceduredatacountasdeclareMemberCountintdeclareMemberVipintdeclareMemberNormintdeclareMemberUnchkRegintdeclareMemberLblRegChkintdeclareCompanyCountintdeclareCompanyRcmdintdeclareSel
