c#操作mysql

C#操作mysql需要添加相应版本的MySql.Data.dll引用

MysqlBase.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;
 
namespace 一键配置工具
{
    class MysqlBase
    {
        private MySqlConnection conn = null;
        private MySqlCommand command = null;
        private MySqlDataReader reader = null;
 
        /// <summary>
        /// 构造方法里建议连接
        /// </summary>
        /// <param name="connstr"></param>
        public MysqlBase(string connstr)
        {
            //我这里是在构造函数里传入mysql连接语句
            conn = new MySqlConnection(connstr);
        }
        /// <summary>
        /// 发送指令
        /// </summary>
        /// <param name="sql"></param>
        public void CreateCommand(string sql)
        {
            conn.Open();
            command = new MySqlCommand(sql, conn);
        }
        /// <summary>
        /// 增、删、改公共方法
        /// </summary>
        /// <returns></returns>
        public int commonExecute()
        {
            int res = -1;
            try
            {
                res = command.ExecuteNonQuery();
            }catch(MySqlException ex)
            {
                Console.WriteLine("操作失败!" + ex.Message);
            }
            conn.Close();
            return res;
        }
        /// <summary>
        /// 查询方法
        /// 注意：尽量不要用select * from table表（返回的数据过长时，DataTable可能会出错），最好指定要查询的字段。
        /// </summary>
        /// <returns></returns>
        public DataTable selectExecute()
        {
            DataTable dt = new DataTable();
            using (reader = command.ExecuteReader(CommandBehavior.CloseConnection))
            {
                dt.Load(reader);
            }
            return dt;
        }
 
    }
}

使用示例：

1、实例类

string mysqlStr = "Database=mydata_db;Data Source=127.0.0.1;User Id=root;Password=root;;pooling=false;CharSet=utf8;port=3306;"
MysqlBase mysqlBase = new MysqlBase(mysqlStr);

2、查询

//查询
string sql = "select * from student where id=" + id;
mysqlBase.CreateCommand(sql);
DataTable dt = mysqlBase.selectExecute();
if (dt.Rows.Count != 0)
{
    int id = Convert.ToInt32(dt.Rows[0][0]);
    int name = dt.Rows[0][1];
}
else
{
    MessageBox.Show("无数据");
}

3、增、删、改

string sql = "update student set name='张三',age=20 where id="+id;
mysqlBase.CreateCommand(sql);
int res = mysqlBase.commonExecute();
if (res > 0)
{
    MessageBox.Show("更新成功");
}else{
    MessageBox.Show("更新失败");
}