一：分层架构
搭建DAL层(数据访问层)、UI层(表示层)、BLL层(业务逻辑层)以及Model层(实体层)
各层的引用关系：
DAL、UI、BLL层引用Model层
UI层引用BLL层
BLL层引用DAL层
二：实现登录功能
StudentDAL层
定义一个bool变量，将Student对象作为参数，使用SqlParameter对象进行填充，判定count值，若大于0.返回真
//01.写一个用于登录的方法
public bool IsLogin(Student stu)
{ bool flag = false; //string sql = "select count(1) from student where studentname='" + stu.StudentName + "' and loginpwd='" + stu.LoginPwd + "'"; string sql = "select count(1) from student where studentname=@name and LoginPwd=@pwd"; SqlParameter[] para ={ new SqlParameter("@name",stu.StudentName),
new SqlParameter("@pwd",stu.LoginPwd) };
//02.将sql语句交给SQL服务器执行
int count = Convert.ToInt32(SQLHelper.ExecuteScalar(sql,para));
if (count > )
{
flag = true;
}
return flag; }
StudentBLL层 数据传递
StudentDAL dal = new StudentDAL();
//登录
public bool isLogin(Student stu)
{
bool result = dal.IsLogin(stu);
return result;
}
UI层
获取两个文本框的值，定义bool变量，拿到登录的方法，传入参数
//登录
private void btnLogin_Click(object sender, EventArgs e)
{
StudentBLL bll = new StudentBLL();
Student stu=new Student();
stu.StudentName = txtName.Text;
stu.LoginPwd = txtPwd.Text; bool result= bll.isLogin(stu);
if (result)
{
frmMain frm = new frmMain();
this.Hide();
frm.Show(); }
三 实现新增学生用户
StudentDAL层
//添加学生
public bool AddStudent(Student stu)
{
bool flag = false;
string sql = "insert into Student Values(@LoginPwd,@StudentName,@Gender,@GradeId,@Phone,@Address,@Birthday,@Email)";
SqlParameter []para=
{
new SqlParameter("@LoginPwd",stu.LoginPwd),
new SqlParameter("@StudentName",stu.StudentName),
new SqlParameter("@Gender",stu.Gender),
new SqlParameter("@GradeId",stu.GradeId),
new SqlParameter("@Phone",stu.Phone),
new SqlParameter("@Address",stu.Address),
new SqlParameter("@Birthday",stu.Birthday),
new SqlParameter("@Email",stu.Email),
};
int count = SQLHelper.ExecuteNonQuery(sql,para);
if(count>)
{
flag = true;
}
return flag;
}
StudentBLL层
//添加学生
public bool AddStudent(Student stu)
{
bool result = dal.AddStudent(stu);
return result; }
UI层
//保存
private void btnEdit_Click(object sender, EventArgs e)
{
//从界面获取各个值
Student stu = new Student();
stu.LoginPwd = txtPwd.Text;
stu.StudentName = txtName.Text;
stu.Gender = rbtnFemale.Checked?"":"";
stu.Phone = txtPhone.Text;
stu.Address = txtAddress.Text;
stu.Email = txtEmail.Text;
//出生日期
stu.Birthday = dpBirthday.Value;
//年级编号
stu.GradeId = Convert.ToInt32(cboGrade.SelectedValue); bool result = studal.AddStudent(stu); if (result)
{
MessageBox.Show("添加成功！");
}
else
{
MessageBox.Show("添加失败！");
}
}
四 加载学生信息<查询>
DAL层
返回的是泛型集合、可使用MyTool工具类或者foreach遍历
//泛型
public List<Student> GetAllStudent()
{
// List<Student> list = new List<Student>(); string sql = "select * from Student"; DataTable dt= SQLHelper.ExecuteDataTable(sql); MyTool tool = new MyTool(); List<Student> list = tool.DataTableToList<Student>(dt); //foreach (DataRow item in dt.Rows)
//{
// Student stu = new Student();
// stu.StudentName = item["StudentName"].ToString();
// stu.StudentNo = Convert.ToInt32(item["StudentNo"]);
// stu.LoginPwd =item["LoginPwd"].ToString(); // list.Add(stu);
//} return list; }
BLL层
//泛型
public List<Student> GetAllStudent()
{
List<Student> list = dal.GetAllStudent();
return list; }
UI层
StudentBLL stuBll = new StudentBLL();
GradeBLL GBll = new GradeBLL(); //主窗体
private void FrmSearchByGrade_Load(object sender, EventArgs e)
{
//删除英文列
dgvList.AutoGenerateColumns = false; List<Student>list= stuBll.GetAllStudent();
dgvList.DataSource = list; List<Grade> glist = GBll.GetAllGrade();
//绑定年级下拉框值
cboGrade.ValueMember = "GradeId";
cboGrade.DisplayMember = "GradeName";
cboGrade.DataSource = glist;
} //查询
private void btnSearch_Click(object sender, EventArgs e)
{
int gradeid =Convert.ToInt32(cboGrade.SelectedValue); //使用BLL层
List<Student> list = stuBll.ByGradeId(gradeid);
dgvList.DataSource = list; }
五 删除学生信息
DAL层
//删除
public bool Delete(int stuNo)
{
bool flag = false;
string sql="delete from Student where StudentNo=@No";
SqlParameter para = new SqlParameter("@No",stuNo);
int count = SQLHelper.ExecuteNonQuery(sql,para);
if(count>)
{
flag = true;
}
return flag;
}
BLL层
//删除
public bool Delete(int stuNo)
{
return dal.Delete(stuNo); }
UI层
注：光标位置
记录选中行的索引值 定义变量index、光标回到index-1
//删除
private void tsmi_del_Click(object sender, EventArgs e)
{
int stuno = Convert.ToInt32(dgvList.SelectedRows[].Cells["Column2"].Value); //记录选中行的索引值
int index = dgvList.CurrentRow.Index; //MessageBox.Show(index.ToString()); bool flag = stuBll.Delete(stuno); if(flag)
{
MessageBox.Show("删除成功！");
List<Student> list = stuBll.GetAllStudent();
dgvList.DataSource = list; //光标回到index-1
dgvList[, index].Selected = true;
dgvList.CurrentCell=dgvList[,index];
}
}
六 修改学生信息
DAL层
//修改
public bool UpdateInfo(Student stu)
{
bool flag = false;
string sql = "update Student set StudentName=@stuName,Gender=@gender,Birthday=@birthday where StudentNo=@No";
SqlParameter[] para =
{
new SqlParameter("@stuName",stu.StudentName),
new SqlParameter("@gender",stu.Gender),
new SqlParameter("@birthday",stu.Birthday),
new SqlParameter("@No",stu.StudentNo)
};
int count = SQLHelper.ExecuteNonQuery(sql,para);
if(count>)
{
flag = true;
}
return flag;
}
BLL层：
//修改
public bool UpdateInfo(Student stu)
{
return dal.UpdateInfo(stu);
}
窗体的关联：
private void tsmi_update_Click(object sender, EventArgs e)
{
FrmUpdate frm = new FrmUpdate();
frm.dgvList = dgvList;
frm.Show();
}
UI层 Load事件中实现窗体的传值
StudentBLL stubll = new StudentBLL();
public DataGridView dgvList;
Student stu = new Student(); private void FrmUpdate_Load(object sender, EventArgs e)
{ stu.StudentNo = Convert.ToInt32(dgvList.SelectedRows[].Cells["Column2"].Value);
stu.StudentName = dgvList.SelectedRows[].Cells["name"].Value.ToString();
stu.Gender = dgvList.SelectedRows[].Cells["Column3"].Value.ToString();
stu.Birthday = Convert.ToDateTime(dgvList.SelectedRows[].Cells["Column4"].Value); // MessageBox.Show(stu.StudentName.ToString());
this.txtNo.Text = stu.StudentNo.ToString();
this.txtName.Text = stu.StudentName;
this.txtGender.Text = stu.Gender;
this.txtBirthday.Text = stu.Birthday.ToString();
} private void btnUpdate_Click(object sender, EventArgs e)
{ stu.StudentName = txtName.Text;
stu.Gender = txtGender.Text;
stu.Birthday = Convert.ToDateTime(txtBirthday.Text);
bool flag = stubll.UpdateInfo(stu);
if(flag)
{
MessageBox.Show("修改成功！！");
List<Student> list = stubll.GetAllStudent();
dgvList.DataSource = list;
}
}