[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),
     @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;
        }