C#连接Mysql数据库进行增删改查 – NA博客

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

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数据库进行增删改查
返回顶部

显示

忘记密码?

显示

显示

获取验证码

Close