C#连接Mysql数据库进行增删改查

2020-5-25

C#一般常用的是MSSQL数据库，至于如何连接MSSQL方法有很多，之前的文章也有提到，回顾MSSQL连接方式可以在本站搜索 DBHelper, 但是有些时候也会使用到其他数据库，比如MYSQL，下面我将整理后的MYSQLDBHelper发布出来，作为个人备份的同时希望对有需要的朋友提供一些帮助。

首先我们需要在项目中引入Mysql.Data.dll

安装步骤：VS打开项目后找到引用，然后右键管理NuGet程序包，点击浏览，搜索MYsql，搜索结果第一个就是，点击安装，等待安装完毕即可

基类代码：

using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;


namespace WebDemo.Models
{
    public class MysqlDbHelper
    {


        /// <summary>
        /// MySqlConnection连接对象
        /// </summary>
        private MySqlConnection connection;
        /// <summary>
        /// 服务器地址
        /// </summary>
        private string server;
        /// <summary>
        /// 数据库实例名称
        /// </summary>
        private string database;
        /// <summary>
        /// 用户名
        /// </summary>  
        private string uid;
        /// <summary>
        /// 密码
        /// </summary>
        private string password;
        /// <summary>
        /// 端口号
        /// </summary>
        private string port;

        public MySqlConnection GetInstance()
        {
            return connection;
        }

        /// <summary>
        /// 初始化mysql连接
        /// </summary>
        /// <param name="server">服务器地址</param>
        /// <param name="database">数据库实例</param>
        /// <param name="uid">用户名称</param>
        /// <param name="password">密码</param>
        public void Initialize(string server, string database, string uid, string password)
        {
            this.server = server;
            this.uid = uid;
            this.password = password;
            this.database = database;
            string connectionString = "server=" + server + ";user id=" + uid + ";password=" + password + ";database=" + database;
            connection = new MySqlConnection(connectionString);
        }

