论坛 › SqlServer › 【MySQL】C# 连接MySQL

伤心客 发表于 2024-8-8 14:37:30

【MySQL】C# 连接MySQL

C# 连接MySQL

1. 添加MySQL引用

安装完MySQL之后，在安装的默认目录 C:\Program Files (x86)\MySQL\Connector NET 8.0 中查找MySQLData.dll文件。
在Visual Studio 中为项目中添加引用。
https://i-blog.csdnimg.cn/blog_migrate/aafe8fcad6d561d4e3b969489360028a.pnghttps://i-blog.csdnimg.cn/blog_migrate/759ec4df206b21b58cab17e44ee3203d.png
2. 引入命名空间

using MySql.Data.MySqlClient;
3. 构建连接

private static MySqlConnection m_Connect = null;
private static void Connect()
{
    string connectStr = "server=127.0.0.1;port=3306;database=test;user=root;password=root;";
    m_Connect = new MySqlConnection(connectStr);
}
server=IP地址;
port=端口号;
database=数据库名字;
user=管理员账号;
password=账号的密码;
4. 增编削查

增

private static void Insert()
{
    try
    {
      m_Connect.Open();
      //string sqlStr = "Insert into users(username, password) values('takil', '789')";
      //string sqlStr = "Insert into users(username, password, registerdate) values('taksil', '789789', '2015-05-09')";
      string sqlStr = "Insert into users(username, password, registerdate) values('wilhelm', '78889', '" + DateTime.Now + "')";
      
      MySqlCommand cmd = new MySqlCommand(sqlStr, m_Connect);
      int result = cmd.ExecuteNonQuery();
      Console.WriteLine("成功影响了{0}条数据", result);
    }
    catch (Exception ex)
    {
      Console.WriteLine(ex.ToString());
    }
    finally
    {
      m_Connect?.Close();
    }
}
删

private static void Delete()
{
    try
    {
      m_Connect.Open();
      string sql = "delete from users where username = 'takil'";

      
      MySqlCommand cmd = new MySqlCommand(sql, m_Connect);
      int result = cmd.ExecuteNonQuery();
      Console.WriteLine("成功影响了{0}条数据", result);
    }
    catch (Exception ex)
    {
      Console.WriteLine(ex.ToString());
    }
    finally
    { m_Connect?.Close(); }
}
改

private static void Update()
{
    try
    {
      m_Connect.Open();
      string sql = "Update users set username = 'wqrwq', password = '123' where id = 5";

      MySqlCommand cmd = new MySqlCommand( sql, m_Connect);
      int result = cmd.ExecuteNonQuery();
      Console.WriteLine("成功影响了{0}条数据", result);
    }
    catch (Exception ex)
    {
      Console.WriteLine(ex.ToString());
    }
    finally
    { m_Connect?.Close(); }
}
查

private static void Read()
{
    try
    {
      m_Connect.Open();
      string sqlStr = "select * from users";
      //string sqlStr = "select id, username, registerdate from users";
      
      MySqlCommand cmd = new MySqlCommand(sqlStr, m_Connect);
      MySqlDataReader reader = cmd.ExecuteReader();
      while (reader.Read())
      {
            //Console.WriteLine(reader.ToString() + " " + reader.ToString() + " " + reader.ToString());
            //Console.WriteLine(reader.GetInt32(0) + " " + reader.GetString(1) + " " + reader.GetString(2));
            Console.WriteLine(reader.GetInt32("id") + " " + reader.GetString("username") + " " + reader.GetString("password"));
      }
    }
    catch(Exception ex)
    {
      Console.WriteLine(ex.ToString());
    }
    finally
    {
      m_Connect?.Close();
    }
}
private static void ReadCount()
{
    try
    {
      m_Connect.Open();
      string sqlStr = "select Count(*) from users";
      MySqlCommand cmd = new MySqlCommand(sqlStr, m_Connect);

      //MySqlDataReader reader = cmd.ExecuteReader();
      //reader.Read();
      //int count = Convert.ToInt32(reader);
      //Console.WriteLine("总数为{0}", count);

      object result = cmd.ExecuteScalar();
      int count = Convert.ToInt32(result);
      Console.WriteLine("总数为{0}", count);
    }
    catch (Exception ex)
    {
      Console.WriteLine(ex.ToString());
    }
    finally
    { m_Connect?.Close(); }
}
验证

private static bool ValifyUser(string username, string password)
{
    try
    {
      m_Connect.Open();
      //string sqlStr = "select * from users where username = '" + username + "' and password = '" + password + "'";
      string sqlStr = "select * from users where username = @para1 and password = @para2";

      MySqlCommand cmd = new MySqlCommand(sqlStr, m_Connect);

      cmd.Parameters.AddWithValue("para1", username);
      cmd.Parameters.AddWithValue("para2", password);

      MySqlDataReader reader = cmd.ExecuteReader();
      if(reader.Read())
      {
            return true;
      }
    }
    catch (Exception ex)
    {
      Console.WriteLine(ex.ToString());
    }
    finally
    {
      m_Connect?.Close();
    }
    return false;
}
   因为作者精力有限，文章中难免出现一些错漏，敬请广大专家和网友品评、指正。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

查看完整版本: 【MySQL】C# 连接MySQL