C#访问MySQL数据库帮助类

MySQL数据库访问帮助类

1.项目添加引用官方MySQL动态库MySql.Data.dll

下载地址：MySql.Data.dll（也可以到官网下载动态库）项目添加引用

这里有一个Mysql帮助类的使用例子可以参考

C#简单三层结构设计UI、BLL、DAL

 

2.web.config配置数据库连接字符串

 1 <?xml version="1.0"?>
 2 <configuration>
 3   <appSettings>
 4     <!-- 连接字符串是否加密 -->
 5     <add key="ConStringEncrypt" value="false"/>
 6     <!-- 数据库连接字符串,（如果采用加密方式，上面一项要设置为true；加密工具，可在官方下载，
 7              如果使用明文这样server=127.0.0.1;database=.....,上面则设置为false。 -->
 8     <add key="ConnectionString" value="server=127.0.0.1;database=BenXHCMS;uid=sa;pwd=jyf"/>
 9   </appSettings>
10 </configuration>

3.数据库字符串读取类（多一个加密算法，可以自己添加）

 1     public class PubConstant
 2     {        
 3         /// <summary>
 4         /// 获取连接字符串
 5         /// </summary>
 6         public static string ConnectionString
 7         {           
 8             get 
 9             {
10                 string _connectionString = ConfigurationManager.AppSettings["ConnectionString"];       
11                 string ConStringEncrypt = ConfigurationManager.AppSettings["ConStringEncrypt"];
12                 if (ConStringEncrypt == "true")
13                 {
14                     _connectionString = DESEncrypt.Decrypt(_connectionString);
15                 }
16                 return _connectionString; 
17             }
18         }
19 
20         /// <summary>
21         /// 得到web.config里配置项的数据库连接字符串。
22         /// </summary>
23         /// <param name="configName"></param>
24         /// <returns></returns>
25         public static string GetConnectionString(string configName)
26         {
27             string connectionString = ConfigurationManager.AppSettings[configName];
28             string ConStringEncrypt = ConfigurationManager.AppSettings["ConStringEncrypt"];
29             if (ConStringEncrypt == "true")
30             {
31                 connectionString = DESEncrypt.Decrypt(connectionString);
32             }
33             return connectionString;
34         }
35 
36 
37     }