        /// <summary>
        /// 打开数据库连接
        /// </summary>
        /// <returns>是否成功</returns>
        public bool OpenConnection()
        {
            try
            {
                connection.Open();
                return true;
            }
            catch (MySqlException ex)
            {

                switch (ex.Number)
                {
                    case 0:
                        Console.Write("Cannot connect to server.  Contact administrator");
                        break;

                    case 1045:
                        Console.Write("Invalid username/password, please try again");
                        break;
                }
                return false;

            }
        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        /// <returns></returns>
        public bool CloseConnection()
        {
            try
            {
                connection.Close();
                return true;
            }
            catch (MySqlException ex)
            {
                Console.Write(ex.Message);
                return false;
            }
        }


        public MySqlDataAdapter GetAdapter(string SQL)
        {
            MySqlDataAdapter Da = new MySqlDataAdapter(SQL, connection);
            return Da;
        }

        /// <summary>
        /// 构建SQL句柄
        /// </summary>
        /// <param name="SQL">SQL语句</param>
        /// <returns></returns>
        public MySqlCommand CreateCmd(string SQL)
        {
            MySqlCommand Cmd = new MySqlCommand(SQL, connection);
            return Cmd;
        }

        /// <summary>
        ///  运行MySql语句返回 MySqlDataReader对象
        /// </summary>
        /// <param name="查询语句"></param>
        /// <returns>MySqlDataReader对象</returns>
        public MySqlDataReader GetReader(string SQL)
        {
            MySqlCommand Cmd = new MySqlCommand(SQL, connection);
            MySqlDataReader Dr;
            try
            {
                Dr = Cmd.ExecuteReader(CommandBehavior.Default);
            }
            catch
            {
                throw new Exception(SQL);
            }
            return Dr;
        }

        /// <summary>
        /// 根据SQL获取DataTable数据表
        /// </summary>
        /// <param name="SQL">查询语句</param>
        /// <param name="Table_name">返回表的表名</param>
        /// <returns></returns>
        public DataTable GetDataTable(string SQL, string Table_name)
        {
            MySqlDataAdapter Da = new MySqlDataAdapter(SQL, connection);
            DataTable dt = new DataTable(Table_name);
            Da.Fill(dt);
            return dt;
        }
        /// <summary>
        /// 运行MySql语句,返回DataSet对象
        /// </summary>
        /// <param name="SQL">查询语句</param>
        /// <param name="Ds">待填充的DataSet对象</param>
        /// <param name="tablename">表名</param>
        /// <returns></returns>
        public DataSet Get_DataSet(string SQL, DataSet Ds, string tablename)
        {
            MySqlDataAdapter Da = new MySqlDataAdapter(SQL, connection);
            try
            {
                Da.Fill(Ds, tablename);
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
            return Ds;
        }
        /// <summary>
        /// 运行MySql语句,返回DataSet对象，将数据进行了分页
        /// </summary>
        /// <param name="SQL">查询语句</param>
        /// <param name="Ds">待填充的DataSet对象</param>
        /// <param name="StartIndex">开始项</param>
        /// <param name="PageSize">每页数据条数</param>
        /// <param name="tablename">表名</param>
        /// <returns></returns>
        public DataSet GetDataSet(string SQL, DataSet Ds, int StartIndex, int PageSize, string tablename)
        {
            MySqlDataAdapter Da = new MySqlDataAdapter(SQL, connection);
            try
            {
                Da.Fill(Ds, StartIndex, PageSize, tablename);
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
            return Ds;
        }
        /// <summary>
        /// 添加数据
        /// </summary>
        /// <param name="mySqlCommand"></param>
        public bool GetInsert(MySqlCommand mySqlCommand)
        {
            try
            {
                if (mySqlCommand.ExecuteNonQuery() > 0)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                string message = ex.Message;
                return false;
            }

        }
        /// <summary>
        /// 修改数据
        /// </summary>
        /// <param name="mySqlCommand"></param>
        public bool GetUpdate(MySqlCommand mySqlCommand)
        {
            try
            {
                if (mySqlCommand.ExecuteNonQuery() > 0)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                string message = ex.Message;
                return false;
            }
        }
        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="mySqlCommand"></param>
        public bool GetDelete(MySqlCommand mySqlCommand)
        {
            try
            {
                if (mySqlCommand.ExecuteNonQuery() > 0)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                string message = ex.Message;
                return false;
            }
        }
    }
}

调用代码

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using WebDemo.Models;
using Newtonsoft.Json;
using MySql.Data.MySqlClient;

namespace WebDemo.Controllers
{
    public class OrdersController : Controller
    {
        // GET: Orders
        public ActionResult Index()
        {
            return View();
        }

        //本页用到的userinfo测试表结构{id,name},id为int类型自增

        MysqlDbHelper mysqlDbHelper = new MysqlDbHelper();

        public JsonResult GetList()
        {
            mysqlDbHelper.Initialize("localhost", "mysqltext", "root", "Aa110110");

            mysqlDbHelper.OpenConnection();

            string sqlstr = string.Format("select * from userinfo");

            DataTable dataTable = mysqlDbHelper.GetDataTable(sqlstr, "tablename");

            mysqlDbHelper.CloseConnection();

            return Json(JsonConvert.SerializeObject(dataTable));
        }

        public JsonResult UpdateEntity(string id, string name)
        {
            mysqlDbHelper.Initialize("localhost", "mysqltext", "root", "Aa110110");

            mysqlDbHelper.OpenConnection();

            string sqlstr = string.Format("update userinfo set name = '{0}' where id={1}", name, id);

            MySqlCommand mySqlCommand = mysqlDbHelper.CreateCmd(sqlstr);

            if (mysqlDbHelper.GetUpdate(mySqlCommand))
            {
                mysqlDbHelper.CloseConnection();
                return Json("true");

            }
            else
            {
                mysqlDbHelper.CloseConnection();
                return Json("true");
            }

        }

        public JsonResult InsertEntity(string name)
        {
            mysqlDbHelper.Initialize("localhost", "mysqltext", "root", "Aa110110");

            mysqlDbHelper.OpenConnection();

            string sqlstr = string.Format("insert into userinfo(name) value('{0}') ", name);

            MySqlCommand mySqlCommand = mysqlDbHelper.CreateCmd(sqlstr);

            if (mysqlDbHelper.GetInsert(mySqlCommand))
            {
                mysqlDbHelper.CloseConnection();
                return Json("true");

            }
            else
            {
                mysqlDbHelper.CloseConnection();
                return Json("true");
            }

        }

        public JsonResult DeleteEntity(string id)
        {
            mysqlDbHelper.Initialize("localhost", "mysqltext", "root", "Aa110110");

            mysqlDbHelper.OpenConnection();

            string sqlstr = string.Format("delete from userinfo where id={0} ", id);

            MySqlCommand mySqlCommand = mysqlDbHelper.CreateCmd(sqlstr);

            if (mysqlDbHelper.GetDelete(mySqlCommand))
            {
                mysqlDbHelper.CloseConnection();
                return Json("true");

            }
            else
            {
                mysqlDbHelper.CloseConnection();
                return Json("true");
            }

        }



        public ActionResult CheckLogin(string username, string password)
        {
            mysqlDbHelper.Initialize("localhost", "mysqltext", "root", "Aa110110");

            mysqlDbHelper.OpenConnection();

            string sqlstr = string.Format("select * from userinfo where username='{0}' and password='{1}'", username, password);

            DataTable dataTable = mysqlDbHelper.GetDataTable("select * from userinfo where", "tablename");

            mysqlDbHelper.CloseConnection();

            if (dataTable.Rows.Count > 0)
            {
                return Json(JsonConvert.SerializeObject(GetMenu(dataTable.Rows[0]["type"].ToString())));
            }
            else
            {
                return Json("false");

            }

        }
    }
}

调用代码图

版权声明：
作者：兴兴
文章：C#连接Mysql数据库进行增删改查
链接：https://www.networkcabin.com/notes/1457
文章版权归本站所有，未经授权请勿转载。
THE END
C# mysql DbHelperC# mysql 增删查改C#使用MYSQL数据库C#如何连接MYSQL
彻底解决Windows远程桌面出现:这可能是由于CredSSP加密修正
<<上一篇
C#快速实现字符串MD5加密
下一篇>>
相关推荐
掌握 Web 开发中的基本算术运算
如何使用ChatGPT来解决编码中遇到的问题
.NET中的面向对象(OOP)编程实例
根据时间区间拆分时间节点进行分账操作
JQuery实现常见的带倒计时按钮
C#快速实现word转pdf与jpg转pdf功能
.NET接口接收和返回XML格式数据
C#快速将一个List拆分为多个List

网络小屋是一个分享原创内容和免费工具的技术型网站,我们一直以来免费为网友提供软件下载和原创文章,网络小屋将会一如既往的更新大量原创内容,为广大网友提供更加优质和稳定的服务。

13,252条垃圾评论已被Akismet阻挡