/// <summary>
/// 通过学号studentNo查询学生信息
/// </summary>
/// <param name="studentNo">学生编号</param>
/// <returns>学生信息</returns>
public StudentsList FindStudentByNo(string studentNo)
{
StudentsList student = null;
string sql = "SELECT studentUserPwd,studentName,studentNo,studentSpeciality,mobileNo,SchoolClassName,emailAddress,remark FROM usta_StudentsList WHERE studentNo=@studentNo";
SqlParameter[] parameters = {
new SqlParameter("@studentNo", SqlDbType.NChar,10)
};
parameters[0].Value = studentNo;
SqlDataReader dr= SqlHelper.ExecuteReader(conn, CommandType.Text, sql, parameters);
while(dr.Read())
{
student = new StudentsList();
student.studentNo = dr["studentNo"].ToString();
student.studentName = dr["studentName"].ToString();
student.studentUserPwd = dr["studentUserPwd"].ToString().Trim();
student.studentSpeciality = dr["studentSpeciality"].ToString();
student.mobileNo = dr["mobileNo"].ToString();
student.emailAddress = dr["emailAddress"].ToString();
student.classNo = dr["SchoolClassName"].ToString();
student.remark = dr["remark"].ToString();
}
dr.Close();
conn.Close();
return student;
}
/// <summary>
/// 通过学号获得学生
/// </summary>
/// <param name="studentNo">学号</param>
/// <returns>学生实体</returns>
public StudentsList GetStudentById(string studentNo)
{
StudentsList student = null;
string cmdstring = "SELECT [studentNo] ,[studentName] ,[studentSpeciality],[mobileNo],[emailAddress] ,[remark],SchoolClassName FROM [USTA].[dbo].[usta_StudentsList] WHERE studentNo=@studentNo";
SqlParameter[] parameters ={
new SqlParameter("@studentNo", SqlDbType.NChar,10),
};
parameters[0].Value = studentNo;
SqlDataReader dr = SqlHelper.ExecuteReader(conn, CommandType.Text, cmdstring, parameters);
if (dr.Read())
{
student = new StudentsList
{
studentNo = dr["studentNo"].ToString().Trim(),
studentName = dr["studentName"].ToString().Trim(),
studentSpeciality = dr["studentSpeciality"].ToString().Trim(),
mobileNo = dr["mobileNo"].ToString().Trim(),
emailAddress = dr["emailAddress"].ToString().Trim(),
remark = dr["remark"].ToString().Trim(),
SchoolClassName = dr["SchoolClassName"].ToString().Trim()
};
}
return student;
}
/// <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="student">学生信息</param>
public void AddStudent(StudentsList student)
{
try
{
string sql = "INSERT INTO usta_StudentsList VALUES(@studentNo,@studentName ,@studentUserPwd ,@studentSpeciality,@mobileNo, @emailAddress,@remark,@classNo)";
SqlParameter[] parameters = {
new SqlParameter("@studentNo", SqlDbType.NChar,10),
new SqlParameter("@studentName", SqlDbType.NChar,10),
new SqlParameter("@studentUserPwd", SqlDbType.NChar,32),
new SqlParameter("@studentSpeciality", SqlDbType.NChar,15),
new SqlParameter("@mobileNo", SqlDbType.NChar,50),
new SqlParameter("@emailAddress", SqlDbType.NChar,50),
new SqlParameter("@remark", SqlDbType.NVarChar,500),
new SqlParameter("@classNo", SqlDbType.NChar,5)};
parameters[0].Value = student.studentNo;
parameters[1].Value = student.studentName;
parameters[2].Value =CommonUtility.EncodeUsingMD5(student.studentUserPwd);
parameters[3].Value = student.studentSpeciality;
parameters[4].Value = student.mobileNo;
parameters[5].Value = student.emailAddress;
parameters[6].Value = student.remark;
parameters[7].Value = student.classNo;
SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql, parameters);
}
catch (Exception ex)
{
MongoDBLog.LogRecord(ex);
CommonUtility.RedirectUrl();
}
finally
{
conn.Close();
}
}
/// <summary>
/// 修改学生信息
/// </summary>
/// <param name="student">学生信息</param>
public void UpdateStudentByStudent(StudentsList student)
{
try
{
string sql = "UPDATE usta_StudentsList SET studentUserPwd=@studentUserPwd,mobileNo=@mobileNo, emailAddress=@emailAddress, remark=@remark, studentSpeciality=@studentSpeciality, studentName=@studentName,classNo=@classNo WHERE studentNo=@studentNo";
SqlParameter[] parameters = {
new SqlParameter("@studentNo", SqlDbType.NChar,10),
new SqlParameter("@studentName", SqlDbType.NChar,10),
new SqlParameter("@studentUserPwd", SqlDbType.NChar,32),
new SqlParameter("@studentSpeciality", SqlDbType.NChar,15),
new SqlParameter("@mobileNo", SqlDbType.NChar,50),
new SqlParameter("@emailAddress", SqlDbType.NChar,50),
new SqlParameter("@remark", SqlDbType.NVarChar,500),
new SqlParameter("@classNo", SqlDbType.NChar,5)};
parameters[0].Value = student.studentNo;
parameters[1].Value = student.studentName;
parameters[2].Value = student.studentUserPwd;
parameters[3].Value = student.studentSpeciality;
parameters[4].Value = student.mobileNo;
parameters[5].Value = student.emailAddress;
parameters[6].Value = student.remark;
parameters[7].Value = student.classNo;
SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql, parameters);
}
catch (Exception ex)
{
MongoDBLog.LogRecord(ex);
CommonUtility.RedirectUrl();
}
finally
{
conn.Close();
}
}
/// <summary>
/// 获取报名学生所需的相关信息
/// </summary>
/// <returns></returns>
public StudentsList GetEnglishExamSignUpStudentInfoByStudentNo(string studentNo)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 studentNo,studentName,studentUserPwd,studentSpeciality,mobileNo,emailAddress,remark,classNo,StudentID,StudentUSID,MajorType,SchoolClass,SchoolClassName,isAdmin,Sex,CardNum,CardType,MatriculationDate from usta_StudentsList ");
strSql.Append(" where studentNo=@studentNo ");
SqlParameter[] parameters = {
new SqlParameter("@studentNo", SqlDbType.NChar,10)};
parameters[0].Value = studentNo;
StudentsList model = new StudentsList();
DataSet ds = SqlHelper.ExecuteDataset(conn
,CommandType.Text,strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
if (ds.Tables[0].Rows[0]["studentNo"] != null && ds.Tables[0].Rows[0]["studentNo"].ToString() != "")
{
model.studentNo = ds.Tables[0].Rows[0]["studentNo"].ToString();
}
if (ds.Tables[0].Rows[0]["studentName"] != null && ds.Tables[0].Rows[0]["studentName"].ToString() != "")
{
model.studentName = ds.Tables[0].Rows[0]["studentName"].ToString();
}
if (ds.Tables[0].Rows[0]["studentUserPwd"] != null && ds.Tables[0].Rows[0]["studentUserPwd"].ToString() != "")
{
model.studentUserPwd = ds.Tables[0].Rows[0]["studentUserPwd"].ToString();
}
if (ds.Tables[0].Rows[0]["studentSpeciality"] != null && ds.Tables[0].Rows[0]["studentSpeciality"].ToString() != "")
{
model.studentSpeciality = ds.Tables[0].Rows[0]["studentSpeciality"].ToString();
}
if (ds.Tables[0].Rows[0]["mobileNo"] != null && ds.Tables[0].Rows[0]["mobileNo"].ToString() != "")
{
model.mobileNo = ds.Tables[0].Rows[0]["mobileNo"].ToString();
}
if (ds.Tables[0].Rows[0]["emailAddress"] != null && ds.Tables[0].Rows[0]["emailAddress"].ToString() != "")
{
model.emailAddress = ds.Tables[0].Rows[0]["emailAddress"].ToString();
}
if (ds.Tables[0].Rows[0]["remark"] != null && ds.Tables[0].Rows[0]["remark"].ToString() != "")
{
model.remark = ds.Tables[0].Rows[0]["remark"].ToString();
}
if (ds.Tables[0].Rows[0]["classNo"] != null && ds.Tables[0].Rows[0]["classNo"].ToString() != "")
{
model.classNo = ds.Tables[0].Rows[0]["classNo"].ToString();
}
if (ds.Tables[0].Rows[0]["StudentID"] != null && ds.Tables[0].Rows[0]["StudentID"].ToString() != "")
{
model.StudentID = ds.Tables[0].Rows[0]["StudentID"].ToString();
}
if (ds.Tables[0].Rows[0]["MajorType"] != null && ds.Tables[0].Rows[0]["MajorType"].ToString() != "")
{
model.MajorType = ds.Tables[0].Rows[0]["MajorType"].ToString();
}
if (ds.Tables[0].Rows[0]["SchoolClass"] != null && ds.Tables[0].Rows[0]["SchoolClass"].ToString() != "")
{
model.SchoolClass = ds.Tables[0].Rows[0]["SchoolClass"].ToString();
}
if (ds.Tables[0].Rows[0]["SchoolClassName"] != null && ds.Tables[0].Rows[0]["SchoolClassName"].ToString() != "")
{
model.SchoolClassName = ds.Tables[0].Rows[0]["SchoolClassName"].ToString();
}
if (ds.Tables[0].Rows[0]["isAdmin"] != null && ds.Tables[0].Rows[0]["isAdmin"].ToString() != "")
{
if ((ds.Tables[0].Rows[0]["isAdmin"].ToString() == "1") || (ds.Tables[0].Rows[0]["isAdmin"].ToString().ToLower() == "true"))
{
model.isAdmin = true;
}
else
{
model.isAdmin = false;
}
}
if (ds.Tables[0].Rows[0]["Sex"] != null && ds.Tables[0].Rows[0]["Sex"].ToString() != "")
{
model.Sex = int.Parse(ds.Tables[0].Rows[0]["Sex"].ToString());
}
if (ds.Tables[0].Rows[0]["CardNum"] != null && ds.Tables[0].Rows[0]["CardNum"].ToString() != "")
{
model.CardNum = ds.Tables[0].Rows[0]["CardNum"].ToString();
}
if (ds.Tables[0].Rows[0]["CardType"] != null && ds.Tables[0].Rows[0]["CardType"].ToString() != "")
{
model.CardType = ds.Tables[0].Rows[0]["CardType"].ToString();
}
if (ds.Tables[0].Rows[0]["MatriculationDate"] != null && ds.Tables[0].Rows[0]["MatriculationDate"].ToString() != "")
{
model.MatriculationDate = DateTime.Parse(ds.Tables[0].Rows[0]["MatriculationDate"].ToString());
}
return model;
}
else
{
return null;
}
}
/// <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;
}
