【MySQL】C# 连接MySQL

C# 连接MySQL

1. 添加MySQL引用

安装完MySQL之后，在安装的默认目录 C:\Program Files (x86)\MySQL\Connector NET 8.0 中查找MySQLData.dll文件。

在Visual Studio 中为项目中添加引用。

2. 引入命名空间

usingMySql.Data.MySqlClient;

3. 构建连接

privatestaticMySqlConnection m_Connect =null;privatestaticvoidConnect(){string connectStr ="server=127.0.0.1;port=3306;database=test;user=root;password=root;";
    m_Connect =newMySqlConnection(connectStr);}

server=IP地址;
port=端口号;
database=数据库名字;
user=管理员账号;
password=账号的密码;

4. 增删改查

增

privatestaticvoidInsert(){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 =newMySqlCommand(sqlStr, m_Connect);int result = cmd.ExecuteNonQuery();
        Console.WriteLine("成功影响了{0}条数据", result);}catch(Exception ex){
        Console.WriteLine(ex.ToString());}finally{
        m_Connect?.Close();}}

删

privatestaticvoidDelete(){try{
        m_Connect.Open();string sql ="delete from users where username = 'takil'";MySqlCommand cmd =newMySqlCommand(sql, m_Connect);int result = cmd.ExecuteNonQuery();
        Console.WriteLine("成功影响了{0}条数据", result);}catch(Exception ex){
        Console.WriteLine(ex.ToString());}finally{ m_Connect?.Close();}}

改

privatestaticvoidUpdate(){try{
        m_Connect.Open();string sql ="Update users set username = 'wqrwq', password = '123' where id = 5";MySqlCommand cmd =newMySqlCommand( sql, m_Connect);int result = cmd.ExecuteNonQuery();
        Console.WriteLine("成功影响了{0}条数据", result);}catch(Exception ex){
        Console.WriteLine(ex.ToString());}finally{ m_Connect?.Close();}}

查

privatestaticvoidRead(){try{
        m_Connect.Open();string sqlStr ="select * from users";//string sqlStr = "select id, username, registerdate from users";MySqlCommand cmd =newMySqlCommand(sqlStr, m_Connect);MySqlDataReader reader = cmd.ExecuteReader();while(reader.Read()){//Console.WriteLine(reader[0].ToString() + " " + reader[1].ToString() + " " + reader[2].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();}}

privatestaticvoidReadCount(){try{
        m_Connect.Open();string sqlStr ="select Count(*) from users";MySqlCommand cmd =newMySqlCommand(sqlStr, m_Connect);//MySqlDataReader reader = cmd.ExecuteReader();//reader.Read();//int count = Convert.ToInt32(reader[0]);//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();}}

验证

privatestaticboolValifyUser(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 =newMySqlCommand(sqlStr, m_Connect);

        cmd.Parameters.AddWithValue("para1", username);
        cmd.Parameters.AddWithValue("para2", password);MySqlDataReader reader = cmd.ExecuteReader();if(reader.Read()){returntrue;}}catch(Exception ex){
        Console.WriteLine(ex.ToString());}finally{
        m_Connect?.Close();}returnfalse;}

因为作者精力有限，文章中难免出现一些错漏，敬请广大专家和网友批评、指正。