DAL


using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using MyOffice.Models;

namespace MyOffice.DAL
{
    /*
     *用户信息数据访问类
     * 创建人:zwn
     * 创建时间:2009-09-21
     * 说明:用于对用户表中数据进行操作
     * 修改人:tu
     * 修改时间:2009-09-22
     * 修改说明:
     * 1.在读取用户表中RealName,ImageUrl时判别是否未空
     * 2.各方法添加注释
     */
    public static partial class UserInfoService
    {
        private static string publicSql = " and UserState=(select UserStateId from UserState where UserStateName!='被屏蔽') and RoleId!=(select RoleId from RoleInfo where RoleName='超级管理员') ";
        /// <summary>
        /// 根据用户ID修改用户密码
        /// </summary>
        /// <param name="userInfo"></param>
        /// <returns></returns>
        public static bool ModifyPasswordByUserId(UserInfo userInfo)
        {
            string sql =
               "UPDATE UserInfo " +
               "SET " + "Password = @Password " +
               "WHERE UserId = @UserId";

            try
            {
                SqlParameter[] para = new SqlParameter[]
    {                   
     new SqlParameter("@Password", userInfo.Password),
                     new SqlParameter("@UserId",userInfo.UserId)
    };

                int result = DBHelper.ExecuteNonQuery(DBHelper.connString, CommandType.Text, sql, para);
                if (result > 0)
                    return true;
                return false;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                throw e;
            }
        }
        /// <summary>
        /// 根据查询参数获取用户信息
        /// </summary>
        /// <param name="condition">查询条件</param>
        /// <param name="conditionValue">对应查询条件的值</param>
        /// <returns></returns>
        public static IList<UserInfo> GetUserInfoByParam(string condition, string conditionValue)
        {
            string sql = "select * from [UserInfo] where 1=1 ";
            if (!conditionValue.Equals("") && conditionValue.Length > 0)
            {
                if (condition.Equals("DepartId"))//当条件为“部门名称”时
                {
                    sql += "and DepartId in (select [DepartId] from dbo.DepartInfo where [DepartName] like @conditionValue)";
                }
                else if (condition.Equals("RoleId"))//当条件为“角色”时
                {
                    sql += "and RoleId in (select [RoleId] from dbo.RoleInfo where [RoleName] like @conditionValue)";
                }
                else if (condition.Equals("UserState"))//当条件为“状态”时
                {
                    sql += "and UserState in (select [UserStateId] from dbo.UserState where [UserStateName] like @conditionValue)";
                }
                else if (condition.Equals("Gender"))//当条件为“性别”时
                {
                    int Gender = 0;
                    if (conditionValue.Equals("女"))
                        Gender = 1;
                    else if (conditionValue.Equals("男"))
                        Gender = 0;
                    else
                        Gender = -1;

                    sql += " and " + condition + " =" + Gender;
                }
                else
                {
                    sql += " and " + condition + " like @conditionValue";
                }
            }
            return GetUserInfosBySql(sql + publicSql + " order by userId desc ", new SqlParameter("@conditionValue", "%" + conditionValue + "%"));
        }

        /// <summary>
        /// 检查用户名是否存在数据库
        /// </summary>
        /// <param name="userName">用户名</param>
        /// <returns></returns>
        public static UserInfo CheckUserName(string userName)
        {
            IList<UserInfo> list = GetUserInfosBySql("select * from [UserInfo] where [UserName]=@UserName", new SqlParameter("@UserName", userName));
            if (list.Count > 0)
                return list[0];
            return null;

        }
        /// <summary>
        /// 根据部门ID获取所有用户信息
        ///  </summary>
        /// <param name="departId">部门ID</param>
        /// <returns></returns>
        public static IList<UserInfo> GetAllUserInfoByDepartId(int departId)
        {
            string sql = "select * from [UserInfo] where DepartId=@DepartId";
            return GetUserInfosBySql(sql, new SqlParameter("@DepartId", departId));
        }

