//-----------------------------------------------------------------
/// <summary>
/// 增加助教信息
/// </summary>
/// <param name="assistant">助教信息</param>
public void AddAssistant(AssistantsList assistant)
{
try
{
string sql = "INSERT INTO usta_TeachersList VALUES(@assistantNo,@assistantUserPwd, @assistantName,@emailAddress ,@officeAddress,@remark,@type)";
SqlParameter[] parameters = {
new SqlParameter("@assistantNo", SqlDbType.NVarChar,50),
new SqlParameter("@assistantUserPwd", SqlDbType.NChar,32),
new SqlParameter("@assistantName", SqlDbType.NChar,10),
new SqlParameter("@emailAddress", SqlDbType.NChar,50),
new SqlParameter("@officeAddress", SqlDbType.NChar,50),
new SqlParameter("@remark", SqlDbType.NVarChar,500),
new SqlParameter("@type", SqlDbType.Int) };
parameters[0].Value = assistant.assistantNo;
parameters[1].Value = CommonUtility.EncodeUsingMD5(assistant.assistantUserPwd);
parameters[2].Value = assistant.assistantName;
parameters[3].Value = assistant.emailAddress;
parameters[4].Value = assistant.officeAddress;
parameters[5].Value = assistant.remark;
parameters[6].Value = 2;
SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql, parameters);
}
catch (Exception ex)
{
MongoDBLog.LogRecord(ex);
CommonUtility.RedirectUrl();
}
finally
{
conn.Close();
}
}
/// <summary>
/// 查询助教信息
/// </summary>
/// <param name="assistantNo">助教编号</param>
/// <returns>助教信息</returns>
public AssistantsList FindAssistantByNo(string assistantNo)
{
AssistantsList assistant = null;
string cmdstring = "SELECT teacherNo as [assistantNo] ,teacherUserPwd as [assistantUserPwd] , teacherName as [assistantName] ,[emailAddress] ,[officeAddress] ,[remark] FROM [USTA].[dbo].[usta_TeachersList] WHERE teacherNo=@teacherNo AND type=2";
SqlParameter[] parameters ={
new SqlParameter("@teacherNo", SqlDbType.NVarChar,50)
};
parameters[0].Value = assistantNo;
SqlDataReader dr = SqlHelper.ExecuteReader(conn, CommandType.Text, cmdstring, parameters);
if (dr.Read())
{
assistant = new AssistantsList
{
assistantUserPwd = dr["assistantUserPwd"].ToString().Trim(),
assistantNo = dr["assistantNo"].ToString().Trim(),
assistantName = dr["assistantName"].ToString().Trim(),
emailAddress = dr["emailAddress"].ToString().Trim(),
officeAddress = dr["officeAddress"].ToString().Trim(),
remark = dr["remark"].ToString().Trim()
};
}
dr.Close();
conn.Close();
return assistant;
}
/// <summary>
///将Excel表的信息数据保存到数据库
/// </summary>
/// <param name="dt">文件名称</param>
/// <param name="tableType">表类型</param>
/// <returns>删除是否成功</returns>
public int DataTabletoDBTables(DataTable dt,string tableType)
{
if (tableType.Equals("学生信息"))
{
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
StudentsList student = new StudentsList();
student.studentNo = dt.Rows[i][1].ToString().Trim();
student.studentName = dt.Rows[i][2].ToString().Trim();
student.studentSpeciality = dt.Rows[i][3].ToString().Trim();
student.classNo = dt.Rows[i][4].ToString().Trim();
student.mobileNo = dt.Rows[i][5].ToString().Trim();
student.emailAddress = dt.Rows[i][6].ToString().Trim();
student.remark = dt.Rows[i][7].ToString().Trim();
student.studentUserPwd = student.studentNo;//密码默认与学号相同
this.AddStudent(student);
}
return dt.Rows.Count;
}
}
else if (tableType.Equals("教师信息"))
{
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
TeachersList teacher = new TeachersList();
teacher.teacherNo = dt.Rows[i][1].ToString().Trim();
teacher.teacherName = dt.Rows[i][2].ToString().Trim();
teacher.emailAddress = dt.Rows[i][3].ToString().Trim();
teacher.officeAddress = dt.Rows[i][4].ToString().Trim();
teacher.remark = dt.Rows[i][5].ToString().Trim();
teacher.teacherUserPwd = teacher.teacherNo;//密码默认与教师编号相同
this.AddTeacher(teacher);
}
return dt.Rows.Count;
}
}
else if (tableType.Equals("助教信息"))
{
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
AssistantsList assistant = new AssistantsList();
assistant.assistantNo = dt.Rows[i][1].ToString().Trim();
assistant.assistantName = dt.Rows[i][2].ToString().Trim();
assistant.emailAddress = dt.Rows[i][3].ToString().Trim();
assistant.officeAddress = dt.Rows[i][4].ToString().Trim();
assistant.remark = dt.Rows[i][5].ToString().Trim();
assistant.assistantUserPwd = assistant.assistantNo;//密码默认与助教编号相同
this.AddAssistant(assistant);
}
return dt.Rows.Count;
}
}
else if (tableType.Equals("课程信息"))
{
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
Courses course = new Courses();
course.courseNo = dt.Rows[i][1].ToString().Trim();
course.courseName= dt.Rows[i][2].ToString().Trim();
course.period =dt.Rows[i][3].ToString().Trim();
course.credit=float.Parse( dt.Rows[i][4].ToString().Trim());
course.courseSpeciality= dt.Rows[i][5].ToString();
course.termTag =dt.Rows[i][6].ToString().Trim();//学期标识
new DalOperationAboutCourses().AddCourse(course);
}
return dt.Rows.Count;
}
}
else if (tableType.Equals("考试安排"))
{
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
ExamArrangeList examTime = new ExamArrangeList();
examTime.courseName= dt.Rows[i][1].ToString().Trim();
examTime.examArrangeTime =DateTime.Parse(dt.Rows[i][2].ToString().Trim());
examTime.examArrageAddress = dt.Rows[i][3].ToString().Trim();
examTime.remark =dt.Rows[i][4].ToString().Trim();
examTime.teacherName = dt.Rows[i][5].ToString();
examTime.courseNo= dt.Rows[i][6].ToString().Trim();
this.AddExamArrange(examTime);
}
return dt.Rows.Count;
}
}
return 0;
}
/// <summary>
/// 修改助教信息
/// </summary>
/// <param name="assistant">助教信息</param>
public void UpdateAssistantByAssistant(AssistantsList assistant)
{
try
{
string sql = "UPDATE usta_TeachersList SET teacherUserPwd=@assistantUserPwd,officeAddress=@officeAddress, emailAddress=@emailAddress, remark=@remark, teacherName=@assistantName WHERE teacherNo=@assistantNo AND type=2";
SqlParameter[] parameters = {
new SqlParameter("@assistantNo", SqlDbType.NVarChar,50),
new SqlParameter("@assistantUserPwd", SqlDbType.NChar,32),
new SqlParameter("@assistantName", SqlDbType.NChar,10),
new SqlParameter("@emailAddress", SqlDbType.NChar,50),
new SqlParameter("@officeAddress", SqlDbType.NChar,50),
new SqlParameter("@remark", SqlDbType.NVarChar,500)};
parameters[0].Value = assistant.assistantNo;
parameters[1].Value = assistant.assistantUserPwd;
parameters[2].Value = assistant.assistantName;
parameters[3].Value = assistant.emailAddress;
parameters[4].Value = assistant.officeAddress;
parameters[5].Value = assistant.remark;
SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql, parameters);
}
catch (Exception ex)
{
MongoDBLog.LogRecord(ex);
CommonUtility.RedirectUrl();
}
finally
{
conn.Close();
}
}
/// <summary>
/// 通过助教的编号获得助教的一个实体
/// </summary>
/// <param name="assistantNo">助教编号</param>
/// <returns>助教对象</returns>
public AssistantsList GetAssistantbyId(string assistantNo)
{
AssistantsList assistant=null;
string cmdstring = "SELECT teacherNo as assistantNo, teacherName as [assistantName] ,[emailAddress] ,[officeAddress] ,[remark] FROM [USTA].[dbo].[usta_TeachersList] WHERE teacherNo=@assistantNo AND type='2'";
SqlParameter[] parameters = new SqlParameter[1]{
new SqlParameter("@assistantNo",assistantNo)
};
SqlDataReader dr=SqlHelper.ExecuteReader(conn,CommandType.Text,cmdstring,parameters);
while(dr.Read()){
assistant = new AssistantsList
{
assistantNo = dr["assistantNo"].ToString().Trim(),
assistantName = dr["assistantName"].ToString().Trim(),
emailAddress = dr["emailAddress"].ToString().Trim(),
officeAddress = dr["officeAddress"].ToString().Trim(),
remark = dr["remark"].ToString().Trim()
};
}
dr.Close();
conn.Close();
return assistant;
}
/// <summary>
/// 根据工作薄的名称判断进行对应实体类数据的封装
/// </summary>
/// <param name="sheetName">工作薄名称</param>
/// <param name="list">具体实体类项目的集合</param>
/// <returns>返回封装后的实体类数据</returns>
public static object ReturnModelDataJudgeBySheetName(string sheetName, List<string> list)
{
object modelClassCorrelationSheet = null;
switch (sheetName)
{
case "Sheet1":
modelClassCorrelationSheet =
new TeachersList
{
teacherNo = list[0],
teacherName = list[1],
emailAddress = list[2],
officeAddress = list[3],
remark = list[4],
teacherUserPwd = list[5]
};
break;
case "Sheet2":
modelClassCorrelationSheet = new AssistantsList
{
assistantNo = list[0],
assistantName = list[1],
emailAddress = list[2],
officeAddress = list[3],
remark = list[4],
assistantUserPwd = list[5]
};
break;
case "Sheet3":
modelClassCorrelationSheet = new StudentsList
{
studentNo = list[0],
studentName = list[1],
studentSpeciality = list[2],
classNo = list[3],
mobileNo = list[4],
emailAddress = list[5],
remark = list[6],
studentUserPwd = list[7]
};
break;
case "Sheet4":
modelClassCorrelationSheet = new TermTags
{
termTag = list[0]
};
break;
case "Sheet5":
modelClassCorrelationSheet = new Courses
{
courseNo = list[0],
courseName = list[1],
period = list[2],
credit = float.Parse(list[3]),
courseSpeciality = list[4],
termTag = list[5],
preCourse = string.Empty,
referenceBooks = string.Empty,
attachmentIds = string.Empty
};
break;
case "Sheet6":
modelClassCorrelationSheet = new CoursesTeachersCorrelation
{
teacherNo = list[0],
courseNo = list[1]
};
break;
case "Sheet7":
modelClassCorrelationSheet = new CoursesAssistantsCorrelation
{
assistantNo = list[0],
courseNo = list[1]
};
break;
case "Sheet8":
modelClassCorrelationSheet = new CoursesStudentsCorrelation
{
studentNo = list[0],
courseNo = list[1]
};
break;
default:
break;
}
return modelClassCorrelationSheet;
}