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<string,object> 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 }