asp.net sql存储过程

　　Visual Studio.Net为SQL的存储过程提供了强大的支持，您既可以通过visual studio.net来新建存储过程，也可以直接在Sql Server的查询分析器中运行，还可以通过企业管理器创建，使用起来也非常方便。大家一直都误认为SQL存储过程是一个比较“高深”的技术，其实掌握一般的语法是没有什么大问题的，而我们在使用存储教程中也主要是增删减的操作，学会使用一般的T-SQL就很容易上手了。

　　我们先来看一下在Sql-server中是如何创建一个存储过程的吧，我们可以使用SQL命令语句创建，也可以通过SQL server中的企业管理器来创建，但其实都是离不开自己写语句的，当然系统存储过程我们就不用去动它了（存储过程分为系统存储过程，本地存储过程，临时存储过程，远程存储过程，扩展存储过程），而本地存儲過程就是我們自己編寫的存储过程，其实也叫用户存储过程。

　　当创建存储过程时需要确定存储过程的三个组成部分

　　所有的输入参数以及传给调用者的输出参数

　　被执行的针对数据库的操作语句包括调用其它存储过程的语句

　　返回给调用者的状态值以指明调用是成功还是失败

　　一、存储过程创建

　　1.用企业管理器来创建存储过程;

　　我们先打开企业管理器，找到我们要创建存储过程的数据库，如图一:

　　我们可以看到，在数据库里面有一个存储过程的项目，我们要用到的就是它了，选中存储过程这项，我们可以看到数据库中里面本身就有很多存储过程存在的了，不过这些都是数据库本身自带的，我们可以看到他的类型是系统（如图二），如果我们自己创建的存储过程，类型就是用户了.

　　在控制树的左边，我们选中存储过程后单击鼠标右键，可以看到有一个“创建存储过程的选项”

　　选中这项，就会出现新的窗口了，这个窗口就是用来写存储过程的了

　　在实际运用过程中，我们所创建的存储过程并不是想像中的这么复杂，而上面的这些参数也不是都要用上，但一般我们在用查询分析器创建存储过程前，都会通过下面这条语句来查询一下，在数据库中是否已经存在相同命名的存储过程,如果存在的话，则先删除。

　　If exists(select name from sysobjects where name='存储过程名' and type='p')

　　Drop procedure ‘存储过程名'

　　在这里，我们也就知道了如何删除一个存储过程了，就是用drop procedure关键字 + 存储过程名.

　　我们先列举几个常见的存储过程：

　　a. 没有使用参数的存储过程

　　复制代码代码如下:

　　用途：查询所有的公司名录

　　德仔创建于2006-3-29

　　create procedure com_select

　　select * from Company

　　b. 有参数的存储过程

　　选择对应的admin

　　创建者:德仔

　　创建日期:2006-4-20

　　create procedure admin_select

　　@adminusername char(50),

　　@adminpassword char(50)

　　select * from superadmin where

　　[Admin_Name]=@adminusername and [Admin_Password]=@adminpassword

　　c. 在该存储过程中使用了OUTPUT 保留字有返回值的存储过程

　　create procedure salequa

　　@stor_id char 4 ,

　　@sum smallint output

　　select

　　ord_num, ord_date,

　　payterms, title_id,

　　qty

　　from sales

　　where stor_id = @stor_id

　　select @sum = sum qty

　　from sales

　　where stor_id = @stor_id

　　上面的几个存储过程是基本的存储过程，同时我们可以看到在存储过程中注释是用/* 注释 */形式.

　　我们下一次再讲讲存储过程在net中的使用吧

　　----------------

　　二、存储过程使用篇

　　1. 在SQL中执行

　　执行已创建的存储过程使用EXECUTE 命令其语法如下

　　[EXECUTE]

