欢迎光临
我的个人博客网站

ADO.NET 操作MySQL的辅助类

ADO.NET 操作MySQL

  1 using MySql.Data.MySqlClient;   2    3 using System;   4 using System.Collections.Generic;   5 using System.Data;   6 using System.Linq;   7 using System.Text;   8 using System.Threading.Tasks;   9   10 namespace DAL  11 {  12     /// <summary>  13     /// 数据库连接,可通过begin开启事务  14     /// <example>参数标识符采用@符号,in语句如下  15     /// <code>  16     /// sql="select * from dual where id in (@list)";</code>  17     /// <code>Dictionary&lt;string,object&gt; dic={{"list",new int[]{1,2,3}}};</code>  18     /// </example>  19     /// </summary>  20     public class DbConnect : IDisposable, IAsyncDisposable  21     {  22         /// <summary>  23         /// 连接实例  24         /// </summary>  25         public MySqlConnection Connection;  26   27         /// <summary>  28         /// IEnumerable参数转换为字典  29         /// </summary>  30         /// <param name="paramName">参数名</param>  31         /// <param name="param">参数值</param>  32         /// <returns></returns>  33         public static Dictionary<string, object> paramsToDictionary(IEnumerable<string> paramName, IEnumerable<object> param)  34         {  35             if (paramName.Count() != param.Count())  36             {  37                 throw new ArgumentException("参数名称与参数列表数量不一致");  38             }  39             Dictionary<string, object> paramDict = new Dictionary<string, object>();  40             var paramNameEnumerator = paramName.GetEnumerator();  41             var paramEnumerator = param.GetEnumerator();  42             while (paramNameEnumerator.MoveNext())  43             {  44                 paramEnumerator.MoveNext();  45                 paramDict[paramNameEnumerator.Current] = paramEnumerator.Current;  46             }  47             return paramDict;  48         }  49   50         /// <summary>  51         /// 按照SQL和参数字典生成Command对象  52         /// </summary>  53         /// <param name="sql"></param>  54         /// <param name="paramDict"></param>  55         /// <returns></returns>  56         public MySqlCommand GenerateCmd(string sql, Dictionary<string, object> paramDict)  57         {  58             using MySqlCommand cmd = new MySqlCommand(sql, Connection);  59             foreach (var item in paramDict)  60             {  61                 if (item.Value is not string && item.Value is System.Collections.IEnumerable InList)  62                 {  63                     var enumerator = InList.GetEnumerator();  64                     StringBuilder paramNameBuilder = new StringBuilder();  65                     int count = 0;  66                     while (enumerator.MoveNext())  67                     {  68                         string paramName = $"{item.Key}_{count:0000}";  69                         paramNameBuilder.Append(" @");  70                         paramNameBuilder.Append(paramName);  71                         paramNameBuilder.Append(",");  72                         cmd.Parameters.AddWithValue(paramName, enumerator.Current);  73                         count++;  74                     }  75                     if (count == 0)  76                     {  77                         cmd.Parameters.AddWithValue(item.Key, item.Value);  78                     }  79                     else  80                     {  81                         cmd.CommandText = cmd.CommandText  82                         //.Replace("(?" + item.Key + ")", "(" + paramNameBuilder.ToString().TrimEnd(',') + ")")  83                         .Replace("(@" + item.Key + ")", "(" + paramNameBuilder.ToString().TrimEnd(',') + ")");  84                     }  85                 }  86                 else  87                 {  88                     cmd.Parameters.AddWithValue(item.Key, item.Value);  89                 }  90             }  91             return cmd;  92         }  93   94         #region 同步调用相关方法  95         /// <summary>  96         /// 按照指定的连接字符串创建连接  97         /// </summary>  98         /// <param name="connectionString">连接字符串</param>  99         public DbConnect(string connectionString) 100         { 101             Connection = new MySqlConnection(connectionString); 102             Connection.Open(); 103         } 104  105         /// <inheritdoc cref="ExecRowCount(string, Dictionary{string, object})"/> 106         /// <param name="sql">SQL语句</param> 107         public long ExecRowCount(string sql) => ExecRowCount(sql, new Dictionary<string, object>()); 108  109         /// <inheritdoc cref="ExecRowCount(string, Dictionary{string, object})"/> 110         /// <param name="sql">SQL语句</param> 111         /// <param name="paramName">参数名称</param> 112         /// <param name="param">参数值</param> 113         public long ExecRowCount(string sql, IEnumerable<string> paramName, IEnumerable<object> param) => ExecRowCount(sql, paramsToDictionary(paramName, param)); 114  115         /// <summary> 116         /// 查询行数 117         /// </summary> 118         /// <param name="sql">SQL语句</param> 119         /// <param name="paramDict">参数字典</param> 120         /// <returns>行数</returns> 121         public long ExecRowCount(string sql, Dictionary<string, object> paramDict) 122         { 123             try 124             { 125                 using MySqlCommand cmd = GenerateCmd($@"select count(*) from ({sql}) countTable", paramDict); 126                 return (long)cmd.ExecuteScalar(); 127             } 128             catch (Exception e) 129             { 130                 Console.WriteLine("SQL错误" + sql + e.Message); 131                 throw; 132             } 133         } 134  135         /// <inheritdoc cref="ExecSQL(string, Dictionary{string, object})"/> 136         /// <param name="sql">SQL语句</param> 137         public DataTable ExecSQL(string sql) => ExecSQL(sql, new Dictionary<string, object>()); 138  139         /// <inheritdoc cref="ExecSQL(string, Dictionary{string, object})"/> 140         /// <param name="sql">SQL语句</param> 141         /// <param name="paramName">参数名称</param> 142         /// <param name="param">参数值</param> 143         public DataTable ExecSQL(string sql, IEnumerable<string> paramName, IEnumerable<object> param) => ExecSQL(sql, paramsToDictionary(paramName, param)); 144  145         /// <summary> 146         /// 查询数据 147         /// </summary> 148         /// <param name="sql">SQL语句</param> 149         /// <param name="paramDict">参数字典</param> 150         /// <returns>结果表</returns> 151         public DataTable ExecSQL(string sql, Dictionary<string, object> paramDict) 152         { 153             try 154             { 155                 using MySqlCommand cmd = GenerateCmd(sql, paramDict); 156                 cmd.CommandTimeout = 60; 157                 DataSet dataSet = new DataSet(); 158                 dataSet.EnforceConstraints = false; 159                 dataSet.Tables.Add(new DataTable()); 160                 dataSet.Tables[0].Load(cmd.ExecuteReader()); 161                 return dataSet.Tables[0]; 162             } 163             catch (Exception e) 164             { 165                 Console.WriteLine("SQL错误" + sql + e.Message); 166                 throw; 167             } 168         } 169  170         /// <inheritdoc cref="ExecCmd(string, Dictionary{string, object})"/> 171         public int ExecCmd(string sql) => ExecCmd(sql, new Dictionary<string, object>()); 172  173         /// <inheritdoc cref="ExecCmd(string, Dictionary{string, object})"/> 174         /// <param name="sql">SQL语句</param> 175         /// <param name="paramName">参数名称</param> 176         /// <param name="param">参数值</param> 177         public int ExecCmd(string sql, IEnumerable<string> paramName, IEnumerable<object> param) => ExecCmd(sql, paramsToDictionary(paramName, param)); 178  179         /// <summary> 180         /// 执行命令 181         /// </summary> 182         /// <param name="sql">SQL语句</param> 183         /// <param name="paramDict">参数字典</param> 184         /// <returns>修改行数</returns> 185         public int ExecCmd(string sql, Dictionary<string, object> paramDict) 186         { 187             try 188             { 189                 using MySqlCommand cmd = GenerateCmd(sql, paramDict); 190                 return cmd.ExecuteNonQuery(); 191             } 192             catch (Exception e) 193             { 194                 Console.WriteLine("SQL错误" + sql + e.Message); 195                 throw; 196             } 197         } 198  199         /// <summary> 200         /// 释放对象 201         /// </summary> 202         public void Dispose() => Connection.Dispose(); 203         #endregion 204  205         #region 异步调用相关方法 206         /// <summary> 207         /// 构造无连接实例,之后采用Open异步打开数据库连接 208         /// </summary> 209         public DbConnect() 210         { 211         } 212  213         /// <summary> 214         /// 异步打开连接 215         /// </summary> 216         /// <param name="connectionString">连接字符串</param> 217         public async Task<DbConnect> Open(string connectionString) 218         { 219             Connection = new MySqlConnection(connectionString); 220             await Connection.OpenAsync(); 221             return this; 222         } 223  224         /// <inheritdoc cref="ExecRowCountAsync(string, Dictionary{string, object})"/> 225         public async Task<long> ExecRowCountAsync(string sql) => await ExecRowCountAsync(sql, new Dictionary<string, object>()); 226  227         /// <inheritdoc cref="ExecRowCountAsync(string, Dictionary{string, object})"/> 228         /// <param name="sql">SQL语句</param> 229         /// <param name="paramName">参数名称</param> 230         /// <param name="param">参数值</param> 231         public async Task<long> ExecRowCountAsync(string sql, IEnumerable<string> paramName, IEnumerable<object> param) => await ExecRowCountAsync(sql, paramsToDictionary(paramName, param)); 232  233         /// <summary> 234         /// 异步查询行数 235         /// </summary> 236         /// <param name="sql">SQL语句</param> 237         /// <param name="paramDict">参数字典</param> 238         /// <returns>行数</returns> 239         public async Task<long> ExecRowCountAsync(string sql, Dictionary<string, object> paramDict) 240         { 241             try 242             { 243                 using MySqlCommand cmd = GenerateCmd($@"select count(*) from ({sql}) countTable", paramDict); 244                 var result = await cmd.ExecuteScalarAsync(); 245                 return (long)result; 246             } 247             catch (Exception e) 248             { 249                 Console.WriteLine("SQL错误" + sql + e.Message); 250                 throw; 251             } 252         } 253  254         /// <inheritdoc cref="ExecSQLAsync(string, Dictionary{string, object})"/> 255         public async Task<DataTable> ExecSQLAsync(string sql) => await ExecSQLAsync(sql, new Dictionary<string, object>()); 256  257         /// <inheritdoc cref="ExecSQLAsync(string, Dictionary{string, object})"/> 258         /// <param name="sql">SQL语句</param> 259         /// <param name="paramName">参数名称</param> 260         /// <param name="param">参数值</param> 261         public async Task<DataTable> ExecSQLAsync(string sql, IEnumerable<string> paramName, IEnumerable<object> param) => await ExecSQLAsync(sql, paramsToDictionary(paramName, param)); 262  263         /// <summary> 264         /// 异步查询数据 265         /// </summary> 266         /// <param name="sql">SQL语句</param> 267         /// <param name="paramDict">参数字典</param> 268         /// <returns>结果表</returns> 269         public async Task<DataTable> ExecSQLAsync(string sql, Dictionary<string, object> paramDict) 270         { 271             try 272             { 273                 using MySqlCommand cmd = GenerateCmd(sql, paramDict); 274                 cmd.CommandTimeout = 60; 275                 DataSet dataSet = new DataSet(); 276                 dataSet.EnforceConstraints = false; 277                 dataSet.Tables.Add(new DataTable()); 278                 dataSet.Tables[0].Load(await cmd.ExecuteReaderAsync()); 279                 return dataSet.Tables[0]; 280             } 281             catch (Exception e) 282             { 283                 Console.WriteLine("SQL错误" + sql + e.Message); 284                 throw; 285             } 286         } 287  288         /// <inheritdoc cref="ExecCmdAsync(string, Dictionary{string, object})"/> 289         public async Task<int> ExecCmdAsync(string sql) => await ExecCmdAsync(sql, new Dictionary<string, object>()); 290  291         /// <inheritdoc cref="ExecCmdAsync(string, Dictionary{string, object})"/> 292         /// <param name="sql">SQL语句</param> 293         /// <param name="paramName">参数名称</param> 294         /// <param name="param">参数值</param> 295         public async Task<int> ExecCmdAsync(string sql, IEnumerable<string> paramName, IEnumerable<object> param) => await ExecCmdAsync(sql, paramsToDictionary(paramName, param)); 296  297         /// <summary> 298         /// 异步执行命令 299         /// </summary> 300         /// <param name="sql">SQL语句</param> 301         /// <param name="paramDict">参数字典</param> 302         /// <returns>修改行数</returns> 303         public async Task<int> ExecCmdAsync(string sql, Dictionary<string, object> paramDict) 304         { 305             try 306             { 307                 using MySqlCommand cmd = GenerateCmd(sql, paramDict); 308                 return await cmd.ExecuteNonQueryAsync(); 309             } 310             catch (Exception e) 311             { 312                 Console.WriteLine("SQL错误" + sql + e.Message); 313                 throw; 314             } 315         } 316  317         /// <summary> 318         /// 异步释放对象 319         /// </summary> 320         /// <returns></returns> 321         public ValueTask DisposeAsync() => Connection.DisposeAsync(); 322         #endregion 323     } 324 }

 

赞(0) 打赏
未经允许不得转载:张拓的天空 » ADO.NET 操作MySQL的辅助类
分享到: 更多 (0)

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

专业的IT技术经验分享 更专业 更方便

联系我们本站主机

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