C#连接MySql 对库中数据增删改查

登陆页面
账单设计页面
账单增删改查代码如下：

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using MySql.Data.MySqlClient; //连接MySQL需添加该using

namespace 今天吃了吗
{
    public partial class 账单增删改查 : Form
    {
        String mysqlStr = "Database=restaurant;Data Source=localhost;User Id=root;Password=*****;pooling=false;CharSet=utf8;port=3306";
        //与Mysql库的连接：Database库名;DataSource数据域安装时默认为localhost
        //UserId用户名 默认为root;Password密码;popling Charset port默认
        public 账单增删改查()
        {
            InitializeComponent();
        }

        private void btbsearch1_Click(object sender, EventArgs e)
        {
            DataTable dt = new DataTable();//数据容器
            string strsql = "select * from bill where bnum=@bnum";
            MySqlParameter mspbnum = new MySqlParameter("@bnum", txtbnum.Text);//类参数定义

            using (MySqlConnection con = new MySqlConnection(mysqlStr))
            //using{}外自动销毁
            {
                con.Open();
                using (MySqlCommand cmd = new MySqlCommand(strsql, con))
                //执行语句命令（语句，库）
                {
                    cmd.Parameters.Add(mspbnum);//添加参数
                    using (MySqlDataAdapter msda = new MySqlDataAdapter(cmd))//管道式类（源）
                    {
                        msda.Fill(dt);//送到dt显示
                    }
                }
            }
            dt3.DataSource = dt;

        }

        private void btadd_Click(object sender, EventArgs e)
        {
            string strsql = "insert into bill(bnum,btime,tnum,cnum,mname,mnamenum,bprice) ";
            strsql += "values(@bnum,@btime,@tnum,@cnum,@mname,@mnamenum,@bprice)";
            MySqlParameter mspbnum = new MySqlParameter("@bnum", txtbnum.Text);
            string nowTime = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
            MySqlParameter mspbtime = new MySqlParameter("@btime",nowTime);
            MySqlParameter msptnum = new MySqlParameter("@tnum", cbbtnum.Text);
            MySqlParameter mspcnum = new MySqlParameter("@cnum", txtcnum.Text);
            MySqlParameter mspmname = new MySqlParameter("@mname", cbbmname.Text);
            MySqlParameter mspmnamenum = new MySqlParameter("@mnamenum", txtmnamenum.Text);
            MySqlParameter mspbprice = new MySqlParameter("@bprice", txtbprice.Text);
            using (MySqlConnection con = new MySqlConnection(mysqlStr))
            {
                con.Open();
                using (MySqlCommand cmd = new MySqlCommand(strsql, con))
                {
                    cmd.Parameters.Add(mspbnum);
                    cmd.Parameters.Add(mspbtime);
                    cmd.Parameters.Add(msptnum);
                    cmd.Parameters.Add(mspcnum);
                    cmd.Parameters.Add(mspmname);
                    cmd.Parameters.Add(mspmnamenum);
                    cmd.Parameters.Add(mspbprice);
                    if (cmd.ExecuteNonQuery() > 0)
                    {
                        MessageBox.Show("添加成功！");
                    }
                    else
                    {
                        MessageBox.Show("添加失败！");
                    }


                }
            }
        }

        private void btbsearch2_Click(object sender, EventArgs e)
        {
            DataTable dt = new DataTable();
            string strsql = "select * from bill ";

            using (MySqlConnection con = new MySqlConnection(mysqlStr))
            {
                con.Open();
                using (MySqlCommand cmd = new MySqlCommand(strsql, con))
                {

                    using (MySqlDataAdapter msda = new MySqlDataAdapter(cmd))
                    {
                        msda.Fill(dt);
                    }
                }
            }
            dt3.DataSource = dt;
        }

        private void btdelete_Click(object sender, EventArgs e)
        {
            string strsql = "delete from bill where bnum=@bnum";
            MySqlParameter mspbnum = new MySqlParameter("@bnum", txtbnum.Text);

            using (MySqlConnection con = new MySqlConnection(mysqlStr))
            {
                con.Open();
                using (MySqlCommand cmd = new MySqlCommand(strsql, con))
                {
                    cmd.Parameters.Add(mspbnum);

                    if (cmd.ExecuteNonQuery() > 0)
                    {
                        MessageBox.Show("删除成功");
                    }
                    else
                    {
                        MessageBox.Show("删除失败");
                    }

                }
            }
        }

        private void btchange_Click(object sender, EventArgs e)
        {
            





        }

        private void btnsday_Click(object sender, EventArgs e)
        {
            DataTable dt = new DataTable();
            string strsql = "SELECT DISTINCT bnum,SUM(bprice) 总消费 FROM bill WHERE MONTH(btime) =@btimed AND DAY(btime)=@btimem GROUP BY bnum";
            MySqlParameter mspbtimed = new MySqlParameter("@btimed",cbbday.Text);
            MySqlParameter mspbtimem = new MySqlParameter("@btimem",cbbmon.Text);
            using (MySqlConnection con = new MySqlConnection(mysqlStr))
            {
                con.Open();
                using (MySqlCommand cmd = new MySqlCommand(strsql, con))
                {
                    cmd.Parameters.Add(mspbtimed);
                    cmd.Parameters.Add(mspbtimem);
                    using (MySqlDataAdapter msda = new MySqlDataAdapter(cmd))
                    {
                        msda.Fill(dt);
                    }
                }
            }
            dt3.DataSource = dt;

        }

        private void button1_Click(object sender, EventArgs e)
        {
            DataTable dt = new DataTable();
            string strsql = "SELECT DISTINCT MONTH(btime) 月份,SUM(bprice) 销售额 FROM bill WHERE YEAR(btime)=@btimey GROUP BY 月份";
            MySqlParameter mspbtimey = new MySqlParameter("@btimey", cbbyear.Text);
            using (MySqlConnection con = new MySqlConnection(mysqlStr))
            {
                con.Open();
                using (MySqlCommand cmd = new MySqlCommand(strsql, con))
                {
                    cmd.Parameters.Add(mspbtimey);
                    using (MySqlDataAdapter msda = new MySqlDataAdapter(cmd))
                    {
                        msda.Fill(dt);
                    }
                }
            }
            dt3.DataSource = dt;

        }

        private void btnmm_Click(object sender, EventArgs e)
        {
            DataTable dt = new DataTable();
            string strsql = "SELECT  mname 菜名,COUNT(mnamenum) 销售量 FROM bill WHERE MONTH(btime) =@btimem GROUP BY mname";
            MySqlParameter mspbtimem = new MySqlParameter("@btimem", cbbmon2.Text);
            using (MySqlConnection con = new MySqlConnection(mysqlStr))
            {
                con.Open();
                using (MySqlCommand cmd = new MySqlCommand(strsql, con))
                {
                    cmd.Parameters.Add(mspbtimem);
                    using (MySqlDataAdapter msda = new MySqlDataAdapter(cmd))
                    {
                        msda.Fill(dt);
                    }
                }
            }
            dt3.DataSource = dt;




        }

点击转全部代码链接