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;
}
}
}
}
苏萦