首先给大家看一下这个小项目的结构(一个 WebAPI 的小项目):
上面红框里的文件是这篇文章所涉及的主要文件。
首先在你的项目中添加 MySQL 的支持,在 NuGet 上搜索 MySql 并安装第三方库(不要选错了哟~),如下图:
创建一个MySQL数据库连接信息实体类,这个类会替代我们常用的连接字符串,而且可以实现对各个参数的配置、热修改。
/// <summary> MySQL数据库连接信息实体类
/// </summary>
public class MySqlDBConnectInfo
{
/// <summary> 唯一别名(用来区分是哪个链接,推荐策略:服务地址-数据库名)
/// </summary>
public string UniqueAlias { get; set; }
/// <summary> 数据库服务连接地址,例:"127.0.0.1"
/// </summary>
public string Server { get; set; }
/// <summary> 数据库服务连接端口号
/// </summary>
public uint Port { get; set; }
/// <summary> 连接的数据库
/// </summary>
public string Database { get; set; }
/// <summary> 连接数据库的用户名
/// </summary>
public string UserID { get; set; }
/// <summary> 用户密码
/// </summary>
public string Password { get; set; }
/// <summary> 设置字符编码,例:"utf8"
/// </summary>
public string CharacterSet { get; set; }
/// <summary> 连接超时时间,单位秒
/// </summary>
public uint ConnectionTimeout { get; set; }
/// <summary> 数据库执行超时时间,单位秒
/// </summary>
public uint DefaultCommandTimeout { get; set; }
/// <summary> 是否开启连接池,true
/// </summary>
public bool Pooling { get; set; }
/// <summary> 连接池中最小连接数
/// </summary>
public uint MinimumPoolSize { get; set; }
/// <summary> 连接池中最大连接数
/// </summary>
public uint MaximumPoolSize { get; set; }
/// <summary> 连接池中连接对象存活时间,单位秒
/// </summary>
public uint ConnectionLifeTime { get; set; }
/// <summary> 连接是否使用压缩,true
/// </summary>
public bool UseCompression { get; set; }
/// <summary> 表示连接池程序是否会自动登记创建线程的当前事务语境中的连接,ture
/// </summary>
public bool AutoEnlist { get; set; }
}
新建一个类 StartupInitMySqlConfig,用来控制程序启动时初始化MySQL数据库配置信息
/// <summary> 启动时初始化MySQL数据库配置信息
/// </summary>
public static class StartupInitMySqlConfig
{
/// <summary> 数据库连接信息配置文件 的 文件名 (MySqlConnectionConfig.xml)
/// </summary>
private const string MySqlDBConnectConfigFilename = "MySqlConnectionConfig.xml";
/// <summary> 唯一别名 的 存储值
/// </summary>
private static string UniqueAliasSV = string.Empty;
/// <summary>
/// 唯一别名 锁;避免同时被修改
/// </summary>
private static readonly object _uniqueAliasLock = new object();
/// <summary> 唯一别名(用来区分是哪个链接,推荐策略:服务地址_数据库名)
/// </summary>
private static string uniqueAlias = string.Empty;
/// <summary> 唯一别名(用来区分是哪个链接,推荐策略:服务地址_数据库名)
/// </summary>
public static string UniqueAlias
{
get { return uniqueAlias; }
set
{
uniqueAlias = value == null ? string.Empty : value;
}
}
/// <summary> 声明一个[MySqlDBConnectInfo]数据库连接信息对象
/// </summary>
private static MySqlDBConnectInfo dbConnectInfo;
/// <summary> 获取[MySqlDBConnectInfo]数据库连接信息对象(默认取配置文件中的第一个连接对象)
/// </summary>
public static MySqlDBConnectInfo DbConnectInfo
{
get
{
try
{
lock (_uniqueAliasLock)
{
try
{
if (dbConnectInfo == null || UniqueAlias != UniqueAliasSV)
{
UniqueAliasSV = UniqueAlias;
string path = AppDomain.CurrentDomain.BaseDirectory + MySqlDBConnectConfigFilename;
if (!File.Exists(path))
{
dbConnectInfo = null;
throw new Exception("系统找不到数据库连接信息配置文件。");
}
else
{
List<MySqlDBConnectInfo> dbConnectInfoList = XmlTool.XmlFlieToTObject<List<MySqlDBConnectInfo>>(path, Encoding.UTF8);
if (string.IsNullOrWhiteSpace(UniqueAliasSV))
{
dbConnectInfo = dbConnectInfoList[0];
}
else
{
foreach (MySqlDBConnectInfo item in dbConnectInfoList)
{
if (item.UniqueAlias == UniqueAliasSV)
{
dbConnectInfo = item;
break;
}
}
}
}
}
}
catch (Exception ex)
{
dbConnectInfo = null;
throw new Exception("数据库连接信息配置文件[MySqlConfig.xml]转数据库连接信息对象[MySqlDBConnectInfo]时失败。\r\n" + ex.ToString());
}
}
}
catch (Exception ex)
{
dbConnectInfo = null;
throw new Exception("数据库连接信息配置文件[MySqlConfig.xml]转数据库连接信息对象[MySqlDBConnectInfo]时失败。\r\n" + ex.ToString());
}
return dbConnectInfo;
}
set { dbConnectInfo = value; }
}
}
下面是一个 MySQL数据库操作工具类【核心功能都在这】
/// <summary> MySQL数据库操作工具类
/// </summary>
public class MySqlTool
{
/// <summary> 声明一个[MySqlDBConnectInfo]数据库连接信息对象
/// </summary>
private MySqlDBConnectInfo mysqlDbConnectInfo;
/// <summary> 无参构造函数(获取 MySqlConfig.xml 配置文件中的第一个连接对象)
/// </summary>
public MySqlTool()
{
StartupInitMySqlConfig.UniqueAlias = string.Empty;
mysqlDbConnectInfo = StartupInitMySqlConfig.DbConnectInfo;
}
/// <summary> 有参构造函数(获取 MySqlConfig.xml 配置文件中 UniqueAlias 参数所匹配的唯一别名的连接对象)
/// </summary>
/// <param name="uniqueAlias">连接对象的唯一别名,用来区分是哪个链接对象</param>
public MySqlTool(string uniqueAlias)
{
StartupInitMySqlConfig.UniqueAlias = uniqueAlias;
mysqlDbConnectInfo = StartupInitMySqlConfig.DbConnectInfo;
}
/// <summary> 声明一个[MySqlConnection]连接对象
/// </summary>
private MySqlConnection mysqlConnection;
/// <summary> 获取[MySqlConnection]连接对象
/// </summary>
private MySqlConnection MysqlConnection
{
get
{
if (mysqlDbConnectInfo == null)
{
mysqlDbConnectInfo = StartupInitMySqlConfig.DbConnectInfo;
}
if (mysqlConnection == null)
{
try
{
MySqlConnectionStringBuilder connectStr = new MySqlConnectionStringBuilder();
connectStr.Server = mysqlDbConnectInfo.Server;
connectStr.Port = mysqlDbConnectInfo.Port;
connectStr.Database = mysqlDbConnectInfo.Database;
connectStr.UserID = mysqlDbConnectInfo.UserID;
connectStr.Password = mysqlDbConnectInfo.Password;
connectStr.CharacterSet = mysqlDbConnectInfo.CharacterSet;
connectStr.ConnectionTimeout = mysqlDbConnectInfo.ConnectionTimeout;
connectStr.DefaultCommandTimeout = mysqlDbConnectInfo.DefaultCommandTimeout;
connectStr.Pooling = mysqlDbConnectInfo.Pooling;
connectStr.MinimumPoolSize = mysqlDbConnectInfo.MinimumPoolSize;
connectStr.MaximumPoolSize = mysqlDbConnectInfo.MaximumPoolSize;
connectStr.ConnectionLifeTime = mysqlDbConnectInfo.ConnectionLifeTime;
connectStr.UseCompression = mysqlDbConnectInfo.UseCompression;
connectStr.AutoEnlist = mysqlDbConnectInfo.AutoEnlist;
mysqlConnection = new MySqlConnection(connectStr.GetConnectionString(true));
}
catch (Exception ex)
{
mysqlConnection = null;
throw new Exception("创建数据库连接时发生异常。\r\n" + ex.ToString());
}
}
return mysqlConnection;
}
}
/// <summary> 获取当前对象已存在的连接信息[MySqlDBConnectInfo]
/// </summary>
/// <returns></returns>
public MySqlDBConnectInfo GetMySqlConnectInfo()
{
return mysqlDbConnectInfo;
}
/// <summary> 打开数据库连接
/// </summary>
public void OpenConnect()
{
if (MysqlConnection.State == ConnectionState.Closed)
{
try
{
MysqlConnection.Open();
}
catch (Exception ex)
{
StartupInitMySqlConfig.DbConnectInfo = null;
throw new Exception("打开数据库连接时发生异常。\r\n" + ex.ToString());
}
}
else if (MysqlConnection.State == ConnectionState.Broken)
{
try
{
MysqlConnection.Close();
MysqlConnection.Open();
}
catch (Exception ex)
{
StartupInitMySqlConfig.DbConnectInfo = null;
throw new Exception("打开数据库连接时发生异常。\r\n" + ex.ToString());
}
}
}
/// <summary> 关闭数据库连接
/// </summary>
public void CloseConnect()
{
if (MysqlConnection.State != ConnectionState.Closed)
{
try
{
MysqlConnection.Close();
}
catch (Exception ex)
{
throw new Exception("关闭数据库连接时发生异常。\r\n" + ex.ToString());
}
}
}
/// <summary> 声明一个[MySqlTransaction]事务处理对象
/// </summary>
private MySqlTransaction mysqlTransaction;
/// <summary> 开启事务处理
/// </summary>
public void BeginTransaction()
{
try
{
mysqlTransaction = MysqlConnection.BeginTransaction();
}
catch (Exception ex)
{
throw new Exception("开始事务处理时发生异常。\r\n" + ex.ToString());
}
}
/// <summary> 提交事务
/// </summary>
public void Commit()
{
try
{
if (mysqlTransaction != null)
{
mysqlTransaction.Commit();
mysqlTransaction.Dispose();
mysqlTransaction = null;
}
}
catch (Exception ex)
{
throw new Exception("提交事务时发生异常。\r\n" + ex.ToString());
}
}
/// <summary> 回滚事务
/// </summary>
public void Rollback()
{
try
{
if (mysqlTransaction != null)
{
mysqlTransaction.Rollback();
mysqlTransaction.Dispose();
mysqlTransaction = null;
}
}
catch (Exception ex)
{
throw new Exception("回滚事务时发生异常。\r\n" + ex.ToString());
}
}
/// <summary> 执行SQL语句,返回受影响行数
/// </summary>
/// <param name="sql">string:SQL语句</param>
/// <param name="dicParam">Dictionary:SQL参数化</param>
/// <returns>int</returns>
public int ExecuteNonQuery(string sql, Dictionary<string, object> dicParam = null)
{
int row = -1;
try
{
using (MySqlCommand command = MysqlConnection.CreateCommand())
{
try
{
command.CommandText = sql;
if (dicParam != null && dicParam.Count > 0)
{
foreach (KeyValuePair<string, object> kvp in dicParam)
{
command.Parameters.AddWithValue(kvp.Key, kvp.Value);
}
}
row = command.ExecuteNonQuery();
}
catch (Exception ex)
{
row = -1;
throw new Exception("执行SQL语句时发生异常[ExecuteNonQuery()]。\r\n" + ex.ToString());
}
}
}
catch (Exception ex)
{
row = -1;
throw new Exception("执行SQL语句时发生异常[ExecuteNonQuery()]。\r\n" + ex.ToString());
}
return row;
}
/// <summary> 执行SQL查询,返回第一行第一列的值(object对象)。【例如:count()函数】
/// </summary>
/// <param name="sql">string:SQL语句</param>
/// <param name="dicParam">Dictionary:SQL参数化</param>
/// <returns>object</returns>
public object ExecuteScalar(string sql, Dictionary<string, object> dicParam = null)
{
object obj = null;
try
{
using (MySqlCommand command = MysqlConnection.CreateCommand())
{
try
{
command.CommandText = sql;
if (dicParam != null && dicParam.Count > 0)
{
foreach (KeyValuePair<string, object> kvp in dicParam)
{
command.Parameters.AddWithValue(kvp.Key, kvp.Value);
}
}
obj = command.ExecuteScalar();
}
catch (Exception ex)
{
obj = null;
throw new Exception("执行SQL语句查询时发生异常[ExecuteScalar()]。\r\n" + ex.ToString());
}
}
}
catch (Exception ex)
{
obj = null;
throw new Exception("执行SQL语句查询时发生异常[ExecuteScalar()]。\r\n" + ex.ToString());
}
return obj;
}
/// <summary> 执行SQL查询,返回一个[ MySqlDataReader ]只进只读对象
/// </summary>
/// <param name="sql">string:SQL语句</param>
/// <param name="dicParam">Dictionary:SQL参数化</param>
/// <returns>(object)MySqlDataReader</returns>
public object ExecuteReader(string sql, Dictionary<string, object> dicParam = null)
{
MySqlDataReader reader = null;
try
{
using (MySqlCommand command = MysqlConnection.CreateCommand())
{
try
{
command.CommandText = sql;
if (dicParam != null && dicParam.Count > 0)
{
foreach (KeyValuePair<string, object> kvp in dicParam)
{
command.Parameters.AddWithValue(kvp.Key, kvp.Value);
}
}
reader = command.ExecuteReader();
}
catch (Exception ex)
{
reader = null;
throw new Exception("执行SQL语句查询时发生异常[ExecuteReader()]。\r\n" + ex.ToString());
}
}
}
catch (Exception ex)
{
reader = null;
throw new Exception("执行SQL语句查询时发生异常[ExecuteReader()]。\r\n" + ex.ToString());
}
return reader;
}
/// <summary> 执行查询,返回一个DataTable对象
/// </summary>
/// <param name="sql">string:SQL语句</param>
/// <param name="dicParam">Dictionary:SQL参数化</param>
/// <returns>DataTable</returns>
public DataTable QueryData_DataTable(string sql, Dictionary<string, object> dicParam = null)
{
DataTable dt = new DataTable();
try
{
using (MySqlDataAdapter da = new MySqlDataAdapter())
{
using (MySqlCommand command = MysqlConnection.CreateCommand())
{
try
{
command.CommandText = sql;
if (dicParam != null && dicParam.Count > 0)
{
foreach (KeyValuePair<string, object> kvp in dicParam)
{
command.Parameters.AddWithValue(kvp.Key, kvp.Value);
}
}
da.SelectCommand = command;
da.Fill(dt);
}
catch (Exception ex)
{
dt = null;
throw new Exception("执行SQL语句查询时发生异常[QueryData_DataTable()]。\r\n" + ex.ToString());
}
}
}
}
catch (Exception ex)
{
dt = null;
throw new Exception("执行SQL语句查询时发生异常[QueryData_DataTable()]。\r\n" + ex.ToString());
}
return dt;
}
/// <summary> 执行查询,返回一个DataSet对象
/// </summary>
/// <param name="sql">string:SQL语句</param>
/// <param name="dicParam">Dictionary:SQL参数化</param>
/// <param name="srcTable">string:映射表名称</param>
/// <returns>DataSet</returns>
public DataSet QueryData_DataSet(string sql, Dictionary<string, object> dicParam = null, string srcTable = null)
{
DataSet ds = new DataSet();
try
{
using (MySqlDataAdapter da = new MySqlDataAdapter())
{
using (MySqlCommand command = MysqlConnection.CreateCommand())
{
try
{
command.CommandText = sql;
if (dicParam != null && dicParam.Count > 0)
{
foreach (KeyValuePair<string, object> kvp in dicParam)
{
command.Parameters.AddWithValue(kvp.Key, kvp.Value);
}
}
da.SelectCommand = command;
if (string.IsNullOrWhiteSpace(srcTable))
{
da.Fill(ds);
}
else
{
da.Fill(ds, srcTable);
}
}
catch (Exception ex)
{
ds = null;
throw new Exception("执行SQL语句查询时发生异常[QueryData_DataSet()]。\r\n" + ex.ToString());
}
}
}
}
catch (Exception ex)
{
ds = null;
throw new Exception("执行SQL语句查询时发生异常[QueryData_DataSet()]。\r\n" + ex.ToString());
}
return ds;
}
/// <summary> 新增数据通用方法(字段名数组与字段值数组必须一一对应)
/// </summary>
/// <param name="tableName">数据库表名称</param>
/// <param name="fieldArray">数据库字段名数组</param>
/// <param name="valueArray">数据库字段值数组</param>
/// <returns></returns>
public int InsertSqlExe(string tableName, string[] fieldArray, object[] valueArray)
{
int row = -1;
if (string.IsNullOrWhiteSpace(tableName))
{
throw new Exception("表名 参数错误。");
}
if ((fieldArray == null) || (valueArray == null) || (fieldArray.Length < 1) || (valueArray.Length < 1) || (fieldArray.Length != valueArray.Length))
{
throw new Exception("字段/值 参数错误。");
}
StringBuilder sqlStr = new StringBuilder();
Dictionary<string, object> dicParam = new Dictionary<string, object>();
try
{
#region 动态产生SQL语句
sqlStr.AppendLine("INSERT INTO " + tableName);
sqlStr.AppendLine("(");
for (int f = 0; f < fieldArray.Length; f++)
{
if (f == fieldArray.Length - 1)
{
sqlStr.AppendLine(fieldArray[f]);
}
else
{
sqlStr.AppendLine(fieldArray[f] + ",");
}
}
sqlStr.AppendLine(")");
sqlStr.AppendLine("VALUES");
sqlStr.AppendLine("(");
for (int f = 0; f < fieldArray.Length; f++)
{
if (f == fieldArray.Length - 1)
{
sqlStr.AppendLine("?" + fieldArray[f]);
}
else
{
sqlStr.AppendLine("?" + fieldArray[f] + ",");
}
}
sqlStr.AppendLine(")");
#endregion
#region 动态参数赋值
for (int v = 0; v < valueArray.Length; v++)
{
dicParam.Add("?" + fieldArray[v], valueArray[v]);
}
#endregion
#region 执行SQL
row = ExecuteNonQuery(sqlStr.ToString(), dicParam);
#endregion
}
catch (Exception ex)
{
throw new Exception("执行新增通用方法时发生异常[InsertSqlExe()]。\r\n" + ex.ToString());
}
return row;
}
/// <summary> 新增数据通用方法(字段名集合与字段值集合必须一一对应)
/// </summary>
/// <param name="tableName">数据库表名称</param>
/// <param name="fieldList">数据库字段名集合</param>
/// <param name="valueList">数据库字段值集合</param>
/// <returns></returns>
public int InsertSqlExe(string tableName, List<string> fieldList, List<object> valueList)
{
int row = -1;
if (string.IsNullOrWhiteSpace(tableName))
{
throw new Exception("表名 参数错误。");
}
if ((fieldList == null) || (valueList == null) || (fieldList.Count < 1) || (valueList.Count < 1) || (fieldList.Count != valueList.Count))
{
throw new Exception("字段/值 参数错误。");
}
StringBuilder sqlStr = new StringBuilder();
Dictionary<string, object> dicParam = new Dictionary<string, object>();
try
{
#region 动态产生SQL语句
sqlStr.AppendLine("INSERT INTO " + tableName);
sqlStr.AppendLine("(");
for (int f = 0; f < fieldList.Count; f++)
{
if (f == fieldList.Count - 1)
{
sqlStr.AppendLine(fieldList[f]);
}
else
{
sqlStr.AppendLine(fieldList[f] + ",");
}
}
sqlStr.AppendLine(")");
sqlStr.AppendLine("VALUES");
sqlStr.AppendLine("(");
for (int f = 0; f < fieldList.Count; f++)
{
if (f == fieldList.Count - 1)
{
sqlStr.AppendLine("?" + fieldList[f]);
}
else
{
sqlStr.AppendLine("?" + fieldList[f] + ",");
}
}
sqlStr.AppendLine(")");
#endregion
#region 动态参数赋值
for (int v = 0; v < valueList.Count; v++)
{
dicParam.Add("?" + fieldList[v], valueList[v]);
}
#endregion
#region 执行SQL
row = ExecuteNonQuery(sqlStr.ToString(), dicParam);
#endregion
}
catch (Exception ex)
{
throw new Exception("执行新增通用方法时发生异常[InsertSqlExe()]。\r\n" + ex.ToString());
}
return row;
}
/// <summary> 修改数据通用方法(修改字段名数组与修改字段值数组必须一一对应;须显式指定主键列/唯一列)
/// </summary>
/// <param name="tableName">数据库表名称</param>
/// <param name="pkField">主键列/唯一列字段名</param>
/// <param name="pkValue">主键列/唯一列字段值</param>
/// <param name="fieldArray">修改字段名数组</param>
/// <param name="valueArray">修改字段值数组</param>
/// <param name="nullIsJoin">空值是否参与数据修改,默认true,false忽略空值进行数据修改</param>
/// <returns></returns>
public int UpdateSqlExe(string tableName, string pkField, object pkValue, string[] fieldArray, object[] valueArray, bool nullIsJoin = true)
{
int row = -1;
if (string.IsNullOrWhiteSpace(tableName))
{
throw new Exception("表名 参数错误。");
}
if ((string.IsNullOrWhiteSpace(pkField)) || pkValue == null)
{
throw new Exception("主键列/唯一列 参数错误。");
}
if ((fieldArray == null) || (valueArray == null) || (fieldArray.Length < 1) || (valueArray.Length < 1) || (fieldArray.Length != valueArray.Length))
{
throw new Exception("字段/值 参数错误。");
}
StringBuilder sqlStr = new StringBuilder();
Dictionary<string, object> dicParam = new Dictionary<string, object>();
List<string> fieldList = null;
List<object> valueList = null;
try
{
#region 忽略空参数处理
if (!nullIsJoin)
{
fieldList = new List<string>();
valueList = new List<object>();
List<string> fieldListNew = new List<string>(fieldArray);
List<object> valueListNew = new List<object>(valueArray);
for (int i = 0; i < valueListNew.Count; i++)
{
if ((valueListNew[i] != null) && (!string.IsNullOrWhiteSpace(Convert.ToString(valueListNew[i]))))
{
fieldList.Add(fieldListNew[i]);
valueList.Add(valueListNew[i]);
}
}
if ((fieldList == null) || (valueList == null) || (fieldList.Count < 1) || (valueList.Count < 1) || (fieldList.Count != valueList.Count))
{
throw new Exception("字段/值 参数错误。");
}
}
else
{
fieldList = new List<string>(fieldArray);
valueList = new List<object>(valueArray);
}
#endregion
#region 动态产生SQL语句
sqlStr.AppendLine("UPDATE " + tableName);
sqlStr.AppendLine("SET");
for (int f = 0; f < fieldList.Count; f++)
{
if (f == fieldList.Count - 1)
{
sqlStr.AppendLine(fieldList[f] + " = ?" + fieldList[f]);
}
else
{
sqlStr.AppendLine(fieldList[f] + " = ?" + fieldList[f] + ",");
}
}
sqlStr.AppendLine("WHERE 1 = 1");
sqlStr.AppendLine("AND " + pkField + " = ?" + pkField);
#endregion
#region 动态参数赋值
for (int v = 0; v < valueArray.Length; v++)
{
dicParam.Add("?" + fieldArray[v], valueArray[v]);
}
dicParam.Add("?" + pkField, pkValue);
#endregion
#region 执行SQL
row = ExecuteNonQuery(sqlStr.ToString(), dicParam);
#endregion
}
catch (Exception ex)
{
throw new Exception("执行修改通用方法时发生异常[UpdateSqlExe()]。\r\n" + ex.ToString());
}
return row;
}
/// <summary> 修改数据通用方法(修改字段名集合与修改字段值集合必须一一对应;须显式指定主键列/唯一列)
/// </summary>
/// <param name="tableName">数据库表名称</param>
/// <param name="conditionFieldList">条件字段名集合</param>
/// <param name="conditionValueList">条件字段值集合</param>
/// <param name="fieldList">修改字段名集合</param>
/// <param name="valueList">修改字段值集合</param>
/// <param name="nullIsJoin">空值是否参与数据修改。默认 true,空值参与数据修改;false,忽略空值进行数据修改</param>
/// <returns></returns>
public int UpdateSqlExe(string tableName, string pkField, object pkValue, List<string> fieldList, List<object> valueList, bool nullIsJoin = true)
{
int row = -1;
if (string.IsNullOrWhiteSpace(tableName))
{
throw new Exception("表名 参数错误。");
}
if ((string.IsNullOrWhiteSpace(pkField)) || pkValue == null)
{
throw new Exception("主键列/唯一列 参数错误。");
}
if ((fieldList == null) || (valueList == null) || (fieldList.Count < 1) || (valueList.Count < 1) || (fieldList.Count != valueList.Count))
{
throw new Exception("字段/值 参数错误。");
}
StringBuilder sqlStr = new StringBuilder();
Dictionary<string, object> dicParam = new Dictionary<string, object>();
try
{
#region 忽略空参数处理
if (!nullIsJoin)
{
List<string> fieldListNew = new List<string>();
List<object> valueListNew = new List<object>();
fieldListNew = fieldList;
valueListNew = valueList;
fieldList.Clear();
valueList.Clear();
for (int i = 0; i < valueListNew.Count; i++)
{
if ((valueListNew[i] != null) && (!string.IsNullOrWhiteSpace(Convert.ToString(valueListNew[i]))))
{
fieldList.Add(fieldListNew[i]);
valueList.Add(valueListNew[i]);
}
}
if ((fieldList == null) || (valueList == null) || (fieldList.Count < 1) || (valueList.Count < 1) || (fieldList.Count != valueList.Count))
{
throw new Exception("字段/值 参数错误。");
}
}
#endregion
#region 动态产生SQL语句
sqlStr.AppendLine("UPDATE " + tableName);
sqlStr.AppendLine("SET");
for (int f = 0; f < fieldList.Count; f++)
{
if (f == fieldList.Count - 1)
{
sqlStr.AppendLine(fieldList[f] + " = ?" + fieldList[f]);
}
else
{
sqlStr.AppendLine(fieldList[f] + " = ?" + fieldList[f] + ",");
}
}
sqlStr.AppendLine("WHERE 1 = 1");
sqlStr.AppendLine("AND " + pkField + " = ?" + pkField);
#endregion
#region 动态参数赋值
for (int v = 0; v < valueList.Count; v++)
{
dicParam.Add("?" + fieldList[v], valueList[v]);
}
dicParam.Add("?" + pkField, pkValue);
#endregion
#region 执行SQL
row = ExecuteNonQuery(sqlStr.ToString(), dicParam);
#endregion
}
catch (Exception ex)
{
throw new Exception("执行修改通用方法时发生异常[UpdateSqlExe()]。\r\n" + ex.ToString());
}
return row;
}
/// <summary> 修改数据通用方法(条件字段名数组与条件字段值数组必须一一对应;修改字段名数组与修改字段值数组必须一一对应)
/// <para>注:条件字段与条件值目前只支持“=”等号运算符(其他暂不考虑,自行写SQL语句)</para>
/// </summary>
/// <param name="tableName">数据库表名称</param>
/// <param name="conditionField">条件字段名数组</param>
/// <param name="conditionValue">条件字段值数组</param>
/// <param name="fieldArray">修改字段名数组</param>
/// <param name="valueArray">修改字段值数组</param>
/// <param name="nullIsJoin">空值是否参与数据修改,默认true,false忽略空值进行数据修改</param>
/// <returns></returns>
public int UpdateSqlExe(string tableName, string[] conditionField, object[] conditionValue, string[] fieldArray, object[] valueArray, bool nullIsJoin = true)
{
int row = -1;
if (string.IsNullOrWhiteSpace(tableName))
{
throw new Exception("表名 参数错误。");
}
if ((conditionField == null) || (conditionValue == null) || (conditionField.Length < 1) || (conditionValue.Length < 1) || (conditionField.Length != conditionValue.Length))
{
throw new Exception("条件 的 字段/值 参数错误。");
}
if ((fieldArray == null) || (valueArray == null) || (fieldArray.Length < 1) || (valueArray.Length < 1) || (fieldArray.Length != valueArray.Length))
{
throw new Exception("字段/值 参数错误。");
}
StringBuilder sqlStr = new StringBuilder();
Dictionary<string, object> dicParam = new Dictionary<string, object>();
List<string> fieldList = null;
List<object> valueList = null;
try
{
#region 忽略空参数处理
if (!nullIsJoin)
{
fieldList = new List<string>();
valueList = new List<object>();
List<string> fieldListNew = new List<string>(fieldArray);
List<object> valueListNew = new List<object>(valueArray);
for (int i = 0; i < valueListNew.Count; i++)
{
if ((valueListNew[i] != null) && (!string.IsNullOrWhiteSpace(Convert.ToString(valueListNew[i]))))
{
fieldList.Add(fieldListNew[i]);
valueList.Add(valueListNew[i]);
}
}
if ((fieldList == null) || (valueList == null) || (fieldList.Count < 1) || (valueList.Count < 1) || (fieldList.Count != valueList.Count))
{
throw new Exception("字段/值 参数错误。");
}
}
else
{
fieldList = new List<string>(fieldArray);
valueList = new List<object>(valueArray);
}
#endregion
#region 动态产生SQL语句
sqlStr.AppendLine("UPDATE " + tableName);
sqlStr.AppendLine("SET");
for (int f = 0; f < fieldList.Count; f++)
{
if (f == fieldList.Count - 1)
{
sqlStr.AppendLine(fieldList[f] + " = ?" + fieldList[f]);
}
else
{
sqlStr.AppendLine(fieldList[f] + " = ?" + fieldList[f] + ",");
}
}
sqlStr.AppendLine("WHERE 1 = 1");
for (int c = 0; c < conditionField.Length; c++)
{
sqlStr.AppendLine("AND " + conditionField[c] + " = ?" + conditionField[c]);
}
#endregion
#region 动态参数赋值
for (int v = 0; v < valueList.Count; v++)
{
dicParam.Add("?" + fieldList[v], valueList[v]);
}
for (int cv = 0; cv < conditionValue.Length; cv++)
{
dicParam.Add("?" + conditionField[cv], conditionValue[cv]);
}
#endregion
#region 执行SQL
row = ExecuteNonQuery(sqlStr.ToString(), dicParam);
#endregion
}
catch (Exception ex)
{
throw new Exception("执行修改通用方法时发生异常[UpdateSqlExe()]。\r\n" + ex.ToString());
}
return row;
}
/// <summary> 修改数据通用方法(条件字段名集合与条件字段值集合必须一一对应,修改字段名集合与修改字段值集合必须一一对应)
/// <para>注:条件字段与条件值目前只支持“=”等号运算符(其他暂不考虑,自行写SQL语句)</para>
/// </summary>
/// <param name="tableName">数据库表名称</param>
/// <param name="conditionFieldList">条件字段名集合</param>
/// <param name="conditionValueList">条件字段值集合</param>
/// <param name="fieldList">修改字段名集合</param>
/// <param name="valueList">修改字段值集合</param>
/// <param name="nullIsJoin">空值是否参与数据修改。默认 true,空值参与数据修改;false,忽略空值进行数据修改</param>
/// <returns></returns>
public int UpdateSqlExe(string tableName, List<string> conditionFieldList, List<object> conditionValueList, List<string> fieldList, List<object> valueList, bool nullIsJoin = true)
{
int row = -1;
if (string.IsNullOrWhiteSpace(tableName))
{
throw new Exception("表名 参数错误。");
}
if ((conditionFieldList == null) || (conditionValueList == null) || (conditionFieldList.Count < 1) || (conditionValueList.Count < 1) || (conditionFieldList.Count != conditionValueList.Count))
{
throw new Exception("条件 字段/值 参数错误。");
}
if ((fieldList == null) || (valueList == null) || (fieldList.Count < 1) || (valueList.Count < 1) || (fieldList.Count != valueList.Count))
{
throw new Exception("字段/值 参数错误。");
}
StringBuilder sqlStr = new StringBuilder();
Dictionary<string, object> dicParam = new Dictionary<string, object>();
try
{
#region 忽略空参数处理
if (!nullIsJoin)
{
List<string> fieldListNew = new List<string>();
List<object> valueListNew = new List<object>();
fieldListNew = fieldList;
valueListNew = valueList;
fieldList.Clear();
valueList.Clear();
for (int i = 0; i < valueListNew.Count; i++)
{
if ((valueListNew[i] != null) && (!string.IsNullOrWhiteSpace(Convert.ToString(valueListNew[i]))))
{
fieldList.Add(fieldListNew[i]);
valueList.Add(valueListNew[i]);
}
}
if ((fieldList == null) || (valueList == null) || (fieldList.Count < 1) || (valueList.Count < 1) || (fieldList.Count != valueList.Count))
{
throw new Exception("字段/值 参数错误。");
}
}
#endregion
#region 动态产生SQL语句
sqlStr.AppendLine("UPDATE " + tableName);
sqlStr.AppendLine("SET");
for (int f = 0; f < fieldList.Count; f++)
{
if (f == fieldList.Count - 1)
{
sqlStr.AppendLine(fieldList[f] + " = ?" + fieldList[f]);
}
else
{
sqlStr.AppendLine(fieldList[f] + " = ?" + fieldList[f] + ",");
}
}
sqlStr.AppendLine("WHERE 1 = 1");
for (int c = 0; c < conditionFieldList.Count; c++)
{
sqlStr.AppendLine("AND " + conditionFieldList[c] + " = ?" + conditionFieldList[c]);
}
#endregion
#region 动态参数赋值
for (int v = 0; v < valueList.Count; v++)
{
dicParam.Add("?" + fieldList[v], valueList[v]);
}
for (int cv = 0; cv < conditionValueList.Count; cv++)
{
dicParam.Add("?" + conditionFieldList[cv], conditionValueList[cv]);
}
#endregion
#region 执行SQL
row = ExecuteNonQuery(sqlStr.ToString(), dicParam);
#endregion
}
catch (Exception ex)
{
throw new Exception("执行修改通用方法时发生异常[UpdateSqlExe()]。\r\n" + ex.ToString());
}
return row;
}
/// <summary> 删除数据通用方法(须显式指定主键列/唯一列)
/// </summary>
/// <param name="tableName">数据库表名称</param>
/// <param name="pkField">主键列字段名</param>
/// <param name="pkValue">主键列字段值</param>
/// <returns></returns>
public int DeleteSqlExe(string tableName, string pkField, object pkValue)
{
int row = -1;
if (string.IsNullOrWhiteSpace(tableName))
{
throw new Exception("表名 参数错误。");
}
if ((string.IsNullOrWhiteSpace(pkField)) || pkValue == null)
{
throw new Exception("主键列 参数错误。");
}
StringBuilder sqlStr = new StringBuilder();
Dictionary<string, object> dicParam = new Dictionary<string, object>();
try
{
#region 动态产生SQL语句
sqlStr.AppendLine("DELETE FROM " + tableName);
sqlStr.AppendLine("WHERE " + pkField + " = ?" + pkField);
#endregion
#region 动态参数赋值
dicParam.Add("?" + pkField, pkValue);
#endregion
#region 执行SQL
row = ExecuteNonQuery(sqlStr.ToString(), dicParam);
#endregion
}
catch (Exception ex)
{
throw new Exception("执行删除通用方法时发生异常[DeleteSqlExe()]。\r\n" + ex.ToString());
}
return row;
}
/// <summary> 删除数据通用方法(条件字段名数组与条件字段值数组必须一一对应)
/// </summary>
/// <param name="tableName">数据库表名称</param>
/// <param name="conditionFieldList">条件字段名数组</param>
/// <param name="conditionValueList">条件字段值数组</param>
/// <returns></returns>
public int DeleteSqlExe(string tableName, string[] conditionField, object[] conditionValue)
{
int row = -1;
if (string.IsNullOrWhiteSpace(tableName))
{
throw new Exception("表名 参数错误。");
}
if ((conditionField == null) || (conditionValue == null) || (conditionField.Length < 1) || (conditionValue.Length < 1) || (conditionField.Length != conditionValue.Length))
{
throw new Exception("条件 字段/值 参数错误。");
}
StringBuilder sqlStr = new StringBuilder();
Dictionary<string, object> dicParam = new Dictionary<string, object>();
try
{
#region 动态产生SQL语句
sqlStr.AppendLine("DELETE FROM " + tableName);
sqlStr.AppendLine("WHERE 1 = 1");
for (int cf = 0; cf < conditionField.Length; cf++)
{
sqlStr.AppendLine("AND " + conditionField[cf] + " = ?" + conditionField[cf]);
}
#endregion
#region 动态参数赋值
for (int vf = 0; vf < conditionValue.Length; vf++)
{
dicParam.Add("?" + conditionValue[vf], conditionValue[vf]);
}
#endregion
#region 执行SQL
row = ExecuteNonQuery(sqlStr.ToString(), dicParam);
#endregion
}
catch (Exception ex)
{
throw new Exception("执行删除通用方法时发生异常[DeleteSqlExe()]。\r\n" + ex.ToString());
}
return row;
}
/// <summary> 删除数据通用方法(条件字段名集合与条件字段值集合必须一一对应)
/// </summary>
/// <param name="tableName">数据库表名称</param>
/// <param name="conditionFieldList">条件字段名集合</param>
/// <param name="conditionValueList">条件字段值集合</param>
/// <returns></returns>
public int DeleteSqlExe(string tableName, List<string> conditionFieldList, List<object> conditionValueList)
{
int row = -1;
if (string.IsNullOrWhiteSpace(tableName))
{
throw new Exception("表名 参数错误。");
}
if ((conditionFieldList == null) || (conditionValueList == null) || (conditionFieldList.Count < 1) || (conditionValueList.Count < 1) || (conditionFieldList.Count != conditionValueList.Count))
{
throw new Exception("条件 字段/值 参数错误。");
}
StringBuilder sqlStr = new StringBuilder();
Dictionary<string, object> dicParam = new Dictionary<string, object>();
try
{
#region 动态产生SQL语句
sqlStr.AppendLine("DELETE FROM " + tableName);
sqlStr.AppendLine("WHERE 1 = 1");
for (int cf = 0; cf < conditionFieldList.Count; cf++)
{
sqlStr.AppendLine("AND " + conditionFieldList[cf] + " = ?" + conditionFieldList[cf]);
}
#endregion
#region 动态参数赋值
for (int vf = 0; vf < conditionValueList.Count; vf++)
{
dicParam.Add("?" + conditionFieldList[vf], conditionValueList[vf]);
}
#endregion
#region 执行SQL
row = ExecuteNonQuery(sqlStr.ToString(), dicParam);
#endregion
}
catch (Exception ex)
{
throw new Exception("执行删除通用方法时发生异常[DeleteSqlExe()]。\r\n" + ex.ToString());
}
return row;
}
}
接下来我们配置启动时代码 Global.asax :
public class WebApiApplication : HttpApplication
{
protected void Application_Start()
{
GlobalConfiguration.Configure(WebApiConfig.Register);
//启动时读取数据库连接信息配置文件(MySqlConnectionConfig.xml)
try
{
MySqlDBConnectInfo dbConnectInfo = StartupInitMySqlConfig.DbConnectInfo;
if (dbConnectInfo == null)
{
throw new Exception("系统获取不到数据库连接信息。");
}
}
catch (Exception ex)
{
LogTool.WriteErrorLog("【启动异常】从配置文件中获取 MySql 连接信息,发生异常", ex.ToString());
}
}
}
MySqlConnectionConfig.xml 配置文件:
<?xml version="1.0" encoding="utf-8"?>
<ArrayOfMySqlDBConnectInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<!-- 一般把程序默认的连接对象放在第一位 -->
<MySqlDBConnectInfo>
<!-- #唯一别名(用来区分是哪个链接,推荐策略:服务地址_数据库名) -->
<UniqueAlias>185.**.36.12-DBName</UniqueAlias>
<!-- ############################################################################################################ -->
<!-- #数据库服务连接地址,例:"127.0.0.1" -->
<Server>185.**.36.12</Server>
<!-- #数据库服务连接端口号 -->
<Port>3306</Port>
<!-- #连接的数据库 -->
<Database>DBName</Database>
<!-- #连接数据库的用户名 -->
<UserID>UserName</UserID>
<!-- #用户密码 -->
<Password>UserPwd</Password>
<!-- ############################################################################################################ -->
<!-- #设置字符编码,例:"utf8" -->
<CharacterSet>utf8</CharacterSet>
<!-- #连接超时时间,单位秒 -->
<ConnectionTimeout>60</ConnectionTimeout>
<!-- #数据库执行超时时间,单位秒 -->
<DefaultCommandTimeout>60</DefaultCommandTimeout>
<!-- #是否开启连接池,true -->
<Pooling>true</Pooling>
<!-- #连接池中最小连接数 -->
<MinimumPoolSize>20</MinimumPoolSize>
<!-- #连接池中最大连接数 -->
<MaximumPoolSize>50</MaximumPoolSize>
<!-- #连接池中连接对象存活时间,单位秒 -->
<ConnectionLifeTime>1200</ConnectionLifeTime>
<!-- #连接是否使用压缩,true -->
<UseCompression>true</UseCompression>
<!-- #表示连接池程序是否会自动登记创建线程的当前事务语境中的连接,ture -->
<AutoEnlist>true</AutoEnlist>
</MySqlDBConnectInfo>
</ArrayOfMySqlDBConnectInfo>
上面代码中用到的 JsonTool 和 XmlTool 都在我之前的博文中。
下面给一个 MySqlTool 的使用例子:
希望给你带来参考和帮助。