1.首先我们要清楚一些专业名词,下面粗劣的解释一下,具体可以自己查阅资料,当然只要了解一些基础下面的操作也是如鱼得水。
1.三层架构主要是指将业务应用规划中的表示层 UI、数据访问层 DAL 以及业务逻辑层 BLL,其分层的核心任务是" 高内聚低耦合 "的实现。. 在整个软件架构中,分层结构是常见和普通的软件结构框架,同时也具有非常重要的地位和意义。. 这种三层架构可以在软件开发的过程中,划分技术人员和开发人员的具体开发工作,重视核心业务系统的分析、设计以及开发,提高信息系统开发质量和开发效率,进而为信息系统日后的更新与维护提供很大的方便。
2.CURD是一个数据库技术中的缩写词,一般的项目开发的各种参数的基本功能都是CURD。它代表创建(Create)、更新(Update)、读取(Read)和删除(Delete)操作。
具体实现功能如下:
CURD
2.进入主题
1.连接数据库,封装DBHelper类
public class DBHelper
{
/// <summary>
/// 连接数据库
/// </summary>
static readonly string ConStr = "Data Source=.;Initial Catalog=STDB;Integrated Security=True";
/// <summary>
/// 查询
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static DataTable ExcetuQuery(string sql)
{
SqlConnection sc = new SqlConnection(ConStr);
SqlDataAdapter ad = new SqlDataAdapter(sql, sc);
DataTable dt = new DataTable();
ad.Fill(dt);
return dt;
}
/// <summary>
/// 增删改
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static int ExcetuNonQuery(string sql)
{
SqlConnection sc = new SqlConnection(ConStr);
sc.Open();
SqlCommand md = new SqlCommand(sql, sc);
int result = md.ExecuteNonQuery();
sc.Close();
return result;
}
}
2.创建实体类,封装sqlserve中的字段
注: 快捷操作ctrl+r+e快速封装字段
public class stuInfo
{
int s_ID;
string s_Name;
string s_Sex;
int s_Age;
DateTime s_Date;
string pwd;
int c_ID;
string c_Name;
public int S_ID { get => s_ID; set => s_ID = value; }
public string S_Name { get => s_Name; set => s_Name = value; }
public string S_Sex { get => s_Sex; set => s_Sex = value; }
public int S_Age { get => s_Age; set => s_Age = value; }
public DateTime S_Date { get => s_Date; set => s_Date = value; }
public string Pwd { get => pwd; set => pwd = value; }
public int C_ID { get => c_ID; set => c_ID = value; }
public string C_Name { get => c_Name; set => c_Name = value; }
}
public class classInfo
{
int c_ID;
string c_Name;
public int C_ID { get => c_ID; set => c_ID = value; }
public string C_Name { get => c_Name; set => c_Name = value; }
}
3.查询数据
将数据填充到datagridview中
数据访问层 DAL
public static List<stuInfo> databind(string where)
{
string sql = "select * from stuInfo where 1=1 " + where;
DataTable dt = Common.DBHelper.ExcetuQuery(sql);
if (dt.Rows.Count>0)
{
List<Model.stuInfo> list = new List<stuInfo>();
for (int i = 0; i < dt.Rows.Count; i++)
{
Model.stuInfo stu = new stuInfo();
stu.S_ID =(int) dt.Rows[i]["S_ID"];
stu.S_Age = (int)dt.Rows[i]["S_Age"];
stu.C_ID = (int)dt.Rows[i]["C_ID"];
stu.S_Name = dt.Rows[i]["S_Name"].ToString();
stu.S_Sex = dt.Rows[i]["S_Sex"].ToString();
stu.S_Date = (DateTime)dt.Rows[i]["S_Date"];
//sql单查询 通过id找name
stu.C_Name = new DAL.classInfoDAL().cless("and C_ID="+ (int)dt.Rows[i]["C_ID"] + "").C_Name;
list.Add(stu);
}return list;
}return null;
}
internal Model.classInfo cless(string where)
{
string sql = "select *from classInfo where 1=1 " + where;
DataTable dt = Common.DBHelper.ExcetuQuery(sql);
Model.classInfo classInfo = new Model.classInfo();
classInfo.C_ID =(int) dt.Rows[0]["C_ID"];
classInfo.C_Name = dt.Rows[0]["C_Name"].ToString();
return classInfo;
}
业务逻辑层BLL
public static List<stuInfo> databind(string where)
{
return DAL.stuInfoDAL.databind(where);
}
UI表示层
void Bind(string where)
{
List<Model.stuInfo> list = BLL.stuInfoBLL.databind(where);
dataGridView1.AutoGenerateColumns = false;
dataGridView1.DataSource = list;
}
将数据绑定到下拉框里
数据访问层 DAL
public static List<classInfo> getclass(string where)
{
string sql = "select *from classInfo where 1=1 " + where;
DataTable dt = Common.DBHelper.ExcetuQuery(sql);
if (dt.Rows.Count>0)
{
List<Model.classInfo> list = new List<classInfo>();
for (int i = 0; i < dt.Rows.Count; i++)
{
Model.classInfo classInfo = new Model.classInfo();
classInfo.C_ID = (int)dt.Rows[i]["C_ID"];
classInfo.C_Name = dt.Rows[i]["C_Name"].ToString();
list.Add(classInfo);
}return list;
}return null;
}
业务逻辑层BLL
public List<classInfo> getclass(string where)
{
return DAL.classInfoDAL.getclass(where);
}
UI表示层
private void MainForm2_Load(object sender, EventArgs e)
{
List<Model.classInfo> list = new BLL.classInfoBLL().getclass("");
list.Insert(0, new Model.classInfo { C_ID = 0, C_Name = "全部" });
this.comboBox1.DataSource = list;
this.comboBox1.DisplayMember = "c_Name";
this.comboBox1.ValueMember = "c_ID";
Bind("");
}
4.条件查询
/// <summary>
/// 条件查询
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button1_Click(object sender, EventArgs e)
{
//下拉框班级查询
string where = "";
if ((int)comboBox1.SelectedValue!=0)
{
where += "and c_ID="+ comboBox1.SelectedValue + "";
}
//单选按钮性别查询
string sex = rb0.Checked ? "女" : "男";
if (rball.Checked==false)
{
where += "and s_Sex='" + sex + "'";
}
//文本框性别查询
if (textBox1.Text!="")
{
where += "and s_Name='" + textBox1.Text.Trim() + "'";
}
Bind(where);
}
5.删除选中行
数据访问层 DAL
public static int delete(int id)
{
string sql = "delete stuInfo where s_ID=" + id + "";
return Common.DBHelper.ExcetuNonQuery(sql);
}
业务逻辑层BLL
public static int delete(int id)
{
return DAL.stuInfoDAL.delete(id);
}
UI表示层
private void 删除所选信息ToolStripMenuItem_Click(object sender, EventArgs e)
{
if(MessageBox.Show("你确定删除吗?", " 提示信息", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) == DialogResult.Yes)
{
int id =(int) dataGridView1.SelectedRows[0].Cells["s_ID"].Value;
if (BLL.stuInfoBLL.delete(id) > 0)
{
MessageBox.Show("删除成功");
Bind("");
this.dataGridView1.Refresh();
}
else
{
MessageBox.Show("删除失败");
Bind("");
}
}
}
6.添加成员信息
数据访问层 DAL
public static int add(stuInfo stuInfo)
{
string sql = $"insert into stuInfo values('{stuInfo.S_Name}', '{stuInfo.S_Sex}', {stuInfo.S_Age}, default, '123456', {stuInfo.C_Name})";
return Common.DBHelper.ExcetuNonQuery(sql);
}
业务逻辑层BLL
public static int add(stuInfo stuInfo)
{
return DAL.stuInfoDAL.add(stuInfo);
}
UI表示层
private void 添加学生信息ToolStripMenuItem_Click(object sender, EventArgs e)
{
ADDForm2 af = new ADDForm2();
if (af.ShowDialog()==DialogResult.OK)
{
Bind("");
this.dataGridView1.Refresh();
}
private void button1_Click(object sender, EventArgs e)
{
Model.stuInfo stuInfo = new Model.stuInfo();
stuInfo.S_Name = this.tbname.Text.Trim();
stuInfo.S_Age = Convert.ToInt32( this.tbage.Text.Trim());
stuInfo.C_Name= this.cbclass.SelectedValue.ToString();
stuInfo.S_Sex = this.rb1.Checked ? "男" : "女";
if (BLL.stuInfoBLL.add(stuInfo)>0)
{
MessageBox.Show("添加成功");
this.DialogResult= DialogResult.OK;
this.Close();
}
else
{
MessageBox.Show("添加失败");
}
}
7.修改成员信息
数据访问层 DAL
public static int upadte(stuInfo stuInfo)
{
string sql = $"update stuInfo set s_Name='{stuInfo.S_Name}' ,s_Age={stuInfo.S_Age},s_Sex='{stuInfo.S_Sex}',c_ID={stuInfo.C_Name} where s_ID={stuInfo.S_ID}";
return Common.DBHelper.ExcetuNonQuery(sql);
}
业务逻辑层BLL
public static int upadte(stuInfo stuInfo)
{
return DAL.stuInfoDAL.upadte(stuInfo);
}
UI表示层
/// <summary>
/// 数据的回显
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void UpdateForm2_Load(object sender, EventArgs e)
{
List<Model.classInfo> list = new BLL.classInfoBLL().getclass("");
this.cbclass.DisplayMember = "C_Name";
this.cbclass.ValueMember = "c_ID";
this.cbclass.DataSource = list;
List<Model.stuInfo> infos = BLL.stuInfoBLL.databind("and s_ID="+this.labesid.Text);
this.tbname.Text=infos[0].S_Name;
this.tbage.Text = infos[0].S_Age.ToString();
this.cbclass.Text = infos[0].C_Name;
if (infos[0].S_Sex=="男")
{
this.rb1.Checked = true;
}
else
{
this.rb0.Checked = true;
}
}
/// <summary>
/// 修改数据
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button1_Click(object sender, EventArgs e)
{
Model.stuInfo stuInfo = new Model.stuInfo();
stuInfo.S_Name = this.tbname.Text;
stuInfo.S_Age = Convert.ToInt32( this.tbage.Text);
stuInfo.C_Name= this.cbclass.SelectedValue.ToString();
stuInfo.S_Sex = this.rb1.Checked ? "男" : "女";
stuInfo.S_ID =Convert.ToInt32( this.labesid.Text);
if (BLL.stuInfoBLL.upadte(stuInfo)>0)
{
MessageBox.Show("修改成功");
this.DialogResult= DialogResult.OK;
this.Close();
}
else
{
MessageBox.Show("修改失败");
}
}
private void dataGridView1_CellMouseDoubleClick(object sender, DataGridViewCellMouseEventArgs e)
{
UpdateForm2 uf = new UpdateForm2();
uf.labesid.Text= this.dataGridView1.SelectedCells[0].Value.ToString();
if (uf.ShowDialog()== DialogResult.OK)
{
Bind("");
dataGridView1.Refresh();
}
}
结束了。。。game over