        /// <summary>
        /// 根据部门ID获取所有用户信息,除了自己
        ///  </summary>
        /// <param name="departId">部门ID</param>
        /// <returns></returns>
        public static IList<UserInfo> GetAllUserInfoByDepartId(int departId, int UserId)
        {
            string sql = "select * from [UserInfo] where DepartId=@DepartId and UserId != @UserId";
            SqlParameter[] para = new SqlParameter[] { new SqlParameter("@DepartId", departId), new SqlParameter("@UserId", UserId) };
            return GetUserInfosBySql(sql, para);
        }
        /// <summary>
        /// 添加用户信息
        /// </summary>
        /// <param name="userInfo">用户对象</param>
        /// <returns>添加的用户信息</returns>
        public static UserInfo AddUserInfo(UserInfo userInfo)
        {
            string sql =
                "INSERT into UserInfo " +
                "VALUES (@UserName, @Password, @DepartId, @Gender, @RoleId, @UserState,@RealName,@ImageURL)";

            sql += " SELECT @@IDENTITY";

            try
            {
                SqlParameter[] para = new SqlParameter[]
    {
     new SqlParameter("@DepartId", userInfo.Depart.DepartId), //FK
     new SqlParameter("@RoleId", userInfo.Role.RoleId), //FK
     new SqlParameter("@UserState", userInfo.UserState.UserStateId), //FK
     new SqlParameter("@UserName", userInfo.UserName),
     new SqlParameter("@Password", userInfo.Password),
     new SqlParameter("@Gender", userInfo.Gender),
                    new SqlParameter("@RealName",userInfo.RealName),
                    new SqlParameter("@ImageURL",userInfo.ImageURL)
    };

                int newId = Convert.ToInt32(DBHelper.ExecuteScalar(DBHelper.connString, CommandType.Text, sql, para));
                return GetUserInfoByUserId(newId);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                throw e;
            }
        }
        /// <summary>
        /// 删除用户信息
        /// </summary>
        /// <param name="userInfo"></param>
        /// <returns></returns>
        public static bool DeleteUserInfo(UserInfo userInfo)
        {
            return DeleteUserInfoByUserId(userInfo.UserId);
        }
        /// <summary>
        /// 根据用户ID删除用户信息
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public static bool DeleteUserInfoByUserId(int userId)
        {
            //string sql = "DELETE UserInfo WHERE UserId = @UserId";
            string sql = "update UserInfo set UserState=(select UserStateId from UserState where UserStateName='被屏蔽') where userId=@UserId";
            int result = DBHelper.ExecuteNonQuery(DBHelper.connString, CommandType.Text, sql, new SqlParameter("@UserId", userId));
            if (result > 0)
                return true;
            return false;
        }
        /// <summary>
        /// 修改用户信息
        /// </summary>
        /// <param name="userInfo"></param>
        /// <returns></returns>
        public static bool ModifyUserInfo(UserInfo userInfo)
        {
            string sql =
                "UPDATE UserInfo " +
                "SET " +
                    "DepartId = @DepartId, " + //FK
                    "RoleId = @RoleId, " + //FK
                    "UserState = @UserState, " + //FK
                    "UserName = @UserName, " +
                    "Gender = @Gender, " +
                    "RealName=@RealName, " +
                    "ImageURL=@ImageURL " +
                "WHERE UserId = @UserId";


            try
            {
                SqlParameter[] para = new SqlParameter[]
    {                   
     new SqlParameter("@DepartId", userInfo.Depart.DepartId), //FK
     new SqlParameter("@RoleId", userInfo.Role.RoleId), //FK
     new SqlParameter("@UserState", userInfo.UserState.UserStateId), //FK
     new SqlParameter("@UserName", userInfo.UserName),
     new SqlParameter("@Gender", userInfo.Gender),
                    new SqlParameter("@RealName",userInfo.RealName),
                    new SqlParameter("@ImageURL",userInfo.ImageURL),
                     new SqlParameter("@UserId",userInfo.UserId)
    };

                int result = DBHelper.ExecuteNonQuery(DBHelper.connString, CommandType.Text, sql, para);
                if (result > 0)
                    return true;
                else
                    return false;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                throw e;
            }

        }
        /// <summary>
        /// 得到全部用户信息信息
        /// </summary>
        /// <returns></returns>
        public static IList<UserInfo> GetAllUserInfos()
        {
            string sqlAll = "SELECT * FROM UserInfo where 1=1" + publicSql + " order by userId desc ";//除超级管理员,全部查询
            return GetUserInfosBySql(sqlAll);
        }

