/// <summary>
/// 只获取试卷的基本信息
/// </summary>
public PaperByRandomSelection SelectByID2(string id)
{
string sql = "select * from T_PaperByRandomSelection where paperID=@paperID";
SqlParameter[] parms =
{
new SqlParameter("@paperID", id)
};
PaperByRandomSelection paper = null;
using (SqlDataReader dr = DBHelper2.Select(sql, parms))
{
if (dr.Read())
{
paper = new PaperByRandomSelection();
paper.PaperID = Convert.ToInt32(dr["paperID"]);
paper.PaperName = dr["paperName"].ToString();
paper.PaperType.DeptName = dr["paperType"].ToString();
paper.Creator.Name = dr["creator"].ToString();
paper.CreatedTime = Convert.ToDateTime(dr["createdTime"]);
paper.JudgeSum = Convert.ToInt32(dr["judgeSum"]);
paper.SingleSelectionSum = Convert.ToInt32(dr["singleSelectionSum"]);
paper.MultiSelectionSum = Convert.ToInt32(dr["multiSelectionSum"]);
}
}
return(paper);
}
public List <SubjectOfSingleSelection> SelectList()
{
List <SubjectOfSingleSelection> list = new List <SubjectOfSingleSelection>();
string sql = "select t1.*,t2.cateName from T_SubjectOfSingleSelection t1 left join T_SubjectTypeCategory t2 on t1.cateID=t2.cateID order by t1.cateID";
using (SqlDataReader dr = DBHelper2.Select(sql, null))
{
while (dr.Read())
{
SubjectOfSingleSelection subject = new SubjectOfSingleSelection();
subject.Id = Convert.ToInt32(dr["id"]);
subject.Question = dr["question"].ToString();
subject.SelectA = dr["selectA"].ToString();
subject.SelectB = dr["selectB"].ToString();
subject.SelectC = dr["selectC"].ToString();
subject.SelectD = dr["selectD"].ToString();
subject.Answer = Convert.ToChar(dr["answer"]);
subject.Category = new SubjectTypeCategory()
{
CateName = dr["cateName"].ToString()
};
list.Add(subject);
}
}
return(list);
}
public List <PaperByManualSelection> SelectList(int deptID)
{
List <PaperByManualSelection> list = new List <PaperByManualSelection>();
string sql = "select * from T_PaperByManualSelection where deptID=@deptID order by paperID desc";
SqlParameter[] parms =
{
new SqlParameter("@deptID", deptID)
};
using (SqlDataReader dr = DBHelper2.Select(sql, parms))
{
while (dr.Read())
{
PaperByManualSelection paper = new PaperByManualSelection();
paper.PaperID = Convert.ToInt32(dr["paperID"]);
paper.PaperName = dr["paperName"].ToString();
paper.PaperType.DeptName = dr["paperType"].ToString();
paper.Creator.Name = dr["creator"].ToString();
paper.CreatedTime = Convert.ToDateTime(dr["createdTime"]);
list.Add(paper);
}
}
return(list);
}
public List <SubjectOfSimpleAnswer> SelectList(List <int> idList)
{
List <SubjectOfSimpleAnswer> list = new List <SubjectOfSimpleAnswer>();
string sql = "select * from T_SubjectOfSimpleAnswer where id in(";
foreach (int id in idList)
{
sql += id + ",";
}
sql = sql.Remove(sql.Length - 1);
sql += ")";
using (SqlDataReader dr = DBHelper2.Select(sql, null))
{
while (dr.Read())
{
SubjectOfSimpleAnswer subject = new SubjectOfSimpleAnswer();
subject.Index = list.Count + 1;
subject.Id = Convert.ToInt32(dr["id"]);
subject.Question = dr["question"].ToString();
subject.Answer = dr["answer"].ToString();
list.Add(subject);
}
}
return(list);
}
public List <SubjectOfSimpleAnswer> SelectList(int cateID)
{
List <SubjectOfSimpleAnswer> list = new List <SubjectOfSimpleAnswer>();
string sql = "select t1.*,t2.cateName from T_SubjectOfSimpleAnswer t1 left join T_SubjectTypeCategory t2 on t1.cateID=t2.cateID where t1.cateID=@cateID";
SqlParameter[] parms =
{
new SqlParameter("@cateID", cateID)
};
using (SqlDataReader dr = DBHelper2.Select(sql, parms))
{
while (dr.Read())
{
SubjectOfSimpleAnswer subject = new SubjectOfSimpleAnswer();
subject.Id = Convert.ToInt32(dr["id"]);
subject.Question = dr["question"].ToString();
subject.Answer = dr["answer"].ToString();
subject.Category = new SubjectTypeCategory()
{
CateName = dr["cateName"].ToString()
};
list.Add(subject);
}
}
return(list);
}
/// <summary>
/// 除了试卷的基本信息外，也包含各类题库集合(随机生成，含各题的分数）
/// </summary>
public PaperByRandomSelection SelectByID(int testID, int paperID)
{
PaperByRandomSelection paper = SelectByID(paperID.ToString());
SqlConnection conn = new SqlConnection(DBHelper2.connStr);
try
{
conn.Open();
//填空题、判断题、单选题、多选题中每题的分数
string sql = "select top 4 * from T_Test_Subject_Scores where testID=@testID order by subjectType asc";
SqlParameter[] parms =
{
new SqlParameter("@testID", testID)
};
using (SqlDataReader dr = DBHelper2.Select(conn, sql, parms))
{
while (dr.Read())
{
int subjectType = Convert.ToInt32(dr["subjectType"]);
switch ((SubjectType)subjectType)
{
case SubjectType.Judge:
foreach (SubjectOfJudge subject in paper.JudgeList)
{
subject.Scores = Convert.ToInt32(dr["scores"]);
}
break;
case SubjectType.SingleSelection:
foreach (SubjectOfSingleSelection subject in paper.SingleSelectionList)
{
subject.Scores = Convert.ToInt32(dr["scores"]);
}
break;
case SubjectType.MultiSelection:
foreach (SubjectOfMultiSelection subject in paper.MultiSelectionList)
{
subject.Scores = Convert.ToInt32(dr["scores"]);
}
break;
default:
break;
}
}
}
}
catch (Exception ex)
{
throw ex;
}
finally { conn.Close(); }
return(paper);
}
public void Delete(string id)
{
string sql = "delete from T_SubjectOfSimpleAnswer where id=@id";
SqlParameter[] parms =
{
new SqlParameter("@id", id)
};
DBHelper2.Delete(sql, parms);
}
public void Delete(string id)
{
string sql = "delete from T_PaperByRandomSelection where paperID=@paperID";
SqlParameter[] parms =
{
new SqlParameter("@paperID", id)
};
DBHelper2.Delete(sql, parms);
}
public void Insert(SubjectTypeCategory obj)
{
string sql = "insert into T_SubjectTypeCategory(cateName,subjectType) values(@cateName,@subjectType)";
SqlParameter[] parms =
{
new SqlParameter("@cateName", obj.CateName),
new SqlParameter("@subjectType", (int)obj.SubjectType)
};
DBHelper2.Insert(sql, parms);
}
public void Insert(SubjectOfSimpleAnswer obj)
{
string sql = "insert into T_SubjectOfSimpleAnswer(question,answer,cateID) values(@question,@answer,@cateID)";
SqlParameter[] parms =
{
new SqlParameter("@question", obj.Question),
new SqlParameter("@answer", obj.Answer),
new SqlParameter("@cateID", obj.Category.CateID)
};
DBHelper2.Insert(sql, parms);
}
public int SelectCount()
{
int count = 0;
string sql = "select count(*) from T_SubjectOfSingleSelection";
using (SqlDataReader dr = DBHelper2.Select(sql, null))
{
if (dr.Read())
{
count = Convert.ToInt32(dr[0]);
}
}
return(count);
}
public int SelectCount(int cateID)
{
int count = 0;
string sql = "select count(*) from T_SubjectOfSingleSelection where cateID=@cateID";
SqlParameter[] parm = { new SqlParameter("@cateID", cateID) };
using (SqlDataReader dr = DBHelper2.Select(sql, parm))
{
if (dr.Read())
{
count = Convert.ToInt32(dr[0]);
}
}
return(count);
}
public void Insert(SubjectOfMultiSelection obj)
{
string sql = "insert into T_SubjectOfMultiSelection(question,selectA,selectB,selectC,selectD,answer,cateID) values(@question,@selectA,@selectB,@selectC,@selectD,@answer,@cateID)";
SqlParameter[] parms =
{
new SqlParameter("@question", obj.Question),
new SqlParameter("@selectA", obj.SelectA),
new SqlParameter("@selectB", obj.SelectB),
new SqlParameter("@selectC", obj.SelectC),
new SqlParameter("@selectD", obj.SelectD),
new SqlParameter("@answer", obj.Answer),
new SqlParameter("@cateID", obj.Category.CateID)
};
DBHelper2.Insert(sql, parms);
}
public List <SubjectTypeCategory> SelectListBySubjectType(SubjectType subjectType)
{
string sql = "select * from T_SubjectTypeCategory where subjectType=@subjectType";
SqlParameter[] parms = { new SqlParameter("@subjectType", (int)subjectType) };
List <SubjectTypeCategory> list = new List <SubjectTypeCategory>();
using (SqlDataReader dr = DBHelper2.Select(sql, parms))
{
while (dr.Read())
{
SubjectTypeCategory category = new SubjectTypeCategory();
category.CateID = Convert.ToInt32(dr["cateID"]);
category.CateName = dr["cateName"].ToString();
list.Add(category);
}
return(list);
}
}
public void Insert(PaperByRandomSelection obj)
{
string sql = "insert into T_PaperByRandomSelection(paperName,deptID,paperType,creator,createdTime,judgeSum,singleSelectionSum,multiSelectionSum,judgeCateID,singleSelectionCateID,multiSelectionCateID) values(@paperName,@deptID,@paperType,@creator,@createdTime,@judgeSum,@singleSelectionSum,@multiSelectionSum,@judgeCateID,@singleSelectionCateID,@multiSelectionCateID)";
SqlParameter[] parms =
{
new SqlParameter("@paperName", obj.PaperName),
new SqlParameter("@deptID", obj.PaperType.DeptID),
new SqlParameter("@paperType", obj.PaperType.DeptName),
new SqlParameter("@creator", obj.Creator.Name),
new SqlParameter("@createdTime", obj.CreatedTime),
new SqlParameter("@judgeSum", obj.JudgeSum),
new SqlParameter("@singleSelectionSum", obj.SingleSelectionSum),
new SqlParameter("@multiSelectionSum", obj.MultiSelectionSum),
new SqlParameter("@judgeCateID", obj.JudgeCateID),
new SqlParameter("@singleSelectionCateID", obj.SingleSelectionCateID),
new SqlParameter("@multiSelectionCateID", obj.MultiSelectionCateID)
};
DBHelper2.Insert(sql, parms);
}
public List <PaperByManualSelection> SelectList()
{
List <PaperByManualSelection> list = new List <PaperByManualSelection>();
string sql = "select * from T_PaperByManualSelection order by paperID desc";
using (SqlDataReader dr = DBHelper2.Select(sql, null))
{
while (dr.Read())
{
PaperByManualSelection paper = new PaperByManualSelection();
paper.PaperID = Convert.ToInt32(dr["paperID"]);
paper.PaperName = dr["paperName"].ToString();
paper.PaperType.DeptName = dr["paperType"].ToString();
paper.Creator.Name = dr["creator"].ToString();
paper.CreatedTime = Convert.ToDateTime(dr["createdTime"]);
list.Add(paper);
}
}
return(list);
}
public List <PaperByRandomSelection> SelectList()
{
string sql = "select * from T_PaperByRandomSelection order by paperID desc";
List <PaperByRandomSelection> list = new List <PaperByRandomSelection>();
using (SqlDataReader dr = DBHelper2.Select(sql, null))
{
while (dr.Read())
{
PaperByRandomSelection paper = new PaperByRandomSelection();
paper.PaperID = Convert.ToInt32(dr["paperID"]);
paper.PaperName = dr["paperName"].ToString();
paper.PaperType.DeptName = dr["paperType"].ToString();
paper.Creator.Name = dr["creator"].ToString();
paper.CreatedTime = Convert.ToDateTime(dr["createdTime"]);
paper.JudgeSum = Convert.ToInt32(dr["judgeSum"]);
paper.SingleSelectionSum = Convert.ToInt32(dr["singleSelectionSum"]);
paper.MultiSelectionSum = Convert.ToInt32(dr["multiSelectionSum"]);
list.Add(paper);
}
return(list);
}
}
public void UpdateTestRecorderAnswer(int recorderID, List <SubjectOfFillBlank> list1, List <SubjectOfJudge> list2, List <SubjectOfSingleSelection> list3, List <SubjectOfMultiSelection> list4, List <SubjectOfSimpleAnswer> list5)
{
SqlConnection conn = new SqlConnection(DBHelper2.connStr);
try
{
conn.Open();
//填空题
if (list1 != null)
{
foreach (SubjectOfFillBlank subject in list1)
{
string sql = "update T_TestRecorder_Answer set answer1=@answer1 where recorderID=@recorderID and subjectType=1 and subjectID=@subjectID";
SqlParameter[] parms =
{
new SqlParameter("@answer1", subject.Answer),
new SqlParameter("@recorderID", recorderID),
new SqlParameter("@subjectID", subject.Id)
};
DBHelper2.Update(sql, parms);
}
}
//判断题
if (list2 != null)
{
foreach (SubjectOfJudge subject in list2)
{
string sql = "update T_TestRecorder_Answer set answer2=@answer2 where recorderID=@recorderID and subjectType=2 and subjectID=@subjectID";
SqlParameter[] parms =
{
new SqlParameter("@answer2", subject.Answer),
new SqlParameter("@recorderID", recorderID),
new SqlParameter("@subjectID", subject.Id)
};
DBHelper2.Update(sql, parms);
}
}
//单选题
if (list3 != null)
{
foreach (SubjectOfSingleSelection subject in list3)
{
string sql = "update T_TestRecorder_Answer set answer3=@answer3 where recorderID=@recorderID and subjectType=3 and subjectID=@subjectID";
SqlParameter[] parms =
{
new SqlParameter("@answer3", subject.Answer),
new SqlParameter("@recorderID", recorderID),
new SqlParameter("@subjectID", subject.Id)
};
DBHelper2.Update(sql, parms);
}
}
//多选题
if (list4 != null)
{
foreach (SubjectOfMultiSelection subject in list4)
{
string sql = "update T_TestRecorder_Answer set answer4=@answer4 where recorderID=@recorderID and subjectType=4 and subjectID=@subjectID";
SqlParameter[] parms =
{
new SqlParameter("@answer4", subject.Answer),
new SqlParameter("@recorderID", recorderID),
new SqlParameter("@subjectID", subject.Id)
};
DBHelper2.Update(sql, parms);
}
}
//简答题
if (list5 != null)
{
foreach (SubjectOfSimpleAnswer subject in list5)
{
string sql = "update T_TestRecorder_Answer set answer5=@answer5 where recorderID=@recorderID and subjectType=5 and subjectID=@subjectID";
SqlParameter[] parms =
{
new SqlParameter("@answer5", subject.Answer),
new SqlParameter("@recorderID", recorderID),
new SqlParameter("@subjectID", subject.Id)
};
DBHelper2.Update(sql, parms);
}
}
//更新分数
SqlParameter[] parm =
{
new SqlParameter("@recorderID", recorderID)
};
DBHelper.Update(conn, "UP_TestRecorder_Answer_UpdateScores", parm);
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
//评卷：保存成绩
public void UpdateTestRecorderAnswer_Scores(int recorderID, List <SubjectOfFillBlank> list1, List <SubjectOfJudge> list2, List <SubjectOfSingleSelection> list3, List <SubjectOfMultiSelection> list4, List <SubjectOfSimpleAnswer> list5)
{
SqlConnection conn = new SqlConnection(DBHelper2.connStr);
try
{
conn.Open();
string sql = "update T_TestRecorder_Answer set scores=@scores where recorderID=@recorderID and subjectType=@subjectType and subjectID=@subjectID";
SqlParameter[] parms =
{
new SqlParameter("@recorderID", SqlDbType.Int),
new SqlParameter("@scores", SqlDbType.Int),
new SqlParameter("@subjectType", SqlDbType.Int),
new SqlParameter("@subjectID", SqlDbType.Int)
};
parms[0].Value = recorderID;
//填空题
if (list1 != null)
{
foreach (SubjectOfFillBlank subject in list1)
{
parms[1].Value = subject.ScoresForTester;
parms[2].Value = (int)SubjectType.FillBlank;
parms[3].Value = subject.Id;
DBHelper2.Update(sql, parms);
}
}
//判断题
if (list2 != null)
{
foreach (SubjectOfJudge subject in list2)
{
parms[1].Value = subject.ScoresForTester;
parms[2].Value = (int)SubjectType.Judge;
parms[3].Value = subject.Id;
DBHelper2.Update(sql, parms);
}
}
//单选题
if (list3 != null)
{
foreach (SubjectOfSingleSelection subject in list3)
{
parms[1].Value = subject.ScoresForTester;
parms[2].Value = (int)SubjectType.SingleSelection;
parms[3].Value = subject.Id;
DBHelper2.Update(sql, parms);
}
}
//多选题
if (list4 != null)
{
foreach (SubjectOfMultiSelection subject in list4)
{
parms[1].Value = subject.ScoresForTester;
parms[2].Value = (int)SubjectType.MultiSelection;
parms[3].Value = subject.Id;
DBHelper2.Update(sql, parms);
}
}
//简答题
if (list5 != null)
{
foreach (SubjectOfSimpleAnswer subject in list5)
{
parms[1].Value = subject.ScoresForTester;
parms[2].Value = (int)SubjectType.SimpleAnswer;
parms[3].Value = subject.Id;
DBHelper2.Update(sql, parms);
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
/// <summary>
/// 除了试卷的基本信息外，也包含各类题库集合(随机生成)
/// </summary>
public PaperByRandomSelection SelectByID(string id)
{
SqlConnection conn = new SqlConnection(DBHelper2.connStr);
conn.Open();
string sql = "select * from T_PaperByRandomSelection where paperID=@paperID";
SqlParameter[] parms =
{
new SqlParameter("@paperID", id)
};
PaperByRandomSelection paper = null;
using (SqlDataReader dr = DBHelper2.Select(conn, sql, parms))
{
if (dr.Read())
{
paper = new PaperByRandomSelection();
paper.PaperID = Convert.ToInt32(dr["paperID"]);
paper.PaperName = dr["paperName"].ToString();
paper.PaperType.DeptName = dr["paperType"].ToString();
paper.Creator.Name = dr["creator"].ToString();
paper.CreatedTime = Convert.ToDateTime(dr["createdTime"]);
paper.JudgeSum = Convert.ToInt32(dr["judgeSum"]);
paper.SingleSelectionSum = Convert.ToInt32(dr["singleSelectionSum"]);
paper.MultiSelectionSum = Convert.ToInt32(dr["multiSelectionSum"]);
paper.JudgeCateID = Convert.ToInt32(dr["judgeCateID"]);
paper.SingleSelectionCateID = Convert.ToInt32(dr["singleSelectionCateID"]);
paper.MultiSelectionCateID = Convert.ToInt32(dr["multiSelectionCateID"]);
dr.Close();
//判断题
sql = "select top " + paper.JudgeSum + " * from T_SubjectOfJudge where cateID=@judgeCateID order by newid()";
SqlParameter[] parm1 = { new SqlParameter("@judgeCateID", paper.JudgeCateID) };
paper.JudgeList = new List <SubjectOfJudge>();
using (SqlDataReader dr2 = DBHelper2.Select(conn, sql, parm1))
{
while (dr2.Read())
{
SubjectOfJudge subject = new SubjectOfJudge();
subject.Index = paper.JudgeList.Count + 1;
subject.Id = Convert.ToInt32(dr2["id"]);
subject.Question = dr2["question"].ToString();
subject.Answer = Convert.ToBoolean(dr2["answer"]);
paper.JudgeList.Add(subject);
}
}
//单选题
sql = "select top " + paper.SingleSelectionSum + " * from T_SubjectOfSingleSelection where cateID=@singleSelectionCateID order by newid()";
SqlParameter[] parm2 = { new SqlParameter("@singleSelectionCateID", paper.SingleSelectionCateID) };
paper.SingleSelectionList = new List <SubjectOfSingleSelection>();
using (SqlDataReader dr2 = DBHelper2.Select(conn, sql, parm2))
{
while (dr2.Read())
{
SubjectOfSingleSelection subject = new SubjectOfSingleSelection();
subject.Index = paper.SingleSelectionList.Count + 1;
subject.Id = Convert.ToInt32(dr2["id"]);
subject.Question = dr2["question"].ToString();
subject.Answer = Convert.ToChar(dr2["answer"]);
subject.SelectA = dr2["selectA"].ToString();
subject.SelectB = dr2["selectB"].ToString();
subject.SelectC = dr2["selectC"].ToString();
subject.SelectD = dr2["selectD"].ToString();
paper.SingleSelectionList.Add(subject);
}
}
//多选题
sql = "select top " + paper.MultiSelectionSum + " * from T_SubjectOfMultiSelection where cateID=@multiSelectionCateID order by newid()";
SqlParameter[] parm3 = { new SqlParameter("@multiSelectionCateID", paper.MultiSelectionCateID) };
paper.MultiSelectionList = new List <SubjectOfMultiSelection>();
using (SqlDataReader dr2 = DBHelper2.Select(conn, sql, parm3))
{
while (dr2.Read())
{
SubjectOfMultiSelection subject = new SubjectOfMultiSelection();
subject.Index = paper.MultiSelectionList.Count + 1;
subject.Id = Convert.ToInt32(dr2["id"]);
subject.Question = dr2["question"].ToString();
subject.Answer = dr2["answer"].ToString();
subject.SelectA = dr2["selectA"].ToString();
subject.SelectB = dr2["selectB"].ToString();
subject.SelectC = dr2["selectC"].ToString();
subject.SelectD = dr2["selectD"].ToString();
paper.MultiSelectionList.Add(subject);
}
}
}
}
return(paper);
}
//填充考生的回答和分数
public void SetTestRecorderAnswer(int recorderID, List <SubjectOfFillBlank> list1, List <SubjectOfJudge> list2, List <SubjectOfSingleSelection> list3, List <SubjectOfMultiSelection> list4, List <SubjectOfSimpleAnswer> list5)
{
string sql = "select * from T_TestRecorder_Answer where recorderID=@recorderID";
SqlParameter[] parms =
{
new SqlParameter("@recorderID", recorderID)
};
using (SqlDataReader dr = DBHelper2.Select(sql, parms))
{
while (dr.Read())
{
int subjectType = Convert.ToInt32(dr["subjectType"]);
int subjectID = Convert.ToInt32(dr["subjectID"]);
switch (subjectType)
{
case 1: //填空题
if (list1 != null)
{
foreach (SubjectOfFillBlank subject in list1)
{
if (subject.Id == subjectID)
{
subject.AnswerByTester = dr["answer1"].ToString();
subject.ScoresForTester = Convert.ToInt32(dr["scores"]);
break;
}
}
}
break;
case 2: //判断题
if (list2 != null)
{
foreach (SubjectOfJudge subject in list2)
{
if (subject.Id == subjectID)
{
try
{
subject.AnswerByTester = Convert.ToBoolean(dr["answer2"]);
}
catch { }
subject.ScoresForTester = Convert.ToInt32(dr["scores"]);
if (subject.ScoresForTester == 0 && subject.AnswerByTester == subject.Answer)
{
subject.ScoresForTester = subject.Scores;
}
break;
}
}
}
break;
case 3: //单选题
if (list3 != null)
{
foreach (SubjectOfSingleSelection subject in list3)
{
if (subject.Id == subjectID)
{
try
{
subject.AnswerByTester = Convert.ToChar(dr["answer3"]);
}
catch { }
subject.ScoresForTester = Convert.ToInt32(dr["scores"]);
if (subject.ScoresForTester == 0 && subject.AnswerByTester == subject.Answer)
{
subject.ScoresForTester = subject.Scores;
}
break;
}
}
}
break;
case 4: //多选题
if (list4 != null)
{
foreach (SubjectOfMultiSelection subject in list4)
{
if (subject.Id == subjectID)
{
subject.AnswerByTester = dr["answer4"].ToString();
subject.ScoresForTester = Convert.ToInt32(dr["scores"]);
if (subject.ScoresForTester == 0 && subject.AnswerByTester.Equals(subject.Answer))
{
subject.ScoresForTester = subject.Scores;
}
break;
}
}
}
break;
case 5: //简答题
if (list5 != null)
{
foreach (SubjectOfSimpleAnswer subject in list5)
{
if (subject.Id == subjectID)
{
subject.AnswerByTester = dr["answer5"].ToString();
subject.ScoresForTester = Convert.ToInt32(dr["scores"]);
break;
}
}
}
break;
}
}
}
}
public PaperByManualSelection SelectByID(string id)
{
PaperByManualSelection paper = new PaperByManualSelection();
string sql = "select * from T_PaperByManualSelection where paperID=@paperID";
SqlParameter[] parms =
{
new SqlParameter("@paperID", id)
};
using (SqlDataReader dr = DBHelper2.Select(sql, parms))
{
if (dr.Read())
{
paper.PaperID = Convert.ToInt32(dr["paperID"]);
paper.PaperName = dr["paperName"].ToString();
paper.PaperType.DeptName = dr["paperType"].ToString();
dr.Close();
}
//填空题
sql = "select t1.* from T_SubjectOfFillBlank t1 inner join T_PaperByManualSelection_Subject t2 on t1.id=t2.subjectID where t2.paperID=@paperID and t2.subjectType=1";
using (SqlDataReader dr2 = DBHelper2.Select(sql, parms))
{
List <SubjectOfFillBlank> list = new List <SubjectOfFillBlank>();
while (dr2.Read())
{
SubjectOfFillBlank subject = new SubjectOfFillBlank();
subject.Index = list.Count + 1;
subject.Id = Convert.ToInt32(dr2["id"]);
subject.Question = dr2["question"].ToString();
subject.Answer = dr2["answer"].ToString();
list.Add(subject);
}
paper.FillBlankList = list;
}
//判断题
sql = "select t1.* from T_SubjectOfJudge t1 inner join T_PaperByManualSelection_Subject t2 on t1.id=t2.subjectID where t2.paperID=@paperID and t2.subjectType=2";
using (SqlDataReader dr2 = DBHelper2.Select(sql, parms))
{
List <SubjectOfJudge> list = new List <SubjectOfJudge>();
while (dr2.Read())
{
SubjectOfJudge subject = new SubjectOfJudge();
subject.Index = list.Count + 1;
subject.Id = Convert.ToInt32(dr2["id"]);
subject.Question = dr2["question"].ToString();
subject.Answer = Convert.ToBoolean(dr2["answer"]);
list.Add(subject);
}
paper.JudgeList = list;
}
//单选题
sql = "select t1.* from T_SubjectOfSingleSelection t1 inner join T_PaperByManualSelection_Subject t2 on t1.id=t2.subjectID where t2.paperID=@paperID and t2.subjectType=3";
using (SqlDataReader dr2 = DBHelper2.Select(sql, parms))
{
List <SubjectOfSingleSelection> list = new List <SubjectOfSingleSelection>();
while (dr2.Read())
{
SubjectOfSingleSelection subject = new SubjectOfSingleSelection();
subject.Index = list.Count + 1;
subject.Id = Convert.ToInt32(dr2["id"]);
subject.Question = dr2["question"].ToString();
subject.Answer = Convert.ToChar(dr2["answer"].ToString().ToUpper());
subject.SelectA = dr2["selectA"].ToString();
subject.SelectB = dr2["selectB"].ToString();
subject.SelectC = dr2["selectC"].ToString();
subject.SelectD = dr2["selectD"].ToString();
list.Add(subject);
}
paper.SingleSelectionList = list;
}
//多选题
sql = "select t1.* from T_SubjectOfMultiSelection t1 inner join T_PaperByManualSelection_Subject t2 on t1.id=t2.subjectID where t2.paperID=@paperID and t2.subjectType=4";
using (SqlDataReader dr2 = DBHelper2.Select(sql, parms))
{
List <SubjectOfMultiSelection> list = new List <SubjectOfMultiSelection>();
while (dr2.Read())
{
SubjectOfMultiSelection subject = new SubjectOfMultiSelection();
subject.Index = list.Count + 1;
subject.Id = Convert.ToInt32(dr2["id"]);
subject.Question = dr2["question"].ToString();
subject.Answer = dr2["answer"].ToString().ToUpper();
subject.SelectA = dr2["selectA"].ToString();
subject.SelectB = dr2["selectB"].ToString();
subject.SelectC = dr2["selectC"].ToString();
subject.SelectD = dr2["selectD"].ToString();
list.Add(subject);
}
paper.MultiSelectionList = list;
}
//简答题
sql = "select t1.* from T_SubjectOfSimpleAnswer t1 inner join T_PaperByManualSelection_Subject t2 on t1.id=t2.subjectID where t2.paperID=@paperID and t2.subjectType=5";
using (SqlDataReader dr2 = DBHelper2.Select(sql, parms))
{
List <SubjectOfSimpleAnswer> list = new List <SubjectOfSimpleAnswer>();
while (dr2.Read())
{
SubjectOfSimpleAnswer subject = new SubjectOfSimpleAnswer();
subject.Index = list.Count + 1;
subject.Id = Convert.ToInt32(dr2["id"]);
subject.Question = dr2["question"].ToString();
subject.Answer = dr2["answer"].ToString();
list.Add(subject);
}
paper.SimpleAnswerList = list;
}
}
return(paper);
}
public void InsertTestRecorderAnswer(int recorderID, List <SubjectOfFillBlank> list1, List <SubjectOfJudge> list2, List <SubjectOfSingleSelection> list3, List <SubjectOfMultiSelection> list4, List <SubjectOfSimpleAnswer> list5)
{
SqlConnection conn = new SqlConnection(DBHelper2.connStr);
try
{
conn.Open();
string sql = "insert into T_TestRecorder_Answer(recorderID,subjectType,subjectID) values(@recorderID,@subjectType,@subjectID)";
SqlParameter[] parms =
{
new SqlParameter("@recorderID", recorderID),
new SqlParameter("@subjectType", 0),
new SqlParameter("@subjectID", 0)
};
//填空题
if (list1 != null)
{
foreach (SubjectOfFillBlank subject in list1)
{
parms[1].Value = (int)SubjectType.FillBlank;
parms[2].Value = subject.Id;
DBHelper2.Insert(sql, parms);
}
}
//判断题
if (list2 != null)
{
foreach (SubjectOfJudge subject in list2)
{
parms[1].Value = (int)SubjectType.Judge;
parms[2].Value = subject.Id;
DBHelper2.Insert(sql, parms);
}
}
//单选题
if (list3 != null)
{
foreach (SubjectOfSingleSelection subject in list3)
{
parms[1].Value = (int)SubjectType.SingleSelection;
parms[2].Value = subject.Id;
DBHelper2.Insert(sql, parms);
}
}
//多选题
if (list4 != null)
{
foreach (SubjectOfMultiSelection subject in list4)
{
parms[1].Value = (int)SubjectType.MultiSelection;
parms[2].Value = subject.Id;
DBHelper2.Insert(sql, parms);
}
}
// 简答题
if (list5 != null)
{
foreach (SubjectOfSimpleAnswer subject in list5)
{
parms[1].Value = (int)SubjectType.SimpleAnswer;
parms[2].Value = subject.Id;
DBHelper2.Insert(sql, parms);
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
public void Insert(PaperByManualSelection obj)
{
string sql = "insert into T_PaperByManualSelection(paperName,deptID,paperType,creator,createdTime) values(@paperName,@deptID,@paperType,@creator,@createdTime); select @@identity";
SqlParameter[] parms =
{
new SqlParameter("@paperName", obj.PaperName),
new SqlParameter("@deptID", obj.PaperType.DeptID),
new SqlParameter("@paperType", obj.PaperType.DeptName),
new SqlParameter("@creator", obj.Creator.Name),
new SqlParameter("@createdTime", obj.CreatedTime)
};
SqlConnection conn = new SqlConnection(DBHelper2.connStr);
SqlTransaction trans = null;
try
{
conn.Open();
trans = conn.BeginTransaction("TInsertPaperByManualSelection");
using (SqlDataReader dr = DBHelper2.Select(trans, sql, parms))
{
if (dr.Read())
{
int id = Convert.ToInt32(dr[0]);
dr.Close();
sql = "insert into T_PaperByManualSelection_Subject(paperID,subjectType,subjectID) values(@paperID,@subjectType,@subjectID)";
//1：填空题，2：判断题，3：单选题，4：多选题，5：简答题
if (obj.FillBlankList != null)
{
foreach (SubjectOfFillBlank subject in obj.FillBlankList)
{
SqlParameter[] parms2 =
{
new SqlParameter("@paperID", id),
new SqlParameter("@subjectType", 1),
new SqlParameter("@subjectID", subject.Id)
};
DBHelper2.Insert(trans, sql, parms2);
}
}
if (obj.JudgeList != null)
{
foreach (SubjectOfJudge subject in obj.JudgeList)
{
SqlParameter[] parms2 =
{
new SqlParameter("@paperID", id),
new SqlParameter("@subjectType", 2),
new SqlParameter("@subjectID", subject.Id)
};
DBHelper2.Insert(trans, sql, parms2);
}
}
if (obj.SingleSelectionList != null)
{
foreach (SubjectOfSingleSelection subject in obj.SingleSelectionList)
{
SqlParameter[] parms2 =
{
new SqlParameter("@paperID", id),
new SqlParameter("@subjectType", 3),
new SqlParameter("@subjectID", subject.Id)
};
DBHelper2.Insert(trans, sql, parms2);
}
}
if (obj.MultiSelectionList != null)
{
foreach (SubjectOfMultiSelection subject in obj.MultiSelectionList)
{
SqlParameter[] parms2 =
{
new SqlParameter("@paperID", id),
new SqlParameter("@subjectType", 4),
new SqlParameter("@subjectID", subject.Id)
};
DBHelper2.Insert(trans, sql, parms2);
}
}
if (obj.SimpleAnswerList != null)
{
foreach (SubjectOfSimpleAnswer subject in obj.SimpleAnswerList)
{
SqlParameter[] parms2 =
{
new SqlParameter("@paperID", id),
new SqlParameter("@subjectType", 5),
new SqlParameter("@subjectID", subject.Id)
};
DBHelper2.Insert(trans, sql, parms2);
}
}
}
}
trans.Commit();
}
catch
{
trans.Rollback();
}
finally
{
trans.Dispose();
conn.Close();
}
}
/// <summary>
/// 获取考生的实际应考试卷和回答
/// </summary>
public PaperByRandomSelection SelectByID3(int testID, int recorderID)
{
SqlConnection conn = new SqlConnection(DBHelper2.connStr);
PaperByRandomSelection paper = new PaperByRandomSelection();
try
{
conn.Open();
string sql = string.Empty;
sql += "SELECT s.*";
sql += ",(SELECT scores FROM T_Test_Subject_Scores WHERE (testID = @testID) AND (subjectType = @subjectType)) AS scores";
sql += ", t.{0}, t.scores AS scoresForTester";
sql += " FROM T_TestRecorder_Answer AS t INNER JOIN {1} AS s ON t.subjectID=s.id ";
sql += " WHERE (t.recorderID = @recorderID) AND (t.subjectType = @subjectType)";
SqlParameter[] parms =
{
new SqlParameter("@subjectType", 0),
new SqlParameter("@testID", testID),
new SqlParameter("@recorderID", recorderID)
};
//判断题
string sql1 = string.Format(sql, "answer2", "T_SubjectOfJudge");
parms[0].Value = (int)SubjectType.Judge;
using (SqlDataReader dr = DBHelper2.Select(conn, sql1, parms))
{
paper.JudgeList = new List <SubjectOfJudge>();
while (dr.Read())
{
SubjectOfJudge subject = new SubjectOfJudge();
try
{
subject.Id = Convert.ToInt32(dr["id"]);
subject.Question = dr["question"].ToString();
subject.Answer = Convert.ToBoolean(dr["answer"]);
subject.Scores = Convert.ToInt32(dr["scores"]);
subject.AnswerByTester = Convert.ToBoolean(dr["answer2"]);
subject.ScoresForTester = Convert.ToInt32(dr["scoresForTester"]);
}
catch { }
paper.JudgeList.Add(subject);
}
}
//单选题
string sql2 = string.Format(sql, "answer3", "T_SubjectOfSingleSelection");
parms[0].Value = (int)SubjectType.SingleSelection;
using (SqlDataReader dr = DBHelper2.Select(conn, sql2, parms))
{
paper.SingleSelectionList = new List <SubjectOfSingleSelection>();
while (dr.Read())
{
SubjectOfSingleSelection subject = new SubjectOfSingleSelection();
try
{
subject.Id = Convert.ToInt32(dr["id"]);
subject.Question = dr["question"].ToString();
subject.SelectA = dr["selectA"].ToString();
subject.SelectB = dr["selectB"].ToString();
subject.SelectC = dr["selectC"].ToString();
subject.SelectD = dr["selectD"].ToString();
subject.Answer = Convert.ToChar(dr["answer"]);
subject.Scores = Convert.ToInt32(dr["scores"]);
subject.AnswerByTester = Convert.ToChar(dr["answer3"]);
subject.ScoresForTester = Convert.ToInt32(dr["scoresForTester"]);
}
catch { }
paper.SingleSelectionList.Add(subject);
}
}
//多选题
string sql3 = string.Format(sql, "answer4", "T_SubjectOfMultiSelection");
parms[0].Value = (int)SubjectType.MultiSelection;
using (SqlDataReader dr = DBHelper2.Select(conn, sql3, parms))
{
paper.MultiSelectionList = new List <SubjectOfMultiSelection>();
while (dr.Read())
{
SubjectOfMultiSelection subject = new SubjectOfMultiSelection();
try
{
subject.Id = Convert.ToInt32(dr["id"]);
subject.Question = dr["question"].ToString();
subject.SelectA = dr["selectA"].ToString();
subject.SelectB = dr["selectB"].ToString();
subject.SelectC = dr["selectC"].ToString();
subject.SelectD = dr["selectD"].ToString();
subject.Answer = dr["answer"].ToString();
subject.Scores = Convert.ToInt32(dr["scores"]);
subject.AnswerByTester = dr["answer4"].ToString();
subject.ScoresForTester = Convert.ToInt32(dr["scoresForTester"]);
}
catch { }
paper.MultiSelectionList.Add(subject);
}
}
}
catch (Exception ex)
{
throw ex;
}
finally { conn.Close(); }
return(paper);
}
public void Insert(Test test,
int fillBlankScoresOfEveryone,
int judgeScoresOfEveryone,
int singleSelectionScoresOfEveryone,
int multiSelectionScoresOfEveryone,
List <SubjectOfSimpleAnswer> simpleAnswerScoresList)
{
Insert(test);
string sql = "insert into T_Test_Subject_Scores(testID,subjectType,scores) values(@testID,@subjectType,@scores)";
SqlParameter[] parms =
{
new SqlParameter("@testID", test.TestID),
new SqlParameter("@subjectType", null),
new SqlParameter("@scores", null)
};
SqlConnection conn = new SqlConnection(DBHelper2.connStr);
try
{
conn.Open();
//填空题
parms[1].Value = (int)SubjectType.FillBlank;
parms[2].Value = fillBlankScoresOfEveryone;
DBHelper2.Insert(conn, sql, parms);
//判断题
parms[1].Value = (int)SubjectType.Judge;
parms[2].Value = judgeScoresOfEveryone;
DBHelper2.Insert(conn, sql, parms);
//单选题
parms[1].Value = (int)SubjectType.SingleSelection;
parms[2].Value = singleSelectionScoresOfEveryone;
DBHelper2.Insert(conn, sql, parms);
//多选题
parms[1].Value = (int)SubjectType.MultiSelection;
parms[2].Value = multiSelectionScoresOfEveryone;
DBHelper2.Insert(conn, sql, parms);
//简答题
string sql2 = "insert into T_Test_SubjectOfSimpleAnswer_Scores(testID,subjectID,scores) values(@testID,@subjectID,@scores)";
SqlParameter[] parms2 =
{
new SqlParameter("@testID", test.TestID),
new SqlParameter("@subjectID", null),
new SqlParameter("@scores", null)
};
if (simpleAnswerScoresList != null)
{
foreach (SubjectOfSimpleAnswer subject in simpleAnswerScoresList)
{
parms2[1].Value = subject.Id;
parms2[2].Value = subject.Scores;
DBHelper2.Insert(conn, sql2, parms2);
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
