/// <summary> /// 添加 /// 扩展 /// </summary> /// <param name="model"></param> /// <returns></returns> public int AddExtend(Model.Results model) { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into Company("); strSql.Append("UserID,PapersID,TitleListID,Answer,Time,SubmitTime)"); strSql.Append(" values ("); strSql.Append("@UserID,@PapersID,@TitleListID,@Answer,@Time,@SubmitTime)"); strSql.Append(";select @@IDENTITY"); SqlParameter[] parameters = { 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), new SqlParameter("@SubmitTime", SqlDbType.DateTime)//添加提交时间 }; parameters[0].Value = model.UserID; parameters[1].Value = model.PapersID; parameters[2].Value = model.TitleListID; parameters[3].Value = model.Answer; parameters[4].Value = model.Time; parameters[5].Value = model.SubmitTime; object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters); if (obj == null) { return(0); } else { return(Convert.ToInt32(obj)); } }
/// <summary> /// 更新一条数据 /// </summary> public bool Update(Model.Results model) { StringBuilder strSql = new StringBuilder(); strSql.Append("update Results set "); strSql.Append("Answer=@Answer,"); strSql.Append("Time=@Time"); strSql.Append(" where UserID=@UserID and PapersID=@PapersID and TitleListID=@TitleListID"); SqlParameter[] parameters = { new SqlParameter("@Answer", SqlDbType.NVarChar, 50), new SqlParameter("@Time", SqlDbType.NVarChar, 50), new SqlParameter("@UserID", SqlDbType.Int), new SqlParameter("@PapersID", SqlDbType.Int), new SqlParameter("@TitleListID", SqlDbType.Int) }; parameters[0].Value = model.Answer; parameters[1].Value = model.Time; parameters[2].Value = model.UserID; parameters[3].Value = model.PapersID; parameters[4].Value = model.TitleListID; int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters); if (rows > 0) { return(true); } else { return(false); } }
/// <summary> /// 提交答案 /// </summary> /// <param name="Type">类型(单选/多选/不定项选/判断)</param> /// <param name="PapersID">试卷ID</param> /// <param name="gv">GridView</param> private void Submit(string Type, int PapersID, GridView gv, string Time) { BLL.PapersTitleList bll = new BLL.PapersTitleList(); if (bll.nGetList(Type, PapersID)) { for (int i = 0; i < gv.Rows.Count; i++) { Label lb = (Label)gv.Rows[i].FindControl("lb_ID"); int TitleListID = int.Parse(lb.Text); string Str_radio = Request.Form["radio" + TitleListID]; //Response.Write(Type + "题目:" + TitleListID + " 答案:" + Str_radio + "<br/>"); BLL.Results bll1 = new BLL.Results(); Model.Results model = new Model.Results(); model.UserID = int.Parse(lb_U.Text); model.PapersID = PapersID; model.TitleListID = TitleListID; model.Answer = Str_radio; model.Time = Time; //Common.Function.Alert(model.UserID.ToString()); //bll1.Add(model); } } else { } }
/// <summary> /// 添加 /// </summary> public void Add(Model.Results model) { if (dal.Add(model) > 0) { //Common.Function.AlertRedirect("交卷成功!", "/Aspx/Success.htm"); } else { //Common.Function.AlertRedirect("交卷失败!系统繁忙请稍后再试!", "/Aspx/Failure.htm"); } }
/// <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> public bool UpdateExtend(Model.Results model) { return(dal.UpdateExtend(model)); }
/// <summary> /// 添加 /// add by wangke 2016-12-12 /// </summary> public int AddResult(Model.Results model) { return(dal.Add(model)); }
/// <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); }
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> /// 详细信息 /// </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); } }