        /// <summary>
        /// 根据用户ID得到用户信息
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public static UserInfo GetUserInfoByUserId(int userId)
        {
            string sql = "SELECT * FROM UserInfo WHERE UserId = @UserId";

            int departId = 0;
            int roleId;
            int userState;

            try
            {
                SqlDataReader reader = DBHelper.ExecuteReader(DBHelper.connString, CommandType.Text, sql, new SqlParameter("@UserId", userId));
                if (reader.Read())
                {
                    UserInfo userInfo = new UserInfo();

                    userInfo.UserId = (int)reader["UserId"];
                    userInfo.UserName = (string)reader["UserName"];
                    userInfo.Password = (string)reader["Password"];
                    userInfo.Gender = (int)reader["Gender"];
                    if (reader["RealName"].GetType().FullName != "System.DBNull")
                        userInfo.RealName = (string)reader["RealName"];
                    if (reader["ImageURL"].GetType().FullName != "System.DBNull")
                        userInfo.ImageURL = (string)reader["ImageURL"];
                    if (reader["DepartId"].GetType().FullName != "System.DBNull")
                        departId = Convert.ToInt32(reader["DepartId"]);
                    roleId = (int)reader["RoleId"]; //FK
                    userState = (int)reader["UserState"]; //FK

                    reader.Close();
                    if (departId != 0)
                        userInfo.Depart = DepartInfoService.GetDepartInfoByDepartId(departId);
                    userInfo.Role = RoleInfoService.GetRoleInfoByRoleId(roleId);
                    userInfo.UserState = UserStateService.GetUserStateByUserStateId(userState);

                    return userInfo;
                }
                else
                {
                    reader.Close();
                    return null;
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                throw e;
            }
        }


        /// <summary>
        /// 使用sql语句对用户表进行操作(集合)
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="values"></param>
        /// <returns></returns>
        private static IList<UserInfo> GetUserInfosBySql(string safeSql)
        {
            List<UserInfo> list = new List<UserInfo>();

            try
            {
                DataSet ds = new DataSet();
                DBHelper.FillDataset(DBHelper.connString, CommandType.Text, safeSql, ds, new string[] { "UserInfo" });
                DataTable table = ds.Tables[0];

                foreach (DataRow row in table.Rows)
                {
                    UserInfo userInfo = new UserInfo();

                    userInfo.UserId = Convert.ToInt32(row["UserId"]);
                    userInfo.UserName = (string)row["UserName"];
                    userInfo.Password = (string)row["Password"];
                    userInfo.Gender = (int)row["Gender"];
                    if (row["RealName"].GetType().FullName != "System.DBNull")
                        userInfo.RealName = (string)row["RealName"];
                    if (row["ImageURL"].GetType().FullName != "System.DBNull")
                        userInfo.ImageURL = (string)row["ImageURL"];
                    if (row["DepartId"].GetType().FullName != "System.DBNull")
                        userInfo.Depart = DepartInfoService.GetDepartInfoByDepartId(Convert.ToInt32(row["DepartId"])); //FK
                    userInfo.Role = RoleInfoService.GetRoleInfoByRoleId(Convert.ToInt32(row["RoleId"])); //FK
                    userInfo.UserState = UserStateService.GetUserStateByUserStateId(Convert.ToInt32(row["UserState"])); //FK

                    list.Add(userInfo);
                }

                return list;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                throw e;
            }

        }
        /// <summary>
        /// 根据角色id查询用户
        /// </summary>
        /// <param name="roleId">角色id</param>
        /// <returns>用户集合</returns>
        public static IList<UserInfo> GetUserByRoleId(int roleId)
        {
            IList<UserInfo> list = new List<UserInfo>();
            string sql = "select * from userInfo where roleId=@roleId";
            try
            {
                DataSet ds = new DataSet();
                DBHelper.FillDataset(DBHelper.connString, CommandType.Text, sql + publicSql, ds, new string[] { "UserInfo" }, new SqlParameter[] { new SqlParameter("@roleId", roleId) });
                DataTable table = ds.Tables[0];

                foreach (DataRow row in table.Rows)
                {
                    UserInfo userInfo = new UserInfo();

                    userInfo.UserId = Convert.ToInt32(row["UserId"]);
                    userInfo.UserName = (string)row["UserName"];
                    userInfo.Password = (string)row["Password"];
                    userInfo.Gender = (int)row["Gender"];
                    if (row["RealName"].GetType().FullName != "System.DBNull")
                        userInfo.RealName = (string)row["RealName"];
                    if (row["ImageURL"].GetType().FullName != "System.DBNull")
                        userInfo.ImageURL = (string)row["ImageURL"];
                    if (row["DepartId"].GetType().FullName != "System.DBNull")
                        userInfo.Depart = DepartInfoService.GetDepartInfoByDepartId((int)row["DepartId"]); //FK
                    userInfo.Role = RoleInfoService.GetRoleInfoByRoleId((int)row["RoleId"]); //FK
                    userInfo.UserState = UserStateService.GetUserStateByUserStateId((int)row["UserState"]); //FK

                    list.Add(userInfo);
                }
                return list;
            }
            catch (Exception)
            {

                throw;
            }

        }
        /// <summary>
        /// 检查用户是否存在
        /// </summary>
        /// <param name="userInfo">用户信息</param>
        /// <returns></returns>
        public static UserInfo CheckUserInfo(UserInfo userInfo)
        {
            string sql = "select * from userInfo where userName=@name and password =@pass and UserState=(select UserStateId from UserState where UserStateName!='被屏蔽')";
            SqlParameter[] paramItem = new SqlParameter[]{
              new SqlParameter("@name",userInfo.UserName),
              new SqlParameter("@pass",userInfo.Password)
            };
            int departId = 0;
            int roleId;
            int userState;
            SqlDataReader reader = DBHelper.ExecuteReader(DBHelper.connString, CommandType.Text, sql, paramItem);
            if (reader.Read())
            {
                userInfo.UserId = (int)reader["UserId"];
                userInfo.UserName = (string)reader["UserName"];
                userInfo.Password = (string)reader["Password"];
                userInfo.Gender = (int)reader["Gender"];
                if (reader["RealName"].GetType().FullName != "System.DBNull")
                    userInfo.RealName = (string)reader["RealName"];
                if (reader["ImageURL"].GetType().FullName != "System.DBNull")
                    userInfo.ImageURL = (string)reader["ImageURL"];
                if (reader["DepartId"].GetType().FullName != "System.DBNull")
                    departId = Convert.ToInt32(reader["DepartId"]);
                roleId = (int)reader["RoleId"]; //FK
                userState = (int)reader["UserState"]; //FK

                reader.Close();
                if (departId != 0)
                    userInfo.Depart = DepartInfoService.GetDepartInfoByDepartId(departId);
                userInfo.Role = RoleInfoService.GetRoleInfoByRoleId(roleId);
                userInfo.UserState = UserStateService.GetUserStateByUserStateId(userState);
            }
            else
                return null;
            return userInfo;

        }
        /// <summary>
        /// 使用sql语句对用户表进行操作
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="values"></param>
        /// <returns></returns>
        private static IList<UserInfo> GetUserInfosBySql(string sql, params SqlParameter[] values)
        {
            List<UserInfo> list = new List<UserInfo>();

            try
            {
                DataSet ds = new DataSet();
                DBHelper.FillDataset(DBHelper.connString, CommandType.Text, sql, ds, new string[] { "UserInfo" }, values);
                DataTable table = ds.Tables[0];

                foreach (DataRow row in table.Rows)
                {
                    UserInfo userInfo = new UserInfo();

                    userInfo.UserId = Convert.ToInt32(row["UserId"]);
                    userInfo.UserName = (string)row["UserName"];
                    userInfo.Password = (string)row["Password"];
                    userInfo.Gender = (int)row["Gender"];
                    if (row["RealName"].GetType().FullName != "System.DBNull")
                        userInfo.RealName = (string)row["RealName"];
                    if (row["ImageURL"].GetType().FullName != "System.DBNull")
                        userInfo.ImageURL = (string)row["ImageURL"];
                    if (row["DepartId"].GetType().FullName != "System.DBNull")
                        userInfo.Depart = DepartInfoService.GetDepartInfoByDepartId((int)row["DepartId"]); //FK
                    userInfo.Role = RoleInfoService.GetRoleInfoByRoleId((int)row["RoleId"]); //FK
                    userInfo.UserState = UserStateService.GetUserStateByUserStateId((int)row["UserState"]); //FK

                    list.Add(userInfo);
                }

                return list;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                throw e;
            }

        }
        /// <summary>
        /// 根据 部门 机构 登录ID 用户名 查询用户
        /// </summary>
        /// <param name="dId">部门ID</param>
        /// <param name="loginId">登录ID(userName)</param>
        /// <param name="userName">用户名(realName)</param>
        /// <param name="bid">机构ID</param>
        /// <returns></returns>
        public static List<UserInfo> GetUserInfoBySerach(int dId, string loginId, string userName, int bid)
        {
            List<SqlParameter> paramItem = new List<SqlParameter>();
            string sql = "select Distinct u.* from userInfo u,DepartInfo d where u.departId = d.departId";
            if (dId != 0)
            {
                sql += " and  d.departId =" + dId;
            }
            if (bid != 0)
            {
                sql += " and d.branchId =" + bid;
            }
            if (loginId != "")
            {
                sql += " and userName =@userName";
                paramItem.Add(new SqlParameter("@userName", loginId));
            }
            if (userName != "")
            {
                sql += " and realName like @realName";
                paramItem.Add(new SqlParameter("@realName", "%" + userName + "%"));
            }
            return (List<UserInfo>)GetUserInfosBySql(sql, paramItem.ToArray());
        }

        /// <summary>
        /// 根据用户ID得到用户信息     (王浩)
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public static UserInfo GetUserInfoByRealName(string realName)
        {
            string sql = "SELECT * FROM UserInfo WHERE RealName like @RealName";
            SqlParameter[] para = new SqlParameter[]
            {
                new SqlParameter("@RealName", "%"+realName+"%"),
            };
            IList<UserInfo> list = GetUserInfosBySql(sql, para);
            if (list.Count > 0)
                return list[0];
            return null;
        }
        /// <summary>
        /// 根据机构、部门查询用户
        /// </summary>
        /// <param name="branchId"></param>
        /// <param name="departId"></param>
        /// <returns></returns>
        public static IList<UserInfo> GetUserByBranchIdOrDepartId(int branchId, int departId)
        {
            string sql = "select * from userInfo where 1=1 and userId!=(select userid from userInfo where userName='admin')  ";
            if (branchId > 0)
                sql += "  and DepartId in(select departId from DepartInfo where BranchID in(select branchId from branchInfo where branchid=" + branchId + "))  ";
            if (departId > 0)
                sql += "  and DepartId in(select departId from DepartInfo where departId=" + departId + ") ";
            return GetUserInfosBySql(sql);
        }

    }
}

阅读更多
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