[SqlServer]用户注册、登录、修改密码存储过程实例

?--添加会员存储过程
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),
???? @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",50);
??????????? paramuserpwd.Value = userpwd;
??????????? mycommand.Parameters.Add(paramuserpwd);
?
??????????? //用户妮称
??????????? SqlParameter paramusername = new SqlParameter("@username",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",4); ??????????? pramuserid.Value = Int32.Parse(userid); ??????????? mycommand.Parameters.Add(pramuserid); ? ??????????? //打开连接并执行操作 ??????????? myconnection.Open(); ??????????? SqlDataReader result = mycommand.ExecuteReader(CommandBehavior.CloseConnection); ??????????? //返回结果 ??????????? return result; ??????? }

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!