--添加会员存储过程
CREATE PROCEDURE Joinmymy
(
@useremail VARCHAR(50),
@userpwd VARCHAR(50),
@username NVARCHAR(50),
@usersex INT,
@userquestion int,
@useranswer NVARCHAR(50),
@userid int output
)
AS
IF EXISTS(SELECT * FROM MymyUsers WHERE UserName=@username)--先判断用户是否存在
BEGIN
SELECT @userid=-1 --如果存在则返回-1
END
ELSE --如果不存在则执行注册插入
BEGIN
INSERT INTO MymyUsers
(
UserEmail,
UserPWD,
UserName,
UserSex,
UserQuestion,
UserAnswer
)
VALUES
(
@useremail,
@userpwd,
@username,
@usersex,
@userquestion,
@useranswer
)
SELECT
@userid =SCOPE_IDENTITY()--获取刚注册分配的用户id
FROM MymyUsers
END
GO
--会员登录存储过程
CREATE PROCEDURE Loginmymy
(
@username NVARCHAR(50),
@userpwd VARCHAR(50),
@userid int output
)
AS
if EXISTS(SELECT * FROM MymyUsers WHERE UserName=@username )--判断用户名为@username的用户是否存在
begin
select @userid=UserId --判断登录信息是否正确,并返回@userid.不正确则返回空值
from MymyUsers
where UserName=@username
and UserPwd=@userpwd
end
else
begin
select @userid=-1 --如果不存在则返回-1
end
GO
--用户修改密码
CREATE PROCEDURE UpdatePwd
(
@userid int,
@oldpwd VARCHAR(50),
@newpwd VARCHAR(50),
@result int output
)
AS
update MymyUsers set UserPwd=@newpwd
where UserId=@userid and UserPwd=@oldpwd
IF @@ROWCOUNT >= 1 --如果影响行数大于或等于1则表示修改成功
SELECT
@result=1
ELSE SELECT
@result=0
GO
--删除文章
CREATE PROCEDURE DeleteArticle
(
@newsid INT,
@result INT OUTPUT
)
AS
DELETE
FROM
Article
WHERE
NewsId=@newsid
IF @@ROWCOUNT >= 1
SELECT
@result=1
ELSE SELECT
@result=0
GO
//调用存储过程的方法如下:
/// <summary>
/// 用户注册提交方法,返回用户ID
/// </summary>
public int AddUser(string useremail, string pwd, string username)
{
SqlConnection myconnection = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]);
SqlCommand mycommand = new SqlCommand("USERADD", myconnection); //sqlcommand指定存储过程名称
mycommand.CommandType = CommandType.StoredProcedure; //sqlcommand指定的类型
//给存储过程添加参数
//用户EMAIL
SqlParameter paramuseremail = new SqlParameter("@useremail", SqlDbType.NVarChar, 50);
paramuseremail.Value = useremail;
mycommand.Parameters.Add(paramuseremail);
string userpwd = OtherDB.PwdEncrypt(pwd + useremail);
//用户密码
SqlParameter paramuserpwd = new SqlParameter("@userpwd", SqlDbType.NVarChar, 50);
paramuserpwd.Value = userpwd;
mycommand.Parameters.Add(paramuserpwd);
//用户妮称
SqlParameter paramusername = new SqlParameter("@username", SqlDbType.NVarChar, 50);
paramusername.Value = username;
mycommand.Parameters.Add(paramusername);
//参数输出,添加成功后返回用户ID
SqlParameter pramuserid = new SqlParameter("@userid", SqlDbType.Int, 4);
pramuserid.Direction = ParameterDirection.Output; //指定为输出类型
mycommand.Parameters.Add(pramuserid);
//打开连接,执行操作
myconnection.Open();
IAsyncResult result = mycommand.BeginExecuteNonQuery();
mycommand.EndExecuteNonQuery(result);
myconnection.Close();
//活动用户妮称(通过存储过程的输出参数)
return (pramuserid.Value == null ? 0 : Int32.Parse(pramuserid.Value.ToString()));
}
//返回一个sqlDatareader
创建TeamList存储过程
create procedure TeamList
(
@userid int
)
as
select * from Team where Uid = @userid
go
public SqlDataReader DrTeamList(string userid)
{
SqlConnection myconnection = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]);
SqlCommand mycommand = new SqlCommand("TeamList", myconnection);
//指定操作类型为存储过程
mycommand.CommandType = CommandType.StoredProcedure;
//添加Id参数到存储过程
SqlParameter pramuserid = new SqlParameter("@userid", SqlDbType.Int, 4);
pramuserid.Value = Int32.Parse(userid);
mycommand.Parameters.Add(pramuserid);
//打开连接并执行操作
myconnection.Open();
SqlDataReader result = mycommand.ExecuteReader(CommandBehavior.CloseConnection);
//返回结果
return result;
}