C#操作mysql数据库的封装类（封装函数⽅法）前⾔：
Mys q lB as e.cs
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Collections;
using System.Diagnostics;
using MySql.Data.MySqlClient;
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)
{
conn = new MySqlConnection(connstr);
}
/// <summary>
/// 检测数据库连接状态
/// </summary>
/// <returns></returns>
public bool CheckConnectStatus()
{
bool result = false;
try
{
conn.Open();
if (conn.State == ConnectionState.Open)
{
result = true;
}
}
catch
{
result = false;
}
finally
{
conn.Close();
}
return result;
}
/// <summary>
/// 增、删、改公共⽅法
/
// 增、删、改公共⽅法
/// </summary>
/// <returns></returns>
public int commonExecute(string sql)
{
int res = -1;
try
{
//当连接处于打开状态时关闭，然后再打开，避免有时候数据不能及时更新
if (conn.State == ConnectionState.Open)
conn.Close();
conn.Open();
command = new MySqlCommand(sql, conn);
res = command.ExecuteNonQuery();
}
catch (MySqlException)
{
}
conn.Close();
return res;
}
/// <summary>
/
// 查询⽅法
/// 注意：尽量不要⽤select * from table表（返回的数据过长时，DataTable可能会出错），最好指定要查询的字段。        /// </summary>
/// <returns></returns>
public DataTable query(string sql)
{
//当连接处于打开状态时关闭，然后再打开，避免有时候数据不能及时更新
rows函数的使用方法及实例if (conn.State == ConnectionState.Open)
conn.Close();
conn.Open();
command = new MySqlCommand(sql, conn);
DataTable dt = new DataTable();
using (reader = command.ExecuteReader(CommandBehavior.CloseConnection))
{
dt.Load(reader);
}
return dt;
}
/// <summary>
/// 获取DataSet数据集
/// </summary>
/// <param name="sql"></param>
/// <param name="tablename"></param>
/
// <returns></returns>
public DataSet GetDataSet(string sql, string tablename)
{
//当连接处于打开状态时关闭，然后再打开，避免有时候数据不能及时更新
if (conn.State == ConnectionState.Open)
conn.Close();
conn.Open();
command = new MySqlCommand(sql, conn);
DataSet dataset = new DataSet();
MySqlDataAdapter adapter = new MySqlDataAdapter(command);
adapter.Fill(dataset, tablename);
conn.Close();
return dataset;
}
/// <summary>
/// 实现多SQL语句执⾏的数据库事务
/// </summary>
/// <param name="SQLStringList">SQL语句集合（多条语句）</param>
public bool ExecuteSqlTran(List<string> SQLStringList)
{
bool flag = false;
bool flag = false;
/
/当连接处于打开状态时关闭，然后再打开，避免有时候数据不能及时更新
if (conn.State == ConnectionState.Open)
conn.Close();
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
//开启事务
MySqlTransaction tran = BeginTransaction();
cmd.Transaction = tran;//将事务应⽤于CMD
try
{
foreach (string strsql in SQLStringList)
{
if (strsql.Trim() != "")
{
cmd.CommandText = strsql;
cmd.ExecuteNonQuery();
}
}
tran.Commit();//提交事务（不提交不会回滚错误）
flag = true;
}
catch (Exception)
{
tran.Rollback();
flag = false;
}
finally
{
conn.Close();
}
return flag;
}
}
}
使⽤⽰例：
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;
DataTable dt = mysqlBase.query(sql);
if (dt.Rows.Count != 0)
{
int id = Convert.ToInt32(dt.Rows[0][0]);
string name = dt.Rows[0][1];
}
else
{
MessageBox.Show("⽆数据");
}
3、增、删、改
string sql = "update student set name='张三',age=20 where id="+id; int res = mysqlBasemonExecute(sql);
if (res > 0)
{
MessageBox.Show("更新成功");
}else{
MessageBox.Show("更新失败");
}
上⾯演⽰的是更新操作，增加和删除是类似的。