　　{[@return_statur=]

　　{procedure_name[;number] | @procedure_name_var}

　　[[@parameter=] {value | @variable [OUTPUT] | [DEFAULT] [,…n]

　　[WITH RECOMPILE]

　　各参数的含义如下

　　@return_status

　　是可选的整型变量用来存储存储过程向调用者返回的值

　　@procedure_name_var

　　是一变量名用来代表存储过程的名字

　　其它参数据和保留字的含义与CREATE PROCEDURE 中介绍的一样

　　例如我们有一个存储过程名为student_list_info要执行,在查询分析器中你只要写

　　Execute student_list_info

　　就可以了

　　如果存储过程中包含有返回值的存储过程，那我们就必须指定参数值.看下面这个例子

　　此例摘自《SQLserver程序员指南》一书

　　create procedure salequa @stor_id char 4 ,@sum smallint output

　　select ord_num, ord_date, payterms, title_id, qty

　　from sales

　　where stor_id = @stor_id

　　select @sum = sum qty

　　from sales

　　where stor_id = @stor_id

　　要执行此存储过程，则我们要指定参数@sort_id，@sum的参数值.

　　declare @totalqua smallint

　　execute salequa '7131',@totalqua output

　　if @totalqua<=50

　　select '销售信息'='销售等级为3 销售量为'+rtrim cast @totalqua as varchar 20

　　if @totalqua>50 and @totalqua<=100

　　select '销售信息'='销售等级为2 销售量为'+rtrim cast @totalqua as varchar 20

　　if @totalqua>100

　　select '销售信息'='销售等级为1 销售量为'+rtrim cast @totalqua as varchar 20

　　运行结果为

　　ord_num ord_date payterms title_id qty

　　-------------------- --------------------------- ------------ -------- ------

　　N914008 1994-09-14 00:00:00.000 Net 30 PS2091 20

　　N914014 1994-09-14 00:00:00.000 Net 30 MC3021 25

　　P3087a 1993-05-29 00:00:00.000 Net 60 PS1372 20

　　P3087a 1993-05-29 00:00:00.000 Net 60 PS2106 25

　　P3087a 1993-05-29 00:00:00.000 Net 60 PS3333 15

　　P3087a 1993-05-29 00:00:00.000 Net 60 PS7777 25

　　6 row s affected

　　销售信息

　　-----------------------------------------

　　销售等级为1 销售量为130

　　2. 在ASP.NET中使用存储过程

　　要在ASP.Net(这里以c#为说明)中使用存储过程，首先要查看一下页面中是否引用了 System.Data.Sqlclient;当然数据库连接是必不可少的。我们知，一般我们在Asp.Net中调用数据的步骤是这样的：

　　新建一个数据库连接对象(一般用SqlConnection)→用Open()方法打开我们要操作的数据库→创建一个SqlCommand或SqlDataAdapter对象→对SQL命令或存储过程用ExecuteNonQuery()方法或ExecuteReader()方法进行执行数据操作→读取或输入数据至数据库→用Close()方法关闭连接.

　　由此可知,在使用存储过程前，我们要用SqlCommand对象或SqlDataAdapter对象使填充DataSet或共它在运用存储过程中有很大的作用.但其运用的方法是跟在Net中直接执行Sql语句区别并不是很大的，我们可以通过例子来说明是乍样调用存储过程的.

　　(1) 采用SqlCommand对象

　　程序代码：

　　复制代码代码如下:

　　string spid=Request.QueryString["supplyid"].Trim();

　　SqlConnection conndb=new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["conn"]);

　　conndb.Open();

　　SqlCommand strselect = new SqlCommand("supplyinfo_select_supplyid",conndb);

　　strselect.CommandType= CommandType.StoredProcedure;

　　strselect.Parameters.Add("@supply_ID",spid);

　　SqlDataReader reader = strselect.ExecuteReader();

　　if(reader.Read())

　　{

　　LblId.Text=reader["Supply_Id"].ToString().Trim();

　　LblTitle.Text=reader["Supply_Subject"].ToString().Trim();

　　LblBigclass.Text=reader["Supply_CatID"].ToString().Trim();

　　LblDesc.Text=reader["Supply_Details"].ToString().Trim();

　　LblPurType.Text=reader["Supply_PurchaseType"].ToString().Trim();

　　if(int.Parse(reader["Supply_Ischecked"].ToString().Trim())==1)

　　{

　　LblIschk.Text="已通过审核";

　　}

　　else

　　{

　　LblIschk.Text="没有通过审核";

　　}

　　if(int.Parse(reader["Supply_Isrcmd"].ToString().Trim())==1)

　　{

　　LblIsrcmd.Text="已设置为推荐";

　　}

　　else

　　{

　　LblIsrcmd.Text="没有设置为推荐";

　　}

　　switch(reader["Supply_Reader_Level"].ToString().Trim())

　　{

　　case "0":

　　LblLevel.Text="设置所有人都可以看到此信息";

　　break;

　　case "1":

　　LblLevel.Text="设置注册会员可以看到此信息";

　　break;

　　case "2":

　　LblLevel.Text="设置VIP会员可以看到此信息";

　　break;

　　}

　　由上可以看到，利用SqlCommand对象调用存储过程的关键语句是:

　　SqlCommand strselect = new SqlCommand("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)采用SqlDataAdapter对象

　　程序代码：

　　复制代码代码如下:

　　private void buycatalog()

　　{

　　SqlConnection conndb= new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["conn"]);

　　conndb.Open();

　　SqlDataAdapter strselect = new SqlDataAdapter("productclass",conndb);

　　strselect.SelectCommand.CommandType = CommandType.StoredProcedure;

　　DataSet ds = new DataSet();

　　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调用存储过程中的关键是：

　　SqlDataAdapter strselect = new SqlDataAdapter("productclass",conndb);

　　strselect.SelectCommand.CommandType = CommandType.StoredProcedure;

　　当存储过程中有参数时，我们又应该乍样做呢？其实这个跟SqlCommand的差不多，我们只要再加一句

　　Strselect.SelectCommand.Parameter.Add(“@pdt_name”,txtpdtname.Text());

　　就可以了，其中@pdt_name是在存储过程中声明的参数变量名，而txtpdtname.text()是在.net中赋于变量@pdt_name的值了。认真看一下下面这个存储过程就很清楚了:

　　由上面我们可以知道在调用存储过程中，最关键的对象是Command对象,这个对象可以通过ExecuteReader()方法执行数据查询，还可以返回一个单一值的查询，还可以通过ExecuteScalar()方法进行相关的数据统计，还可以通过ExecuteNonQuery()方法进行数据更新，增删改的执行操作，而在执行这些SQL操作时，往往是与相关的控件DataGrid ，DataList,Repeat控件结合使用的.

　　(3)常用的一些存储过程例子

　　以下是自己在最近所做的一个项目中所用到的一些存储过程，可能由于自己水平有限，有些写得不是很规范，不过大部分都实现到我想要的结果了，这些存储过程都可以正常执行,把这些发出来给大家(数据库因保密请见谅)，希望对大家用用，同时希望指正其中的错误，谢谢。

　　(1) 选择所有的记录

　　程序代码：

　　复制代码代码如下:

　　作者：德仔

　　用途：查询sellinfo里所有的记录

　　日期：2006-3-23

　　create procedure sellinfo_select

　　select * from sellinfo

　　(2) 删除指定的ID记录

　　程序代码：

　　复制代码代码如下:

　　作者：德仔

　　用途：删除sellinfo里由输入参数@sell_id指定的ID记录

　　日期：2006-3-23

　　CREATE PROCEDURE sellinfo_delete

　　@sell_id bigint

　　delete from [sellinfo]

　　where

　　sell_id=@sell_id

　　(3)更新所对应的记录

　　程序代码：

　　复制代码代码如下:

　　作者：德仔

　　用途：修改相对应的小类名

　　日期：2006-4-5

　　create procedure prosmallclass_update_id

　　@smallid int,

　　@smallname char(50)

　　update [ProductCats]

　　set

　　PdtCat_Name = @smallname

　　where

　　PdtCat_id =@smallid

　　(4)验证登陆

　　程序代码：

　　复制代码代码如下:

　　作者：德仔

　　用途：通过得到的@user_name @user_password验证登陆

　　日期：2006-3-21

　　CREATE procedure user_login

　　@user_name varchar(50),

　　@user_password varchar(50)

　　select * from usercompany where [User_Name] = @User_Name and [User_Pwd] = @User_Password

　　if @@rowcount>0

　　begin

　　update [users] set user_LoginTimes=user_LoginTimes+1 where [User_Name] = @User_Name and [User_Pwd] = @User_Password

　　end

　　(5)密码修改

　　程序代码：

　　复制代码代码如下:

　　作者：德仔

　　用途：先查到user的密码，再修改新密码

　　日期：2006-3-23

　　create procedure user_pwd

　　@user_name varchar(30),

　　@user_oldpwd varchar(30),

　　@user_newpwd varchar(30),

　　@iOutput int output

　　if exists(select * from users where User_Name=@user_name and user_pwd=@user_oldpwd)

　　begin

　　update users set user_pwd=@user_newpwd where User_Name=@user_name and user_pwd=@user_oldpwd

　　set @iOutput = 1

　　end

　　else

　　set @ioutput = -1

　　(6)增加新记录

　　程序代码：

　　复制代码代码如下:

　　作者：德仔

　　用途：添加一条新留言

　　日期：2006-4-8

　　CREATE procedure gb_add

　　@gbusername char(50),

　　@gbusermemberid char(50),

　　@gbuseremail char(50),

　　@gbusersubject char(50),

　　@gbusercontent char(1500)

　　insert gb

　　(

　　gbusername,

　　gbusermemberid,

　　gbuseremail,

　　gbsubject,

　　gbcontent

　　)

　　values

　　(

　　@gbusername,

　　@gbusermemberid,

　　@gbuseremail,

　　@gbusersubject,

　　@gbusercontent

　　)

　　(7)统计数据

　　程序代码： [ 复制代码到剪贴板 ]

　　复制代码代码如下:

　　作者：德仔

　　用途：用来统计站上所有的信息总数,包括新闻,产品,公司,等的总数

　　日期：2006-3-23

　　CREATE procedure datacount

　　declare @MemberCount int

　　declare @MemberVip int

　　declare @MemberNorm int

　　declare @MemberUnchkReg int

　　declare @MemberLblRegChk int

　　declare @CompanyCount int

　　declare @CompanyRcmd int

　　declare @SellCount int

　　declare @SellRcmd int

　　declare @SellUnchk int

　　declare @SellChk int

　　declare @CountSupply int

　　declare @SupplyRcmd int

　　declare @SupplyUnchk int

　　declare @SupplyChk int

　　declare @NewsCount int

　　declare @NewsRcmd int

　　declare @NewsClassCount int

　　declare @SupplyClass int

　　declare @SellClass int

　　declare @MsgCount int

　　declare @ProBigclass int

　　declare @proSmallclass int

　　select @MemberCount= count(User_Id)from Users

　　select @MemberVip=count(User_Id)from Users where User_Level =2

　　select @MemberNorm=count(User_Id)from Users where User_Level =1

　　select @MemberUnchkReg=count(user_id) from users where user_IsChecked=0

　　select @MemberLblRegChk=count(user_id) from users where user_IsChecked=1

　　select @CompanyCount=count(COM_id) from Company

　　select @CompanyRcmd=count(COM_id) from Company where COM_IsRcmd=1

　　select @SellCount =count(Sell_Id) from sellinfo

　　select @SellRcmd =count(Sell_Id) from sellinfo where Sell_IsRcmd=1

　　select @SellUnchk =count(Sell_Id) from sellinfo where Sell_Ischecked = 0

　　select @SellChk =count(Sell_Id) from sellinfo where Sell_Ischecked = 1

　　select @CountSupply =count(Supply_Id)from supplyInfo

　　select @SupplyRcmd =count(Supply_Id)from supplyInfo where Supply_Isrcmd=1

　　select @SupplyUnchk =count(Supply_Id)from supplyInfo where Supply_Ischecked=0

　　select @SupplyChk =count(Supply_Id)from supplyInfo where Supply_Ischecked=1

　　select @NewsCount =count(news_id) from news

　　select @NewsRcmd =count(news_id) from news where News_Recommand=1

　　select @NewsClassCount =count(news_id) from news

　　select @proBigclass = count(PdtCat_SortId) from productcats where PdtCat_SortId=0

　　select @proSmallClass = count(PdtCat_SortId)from productcats where PdtCat_SortId<>0

　　select @MsgCount = count(Msg_id) from MSg

　　select

　　MemberCount=@MemberCount,

　　MemberVip=@MemberVip,

　　MemberNorm=@MemberNorm,

　　MemberUnchkReg=@MemberUnchkReg,

　　MemberLblRegChk=@MemberLblRegChk,

　　CompanyCount=@CompanyCount,

　　CompanyRcmd=@CompanyRcmd,

　　SellCount=@SellCount,

　　SellRcmd=@SellRcmd,

　　SellUnchk=@SellUnchk,

　　SellChk=@SellChk,

　　CountSupply =@CountSupply,

　　SupplyRcmd =@SupplyRcmd,

　　SupplyUnchk=@SupplyUnchk,

　　SupplyChk =@SupplyChk,

　　NewsCount=@NewsCount,

　　NewsRcmd=@NewsRcmd,

　　NewsClassCount=@NewsClassCount,

　　probigclass=@probigclass,

　　prosmallclass=@prosmallclass,

　　MsgCount = @MsgCount

　　(8)模糊查询

　　程序代码：

　　复制代码代码如下:

　　作者：德仔

　　用途：用来进行查询sell_info

　　日期：2006-4-10

　　CREATE PROCEDURE sellinfo_search

　　@keyword nvarchar (20)

　　select sell_subject from sellinfo where sell_subject like '%' + @keyword + '%'

　　以上只是自己在学习asp.net中的一点个人经验，因个人水平所限，不免有错，欢迎大家指正，并请多多指教!

　　您可能感兴趣的文章:

您可能感兴趣的文章:

asp.net sql存储过程

相关文章

大家感兴趣的内容

最近更新的内容