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 }