DataGridView连接数据库对表进行增删改查
一、绑定数据源
//做一个变量控制页面刷新
public static int bs = ; 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[].ToString();
data.Name = _dr[].ToString();
data.Sex = Convert.ToBoolean(_dr[]);
data.School = _dr[].ToString();
data.Birthday = Convert.ToDateTime(_dr[]); 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 = ; //关闭窗口
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[].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 > )
{
//取出选中项的主键值
JianSan zj = dataGridView1.SelectedRows[].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 = ; //关闭窗口
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 == )
{
JianSanDA da = new JianSanDA();
dataGridView1.DataSource = da.Select();
bs = ;
}
}
※数据区别显示
//遍历datagridview里面行的集合，取出每一个行
foreach (DataGridViewRow row in dataGridView1.Rows)
{
//将该行里面绑定的数据项取出
Info data = row.DataBoundItem as Info;
//判断是不是男女
if (data.Sex)
{
}
}