usersdal.cs

一个简单的网上书店

using System;
using System.Collections.Generic;
using System.Text;
using Model;
using BLL;
using BLL.interfaces;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
   public class UsersDal:IUsers
    {
       #region IUsers 成员

       public bool addUser(Users user)
       {
           string sqlStr = "insert into users (loginid,loginpwd,[name],address,phone,mail) values ";
           sqlStr += "(@loginId,@loginPwd,@name,@address,@phone,@mail)";
           SqlParameter[] p = new SqlParameter[] 
            {
                new SqlParameter("@loginId",user.LoginId),
                new SqlParameter("@loginPwd",user.LoginPwd),
                new SqlParameter("@name",user.Name),
                new SqlParameter("@address",user.Address),
                new SqlParameter("@phone",user.Phone),
                new SqlParameter("@mail",user.Mail)
            };
           return DBHelp.Rework1(sqlStr, p) > 0 ? true : false;
       }

       public bool updateUser(Users user)
       {
           string sqlStr = "update users set userroleid=@role,userstateid=@state where id=@id";
           SqlParameter[] p = new SqlParameter[] 
            {
                new SqlParameter("@role",user.UserRoleId),
                new SqlParameter("@state",user.UserStateId),
                new SqlParameter("@id",user.Id)
            };
           return DBHelp.Rework1(sqlStr, p) > 0 ? true : false;
       }

       public bool delUser(string id)
       {
           string sqlStr = "delete from users where id=@id";
         
           SqlParameter[] p = new SqlParameter[] 
            {
                new SqlParameter("@Id",id)
            };
           return DBHelp.Rework1(sqlStr, p) > 0 ? true : false;
       }


       public string[] login(string loginid, string pwd)
       {
           string sqlStr = "select u.id,u.[name] ,ur.[name] as role ";
           sqlStr += "from users as u,userroles as ur,userStates as us  ";
           sqlStr += "where u.userroleid=ur.id and loginid=@loginId and loginpwd=@loginPwd";
           SqlParameter[] p = new SqlParameter[]
            {
                new SqlParameter("@loginId",loginid),
                new SqlParameter("@loginPwd",pwd)
            };
           string[] str=null;
           using (SqlDataReader read = DBHelp.Selparam(sqlStr, p))
           {
               if (read.Read())
               {
                   str = new string[] { read["id"].ToString(), read["name"].ToString(), read["role"].ToString() };   
               }
           }
           return str;
       }

       public int count()
       {
           int count = 0;
           string sqlStr = "select count(*) from users";
           count = Count.getCount(sqlStr);
           return count;
       }

       public List<Users> selUser(Page pager,int role)
       {
           string sqlStr = "select top " + pager.getPageSize() + " u.id,loginid,loginpwd,u.[name],address,phone,mail,userroleid,userstateid,ur.[name] as role,us.[name] as state  ";
           sqlStr += "from users as u,userroles as ur,userStates as us  ";
           sqlStr += "where u.userroleid=ur.id and u.userstateid=us.id";
           if (role != 0)
           {
               sqlStr += " and u.userroleid=" + role;
           }
           sqlStr += " and u.id not in (select top " + (pager.getCurrentPage() - 1) * pager.getPageSize() + " id from users ";
           if (role != 0)
           {
               sqlStr += " where userroleid=" + role;
           }
           sqlStr +=")";
           List<Users> list = new List<Users>();
           using (SqlDataReader read = DBHelp.Sel(sqlStr))
           {
               while (read.Read())
               {
                   Users user = new Users();
                   user.Id =int.Parse(read["id"].ToString());
                   user.LoginId = read["loginid"].ToString();
                   user.LoginPwd = read["loginpwd"].ToString();
                   user.Name = read["name"].ToString();
                   user.Address = read["address"].ToString();
                   user.Phone = read["phone"].ToString();
                   user.Mail = read["mail"].ToString();
                   user.UserRoleId = int.Parse(read["userroleid"].ToString());
                   user.UserStateId = int.Parse(read["userstateid"].ToString());
                   user.Role.Name = read["role"].ToString();
                   user.State.Name = read["state"].ToString();
                   list.Add(user);
               }
               return list;
           }
       }

       public bool updatePwd(string pwd,string id)
       {
           string sqlStr = "update users set loginPwd=@pwd where loginId=@id";
           SqlParameter[] p = new SqlParameter[] 
            {
                new SqlParameter("@pwd",pwd),
                new SqlParameter("@id",id)
            };
           return DBHelp.Rework1(sqlStr, p) > 0 ? true : false;
       }

       public bool updateMy(Users user)
       {
           string sqlStr = "update users set address=@address,phone=@phone where id=@id";
           SqlParameter[] p = new SqlParameter[] 
            {
                new SqlParameter("@address",user.Address),
                new SqlParameter("@phone",user.Phone),
                new SqlParameter("@id",user.Id)
            };
           return DBHelp.Rework1(sqlStr, p) > 0 ? true : false;
       }


       public List<Users> selOnly(int id)
       {
           string sqlStr = "select loginid,loginpwd,u.[name],address,phone,mail,userroleid,userstateid,ur.[name] as role,us.[name] as state ";
           sqlStr += "from users as u,userroles as ur,userStates as us  ";
           sqlStr += "where u.userroleid=ur.id and u.userstateid=us.id and u.id =@id ";
           SqlParameter[] p = new SqlParameter[] 
            {
                new SqlParameter("@id",id)
            };
           List<Users> list = new List<Users>();
           using (SqlDataReader read = DBHelp.Selparam(sqlStr,p))
           {
               while (read.Read())
               {
                   Users user = new Users();
                   user.LoginId = read["loginid"].ToString();
                   user.LoginPwd = read["loginpwd"].ToString();
                   user.Name = read["name"].ToString();
                   user.Address = read["address"].ToString();
                   user.Phone = read["phone"].ToString();
                   user.Mail = read["mail"].ToString();
                   user.UserRoleId = int.Parse(read["userroleid"].ToString());
                   user.UserStateId = int.Parse(read["userstateid"].ToString());
                   user.Role.Name = read["role"].ToString();
                   user.State.Name = read["state"].ToString();
                   list.Add(user);
               }
               return list;
           }
       }

       #endregion

       #region IUsers 成员


       public bool updateUserList(List<string> lst, int mothed)
       {
           string id = "";
           for (int i = 0; i < lst.Count; i++)
           {
               id += lst[i]+",";
           }
           string sqlStr = "update users set userstateid=@state where id in ("+id.Substring(0,id.Length-1)+")";
           SqlParameter[] p = new SqlParameter[] 
            {
                new SqlParameter("@state",mothed)
            };
           return DBHelp.Rework1(sqlStr, p) > 0 ? true : false;
       }

       #endregion
   }
}