C#数据库连接并进行基本crud操作

实验目的：

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

实验过程：

1.请设计一个项目连接到自己的MySQL数据库，数据库包含至少三张表；

注：该版本为VS2022

点击第二个选项

在搜索框搜索MySQL

点击第一个下载即可连接MySQL数据库。

2.实现crud操作

在写代码之前需要加入using MySql.Data.MySqlClient;

 核心代码

using System;
using System.Data;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
namespace mysql
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
 
        private void Form1_Load(object sender, EventArgs e)
        {
 
        }
 
        private void button1_Click(object sender, EventArgs e)
        {
            //与数据库连接的信息
            MySqlConnectionStringBuilder builder = new MySqlConnectionStringBuilder();
            //用户名
            builder.UserID = "root";
            //密码
            builder.Password = "12345678";
            //服务器地址
            builder.Server = "localhost";
            //连接时的数据库
            builder.Database = "test";
            //定义与数据连接的链接
            MySqlConnection connection = new MySqlConnection(builder.ConnectionString);
            //打开这个链接
            connection.Open();
            string strcmd = this.textBox1.Text;
            MySqlCommand cmd = new MySqlCommand(strcmd, connection);
            MySqlDataAdapter ada = new MySqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            ada.Fill(ds);//查询结果填充数据集
            dataGridView1.DataSource = ds.Tables[0];
            connection.Close();//关闭连接
        }
    }
}

结果展示：

（1）查询

 

 代码

private void button_select_Click(object sender, EventArgs e)
        {
            String StuID = textBox_sid.Text;
            String StuName = textBox_sname.Text;
            String StuSex = textBox_ssex.Text;
            String StuBirth = textBox_sbirth.Text;
            try
            {
                conn.Open();
                String select_by_id = "select * from Student where ";
                int flag1 = 0; //表示前面是否已经加了筛选条件,为1，则后面的条件需要加AND
                if (checkBox_sid.Checked == true)
                {
                    if (flag1 == 0)
                    {
                        select_by_id += "s_id =" + "\"" + StuID + "\"";
                        flag1 = 1;
                    }
                    else
                        select_by_id += "AND s_id =" + "\"" + StuID + "\"";               
                }
                if (checkBox_sname.Checked == true)
                {
                    if (flag1 == 0)
                    {
                        select_by_id += "s_name =" + "\"" + StuName + "\"";
                        flag1 = 1;
                    }
                    else
                        select_by_id += "AND s_name =" +"\"" + StuName + "\"";
                }
                if (checkBox_ssex.Checked == true)
                {
                    if (flag1 == 0)
                    {
                        select_by_id += "s_sex =" + "\"" + StuSex + "\"";
                        flag1 = 1;
                    }
                    else
                        select_by_id += "AND s_sex =" + "\"" + StuSex + "\"";
                }
                if (checkBox_sbirth.Checked == true)
                {
                    if (flag1 == 0)
                    {
                        select_by_id += "s_birth ="+ "\""+StuBirth + "\"";
                        flag1 = 1;
                    }
                    else
                        select_by_id += "AND Sage =" + "\"" + StuBirth + "\"";
                }
                MySqlCommand sqlCommand = new MySqlCommand(select_by_id, conn);
                MySqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
                BindingSource bindingSource = new BindingSource();
                bindingSource.DataSource = sqlDataReader;
                dataGridView1.DataSource = bindingSource;
            }
            catch{MessageBox.Show("查询语句有误，请认真检查SQL语句!", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);}
            finally{conn.Close();}
        }

 （2) 删除

        private void button_delete_Click(object sender, EventArgs e)
        {
            try
            {
                conn.Open();
                string select_id = textBox_sid.Text;//选择的当前行第一列的值，也就是ID
                string delete_by_id = "delete from Student where s_id = " + "\"" + select_id + "\"";//sql删除语句,根据学号删除
                MySqlCommand cmd = new MySqlCommand(delete_by_id, conn);
                cmd.ExecuteNonQuery(); //执行命令
            }
            catch { MessageBox.Show("请正确选择行!", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error); }
            finally { conn.Dispose(); }
            print_in_dataGridView();
 
        }

（3)  插入

private void button_insert_Click(object sender, EventArgs e)
        {
            String StuID = textBox_sid.Text;
            String StuName = textBox_sname.Text;
            String StuBirth = textBox_sbirth.Text;
            String StuSex = textBox_ssex.Text;
            
            try
            {
                conn.Open();
                String insertstr = "INSERT INTO Student (s_id,s_name,s_birth,s_sex) VALUES"+"("+StuID+","+StuName+","+StuBirth+","+StuSex+");";
                MySqlCommand cmd = new MySqlCommand(insertstr, conn); //实例化数据库命令对象
                cmd.ExecuteNonQuery(); //执行命令
            }
            catch{ MessageBox.Show("输入数据有误，请输入有效数据！", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);}
            finally { conn.Close(); }
            //显示到dataGridView
            print_in_dataGridView();
        }

（3）修改

        private void button_update_Click(object sender, EventArgs e)
        {
            int flag1 = 0, flag2 = 0;
            try
            {
                conn.Open();//打开数据库
                string updatestr = "UPDATE Student SET ";
                String StuID = textBox_sid.Text;
                String StuName = textBox_sname.Text;
                String StuBirth = textBox_sbirth.Text;
                String StuSex = textBox_ssex.Text;
                if (checkBox_sbirth.Checked == true)
                {
                    if (flag1 == 0)
                    {
                        updatestr += "s_birth = " +"\""+ StuBirth+"\"";
 
                        flag1 = 1;
 
                    }
                    else
                        updatestr += ", s_birth = " + "\"" + StuBirth + "\"";
                }
                if (checkBox_sname.Checked == true)
                {
                    if (flag1 == 0)
                    {
                        updatestr += "s_name = " + "\"" + StuName + "\"";
                        flag1 = 1;
                    }
                    else
                        updatestr += ", s_name = " + "\"" + StuName + "\"";              
}
                if (checkBox_ssex.Checked == true)
                {
                    if (flag1 == 0)
                    {
                        updatestr += "s_sex = " + "\""+ StuSex + "\"";                    
                        flag1 = 1;
                    }
                    else
                        updatestr += ", s_sex = " + "\"" + StuSex + "\"";
                }
                updatestr += " WHERE s_id = " + "\"" + StuID + "\"";
                MySqlCommand cmd = new MySqlCommand(updatestr, conn);
                cmd.ExecuteNonQuery();
            }
            catch
            {
                flag2 = 1;
                MessageBox.Show("输入数据违反要求!", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally{ conn.Close();}
 
            print_in_dataGridView();
            if (flag2 == 0)
            {
                MessageBox.Show("修改成功！", "Tips", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
            }
        }

3.实验小结

本次实验最大的问题是开始连接数据库的过程，因为网上大部分都是数据库连接VS2018或2019，在同学的帮助下完成了数据库的连接，后续操作比较常规，没有遇到太大的问题。

git仓库地址