WinForm DataGridView增删改查

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)
{
}
}