ASP.Net学习之常用SQL存储过程

转载 2007年10月09日 23:23:00

在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 ‘存储过程名’
Go

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

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

a.     没有使用参数的存储过程
/*
用途:查询所有的公司名录
德仔创建于2006-3-29
*/
create procedure com_select
as
select * from Company
GO

b.     有参数的存储过程
/*
选择对应的admin
创建者:德仔
创建日期:2006-4-20
*/
create procedure admin_select
@adminusername char(50),
@adminpassword char(50)
as
select * from superadmin where
[Admin_Name]=@adminusername and [Admin_Password]=@adminpassword
GO


c.     在该存储过程中使用了OUTPUT 保留字有返回值的存储过程
create procedure salequa
@stor_id char 4 ,
@sum smallint output
as
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
go


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


我们下一次再讲讲存储过程在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
Go
就可以了
如果存储过程中包含有返回值的存储过程,那我们就必须指定参数值.看下面这个例子
此例摘自《SQLserver程序员指南》一书
create procedure salequa @stor_id char 4 ,@sum smallint output
as
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
go

要执行此存储过程,则我们要指定参数@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
as
select * from sellinfo
GO





(2)     删除指定的ID记录


程序代码: [ 复制代码到剪贴板 ]
/*
作者:德仔
用途:删除sellinfo里由输入参数@sell_id指定的ID记录
日期:2006-3-23
*/
CREATE PROCEDURE sellinfo_delete
@sell_id bigint
as
delete from [sellinfo]
where
sell_id=@sell_id
GO




(3)更新所对应的记录


程序代码: [ 复制代码到剪贴板 ]
/*
作者:德仔
用途:修改相对应的小类名
日期:2006-4-5
*/
create procedure prosmallclass_update_id
@smallid int,
@smallname char(50)
as
update [ProductCats]
set
PdtCat_Name = @smallname
where
PdtCat_id =@smallid
GO




(4)验证登陆


程序代码: [ 复制代码到剪贴板 ]
/*
作者:德仔
用途:通过得到的@user_name @user_password验证登陆
日期:2006-3-21
*/
CREATE procedure user_login
@user_name varchar(50),
@user_password varchar(50)
as
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
GO




(5)密码修改


程序代码: [ 复制代码到剪贴板 ]
/*
作者:德仔
用途:先查到user的密码,再修改新密码
日期:2006-3-23
*/
create procedure user_pwd
@user_name varchar(30),
@user_oldpwd varchar(30),
@user_newpwd varchar(30),
@iOutput int output
as
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
GO




(6)增加新记录


程序代码: [ 复制代码到剪贴板 ]
/*
作者:德仔
用途:添加一条新留言
日期:2006-4-8
*/
CREATE procedure gb_add
@gbusername char(50),
@gbusermemberid char(50),
@gbuseremail char(50),
@gbusersubject char(50),
@gbusercontent char(1500)
as
insert gb
(
gbusername,
gbusermemberid,
gbuseremail,
gbsubject,
gbcontent

)
values
(
@gbusername,
@gbusermemberid,
@gbuseremail,
@gbusersubject,
@gbusercontent
)
GO



(7)统计数据


程序代码: [ 复制代码到剪贴板 ]
/*
作者:德仔
用途:用来统计站上所有的信息总数,包括新闻,产品,公司,等的总数
日期:2006-3-23
*/

CREATE procedure datacount
as
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
GO




(8)模糊查询


程序代码: [ 复制代码到剪贴板 ]
/*
作者:德仔
用途:用来进行查询sell_info
日期:2006-4-10
*/
CREATE PROCEDURE sellinfo_search
@keyword nvarchar (20)
AS
select sell_subject from sellinfo where sell_subject like '%' + @keyword + '%'
GO




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

SQL存储过程.

  • 2012年03月01日 08:59
  • 642KB
  • 下载

ASP.Net学习之常用SQL存储过程

在ASP.Net项目中使用存储过程,首先可以提高数据库的安全性,其次可以提高运行SQL代码运行的速度,在大型项目中一般是必不可少的。Visual Studio.Net为SQL的存储过程提供了强大的支持...
  • RemanticMemory
  • RemanticMemory
  • 2007-01-02 23:43:00
  • 676

PL/SQL 存储过程学习

PL/SQL 存储过程学习基本语法 语法: CREATE OR REPLACE PROCEDURE 存储过程名 IS BEGIN NULL; END; 解释: CREATE OR REPLA...
  • u012414590
  • u012414590
  • 2017-04-17 19:45:49
  • 827

ASP.Net学习之常用SQL存储过程(2)

http://blog.csdn.net/qdzx2008/articles/790881.aspx
  • sqlmlm
  • sqlmlm
  • 2009-08-03 10:43:00
  • 221

asp.net利用存储过程分页代码

  • 2010年04月19日 15:05
  • 51KB
  • 下载

sql 存储过程 入门级 练习

sql 存储过程 入门级
  • samuel_zhang_
  • samuel_zhang_
  • 2015-08-20 10:16:38
  • 2971

SQL存储过程学习asp.net ,c#

  • 2009年07月20日 17:46
  • 6KB
  • 下载

ASP.Net中常用SQL存储过程

 1.    在SQL中执行执行已创建的存储过程使用EXECUTE 命令其语法如下[EXECUTE]{[@return_statur=]{procedure_name[;number] | @proc...
  • sakurain
  • sakurain
  • 2006-11-15 10:56:00
  • 959

技术笔记5_Asp.net:如何在vs环境下调试sqlserver中的存储过程

如果存储过程发生了异常,而返回的异常的消息又不能明确显示错误的原因,这时需要能够直接调试存储过程,最好能在vs中像调试普通代码一样,(默认情形下是无法的进行的),需要通过下面方法打开: 1.在wep...
  • polaris11767
  • polaris11767
  • 2014-12-06 20:42:01
  • 750

asp.net存储过程操作数据库实例

  • 2008年10月29日 11:02
  • 98KB
  • 下载
收藏助手
不良信息举报
您举报文章:ASP.Net学习之常用SQL存储过程
举报原因:
原因补充:

(最多只允许输入30个字)