public static DataSet GetQuesCountDetail(int subjectid, String tableName)
{
String strSql = "SELECT [tbChapter].[id],[chapterno],[chaptername],COUNT([chaptername]) AS AllCount FROM [tbChapter] ," + tableName + " WHERE [tbChapter].[id]=" + tableName + ".[chapterid] AND subjectid=@subjectid GROUP BY [tbChapter].[id],[chaptername],[chapterno] ORDER BY [chapterno]";
MyUtil.PrintSql(strSql);
return(DbHelperSQL.Query(strSql, new SqlParameter("@subjectid", subjectid)));
}
public static int GetQuesCount(int subjectid, String tableName)
{
String strSql = "select count(*) from " + tableName + " where chapterid in (select id from tbChapter where subjectid=" + subjectid + ");";
MyUtil.PrintSql(strSql);
return((int)DbHelperSQL.GetSingle(strSql));
}
/// <summary>
/// 判断某个科目是否属于当前账户
/// </summary>
/// <param name="str"></param>
/// <returns></returns>
public static bool IsGrantSubject(String str)
{
DataTable dt = MyUtil.GetMySubject().Tables[0];
for (int i = 0; i < dt.Rows.Count; i++)
{
if (str.Trim() == dt.Rows[i]["subjectname"].ToString().Trim())
{
return(true);
}
}
return(false);
}
/// <summary>
/// 根据subjectname和chaptername获取chapterid
/// </summary>
/// <param name="subject"></param>
/// <param name="chapter"></param>
/// <returns></returns>
public static int GetChapterID(String subject, String chapterno)
{
int ret;
String strSql = "select tbChapter.id from tbChapter,tbSubject where tbChapter.subjectid=tbSubject.id and subjectname=@subjectname and chapterno=@chapterno";
if (!MyUtil.IsGrantSubject(subject))
{
return(0);
}
try
{
ret = (int)DbHelperSQL.GetSingle(strSql, new SqlParameter("@subjectname", subject), new SqlParameter("@chapterno", Convert.ToInt32(chapterno)));
}
catch (Exception)
{
ret = 0;
}
return(ret);
}
/// <summary>
/// 获取某个用户的科目ID字符串
/// </summary>
/// <returns></returns>
public static String GetMySubjectString()
{
DataTable dt = MyUtil.GetMySubject().Tables[0];
StringBuilder sb = new StringBuilder();
sb.Append(" (");
for (int i = 0; i < dt.Rows.Count; i++)
{
sb.Append(dt.Rows[i]["id"].ToString());
sb.Append(",");
}
String str = sb.ToString();
if (str.EndsWith(","))
{
str = str.TrimEnd(',');
}
str = str + ")";
return(str);
}
/// <summary>
/// 导入到数据库
/// </summary>
/// <param name="dt"></param>
/// <returns>返回插入不成功的学号</returns>
public static List <String> ExportToDB(DataTable dt)
{
List <String> list = new List <string>();
tbUserDAL userDAL = new tbUserDAL();
for (int i = 0; i < dt.Rows.Count; i++)
{
tbUser user = new tbUser();
String loginname = dt.Rows[i][0].ToString();
String realname = dt.Rows[i][1].ToString();
String userpwd = dt.Rows[i][2].ToString();
String classname = dt.Rows[i][3].ToString();
String usertype = dt.Rows[i][4].ToString();
user.loginname = loginname; //登陆名
user.realname = realname; //真实名
if (usertype.Trim() == "学生")
{
if (IsNum(loginname))
{
//班级
try
{
int classid = (int)DbHelperSQL.GetSingle("select top 1 id from tbClass where classname=@classname", new SqlParameter("@classname", classname));
user.classid = classid;
}
catch (Exception)
{
list.Add("行" + (i + 2) + "的班级不存在！");
continue;
}
}
else
{
list.Add("行" + (i + 2) + "的学号格式不正确！");
continue;
}
user.usertype = 3;
}
else
if (usertype.Trim() == "教师")
{
user.usertype = 2;
}
else
{
list.Add("行" + (i + 2) + "的身份格式不正确！");
continue;
}
//密码
try
{
user.userpwd = MyUtil.MD5(GetPwdByCard(userpwd));
}
catch (Exception)
{
list.Add("行" + (i + 2) + "的密码格式不正确！");
continue;
}
//判断登陆名是否存在
if (DbHelperSQL.Exists("select * from tbUser where loginname=@loginname", new SqlParameter("@loginname", loginname)))
{
list.Add("行" + (i + 2) + "的登陆名已经存在！");
continue;
}
userDAL.Add(user);
}
return(list);
}
/// <summary>
/// 导入到数据库
/// </summary>
/// <param name="dt"></param>
/// <returns>返回插入不成功的学号</returns>
public static List <String> ExportToDB(DataTable dt)
{
List <String> list = new List <string>();
tbPaper paper = new tbPaper();
tbSingle single = new tbSingle();
tbCheck check = new tbCheck();
tbJudge judge = new tbJudge();
tbBlank blank = new tbBlank();
tbAnswer answer = new tbAnswer();
tbPaperDAL paperDAL = new tbPaperDAL();
tbSingleDAL singleDAL = new tbSingleDAL();
tbCheckDAL checkDAL = new tbCheckDAL();
tbJudgeDAL judgeDAL = new tbJudgeDAL();
tbBlankDAL blankDAL = new tbBlankDAL();
tbAnswerDAL answerDAL = new tbAnswerDAL();
//取得数据库连接
SqlConnection conn = SQLHelper.GetConnection();
//打开数据库连接
conn.Open();
//创建事务
SqlTransaction SqlTransaction = conn.BeginTransaction();
try
{
for (int i = 0; i < dt.Rows.Count; i++)
{
String flag = dt.Rows[i][0].ToString();
String temp = dt.Rows[i][1].ToString();
switch (flag)
{
case "#科目#":
//获取科目id
try
{
int subjectid = (int)DbHelperSQL.GetSingle("select id from tbSubject where subjectname=@subjectname and id in" + MyUtil.GetMySubjectString(), new SqlParameter("@subjectname", dt.Rows[i][1].ToString()));
paper.subjectid = subjectid;
}
catch (Exception)
{
list.Add("科目不存在或者没有权限！");
continue;
}
break;
case "#标题#":
paper.papertitle = dt.Rows[i][1].ToString();
if (String.IsNullOrEmpty(paper.papertitle))
{
list.Add("标题设置不正确！");
continue;
}
break;
case "#时间#":
try
{
paper.durationtime = Convert.ToInt32(dt.Rows[i][1].ToString().TrimEnd('分'));
}
catch (Exception)
{
list.Add("时间格式不正确！");
continue;
}
break;
case "#单选分值#":
try
{
paper.sr_scoreofeach = Convert.ToInt32(dt.Rows[i][1].ToString().TrimEnd('分'));
}
catch (Exception)
{
list.Add("单选分值格式不正确！");
continue;
}
break;
case "#多选分值#":
try
{
paper.cb_scoreofeach = Convert.ToInt32(dt.Rows[i][1].ToString().TrimEnd('分'));
}
catch (Exception)
{
list.Add("多选分值格式不正确！");
continue;
}
break;
case "#判断分值#":
try
{
paper.jd_scoreofeach = Convert.ToInt32(dt.Rows[i][1].ToString().TrimEnd('分'));
}
catch (Exception)
{
list.Add("判断分值格式不正确！");
continue;
}
break;
case "#填空分值#":
try
{
paper.bf_scoreofeach = Convert.ToInt32(dt.Rows[i][1].ToString().TrimEnd('分'));
}
catch (Exception)
{
list.Add("填空分值格式不正确！");
continue;
}
break;
case "#简答分值#":
try
{
paper.sa_scoreofeach = Convert.ToInt32(dt.Rows[i][1].ToString().TrimEnd('分'));
}
catch (Exception)
{
list.Add("简答分值格式不正确！");
continue;
}
break;
case "#单选#":
single.chapterid = 0; //不属于任何章节
single.diff = 3; //默认难度一般
single.questype = 2; //固定类型
single.ques = dt.Rows[i][1].ToString();
single.option_a = dt.Rows[i][2].ToString();
single.option_b = dt.Rows[i][3].ToString();
single.option_c = dt.Rows[i][4].ToString();
single.option_d = dt.Rows[i][5].ToString();
single.ans = dt.Rows[i][6].ToString();
if (single.ans != "A" && single.ans != "B" && single.ans != "C" && single.ans != "D")
{
list.Add("行" + (i + 2) + "的单选答案不正确！");
continue;
}
int singleid = singleDAL.AddTran(single, SqlTransaction);
paper.sr_list += singleid + ",";
paper.sr_count += 1;
break;
case "#多选#":
check.chapterid = 0; //不属于任何章节
check.diff = 3; //默认难度一般
check.questype = 2; //固定类型
check.ques = dt.Rows[i][1].ToString();
check.option_a = dt.Rows[i][2].ToString();
check.option_b = dt.Rows[i][3].ToString();
check.option_c = dt.Rows[i][4].ToString();
check.option_d = dt.Rows[i][5].ToString();
check.option_e = dt.Rows[i][6].ToString();
check.option_f = dt.Rows[i][7].ToString();
check.option_g = dt.Rows[i][8].ToString();
check.ans = dt.Rows[i][9].ToString();
if (check.ans == "" || check.ans == null)
{
list.Add("行" + (i + 2) + "的多选答案不正确！");
continue;
}
int checkid = checkDAL.AddTran(check, SqlTransaction);
paper.cb_list += checkid + ",";
paper.cb_count += 1;
break;
case "#判断#":
judge.chapterid = 0; //不属于任何章节
judge.diff = 3; //默认难度一般
judge.questype = 2; //固定类型
judge.ques = dt.Rows[i][1].ToString();
judge.ans = dt.Rows[i][2].ToString();
if (judge.ans != "对" && judge.ans != "错")
{
list.Add("行" + (i + 2) + "的判断答案不正确！");
continue;
}
int judgeid = judgeDAL.AddTran(judge, SqlTransaction);
paper.jd_list += judgeid + ",";
paper.jd_count += 1;
break;
case "#填空#":
blank.chapterid = 0; //不属于任何章节
blank.diff = 3; //默认难度一般
blank.questype = 2; //固定类型
blank.ques = dt.Rows[i][1].ToString();
blank.ans = dt.Rows[i][2].ToString();
blank.blanklength = blank.ans.Length;
int blankid = blankDAL.AddTran(blank, SqlTransaction);
paper.bf_list += blankid + ",";
paper.bf_count += 1;
break;
case "#简答#":
answer.chapterid = 0; //不属于任何章节
answer.diff = 3; //默认难度一般
answer.questype = 2; //固定类型
answer.ques = dt.Rows[i][1].ToString();
answer.ans = dt.Rows[i][2].ToString();
int answerid = answerDAL.AddTran(answer, SqlTransaction);
paper.sa_list += answerid + ",";
paper.sa_count += 1;
break;
}
}
if (list.Count == 0)
{
if (paper.sr_list.EndsWith(","))
{
paper.sr_list = paper.sr_list.TrimEnd(',');
}
if (paper.cb_list.EndsWith(","))
{
paper.cb_list = paper.cb_list.TrimEnd(',');
}
if (paper.jd_list.EndsWith(","))
{
paper.jd_list = paper.jd_list.TrimEnd(',');
}
if (paper.bf_list.EndsWith(","))
{
paper.bf_list = paper.bf_list.TrimEnd(',');
}
if (paper.sa_list.EndsWith(","))
{
paper.sa_list = paper.sa_list.TrimEnd(',');
}
paper.allscore = paper.sr_count * paper.sr_scoreofeach + paper.cb_count * paper.cb_scoreofeach + paper.jd_count * paper.jd_scoreofeach + paper.bf_count * paper.bf_scoreofeach + paper.sa_count * paper.sa_scoreofeach;
paper.papertype = 2;//固定试卷
paper.createtime = DateTime.Now;
paper.userid = ((tbUser)HttpContext.Current.Session[Constant.User]).id;
paperDAL.AddTran(paper, SqlTransaction);
SqlTransaction.Commit();
}
}
catch (Exception)
{
try
{
SqlTransaction.Rollback();
}
catch (Exception)
{
//事务回滚出错
}
}
finally
{
//关闭各种资源
SqlTransaction.Dispose();
conn.Close();
}
return(list);
}