创建存储过程，与存储过程调用

1，创建存储过程：

--返回值1:登录成功;2:密码错误;3：用户不存在;4:试错超过当天限制
--登录操作 存储过程
   --判断是否已存在该存储过程，存在则删除
if exists (select * from sysobjects where name='LogonProc' and type='p')
drop proc LogonProc
go
--创建存储过程
create proc LogonProc  @name  nvarchar(50),@password  nvarchar(5),@rejectMsg nvarchar(50) output
as
	declare @logondate date
	declare @result int;
--首先判断该用户是否存在
  if not exists(select * from tb_userinfo where name=@name)  
  begin
  set @rejectMsg='该用户不存在'
  return 3
  end

--获取存储的登录日期,并与当前日期比对
	set @logondate=(select logondate from tb_UserInfo where Name= @name)
	if(@logondate!=CAST(getdate() as date))
		begin
--更新日期并将登录次数更新为0
		update tb_UserInfo set logondate=GETDATE(),Time=0 where Name= @name
		end

--存储日期与当前日期一致时，获取当前登录次数如果登录次数超过3次则拒绝再次登录
	declare @time int
	set @time=(select Time from tb_UserInfo where Name= @name)
--登录次数超过3次禁止再次登录
	if(@time>3)
		begin
		set @rejectMsg='登录错误已超过三次，账号今天已封禁'
		return 4
		end
	else
		begin
		declare @pwd nvarchar(50)
		set @pwd=(select password from tb_UserInfo where Name=@name)
		if(@pwd=@password)
			begin 
			update tb_UserInfo set Time=0 where Name=@name
			set @rejectMsg='登录成功！'
			select * from tb_UserInfo where Name=@name
			return 1
			end
			else
			begin
			set @time=@time+1
			update tb_UserInfo set Time=@time where Name=@name
			set @rejectMsg='第'+CONVERT(nvarchar(10),@time) +'次密码错误，超过三次，今天将不能再登录'
			return 2
			end
			end
			
	go

2，C#调用存储过程

/// <summary>
        /// 账户登录
        /// </summary>
        /// <param name="name">登录名</param>
        /// <param name="pwd">登录密码</param>
        /// <param name="msg">返回的登录消息</param>
        /// <param name="userInfo">返回登录对象</param>
        /// <returns></returns>
        public bool Login(string name,string pwd,out string msg,out UserInfo userInfo)
        {
            bool loginResult = false;
            //'返回值:1:OK;2:密码错误;3:用户不存在;4:登录超过三次禁止登录
            //存储过程：exec @result=LogonProc 'lisi','123',@rejectmsg output
            SqlParameter[] paras = new SqlParameter[]
            {
                new SqlParameter("@result",SqlDbType.Int) { Direction = ParameterDirection.ReturnValue },
                new SqlParameter("@name",name),
                new SqlParameter("@password",pwd),
                new SqlParameter("@rejectMsg",SqlDbType.NVarChar,50) {Direction= ParameterDirection.Output }
            };
            string sqlcmd = "LogonProc";
            DataTable dt = SqlHelper.ExecuteStoredProcedure(sqlcmd, paras);
            int result = Convert.ToInt32(paras[0].Value);
            if (result == 1)
            {
                userInfo = new UserInfo();
                userInfo.Id = dt.Rows[0].Field<int>("Id");
                userInfo.Name = dt.Rows[0].Field<string>("Name");
                userInfo.Nick = dt.Rows[0].Field<string>("Nick");
                userInfo.PassWord = dt.Rows[0].Field<string>("PassWord");
                userInfo.Phone = dt.Rows[0].Field<string>("Phone");
                userInfo.Time = dt.Rows[0].Field<int>("Time");
                userInfo.LogonDate = dt.Rows[0].Field<DateTime>("LogonDate");
                loginResult = true;
                
            }
            else
            {
                loginResult = false;
                userInfo = null;
            }
            msg = paras[3].Value.ToString();
            return loginResult;
        }
  /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="procedure"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static DataTable ExecuteStoredProcedure(string procedure,params SqlParameter[] paras)
        {
            DataTable dt = new DataTable();
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand com = new SqlCommand(procedure, con))
                {
                  
                    com.Parameters.AddRange(paras);
                    com.CommandType = CommandType.StoredProcedure;

                    using (SqlDataAdapter sda = new SqlDataAdapter(com))
                    {

                        sda.Fill(dt);
                    }
                }
            }
            return dt;
        }