---
title: "cunchuguocheng"
author: "mywebcode"
date: "2012-11-10T19:53:00Z"
tags: []
slug: "cunchuguocheng"
draft: false
lastmod: "2013-05-08T10:26:29Z"
id: 1862866
url: "https://mywebcode.iteye.com/blog/1862866"
---

--事务  
ACID特性：原子性、一致性、隔离性、持久性  
\--存储过程  
1、系统存储过程  
2、自定义存储过程  
极其类似与C#中的方法。  
1.没有参数没有返回值的存储过程  
create proc usp\_Helloworld  
as  
begin  
print 'Hello world'  
end

exec usp\_Helloworld  
2、带参数的存储过程  
\--创建一个存储过程完成计算两个数的和  
create proc usp\_AddTwo  
@number1 int,--参数  
@number2 int  
as  
begin  
print @number1+@number2  
end  
\--declare @a int=20  
\--declare @b int=40

exec usp\_AddTwo 20,40  
3、存储过程的参数过程  
exec usp\_AddTwo  
alter proc usp\_AddTwo  
@number1 int=200,  
@number2 int=1000  
as  
begin  
print @number1+@number2  
end  
exec usp\_AddTwo 20,40 --结果60  
exec usp\_AddTwo --1200

exec usp\_AddTwo  
alter proc usp\_AddTwo  
@number1 int,  
@number2 int=1000  
as  
begin  
print @number1+@number2  
end  
exec usp\_AddTwo 200 --结果1200  
\--显性赋值  
exec usp\_AddTwo  
alter proc usp\_AddTwo  
@number1 int=200,  
@number2 int  
as  
begin  
print @number1+@number2  
end  
exec usp\_AddTwo @number2=200 --结果1200

创建一个存储过程完成计算两个数的和，并返回两个数的和  
存储过程返回值，可以使用“output参数”！！类似于C#中的参数  
alter proc usp\_AddTwo  
@number1 int=200,  
@number2 int=1000，  
@sumTwo int output  
as  
begin  
print @sumTwo=@number1+@number2  
end

exec usp\_AddTwo  
declare @var int  
exect usp\_AddTwo @number2=500,@number1=300,@sumTwo=@var output print @var  
\--存储过程的output参数是要得到返回值得，这里需要传进去一个同类型的变量来接收返回值

--如果english不及格的人超过半数，则给每个人增加2分，循环加，直到不及格的人数少于一半  
use Shool  
select * from Sore  
updata Score set english=english-10 where english between 60 and 70  
create proc usp\_promoteGrade  
@passline int  
as  
begin  
计算总人数  
declare @count int=(select count(\*) from Score)  
计算英语不及格人数  
declare @loster int=(select count(\*)from Score where english&lt;@passline)  
declare @harfcount int=floor(@count2/2)  
while @harfcount&lt;@loster  
begin  
update Score set english=english+2 where english&lt;@passline  
set @loster=(select count(\*)from Score where english&lt;@passline)  
end  
end  
exec usp\_promoteGrade 60  
\----------------------------------------------  
1.在ADO中使用存储过程  
use UserDB1  
select * from T\_Users  
create proc usp\_login  
@uname varchar(50),  
@password varchar(50),  
@result bit output----布尔类型的参数，是用来返回值的，设置存储用户登录是否成功1---true,0------false  
as  
begin  
set @count=( select count(\*) from T\_Users where [FuserName=@uname](mailto:FuserName=@uname) and [Fpassword=@password](mailto:Fpassword=@password))  
if @count&gt;0  
begin  
set @result=1  
end  
else  
begin  
set @result=0  
end  
end  
declare @r bit  
exec usp\_Login @uname='admin321232','111',@r output  
print @r  
print @@servername

btnClick  
{ string username=txtUserName.Text.Trin();  
string password=txtPassword.Text.Trin();

使用ADO连接数据库  
Connection--SqlConnection--连接字符串  
Command--SqlCommand  
SqlParaneter  
SqlDataReader  
......

string connstr="Data Source=PC-20120907SDQU;Initial Catalog=UserDbl;User ID=sa; Password=admin"  
using(SqlConnection conn=new SqlConnection();)  
{  
conn.Open();  
using(SqlCommand cmd= conn.CreateCommand();)  
{string sql="insert into T\_Users(FuseName,Fpassword) values('王妃','555')"  
cmd.CommandText=sql;  
int r=cmd.ExecuteNonQuery();  
Label1.Text=r.ToString();  
\-------------  
string sp\_name="usp\_Login";  
//执行sql语句和执行存储过程最大的区别！需要设置一个commandType  
cmd.CommandText=CommandType.StoredProcedure;  
cmd.CommandText=sp\_name;  
SqlParameter prm1=new SqlParameter("@uname",username);  
SqlParameter prm2=new SqlParameter("@password",password);  
SqlParameter prm3=new SqlParameter("@result",System.Data.SqlDbType.Bit);

cmd.Parameters.Add(prm1);  
cmd.Parameters.Add(prm2);  
cmd.Parameters.Add(prm3);  
prm3.Direction=ParameterDirection.Output;

//int r=cmd.ExecuteNonQuery();  
//object obj=cmd.ExecuteNonQuery();  
bool b=Converrt.ToBoolean(prm3.value);  
if(b)  
{  
Label2.Text="登录成功";  
}  
else  
{  
Label2.Text="登录失败";  
}  
}  
}

}