C#基础学习19（数据库增删改查）

效果图：

1.引用MySql.Data.dll并在程序中  using MySql.Data.MySqlClient; 

2.写一个Product的实体类

3.制作窗体

4.思路代码：

public partial class Form1 : Form
{
    //连接字符串
    private string connStr = "server=localhost;user id=root;password=123;database=warehouse;port=3306;charset=utf8";
    public Form1()
    {
        InitializeComponent();
    }
    //窗体加载
    private void Form1_Load(object sender, EventArgs e)
    {
        LoadProductInfo();
    }

    private void LoadProductInfo()
    {
        List<Product> products = new List<Product>();
        //创建连接数据库对象
        using (MySqlConnection conn = new MySqlConnection(connStr))
        {
            //查询语句
            string sql = "select * from product";
            using (MySqlCommand mcmd = new MySqlCommand(sql,conn))
            {
                conn.Open();//打开数据库
                MySqlDataReader reader = mcmd.ExecuteReader();//获取数据库内的数据
                if (reader.HasRows)//是否有数据
                {
                    //有数据循环读，存到List中
                    while (reader.Read())
                    {
                        products.Add(new Product() 
                        { 
                            Id=Convert.ToInt32(reader["number"]),
                            Name=reader["name"].ToString(),
                            Madetime=reader["madetime"].ToString(),
                            Price=Convert.ToInt32(reader["price"].ToString())
                        });
                    }
                }
            }
        }
        dgv.AutoGenerateColumns = false;//禁止自动生成列
        dgv.DataSource = products;//绑定数据
    }
    //点击添加按钮
    private void button1_Click(object sender, EventArgs e)
    {
        string name = txtAddName.Text.Trim();
        string time = txtAddTime.Text.Trim();
        string price = txtAddPrice.Text.ToString().Trim();
        if (name.Length>0 && time.Length>0 && price.Length>0)
        {
            int r = 0;
            using (MySqlConnection conn = new MySqlConnection(connStr))
            {
                string sql = string.Format("insert into product (name,madetime,price) VALUES('{0}','{1}',{2})",name,time,price);
                using (MySqlCommand mcmd = new MySqlCommand(sql,conn))
                {
                    conn.Open();
                    r = mcmd.ExecuteNonQuery();
                    txtAddName.Text = "";
                    txtAddTime.Text = "";
                    txtAddPrice.Text = "";
                }
            }
            string result = r > 0 ? "添加成功" : "添加失败";
            MessageBox.Show(result);
            LoadProductInfo();
        }
        else
        {
            MessageBox.Show("不能为空");
        }
    }
    //点击删除该商品事件
    private void 删除该商品ToolStripMenuItem_Click(object sender, EventArgs e)
    {
        //判断是否选定行
        if (dgv.SelectedRows.Count>0)
        {
            int r = 0;
            //获取数据中的第一行第一列：id
            string id = dgv.SelectedRows[0].Cells[0].Value.ToString();
            using (MySqlConnection conn=new MySqlConnection(connStr))
            {
                string sql = "delete from product where number="+id;
                using (MySqlCommand mcmd=new MySqlCommand(sql,conn))
                {
                    conn.Open();
                    r = mcmd.ExecuteNonQuery();
                }
            }
            string result = r > 0 ? "删除成功" : "删除失败";
            LoadProductInfo();
        }
        else
        {
            MessageBox.Show("请先选中行");
        }
    }
    //数据被点击时
    private void dgv_CellClick(object sender, DataGridViewCellEventArgs e)
    {
        if (dgv.SelectedRows.Count>0)
        {
            string name = dgv.SelectedRows[0].Cells[1].Value.ToString();
            string time = dgv.SelectedRows[0].Cells[2].Value.ToString();
            string price = dgv.SelectedRows[0].Cells[3].Value.ToString();
            txtUpdateName.Text = name;
            txtUpdateTime.Text = time;
            txtUpdatePrice.Text = price;
        }
    }
    //点击修改按钮时
    private void button2_Click(object sender, EventArgs e)
    {
        string name = txtUpdateName.Text.Trim();
        string time = txtUpdateTime.Text.Trim();
        string price = txtUpdatePrice.Text.ToString().Trim();
        if (name.Length > 0 && time.Length > 0 && price.Length > 0)
        {
            int r = 0;
            using (MySqlConnection conn=new MySqlConnection(connStr))
            {
                string sql = string.Format("update product set name='{0}',madetime='{1}',price='{2}' where number={3}",name,time,price,dgv.SelectedRows[0].Cells[0].Value.ToString().Trim());
                using (MySqlCommand mcmd=new MySqlCommand(sql,conn))
                {
                    conn.Open();
                    r = mcmd.ExecuteNonQuery();
                }
            }
            string result = r > 0 ? "修改成功" : "修改失败";
            MessageBox.Show(result);
            LoadProductInfo();
        }
        else
        {
            MessageBox.Show("请先选中商品!");
        }
    }
}