4.MySQL数据库访问类--注意：对 MySql.Data.MySqlClient引用

  1 using System;
  2 using System.Collections;
  3 using System.Collections.Specialized;
  4 using System.Data;
  5 using MySql.Data.MySqlClient;
  6 using System.Configuration;
  7 using System.Data.Common;
  8 using System.Collections.Generic;
  9 namespace Maticsoft.DBUtility
 10 {
 11     /// <summary>
 12     /// 数据访问抽象基础类
 13     /// Copyright (C) Maticsoft
 14     /// </summary>
 15     public abstract class DbHelperMySQL
 16     {
 17         //数据库连接字符串(web.config来配置)，可以动态更改connectionString支持多数据库.        
 18         public static string connectionString = PubConstant.ConnectionString;
 19         public DbHelperMySQL()
 20         {            
 21         }
 22 
 23         #region 公用方法
 24         /// <summary>
 25         /// 得到最大值
 26         /// </summary>
 27         /// <param name="FieldName"></param>
 28         /// <param name="TableName"></param>
 29         /// <returns></returns>
 30         public static int GetMaxID(string FieldName, string TableName)
 31         {
 32             string strsql = "select max(" + FieldName + ")+1 from " + TableName;
 33             object obj = GetSingle(strsql);
 34             if (obj == null)
 35             {
 36                 return 1;
 37             }
 38             else
 39             {
 40                 return int.Parse(obj.ToString());
 41             }
 42         }
 43         /// <summary>
 44         /// 是否存在
 45         /// </summary>
 46         /// <param name="strSql"></param>
 47         /// <returns></returns>
 48         public static bool Exists(string strSql)
 49         {
 50             object obj = GetSingle(strSql);
 51             int cmdresult;
 52             if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
 53             {
 54                 cmdresult = 0;
 55             }
 56             else
 57             {
 58                 cmdresult = int.Parse(obj.ToString());
 59             }
 60             if (cmdresult == 0)
 61             {
 62                 return false;
 63             }
 64             else
 65             {
 66                 return true;
 67             }
 68         }    
 69         /// <summary>
 70         /// 是否存在（基于MySqlParameter）
 71         /// </summary>
 72         /// <param name="strSql"></param>
 73         /// <param name="cmdParms"></param>
 74         /// <returns></returns>
 75         public static bool Exists(string strSql, params MySqlParameter[] cmdParms)
 76         {
 77             object obj = GetSingle(strSql, cmdParms);
 78             int cmdresult;
 79             if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
 80             {
 81                 cmdresult = 0;
 82             }
 83             else
 84             {
 85                 cmdresult = int.Parse(obj.ToString());
 86             }
 87             if (cmdresult == 0)
 88             {
 89                 return false;
 90             }
 91             else
 92             {
 93                 return true;
 94             }
 95         }
 96         #endregion
 97 
 98         #region  执行简单SQL语句
 99 
100         /// <summary>
101         /// 执行SQL语句，返回影响的记录数
102         /// </summary>
103         /// <param name="SQLString">SQL语句</param>
104         /// <returns>影响的记录数</returns>
105         public static int ExecuteSql(string SQLString)
106         {
107             using (MySqlConnection connection = new MySqlConnection(connectionString))
108             {
109                 using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
110                 {
111                     try
112                     {
113                         connection.Open();
114                         int rows = cmd.ExecuteNonQuery();
115                         return rows;
116                     }
117                     catch (MySql.Data.MySqlClient.MySqlException e)
118                     {
119                         connection.Close();
120                         throw e;
121                     }
122                 }
123             }
124         }
125 
126         public static int ExecuteSqlByTime(string SQLString, int Times)
127         {
128             using (MySqlConnection connection = new MySqlConnection(connectionString))
129             {
130                 using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
131                 {
132                     try
133                     {
134                         connection.Open();
135                         cmd.CommandTimeout = Times;
136                         int rows = cmd.ExecuteNonQuery();
137                         return rows;
138                     }
139                     catch (MySql.Data.MySqlClient.MySqlException e)
140                     {
141                         connection.Close();
142                         throw e;
143                     }
144                 }
145             }
146         }
147       
148         /// <summary>
149         /// 执行MySql和Oracle滴混合事务
150         /// </summary>
151         /// <param name="list">SQL命令行列表</param>
152         /// <param name="oracleCmdSqlList">Oracle命令行列表</param>
153         /// <returns>执行结果 0-由于SQL造成事务失败 -1 由于Oracle造成事务失败 1-整体事务执行成功</returns>
154         public static int ExecuteSqlTran(List<CommandInfo> list, List<CommandInfo> oracleCmdSqlList)
155         {
156             using (MySqlConnection conn = new MySqlConnection(connectionString))
157             {
158                 conn.Open();
159                 MySqlCommand cmd = new MySqlCommand();
160                 cmd.Connection = conn;
161                 MySqlTransaction tx = conn.BeginTransaction();
162                 cmd.Transaction = tx;
163                 try
164                 {
165                     foreach (CommandInfo myDE in list)
166                     {
167                         string cmdText = myDE.CommandText;
168                         MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Parameters;
169                         PrepareCommand(cmd, conn, tx, cmdText, cmdParms);
170                         if (myDE.EffentNextType == EffentNextType.SolicitationEvent)
171                         {
172                             if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
173                             {
174                                 tx.Rollback();
175                                 throw new Exception("违背要求"+myDE.CommandText+"必须符合select count(..的格式");
176                                 //return 0;
177                             }
178 
179                             object obj = cmd.ExecuteScalar();
180                             bool isHave = false;
181                             if (obj == null && obj == DBNull.Value)
182                             {
183                                 isHave = false;
184                             }
185                             isHave = Convert.ToInt32(obj) > 0;
186                             if (isHave)
187                             {
188                                 //引发事件
189                                 myDE.OnSolicitationEvent();
190                             }
191                         }
192                         if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)
193                         {
194                             if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
195                             {
196                                 tx.Rollback();
197                                 throw new Exception("SQL:违背要求" + myDE.CommandText + "必须符合select count(..的格式");
198                                 //return 0;
199                             }
200 
201                             object obj = cmd.ExecuteScalar();
202                             bool isHave = false;
203                             if (obj == null && obj == DBNull.Value)
204                             {
205                                 isHave = false;
206                             }
207                             isHave = Convert.ToInt32(obj) > 0;
208 
209                             if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
210                             {
211                                 tx.Rollback();
212                                 throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须大于0");
213                                 //return 0;
214                             }
215                             if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
216                             {
217                                 tx.Rollback();
218                                 throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须等于0");
219                                 //return 0;
220                             }
221                             continue;
222                         }
223                         int val = cmd.ExecuteNonQuery();
224                         if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
225                         {
226                             tx.Rollback();
227                             throw new Exception("SQL:违背要求" + myDE.CommandText + "必须有影响行");
228                             //return 0;
229                         }
230                         cmd.Parameters.Clear();
231                     }
232                     string oraConnectionString = PubConstant.GetConnectionString("ConnectionStringPPC");
233                     bool res = OracleHelper.ExecuteSqlTran(oraConnectionString, oracleCmdSqlList);
234                     if (!res)
235                     {
236                         tx.Rollback();
237                         throw new Exception("执行失败");
238                         // return -1;
239                     }
240                     tx.Commit();
241                     return 1;
242                 }
243                 catch (MySql.Data.MySqlClient.MySqlException e)
244                 {
245                     tx.Rollback();
246                     throw e;
247                 }
248                 catch (Exception e)
249                 {
250                     tx.Rollback();
251                     throw e;
252                 }
253             }
254         }        
255         /// <summary>
256         /// 执行多条SQL语句，实现数据库事务。
257         /// </summary>
258         /// <param name="SQLStringList">多条SQL语句</param>        
259         public static int ExecuteSqlTran(List<String> SQLStringList)
260         {
261             using (MySqlConnection conn = new MySqlConnection(connectionString))
262             {
263                 conn.Open();
264                 MySqlCommand cmd = new MySqlCommand();
265                 cmd.Connection = conn;
266                 MySqlTransaction tx = conn.BeginTransaction();
267                 cmd.Transaction = tx;
268                 try
269                 {
270                     int count = 0;
271                     for (int n = 0; n < SQLStringList.Count; n++)
272                     {
273                         string strsql = SQLStringList[n];
274                         if (strsql.Trim().Length > 1)
275                         {
276                             cmd.CommandText = strsql;
277                             count += cmd.ExecuteNonQuery();
278                         }
279                     }
280                     tx.Commit();
281                     return count;
282                 }
283                 catch
284                 {
285                     tx.Rollback();
286                     return 0;
287                 }
288             }
289         }
290         /// <summary>
291         /// 执行带一个存储过程参数的的SQL语句。
292         /// </summary>
293         /// <param name="SQLString">SQL语句</param>
294         /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
295         /// <returns>影响的记录数</returns>
296         public static int ExecuteSql(string SQLString, string content)
297         {
298             using (MySqlConnection connection = new MySqlConnection(connectionString))
299             {
300                 MySqlCommand cmd = new MySqlCommand(SQLString, connection);
301                 MySql.Data.MySqlClient.MySqlParameter myParameter = new MySql.Data.MySqlClient.MySqlParameter("@content", SqlDbType.NText);
302                 myParameter.Value = content;
303                 cmd.Parameters.Add(myParameter);
304                 try
305                 {
306                     connection.Open();
307                     int rows = cmd.ExecuteNonQuery();
308                     return rows;
309                 }
310                 catch (MySql.Data.MySqlClient.MySqlException e)
311                 {
312                     throw e;
313                 }
314                 finally
315                 {
316                     cmd.Dispose();
317                     connection.Close();
318                 }
319             }
320         }
321         /// <summary>
322         /// 执行带一个存储过程参数的的SQL语句。
323         /// </summary>
324         /// <param name="SQLString">SQL语句</param>
325         /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
326         /// <returns>影响的记录数</returns>
327         public static object ExecuteSqlGet(string SQLString, string content)
328         {
329             using (MySqlConnection connection = new MySqlConnection(connectionString))
330             {
331                 MySqlCommand cmd = new MySqlCommand(SQLString, connection);
332                 MySql.Data.MySqlClient.MySqlParameter myParameter = new MySql.Data.MySqlClient.MySqlParameter("@content", SqlDbType.NText);
333                 myParameter.Value = content;
334                 cmd.Parameters.Add(myParameter);
335                 try
336                 {
337                     connection.Open();
338                     object obj = cmd.ExecuteScalar();
339                     if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
340                     {
341                         return null;
342                     }
343                     else
344                     {
345                         return obj;
346                     }
347                 }
348                 catch (MySql.Data.MySqlClient.MySqlException e)
349                 {
350                     throw e;
351                 }
352                 finally
353                 {
354                     cmd.Dispose();
355                     connection.Close();
356                 }
357             }
358         }
359         /// <summary>
360         /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
361         /// </summary>
362         /// <param name="strSQL">SQL语句</param>
363         /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
364         /// <returns>影响的记录数</returns>
365         public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
366         {
367             using (MySqlConnection connection = new MySqlConnection(connectionString))
368             {
369                 MySqlCommand cmd = new MySqlCommand(strSQL, connection);
370                 MySql.Data.MySqlClient.MySqlParameter myParameter = new MySql.Data.MySqlClient.MySqlParameter("@fs", SqlDbType.Image);
371                 myParameter.Value = fs;
372                 cmd.Parameters.Add(myParameter);
373                 try
374                 {
375                     connection.Open();
376                     int rows = cmd.ExecuteNonQuery();
377                     return rows;
378                 }
379                 catch (MySql.Data.MySqlClient.MySqlException e)
380                 {
381                     throw e;
382                 }
383                 finally
384                 {
385                     cmd.Dispose();
386                     connection.Close();
387                 }
388             }
389         }
390 
391         /// <summary>
392         /// 执行一条计算查询结果语句，返回查询结果（object）。
393         /// </summary>
394         /// <param name="SQLString">计算查询结果语句</param>
395         /// <returns>查询结果（object）</returns>
396         public static object GetSingle(string SQLString)
397         {
398             using (MySqlConnection connection = new MySqlConnection(connectionString))
399             {
400                 using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
401                 {
402                     try
403                     {
404                         connection.Open();
405                         object obj = cmd.ExecuteScalar();
406                         if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
407                         {
408                             return null;
409                         }
410                         else
411                         {
412                             return obj;
413                         }
414                     }
415                     catch (MySql.Data.MySqlClient.MySqlException e)
416                     {
417                         connection.Close();
418                         throw e;
419                     }
420                 }
421             }
422         }
423         public static object GetSingle(string SQLString, int Times)
424         {
425             using (MySqlConnection connection = new MySqlConnection(connectionString))
426             {
427                 using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
428                 {
429                     try
430                     {
431                         connection.Open();
432                         cmd.CommandTimeout = Times;
433                         object obj = cmd.ExecuteScalar();
434                         if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
435                         {
436                             return null;
437                         }
438                         else
439                         {
440                             return obj;
441                         }
442                     }
443                     catch (MySql.Data.MySqlClient.MySqlException e)
444                     {
445                         connection.Close();
446                         throw e;
447                     }
448                 }
449             }
450         }
451         /// <summary>
452         /// 执行查询语句，返回MySqlDataReader ( 注意：调用该方法后，一定要对MySqlDataReader进行Close )
453         /// </summary>
454         /// <param name="strSQL">查询语句</param>
455         /// <returns>MySqlDataReader</returns>
456         public static MySqlDataReader ExecuteReader(string strSQL)
457         {
458             MySqlConnection connection = new MySqlConnection(connectionString);
459             MySqlCommand cmd = new MySqlCommand(strSQL, connection);
460             try
461             {
462                 connection.Open();
463                 MySqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
464                 return myReader;
465             }
466             catch (MySql.Data.MySqlClient.MySqlException e)
467             {
468                 throw e;
469             }   
470 
471         }
472         /// <summary>
473         /// 执行查询语句，返回DataSet
474         /// </summary>
475         /// <param name="SQLString">查询语句</param>
476         /// <returns>DataSet</returns>
477         public static DataSet Query(string SQLString)
478         {
479             using (MySqlConnection connection = new MySqlConnection(connectionString))
480             {
481                 DataSet ds = new DataSet();
482                 try
483                 {
484                     connection.Open();
485                     MySqlDataAdapter command = new MySqlDataAdapter(SQLString, connection);
486                     command.Fill(ds, "ds");
487                 }
488                 catch (MySql.Data.MySqlClient.MySqlException ex)
489                 {
490                     throw new Exception(ex.Message);
491                 }
492                 return ds;
493             }
494         }
495         public static DataSet Query(string SQLString, int Times)
496         {
497             using (MySqlConnection connection = new MySqlConnection(connectionString))
498             {
499                 DataSet ds = new DataSet();
500                 try
501                 {
502                     connection.Open();
503                     MySqlDataAdapter command = new MySqlDataAdapter(SQLString, connection);
504                     command.SelectCommand.CommandTimeout = Times;
505                     command.Fill(ds, "ds");
506                 }
507                 catch (MySql.Data.MySqlClient.MySqlException ex)
508                 {
509                     throw new Exception(ex.Message);
510                 }
511                 return ds;
512             }
513         }
514 
515 
516 
517         #endregion
518 
519         #region 执行带参数的SQL语句
520 
521         /// <summary>
522         /// 执行SQL语句，返回影响的记录数
523         /// </summary>
524         /// <param name="SQLString">SQL语句</param>
525         /// <returns>影响的记录数</returns>
526         public static int ExecuteSql(string SQLString, params MySqlParameter[] cmdParms)
527         {
528             using (MySqlConnection connection = new MySqlConnection(connectionString))
529             {
530                 using (MySqlCommand cmd = new MySqlCommand())
531                 {
532                     try
533                     {
534                         PrepareCommand(cmd, connection, null, SQLString, cmdParms);
535                         int rows = cmd.ExecuteNonQuery();
536                         cmd.Parameters.Clear();
537                         return rows;
538                     }
539                     catch (MySql.Data.MySqlClient.MySqlException e)
540                     {
541                         throw e;
542                     }
543                 }
544             }
545         }
546 
547 
548         /// <summary>
549         /// 执行多条SQL语句，实现数据库事务。
550         /// </summary>
551         /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的MySqlParameter[]）</param>
552         public static void ExecuteSqlTran(Hashtable SQLStringList)
553         {
554             using (MySqlConnection conn = new MySqlConnection(connectionString))
555             {
556                 conn.Open();
557                 using (MySqlTransaction trans = conn.BeginTransaction())
558                 {
559                     MySqlCommand cmd = new MySqlCommand();
560                     try
561                     {
562                         //循环
563                         foreach (DictionaryEntry myDE in SQLStringList)
564                         {
565                             string cmdText = myDE.Key.ToString();
566                             MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Value;
567                             PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
568                             int val = cmd.ExecuteNonQuery();
569                             cmd.Parameters.Clear();
570                         }
571                         trans.Commit();
572                     }
573                     catch
574                     {
575                         trans.Rollback();
576                         throw;
577                     }
578                 }
579             }
580         }
581         /// <summary>
582         /// 执行多条SQL语句，实现数据库事务。
583         /// </summary>
584         /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的MySqlParameter[]）</param>
585         public static int ExecuteSqlTran(System.Collections.Generic.List<CommandInfo> cmdList)
586         {
587             using (MySqlConnection conn = new MySqlConnection(connectionString))
588             {
589                 conn.Open();
590                 using (MySqlTransaction trans = conn.BeginTransaction())
591                 {
592                     MySqlCommand cmd = new MySqlCommand();
593                     try
594                     { int count = 0;
595                         //循环
596                         foreach (CommandInfo myDE in cmdList)
597                         {
598                             string cmdText = myDE.CommandText;
599                             MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Parameters;
600                             PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
601                            
602                             if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)
603                             {
604                                 if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
605                                 {
606                                     trans.Rollback();
607                                     return 0;
608                                 }
609 
610                                 object obj = cmd.ExecuteScalar();
611                                 bool isHave = false;
612                                 if (obj == null && obj == DBNull.Value)
613                                 {
614                                     isHave = false;
615                                 }
616                                 isHave = Convert.ToInt32(obj) > 0;
617 
618                                 if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
619                                 {
620                                     trans.Rollback();
621                                     return 0;
622                                 }
623                                 if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
624                                 {
625                                     trans.Rollback();
626                                     return 0;
627                                 }
628                                 continue;
629                             }
630                             int val = cmd.ExecuteNonQuery();
631                             count += val;
632                             if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
633                             {
634                                 trans.Rollback();
635                                 return 0;
636                             }
637                             cmd.Parameters.Clear();
638                         }
639                         trans.Commit();
640                         return count;
641                     }
642                     catch
643                     {
644                         trans.Rollback();
645                         throw;
646                     }
647                 }
648             }
649         }
650         /// <summary>
651         /// 执行多条SQL语句，实现数据库事务。
652         /// </summary>
653         /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的MySqlParameter[]）</param>
654         public static void ExecuteSqlTranWithIndentity(System.Collections.Generic.List<CommandInfo> SQLStringList)
655         {
656             using (MySqlConnection conn = new MySqlConnection(connectionString))
657             {
658                 conn.Open();
659                 using (MySqlTransaction trans = conn.BeginTransaction())
660                 {
661                     MySqlCommand cmd = new MySqlCommand();
662                     try
663                     {
664                         int indentity = 0;
665                         //循环
666                         foreach (CommandInfo myDE in SQLStringList)
667                         {
668                             string cmdText = myDE.CommandText;
669                             MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Parameters;
670                             foreach (MySqlParameter q in cmdParms)
671                             {
672                                 if (q.Direction == ParameterDirection.InputOutput)
673                                 {
674                                     q.Value = indentity;
675                                 }
676                             }
677                             PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
678                             int val = cmd.ExecuteNonQuery();
679                             foreach (MySqlParameter q in cmdParms)
680                             {
681                                 if (q.Direction == ParameterDirection.Output)
682                                 {
683                                     indentity = Convert.ToInt32(q.Value);
684                                 }
685                             }
686                             cmd.Parameters.Clear();
687                         }
688                         trans.Commit();
689                     }
690                     catch
691                     {
692                         trans.Rollback();
693                         throw;
694                     }
695                 }
696             }
697         }
698         /// <summary>
699         /// 执行多条SQL语句，实现数据库事务。
700         /// </summary>
701         /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的MySqlParameter[]）</param>
702         public static void ExecuteSqlTranWithIndentity(Hashtable SQLStringList)
703         {
704             using (MySqlConnection conn = new MySqlConnection(connectionString))
705             {
706                 conn.Open();
707                 using (MySqlTransaction trans = conn.BeginTransaction())
708                 {
709                     MySqlCommand cmd = new MySqlCommand();
710                     try
711                     {
712                         int indentity = 0;
713                         //循环
714                         foreach (DictionaryEntry myDE in SQLStringList)
715                         {
716                             string cmdText = myDE.Key.ToString();
717                             MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Value;
718                             foreach (MySqlParameter q in cmdParms)
719                             {
720                                 if (q.Direction == ParameterDirection.InputOutput)
721                                 {
722                                     q.Value = indentity;
723                                 }
724                             }
725                             PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
726                             int val = cmd.ExecuteNonQuery();
727                             foreach (MySqlParameter q in cmdParms)
728                             {
729                                 if (q.Direction == ParameterDirection.Output)
730                                 {
731                                     indentity = Convert.ToInt32(q.Value);
732                                 }
733                             }
734                             cmd.Parameters.Clear();
735                         }
736                         trans.Commit();
737                     }
738                     catch
739                     {
740                         trans.Rollback();
741                         throw;
742                     }
743                 }
744             }
745         }
746         /// <summary>
747         /// 执行一条计算查询结果语句，返回查询结果（object）。
748         /// </summary>
749         /// <param name="SQLString">计算查询结果语句</param>
750         /// <returns>查询结果（object）</returns>
751         public static object GetSingle(string SQLString, params MySqlParameter[] cmdParms)
752         {
753             using (MySqlConnection connection = new MySqlConnection(connectionString))
754             {
755                 using (MySqlCommand cmd = new MySqlCommand())
756                 {
757                     try
758                     {
759                         PrepareCommand(cmd, connection, null, SQLString, cmdParms);
760                         object obj = cmd.ExecuteScalar();
761                         cmd.Parameters.Clear();
762                         if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
763                         {
764                             return null;
765                         }
766                         else
767                         {
768                             return obj;
769                         }
770                     }
771                     catch (MySql.Data.MySqlClient.MySqlException e)
772                     {
773                         throw e;
774                     }
775                 }
776             }
777         }
778 
779         /// <summary>
780         /// 执行查询语句，返回MySqlDataReader ( 注意：调用该方法后，一定要对MySqlDataReader进行Close )
781         /// </summary>
782         /// <param name="strSQL">查询语句</param>
783         /// <returns>MySqlDataReader</returns>
784         public static MySqlDataReader ExecuteReader(string SQLString, params MySqlParameter[] cmdParms)
785         {
786             MySqlConnection connection = new MySqlConnection(connectionString);
787             MySqlCommand cmd = new MySqlCommand();
788             try
789             {
790                 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
791                 MySqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
792                 cmd.Parameters.Clear();
793                 return myReader;
794             }
795             catch (MySql.Data.MySqlClient.MySqlException e)
796             {
797                 throw e;
798             }
799             //            finally
800             //            {
801             //                cmd.Dispose();
802             //                connection.Close();
803             //            }    
804 
805         }
806 
807         /// <summary>
808         /// 执行查询语句，返回DataSet
809         /// </summary>
810         /// <param name="SQLString">查询语句</param>
811         /// <returns>DataSet</returns>
812         public static DataSet Query(string SQLString, params MySqlParameter[] cmdParms)
813         {
814             using (MySqlConnection connection = new MySqlConnection(connectionString))
815             {
816                 MySqlCommand cmd = new MySqlCommand();
817                 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
818                 using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
819                 {
820                     DataSet ds = new DataSet();
821                     try
822                     {
823                         da.Fill(ds, "ds");
824                         cmd.Parameters.Clear();
825                     }
826                     catch (MySql.Data.MySqlClient.MySqlException ex)
827                     {
828                         throw new Exception(ex.Message);
829                     }
830                     return ds;
831                 }
832             }
833         }
834 
835 
836         private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, string cmdText, MySqlParameter[] cmdParms)
837         {
838             if (conn.State != ConnectionState.Open)
839                 conn.Open();
840             cmd.Connection = conn;
841             cmd.CommandText = cmdText;
842             if (trans != null)
843                 cmd.Transaction = trans;
844             cmd.CommandType = CommandType.Text;//cmdType;
845             if (cmdParms != null)
846             {
847 
848 
849                 foreach (MySqlParameter parameter in cmdParms)
850                 {
851                     if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
852                         (parameter.Value == null))
853                     {
854                         parameter.Value = DBNull.Value;
855                     }
856                     cmd.Parameters.Add(parameter);
857                 }
858             }
859         }
860 
861         #endregion
862 
863         
864 
865     }
866 
867 }

 

posted @ 2017-03-18 11:47  JiYF  阅读(13278)  评论(0)  编辑  收藏  举报