使用C#控制MySQL数据库的增删改查

首先需要为VS添加新的引用，路径：xxxxx\MySQL\Connector NET 8.0\Assemblies\v4.5.2\MySql.Data.dll

//#define 查询
//#define 插入
//#define 删除
#define 更新
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;


namespace DatabaseOperation
{
    class Program
    {
        static void Main(string[] args)
        {
            string connStr = "Database=test;Data Source=127.0.0.1;port=3306;User Id=root;Password=wan123;";//连接数据库的语句
            //string connStr2 = "Database=test;datasource=127.0.0.1;port=3306;user=root;pwd=wan123;";//同样可以识别到
            MySqlConnection conn = new MySqlConnection(connStr);
            conn.Open();//进行数据库连接
#if 查询
            MySqlCommand cmd = new MySqlCommand("select * from mytable", conn);//发送一条命令
            MySqlDataReader reader = cmd.ExecuteReader();//查询命令
            while (reader.Read())
            {
                int id = reader.GetInt32("id");
                string username = reader.GetString("username");
                string password = reader.GetString("password");
                Console.WriteLine("id:" + id + "  username:" + username + "  password:" + password);
            }
            Console.ReadKey();
            reader.Close();
            conn.Close();
#endif
#if 插入
            string username = "赵刚";
            string password = "pw4512345;delete from user;";//这条字符串带有恶意的SQL注入语句
            //虽然使用这种组拼的方式也能输入SQL语句，但是无法防止SQL注入的问题，而且组拼很容易出错
            //MySqlCommand cmd = new MySqlCommand("insert into mytable(username,password)values(\"" + username + "\",\"" + password + "\");",conn);
            //下面这种输入方式就可以防止SQL注入问题，形式上更加简洁，不容易出错
            MySqlCommand cmd = new MySqlCommand("insert into mytable(username,password)values(@user,@pwd)", conn);
            cmd.Parameters.AddWithValue("user", username);
            cmd.Parameters.AddWithValue("pwd", password);
            cmd.ExecuteNonQuery();//此语句表示当前命令是非查询命令
            Console.ReadKey();
            
            conn.Close();
#endif
#if 删除
            MySqlCommand cmd = new MySqlCommand("delete from mytable where id=8", conn);
            cmd.ExecuteNonQuery();
            Console.ReadKey();
            
            conn.Close();
#endif
#if 更新
            MySqlCommand cmd = new MySqlCommand("update mytable set username=@name,password=@pwd where id=6", conn);
            cmd.Parameters.AddWithValue("name", "李白");
            cmd.Parameters.AddWithValue("pwd", "11111");
            cmd.ExecuteNonQuery();
            Console.ReadKey();
            
            conn.Close();
#endif
        }
    }
}