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数据库进行增删改查
链接：https://www.networkcabin.com/notes/1457
文章版权归本站所有，未经授权请勿转载。
作者：兴兴
文章：C#连接Mysql数据库进行增删改查
链接：https://www.networkcabin.com/notes/1457
文章版权归本站所有，未经授权请勿转载。
THE END
