c# mysql修改_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

{

///

/// MySqlConnection连接对象

///

private MySqlConnection connection;

///

/// 服务器地址

///

private string server;

///

/// 数据库实例名称

///

private string database;

///

/// 用户名

///

private string uid;

///

/// 密码

///

private string password;

///

/// 端口号

///

private string port;

public MySqlConnection GetInstance()

{

return connection;

}

///

/// 初始化mysql连接

///

/// 服务器地址

/// 数据库实例

/// 用户名称

/// 密码

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);

}

///

/// 打开数据库连接

///

/// 是否成功

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;

}

}

///

/// 关闭数据库连接

///

///

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;

}

///

/// 构建SQL句柄

///

/// SQL语句

///

public MySqlCommand CreateCmd(string SQL)

{

MySqlCommand Cmd = new MySqlCommand(SQL, connection);

return Cmd;

}

///

/// 运行MySql语句返回 MySqlDataReader对象

///

///

/// MySqlDataReader对象

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;

}

///

/// 根据SQL获取DataTable数据表

///

/// 查询语句

/// 返回表的表名

///

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;

}

///

/// 运行MySql语句,返回DataSet对象

///

/// 查询语句

/// 待填充的DataSet对象

/// 表名

///

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;

}

///

/// 运行MySql语句,返回DataSet对象，将数据进行了分页

///

/// 查询语句

/// 待填充的DataSet对象

/// 开始项

/// 每页数据条数

/// 表名

///

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;

}

///

/// 添加数据

///

///

public bool GetInsert(MySqlCommand mySqlCommand)

{

try

{

if (mySqlCommand.ExecuteNonQuery() > 0)

return true;

else

return false;

}

catch (Exception ex)

{

string message = ex.Message;

return false;

}

}

///

/// 修改数据

///

///

public bool GetUpdate(MySqlCommand mySqlCommand)

{

try

{

if (mySqlCommand.ExecuteNonQuery() > 0)

return true;

else

return false;

}

catch (Exception ex)

{

string message = ex.Message;

return false;

}

}

///

/// 删除数据

///

///

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");

}

}

}

}

调用代码图