public void InsertStudentInfo(StudentInfoBusiness studentInfo)
{
string sql = "insert into StudentInfo values(@StudentClassID,@StudentName,@StudentNumber,@StudentSex,@StudentMarket)";
SqlParameter[] parameter = { new SqlParameter("@StudentClassID", studentInfo.StudentClassID),
new SqlParameter("@StudentName", studentInfo.StudentName),
new SqlParameter("@StudentNumber", studentInfo.StudentNumber),
new SqlParameter("@StudentSex", studentInfo.StudentSex),
new SqlParameter("@StudentMarket", studentInfo.StudentMarket) };
DBHelpers.UpdateInfo(sql, parameter);
}
public List <StudentInfoBusiness> GetAllStudentInfo(string Guanjianzi, string txt)
{
List <StudentInfoBusiness> list = new List <StudentInfoBusiness>();
StringBuilder sql = new StringBuilder("SELECT CollegeInfo.CollegeName, MajorInfo.MajorSimple, GradeInfo.GradeName, StudentClass.ClassName, StudentInfo.* FROM StudentInfo INNER JOIN StudentClass ON StudentInfo.StudentClassID = StudentClass.StudentClassID INNER JOIN MajorInfo ON StudentClass.MajorInfoID = MajorInfo.MajorInfoID INNER JOIN GradeInfo ON StudentClass.GradeInfoID = GradeInfo.GradeInfoID INNER JOIN CollegeInfo ON StudentClass.CollegeID = CollegeInfo.CollegeID where 1=1");
switch (Guanjianzi)
{
case "学生姓名":
sql.Append(string.Format(" and StudentInfo.StudentName like '{0}'", txt));
break;
case "学生学号":
sql.Append(string.Format(" and StudentInfo.StudentNumber like '{0}'", txt));
break;
case "班级名称":
sql.Append(string.Format(" and StudentClass.ClassName like '{0}'", txt));
break;
case "专业名称":
sql.Append(string.Format(" and MajorInfo.MajorSimple like '{0}'", txt));
break;
case "学院名称":
sql.Append(string.Format(" and CollegeInfo.CollegeName like '{0}'", txt));
break;
case "年级名称":
sql.Append(string.Format(" and GradeInfo.GradeName like '{0}'", txt));
break;
default:
break;
}
using (SqlDataReader sdr = DBHelpers.GetAllInfo(sql.ToString()))
{
while (sdr.Read())
{
StudentInfoBusiness studentinfo = new StudentInfoBusiness();
studentinfo.StudentName = sdr["StudentName"].ToString();
studentinfo.StudentNumber = sdr["StudentNumber"].ToString();
studentinfo.StudentMarket = sdr["StudentMarket"].ToString();
studentinfo.StudentSex = sdr["StudentSex"].ToString();
studentinfo.CollegeName = sdr["CollegeName"].ToString();
studentinfo.ClassName = sdr["ClassName"].ToString();
studentinfo.GradeName = sdr["GradeName"].ToString();
studentinfo.MajorSimple = sdr["MajorSimple"].ToString();
studentinfo.StudentClassID = Convert.ToInt32(sdr["StudentClassID"]);
studentinfo.StudentInfoID = Convert.ToInt32(sdr["StudentInfoID"]);
list.Add(studentinfo);
}
}
return(list);
}
public void UpdateStudentInfo(StudentInfoBusiness studentInfo)
{
string sql = string.Format("update studentInfo set StudentClassID=@StudentClassID,StudentName=@StudentName,StudentNumber=@StudentNumber,StudentSex=@StudentSex,StudentMarket=@StudentMarket where StudentInfoID=@StudentInfoID");
SqlParameter[] parameter = { new SqlParameter("@StudentClassID", studentInfo.StudentClassID),
new SqlParameter("@StudentName", studentInfo.StudentName),
new SqlParameter("@StudentNumber", studentInfo.StudentNumber),
new SqlParameter("@StudentSex", studentInfo.StudentSex),
new SqlParameter("@StudentMarket", studentInfo.StudentMarket),
new SqlParameter("@StudentInfoID", studentInfo.StudentInfoID) };
DBHelpers.UpdateInfo(sql, parameter);
}
public StudentInfoBusiness text()
{
StudentInfoBusiness studentinfo = new StudentInfoBusiness();
studentinfo.StudentClassID = Convert.ToInt32(cmbStudentClass.SelectedValue);
studentinfo.StudentName = txtStudentName.Text.Trim();
studentinfo.StudentNumber = txtStudentNumber.Text.Trim();
studentinfo.StudentSex = "男";
if (rbtnWoman.Checked)
{
studentinfo.StudentSex = "女";
}
studentinfo.StudentMarket = txtStudentMarket.Text.Trim();
try
{
studentinfo.StudentInfoID = Convert.ToInt32(dgvQuery.CurrentRow.Cells["学生信息编号"].Value);
}
catch (Exception)
{
//throw;
}
return(studentinfo);
}
/// <summary>
/// 获取学生信息的方法
/// </summary>
/// <param name="TypeID">可以选择sql语句的调用</param>
/// <param name="ID">ID可以表示学院信息ID 也可以表示班级信息ID</param>
/// <returns></returns>
public List <StudentInfoBusiness> GetAllStudentInfo(int TypeID, int ID)
{
List <StudentInfoBusiness> list = new List <StudentInfoBusiness>();
StringBuilder sql = new StringBuilder("SELECT StudentInfo.StudentInfoID,StudentInfo.StudentMarket, StudentClass.ClassName, StudentInfo.StudentName, StudentInfo.StudentNumber, StudentInfo.StudentSex, CollegeInfo.CollegeName, GradeInfo.GradeName, MajorInfo.MajorSimple FROM StudentInfo INNER JOIN StudentClass ON StudentInfo.StudentClassID = StudentClass.StudentClassID INNER JOIN CollegeInfo ON StudentClass.CollegeID = CollegeInfo.CollegeID INNER JOIN MajorInfo ON StudentClass.MajorInfoID = MajorInfo.MajorInfoID INNER JOIN GradeInfo ON StudentClass.GradeInfoID = GradeInfo.GradeInfoID where 1=1 ");
if (TypeID == 0)
{
sql.ToString();
}
if (TypeID == 1)
{
sql.Append(string.Format(" and CollegeInfo.CollegeID ={0}", ID));
}
if (TypeID == 2)
{
sql.Append(string.Format(" and StudentClass.StudentClassID ={0}", ID));
}
using (SqlDataReader sdr = DBHelpers.GetAllInfo(sql.ToString()))
{
while (sdr.Read())
{
StudentInfoBusiness student = new StudentInfoBusiness();
student.ClassName = sdr["ClassName"].ToString();
student.CollegeName = sdr["CollegeName"].ToString();
//student.GradeName = sdr["GradeName"].ToString();
student.MajorSimple = sdr["MajorSimple"].ToString();
student.StudentInfoID = Convert.ToInt32(sdr["StudentInfoID"]);
student.StudentName = sdr["StudentName"].ToString();
student.StudentNumber = sdr["StudentNumber"].ToString();
student.StudentSex = sdr["StudentSex"].ToString();
student.StudentMarket = sdr["StudentMarket"].ToString();
list.Add(student);
}
}
return(list);
}
public void UpdateStudentInfo(StudentInfoBusiness studentInfo)
{
dalstudentInfo.UpdateStudentInfo(studentInfo);
}
public void InsertStudentInfo(StudentInfoBusiness studentInfo)
{
dalstudentInfo.InsertStudentInfo(studentInfo);
}