1 /// <summary>
2 /// MySqlHelper操作类
3 /// </summary>
4 public sealed partial class MySQLHelper
5 {
6 public static readonly string DBConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings[""].ToString();
7 /// <summary>
8 /// 批量操作每批次记录数
9 /// </summary>
10 public static int BatchSize = 2000;
11
12 /// <summary>
13 /// 超时时间
14 /// </summary>
15 public static int CommandTimeOut = 600;
16
17 /// <summary>
18 ///初始化MySqlHelper实例
19 /// </summary>
20 /// <param name="connectionString">数据库连接字符串</param>
21 public MySQLHelper(string connectionString)
22 {
23 this.ConnectionString = connectionString;
24 }
25
26 /// <summary>
27 /// 数据库连接字符串
28 /// </summary>
29 public string ConnectionString { get; set; }
30
31 #region 实例方法
32
33 #region ExecuteNonQuery
34
35 /// <summary>
36 /// 执行SQL语句,返回影响的行数
37 /// </summary>
38 /// <param name="commandText">SQL语句</param>
39 /// <param name="parms">查询参数</param>
40 /// <returns>返回影响的行数</returns>
41 public int ExecuteNonQuery(string commandText, params MySqlParameter[] parms)
42 {
43 return ExecuteNonQuery(ConnectionString, CommandType.Text, commandText, parms);
44 }
45
46 /// <summary>
47 /// 执行SQL语句,返回影响的行数
48 /// </summary>
49 /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
50 /// <param name="commandText">SQL语句或存储过程名称</param>
51 /// <param name="parms">查询参数</param>
52 /// <returns>返回影响的行数</returns>
53 public int ExecuteNonQuery(CommandType commandType, string commandText, params MySqlParameter[] parms)
54 {
55 return ExecuteNonQuery(ConnectionString, commandType, commandText, parms);
56 }
57
58 #endregion ExecuteNonQuery
59
60 #region ExecuteScalar
61
62 /// <summary>
63 /// 执行SQL语句,返回结果集中的第一行第一列
64 /// </summary>
65 /// <typeparam name="T">返回对象类型</typeparam>
66 /// <param name="commandText">SQL语句</param>
67 /// <param name="parms">查询参数</param>
68 /// <returns>返回结果集中的第一行第一列</returns>
69 public T ExecuteScalar<T>(string commandText, params MySqlParameter[] parms)
70 {
71 return ExecuteScalar<T>(ConnectionString, commandText, parms);
72 }
73
74 /// <summary>
75 /// 执行SQL语句,返回结果集中的第一行第一列
76 /// </summary>
77 /// <param name="commandText">SQL语句</param>
78 /// <param name="parms">查询参数</param>
79 /// <returns>返回结果集中的第一行第一列</returns>
80 public object ExecuteScalar(string commandText, params MySqlParameter[] parms)
81 {
82 return ExecuteScalar(ConnectionString, CommandType.Text, commandText, parms);
83 }
84
85 /// <summary>
86 /// 执行SQL语句,返回结果集中的第一行第一列
87 /// </summary>
88 /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
89 /// <param name="commandText">SQL语句或存储过程名称</param>
90 /// <param name="parms">查询参数</param>
91 /// <returns>返回结果集中的第一行第一列</returns>
92 public object ExecuteScalar(CommandType commandType, string commandText, params MySqlParameter[] parms)
93 {
94 return ExecuteScalar(ConnectionString, commandType, commandText, parms);
95 }
96
97 #endregion ExecuteScalar
98
99 #region ExecuteDataReader
100
101 /// <summary>
102 /// 执行SQL语句,返回只读数据集
103 /// </summary>
104 /// <param name="commandText">SQL语句</param>
105 /// <param name="parms">查询参数</param>
106 /// <returns>返回只读数据集</returns>
107 private MySqlDataReader ExecuteDataReader(string commandText, params MySqlParameter[] parms)
108 {
109 return ExecuteDataReader(ConnectionString, CommandType.Text, commandText, parms);
110 }
111
112 /// <summary>
113 /// 执行SQL语句,返回只读数据集
114 /// </summary>
115 /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
116 /// <param name="commandText">SQL语句或存储过程名称</param>
117 /// <param name="parms">查询参数</param>
118 /// <returns>返回只读数据集</returns>
119 private MySqlDataReader ExecuteDataReader(CommandType commandType, string commandText, params MySqlParameter[] parms)
120 {
121 return ExecuteDataReader(ConnectionString, commandType, commandText, parms);
122 }
123 #endregion
124
125 #region ExecuteDataRow
126
127 /// <summary>
128 /// 执行SQL语句,返回结果集中的第一行
129 /// </summary>
130 /// <param name="commandText">SQL语句</param>
131 /// <param name="parms">查询参数</param>
132 /// <returns>返回结果集中的第一行</returns>
133 public DataRow ExecuteDataRow(string commandText, params MySqlParameter[] parms)
134 {
135 return ExecuteDataRow(ConnectionString, CommandType.Text, commandText, parms);
136 }
137
138 /// <summary>
139 /// 执行SQL语句,返回结果集中的第一行
140 /// </summary>
141 /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
142 /// <param name="commandText">SQL语句或存储过程名称</param>
143 /// <param name="parms">查询参数</param>
144 /// <returns>返回结果集中的第一行</returns>
145 public DataRow ExecuteDataRow(CommandType commandType, string commandText, params MySqlParameter[] parms)
146 {
147 return ExecuteDataRow(ConnectionString, commandType, commandText, parms);
148 }
149
150 #endregion ExecuteDataRow
151
152 #region ExecuteDataTable
153
154 /// <summary>
155 /// 执行SQL语句,返回结果集中的第一个数据表
156 /// </summary>
157 /// <param name="commandText">SQL语句</param>
158 /// <param name="parms">查询参数</param>
159 /// <returns>返回结果集中的第一个数据表</returns>
160 public DataTable ExecuteDataTable(string commandText, params MySqlParameter[] parms)
161 {
162 return ExecuteDataTable(ConnectionString, CommandType.Text, commandText, parms);
163 }
164
165 /// <summary>
166 /// 执行SQL语句,返回结果集中的第一个数据表
167 /// </summary>
168 /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
169 /// <param name="commandText">SQL语句或存储过程名称</param>
170 /// <param name="parms">查询参数</param>
171 /// <returns>返回结果集中的第一个数据表</returns>
172 public DataTable ExecuteDataTable(CommandType commandType, string commandText, params MySqlParameter[] parms)
173 {
174 return ExecuteDataSet(ConnectionString, commandType, commandText, parms).Tables[0];
175 }
176
177 #endregion ExecuteDataTable
178
179 #region ExecuteDataSet
180
181 /// <summary>
182 /// 执行SQL语句,返回结果集
183 /// </summary>
184 /// <param name="commandText">SQL语句</param>
185 /// <param name="parms">查询参数</param>
186 /// <returns>返回结果集</returns>
187 public DataSet ExecuteDataSet(string commandText, params MySqlParameter[] parms)
188 {
189 return ExecuteDataSet(ConnectionString, CommandType.Text, commandText, parms);
190 }
191
192 /// <summary>
193 /// 执行SQL语句,返回结果集
194 /// </summary>
195 /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
196 /// <param name="commandText">SQL语句或存储过程名称</param>
197 /// <param name="parms">查询参数</param>
198 /// <returns>返回结果集</returns>
199 public DataSet ExecuteDataSet(CommandType commandType, string commandText, params MySqlParameter[] parms)
200 {
201 return ExecuteDataSet(ConnectionString, commandType, commandText, parms);
202 }
203
204 #endregion ExecuteDataSet
205
206 #region 批量操作
207
208 /// <summary>
209 /// 使用MySqlDataAdapter批量更新数据
210 /// </summary>
211 /// <param name="table">数据表</param>
212 public void BatchUpdate(DataTable table)
213 {
214 BatchUpdate(ConnectionString, table);
215 }
216
217 /// <summary>
218 ///大批量数据插入,返回成功插入行数
219 /// </summary>
220 /// <param name="table">数据表</param>
221 /// <returns>返回成功插入行数</returns>
222 public int BulkInsert(DataTable table)
223 {
224 return BulkInsert(ConnectionString, table);
225 }
226
227 #endregion 批量操作
228
229 #endregion 实例方法
230
231 #region 静态方法
232
233 private static void PrepareCommand(MySqlCommand command, MySqlConnection connection, MySqlTransaction transaction, CommandType commandType, string commandText, MySqlParameter[] parms)
234 {
235 if (connection.State != ConnectionState.Open) connection.Open();
236
237 command.Connection = connection;
238 command.CommandTimeout = CommandTimeOut;
239 // 设置命令文本(存储过程名或SQL语句)
240 command.CommandText = commandText;
241 // 分配事务
242 if (transaction != null)
243 {
244 command.Transaction = transaction;
245 }
246 // 设置命令类型.
247 command.CommandType = commandType;
248 if (parms != null && parms.Length > 0)
249 {
250 //预处理MySqlParameter参数数组，将为NULL的参数赋值为DBNull.Value;
251 foreach (MySqlParameter parameter in parms)
252 {
253 if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null))
254 {
255 parameter.Value = DBNull.Value;
256 }
257 }
258 command.Parameters.AddRange(parms);
259 }
260 }
261
262 #region ExecuteNonQuery
263
264 /// <summary>
265 /// 执行SQL语句,返回影响的行数
266 /// </summary>
267 /// <param name="connectionString">数据库连接字符串</param>
268 /// <param name="commandText">SQL语句</param>
269 /// <param name="parms">查询参数</param>
270 /// <returns>返回影响的行数</returns>
271 public static int ExecuteNonQuery(string connectionString, string commandText, params MySqlParameter[] parms)
272 {
273 using (MySqlConnection connection = new MySqlConnection(connectionString))
274 {
275 return ExecuteNonQuery(connection, CommandType.Text, commandText, parms);
276 }
277 }
278
279 /// <summary>
280 /// 执行SQL语句,返回影响的行数
281 /// </summary>
282 /// <param name="connectionString">数据库连接字符串</param>
283 /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
284 /// <param name="commandText">SQL语句或存储过程名称</param>
285 /// <param name="parms">查询参数</param>
286 /// <returns>返回影响的行数</returns>
287 public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params MySqlParameter[] parms)
288 {
289 using (MySqlConnection connection = new MySqlConnection(connectionString))
290 {
291 return ExecuteNonQuery(connection, commandType, commandText, parms);
292 }
293 }
294
295 /// <summary>
296 /// 执行SQL语句,返回影响的行数
297 /// </summary>
298 /// <param name="connection">数据库连接</param>
299 /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
300 /// <param name="commandText">SQL语句或存储过程名称</param>
301 /// <param name="parms">查询参数</param>
302 /// <returns>返回影响的行数</returns>
303 public static int ExecuteNonQuery(MySqlConnection connection, CommandType commandType, string commandText, params MySqlParameter[] parms)
304 {
305 return ExecuteNonQuery(connection, null, commandType, commandText, parms);
306 }
307
308 /// <summary>
309 /// 执行SQL语句,返回影响的行数
310 /// </summary>
311 /// <param name="transaction">事务</param>
312 /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
313 /// <param name="commandText">SQL语句或存储过程名称</param>
314 /// <param name="parms">查询参数</param>
315 /// <returns>返回影响的行数</returns>
316 public static int ExecuteNonQuery(MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] parms)
317 {
318 return ExecuteNonQuery(transaction.Connection, transaction, commandType, commandText, parms);
319 }
320
321 /// <summary>
322 /// 执行SQL语句,返回影响的行数
323 /// </summary>
324 /// <param name="connection">数据库连接</param>
325 /// <param name="transaction">事务</param>
326 /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
327 /// <param name="commandText">SQL语句或存储过程名称</param>
328 /// <param name="parms">查询参数</param>
329 /// <returns>返回影响的行数</returns>
330 private static int ExecuteNonQuery(MySqlConnection connection, MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] parms)
331 {
332 MySqlCommand command = new MySqlCommand();
333 PrepareCommand(command, connection, transaction, commandType, commandText, parms);
334 int retval = command.ExecuteNonQuery();
335 command.Parameters.Clear();
336 return retval;
337 }
338
339 #endregion ExecuteNonQuery
340
341 #region ExecuteScalar
342
343 /// <summary>
344 /// 执行SQL语句,返回结果集中的第一行第一列
345 /// </summary>
346 /// <typeparam name="T">返回对象类型</typeparam>
347 /// <param name="connectionString">数据库连接字符串</param>
348 /// <param name="commandText">SQL语句</param>
349 /// <param name="parms">查询参数</param>
350 /// <returns>返回结果集中的第一行第一列</returns>
351 public static T ExecuteScalar<T>(string connectionString, string commandText, params MySqlParameter[] parms)
352 {
353 object result = ExecuteScalar(connectionString, commandText, parms);
354 if (result != null)
355 {
356 return (T)Convert.ChangeType(result, typeof(T)); ;
357 }
358 return default(T);
359 }
360
361 /// <summary>
362 /// 执行SQL语句,返回结果集中的第一行第一列
363 /// </summary>
364 /// <param name="connectionString">数据库连接字符串</param>
365 /// <param name="commandText">SQL语句</param>
366 /// <param name="parms">查询参数</param>
367 /// <returns>返回结果集中的第一行第一列</returns>
368 public static object ExecuteScalar(string connectionString, string commandText, params MySqlParameter[] parms)
369 {
370 using (MySqlConnection connection = new MySqlConnection(connectionString))
371 {
372 return ExecuteScalar(connection, CommandType.Text, commandText, parms);
373 }
374 }
375
376 /// <summary>
377 /// 执行SQL语句,返回结果集中的第一行第一列
378 /// </summary>
379 /// <param name="connectionString">数据库连接字符串</param>
380 /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
381 /// <param name="commandText">SQL语句或存储过程名称</param>
382 /// <param name="parms">查询参数</param>
383 /// <returns>返回结果集中的第一行第一列</returns>
384 public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params MySqlParameter[] parms)
385 {
386 using (MySqlConnection connection = new MySqlConnection(connectionString))
387 {
388 return ExecuteScalar(connection, commandType, commandText, parms);
389 }
390 }
391
392 /// <summary>
393 /// 执行SQL语句,返回结果集中的第一行第一列
394 /// </summary>
395 /// <param name="connection">数据库连接</param>
396 /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
397 /// <param name="commandText">SQL语句或存储过程名称</param>
398 /// <param name="parms">查询参数</param>
399 /// <returns>返回结果集中的第一行第一列</returns>
400 public static object ExecuteScalar(MySqlConnection connection, CommandType commandType, string commandText, params MySqlParameter[] parms)
401 {
402 return ExecuteScalar(connection, null, commandType, commandText, parms);
403 }
404
405 /// <summary>
406 /// 执行SQL语句,返回结果集中的第一行第一列
407 /// </summary>
408 /// <param name="transaction">事务</param>
409 /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
410 /// <param name="commandText">SQL语句或存储过程名称</param>
411 /// <param name="parms">查询参数</param>
412 /// <returns>返回结果集中的第一行第一列</returns>
413 public static object ExecuteScalar(MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] parms)
414 {
415 return ExecuteScalar(transaction.Connection, transaction, commandType, commandText, parms);
416 }
417
418 /// <summary>
419 /// 执行SQL语句,返回结果集中的第一行第一列
420 /// </summary>
421 /// <param name="connection">数据库连接</param>
422 /// <param name="transaction">事务</param>
423 /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
424 /// <param name="commandText">SQL语句或存储过程名称</param>
425 /// <param name="parms">查询参数</param>
426 /// <returns>返回结果集中的第一行第一列</returns>
427 private static object ExecuteScalar(MySqlConnection connection, MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] parms)
428 {
429 MySqlCommand command = new MySqlCommand();
430 PrepareCommand(command, connection, transaction, commandType, commandText, parms);
431 object retval = command.ExecuteScalar();
432 command.Parameters.Clear();
433 return retval;
434 }
435
436 #endregion ExecuteScalar
437
438 #region ExecuteDataReader
439
440 /// <summary>
441 /// 执行SQL语句,返回只读数据集
442 /// </summary>
443 /// <param name="connection">数据库连接</param>
444 /// <param name="commandText">SQL语句</param>
445 /// <param name="parms">查询参数</param>
446 /// <returns>返回只读数据集</returns>
447 private static MySqlDataReader ExecuteDataReader(string connectionString, string commandText, params MySqlParameter[] parms)
448 {
449 MySqlConnection connection = new MySqlConnection(connectionString);
450 return ExecuteDataReader(connection, null, CommandType.Text, commandText, parms);
451 }
452
453 /// <summary>
454 /// 执行SQL语句,返回只读数据集
455 /// </summary>
456 /// <param name="connection">数据库连接</param>
457 /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
458 /// <param name="commandText">SQL语句或存储过程名称</param>
459 /// <param name="parms">查询参数</param>
460 /// <returns>返回只读数据集</returns>
461 private static MySqlDataReader ExecuteDataReader(string connectionString, CommandType commandType, string commandText, params MySqlParameter[] parms)
462 {
463 MySqlConnection connection = new MySqlConnection(connectionString);
464 return ExecuteDataReader(connection, null, commandType, commandText, parms);
465 }
466
467 /// <summary>
468 /// 执行SQL语句,返回只读数据集
469 /// </summary>
470 /// <param name="connection">数据库连接</param>
471 /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
472 /// <param name="commandText">SQL语句或存储过程名称</param>
473 /// <param name="parms">查询参数</param>
474 /// <returns>返回只读数据集</returns>
475 private static MySqlDataReader ExecuteDataReader(MySqlConnection connection, CommandType commandType, string commandText, params MySqlParameter[] parms)
476 {
477 return ExecuteDataReader(connection, null, commandType, commandText, parms);
478 }
479
480 /// <summary>
481 /// 执行SQL语句,返回只读数据集
482 /// </summary>
483 /// <param name="transaction">事务</param>
484 /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
485 /// <param name="commandText">SQL语句或存储过程名称</param>
486 /// <param name="parms">查询参数</param>
487 /// <returns>返回只读数据集</returns>
488 private static MySqlDataReader ExecuteDataReader(MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] parms)
489 {
490 return ExecuteDataReader(transaction.Connection, transaction, commandType, commandText, parms);
491 }
492
493 /// <summary>
494 /// 执行SQL语句,返回只读数据集
495 /// </summary>
496 /// <param name="connection">数据库连接</param>
497 /// <param name="transaction">事务</param>
498 /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
499 /// <param name="commandText">SQL语句或存储过程名称</param>
500 /// <param name="parms">查询参数</param>
501 /// <returns>返回只读数据集</returns>
502 private static MySqlDataReader ExecuteDataReader(MySqlConnection connection, MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] parms)
503 {
504 MySqlCommand command = new MySqlCommand();
505 PrepareCommand(command, connection, transaction, commandType, commandText, parms);
506 return command.ExecuteReader(CommandBehavior.CloseConnection);
507 }
508
509 #endregion
510
511 #region ExecuteDataRow
512
513 /// <summary>
514 /// 执行SQL语句,返回结果集中的第一行
515 /// </summary>
516 /// <param name="connectionString">数据库连接字符串</param>
517 /// <param name="commandText">SQL语句</param>
518 /// <param name="parms">查询参数</param>
519 /// <returns>,返回结果集中的第一行</returns>
520 public static DataRow ExecuteDataRow(string connectionString, string commandText, params MySqlParameter[] parms)
521 {
522 DataTable dt = ExecuteDataTable(connectionString, CommandType.Text, commandText, parms);
523 return dt.Rows.Count > 0 ? dt.Rows[0] : null;
524 }
525
526 /// <summary>
527 /// 执行SQL语句,返回结果集中的第一行
528 /// </summary>
529 /// <param name="connectionString">数据库连接字符串</param>
530 /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
531 /// <param name="commandText">SQL语句或存储过程名称</param>
532 /// <param name="parms">查询参数</param>
533 /// <returns>,返回结果集中的第一行</returns>
534 public static DataRow ExecuteDataRow(string connectionString, CommandType commandType, string commandText, params MySqlParameter[] parms)
535 {
536 DataTable dt = ExecuteDataTable(connectionString, commandType, commandText, parms);
537 return dt.Rows.Count > 0 ? dt.Rows[0] : null;
538 }
539
540 /// <summary>
541 /// 执行SQL语句,返回结果集中的第一行
542 /// </summary>
543 /// <param name="connection">数据库连接</param>
544 /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
545 /// <param name="commandText">SQL语句或存储过程名称</param>
546 /// <param name="parms">查询参数</param>
547 /// <returns>,返回结果集中的第一行</returns>
548 public static DataRow ExecuteDataRow(MySqlConnection connection, CommandType commandType, string commandText, params MySqlParameter[] parms)
549 {
550 DataTable dt = ExecuteDataTable(connection, commandType, commandText, parms);
551 return dt.Rows.Count > 0 ? dt.Rows[0] : null;
552 }
553
554 /// <summary>
555 /// 执行SQL语句,返回结果集中的第一行
556 /// </summary>
557 /// <param name="transaction">事务</param>
558 /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
559 /// <param name="commandText">SQL语句或存储过程名称</param>
560 /// <param name="parms">查询参数</param>
561 /// <returns>,返回结果集中的第一行</returns>
562 public static DataRow ExecuteDataRow(MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] parms)
563 {
564 DataTable dt = ExecuteDataTable(transaction, commandType, commandText, parms);
565 return dt.Rows.Count > 0 ? dt.Rows[0] : null;
566 }
567
568 #endregion ExecuteDataRow
569
570 #region ExecuteDataTable
571
572 /// <summary>
573 /// 执行SQL语句,返回结果集中的第一个数据表
574 /// </summary>
575 /// <param name="connectionString">数据库连接字符串</param>
576 /// <param name="commandText">SQL语句</param>
577 /// <param name="parms">查询参数</param>
578 /// <returns>返回结果集中的第一个数据表</returns>
579 public static DataTable ExecuteDataTable(string connectionString, string commandText, params MySqlParameter[] parms)
580 {
581 return ExecuteDataSet(connectionString, CommandType.Text, commandText, parms).Tables[0];
582 }
583
584 /// <summary>
585 /// 执行SQL语句,返回结果集中的第一个数据表
586 /// </summary>
587 /// <param name="connectionString">数据库连接字符串</param>
588 /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
589 /// <param name="commandText">SQL语句或存储过程名称</param>
590 /// <param name="parms">查询参数</param>
591 /// <returns>返回结果集中的第一个数据表</returns>
592 public static DataTable ExecuteDataTable(string connectionString, CommandType commandType, string commandText, params MySqlParameter[] parms)
593 {
594 return ExecuteDataSet(connectionString, commandType, commandText, parms).Tables[0];
595 }
596
597 /// <summary>
598 /// 执行SQL语句,返回结果集中的第一个数据表
599 /// </summary>
600 /// <param name="connection">数据库连接</param>
601 /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
602 /// <param name="commandText">SQL语句或存储过程名称</param>
603 /// <param name="parms">查询参数</param>
604 /// <returns>返回结果集中的第一个数据表</returns>
605 public static DataTable ExecuteDataTable(MySqlConnection connection, CommandType commandType, string commandText, params MySqlParameter[] parms)
606 {
607 return ExecuteDataSet(connection, commandType, commandText, parms).Tables[0];
608 }
609
610 /// <summary>
611 /// 执行SQL语句,返回结果集中的第一个数据表
612 /// </summary>
613 /// <param name="transaction">事务</param>
614 /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
615 /// <param name="commandText">SQL语句或存储过程名称</param>
616 /// <param name="parms">查询参数</param>
617 /// <returns>返回结果集中的第一个数据表</returns>
618 public static DataTable ExecuteDataTable(MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] parms)
619 {
620 return ExecuteDataSet(transaction, commandType, commandText, parms).Tables[0];
621 }
622
623 /// <summary>
624 /// 执行SQL语句,返回结果集中的第一个数据表
625 /// </summary>
626 /// <param name="connectionString">数据库连接字符串</param>
627 /// <param name="tableName">数据表名称</param>
628 /// <returns>返回结果集中的第一个数据表</returns>
629 public static DataTable ExecuteEmptyDataTable(string connectionString, string tableName)
630 {
631 return ExecuteDataSet(connectionString, CommandType.Text, string.Format("select * from {0} where 1=-1", tableName)).Tables[0];
632 }
633
634 #endregion ExecuteDataTable
635
636 #region ExecuteDataSet
637
638 /// <summary>
639 /// 执行SQL语句,返回结果集
640 /// </summary>
641 /// <param name="connectionString">数据库连接字符串</param>
642 /// <param name="commandText">SQL语句</param>
643 /// <param name="parms">查询参数</param>
644 /// <returns>返回结果集</returns>
645 public static DataSet ExecuteDataSet(string connectionString, string commandText, params MySqlParameter[] parms)
646 {
647 return ExecuteDataSet(connectionString, CommandType.Text, commandText, parms);
648 }
649
650 /// <summary>
651 /// 执行SQL语句,返回结果集
652 /// </summary>
653 /// <param name="connectionString">数据库连接字符串</param>
654 /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
655 /// <param name="commandText">SQL语句或存储过程名称</param>
656 /// <param name="parms">查询参数</param>
657 /// <returns>返回结果集</returns>
658 public static DataSet ExecuteDataSet(string connectionString, CommandType commandType, string commandText, params MySqlParameter[] parms)
659 {
660 using (MySqlConnection connection = new MySqlConnection(connectionString))
661 {
662 return ExecuteDataSet(connection, commandType, commandText, parms);
663 }
664 }
665
666 /// <summary>
667 /// 执行SQL语句,返回结果集
668 /// </summary>
669 /// <param name="connection">数据库连接</param>
670 /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
671 /// <param name="commandText">SQL语句或存储过程名称</param>
672 /// <param name="parms">查询参数</param>
673 /// <returns>返回结果集</returns>
674 public static DataSet ExecuteDataSet(MySqlConnection connection, CommandType commandType, string commandText, params MySqlParameter[] parms)
675 {
676 return ExecuteDataSet(connection, null, commandType, commandText, parms);
677 }
678
679 /// <summary>
680 /// 执行SQL语句,返回结果集
681 /// </summary>
682 /// <param name="transaction">事务</param>
683 /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
684 /// <param name="commandText">SQL语句或存储过程名称</param>
685 /// <param name="parms">查询参数</param>
686 /// <returns>返回结果集</returns>
687 public static DataSet ExecuteDataSet(MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] parms)
688 {
689 return ExecuteDataSet(transaction.Connection, transaction, commandType, commandText, parms);
690 }
691
692 /// <summary>
693 /// 执行SQL语句,返回结果集
694 /// </summary>
695 /// <param name="connection">数据库连接</param>
696 /// <param name="transaction">事务</param>
697 /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
698 /// <param name="commandText">SQL语句或存储过程名称</param>
699 /// <param name="parms">查询参数</param>
700 /// <returns>返回结果集</returns>
701 private static DataSet ExecuteDataSet(MySqlConnection connection, MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] parms)
702 {
703 MySqlCommand command = new MySqlCommand();
704
705 PrepareCommand(command, connection, transaction, commandType, commandText, parms);
706 MySqlDataAdapter adapter = new MySqlDataAdapter(command);
707
708 DataSet ds = new DataSet();
709 adapter.Fill(ds);
710 if (commandText.IndexOf("@") > 0)
711 {
712 commandText = commandText.ToLower();
713 int index = commandText.IndexOf("where ");
714 if (index < 0)
715 {
716 index = commandText.IndexOf("\nwhere");
717 }
718 if (index > 0)
719 {
720 ds.ExtendedProperties.Add("SQL", commandText.Substring(0, index - 1)); //将获取的语句保存在表的一个附属数组里，方便更新时生成CommandBuilder
721 }
722 else
723 {
724 ds.ExtendedProperties.Add("SQL", commandText); //将获取的语句保存在表的一个附属数组里，方便更新时生成CommandBuilder
725 }
726 }
727 else
728 {
729 ds.ExtendedProperties.Add("SQL", commandText); //将获取的语句保存在表的一个附属数组里，方便更新时生成CommandBuilder
730 }
731
732 foreach (DataTable dt in ds.Tables)
733 {
734 dt.ExtendedProperties.Add("SQL", ds.ExtendedProperties["SQL"]);
735 }
736
737 command.Parameters.Clear();
738 return ds;
739 }
740
741 #endregion ExecuteDataSet
742
743 #region 批量操作
744
745 /// <summary>
746 ///使用MySqlDataAdapter批量更新数据
747 /// </summary>
748 /// <param name="connectionString">数据库连接字符串</param>
749 /// <param name="table">数据表</param>
750 public static void BatchUpdate(string connectionString, DataTable table)
751 {
752 MySqlConnection connection = new MySqlConnection(connectionString);
753
754 MySqlCommand command = connection.CreateCommand();
755 command.CommandTimeout = CommandTimeOut;
756 command.CommandType = CommandType.Text;
757 MySqlDataAdapter adapter = new MySqlDataAdapter(command);
758 MySqlCommandBuilder commandBulider = new MySqlCommandBuilder(adapter);
759 commandBulider.ConflictOption = ConflictOption.OverwriteChanges;
760
761 MySqlTransaction transaction = null;
762 try
763 {
764 connection.Open();
765 transaction = connection.BeginTransaction();
766 //设置批量更新的每次处理条数
767 adapter.UpdateBatchSize = BatchSize;
768 //设置事物
769 adapter.SelectCommand.Transaction = transaction;
770
771 if (table.ExtendedProperties["SQL"] != null)
772 {
773 adapter.SelectCommand.CommandText = table.ExtendedProperties["SQL"].ToString();
774 }
775 adapter.Update(table);
776 transaction.Commit();/////提交事务
777 }
778 catch (MySqlException ex)
779 {
780 if (transaction != null) transaction.Rollback();
781 throw ex;
782 }
783 finally
784 {
785 connection.Close();
786 connection.Dispose();
787 }
788 }
789
790 /// <summary>
791 ///大批量数据插入,返回成功插入行数
792 /// </summary>
793 /// <param name="connectionString">数据库连接字符串</param>
794 /// <param name="table">数据表</param>
795 /// <returns>返回成功插入行数</returns>
796 public static int BulkInsert(string connectionString, DataTable table)
797 {
798 if (string.IsNullOrEmpty(table.TableName)) throw new Exception("请给DataTable的TableName属性附上表名称");
799 if (table.Rows.Count == 0) return 0;
800 int insertCount = 0;
801 string tmpPath = Path.GetTempFileName();
802 string csv = DataTableToCsv(table);
803 File.WriteAllText(tmpPath, csv);
804 using (MySqlConnection conn = new MySqlConnection(connectionString))
805 {
806 MySqlTransaction tran = null;
807 try
808 {
809 conn.Open();
810 tran = conn.BeginTransaction();
811 MySqlBulkLoader bulk = new MySqlBulkLoader(conn)
812 {
813 FieldTerminator = ",",
814 FieldQuotationCharacter = '"',
815 EscapeCharacter = '"',
816 LineTerminator = "\r\n",
817 FileName = tmpPath,
818 NumberOfLinesToSkip = 0,
819 TableName = table.TableName,
820 };
821 bulk.Columns.AddRange(table.Columns.Cast<DataColumn>().Select(colum => colum.ColumnName).ToList());
822 insertCount = bulk.Load();
823 tran.Commit();
824 }
825 catch (MySqlException ex)
826 {
827 if (tran != null) tran.Rollback();
828 throw ex;
829 }
830 }
831 File.Delete(tmpPath);
832 return insertCount;
833 }
834
835 /// <summary>
836 ///将DataTable转换为标准的CSV
837 /// </summary>
838 /// <param name="table">数据表</param>
839 /// <returns>返回标准的CSV</returns>
840 private static string DataTableToCsv(DataTable table)
841 {
842 //以半角逗号（即,）作分隔符，列为空也要表达其存在。
843 //列内容如存在半角逗号（即,）则用半角引号（即""）将该字段值包含起来。
844 //列内容如存在半角引号（即"）则应替换成半角双引号（""）转义，并用半角引号（即""）将该字段值包含起来。
845 StringBuilder sb = new StringBuilder();
846 DataColumn colum;
847 foreach (DataRow row in table.Rows)
848 {
849 for (int i = 0; i < table.Columns.Count; i++)
850 {
851 colum = table.Columns[i];
852 if (i != 0) sb.Append(",");
853 if (colum.DataType == typeof(string) && row[colum].ToString().Contains(","))
854 {
855 sb.Append("\"" + row[colum].ToString().Replace("\"", "\"\"") + "\"");
856 }
857 else sb.Append(row[colum].ToString());
858 }
859 sb.AppendLine();
860 }
861
862 return sb.ToString();
863 }
864
865 #endregion 批量操作
866
867 #endregion 静态方法
868 }
MySQL 帮助类 MySQLHelper
Wesley13
• 阅读
704
点赞
收藏
