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); //用户ID Params[1] = DB.MakeInParam("@PaperID", SqlDbType.Int, 4, paperid); //试卷ID 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); }