DataGridView连接数据库对表进行增删改查
一、绑定数据源
//做一个变量控制页面刷新 public static int bs = 0; public Form1() { InitializeComponent(); } private void Form1_Load(object sender, EventArgs e) { JianSanDA da = new JianSanDA(); //绑定数据源 dataGridView1.DataSource = da.Select(); //设置不自动生成列 dataGridView1.AutoGenerateColumns = false; //取消默认第一行 dataGridView1.ClearSelection(); JSchoolDA sch = new JSchoolDA(); //给chaschool指定数据源 chaschool.DataSource = sch.Select(); //指定显示的值 chaschool.DisplayMember = "Sname"; //后台的value值 chaschool.ValueMember = "Scode"; JSchool data = new JSchool();//添加一列 data.Scode = "qxz"; data.Sname = "全门派"; List<JSchool> list = sch.Select(); list.Add(data); chaschool.DataSource = list; chaschool.DisplayMember = "Sname"; chaschool.ValueMember = "Scode"; chaschool.SelectedValue = "qxz"; }
二、查询
1.JianSanDA里面的多条件查询方法：（注意条件前后加空格）
//根据多条条件查询（重载） public List<JianSan> Select(string name, string school) { //做两个恒成立的条件 string cx1 = " 1=1 "; string cx2 = " 1=1 "; //根据用户输入的条件判断查询 if (name != "")//输入了姓名 { cx1 = " Name like @name "; } if (school != ""&&school !="qxz")//输入了门派且不是全门派 { cx2 = " School = @school "; } //拼接成完整条件 string cx = " where "+cx1+" and "+cx2; List<JianSan> list = new List<JianSan>(); _cmd.CommandText = "select * from JianSan " + cx; _cmd.Parameters.Clear(); _cmd.Parameters.AddWithValue("@name","%"+name+"%"); _cmd.Parameters.AddWithValue("@school",school); _conn.Open(); _dr = _cmd.ExecuteReader(); if (_dr.HasRows) { while (_dr.Read()) { JianSan data = new JianSan(); data.Code = _dr[0].ToString(); data.Name = _dr[1].ToString(); data.Sex = Convert.ToBoolean(_dr[2]); data.School = _dr[3].ToString(); data.Birthday = Convert.ToDateTime(_dr[4]); list.Add(data); } } _conn.Close(); return list; }
2.主窗口查询代码：
//查询 private void button4_Click(object sender, EventArgs e) { //取数据 string name = chaname.Text; string school = chaschool.SelectedValue.ToString(); //根据查询条件，把结果交给datagridview1显示 //首先需要重载查询方法 JianSanDA da = new JianSanDA(); dataGridView1.DataSource = da.Select(name, school); dataGridView1.AutoGenerateColumns = false; }
三、添加
1.打开新窗体：
//添加 private void button1_Click(object sender, EventArgs e) { //打开添加窗口 TianJia tj = TianJia.NewTianJia(); //显示窗体 tj.Show(); }
2.JianSanDA类添加代码：
//添加数据 public void Add(string code,string name,bool sex,string school,DateTime birthday) { _cmd.CommandText = "insert into JianSan values(@code,@name,@sex,@school,@birthday)"; _cmd.Parameters.Clear(); _cmd.Parameters.AddWithValue("@code",code); _cmd.Parameters.AddWithValue("@name", name); _cmd.Parameters.AddWithValue("@sex", sex); _cmd.Parameters.AddWithValue("@school", school); _cmd.Parameters.AddWithValue("@birthday", birthday); _conn.Open(); _cmd.ExecuteNonQuery(); _conn.Close(); }
3.子窗体添加界面代码：
public partial class TianJia : Form { //储存该类的对象 控制数量 private static TianJia tj = null; private TianJia() { InitializeComponent(); } //返回对象的方法 一个窗口 public static TianJia NewTianJia() { if (tj == null || tj.IsDisposed) { tj = new TianJia(); } return tj; } private void TianJia_Load(object sender, EventArgs e) { //给下拉列表绑定值 JSchoolDA jda = new JSchoolDA(); cbschool.DataSource = jda.Select(); cbschool.DisplayMember = "Sname"; cbschool.ValueMember = "Scode"; } //确定修改 private void button1_Click(object sender, EventArgs e) { MessageBoxButtons btn = MessageBoxButtons.YesNoCancel; if (MessageBox.Show("确定要添加吗？", "添加数据", btn) == DialogResult.Yes) { string _code = txtcode.Text; string _name = txtname.Text; bool _sex = rdnan.Checked; string _school = cbschool.SelectedValue.ToString(); DateTime _birthday = Convert.ToDateTime(txtbirthday.Text); JianSanDA jda = new JianSanDA(); jda.Add(_code, _name, _sex, _school, _birthday); //给Form1的成员变量bs赋值 刷新页面 Form1.bs = 1; //关闭窗口 this.Close(); } } }
四、删除
※删除加确认
MessageBoxButtons btn = MessageBoxButtons.YesNoCancel;
if (MessageBox.Show("确定要删除么？", "删除数据", btn) == DialogResult.Yes)
{
}
1.主窗口删除代码：
//删除 private void button2_Click(object sender, EventArgs e) { //让用户选择是否删除 MessageBoxButtons btn = MessageBoxButtons.YesNoCancel; if (MessageBox.Show("确定要删除数据吗？", "删除数据", btn) == DialogResult.Yes) { //取出选中行里面绑定的对象 JianSan data = dataGridView1.SelectedRows[0].DataBoundItem as JianSan; //初始化数据访问类 调用删除方法删除数据 JianSanDA da = new JianSanDA(); da.Delete(data.Code); //确定删除的同时刷新数据 dataGridView1.DataSource = da.Select(); } }
2.JianSanDA类删除代码：
//删除数据 public void Delete(string code) { _cmd.CommandText = "delete from JianSan where Code=@code"; _cmd.Parameters.Clear(); _cmd.Parameters.AddWithValue("@code",code); _conn.Open(); _cmd.ExecuteNonQuery(); _conn.Close(); }
五、修改
1.主窗体修改代码：
//修改 private void button3_Click(object sender, EventArgs e) { //判断是否有选中项 if (dataGridView1.SelectedRows.Count > 0) { //取出选中项的主键值 JianSan zj = dataGridView1.SelectedRows[0].DataBoundItem as JianSan; //打出修改窗体 //XiuGai xg = new XiuGai();——应用单例模式控制只出现一个修改窗口 XiuGai xg = XiuGai.NewXiuGai(zj.Code); //显示窗体 xg.Show(); //让修改窗体属于Form1 //xg.Owner = this; //让修改窗体获得焦点 //xg.Focus(); } else { MessageBox.Show("没有选中任何项！"); } }
2.子窗体修改代码：
public partial class XiuGai : Form { //用来存储传递来的主键值 private string Code = ""; //用来存储该类的对象（控制一个窗口） private static XiuGai xg = null; public XiuGai() { InitializeComponent(); } //构造一个有参数的方法 public XiuGai(string code) { InitializeComponent(); this.Code = code; } //返回对象的方法 单例模式 public static XiuGai NewXiuGai(string code) { if (xg == null || xg.IsDisposed) { xg = new XiuGai(code); } return xg; } private void XiuGai_Load(object sender, EventArgs e) { //给下拉列表绑定值 JSchoolDA sda = new JSchoolDA(); cbschool.DataSource = sda.Select(); cbschool.DisplayMember = "Sname"; cbschool.ValueMember = "Scode"; //对界面内容进行初始化 JianSanDA da = new JianSanDA(); JianSan data = da.Select(Code); txtcode.Text = data.Code; txtname.Text = data.Name; rdnan.Checked = data.Sex; rdnv.Checked = !data.Sex; //改下拉列表（school）设置选中项 cbschool.SelectedValue = data.School; txtbirthday.Text = data.Birthday.ToString("yyyy-MM-dd"); } //确定修改数据 private void button1_Click(object sender, EventArgs e) { MessageBoxButtons btn = MessageBoxButtons.YesNoCancel; if (MessageBox.Show("确定要修改吗？", "修改数据", btn) == DialogResult.Yes) { string _code = txtcode.Text; string _name = txtname.Text; bool _sex = rdnan.Checked; string _school = cbschool.SelectedValue.ToString(); DateTime _birthday = Convert.ToDateTime(txtbirthday.Text); JianSanDA jda = new JianSanDA(); jda.Update(_code, _name, _sex, _school, _birthday); //给Form1的成员变量bs赋值 调用刷新数据 Form1.bs = 1; //关闭窗口 this.Close(); } } }
3.JianSanDA类中的修改代码：
//修改数据 public void Update(string code,string name,bool sex,string school,DateTime birthday) { _cmd.CommandText = "update JianSan set Name=@name,Sex=@sex,School=@school,Birthday=@birthday where Code=@code"; _cmd.Parameters.Clear(); _cmd.Parameters.AddWithValue("@code",code); _cmd.Parameters.AddWithValue("@name", name); _cmd.Parameters.AddWithValue("@sex", sex); _cmd.Parameters.AddWithValue("@school", school); _cmd.Parameters.AddWithValue("@birthday", birthday); _conn.Open(); _cmd.ExecuteNonQuery(); _conn.Close(); }
六、做一个Timer控件控制刷新
//用timer 刷新 修改和添加完的数据 private void timeshuaxin_Tick(object sender, EventArgs e) { if (bs == 1) { JianSanDA da = new JianSanDA(); dataGridView1.DataSource = da.Select(); bs = 0; } }
※数据区别显示
//遍历datagridview里面行的集合，取出每一个行
foreach (DataGridViewRow row in dataGridView1.Rows)
{
//将该行里面绑定的数据项取出
Info data = row.DataBoundItem as Info;
//判断是不是男女
if (data.Sex)
{
}
}