userservices.cs

在线考试系统完整版源码(C#&Access) 该考试系统为一同学毕业设计

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using ExamModel.Auto_Generated_Code;

namespace ExamDAL.Auto_Generated_Code
{
   public class UserServices
    {

       //根据登录名查询用户
       public static User GetUserByLoginId(string loginId)
       {
           string sql = "select * from [User] where [UID]=@loginId";
           int departmentid;
           int roleid;
           using (OleDbDataReader reader = DBHelp.GetReader(sql, new OleDbParameter("@LoginId", loginId)))
           {
               if (reader.Read())
               {
                   User user = new User();
                   user.ID = (int)reader["ID"];
                   user.UID = (string)reader["UID"];
                   departmentid = (int)reader["DeparmentID"];
                   user.UserName = (string)reader["Name"];
                   user.UPassWord = (string)reader["PassWord"];
                   user.USex = (bool)reader["Sex"];
                   user.UAddress = (string)reader["Address"];
                   user.UPhone = (string)reader["Phone"];
                   user.UEmail = (string)reader["Email"];
                   roleid = (int)reader["Role"];
                   reader.Close();
                   user.UDepartment = DepartmentService.GetDepartmentById(departmentid);//FK
                   user.URole = RoleServices.GetRoleById(roleid);//FK
                   return user;
               }
               else
               {
                   reader.Close();
                   return null;
               }
           }
       }












        /// <summary>
        /// 添加新用户
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public static int AddUser(User user)
        {

            string sql = "insert into [User](UID,DeparmentID,[Name],[PassWord],[Sex],[Address],[Phone],[Email],[Role]) values (@UID,@DeparmentID,@Name,@PassWord,@Sex,@Address,@Phone,@Mail,@Role)";
            OleDbParameter[] para = new OleDbParameter[]
                {
                      new OleDbParameter("@UID",user.UID),
                      new OleDbParameter("@DeparmentID",user.UDepartment.DepartmentID),//FK
                      new OleDbParameter("@Name",user.UserName),
                      new OleDbParameter("@PassWord",user.UPassWord),
                      new OleDbParameter("@Sex",user.USex),
                      new OleDbParameter("@Address",user.UAddress),
                      new OleDbParameter("@Phone",user.UPhone),
                      new OleDbParameter("@Mail",user.UEmail),
                      new OleDbParameter("@Role",user.URole.RoleID) //Fk
                };
            return DBHelp.ExecuteCommand(sql, para);

               //string sql = "insert into User (UID,DeparmentID,Name,PassWord,Sex,Address,Phone,Email,Role) values (" + user.UID + "," + user.UDepartment.DepartmentID + ",'" + user.UserName + "','" + user.UPassWord + "'," + user.USex + ",'" + user.UAddress + "','" + user.UPhone + "','" + user.UEmail + "'," + user.URole.RoleID + ")";
               //return DBHelp.ExecuteCommand(sql);
        }
        //通过ID获得科目对象
        public static User GetUserById(int userId)
        {
            string sql = "select * from [User] where ID=" + userId;

            int departmentId;
            int roleId;
            try
            {
                OleDbDataReader reader = DBHelp.GetReader(sql);
                if (reader.Read())
                {
                    User user = new User();
                    user.ID = (int)reader["ID"];
                    user.UID = (string)reader["UID"];
                    user.UDepartment = DepartmentService.GetDepartmentById((int)reader["DeparmentID"]);//FK
                    user.UserName = (string)reader["Name"];
                    user.UPassWord = (string)reader["PassWord"];
                    user.USex = (bool)reader["Sex"];
                    user.UAddress = (string)reader["Address"];
                    user.UPhone = (string)reader["Phone"];
                    user.UEmail = (string)reader["Email"];
                    user.URole = RoleServices.GetRoleById((int)reader["Role"]);
                    reader.Close();
                    return user;

                }
                else
                {
                    reader.Close();
                    return null;
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                throw e;
            }
        }
        //通过ID获得科目对象
       public static User GetUserByuserId(string userId)
       {
           string sql = "select * from [User] where UID='" + userId+"'";

           int departmentId;
           int roleId;
           try
           {
               OleDbDataReader reader = DBHelp.GetReader(sql);
               if (reader.Read())
               {
                   User user = new User();
                   user.ID = (int)reader["ID"];
                   user.UID = (string)reader["UID"];
                   user.UDepartment = DepartmentService.GetDepartmentById((int)reader["DeparmentID"]);//FK
                   user.UserName = (string)reader["Name"];
                   user.UPassWord = (string)reader["PassWord"];
                   user.USex = (bool)reader["Sex"];
                   user.UAddress = (string)reader["Address"];
                   user.UPhone = (string)reader["Phone"];
                   user.UEmail = (string)reader["Email"];
                   user.URole = RoleServices.GetRoleById((int)reader["Role"]);
                   reader.Close();
                   return user;

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


       //获得所有用户
        public static IList<User> GetAllUser()
        {
            string sqlAll = "select * from [User]";
            return GetUserBySql(sqlAll);
        }
       
        private static IList<User> GetUserBySql(string safeSql)
        {
            List<User> list = new List<User>();
            try
            {
                DataSet ds = DBHelp.GetDataSet(safeSql);
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    User user = new User();
                    user.ID = (int)row["ID"];
                    user.UID = (string)row["UID"];
                    user.UDepartment = DepartmentService.GetDepartmentById((int)row["DeparmentID"]);//FK
                    user.UserName = (string)row["Name"];
                    user.UPassWord = (string)row["PassWord"];
                    user.USex = (bool)row["Sex"];
                    user.UAddress = (string)row["Address"];
                    user.UPhone = (string)row["Phone"];
                    user.UEmail = (string)row["Email"];
                    user.URole = RoleServices.GetRoleById((int)row["Role"]);//Fk

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

        }
        //更新单选题
        public static bool UpdateUser(User user)
        {
            try
            {
                string sql1 = "update [User] set [UID]=" + user.UID + ",[DeparmentID]=" + user.UDepartment.DepartmentID + ",[Name]='" + user.UserName + "',[PassWord]='" + user.UPassWord + "',[Sex]=" +user.USex + ",[Address]='" + user.UAddress + "',[Phone]='" + user.UPhone + "',[Email]='"+user.UEmail+"',[Role]="+user.URole.RoleID+" where [ID]=" + user.ID;
                DBHelp.ExecuteCommand(sql1);
                return true;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                throw e;
            }

        }
    }
}