/// <summary> /// 获取所有列表 /// </summary> /// <returns></returns> public List <Model.Status> GetList(string Ys_Code) { string strSql = ""; strSql = "Select * FROM [Status] WHERE [YS_TYPE]='" + Ys_Code + "'"; List <Model.Status> List = new List <Model.Status>(); SqlParameter[] parameters = { }; using (SqlDataReader DR = ExamSys.ExecuteReader(strSql.ToString(), parameters)) { while (DR.Read()) { Model.Status model = new Model.Status(); model.YS_ID = int.Parse(DR["YS_ID"].ToString()); model.YS_CODE = DR["YS_CODE"].ToString(); model.YS_DESC = DR["YS_DESC"].ToString(); model.YS_SEQ = int.Parse(DR["YS_SEQ"].ToString()); List.Add(model); } DR.Close(); DR.Dispose(); } return(List); }
/// <summary> /// 详细信息 /// </summary> /// <param name="ID">ID</param> /// <returns></returns> public List <Model.TitleList> Details(int ID) { string strSql = ""; strSql = "Select * FROM [TitleList] Where [ID]='" + ID + "'"; List <Model.TitleList> List = new List <Model.TitleList>(); SqlParameter[] parameters = { }; SqlDataReader DR; DR = ExamSys.ExecuteReader(strSql.ToString(), parameters); using (DR) { while (DR.Read()) { Model.TitleList model = new Model.TitleList(); model.Title = DR["Title"].ToString(); model.T_F = DR["T_F"].ToString(); model.Analysis = DR["Analysis"].ToString(); model.ID = int.Parse(DR["ID"].ToString()); model.CourseID = int.Parse(DR["CourseID"].ToString()); model.Status_id = int.Parse(DR["Status_id"].ToString()); List.Add(model); } DR.Close(); DR.Dispose(); } return(List); }
/// <summary> /// 查询所有 /// </summary> /// <returns></returns> public List <Model.HardInfo> GetList() { string strSql = ""; strSql = "Select * FROM HardInfo"; List <Model.HardInfo> List = new List <Model.HardInfo>(); SqlParameter[] parameters = { }; using (SqlDataReader DR = ExamSys.ExecuteReader(strSql.ToString(), parameters)) { while (DR.Read()) { Model.HardInfo model = new Model.HardInfo(); model.HostName = DR["HostName"].ToString(); model.CpuID = DR["CpuID"].ToString(); model.HardDiskID = DR["HardDiskID"].ToString(); model.NetCardMAC = DR["NetCardMAC"].ToString(); model.NetCardIP = DR["NetCardIP"].ToString(); model.VolOf = DR["VolOf"].ToString(); List.Add(model); } DR.Close(); DR.Dispose(); } return(List); }
/// <summary> /// 显示所有 /// </summary> /// <param name="Type">类型(单选/多选/不定项选/判断)</param> /// <param name="PapersID">试卷ID</param> /// <returns></returns> public List <Model.PapersTitleList> GetList(string Type, int PapersID) { string strSql = ""; strSql = "Select * FROM [PapersTitleList] Where [Type]='" + Type + "' AND [PapersID]='" + PapersID + "' order by newid()"; List <Model.PapersTitleList> List = new List <Model.PapersTitleList>(); SqlParameter[] parameters = { }; SqlDataReader DR; DR = ExamSys.ExecuteReader(strSql.ToString(), parameters); using (DR) { while (DR.Read()) { Model.PapersTitleList model = new Model.PapersTitleList(); model.Title = DR["Title"].ToString(); model.T_F = DR["T_F"].ToString(); model.ID = int.Parse(DR["ID"].ToString()); model.Score = int.Parse(DR["Score"].ToString()); List.Add(model); } DR.Close(); DR.Dispose(); } return(List); }
/// <summary> /// 添加 /// </summary> /// <param name="model"></param> /// <returns></returns> public int Add(Model.TitleList model) { int rowsAffected; SqlParameter[] parameters = { new SqlParameter("@ReturnValue", SqlDbType.Int), new SqlParameter("@CourseID", SqlDbType.Int), new SqlParameter("@Type", SqlDbType.NVarChar, 50), new SqlParameter("@Title", SqlDbType.NVarChar, 500), new SqlParameter("@T_F", SqlDbType.NVarChar, 50), new SqlParameter("@Analysis", SqlDbType.Text), new SqlParameter("@Extent", SqlDbType.NVarChar, 50), new SqlParameter("@Status_id", SqlDbType.Int) }; parameters[0].Direction = ParameterDirection.Output; parameters[1].Value = model.CourseID; parameters[2].Value = model.Type; parameters[3].Value = model.Title; parameters[4].Value = model.T_F; parameters[5].Value = model.Analysis; parameters[6].Value = model.Extent; parameters[7].Value = model.Status_id; ExamSys.RunProcedure("TitleList_Add", parameters, out rowsAffected); return((int)parameters[0].Value); }
/// <summary> /// /// </summary> /// <param name="PageCount"></param> /// <param name="PageSize"></param> /// <param name="Type"></param> /// <param name="Extent"></param> /// <returns></returns> public List <Model.TitleList> GetList(int PageCount, int PageSize, string Type, string Extent) { string strSql = ""; string RedCountsql = ""; int RowTotal = (PageCount - 1) * PageSize;// RedCountsql = "(select count(ID) from [TitleList] Where [Type]='" + Type + "' and [Extent]='" + Extent + "' ) as RowTotal"; strSql = "Select Top " + PageSize + " *," + RedCountsql + " FROM [TitleList] where [ID] not in (Select Top " + RowTotal + " [ID] from [TitleList] Where [Type]='" + Type + "' and [Extent]='" + Extent + "' ORDER BY ID DESC) and [Type]='" + Type + "' and [Extent]='" + Extent + "' ORDER BY ID DESC"; List <Model.TitleList> List = new List <Model.TitleList>(); SqlParameter[] parameters = { }; using (SqlDataReader DR = ExamSys.ExecuteReader(strSql.ToString(), parameters)) { while (DR.Read()) { Model.TitleList model = new Model.TitleList(); model.RowTotal = int.Parse(DR["RowTotal"].ToString()); model.ID = int.Parse(DR["ID"].ToString()); model.Type = DR["Type"].ToString(); model.Title = DR["Title"].ToString(); model.T_F = DR["T_F"].ToString(); model.CourseID = int.Parse(DR["CourseID"].ToString()); model.Extent = DR["Extent"].ToString(); List.Add(model); } DR.Close(); DR.Dispose(); } return(List); }
/// <summary> /// 返回题目类别 /// </summary> /// <returns></returns> public List <Model.Status> GetCategory() { string strSql = ""; strSql = "Select YS_TYPE,YS_REMARK FROM [Status] GROUP BY YS_TYPE,YS_REMARK"; List <Model.Status> List = new List <Model.Status>(); SqlParameter[] parameters = { }; using (SqlDataReader DR = ExamSys.ExecuteReader(strSql.ToString(), parameters)) { while (DR.Read()) { Model.Status model = new Model.Status(); model.YS_TYPE = DR["YS_TYPE"].ToString(); model.YS_REMARK = DR["YS_REMARK"].ToString(); List.Add(model); } DR.Close(); DR.Dispose(); } return(List); }
/// <summary> /// 显示所有 /// </summary> /// <param name="Type">类型(单选/多选/不定项选/判断)</param> /// <param name="CourseID">课程ID</param> /// <param name="Extent">程度</param> /// <returns></returns> public List <Model.TitleList> GetList(string Type, int CourseID, string Extent) { string strSql = ""; strSql = "Select * FROM [TitleList] Where [Type]='" + Type + "' AND [CourseID]='" + CourseID + "' AND [Extent]='" + Extent + "'"; List <Model.TitleList> List = new List <Model.TitleList>(); SqlParameter[] parameters = { }; SqlDataReader DR; DR = ExamSys.ExecuteReader(strSql.ToString(), parameters); using (DR) { while (DR.Read()) { Model.TitleList model = new Model.TitleList(); model.Title = DR["Title"].ToString(); model.T_F = DR["T_F"].ToString(); model.Analysis = DR["Analysis"].ToString(); model.ID = int.Parse(DR["ID"].ToString()); model.Extent = DR["Extent"].ToString(); List.Add(model); } DR.Close(); DR.Dispose(); } return(List); }
/// <summary> /// 删除TitleListID /// </summary> /// <param name="ID">所属题目ID</param> /// <returns></returns> public int Del_TitleListID(int ID) { int rowsAffected; SqlParameter[] parameters = { new SqlParameter("@ReturnValue", SqlDbType.Int), new SqlParameter("@ID", SqlDbType.Int), }; parameters[0].Direction = ParameterDirection.Output; parameters[1].Value = ID; ExamSys.RunProcedure("OptionsList_Del_TitleListID", parameters, out rowsAffected); return((int)parameters[0].Value); }
/// <summary> /// 显示所有 /// </summary> /// <returns></returns> public DataSet GetList(string strWhere) { StringBuilder strSql = new StringBuilder(); strSql.Append("select ID,Name,Type "); strSql.Append(" FROM Papers "); SqlParameter[] parameters = { }; if (strWhere.Trim() != "") { strSql.Append(" where " + strWhere); } return(ExamSys.Query(strSql.ToString())); }
/// <summary> /// 查询所有 /// </summary> /// <returns></returns> public List <Model.UserSystem> GetList(int PageCount, int PageSize) { string strSql = ""; string RedCountsql = ""; int RowTotal = (PageCount - 1) * PageSize;// RedCountsql = "(select count(TR_ID) from [AP_TRAINEE_USER]) as RowTotal"; strSql = "Select Top " + PageSize + " [TR_ID],[TR_POSITION],[TR_NICKNAME],[TR_NAME],[TR_SEX],[TR_CITY],[TR_PHONE],[TR_EMAIL],[TR_COMPANY_TEL],[TR_QR_CODE]," + RedCountsql + " FROM AP_TRAINEE_USER where [TR_ID] not in (Select Top " + RowTotal + " [TR_ID] from AP_TRAINEE_USER ORDER BY TR_ID DESC) ORDER BY TR_ID DESC"; List <Model.UserSystem> List = new List <Model.UserSystem>(); SqlParameter[] parameters = { }; SqlDataReader DR; if (GetUserSysSetupInfo()) { DR = UserSys.ExecuteReader(strSql.ToString(), parameters); } else { DR = ExamSys.ExecuteReader(strSql.ToString(), parameters); } using (DR) { while (DR.Read()) { Model.UserSystem model = new Model.UserSystem(); model.ID = int.Parse(DR["TR_ID"].ToString()); model.cnName = DR["TR_NAME"].ToString(); model.enName = DR["TR_NICKNAME"].ToString(); model.Sex = DR["TR_SEX"].ToString(); model.ZipCode = DR["TR_QR_CODE"].ToString(); model.Address = DR["TR_CITY"].ToString(); model.Mobile = DR["TR_COMPANY_TEL"].ToString(); model.Phone = DR["TR_PHONE"].ToString(); model.Email = DR["TR_EMAIL"].ToString(); model.RowTotal = int.Parse(DR["RowTotal"].ToString()); List.Add(model); } DR.Close(); DR.Dispose(); } return(List); }
/// <summary> /// add by wangke 2016-12-8 /// 获得数据列表 /// 获取该试卷题目所属StatusId /// </summary> public DataTable GetList(int papersID) { StringBuilder strSql = new StringBuilder(); strSql.Append("SELECT Status_id FROM ExaminationSystem.Exam.PapersTitleList WHERE PapersID=@papersID GROUP BY Status_id "); List <Model.Status> List = new List <Model.Status>(); SqlParameter[] parameters = { new SqlParameter("@papersID", SqlDbType.Int) }; parameters[0].Value = papersID; DataTable dt = ExamSys.Query(strSql.ToString(), parameters).Tables[0]; return(dt); }
/// <summary> /// 更新 /// </summary> /// <param name="model"></param> /// <returns></returns> public int Updata(Model.Papers model) { int rowsAffected; SqlParameter[] parameters = { new SqlParameter("@ReturnValue", SqlDbType.Int), new SqlParameter("@Lock", SqlDbType.Int), new SqlParameter("@Name", SqlDbType.NVarChar,50), new SqlParameter("@ID", SqlDbType.Int), }; parameters[0].Direction = ParameterDirection.Output; parameters[1].Value = model.Lock; parameters[2].Value = model.Name; parameters[3].Value = model.ID; ExamSys.RunProcedure("Papers_Updata", parameters, out rowsAffected); return((int)parameters[0].Value); }
/// <summary> /// 添加 /// </summary> /// <param name="model"></param> /// <returns></returns> public int Add(Model.OptionsList model) { int rowsAffected; SqlParameter[] parameters = { new SqlParameter("@ReturnValue", SqlDbType.Int), new SqlParameter("@TitleListID", SqlDbType.Int), new SqlParameter("@Name", SqlDbType.NVarChar,500), new SqlParameter("@T_F", SqlDbType.NVarChar, 50), }; parameters[0].Direction = ParameterDirection.Output; parameters[1].Value = model.TitleListID; parameters[2].Value = model.Name; parameters[3].Value = model.T_F; ExamSys.RunProcedure("OptionsList_Add", parameters, out rowsAffected); return((int)parameters[0].Value); }
/// <summary> /// 显示所有 /// add by wangke 2016-11-23 添加返回datatable类型 /// </summary> /// <param name="Type">类型(单选/多选/不定项选/判断)</param> /// <param name="CourseID">课程ID</param> /// <returns></returns> public DataTable GetList(string Type, int CourseID, int Status_id) { string strSql = ""; if (Status_id != 0) { strSql = "Select * FROM [TitleList] Where [Type]='" + Type + "' AND [CourseID]='" + CourseID + "' AND [Status_id]=" + Status_id; } else { strSql = "Select * FROM [TitleList] Where [Type]='" + Type + "' AND [CourseID]='" + CourseID + "'"; } DataTable dt = new DataTable(); dt.Columns.Add("Title"); dt.Columns.Add("T_F"); dt.Columns.Add("Analysis"); dt.Columns.Add("ID"); dt.Columns.Add("Extent"); SqlParameter[] parameters = { }; SqlDataReader DR; DR = ExamSys.ExecuteReader(strSql.ToString(), parameters); using (DR) { while (DR.Read()) { DataRow dr = dt.NewRow(); dr["Title"] = DR["Title"].ToString(); dr["T_F"] = DR["T_F"].ToString(); dr["Analysis"] = DR["Analysis"].ToString(); dr["ID"] = DR["ID"].ToString(); dr["Extent"] = DR["Extent"].ToString(); dt.Rows.Add(dr); } DR.Close(); DR.Dispose(); } return(dt); }
/// <summary> /// 获得数据列表 /// </summary> public DataTable GetStatusList(string strWhere) { StringBuilder strSql = new StringBuilder(); strSql.Append("select YS_CODE,YS_DESC,YS_TYPE,YS_REMARK,YS_SEQ,YS_ID "); strSql.Append(" FROM Status "); if (strWhere.Trim() != "") { strSql.Append(" where " + strWhere); } List <Model.Status> List = new List <Model.Status>(); SqlParameter[] parameters = { }; DataTable dt = ExamSys.Query(strSql.ToString(), parameters).Tables[0]; return(dt); }
/// <summary> /// 查询所有 /// </summary> /// <param name="UserName">用户名(可中可英)</param> /// <returns></returns> public List <Model.UserSystem> GetList(string UserName) { string strSql = ""; strSql = "Select * FROM [AP_TRAINEE_USER] Where [TR_NAME] like '%" + UserName + "%' or [TR_NICKNAME] like '%" + UserName + "%'"; List <Model.UserSystem> List = new List <Model.UserSystem>(); SqlParameter[] parameters = { }; SqlDataReader DR; if (GetUserSysSetupInfo()) { DR = UserSys.ExecuteReader(strSql.ToString(), parameters); } else { DR = ExamSys.ExecuteReader(strSql.ToString(), parameters); } using (DR) { while (DR.Read()) { Model.UserSystem model = new Model.UserSystem(); model.ID = int.Parse(DR["TR_ID"].ToString()); model.cnName = DR["TR_NAME"].ToString(); model.enName = DR["TR_NICKNAME"].ToString(); model.Sex = DR["TR_SEX"].ToString(); model.ZipCode = DR["TR_QR_CODE"].ToString(); model.Address = DR["TR_CITY"].ToString(); model.Mobile = DR["TR_COMPANY_TEL"].ToString(); model.Phone = DR["TR_PHONE"].ToString(); model.Email = DR["TR_EMAIL"].ToString(); List.Add(model); } DR.Close(); DR.Dispose(); } return(List); }
/// <summary> /// 查询所有 /// </summary> /// <param name="CourseLbID">类别ID</param> /// <returns></returns> public List <Model.CourseSystem> GetList(int PageCount, int PageSize, int CourseLbID) { string strSql = ""; string RedCountsql = ""; int RowTotal = (PageCount - 1) * PageSize;// RedCountsql = "(select count(CR_ID) from [AP_COURSE] where [CR_CATEGORY_ID]='" + CourseLbID + "' ) as RowTotal"; strSql = "Select Top " + PageSize + " *," + RedCountsql + " FROM [AP_COURSE] where [CR_ID] not in (Select Top " + RowTotal + " [CR_ID] from [AP_COURSE] Where [CR_CATEGORY_ID]='" + CourseLbID + "' ORDER BY CR_ID DESC ) And [CR_CATEGORY_ID]='" + CourseLbID + "' ORDER BY CR_ID DESC"; List <Model.CourseSystem> List = new List <Model.CourseSystem>(); SqlParameter[] parameters = { }; SqlDataReader DR; if (GetCourseSysSetupInfo()) { DR = CourseSys.ExecuteReader(strSql.ToString(), parameters); } else { DR = ExamSys.ExecuteReader(strSql.ToString(), parameters); } using (DR) { while (DR.Read()) { Model.CourseSystem model = new Model.CourseSystem(); model.ID = int.Parse(DR["CR_ID"].ToString()); model.Title = DR["CR_COURSE_NAME"].ToString(); model.RowTotal = int.Parse(DR["RowTotal"].ToString()); model.Credit = int.Parse(DR["CR_CREDIT"].ToString()); model.Money = int.Parse(DR["CR_MONEY"].ToString()); List.Add(model); } DR.Close(); DR.Dispose(); } return(List); }
/// <summary> /// 统计分值 /// </summary> /// <param name="Type">类型(单选/多选/不定项选/判断)</param> /// <param name="PapersID">试卷ID</param> /// <returns></returns> private List <Model.PapersTitleList> nSumScore(string Type, int PapersID) { string strSql = ""; if (Type != "") { strSql = "SELECT SUM([Score]) as sumscore FROM [ExaminationSystem].[Exam].[PapersTitleList] where PapersID='" + PapersID + "' and Type='" + Type + "'"; } else { strSql = "SELECT SUM([Score]) as sumscore FROM [ExaminationSystem].[Exam].[PapersTitleList] where PapersID='" + PapersID + "'"; } List <Model.PapersTitleList> List = new List <Model.PapersTitleList>(); SqlParameter[] parameters = { }; SqlDataReader DR; DR = ExamSys.ExecuteReader(strSql.ToString(), parameters); using (DR) { while (DR.Read()) { Model.PapersTitleList model = new Model.PapersTitleList(); if (DR["sumscore"].ToString() == "") { model.Score = 0; } else { model.Score = int.Parse(DR["sumscore"].ToString()); } List.Add(model); } DR.Close(); DR.Dispose(); } return(List); }
/// <summary> /// 添加 /// </summary> /// <param name="model"></param> /// <returns></returns> public int Add(Model.Results model) { int rowsAffected; SqlParameter[] parameters = { new SqlParameter("@ReturnValue", SqlDbType.Int), new SqlParameter("@UserID", SqlDbType.Int), new SqlParameter("@PapersID", SqlDbType.Int), new SqlParameter("@TitleListID", SqlDbType.Int), new SqlParameter("@Answer", SqlDbType.NVarChar,50), new SqlParameter("@Time", SqlDbType.NVarChar, 50) }; parameters[0].Direction = ParameterDirection.Output; parameters[1].Value = model.UserID; parameters[2].Value = model.PapersID; parameters[3].Value = model.TitleListID; parameters[4].Value = model.Answer; parameters[5].Value = model.Time; ExamSys.RunProcedure("Results_Add", parameters, out rowsAffected); return((int)parameters[0].Value); }
/// <summary> /// 查询所有 /// </summary> /// <returns></returns> public List <Model.CourseSystem> GetList() { string strSql = ""; strSql = "Select * FROM [AP_COURSE_CATEGORY]"; List <Model.CourseSystem> List = new List <Model.CourseSystem>(); SqlParameter[] parameters = { }; SqlDataReader DR; if (GetCourseSysSetupInfo()) { DR = CourseSys.ExecuteReader(strSql.ToString(), parameters); } else { DR = ExamSys.ExecuteReader(strSql.ToString(), parameters); } using (DR) { while (DR.Read()) { Model.CourseSystem model = new Model.CourseSystem(); model.CourseLbID = int.Parse(DR["CRC_ID"].ToString()); model.Name = DR["CRC_CATEGORY_NAME"].ToString(); model.Up = int.Parse(DR["CRC_PARENT_ID"].ToString()); model.CourseCount = GetCourseCount(int.Parse(DR["CRC_ID"].ToString())); //model.CourseCount = 1; List.Add(model); } DR.Close(); DR.Dispose(); } return(List); }
/// <summary> /// 显示详细 /// </summary> /// <param name="ID">ID</param> /// <returns></returns> public List <Model.CourseSystem> Details(int ID) { string strSql = ""; strSql = "Select * FROM [AP_COURSE] Where [CR_ID]='" + ID + "'"; List <Model.CourseSystem> List = new List <Model.CourseSystem>(); SqlParameter[] parameters = { }; SqlDataReader DR; if (GetCourseSysSetupInfo()) { DR = CourseSys.ExecuteReader(strSql.ToString(), parameters); } else { DR = ExamSys.ExecuteReader(strSql.ToString(), parameters); } using (DR) { while (DR.Read()) { Model.CourseSystem model = new Model.CourseSystem(); model.ID = int.Parse(DR["CR_ID"].ToString()); model.Title = DR["CR_COURSE_NAME"].ToString(); model.Abstract = DR["CR_DESC"].ToString(); model.Outline = DR["CR_OUTLINE"].ToString(); List.Add(model); } DR.Close(); DR.Dispose(); } return(List); }
/// <summary> /// 更新 /// </summary> /// <param name="model"></param> /// <returns></returns> public int Updata(Model.HardInfo model) { int rowsAffected; SqlParameter[] parameters = { new SqlParameter("@ReturnValue", SqlDbType.Int), new SqlParameter("@HostName", SqlDbType.NVarChar, 500), new SqlParameter("@CpuID", SqlDbType.NVarChar, 500), new SqlParameter("@HardDiskID", SqlDbType.NVarChar, 500), new SqlParameter("@NetCardMAC", SqlDbType.NVarChar, 500), new SqlParameter("@VolOf", SqlDbType.NVarChar, 500), new SqlParameter("@NetCardIP", SqlDbType.NVarChar, 500), }; parameters[0].Direction = ParameterDirection.Output; parameters[1].Value = model.HostName; parameters[2].Value = model.CpuID; parameters[3].Value = model.HardDiskID; parameters[4].Value = model.NetCardMAC; parameters[5].Value = model.VolOf; parameters[6].Value = model.NetCardIP; ExamSys.RunProcedure("HardInfo_Updata", parameters, out rowsAffected); return((int)parameters[0].Value); }
public List <Model.Results> GetList(int PageCount, int PageSize, string Sort, string Search, string Type) { string strSql = ""; strSql = "SELECT DISTINCT Exam.Results.Time FROM Exam.Papers INNER JOIN Exam.Results ON Exam.Papers.ID = Exam.Results.PapersID "; List <Model.Results> List = new List <Model.Results>(); DataTable dt_ResultaForTime = DBUtility.ExamSys.gettb(strSql); int dtCount = dt_ResultaForTime.Rows.Count; //DROP tempResultsForTime表 try { strSql = "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Exam].[tempResultsForTime]') AND type in (N'U')) DROP TABLE [Exam].[tempResultsForTime]"; DBUtility.ExamSys.non(strSql); } catch { } if (dtCount > 0) { for (int i = 0; i < dtCount; i++) { if (i == 0) { strSql = "Select Top 1 [Score],[UserID],[PapersID],[Time] Into tempResultsForTime FROM Results Where Time='" + dt_ResultaForTime.Rows[i][0] + "'"; } else { strSql = "Insert Into tempResultsForTime([Score],[UserID],[PapersID],[Time]) Select Top 1 [Score],[UserID],[PapersID],[Time] FROM Results Where Time='" + dt_ResultaForTime.Rows[i][0] + "'"; } DBUtility.ExamSys.non(strSql); } //string strSql = ""; string RedCountsql = ""; int RowTotal = (PageCount - 1) * PageSize;// RedCountsql = "(select count(*) from [tempResultsForTime] where " + Type + " like '%" + Search + "%') as RowTotal"; strSql = "Select Top " + PageSize + " *," + RedCountsql + " FROM [tempResultsForTime] where [Time] not in (Select Top " + RowTotal + " [Time] from [tempResultsForTime] where " + Type + " like '%" + Search + "%' ORDER BY " + Sort + ") and " + Type + " like '%" + Search + "%' ORDER BY " + Sort + ""; //strSql = "Select * FROM tempResultsForTime ORDER BY Time DESC"; SqlParameter[] parameters = { }; using (SqlDataReader DR = ExamSys.ExecuteReader(strSql.ToString(), parameters)) { while (DR.Read()) { Model.Results model = new Model.Results(); model.Time = DR["Time"].ToString(); model.UserID = int.Parse(DR["UserID"].ToString()); model.UserName = getUserName(model.UserID); model.PapersID = int.Parse(DR["PapersID"].ToString()); model.PapersName = getPapersName(model.PapersID); if (DR["Score"].ToString() == "") { model.Percentage = ""; model.Score = 0; model.SysScore = 0; } else { model.Score = getScore(model.UserID, model.PapersID, model.Time); model.SysScore = getSysScore(model.UserID, model.PapersID, model.Time); model.Percentage = getPercentage(model.Score, model.SysScore); } model.RowTotal = int.Parse(DR["RowTotal"].ToString()); List.Add(model); } DR.Close(); DR.Dispose(); } } else { } return(List); }
/// <summary> /// 执行SQL语句 /// </summary> public DataTable ExecSQL(string strSql, params SqlParameter[] parames) { DataTable dt = ExamSys.Query(strSql.ToString(), parames).Tables[0]; return(dt); }
/// <summary> /// 查询所有 /// </summary> /// <param name="UserID">用户ID</param> /// <returns></returns> public List <Model.Results> GetListUserID(int UserID) { string strSql = ""; strSql = "Select DISTINCT [Time] FROM Results Where UserID='" + UserID + "'"; List <Model.Results> List = new List <Model.Results>(); DataTable dt_ResultaForTime = DBUtility.ExamSys.gettb(strSql); int dtCount = dt_ResultaForTime.Rows.Count; //DROP tempResultsForTime表 try { strSql = "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Exam].[tempResultsForTime]') AND type in (N'U')) DROP TABLE [Exam].[tempResultsForTime]"; DBUtility.ExamSys.non(strSql); } catch { } if (dtCount > 0) { for (int i = 0; i < dtCount; i++) { if (i == 0) { strSql = "Select Top 1 [Score],[UserID],[PapersID],[Time] Into tempResultsForTime FROM Results Where Time='" + dt_ResultaForTime.Rows[i][0] + "'"; } else { strSql = "Insert Into tempResultsForTime([Score],[UserID],[PapersID],[Time]) Select Top 1 [Score],[UserID],[PapersID],[Time] FROM Results Where Time='" + dt_ResultaForTime.Rows[i][0] + "'"; } DBUtility.ExamSys.non(strSql); } strSql = "Select * FROM tempResultsForTime ORDER BY Time DESC"; SqlParameter[] parameters = { }; using (SqlDataReader DR = ExamSys.ExecuteReader(strSql.ToString(), parameters)) { while (DR.Read()) { Model.Results model = new Model.Results(); model.Time = DR["Time"].ToString(); model.UserID = int.Parse(DR["UserID"].ToString()); model.UserName = getUserName(model.UserID); model.PapersID = int.Parse(DR["PapersID"].ToString()); model.PapersName = getPapersName(model.PapersID); if (DR["Score"].ToString() == "") { model.Percentage = ""; model.Score = 0; model.SysScore = 0; } else { model.Score = getScore(model.UserID, model.PapersID, model.Time); model.SysScore = getSysScore(model.UserID, model.PapersID, model.Time); model.Percentage = getPercentage(model.Score, model.SysScore); } List.Add(model); } DR.Close(); DR.Dispose(); } } else { } return(List); }
/// <summary> /// 详细信息 /// </summary> /// <param name="UserID">用户ID</param> /// <param name="PapersID">试卷ID</param> /// <returns></returns> public List <Model.Results> Details(int UserID, int PapersID, string Time) { DataTable dt = DBUtility.ExamSys.gettb("SELECT SUM(Score)FROM [Results] WHERE [UserID]='" + UserID + "' AND [PapersID]='" + PapersID + "' AND [Time]='" + Time + "'"); //改卷 if (dt.Rows[0][0].ToString() == "") { //根据用户ID和试卷ID查询[成绩单]中所有题目 DataTable dt_Results = DBUtility.ExamSys.gettb("SELECT [TitleListID],[Answer] FROM [Results] WHERE [UserID]='" + UserID + "' AND [PapersID]='" + PapersID + "' AND [Time]='" + Time + "'"); int Count = dt_Results.Rows.Count; for (int i = 0; i < Count; i++) { //题目ID int TitleListID = int.Parse(dt_Results.Rows[i][0].ToString()); //用户答案 string Answer = dt_Results.Rows[i][1].ToString(); if (Answer == "T" || Answer == "F") { //判断题 DataTable dt_TitleList = DBUtility.ExamSys.gettb("SELECT [T_F],[Score] FROM [PapersTitleList] WHERE ID='" + TitleListID + "' AND PapersID='" + PapersID + "'"); string SysScore = dt_TitleList.Rows[0]["Score"].ToString(); if (dt_TitleList.Rows[0]["T_F"].ToString() == Answer) { //用户答案正确 DBUtility.ExamSys.non("update Results set [Score]='" + SysScore + "',[SysScore]='" + SysScore + "' Where [TitleListID]='" + TitleListID + "' AND PapersID='" + PapersID + "' AND [Time]='" + Time + "'"); } else { //用户答案错误 DBUtility.ExamSys.non("update Results set [Score]='0',[SysScore]='" + SysScore + "' Where [TitleListID]='" + TitleListID + "' AND PapersID='" + PapersID + "' AND [Time]='" + Time + "'"); } } else { if (Answer == "") { DataTable dt_TitleList = DBUtility.ExamSys.gettb("SELECT [Score],[Type] FROM [PapersTitleList] WHERE ID='" + TitleListID + "' AND PapersID='" + PapersID + "'"); string Type = dt_TitleList.Rows[0]["Type"].ToString(); string SysScore = dt_TitleList.Rows[0]["Score"].ToString(); if (Type == "单选") { //用户答案错误 DBUtility.ExamSys.non("update Results set [Score]='0',[SysScore]='" + SysScore + "' Where [TitleListID]='" + TitleListID + "' AND PapersID='" + PapersID + "' AND [Time]='" + Time + "'"); } else { if (Type == "多选") { //用户答案错误 DBUtility.ExamSys.non("update Results set [Score]='0',[SysScore]='" + SysScore + "' Where [TitleListID]='" + TitleListID + "' AND PapersID='" + PapersID + "' AND [Time]='" + Time + "'"); } else { if (Type == "不定项选") { //用户答案错误 DBUtility.ExamSys.non("update Results set [Score]='0',[SysScore]='" + SysScore + "' Where [TitleListID]='" + TitleListID + "' AND PapersID='" + PapersID + "' AND [Time]='" + Time + "'"); } else { DBUtility.ExamSys.non("update Results set [Score]='0',[SysScore]='" + SysScore + "' Where [TitleListID]='" + TitleListID + "' AND PapersID='" + PapersID + "' AND [Time]='" + Time + "'"); } } } } else { DataTable dt_TitleList = DBUtility.ExamSys.gettb("SELECT [Score],[Type] FROM [PapersTitleList] WHERE ID='" + TitleListID + "' AND PapersID='" + PapersID + "'"); string Type = dt_TitleList.Rows[0]["Type"].ToString(); string SysScore = dt_TitleList.Rows[0]["Score"].ToString(); if (Type == "单选") { int ID = int.Parse(Answer); DataTable dt_PapersOptionsList = DBUtility.ExamSys.gettb("SELECT * FROM [PapersOptionsList] Where TitleListID='" + TitleListID + "' AND PapersID='" + PapersID + "' AND ID='" + ID + "'"); if (dt_PapersOptionsList.Rows[0]["T_F"].ToString() == "") { //用户答案错误 DBUtility.ExamSys.non("update Results set [Score]='0',[SysScore]='" + SysScore + "' Where [TitleListID]='" + TitleListID + "' AND PapersID='" + PapersID + "' AND [Time]='" + Time + "'"); } else { //用户答案正确 DBUtility.ExamSys.non("update Results set [Score]='" + SysScore + "',[SysScore]='" + SysScore + "' Where [TitleListID]='" + TitleListID + "' AND PapersID='" + PapersID + "' AND [Time]='" + Time + "'"); } } else { if (Type == "多选") { DataTable dt_PapersOptionsList = DBUtility.ExamSys.gettb("SELECT * FROM [PapersOptionsList] Where TitleListID='" + TitleListID + "' AND PapersID='" + PapersID + "' ORDER BY [ID] ASC"); string sAnswer = null; for (int j = 0; j < dt_PapersOptionsList.Rows.Count; j++) { if (dt_PapersOptionsList.Rows[j]["T_F"].ToString() != "") { sAnswer = sAnswer + dt_PapersOptionsList.Rows[j]["ID"].ToString() + ","; } } int length = sAnswer.Length; sAnswer = sAnswer.Substring(0, length - 1); string myAnswer = Answer; int[] c = StringToInt(myAnswer); myAnswer = ""; for (int ii = 0; ii < c.Count(); ii++) { myAnswer = myAnswer + c[ii] + ","; } int length1 = myAnswer.Length; myAnswer = myAnswer.Substring(0, length - 1); if (sAnswer == myAnswer) { //用户答案正确 DBUtility.ExamSys.non("update Results set [Score]='" + SysScore + "',[SysScore]='" + SysScore + "' Where [TitleListID]='" + TitleListID + "' AND PapersID='" + PapersID + "' AND [Time]='" + Time + "'"); } else { //用户答案错误 DBUtility.ExamSys.non("update Results set [Score]='0',[SysScore]='" + SysScore + "' Where [TitleListID]='" + TitleListID + "' AND PapersID='" + PapersID + "' AND [Time]='" + Time + "'"); } } else { if (Type == "不定项选") { DataTable dt_PapersOptionsList = DBUtility.ExamSys.gettb("SELECT * FROM [PapersOptionsList] Where TitleListID='" + TitleListID + "' AND PapersID='" + PapersID + "' ORDER BY [ID] ASC"); string sAnswer = ""; for (int j = 0; j < dt_PapersOptionsList.Rows.Count; j++) { if (dt_PapersOptionsList.Rows[j]["T_F"].ToString() != "") { sAnswer = sAnswer + dt_PapersOptionsList.Rows[j]["ID"].ToString() + ","; } } int length = sAnswer.Length; sAnswer = sAnswer.Substring(0, length - 1); string myAnswer = Answer; int[] c = StringToInt(myAnswer); myAnswer = ""; for (int ii = 0; ii < c.Count(); ii++) { myAnswer = myAnswer + c[ii] + ","; } int length1 = myAnswer.Length; myAnswer = myAnswer.Substring(0, length - 1); if (sAnswer == myAnswer) { //用户答案正确 DBUtility.ExamSys.non("update Results set [Score]='" + SysScore + "',[SysScore]='" + SysScore + "' Where [TitleListID]='" + TitleListID + "' AND PapersID='" + PapersID + "' AND [Time]='" + Time + "'"); } else { //用户答案错误 DBUtility.ExamSys.non("update Results set [Score]='0',[SysScore]='" + SysScore + "' Where [TitleListID]='" + TitleListID + "' AND PapersID='" + PapersID + "' AND [Time]='" + Time + "'"); } } else { //值选题 int ID = int.Parse(Answer); DataTable dt_PapersOptionsList = DBUtility.ExamSys.gettb("SELECT * FROM [PapersOptionsList] Where TitleListID='" + TitleListID + "' AND PapersID='" + PapersID + "' AND ID='" + ID + "'"); //用户值答案分值 DBUtility.ExamSys.non("update Results set [Score]='" + dt_PapersOptionsList.Rows[0]["Score"].ToString() + "',[SysScore]='" + SysScore + "' Where [TitleListID]='" + TitleListID + "' AND PapersID='" + PapersID + "' AND [Time]='" + Time + "'"); } } } } } } //For结束 string strSql = ""; strSql = "Select DISTINCT [UserID],[PapersID] FROM Results WHERE [UserID]='" + UserID + "' AND [PapersID]='" + PapersID + "' AND [Time]='" + Time + "'"; List <Model.Results> List = new List <Model.Results>(); SqlParameter[] parameters = { }; using (SqlDataReader DR = ExamSys.ExecuteReader(strSql.ToString(), parameters)) { while (DR.Read()) { Model.Results model = new Model.Results(); model.UserName = getUserName(UserID); model.Score = getScore(UserID, PapersID, Time); model.SysScore = getSysScore(UserID, PapersID, Time); model.PapersName = getPapersName(PapersID); model.Percentage = getPercentage(model.Score, model.SysScore); List.Add(model); } DR.Close(); DR.Dispose(); } return(List); } else { string strSql = ""; strSql = "Select DISTINCT [UserID],[PapersID],[Time] FROM Results WHERE [UserID]='" + UserID + "' AND [PapersID]='" + PapersID + "' AND [Time]='" + Time + "'"; List <Model.Results> List = new List <Model.Results>(); SqlParameter[] parameters = { }; using (SqlDataReader DR = ExamSys.ExecuteReader(strSql.ToString(), parameters)) { while (DR.Read()) { Model.Results model = new Model.Results(); model.UserName = getUserName(UserID); model.Score = getScore(UserID, PapersID, Time); model.SysScore = getSysScore(UserID, PapersID, Time); model.PapersName = getPapersName(PapersID); model.Percentage = getPercentage(model.Score, model.SysScore); model.Time = DR["Time"].ToString(); List.Add(model); } DR.Close(); DR.Dispose(); } return(List); } }