asp.net sql存储过程

 
/* 
用途：查询所有的公司名录 
德仔创建于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 

 
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; 
} 
} 

 
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(); 
} 

 
/* 
作者：德仔 
用途：查询sellinfo里所有的记录 
日期：2006-3-23 
*/ 
create procedure sellinfo_select 
as 
select * from sellinfo 
GO 

 
/* 
作者：德仔 
用途：删除sellinfo里由输入参数@sell_id指定的ID记录 
日期：2006-3-23 
*/ 
CREATE PROCEDURE sellinfo_delete 
@sell_id bigint 
as 
delete from [sellinfo] 
where 
sell_id=@sell_id 
GO 

 
/* 
作者：德仔 
用途：修改相对应的小类名 
日期：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 

 
/* 
作者：德仔 
用途：通过得到的@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 

 
/* 
作者：德仔 
用途：先查到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 

 
/* 
作者：德仔 
用途：添加一条新留言 
日期：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 

 
/* 
作者：德仔 
用途：用来统计站上所有的信息总数,包括新闻,产品,公司,等的总数 
日期：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 

 
/* 
作者：德仔 
用途：用来进行查询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存储过程就介绍到这了。有一位青年去拜访一位很知名的科学家，向他诉说自己是如何刻苦努力，却还没有什么建树，科学家拿出一只放大镜，一张白纸，放在阳光下，纸上出现一个耀眼的光斑，片刻间就燃烧起来，年轻人茅塞顿开，欣然离去。更多相关asp.net sql存储过程内容请查看相关栏目，小编编辑不易，再次感谢大家的支持！