protected void ButInput_Click(object sender, System.EventArgs e) { if (UpLoadFile.PostedFile != null) //检查上传文件不为空 { if (UpLoadFile.PostedFile.FileName == "") { this.RegisterStartupScript("newWindow", "<script language='javascript'>alert('请选择正确的Excel文件!')</script>"); return; } string strName = UpLoadFile.PostedFile.FileName.Substring(UpLoadFile.PostedFile.FileName.LastIndexOf(".")); //获取后缀名 if (strName.ToUpper() != ".XLS") { this.RegisterStartupScript("newWindow", "<script language='javascript'>alert('请选择正确的Excel文件!')</script>"); return; } UpLoadFile.PostedFile.SaveAs(Server.MapPath("..\\UpLoadFiles\\ImportUser.xls")); string FilePath = Server.MapPath("..\\UpLoadFiles\\ImportUser.xls"); int intTmp = 0; string SheetName = ""; bool canOpen = false; System.Data.DataTable rs = new System.Data.DataTable(); OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath + ";Extended Properties=\"Excel 8.0;\""); try //尝试数据连接是否可用 { conn.Open(); conn.Close(); canOpen = true; } catch //不是Excel文件格式 { canOpen = false; LabelMessage.Text = "不是Excel文件格式,导入帐户失败!"; } if (canOpen) { try //如果数据连接可以打开则尝试读入数据 { SheetName = "Sheet1"; OleDbCommand myOleDbCommand = new OleDbCommand("SELECT * FROM [" + SheetName + "$]", conn); OleDbDataAdapter myData = new OleDbDataAdapter(myOleDbCommand); myData.Fill(rs); conn.Close(); } catch //如果数据连接可以打开但是读入数据失败,则从文件中提取出工作表的名称,再读入数据 { SheetName = ObjFun.GetSheetName(FilePath); if (SheetName.Length > 0) { OleDbCommand myOleDbCommand = new OleDbCommand("SELECT * FROM [" + SheetName + "$]", conn); OleDbDataAdapter myData = new OleDbDataAdapter(myOleDbCommand); myData.Fill(rs); conn.Close(); } } intTmp = ObjFun.ExecuteSqlCmd("delete from ImportErr"); //清空ImportErr数据表 string strLoginID, strUserName, strUserPwd, strUserSex, strBirthday, strDept, strJob, strTelephone, strCertType, strCertNum, strLoginIP, strUserType, strUserState; string strDeptID, strJobID; string strCreateUserID = Convert.ToString(myUserID); DateTime dtmCreateDate; System.DateTime currentTime = new System.DateTime(); currentTime = System.DateTime.Now; dtmCreateDate = Convert.ToDateTime(Convert.ToString(currentTime.ToString("d"))); int i = 0, index = 0; for (i = 0; i < rs.Rows.Count; i++) { strLoginID = ObjFun.getStr(ObjFun.CheckString(rs.Rows[i][0].ToString().Trim()), 20); //帐号 strUserSex = rs.Rows[i][3].ToString().Trim(); //性别 strBirthday = rs.Rows[i][4].ToString().Trim(); //出生年月 strDept = rs.Rows[i][5].ToString().Trim(); //部门 strJob = rs.Rows[i][6].ToString().Trim(); //职务 strUserType = rs.Rows[i][11].ToString().Trim(); //类型 strUserState = rs.Rows[i][12].ToString().Trim(); //状态 //帐号 if (strLoginID == "") { index = index + 1; intTmp = ObjFun.ExecuteSqlCmd("insert into ImportErr(ErrID,RowNum,ErrInfo) values(" + index + "," + (i + 2) + ",'帐号为空')"); } else { if (ObjFun.GetValues("select LoginID from UserInfo where LoginID='" + ObjFun.CheckString(strLoginID) + "'", "LoginID") != "") { index = index + 1; intTmp = ObjFun.ExecuteSqlCmd("insert into ImportErr(ErrID,RowNum,ErrInfo) values(" + index + "," + (i + 2) + ",'帐号已存在')"); } } //性别 if (strUserSex != "") { if ((strUserSex != "男") && (strUserSex != "女")) { index = index + 1; intTmp = ObjFun.ExecuteSqlCmd("insert into ImportErr(ErrID,RowNum,ErrInfo) values(" + index + "," + (i + 2) + ",'性别应为男或女')"); } } //出生年月 if (strBirthday != "") { try { Convert.ToDateTime(strBirthday); } catch { index = index + 1; intTmp = ObjFun.ExecuteSqlCmd("insert into ImportErr(ErrID,RowNum,ErrInfo) values(" + index + "," + (i + 2) + ",'出生年月为空或格式不正确')"); } } //部门 if ((strDept != "") && (ObjFun.GetValues("select DeptID from DeptInfo where DeptName='" + ObjFun.CheckString(strDept) + "'", "DeptID") == "")) { index = index + 1; intTmp = ObjFun.ExecuteSqlCmd("insert into ImportErr(ErrID,RowNum,ErrInfo) values(" + index + "," + (i + 2) + ",'部门不存在,请先创建')"); } //职务 if ((strJob != "") && (ObjFun.GetValues("select JobID from JobInfo where JobName='" + ObjFun.CheckString(strJob) + "'", "JobID") == "")) { index = index + 1; intTmp = ObjFun.ExecuteSqlCmd("insert into ImportErr(ErrID,RowNum,ErrInfo) values(" + index + "," + (i + 2) + ",'职务不存在,请先创建')"); } //类型 if (myLoginID.Trim().ToUpper() == "ADMIN") { if ((strUserType != "普通帐户") && (strUserType != "管理帐户")) { index = index + 1; intTmp = ObjFun.ExecuteSqlCmd("insert into ImportErr(ErrID,RowNum,ErrInfo) values(" + index + "," + (i + 2) + ",'类型应为普通帐户或管理帐户')"); } } else { if (strUserType != "普通帐户") { index = index + 1; intTmp = ObjFun.ExecuteSqlCmd("insert into ImportErr(ErrID,RowNum,ErrInfo) values(" + index + "," + (i + 2) + ",'管理帐户只能创建普通帐户')"); } } //状态 if ((strUserState != "正常") && (strUserState != "禁止")) { index = index + 1; intTmp = ObjFun.ExecuteSqlCmd("insert into ImportErr(ErrID,RowNum,ErrInfo) values(" + index + "," + (i + 2) + ",'状态应为正常或禁止')"); } } string strSql = "select a.ErrID,a.RowNum,a.ErrInfo from ImportErr a order by a.ErrID asc"; string strConn = ConfigurationSettings.AppSettings["strConn"]; SqlConnection SqlConn = new SqlConnection(strConn); SqlDataAdapter SqlCmd = new SqlDataAdapter(strSql, SqlConn); DataSet SqlDS = new DataSet(); SqlCmd.Fill(SqlDS, "ImportErr"); if (SqlDS.Tables["ImportErr"].Rows.Count > 0) //如果导入有错误,则绑定DataGridErr { DataGridErr.DataSource = SqlDS; DataGridErr.AllowPaging = false; DataGridErr.DataBind(); DataGridErr.Visible = true; LabelMessage.Text = "导入帐户失败!"; } else //帐户导入UserInfo数据表 { DataGridErr.DataSource = null; DataGridErr.Visible = false; strConn = ConfigurationSettings.AppSettings["strConn"]; SqlConnection ObjConn = new SqlConnection(strConn); ObjConn.Open(); SqlTransaction ObjTran = ObjConn.BeginTransaction(); SqlCommand ObjCmd = new SqlCommand(); ObjCmd.Connection = ObjConn; ObjCmd.Transaction = ObjTran; try { for (i = 0; i < rs.Rows.Count; i++) { strLoginID = ObjFun.getStr(ObjFun.CheckString(rs.Rows[i][0].ToString().Trim()), 20); //帐号 strUserName = ObjFun.getStr(ObjFun.CheckString(rs.Rows[i][1].ToString().Trim()), 20); //姓名 strUserPwd = ObjFun.getStr(ObjFun.CheckString(rs.Rows[i][2].ToString().Trim()), 20); //密码 strUserSex = rs.Rows[i][3].ToString().Trim(); //性别 strBirthday = rs.Rows[i][4].ToString().Trim(); //出生年月 strDept = rs.Rows[i][5].ToString().Trim(); //部门 strJob = rs.Rows[i][6].ToString().Trim(); //职务 strTelephone = ObjFun.getStr(ObjFun.CheckString(rs.Rows[i][7].ToString().Trim()), 20); //电话 strCertType = ObjFun.getStr(ObjFun.CheckString(rs.Rows[i][8].ToString().Trim()), 20); //证件类型 strCertNum = ObjFun.getStr(ObjFun.CheckString(rs.Rows[i][9].ToString().Trim()), 20); //证件号码 strLoginIP = ObjFun.getStr(rs.Rows[i][10].ToString().Trim(), 20); //登录IP strUserType = rs.Rows[i][11].ToString().Trim(); //类型 strUserState = rs.Rows[i][12].ToString().Trim(); //状态 //性别 if (strUserSex == "") { strUserSex = "男"; } //部门 if (strDept != "") { strDeptID = ObjFun.GetValues("select DeptID from DeptInfo where DeptName='" + ObjFun.CheckString(strDept) + "'", "DeptID"); } else { strDeptID = "0"; } //职务 if (strJob != "") { strJobID = ObjFun.GetValues("select JobID from JobInfo where JobName='" + ObjFun.CheckString(strJob) + "'", "JobID"); } else { strJobID = "0"; } //类型 if (strUserType == "普通帐户") { strUserType = "0"; } else { strUserType = "1"; } //状态 if (strUserState == "正常") { strUserState = "1"; } else { strUserState = "0"; } //权限 int intJudgeUser = 0; int intJudgeTestType = 0; int intRoleMenu = 0; if (strUserType == "1") { intJudgeUser = 1; intJudgeTestType = 1; intRoleMenu = 1; } if (ObjFun.GetValues("select LoginID from UserInfo where LoginID='" + strLoginID + "'", "LoginID") == "") { if (strBirthday != "") { intTmp = ObjFun.ExecuteSqlCmd("Insert into UserInfo(LoginID,UserName,UserPwd,UserSex,Birthday,DeptID,JobID,Telephone,CertType,CertNum,LoginIP,UserType,UserState,JudgeUser,JudgeTestType,RoleMenu,CreateUserID,CreateDate) Values ('" + strLoginID + "','" + strUserName + "','" + strUserPwd + "','" + strUserSex + "','" + Convert.ToDateTime(strBirthday) + "','" + strDeptID + "','" + strJobID + "','" + strTelephone + "','" + strCertType + "','" + strCertNum + "','" + strLoginIP + "','" + strUserType + "','" + strUserState + "'," + intJudgeUser + "," + intJudgeTestType + "," + intRoleMenu + ",'" + strCreateUserID + "','" + dtmCreateDate + "')"); } else { intTmp = ObjFun.ExecuteSqlCmd("Insert into UserInfo(LoginID,UserName,UserPwd,UserSex,DeptID,JobID,Telephone,CertType,CertNum,LoginIP,UserType,UserState,JudgeUser,JudgeTestType,RoleMenu,CreateUserID,CreateDate) Values ('" + strLoginID + "','" + strUserName + "','" + strUserPwd + "','" + strUserSex + "','" + strDeptID + "','" + strJobID + "','" + strTelephone + "','" + strCertType + "','" + strCertNum + "','" + strLoginIP + "','" + strUserType + "','" + strUserState + "'," + intJudgeUser + "," + intJudgeTestType + "," + intRoleMenu + ",'" + strCreateUserID + "','" + dtmCreateDate + "')"); } } } ObjTran.Commit(); } catch { ObjTran.Rollback(); LabelMessage.Text = "导入帐户失败!"; } finally { ObjConn.Close(); ObjConn.Dispose(); LabelMessage.Text = "导入帐户成功!"; } } } } }
protected void ButInput_Click(object sender, System.EventArgs e) { if (UpLoadFile.PostedFile != null) //检查上传文件不为空 { if (UpLoadFile.PostedFile.FileName == "") { this.RegisterStartupScript("newWindow", "<script language='javascript'>alert('请选择正确的Excel文件!')</script>"); return; } string strName = UpLoadFile.PostedFile.FileName.Substring(UpLoadFile.PostedFile.FileName.LastIndexOf(".")); //获取后缀名 if (strName.ToUpper() != ".XLS") { this.RegisterStartupScript("newWindow", "<script language='javascript'>alert('请选择正确的Excel文件!')</script>"); return; } UpLoadFile.PostedFile.SaveAs(Server.MapPath("..\\UpLoadFiles\\ImportTest.xls")); string FilePath = Server.MapPath("..\\UpLoadFiles\\ImportTest.xls"); int intTmp = 0; string strTmp = ""; string SheetName = ""; bool canOpen = false; System.Data.DataTable rs = new System.Data.DataTable(); OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath + ";Extended Properties=\"Excel 8.0;\""); try //尝试数据连接是否可用 { conn.Open(); conn.Close(); canOpen = true; } catch //不是Excel文件格式 { canOpen = false; LabelMessage.Text = "不是Excel文件格式,导入试题失败!"; } if (canOpen) { try //如果数据连接可以打开则尝试读入数据 { SheetName = "Sheet1"; OleDbCommand myOleDbCommand = new OleDbCommand("SELECT * FROM [" + SheetName + "$]", conn); OleDbDataAdapter myData = new OleDbDataAdapter(myOleDbCommand); myData.Fill(rs); conn.Close(); } catch //如果数据连接可以打开但是读入数据失败,则从文件中提取出工作表的名称,再读入数据 { SheetName = ObjFun.GetSheetName(FilePath); if (SheetName.Length > 0) { OleDbCommand myOleDbCommand = new OleDbCommand("SELECT * FROM [" + SheetName + "$]", conn); OleDbDataAdapter myData = new OleDbDataAdapter(myOleDbCommand); myData.Fill(rs); conn.Close(); } } intTmp = ObjFun.ExecuteSqlCmd("delete from ImportErr"); //清空ImportErr数据表 int intSubjectID, intLoreID, intTestTypeID, intOptionNum, intTypeTime, intStandardSpeed, intCreateUserID; string strSubjectName, strLoreName, strTestTypeName, strBaseTestType, strTestDiff, strTestMark, strTestContent, strOptionContent, strStandardAnswer, strTestParse; double dblTestMark; string[] strArrOptionContent, strArrTypeStandardAnswer; intCreateUserID = Convert.ToInt32(myUserID); DateTime dtmCreateDate; System.DateTime currentTime = new System.DateTime(); currentTime = System.DateTime.Now; dtmCreateDate = Convert.ToDateTime(Convert.ToString(currentTime.ToString("d"))); int i = 0, index = 0; for (i = 0; i < rs.Rows.Count; i++) { strSubjectName = ObjFun.CheckString(rs.Rows[i][0].ToString().Trim()); //科目名称 strLoreName = ObjFun.CheckString(rs.Rows[i][1].ToString().Trim()); //知识点 strTestTypeName = ObjFun.CheckString(rs.Rows[i][2].ToString().Trim()); //题型名称 strTestDiff = ObjFun.CheckString(rs.Rows[i][3].ToString().Trim()); //试题难度 strTestMark = rs.Rows[i][4].ToString().Trim(); //试题分数 strTestContent = ObjFun.getStr(ObjFun.CheckTestStr(rs.Rows[i][5].ToString().Trim()), 4000); //试题内容 strOptionContent = ObjFun.getStr(ObjFun.CheckTestStr(rs.Rows[i][6].ToString().Trim()), 1800); //试题选项 strStandardAnswer = ObjFun.getStr(ObjFun.CheckTestStr(rs.Rows[i][7].ToString().Trim()), 2000); //标准答案 strTestParse = ObjFun.getStr(ObjFun.CheckTestStr(rs.Rows[i][8].ToString().Trim()), 500); //试题解析 intSubjectID = 0; intLoreID = 0; intTestTypeID = 0; //科目名称 if (strSubjectName == "") { index = index + 1; intTmp = ObjFun.ExecuteSqlCmd("insert into ImportErr(ErrID,RowNum,ErrInfo) values(" + index + "," + (i + 2) + ",'科目名称为空')"); } else { strTmp = ObjFun.GetValues("select SubjectID from SubjectInfo where SubjectName='" + ObjFun.CheckString(strSubjectName) + "'", "SubjectID"); if (strTmp == "") { index = index + 1; intTmp = ObjFun.ExecuteSqlCmd("insert into ImportErr(ErrID,RowNum,ErrInfo) values(" + index + "," + (i + 2) + ",'科目名称不存在')"); } else { intSubjectID = Convert.ToInt32(strTmp); } } //知识点 if (strLoreName == "") { index = index + 1; intTmp = ObjFun.ExecuteSqlCmd("insert into ImportErr(ErrID,RowNum,ErrInfo) values(" + index + "," + (i + 2) + ",'知识点为空')"); } else { strTmp = ObjFun.GetValues("select SubjectID from SubjectInfo where SubjectName='" + ObjFun.CheckString(strSubjectName) + "'", "SubjectID"); if (strTmp != "") { if (ObjFun.GetValues("select LoreID from LoreInfo where SubjectID=" + Convert.ToInt32(strTmp) + " and LoreName='" + ObjFun.CheckString(strLoreName) + "'", "LoreID") == "") { index = index + 1; intTmp = ObjFun.ExecuteSqlCmd("insert into ImportErr(ErrID,RowNum,ErrInfo) values(" + index + "," + (i + 2) + ",'当前科目知识点不存在')"); } else { intLoreID = Convert.ToInt32(ObjFun.GetValues("select LoreID from LoreInfo where SubjectID=" + Convert.ToInt32(strTmp) + " and LoreName='" + ObjFun.CheckString(strLoreName) + "'", "LoreID")); } } } //题型名称 if (strTestTypeName == "") { index = index + 1; intTmp = ObjFun.ExecuteSqlCmd("insert into ImportErr(ErrID,RowNum,ErrInfo) values(" + index + "," + (i + 2) + ",'题型名称为空')"); } else { if (ObjFun.GetValues("select TestTypeID from TestTypeInfo where TestTypeName='" + ObjFun.CheckString(strTestTypeName) + "'", "TestTypeID") == "") { index = index + 1; intTmp = ObjFun.ExecuteSqlCmd("insert into ImportErr(ErrID,RowNum,ErrInfo) values(" + index + "," + (i + 2) + ",'题型名称不存在')"); } else { intTestTypeID = Convert.ToInt32(ObjFun.GetValues("select TestTypeID from TestTypeInfo where TestTypeName='" + ObjFun.CheckString(strTestTypeName) + "'", "TestTypeID")); } } //试题难度 if ((strTestDiff != "易") && (strTestDiff != "较易") && (strTestDiff != "中等") && (strTestDiff != "较难") && (strTestDiff != "难")) { index = index + 1; intTmp = ObjFun.ExecuteSqlCmd("insert into ImportErr(ErrID,RowNum,ErrInfo) values(" + index + "," + (i + 2) + ",'试题难度不正确')"); } //试题分数 try { dblTestMark = Convert.ToDouble(strTestMark); } catch { index = index + 1; intTmp = ObjFun.ExecuteSqlCmd("insert into ImportErr(ErrID,RowNum,ErrInfo) values(" + index + "," + (i + 2) + ",'试题分数不正确')"); } if ((intSubjectID != 0) && (intLoreID != 0) && (intTestTypeID != 0)) { //试题内容 if (strTestContent == "") { index = index + 1; intTmp = ObjFun.ExecuteSqlCmd("insert into ImportErr(ErrID,RowNum,ErrInfo) values(" + index + "," + (i + 2) + ",'试题内容为空')"); } //题型检查 strTmp = ObjFun.GetValues("select RubricID from RubricInfo where SubjectID='" + intSubjectID + "' and LoreID='" + intLoreID + "' and TestContent='" + strTestContent + "'", "RubricID"); if (strTmp.Trim() != "") { index = index + 1; intTmp = ObjFun.ExecuteSqlCmd("insert into ImportErr(ErrID,RowNum,ErrInfo) values(" + index + "," + (i + 2) + ",'当前科目知识点中已经存在此试题')"); } else { if (ObjFun.GetValues("select BaseTestType from TestTypeInfo where TestTypeID='" + intTestTypeID + "'", "BaseTestType") == "单选类") { if (strOptionContent.IndexOf("|") < 0) { index = index + 1; intTmp = ObjFun.ExecuteSqlCmd("insert into ImportErr(ErrID,RowNum,ErrInfo) values(" + index + "," + (i + 2) + ",'试题选项内容未用“|”隔开')"); } if ((strStandardAnswer.ToUpper() != "A") && (strStandardAnswer.ToUpper() != "B") && (strStandardAnswer.ToUpper() != "C") && (strStandardAnswer.ToUpper() != "D") && (strStandardAnswer.ToUpper() != "E") && (strStandardAnswer.ToUpper() != "F")) { index = index + 1; intTmp = ObjFun.ExecuteSqlCmd("insert into ImportErr(ErrID,RowNum,ErrInfo) values(" + index + "," + (i + 2) + ",'试题答案不正确')"); } } if (ObjFun.GetValues("select BaseTestType from TestTypeInfo where TestTypeID='" + intTestTypeID + "'", "BaseTestType") == "多选类") { if (strOptionContent.IndexOf("|") < 0) { index = index + 1; intTmp = ObjFun.ExecuteSqlCmd("insert into ImportErr(ErrID,RowNum,ErrInfo) values(" + index + "," + (i + 2) + ",'试题选项内容未用“|”隔开')"); } if ((strStandardAnswer.ToUpper().IndexOf("A") < 0) && (strStandardAnswer.ToUpper().IndexOf("B") < 0) && (strStandardAnswer.ToUpper().IndexOf("C") < 0) && (strStandardAnswer.ToUpper().IndexOf("D") < 0) && (strStandardAnswer.ToUpper().IndexOf("E") < 0) && (strStandardAnswer.ToUpper().IndexOf("F") < 0)) { index = index + 1; intTmp = ObjFun.ExecuteSqlCmd("insert into ImportErr(ErrID,RowNum,ErrInfo) values(" + index + "," + (i + 2) + ",'试题答案不正确')"); } } if (ObjFun.GetValues("select BaseTestType from TestTypeInfo where TestTypeID='" + intTestTypeID + "'", "BaseTestType") == "判断类") { if ((strStandardAnswer.ToUpper() != "正确") && (strStandardAnswer.ToUpper() != "错误")) { index = index + 1; intTmp = ObjFun.ExecuteSqlCmd("insert into ImportErr(ErrID,RowNum,ErrInfo) values(" + index + "," + (i + 2) + ",'试题答案不正确')"); } } if (ObjFun.GetValues("select BaseTestType from TestTypeInfo where TestTypeID='" + intTestTypeID + "'", "BaseTestType") == "填空类") { if (strTestContent.IndexOf("___") < 0) { index = index + 1; intTmp = ObjFun.ExecuteSqlCmd("insert into ImportErr(ErrID,RowNum,ErrInfo) values(" + index + "," + (i + 2) + ",'试题内容中未用3个下划线“_”表示填空位置')"); } Regex regex1 = new Regex("___"); Regex regex2 = new Regex(","); if (regex1.Matches(strTestContent, 0).Count != (regex2.Matches(strStandardAnswer, 0).Count + 1)) { index = index + 1; intTmp = ObjFun.ExecuteSqlCmd("insert into ImportErr(ErrID,RowNum,ErrInfo) values(" + index + "," + (i + 2) + ",'填空位置与答案数量不一致,答案间用半角逗号“,”隔开')"); } } if ((ObjFun.GetValues("select BaseTestType from TestTypeInfo where TestTypeID='" + intTestTypeID + "'", "BaseTestType") == "问答类") || (ObjFun.GetValues("select BaseTestType from TestTypeInfo where TestTypeID='" + intTestTypeID + "'", "BaseTestType") == "作文类")) { if (strStandardAnswer == "") { index = index + 1; intTmp = ObjFun.ExecuteSqlCmd("insert into ImportErr(ErrID,RowNum,ErrInfo) values(" + index + "," + (i + 2) + ",'试题答案不能为空')"); } } if (ObjFun.GetValues("select BaseTestType from TestTypeInfo where TestTypeID='" + intTestTypeID + "'", "BaseTestType") == "打字类") { if (strStandardAnswer.IndexOf(",") < 0) { index = index + 1; intTmp = ObjFun.ExecuteSqlCmd("insert into ImportErr(ErrID,RowNum,ErrInfo) values(" + index + "," + (i + 2) + ",'试题参数未用“,”隔开')"); } strArrTypeStandardAnswer = strStandardAnswer.Split(','); try { intTypeTime = Convert.ToInt32(strArrTypeStandardAnswer[0]); } catch { index = index + 1; intTmp = ObjFun.ExecuteSqlCmd("insert into ImportErr(ErrID,RowNum,ErrInfo) values(" + index + "," + (i + 2) + ",'试题参数的打字时间不正确')"); } try { intStandardSpeed = Convert.ToInt32(strArrTypeStandardAnswer[1]); } catch { index = index + 1; intTmp = ObjFun.ExecuteSqlCmd("insert into ImportErr(ErrID,RowNum,ErrInfo) values(" + index + "," + (i + 2) + ",'试题参数的标准速度不正确')"); } } if (ObjFun.GetValues("select BaseTestType from TestTypeInfo where TestTypeID='" + intTestTypeID + "'", "BaseTestType") == "操作类") { } } } } string strSql = "select a.ErrID,a.RowNum,a.ErrInfo from ImportErr a order by a.ErrID asc"; string strConn = ConfigurationSettings.AppSettings["strConn"]; SqlConnection SqlConn = new SqlConnection(strConn); SqlDataAdapter SqlCmd = new SqlDataAdapter(strSql, SqlConn); DataSet SqlDS = new DataSet(); SqlCmd.Fill(SqlDS, "ImportErr"); if (SqlDS.Tables["ImportErr"].Rows.Count > 0) //如果导入有错误,则绑定DataGridErr { DataGridErr.DataSource = SqlDS; DataGridErr.AllowPaging = false; DataGridErr.DataBind(); DataGridErr.Visible = true; LabelMessage.Text = "导入试题失败!"; } else //试题导入UserInfo数据表 { DataGridErr.DataSource = null; DataGridErr.Visible = false; strConn = ConfigurationSettings.AppSettings["strConn"]; SqlConnection ObjConn = new SqlConnection(strConn); ObjConn.Open(); SqlTransaction ObjTran = ObjConn.BeginTransaction(); SqlCommand ObjCmd = new SqlCommand(); ObjCmd.Connection = ObjConn; ObjCmd.Transaction = ObjTran; try { for (i = 0; i < rs.Rows.Count; i++) { strSubjectName = ObjFun.CheckString(rs.Rows[i][0].ToString().Trim()); //科目名称 strLoreName = ObjFun.CheckString(rs.Rows[i][1].ToString().Trim()); //知识点 strTestTypeName = ObjFun.CheckString(rs.Rows[i][2].ToString().Trim()); //题型名称 strTestDiff = ObjFun.CheckString(rs.Rows[i][3].ToString().Trim()); //试题难度 strTestMark = rs.Rows[i][4].ToString().Trim(); //试题分数 strTestContent = ObjFun.getStr(ObjFun.CheckTestStr(rs.Rows[i][5].ToString().Trim()), 4000); //试题内容 strOptionContent = ObjFun.getStr(ObjFun.CheckTestStr(rs.Rows[i][6].ToString().Trim()), 1800); //试题选项 strStandardAnswer = ObjFun.getStr(ObjFun.CheckTestStr(rs.Rows[i][7].ToString().Trim()), 2000); //标准答案 strTestParse = ObjFun.getStr(ObjFun.CheckTestStr(rs.Rows[i][8].ToString().Trim()), 500); //试题解析 intSubjectID = 0; intLoreID = 0; intTestTypeID = 0; strTmp = ObjFun.GetValues("select SubjectID from SubjectInfo where SubjectName='" + ObjFun.CheckString(strSubjectName) + "'", "SubjectID"); if (strTmp != "") { intSubjectID = Convert.ToInt32(strTmp); } strTmp = ObjFun.GetValues("select LoreID from LoreInfo where SubjectID=" + intSubjectID + " and LoreName='" + ObjFun.CheckString(strLoreName) + "'", "LoreID"); if (strTmp != "") { intLoreID = Convert.ToInt32(strTmp); } strTmp = ObjFun.GetValues("select TestTypeID from TestTypeInfo where TestTypeName='" + ObjFun.CheckString(strTestTypeName) + "'", "TestTypeID"); if (strTmp != "") { intTestTypeID = Convert.ToInt32(strTmp); } if ((intSubjectID != 0) && (intLoreID != 0) && (intTestTypeID != 0)) { strTmp = ObjFun.GetValues("select RubricID from RubricInfo where SubjectID='" + intSubjectID + "' and LoreID='" + intLoreID + "' and TestContent='" + strTestContent.Trim() + "'", "RubricID"); if (strTmp.Trim() == "") { dblTestMark = Convert.ToDouble(strTestMark); strBaseTestType = ""; intOptionNum = 4; if (ObjFun.GetValues("select BaseTestType from TestTypeInfo where TestTypeID='" + intTestTypeID + "'", "BaseTestType") == "单选类") { strBaseTestType = "单选类"; strArrOptionContent = strOptionContent.Split('|'); intOptionNum = strArrOptionContent.Length; strStandardAnswer = strStandardAnswer.ToUpper(); } if (ObjFun.GetValues("select BaseTestType from TestTypeInfo where TestTypeID='" + intTestTypeID + "'", "BaseTestType") == "多选类") { strBaseTestType = "多选类"; strArrOptionContent = strOptionContent.Split('|'); intOptionNum = strArrOptionContent.Length; strStandardAnswer = strStandardAnswer.ToUpper(); } if (ObjFun.GetValues("select BaseTestType from TestTypeInfo where TestTypeID='" + intTestTypeID + "'", "BaseTestType") == "判断类") { strBaseTestType = "判断类"; } if (ObjFun.GetValues("select BaseTestType from TestTypeInfo where TestTypeID='" + intTestTypeID + "'", "BaseTestType") == "填空类") { strBaseTestType = "填空类"; } if (ObjFun.GetValues("select BaseTestType from TestTypeInfo where TestTypeID='" + intTestTypeID + "'", "BaseTestType") == "问答类") { strBaseTestType = "问答类"; } if (ObjFun.GetValues("select BaseTestType from TestTypeInfo where TestTypeID='" + intTestTypeID + "'", "BaseTestType") == "作文类") { strBaseTestType = "作文类"; } if (ObjFun.GetValues("select BaseTestType from TestTypeInfo where TestTypeID='" + intTestTypeID + "'", "BaseTestType") == "打字类") { strBaseTestType = "打字类"; } if (ObjFun.GetValues("select BaseTestType from TestTypeInfo where TestTypeID='" + intTestTypeID + "'", "BaseTestType") == "操作类") { strBaseTestType = "操作类"; strStandardAnswer = ""; } intTmp = ObjFun.ExecuteSqlCmd("Insert into RubricInfo(SubjectID,LoreID,TestTypeID,TestDiff,OptionNum,TestMark,TestContent,OptionContent,StandardAnswer,TestParse,CreateUserID,CreateDate) Values (" + intSubjectID + "," + intLoreID + "," + intTestTypeID + ",'" + strTestDiff + "'," + intOptionNum + "," + dblTestMark + ",'" + strTestContent + "','" + strOptionContent + "','" + strStandardAnswer + "','" + strTestParse + "'," + intCreateUserID + ",'" + dtmCreateDate + "')"); } } } ObjTran.Commit(); } catch { ObjTran.Rollback(); LabelMessage.Text = "导入试题失败!"; } finally { ObjConn.Close(); ObjConn.Dispose(); LabelMessage.Text = "导入试题成功!"; } } } } }