-- c#.net开发中使用mysql的相关问题.
【官网】:https://www.mysql.com/
应用场景
在有的场景下,使用c#.net开发应用企业应用,但同时需要使用mysql数据库..mysql很灵活很强大,但是相关的前期配置和版本,驱动的配置需要注意..基础资源
visual studio 2015, Mysql5.6,ADO.NET Driver for MySQL(Mysql.Data.dll 6.9.9)
使用须知
mysql的不同版本有些差异..mysql.data.dll这个面向.net的连接驱动版本差异更大...有的不支持emoj字符(utf8mb4), 有的调用存储过程的细节不同...因此需要注意整个方案的各种版本.
配置步骤
步骤1)前往https://www.mysql.com/products/connector/这个地址下载.net 数据库连接驱动安装包.
步骤2)下载完指定版本后,在本地安装.
步骤3)从上述{安装目录}MySQLMySQL Connector Net 6.9.9Assemblies下的v2.0或v4.0下复制Mysql.Data.dll文件到项目的引用目录下.//使用nuget更加方便,请大家自行百度.
步骤4)在相关的.net项目中引用上述dll.
步骤5)新建一个类,增加命名空间:using MySql.Data.MySqlClient;
步骤6)在当前项目的配置文件中增加:
<connectionStrings>
<add name="MySqlStr" connectionString="server = localhost; user id = XXXX; password = XXXX; database = XXXX"/>
常见问题
-
在 System.String.InternalSubStringWithChecks(Int32 startIndex, Int32 length, Boolean fAlwaysCopy)
【解决方案】当使用参数化时,确保Size在前,Value在后. -
.net调用带有参数注释的mysql存储过程总出错
【解决方案】部分版本的mysql驱动器会在.net调用mysql存储过程时把其中的存储过程参数注释也作为参数的一部分,删掉其注释就可以调用了 -
MySql.Data.MySqlClient.CharSetMap.GetChararcterSet(DBVersion version, String CharSetName)
【解决方案】字符集版本问题其实就是.net连接驱动版本不对(6.9.9可支持utf8mb4,就是平常的emoj表情符,连接字符串需增加charset=‘utf8mb4‘;). -
System.Collections.Generic.Dictionary`2.get_Item(TKey key)
【解决方案】字符集版本问题其实就是.net连接驱动版本不对(6.9.9可支持utf8mb4,就是平常的emoj表情符,连接字符串需增加charset=‘utf8mb4‘;).
快速入门
【下面分享一个c#使用的mysql工具类:里面涉及保存错误日志的相关处理,请自行处理】
using System;
using System.Collections.Generic;
using System.Text;
using MySql.Data;
using MySql.Data.MySqlClient;
using System.Data;
using System.Collections;
using Rwx.Comp;
using Rwx.Comp.DataUtils;
namespace Rwx.DBUtility
{
///
///
///
public class MySQLHelper
{
///
/// 用于mysql的连接字符串
///
private string _connStr = "";
public MySQLHelper(string sConnStr)
{
this.Init(sConnStr);
}
///
/// 初始化
///
///
private void Init(string sConnStr)
{
this._connStr = sConnStr;
}
///
/// 功能备注:用事务的方式执行多条SQL语句
/// 创建人:黄岛主(taohuadaozhu007@qq.com)
/// 联系地址:http://www.cnblogs.com/taohuadaozhu
///
///事务隔离级别设置,可以传入:IsolationLevel.ReadCommitted
///
public void RunSQLWithTransaction(TransactionController theTransactionTool, IsolationLevel trans_IsolationLevel, string sSQLConnectstr)
{
if (theTransactionTool == null
|| theTransactionTool.ListCommand.Count == 0)
return;
int iExcuteCount = 0;
using (MySqlConnection conn = new MySqlConnection(sSQLConnectstr))
{
if (conn.State == ConnectionState.Broken || conn.State == ConnectionState.Closed)
conn.Open();
using (MySqlTransaction tran = conn.BeginTransaction(trans_IsolationLevel))
{
using (MySqlCommand sqlcm = new MySqlCommand())
{
if (conn.State == ConnectionState.Closed || conn.State == ConnectionState.Broken)
conn.Open();
sqlcm.Connection = conn;
sqlcm.Transaction = tran;
try
{
for (int i = 0; i < theTransactionTool.ListCommand.Count; i++)
{
PrepareCommand(sqlcm, conn, null, CommandType.Text, theTransactionTool.ListCommand[i].CommendText, null);
theTransactionTool.ListCommand[i].ActualReturn = sqlcm.ExecuteNonQuery();
if (theTransactionTool.ListCommand[i].IsSuccess() == false)
{
tran.Rollback();
sqlcm.Dispose();
if (conn.State == ConnectionState.Open)
conn.Close();
break;
}
iExcuteCount++;
}
if (theTransactionTool.IsSuccess())
{
tran.Commit();
}
}
catch (Exception ex)
{
tran.Rollback();
Rwx.Utils.SaveLog(string.Format("执行脚本【{0}】失败,并执行事务回滚:{1}", theTransactionTool.ListCommand[iExcuteCount].CommendText, ex.Message));
}
finally
{
sqlcm.Dispose();
tran.Dispose();
if (conn.State == ConnectionState.Open)
conn.Close();
}
}
}
}
}
public void RunSQLWithTransaction(TransactionController theTransactionTool)
{
RunSQLWithTransaction(theTransactionTool, IsolationLevel.ReadCommitted, this._connStr);
}
///
/// 给定连接的数据库用假设参数执行一个sql命令(不返回数据集)
///
///一个有效的连接字符串
///命令类型(存储过程, 文本, 等等)
///存储过程名称或者sql命令语句
///执行命令所用参数的集合
///执行命令所影响的行数
private int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
{
MySqlCommand cmd = new MySqlCommand();
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
int val = 0;
try
{
val = cmd.ExecuteNonQuery();
}
catch (Exception e)
{
//Utils.SaveLog(string.Format("执行【{0}】出错,调试信息:【{1}】", cmdText, e.StackTrace));
Utils.SaveErrorLog(string.Format("执行【{0}】出错,参数【{1}】调试跟踪:【2】【{3}】", cmdText, this.GetParamsInfo(commandParameters),e.Message, e.StackTrace));
}
finally
{
cmd.Parameters.Clear();
conn.Close();
}
return val;
}
}
///
/// 执行存储过程,并且
///
///一个有效的连接字符串
///存储过程的调用
///执行命令所用参数的集合
///执行命令所影响的行数
private void ExecuteNonQueryProcWithOutputParams(string connectionString, string cmdText, params MySqlParameter[] mysqlParamsArray)
{
int iRunStatus = 0;
this.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, cmdText, mysqlParamsArray);
}
///
/// 执行存储过程,并且
///
///一个有效的连接字符串
///存储过程的调用
///执行命令所用参数的集合
///执行命令所影响的行数
public void ExecuteNonQueryProcWithOutputParams(string cmdProcCall,params MySqlParameter[] mysqlParamsArray)
{
this.ExecuteNonQueryProcWithOutputParams(this._connStr, cmdProcCall,mysqlParamsArray);
}
///
/// 给定连接的数据库用假设参数执行一个sql命令(不返回数据集)
///
///命令类型(存储过程, 文本, 等等)
///存储过程名称或者sql命令语句
///执行命令所用参数的集合
///执行命令所影响的行数
public int ExecuteNonQuery(CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
{
return this.ExecuteNonQuery(this._connStr, cmdType, cmdText, commandParameters);
}
///
/// 给定连接的数据库用假设参数执行一个sql命令(不返回数据集)
///
///存储过程名称或者sql命令语句
///执行命令所用参数的集合
///执行命令所影响的行数
public int ExecuteNonQuery(string cmdText, params MySqlParameter[] commandParameters)
{
return this.ExecuteNonQuery(CommandType.Text, cmdText, commandParameters);
}
///
/// 用现有的数据库连接执行一个sql命令(不返回数据集)
///
///一个现有的数据库连接
///命令类型(存储过程, 文本, 等等)
///存储过程名称或者sql命令语句
///执行命令所用参数的集合
///执行命令所影响的行数
private int ExecuteNonQuery(MySqlConnection connection, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
{
MySqlCommand cmd = new MySqlCommand();
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
///
///使用现有的SQL事务执行一个sql命令(不返回数据集)
///
///
///举例:
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24));
///
///一个现有的事务
///命令类型(存储过程, 文本, 等等)
///存储过程名称或者sql命令语句
///执行命令所用参数的集合
///执行命令所影响的行数
public int ExecuteNonQuery(MySqlTransaction trans, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
{
MySqlCommand cmd = new MySqlCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
///
/// 用执行的数据库连接执行一个返回数据集的sql命令
///
///
/// 举例:
/// MySqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24));
///
///一个有效的连接字符串
///命令类型(存储过程, 文本, 等等)
///存储过程名称或者sql命令语句
///执行命令所用参数的集合
///包含结果的读取器
private MySqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
{
//创建一个MySqlCommand对象
MySqlCommand cmd = new MySqlCommand();
//创建一个MySqlConnection对象
MySqlConnection conn = new MySqlConnection(connectionString);
//在这里我们用一个try/catch结构执行sql文本命令/存储过程,因为如果这个方法产生一个异常我们要关闭连接,因为没有读取器存在,
//因此commandBehaviour.CloseConnection 就不会执行
try
{
//调用 PrepareCommand 方法,对 MySqlCommand 对象设置参数
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
//调用 MySqlCommand 的 ExecuteReader 方法
MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
//清除参数
cmd.Parameters.Clear();
return reader;
}
catch(Exception e)
{
//关闭连接,抛出异常
conn.Close();
Utils.SaveLog(string.Format("执行【{0}】出错,调试信息:【{1}】", cmdText, e.StackTrace));
throw e;
}
}
///
/// 返回DataSet
///
///一个有效的连接字符串
///命令类型(存储过程, 文本, 等等)
///存储过程名称或者sql命令语句
///执行命令所用参数的集合
///
private DataSet GetDataSet(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
{
//创建一个MySqlCommand对象
MySqlCommand cmd = new MySqlCommand();
//创建一个MySqlConnection对象
MySqlConnection conn = new MySqlConnection(connectionString);
//在这里我们用一个try/catch结构执行sql文本命令/存储过程,因为如果这个方法产生一个异常我们要关闭连接,因为没有读取器存在,
DataSet ds = null;
try
{
//调用 PrepareCommand 方法,对 MySqlCommand 对象设置参数
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
//调用 MySqlCommand 的 ExecuteReader 方法
MySqlDataAdapter adapter = new MySqlDataAdapter();
adapter.SelectCommand = cmd;
ds = new DataSet();
adapter.Fill(ds);
//清除参数
cmd.Parameters.Clear();
}
catch (Exception e)
{
Utils.SaveErrorLog(string.Format("执行【{0}】出错,参数【{1}】调试信息:【{2}】,异常消息:【{3}】", cmdText, this.GetParamsInfo(commandParameters), e.StackTrace,e.Message));
}
finally {
if(conn!=null)
{
conn.Close();
}
}
return ds;
}
private string GetParamsInfo( MySqlParameter[] commandParameters)
{
StringBuilder sbParams = new StringBuilder("");
try
{
if (commandParameters != null && commandParameters.Length > 0)
{
foreach (MySqlParameter p in commandParameters)
{
sbParams.AppendFormat("({0}:{1})", p.ParameterName, p.Value.ToString());
}
}
}
catch(Exception error){
Utils.SaveErrorLog(string.Format("获取参数时的异常:{0}", error.Message));
}
return sbParams.ToString();
}
///
/// 返回DataTable
///
///一个有效的连接字符串
///命令类型(存储过程, 文本, 等等)
///存储过程名称或者sql命令语句
///执行命令所用参数的集合
///
private DataTable GetDataTable(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
{
DataSet dsResult = GetDataSet(connectionString, cmdType, cmdText, commandParameters);
if (dsResult == null || dsResult.Tables == null || dsResult.Tables.Count == 0)
return null;
return dsResult.Tables[0];
}
///
/// 返回DataTable
///
///命令类型(存储过程, 文本, 等等)
///存储过程名称或者sql命令语句
///执行命令所用参数的集合
///
public DataTable GetDataTable( CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
{
return this.GetDataTable(this._connStr, cmdType, cmdText, commandParameters);
}
///
/// 返回DataTable
///
///存储过程名称或者sql命令语句
///执行命令所用参数的集合
///
public DataTable GetDataTable(string cmdText, params MySqlParameter[] commandParameters)
{
return this.GetDataTable(CommandType.Text, cmdText, commandParameters);
}
///
/// 用指定的数据库连接字符串执行一个命令并返回一个数据集的第一列
///
///
///例如:
/// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24));
///
///一个有效的连接字符串
///命令类型(存储过程, 文本, 等等)
///存储过程名称或者sql命令语句
///执行命令所用参数的集合
///用 Convert.To{Type}把类型转换为想要的
private object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
{
MySqlCommand cmd = new MySqlCommand();
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
}
///
/// 用指定的数据库连接字符串执行一个命令并返回一个数据集的第一列
///
///
///例如:
/// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24));
///
///命令类型(存储过程, 文本, 等等)
///存储过程名称或者sql命令语句
///执行命令所用参数的集合
///用 Convert.To{Type}把类型转换为想要的
public object ExecuteScalar(CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
{
return this.ExecuteScalar(this._connStr, cmdType, cmdText, commandParameters);
}
///
/// 用指定的数据库连接字符串执行一个命令并返回一个数据集的第一列
///
///
///例如:
/// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24));
///
///存储过程名称或者sql命令语句
///执行命令所用参数的集合
///用 Convert.To{Type}把类型转换为想要的
public object ExecuteScalar(string cmdText, params MySqlParameter[] commandParameters)
{
return ExecuteScalar(CommandType.Text,cmdText, commandParameters);
}
///
/// 用指定的数据库连接字符串执行一个命令并返回一个数据集的第一列
///
///存储过程名称或者sql命令语句
///执行命令所用参数的集合
///用 Convert.To{Type}把类型转换为想要的
public int ExecuteScalarWithIntReturn(string cmdText, params MySqlParameter[] commandParameters)
{
object obj = this.ExecuteScalar(cmdText, commandParameters);
if (obj == null || obj.ToString().Trim().Length == 0)
return int.MinValue;
int iReturn = int.MinValue;
if (int.TryParse(obj.ToString(), out iReturn))
return iReturn;
return int.MinValue;
}
///
/// 用指定的数据库连接执行一个命令并返回一个数据集的第一列
///
///
/// 例如:
/// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24));
///
///一个存在的数据库连接
///命令类型(存储过程, 文本, 等等)
///存储过程名称或者sql命令语句
///执行命令所用参数的集合
///用 Convert.To{Type}把类型转换为想要的
private object ExecuteScalar(MySqlConnection connection, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)
{
MySqlCommand cmd = new MySqlCommand();
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
public bool ExistTableName(string sTableName) {
MySqlParameter[] arrayParams = new MySqlParameter[] {
new MySqlParameter(){ DbType=DbType.String, ParameterName="tablename", Value=sTableName}
};
DataTable dtResult = this.GetDataTable(string.Format("SELECT table_name FROM information_schema.TABLES WHERE table_name =?tablename"), arrayParams);
return (dtResult!=null&&dtResult.Rows.Count>0);
}
///
/// 准备执行一个命令
///
///sql命令
///OleDb连接
///OleDb事务
///命令类型例如 存储过程或者文本
///命令文本,例如:Select * from Products
///执行命令的参数
private void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, CommandType cmdType, string cmdText, MySqlParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = cmdType;
if (cmdParms != null)
{
foreach (MySqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
}
///
/// 检查指定表是否存在指定的列
///
///
///
///
public bool ExistColumn(string sDBName,string sTableName, string sColumnName) {
string sSql = string.Format("show columns from {0}.`{1}` like ‘{2}‘ ", sDBName, sTableName, sColumnName);
DataTable resultDT = this.GetDataTable(sSql, null);
if (resultDT == null || resultDT.Rows.Count == 0)
return false;
return true;
}
///
///
///
///
///
///例如:QQBlob
///例如:blob not null
public void AlterTableColumn(string sDBName,string sTableName,string sColumnName,string sColumnTypeAndConstraint) {
//alter table qqgroup.`table_343` add column QQBlob blob not null;
string sSql = string.Format("alter table {0}.`{1}` add column {2} {3};",sDBName,sTableName,sColumnName,sColumnTypeAndConstraint);
this.ExecuteNonQuery(sSql, null);
Utils.SaveLog(string.Format("执行了【{0}】修改列语句",sSql));
}
}
}