public static DataTable Query(Hashtable queryItems)
{
string where = SQLString.GetConditionClause(queryItems);
string sql = "Select * From [Departmentmr]" + where;
DataBase db = new DataBase();
return db.GetDataTable(sql);
}
//ɾ����ʦϵ����Ϣ
public bool DeleteByStr(string DepartmentId)
{
SqlParameter[] Params = new SqlParameter[1];
DataBase DB = new DataBase();
string strsql = "DELETE FROM Departmentmr WHERE (DepartmentId = @DepartmentId)";
Params[0] = DB.MakeInParam("@DepartmentId", SqlDbType.Int, 4, DepartmentId); //�û����
int Count = -1;
Count = DB.ProcStr(strsql, Params);
if (Count > 0)
return true;
else return false;
}
//向Coursemr表中添加考试科目信息
//输出：
// 如果插入成功：返回True；
// 如果插入失败：返回False；
public bool InsertByStr()
{
SqlParameter[] Params = new SqlParameter[1];
DataBase DB = new DataBase();
string strsql = "INSERT INTO Coursemr(Name) VALUES (@Name)";
Params[0] = DB.MakeInParam("@Name", SqlDbType.VarChar, 50, Name); //考试科目名称
int Count = -1;
Count = DB.ProcStr(strsql, Params);
if (Count > 0)
return true;
else return false;
}
//删除考试科目
//输入：
// CID - 科目编号；
//输出：
// 如果删除成功：返回True；
// 如果删除失败：返回False；
public bool DeleteByStr(int CID)
{
SqlParameter[] Params = new SqlParameter[1];
DataBase DB = new DataBase();
string strsql = "DELETE FROM Coursemr WHERE ID = @ID";
Params[0] = DB.MakeInParam("@ID", SqlDbType.Int,4, CID); //科目编号
int Count = -1;
Count = DB.ProcStr(strsql, Params);
if (Count > 0)
return true;
else return false;
}
public bool CheckDepartment(int XDepartmentId)
{
SqlParameter[] Params = new SqlParameter[1];
DataBase DB = new DataBase();
string strsql = "SELECT * FROM Departmentmr WHERE DepartmentId =@DepartmentId";
Params[0] = DB.MakeInParam("@DepartmentId", SqlDbType.Int, 4, XDepartmentId);
SqlDataReader DR = DB.RunStrGetReader(strsql, Params);
if (!DR.Read())
{
return false;
}
else
{
return true;
}
}
//ɾ����Ŀ
//���룺
// TID - ��Ŀ��ţ�
//�����
// ɾ���ɹ�������True��
// ɾ��ʧ�ܣ�����False��
public bool DeleteByStr(int TID)
{
SqlParameter[] Params = new SqlParameter[1];
DataBase DB = new DataBase();
string strsql = "DELETE FROM QuestionProblemmr WHERE (ID= @ID)";
Params[0] = DB.MakeInParam("@ID", SqlDbType.Int, 4, TID); //��Ŀ���
int Count = -1;
Count = DB.ProcStr(strsql, Params);
if (Count > 0)
return true;
else return false;
}
//�����ж������Ϣ
public bool UpdateByStr(int TID)
{
SqlParameter[] Params = new SqlParameter[4];
DataBase DB = new DataBase();
string strsql = "UPDATE QuestionProblemmr SET CourseID= @CourseID,Title= @Title,Answer = @Answer WHERE (ID = @ID)";
Params[0] = DB.MakeInParam("@ID", SqlDbType.Int, 4, TID); //��Ŀ���
Params[1] = DB.MakeInParam("@CourseID", SqlDbType.Int, 4, CourseID); //��Ŀ���
Params[2] = DB.MakeInParam("@Title", SqlDbType.VarChar, 1000, Title); //��Ŀ
Params[3] = DB.MakeInParam("@Answer", SqlDbType.VarChar, 1000, Answer); //��
int Count = -1;
Count = DB.ProcStr(strsql, Params);
if (Count > 0)
return true;
else return false;
}
//更新试卷是否评阅的状态
public bool UpdateByProc(string XUserID,int XPaperID,string Xstate)
{
SqlParameter[] Params = new SqlParameter[3];
DataBase DB = new DataBase();
string strsql = "UPDATE UserAnswermr SET state= @state WHERE UserID = @UserID and PaperID= @PaperID";
Params[0] = DB.MakeInParam("@UserID", SqlDbType.VarChar, 50, XUserID);
Params[1] = DB.MakeInParam("@PaperID", SqlDbType.Int, 4, XPaperID);
Params[2] = DB.MakeInParam("@state", SqlDbType.VarChar, 50,Xstate);
int Count = -1;
Count = DB.ProcStr(strsql,Params);
if (Count > 0)
return true;
else return false;
}
//查询某个用户考试的试卷
public DataSet QueryUserPaper(string type,string userid)
{
DataBase DB = new DataBase();
SqlParameter[] Params = new SqlParameter[2];
//Params[0] = DB.MakeInParam("@PaperID", SqlDbType.Int, 4, paperid); //试卷编号
Params[0] = DB.MakeInParam("@Type", SqlDbType.VarChar, 10, type); //题目类型
Params[1] = DB.MakeInParam("@UserID", SqlDbType.VarChar, 50, userid); //用户ID
return DB.GetDataSet("Proc_UserAnswermr", Params);
}
//判断是否已经考试
public bool IsTest(string UserID, int PaperID)
{
SqlParameter[] Params = new SqlParameter[2];
DataBase DB = new DataBase();
string strsql = "SELECT * FROM UserAnswermr WHERE UserID=@UserID AND PaperID=@PaperID";
Params[0] = DB.MakeInParam("@UserID", SqlDbType.VarChar, 20, UserID);
Params[1] = DB.MakeInParam("@PaperID", SqlDbType.Int, 4, PaperID);
SqlDataReader DR = DB.RunStrGetReader(strsql, Params);
if (!DR.Read())
{
return false;
}
else
{
return true;
}
}
//查询填空题
//课程编号
public DataSet QueryFillBlankProblem(int TCourseID)
{
SqlParameter[] Params = new SqlParameter[1];
DataBase DB = new DataBase();
string strsql = "SELECT FillBlankProblemmr.ID, FillBlankProblemmr.FrontTitle,FillBlankProblemmr.BackTitle FROM FillBlankProblemmr where CourseID=@CourseID";
Params[0] = DB.MakeInParam("@CourseID", SqlDbType.Int, 4, TCourseID); //题目编号
return DB.GetStrDataSetSql(strsql, Params);
}
//更新用户的信息
public bool UpdateByProc(string XUserID)
{
SqlParameter[] Params = new SqlParameter[4];
DataBase DB = new DataBase();
string strsql = "UPDATE Usersmr SET UserName= @UserName,DepartmentId= @DepartmentId,RoleId = @RoleId WHERE UserID = @UserID";
Params[0] = DB.MakeInParam("@UserID", SqlDbType.VarChar, 50, XUserID); //用户编号
Params[1] = DB.MakeInParam("@UserName", SqlDbType.VarChar, 50, UserName); //用户姓名
Params[2] = DB.MakeInParam("@DepartmentId", SqlDbType.Int, 4, DepartmentId); //部门
Params[3] = DB.MakeInParam("@RoleId", SqlDbType.Int, 4, RoleId); //角色
int Count = -1;
Count = DB.ProcStr(strsql, Params);
if (Count > 0)
return true;
else return false;
}
//更新考试科目的信息
public bool UpdateByStr(int CID)
{
SqlParameter[] Params = new SqlParameter[2];
DataBase DB = new DataBase();
string strsql = "UPDATE Coursemr SET Name = @Name WHERE ID = @ID";
Params[0] = DB.MakeInParam("@ID", SqlDbType.Int, 4, CID); //用户编号
Params[1] = DB.MakeInParam("@Name", SqlDbType.VarChar, 200, Name); //用户权限
int Count = -1;
Count = DB.ProcStr(strsql, Params);
if (Count > 0)
return true;
else return false;
}
//查询用户
//查询所用用户
//不需要参数
public DataSet QueryUsers()
{
DataBase DB = new DataBase();
string strsql = "SELECT Usersmr.UserID, Usersmr.UserName,Departmentmr.DepartmentName,Rolemr.RoleName FROM Usersmr,Departmentmr,Rolemr WHERE Usersmr.DepartmentId=Departmentmr.DepartmentId AND Usersmr.RoleId=Rolemr.RoleId";
//return DB.GetDataSet("Proc_UsersList");
return DB.GetStrDataSet(strsql);
}
//修改用户的密码
//输入：
// XUserID - 用户编号；
//输出：
// 修改成功：返回True；
// 修改失败：返回False；
public bool ModifyPassword(string XUserID)
{
SqlParameter[] Params = new SqlParameter[2];
DataBase DB = new DataBase();
string strsql = "UPDATE Usersmr SET UserPwd = @UserPwd WHERE (UserID = @UserID)";
Params[0] = DB.MakeInParam("@UserID", SqlDbType.VarChar, 20, XUserID); //用户编号
Params[1] = DB.MakeInParam("@UserPwd", SqlDbType.VarChar, 64, UserPwd); //用户密码
int Count = -1;
Count = DB.ProcStr(strsql, Params);
if (Count > 0)
return true;
else return false;
}
//根据用户 UserID 初始化该用户
//输入：
// XUserID - 用户编号；
//输出：
// 用户存在：返回True；
// 用户不在：返回False；
public bool LoadData(string XUserID)
{
SqlParameter[] Params = new SqlParameter[1];
DataBase DB = new DataBase();
Params[0] = DB.MakeInParam("@UserID", SqlDbType.VarChar, 50, XUserID); //用户编号
string strsql = "SELECT * FROM Usersmr,Departmentmr,Rolemr WHERE UserID=@UserID AND Usersmr.DepartmentId = Departmentmr.DepartmentId And Usersmr.RoleId = Rolemr.RoleId";
DataSet ds = DB.GetStrDataSetSql(strsql, Params);
ds.CaseSensitive = false;
DataRow DR;
if (ds.Tables[0].Rows.Count > 0)
{
DR= ds.Tables[0].Rows[0];
this._userID = GetSafeData.ValidateDataRow_S(DR, "UserID"); //用户编号
this._userName = GetSafeData.ValidateDataRow_S(DR, "UserName"); //用户姓名
this._userPwd = GetSafeData.ValidateDataRow_S(DR, "UserPwd"); //用户密码
this._department = GetSafeData.ValidateDataRow_N(DR, "DepartmentId"); //所在部门
this._roleid = GetSafeData.ValidateDataRow_N(DR, "RoleId"); //用户权限
this._rolename = GetSafeData.ValidateDataRow_S(DR, "RoleName"); //用户权限
//获取权限集合
string colName = "";
for (int i = 0; i < DR.ItemArray.Length; i++)
{
colName = DR.Table.Columns[i].ColumnName;
if (colName.StartsWith("HasDuty_") && GetSafeData.ValidateDataRow_N(DR, colName) == 1)
{
this._duties.Add(DR.Table.Columns[i].ColumnName.Substring(8)); //去掉前缀“HasDuty_”
}
}
return true;
}
else
{
return false;
}
}
//向FillBlankProblemmr表中添加题目信息
//输出：
// 如果插入成功：返回True；
// 如果插入失败：返回False；
public bool InsertByStr()
{
SqlParameter[] Params = new SqlParameter[4];
DataBase DB = new DataBase();
string strsql = "INSERT INTO FillBlankProblemmr (CourseID,FrontTitle,BackTitle,Answer) VALUES (@CourseID,@FrontTitle,@BackTitle,@Answer)";
Params[0] = DB.MakeInParam("@CourseID", SqlDbType.Int, 4, CourseID); //科目编号
Params[1] = DB.MakeInParam("@FrontTitle", SqlDbType.VarChar, 500, FrontTitle); //题目前部分
Params[2] = DB.MakeInParam("@BackTitle", SqlDbType.VarChar, 500, BackTitle); //题名后部分
Params[3] = DB.MakeInParam("@Answer", SqlDbType.VarChar, 200, Answer); //答案
int Count = -1;
Count = DB.ProcStr(strsql, Params);
if (Count > 0)
return true;
else return false;
}
// 删除某位用户的试卷
public bool DeleteByProc(string userid,int paperid)
{
SqlParameter[] Params = new SqlParameter[2];
DataBase DB = new DataBase();
string strsql = "DELETE From UserAnswermr WHERE UserID= @UserID and PaperID=@PaperID";
Params[0] = DB.MakeInParam("@UserID", SqlDbType.VarChar, 50, userid); //用户ＩＤ
Params[1] = DB.MakeInParam("@PaperID", SqlDbType.Int, 4, paperid); //试卷ＩＤ
int Count = -1;
//Count = DB.RunProc("Proc_UserPaperDelete", Params);
Count = DB.ProcStr(strsql, Params);
if (Count > 0)
return true;
else return false;
}
//根据题目ID 初始化题目
//输入：
// TID - 题目编号；
//输出：
// 如果考试题目存在：返回True；
// 如果考试题目不在：返回False；
public bool LoadData(int TID)
{
SqlParameter[] Params = new SqlParameter[1];
DataBase DB = new DataBase();
string strsql = "SELECT * FROM FillBlankProblemmr where ID=@ID";
Params[0] = DB.MakeInParam("@ID", SqlDbType.Int, 4, TID); //用户编号
DataSet ds = DB.GetStrDataSetSql(strsql, Params);
ds.CaseSensitive = false;
DataRow DR;
if (ds.Tables[0].Rows.Count > 0)
{
DR = ds.Tables[0].Rows[0];
this._CourseID = GetSafeData.ValidateDataRow_N(DR, "CourseID"); //科目编号
this._FrontTitle = GetSafeData.ValidateDataRow_S(DR, "FrontTitle"); //题目前部分
this._BackTitle = GetSafeData.ValidateDataRow_S(DR, "BackTitle"); //题目后部分
this._Answer = GetSafeData.ValidateDataRow_S(DR, "Answer"); //答案
return true;
}
else
{
return false;
}
}
//查询所用试卷
//不需要参数
public DataSet QueryAllPaper()
{
DataBase DB = new DataBase();
string strsql = "SELECT *,CASE Papermr.PaperState WHEN 1 THEN '可用' ELSE '不可用' END AS state FROM Papermr,Coursemr where Papermr.CourseID=Coursemr.ID";
return DB.GetStrDataSet(strsql);
}
//更新填空题的信息
public bool UpdateByStr(int TID)
{
SqlParameter[] Params = new SqlParameter[5];
DataBase DB = new DataBase();
string strsql = "UPDATE FillBlankProblemmr SET CourseID= @CourseID,FrontTitle= @FrontTitle,BackTitle= @BackTitle,Answer= @Answer WHERE (ID= @ID)";
Params[0] = DB.MakeInParam("@ID", SqlDbType.Int, 4, TID); //题目编号
Params[1] = DB.MakeInParam("@CourseID", SqlDbType.Int, 4, CourseID); //科目编号
Params[2] = DB.MakeInParam("@FrontTitle", SqlDbType.VarChar, 500, FrontTitle); //题目前部分
Params[3] = DB.MakeInParam("@BackTitle", SqlDbType.VarChar, 500, BackTitle); //题名后部分
Params[4] = DB.MakeInParam("@Answer", SqlDbType.VarChar, 200, Answer); //答案
int Count = -1;
Count = DB.ProcStr(strsql, Params);
if (Count > 0)
return true;
else return false;
}
//向Usersmr表中添加用户信息(采用存储过程)
//输出：
// 插入成功：返回True；
// 插入失败：返回False；
public bool InsertByStr()
{
SqlParameter[] Params = new SqlParameter[5];
DataBase DB = new DataBase();
string strsql = "INSERT INTO Usersmr (UserID,UserName,UserPwd,DepartmentId,RoleId) VALUES ( @UserID,@UserName,@UserPwd,@DepartmentId, @RoleId)";
Params[0] = DB.MakeInParam("@UserID", SqlDbType.VarChar, 50, UserID); //用户编号
Params[1] = DB.MakeInParam("@UserName", SqlDbType.VarChar, 50, UserName); //用户姓名
Params[2] = DB.MakeInParam("@UserPwd", SqlDbType.VarChar,64, UserPwd); //用户密码
Params[3] = DB.MakeInParam("@RoleId", SqlDbType.Int, 4,RoleId); //角色
Params[4] = DB.MakeInParam("@DepartmentId", SqlDbType.Int, 4, DepartmentId); //部门
int Count = -1;
//Count = DB.RunProc("Proc_UsersAdd", Params);
Count = DB.ProcStr(strsql, Params);
if (Count > 0)
return true;
else return false;
}
//向Papermr表中添加试卷信息(采用存储过程)
//输出：
// 如果插入成功：返回True；
// 如果插入失败：返回False；
public bool InsertByProc()
{
SqlParameter[] Params = new SqlParameter[3];
DataBase DB = new DataBase();
string strsql = "";
Params[0] = DB.MakeInParam("@CourseID", SqlDbType.Int, 4, CourseID); //科目编号
Params[1] = DB.MakeInParam("@PaperName", SqlDbType.VarChar, 200, PaperName); //试卷名称
Params[2] = DB.MakeInParam("@PaperState", SqlDbType.Bit,1, PaperState); //试卷状态
int Count = -1;
Count = DB.RunProc("Proc_PaperAdd", Params);
if (Count > 0)
return true;
else return false;
}
//查询所用考试科目
//不需要带参数
public DataSet QueryCourse()
{
DataBase DB = new DataBase();
string strsql = "select * from Coursemr";
return DB.GetStrDataSet(strsql);
}
//查询所用可用试卷
//不需要参数
public DataSet QueryPaper()
{
DataBase DB = new DataBase();
string strsql = "SELECT Papermr.PaperID,Papermr.PaperName FROM Papermr where PaperState=@paperState";
SqlParameter[] Params = new SqlParameter[1];
Params[0] = DB.MakeInParam("@PaperState", SqlDbType.Bit,1, "true"); //题目编号
//return DB.GetDataSet("Proc_PaperUseList",Params);
return DB.GetStrDataSetSql(strsql, Params);
}
//��SingleProblemmr���������Ŀ��Ϣ(���ò�������)
//�����
// ����ɹ�������True��
// ����ʧ�ܣ�����False��
public bool InsertByStr()
{
SqlParameter[] Params = new SqlParameter[3];
DataBase DB = new DataBase();
string strsql = "INSERT INTO QuestionProblemmr (CourseID,Title,Answer) VALUES (@CourseID,@Title,@Answer)";
Params[0] = DB.MakeInParam("@CourseID", SqlDbType.Int, 4, CourseID); //��Ŀ���
Params[1] = DB.MakeInParam("@Title", SqlDbType.VarChar, 1000, Title); //��Ŀ
Params[2] = DB.MakeInParam("@Answer", SqlDbType.VarChar,1000, Answer); //��A
int Count = -1;
Count = DB.ProcStr(strsql, Params);
if (Count > 0)
return true;
else return false;
}
//查询所有用户考试的试卷
public DataSet QueryUserPaperList()
{
DataBase DB = new DataBase();
string strsql = "SELECT distinct Usersmr.UserID,Usersmr.UserName,Usersmr.DepartmentId, Departmentmr.DepartmentId,Departmentmr.DepartmentName,UserAnswermr.UserID,UserAnswermr.PaperID,UserAnswermr.ExamTime,UserAnswermr.state,Papermr.PaperName FROM Usersmr,Departmentmr,UserAnswermr,Papermr where Usersmr.DepartmentId=Departmentmr.DepartmentId and Usersmr.UserID=UserAnswermr.UserID and UserAnswermr.PaperID=Papermr.PaperID order by UserAnswermr.ExamTime desc";
return DB.GetStrDataSet(strsql);
}
//������ĿID ��ʼ����Ŀ
//���룺
// TID - ��Ŀ��ţ�
//�����
// ���������Ŀ���ڣ�����True��
// ���������Ŀ���ڣ�����False��
public bool LoadData(int TID)
{
SqlParameter[] Params = new SqlParameter[1];
DataBase DB = new DataBase();
string strsql = "SELECT * FROM QuestionProblemmr where ID=@ID";
Params[0] = DB.MakeInParam("@ID", SqlDbType.Int, 4, TID); //�û����
DataSet ds = DB.GetStrDataSetSql(strsql, Params);
ds.CaseSensitive = false;
DataRow DR;
if (ds.Tables[0].Rows.Count > 0)
{
DR = ds.Tables[0].Rows[0];
this._CourseID = GetSafeData.ValidateDataRow_N(DR, "CourseID"); //��Ŀ���
this._Title = GetSafeData.ValidateDataRow_S(DR, "Title"); //��Ŀ
this._Answer = GetSafeData.ValidateDataRow_S(DR, "Answer"); //��
return true;
}
else
{
return false;
}
}
//更新试卷信息
public bool UpdateByStr(int PID)
{
SqlParameter[] Params = new SqlParameter[2];
DataBase DB = new DataBase();
string strsql = "UPDATE Papermr SET PaperState = @PaperState WHERE PaperID= @PaperID";
Params[0] = DB.MakeInParam("@PaperID", SqlDbType.Int, 4, PID); //试卷编号
Params[1] = DB.MakeInParam("@PaperState", SqlDbType.Bit, 1, PaperState); //试卷状态
int Count = -1;
Count = DB.ProcStr(strsql, Params);
if (Count > 0)
return true;
else return false;
}
//��ѯ�ʴ���
//�γ̱��
public DataSet QueryQuestionProblem(int TCourseID)
{
SqlParameter[] Params = new SqlParameter[1];
DataBase DB = new DataBase();
string strsql = "SELECT QuestionProblemmr.ID,QuestionProblemmr.Title FROM QuestionProblemmr where CourseID=@CourseID";
Params[0] = DB.MakeInParam("@CourseID", SqlDbType.Int, 4, TCourseID); //��Ŀ���
return DB.GetStrDataSetSql(strsql, Params);
}
