C#作业三数据库连接

一、实验要求

1、请设计一个项目连接到自己的MySQL数据库，数据库包含至少三张表；
2、使用dataGridView控件显示表中的数据；
3、实现基本crud操作；

二、实验内容

1、连接数据库

VS2022连接mysql数据库

首先打开一个项目点击进去——选择工具——点击NuGet包管理器——点击第二个选项管理解决方案的NuGet程序包——在浏览中搜索mysql——安装第一个

2、界面设计

3、mysql在数据库中建表

 在school数据库中建立了三个表——stucent表,teacher表,course表

下面的实验中用到的实例表是student表

4、核心代码

a、数据库连接

private void button1_Click(object sender, EventArgs e)
        {
            string M_str_sqlcon = "server=localhost;user id=root;password=19990817zy;database=school;"；                                                                                            
            mc = new MySqlConnection(M_str_sqlcon);
            try
            {
                mc.Open();
                MessageBox.Show("数据库连接成功！");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        }

b、查询

private void button2_Click(object sender, EventArgs e)
        {
            string sql = "select * from student";
            mda = new MySqlDataAdapter(sql, mc);
            ds = new DataSet();
            mda.Fill(ds, "student");
            dataGridView1.DataSource = ds.Tables["student"];
            mc.Close();
        }

c、增加——添加数据

private void button3_Click(object sender, EventArgs e)
        {
            if (mda == null || ds == null)
            {
                MessageBox.Show("导入数据");
                return;
            }
            try
            {
                string msg = "你是否确定添加这条数据";
                if (1 == (int)MessageBox.Show(msg, "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Exclamation))
                {
                    MySqlCommandBuilder builder = new MySqlCommandBuilder(mda);
                    mda.Update(ds, "student");
                    MessageBox.Show("添加成功", "提示");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString(), "错误信息");
            }


        }

d、修改

private void button4_Click(object sender, EventArgs e)
        {
            if (mda == null || ds == null) 
            {
                MessageBox.Show("请先导入数据");
                return;
            }
            try
            {
                string msg = "您确定要修改吗？";
                if (1 == (int)MessageBox.Show(msg, "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Exclamation))
                {
                    MySqlCommandBuilder builder = new MySqlCommandBuilder(mda); 
                    mda.Update(ds, "student");
                    MessageBox.Show("修改成功", "提示");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString(), "错误信息");
            }
        }

e、删除

private void button5_Click(object sender, EventArgs e)
        {
            int index = dataGridView1.CurrentCell.RowIndex;
            int s_id = (int)dataGridView1.Rows[index].Cells[0].Value;
            string sql = "delete from student where s_id=" + s_id + "";
            mc.Open();
            MySqlCommand cmd = mc.CreateCommand();
            cmd.CommandText = sql;
            int i = cmd.ExecuteNonQuery();
            if (i < 0)
            {
                mc.Close();
                MessageBox.Show("删除失败");
                return;
            }
            mc.Close();     


        }

f、退出

private void button6_Click(object sender, EventArgs e)
        {
            Application.Exit();
        }

三、完整代码

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;
using System.Diagnostics;
namespace _05272
{
    public partial class Form1 : Form
    {
        MySqlConnection mc; 
        MySqlDataAdapter mda; 
        DataSet ds;  
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string M_str_sqlcon = "server=localhost;user id=root;password=19990817zy;database=school;";                                                                                             //创建数据库连接对象
            mc = new MySqlConnection(M_str_sqlcon);
            try
            {
                mc.Open();
                MessageBox.Show("数据库连接成功！");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        }

        private void button2_Click(object sender, EventArgs e)
        {
            string sql = "select * from student";
            mda = new MySqlDataAdapter(sql, mc);
            ds = new DataSet();
            mda.Fill(ds, "student");
            dataGridView1.DataSource = ds.Tables["student"];
            mc.Close();
        }

        private void button3_Click(object sender, EventArgs e)
        {
            if (mda == null || ds == null)
            {
                MessageBox.Show("导入数据");
                return;
            }
            try
            {
                string msg = "你是否确定添加这条数据";
                if (1 == (int)MessageBox.Show(msg, "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Exclamation))
                {
                    MySqlCommandBuilder builder = new MySqlCommandBuilder(mda);
                    mda.Update(ds, "student");
                    MessageBox.Show("添加成功", "提示");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString(), "错误信息");
            }


        }

        private void button4_Click(object sender, EventArgs e)
        {
            if (mda == null || ds == null) 
            {
                MessageBox.Show("请先导入数据");
                return;
            }
            try
            {
                string msg = "您确定要修改吗？";
                if (1 == (int)MessageBox.Show(msg, "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Exclamation))
                {
                    MySqlCommandBuilder builder = new MySqlCommandBuilder(mda); 
                    mda.Update(ds, "student");
                    MessageBox.Show("修改成功", "提示");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString(), "错误信息");
            }
        }

        private void button5_Click(object sender, EventArgs e)
        {
            int index = dataGridView1.CurrentCell.RowIndex;
            int s_id = (int)dataGridView1.Rows[index].Cells[0].Value;
            string sql = "delete from student where s_id=" + s_id + "";
            mc.Open();
            MySqlCommand cmd = mc.CreateCommand();
            cmd.CommandText = sql;
            int i = cmd.ExecuteNonQuery();
            if (i < 0)
            {
                mc.Close();
                MessageBox.Show("删除失败");
                return;
            }
            mc.Close();     


        }

        private void button6_Click(object sender, EventArgs e)
        {
            Application.Exit();
        }
    }
}

四、运行结果

五、实验小结

本次实验遇到的第一个问题就是VS连接mysql数据库的问题，由于VS2022并不兼容VS2019版本，无法按VS2019的操作来进行连接数据库。最后通过查阅资料，解决了这个问题。另外就是数据库查询，增加，删除，修改的代码的编写上，代码本身难度不大，但要仔细。

六、git仓库地址

https://gitee.com/long-yayun/