NET-数据访问基础类(基于MySQL)

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