public static int UpdateStudent(StuBasicinfo student)
{
string sql = "update Student_Basic_Info set Age=@Age,[Student ID]=@StudentID, [ID Number]=@IDNumber, " +
"[Full name]=@Fullname, Class=@Class,Gender=@Gender," +
"[Biogenic Land]=@BiogenicLand,College=@College,Major=@Major,[NCEE Score]=@NCEEScore," +
"[Phone Number]=@PhoneNumber,[Name of Guardian 1]=@NameofGuardian1,[Name of Guardian 2]=@NameofGuardian2," +
"[Phone Number of Guardian 1]=@PhoneNumberofGuardian1,[Phone Number of Guardian 2]=@PhoneNumberofGuardian2 where ID=@ID";
// 添加参数
List <SqlParameter> parameters = new List <SqlParameter>();
parameters.Add(new SqlParameter("IDNumber", student.IDNumber));
parameters.Add(new SqlParameter("StudentID", student.StudentID));
parameters.Add(new SqlParameter("Fullname", student.stuName));
parameters.Add(new SqlParameter("Class", student.stuClass));//
parameters.Add(new SqlParameter("Gender", student.stuGender));
parameters.Add(new SqlParameter("Age", student.stuAge));
parameters.Add(new SqlParameter("BiogenicLand", student.BiogenicLand));
parameters.Add(new SqlParameter("College", student.stuCollege));
parameters.Add(new SqlParameter("Major", student.stuMajor));
parameters.Add(new SqlParameter("NCEEScore", student.GKchengji));
parameters.Add(new SqlParameter("PhoneNumber", student.phoneNumber));
parameters.Add(new SqlParameter("NameofGuardian1", student.stuGuardian1));
parameters.Add(new SqlParameter("NameofGuardian2", student.stuGuardian2));
parameters.Add(new SqlParameter("PhoneNumberofGuardian1", student.Guar1PhoneNumber));
parameters.Add(new SqlParameter("PhoneNumberofGuardian2", student.Guar2PhoneNumber));
parameters.Add(new SqlParameter("ID", student.ID));
return(DAL.Services.UpdateService.Update(sql, parameters.ToArray()));
}
public static int InsertStudent(StuBasicinfo student)
{
//string sql = "insert into Student_Basic_Info values (@shenfenzheng, @stu_num, @stu_name, @stu_class, " +
// "@stu_gender, @stu_age, @shengyuandi, @stu_college, @stu_major, @GK, @stu_phone, @stu_g1, @stu_g2, @stu_pwd)";
string sql = "insert [dbo].[Student_Basic_Info] ([ID Number],[Student ID]," +
"[Full name],[Class],[Gender],[Age],[Biogenic Land],[Major],[NCEE Score]," +
"[Phone Number],[Name of Guardian 1],[Name of Guardian 2],[Phone Number of" +
" Guardian 1],[Phone Number of Guardian 2],[password]) values " +
"(@IDNumber, @StudentID, @Fullname, @Class, @Gender, @Age, @BIogenicLand," +
" @Major, @NCEEScore, @PhoneNumber, @NameofGuardian1,@NameofGuardian2, @PhoneNumberofGuardian1," +
" @PhoneNumberofGuardian2, @password)";
List <SqlParameter> parameters = new List <SqlParameter>();
parameters.Add(new SqlParameter("IDNumber", student.IDNumber));
parameters.Add(new SqlParameter("StudentID", student.StudentID));
parameters.Add(new SqlParameter("Fullname", student.stuName));
parameters.Add(new SqlParameter("Class", student.stuClass));//
parameters.Add(new SqlParameter("Gender", student.stuGender));
parameters.Add(new SqlParameter("Age", student.stuAge));
parameters.Add(new SqlParameter("BiogenicLand", student.BiogenicLand));
parameters.Add(new SqlParameter("College", student.stuCollege));
parameters.Add(new SqlParameter("Major", student.stuMajor));
parameters.Add(new SqlParameter("NCEEScore", student.GKchengji));
parameters.Add(new SqlParameter("PhoneNumber", student.phoneNumber));
parameters.Add(new SqlParameter("NameofGuardian1", student.stuGuardian1));
parameters.Add(new SqlParameter("NameofGuardian2", student.stuGuardian2));
parameters.Add(new SqlParameter("PhoneNumberofGuardian1", student.stuGuardian2));
parameters.Add(new SqlParameter("PhoneNumberofGuardian2", student.stuGuardian2));
parameters.Add(new SqlParameter("password", student.stu_pwd));
return(DAL.Services.UpdateService.Update(sql, parameters.ToArray()));
}
public StuBasicinfo GetStuByStuID(string StuId)
{
string sql = $"select * from Student_Basic_Info where [Student ID]='{StuId}'";
SqlDataReader sdr = SqlHelper.GetAllResult(sql); // 得到reader对象
StuBasicinfo stu = null;
if (sdr.Read())
{
StuBasicinfo newStu = new StuBasicinfo()
{
ID = (int)sdr["ID"],
IDNumber = (string)sdr["ID Number"],
StudentID = (string)sdr["Student ID"],
stuName = (string)sdr["Full name"],
stuClass = (string)sdr["Class"],
stuGender = (string)sdr["Gender"],
stuAge = (string)sdr["Age"],
BiogenicLand = (string)sdr["Biogenic Land"],
stuCollege = (string)sdr["College"],
stuMajor = (string)sdr["Major"],
GKchengji = (string)sdr["NCEE Score"],
phoneNumber = (string)sdr["Phone Number"],
stuGuardian1 = (string)sdr["Name of Guardian 1"],
stuGuardian2 = (string)sdr["Name of Guardian 2"],
Guar1PhoneNumber = (string)sdr["Phone Number of Guardian 1"],
Guar2PhoneNumber = (string)sdr["Phone Number of Guardian 2"],
stu_pwd = (string)sdr["password"]
};
stu = newStu;
}
sdr.Close();
return(stu);
}
/// <summary>
/// 获取学生列表, 在其他地方设置为只有权限为老师时才能够获取
/// </summary>
/// <param name="stu"></param>
/// <returns>list<stubasicinfo></stubasicinfo></returns>
public List <StuBasicinfo> GetStus()
{
string sql = "select * from Student_Basic_Info";
SqlDataReader sdr = SqlHelper.GetAllResult(sql); // 得到reader对象
List <StuBasicinfo> students = new List <StuBasicinfo>();
while (sdr.Read())
{
StuBasicinfo newStu = new StuBasicinfo()
{
ID = (int)sdr["ID"],
IDNumber = (string)sdr["ID Number"],
StudentID = (string)sdr["Student ID"],
stuName = (string)sdr["Full name"],
stuClass = (string)sdr["Class"],
stuGender = (string)sdr["Gender"],
stuAge = (string)sdr["Age"],
BiogenicLand = (string)sdr["Biogenic Land"],
stuCollege = (string)sdr["College"],
stuMajor = (string)sdr["Major"],
GKchengji = (string)sdr["NCEE Score"],
phoneNumber = (string)sdr["Phone Number"],
stuGuardian1 = (string)sdr["Name of Guardian 1"],
stuGuardian2 = (string)sdr["Name of Guardian 2"],
Guar1PhoneNumber = (string)sdr["Phone Number of Guardian 1"],
Guar2PhoneNumber = (string)sdr["Phone Number of Guardian 2"],
stu_pwd = (string)sdr["password"]
};
students.Add(newStu);
}
sdr.Close();
return(students);
}
void initTextBox(StuBasicinfo studen)
{
this.textBoxName.Text = student.stuName;
this.textBoxNumber.Text = student.StudentID;
this.textBoxMajor.Text = student.stuMajor;
this.textBoxage.Text = student.stuAge.ToString();
this.textBoxClass.Text = student.stuClass;
this.textBoxCollege.Text = studen.stuCollege;
this.textBoxGKchengji.Text = studen.GKchengji.ToString();
this.textBoxShengyuandi.Text = studen.BiogenicLand.ToString();
this.textBoxPhoneNumber.Text = studen.phoneNumber;
this.textBoxGuardian1.Text = studen.stuGuardian1;
this.textBoxGuardian2.Text = studen.stuGuardian2;
this.textBoxIDcard.Text = studen.IDNumber;
this.comboBoxGender.SelectedIndex = Gender2Number(studen.stuGender);
foreach (var item in this.Controls)
{
if (item is TextBox)
{
((TextBox)item).Enabled = false;
}
}
this.comboBoxGender.Enabled = false;
}
StuBasicinfo GetStuFromForm()
{
string pwd;
string ID;
// 如果是添加学生
if (isAdd)
{
pwd = this.textBoxPwd.Text.Trim();
}
else
{
pwd = this.student.stu_pwd;
}
// 复制一个student对象
StuBasicinfo student = new StuBasicinfo()
{
ID = this.student.ID,
IDNumber = this.student.IDNumber,
StudentID = this.student.StudentID,
stuName = this.student.stuName,
stuClass = this.student.stuClass,
stuGender = this.student.stuGender,
stuAge = this.student.stuAge,
BiogenicLand = this.student.BiogenicLand,
stuCollege = this.student.stuCollege,
stuMajor = this.student.stuMajor,
GKchengji = this.student.GKchengji,
phoneNumber = this.student.phoneNumber,
stuGuardian1 = this.student.stuGuardian1,
stuGuardian2 = this.student.stuGuardian2,
Guar1PhoneNumber = this.student.Guar1PhoneNumber,
Guar2PhoneNumber = this.student.Guar2PhoneNumber,
stu_pwd = this.student.stu_pwd
};
student.stuName = this.textBoxName.Text;
student.StudentID = this.textBoxNumber.Text;
student.stuMajor = this.textBoxMajor.Text;
student.stuAge = this.textBoxage.Text;
student.stuClass = this.textBoxClass.Text;
student.stuCollege = this.textBoxCollege.Text;
student.GKchengji = this.textBoxGKchengji.Text;
student.BiogenicLand = this.textBoxShengyuandi.Text;
student.phoneNumber = this.textBoxPhoneNumber.Text;
student.stuGuardian1 = this.textBoxGuardian1.Text;
student.stuGuardian2 = this.textBoxGuardian2.Text;
student.IDNumber = this.textBoxIDcard.Text;
student.stuGender = Number2Gender(this.comboBoxGender.SelectedIndex);
// 不更新项目
student.stu_pwd = pwd;
if (!isAdd)
{
student.ID = this.student.ID;
}
return(student);
}
private void buttonChangeInfo_MouseClick(object sender, MouseEventArgs e)
{
if (0 == string.Compare(this.buttonChangeInfo.Text, "修改信息"))
{
foreach (var item in this.Controls)
{
if (item is TextBox)
{
((TextBox)item).Enabled = true;
}
this.buttonChangeInfo.Text = "确认修改";
}
this.textBoxNumber.Enabled = false; // 学号不能够更改
this.comboBoxGender.Enabled = true;
}
else
{
// 开始尝试修改信息
if (0 != string.Compare(this.buttonChangeInfo.Text, "确认修改"))
{
try
{
int bs = BLL.Update.UpdateStudent(GetStuFromForm());
}
catch (Exception es)
{
MessageBox.Show(es.Message);
}
}
if (0 == string.Compare(this.buttonChangeInfo.Text, "确认添加"))
{
StuBasicinfo stu = GetStuFromForm();
// 添加后不是修改
try
{
int vs = BLL.Update.InsertStudent(stu);
}
catch (Exception ex)
{
MessageBox.Show("输入数据错误!" + ex.Message);
return;
}
this.isAdd = false;
MessageBox.Show("添加成功!");
this.Close();
}
DAL.Services.LoginService ls = new DAL.Services.LoginService();
this.student = ls.StuLogin(student);// 更新信息
initTextBox(this.student);
this.buttonChangeInfo.Text = "修改信息";
}
}
/// <summary>
/// 查询单个学生数据, 传入的学生对象应该有学号与密码
/// </summary>
/// <param name="stu">null或填充好数据的学生对象</param>
/// <returns></returns>
public StuBasicinfo StuLogin(StuBasicinfo stu)
{
string sql = "select top 1 * from Student_Basic_Info where [Student ID] = @StudentID"; //带参数的语句
SqlParameter para1 = new SqlParameter("StudentID", System.Data.SqlDbType.VarChar)
{
Value = stu.StudentID
};//参数
SqlParameter[] paras = new SqlParameter[1];
paras[0] = para1;
SqlDataReader sdr = SqlHelper.GetAllResult(sql, paras); // 得到reader对象
StuBasicinfo newStu = new StuBasicinfo();
if (sdr.Read())
{
newStu.ID = (int)sdr["ID"];
newStu.IDNumber = (string)sdr["ID Number"];
newStu.StudentID = (string)sdr["Student ID"];
newStu.stuName = (string)sdr["Full name"];
newStu.stuClass = (string)sdr["Class"];
newStu.stuGender = (string)sdr["Gender"];
newStu.stuAge = (string)sdr["Age"];
newStu.BiogenicLand = sdr.GetString(7);
newStu.stuCollege = sdr.GetString(8);
newStu.stuMajor = sdr.GetString(9);
newStu.GKchengji = sdr.GetString(10);
newStu.phoneNumber = sdr.GetString(11);
newStu.stuGuardian1 = sdr.GetString(12);
newStu.stuGuardian2 = sdr.GetString(13);
newStu.Guar1PhoneNumber = sdr.GetString(14);
newStu.Guar2PhoneNumber = sdr.GetString(15);
newStu.stu_pwd = sdr.GetString(16);
}
else
{
// 读取不到记录, 说明账号不存在
return(null);
}
sdr.Close();
//如果md5对不上
if (!IsMd5Same(stu.stu_pwd, newStu.stu_pwd))
{
return(null);
}
return(newStu);
}
// 测试方法
static void Test()
{
StuBasicinfo stu = new StuBasicinfo();
stu.StudentID = "1702110527";
stu.stu_pwd = "123456";
LoginService ls = new LoginService();
stu = ls.StuLogin(stu);
TeacherInfo teacher = new TeacherInfo()
{
JobNumber = "1702110527",
t_pwd = "123456"
};
teacher = ls.TeacherLogin(teacher);
}
public FrmMain(object user)
{
InitializeComponent();
if (user is TeacherInfo)
{
this.teacher = (TeacherInfo)user;
isTeacher = true;
userName = this.teacher.teacherName;
}
else
{
student = (StuBasicinfo)user;
isTeacher = false;
userName = student.stuName;
}
this.StartPosition = FormStartPosition.CenterScreen;
}
private void buttonChaxun_Click(object sender, EventArgs e)
{
string stuNumber = this.textBoxNumber.Text;// 获取学号
DAL.Services.StuBasicInfoService sbs = new DAL.Services.StuBasicInfoService();
StuBasicinfo student = sbs.GetStuByStuID(stuNumber);
if (null == student)
{
MessageBox.Show("未查询到学生信息!");
return;
}
else
{
Form frmStu = new frmStuInfo(student);
frmStu.TopLevel = false;
frmStu.Parent = this.panel1;
frmStu.Show();
}
}
public FormStuChaxun(StuBasicinfo student)
{
InitializeComponent();
this.student = student;
}
private void buttonLogin_Click(object sender, EventArgs e)
{
// 老师登录逻辑
LoginService login = new LoginService();
// 获取输入框数据
string accu = this.TextAccount.Text.Trim();
string pwd = this.TextPwd.Text.Trim();
if (this.RadioButtonTeacher.Checked)
{
TeacherInfo teacher = new TeacherInfo()
{
JobNumber = accu,
t_pwd = pwd
};
try
{
teacher = login.TeacherLogin(teacher);
}
catch (Exception ex)
{
MessageBox.Show("数据库连接失败! " + ex.Message);
return;
}
if (null == teacher)
{
MessageBox.Show("账号或密码错误!", "登录失败", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
else
{
// 测试用
// MessageBox.Show("欢迎你! " + teacher.teacherName, "登录成功", MessageBoxButtons.OK);
Form frmMain = new FrmMain(teacher); // 传入当前用户
this.Hide();
frmMain.Show();
}
}
// 学生登录逻辑
else
{
StuBasicinfo student = new StuBasicinfo()
{
StudentID = accu,
stu_pwd = pwd
};
try
{
student = login.StuLogin(student);
}
catch (Exception ex)
{
MessageBox.Show("数据库连接失败! " + ex.Message);
return;
}
if (null == student)
{
MessageBox.Show("账号或密码错误!", "登录失败", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
else
{
// 测试用
// MessageBox.Show("欢迎你! " + student.stuName, "登录成功", MessageBoxButtons.OK);
Form frmMain = new FrmMain(student); // 传入当前用户
this.Hide();
frmMain.Show();
}
}
}
public frmStuInfo(StuBasicinfo student)
{
InitializeComponent();
this.student = student;
this.isAdd = false;
}
StuBasicinfo GetStuFromForm()
{
string pwd;
// 如果是添加学生
if (isAdd)
{
pwd = this.textBoxPwd.Text.Trim();
}
else
{
pwd = this.student.stu_pwd;
}
StuBasicinfo stu = new StuBasicinfo();
//if (!isAdd)
//{
// // 复制一个student对象
// StuBasicinfo student = new StuBasicinfo()
// {
// ID = this.student.ID,
// IDNumber = this.student.IDNumber,
// StudentID = this.student.StudentID,
// stuName = this.student.stuName,
// stuClass = this.student.stuClass,
// stuGender = this.student.stuGender,
// stuAge = this.student.stuAge,
// BiogenicLand = this.student.BiogenicLand,
// stuCollege = this.student.stuCollege,
// stuMajor = this.student.stuMajor,
// GKchengji = this.student.GKchengji,
// phoneNumber = this.student.phoneNumber,
// stuGuardian1 = this.student.stuGuardian1,
// stuGuardian2 = this.student.stuGuardian2,
// Guar1PhoneNumber = this.student.Guar1PhoneNumber,
// Guar2PhoneNumber = this.student.Guar2PhoneNumber,
// stu_pwd = this.student.stu_pwd
// };
//}
if (!isAdd)
{
stu.ID = this.student.ID;
}
stu.stu_pwd = pwd;
stu.stuName = this.textBoxName.Text;
stu.StudentID = this.textBoxNumber.Text;
stu.stuMajor = this.textBoxMajor.Text;
stu.stuAge = this.textBoxage.Text;
stu.stuClass = this.textBoxClass.Text;
stu.stuCollege = this.textBoxCollege.Text;
stu.GKchengji = this.textBoxGKchengji.Text;
stu.BiogenicLand = this.textBoxShengyuandi.Text;
stu.phoneNumber = this.textBoxPhoneNumber.Text;
stu.stuGuardian1 = this.textBoxGuardian1.Text;
stu.stuGuardian2 = this.textBoxGuardian2.Text;
stu.IDNumber = this.textBoxIDcard.Text;
stu.stuGender = Number2Gender(this.comboBoxGender.SelectedIndex);
stu.Guar1PhoneNumber = textBoxGP1.Text;
stu.Guar2PhoneNumber = textBoxGP2.Text;
// 不更新项目
return(stu);
}