/// <summary>
/// 将excel文件中的数据转换成选择题实体集合
/// </summary>
/// <param name="filePath">excel文件的物理路径</param>
/// <returns></returns>
private static IList<ChoiceItemInfo> ChangeExcelToChoices(string filePath)
{
IList<ChoiceItemInfo> choiceitems = new List<ChoiceItemInfo>();
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
if (filePath.EndsWith("xlsx")) strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"";
OleDbConnection myConn = new OleDbConnection(strConn);
string strCom = " SELECT * FROM [Sheet1$]";
OleDbCommand cmd = new OleDbCommand(strCom, myConn);
OleDbDataReader dr = null;
try
{
myConn.Open();
dr = cmd.ExecuteReader();
while (dr.Read())
{
//if(dr["姓名"]==null||dr["学号"]==null||dr["班级"]==null||dr["专业"]==null|| dr["家庭地址"]==null)
// continue;
string sAnswers = "";
SelectChoiceInfo s = new SelectChoiceInfo();
s.Title=dr["题目"].ToString();
sAnswers = dr["答案"].ToString();
if (sAnswers.Length == 1) s.IsSingleSelect = true;
else s.IsSingleSelect = false;
s.ChoiceCount = 4;
s.CourseID = BLLBase.ReturnID(BLLBase.GetCourseDic(), dr["课程"].ToString());
s.ChapterID = BLLBase.ReturnID(BLLBase.GetChapterDic(), dr["章节"].ToString());
int choiceID= Choice.InsertSelectChoice(s);
string[] choiceNames = { "A", "B", "C", "D" };
for (int i = 0; i < 4; i++)
{
string m= choiceNames[i];
ChoiceItemInfo c = new ChoiceItemInfo();
c.Title= dr[m].ToString();
if (sAnswers.Contains(m))
c.IsRight = true;
else c.IsRight = false;
c.SelectChoiceID = choiceID;
choiceitems.Add(c);
}
// HttpContext.Current.Response.Write("一条记录<br>");
}
}
catch (Exception e)
{
HttpContext.Current.Response.Write("错误信息：" + e.Message);
choiceitems = null;
}
finally
{
if (dr != null) dr.Close();
myConn.Close();
} return choiceitems;
}
public static bool InsertQuestionsByExcleFile(string filePath)
{
ExamDbDataContext dc = DataAccess.CreateDBContext();
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
if (filePath.EndsWith("xlsx")) strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"";
OleDbConnection myConn = new OleDbConnection(strConn);
string strCom = " SELECT * FROM [Sheet1$]";
OleDbCommand cmd = new OleDbCommand(strCom, myConn);
OleDbDataReader dr = null;
IList<QuestionInfo> questions = new List<QuestionInfo>();
IList<ChoiceItemInfo> choiceitems = new List<ChoiceItemInfo>();
try
{
myConn.Open();
dr = cmd.ExecuteReader();
while (dr.Read())//在此处逐条输入试题信息
{
string questionTypeName = dr["题型"].ToString();
if (string.IsNullOrEmpty(questionTypeName)) continue;
QuestionInfo q = new QuestionInfo();
q.Title=dr["题目内容"].ToString();
q.RefAnswer=dr["参考答案"].ToString();
string sAnswers = "";
switch (questionTypeName)
{
case "选择":
SelectChoiceInfo s = new SelectChoiceInfo();
s.Title = dr["题目内容"].ToString();
sAnswers = dr["参考答案"].ToString();
if (sAnswers.Length == 1) s.IsSingleSelect = true;
else s.IsSingleSelect = false;
s.ChoiceCount = 4;
s.CourseID = BLLBase.ReturnID(BLLBase.GetCourseDic(), dr["课程"].ToString());
s.ChapterID = BLLBase.ReturnID(BLLBase.GetChapterDic(), dr["章节"].ToString());
int choiceID= Choice.InsertSelectChoice(s);
string[] choiceNames = { "A", "B", "C", "D" };
for (int i = 0; i < 4; i++)
{
string m= choiceNames[i];
ChoiceItemInfo c = new ChoiceItemInfo();
c.Title= dr[m].ToString();
if (sAnswers.Contains(m))
c.IsRight = true;
else c.IsRight = false;
c.SelectChoiceID = choiceID;
choiceitems.Add(c);
}
break;
case "填空":
break;
case "判断":
break;
case "简答":
break;
case "程序填空":
break;
}
}
}
catch (Exception e)
{
HttpContext.Current.Response.Write("错误信息：" + e.Message);
return false;
}
finally
{
if (dr != null) dr.Close();
myConn.Close();
}
if (questions == null) return false;
dc.ChoiceItemInfo.InsertAllOnSubmit<ChoiceItemInfo>(choiceitems);
dc.SubmitChanges();
return true;
}
/// <summary>
/// 更新选择项信息
/// </summary>
/// <param name="s">选择项实体</param>
public static void UpdateChoiceItemByCopy(ChoiceItemInfo s)
{
ExamDbDataContext dc = DataAccess.CreateDBContext();
ChoiceItemInfo c = dc.ChoiceItemInfo.Single(p => p.ChoiceItemID == s.ChoiceItemID);
c.Title = s.Title;
c.IsRight = s.IsRight;
// PurgeCacheItems("AllChoiceItems_" + s.DepartmentID.ToString());
dc.SubmitChanges();
}
/// <summary>
/// 更新选择项信息
/// </summary>
/// <param name="s">选择项实体</param>
public static void UpdateChoiceItem(ChoiceItemInfo s, ExamDbDataContext dc)
{
AttachInfo<ChoiceItemInfo>(dc.ChoiceItemInfo, s);
dc.SubmitChanges();
// PurgeCacheItems("AllChoiceItems_" + s.DepartmentID.ToString());
}
/// <summary>
/// 根据指定专业id返回对应选择项,
/// </summary>
/// <param name="id">选择项id</param>
/// <returns></returns>
//public static IList<ChoiceItemInfo> GetChoiceItemsByID(int majorID)
//{
// ExamDbDataContext dc = DataAccess.CreateDBContext();
// var ChoiceItems = from s in dc.ChoiceItemInfo
// where s.MajorID==majorID
// select s;
// return ToList<ChoiceItemInfo>(ChoiceItems);
//}
/// <summary>
/// 删除选择项信息
/// </summary>
/// <param name="s">选择项实体</param>
public static void DeleteChoiceItem(ChoiceItemInfo s, ExamDbDataContext dc)
{
AttachInfo<ChoiceItemInfo>(dc.ChoiceItemInfo, s);
dc.ChoiceItemInfo.DeleteOnSubmit(s);
dc.SubmitChanges();
}
/// <summary>
/// 插入选择项信息
/// </summary>
/// <param name="s">选择项实体</param>
public static int InsertChoiceItem(ChoiceItemInfo s)
{
ExamDbDataContext dc = DataAccess.CreateDBContext();
dc.ChoiceItemInfo.InsertOnSubmit(s);
dc.SubmitChanges();
// PurgeCacheItems("AllSubjects_" + s.DepartmentID.ToString());
return s.ChoiceItemID;
}
private void InsertItemsOfChoice(int choiceID, IList<TextBox> tlist)
{
IList<ChoiceItemInfo> items = new List<ChoiceItemInfo>();
int i = 0;
foreach (TextBox t in tlist)
{
ChoiceItemInfo item = new ChoiceItemInfo();
item.Title =t.Text;
item.SelectChoiceID = choiceID;
item.IsRight = chkListAnswer.Items[i].Selected;
items.Add(item);
i++;
}
ChoiceItem.InsertChoiceItems(items);
}
private void InsertChoiceItem(int choiceID, string title, bool isRight)
{
ChoiceItemInfo item = new ChoiceItemInfo();
item.Title = title;
item.SelectChoiceID = choiceID;
item.IsRight = isRight;
ChoiceItem.InsertChoiceItem(item);
}
private void detach_ChoiceItemInfo(ChoiceItemInfo entity)
{
this.SendPropertyChanging();
entity.SelectChoiceInfo = null;
}