坐峰怀雪灬

路漫漫其修远兮,吾将上下而求索。
  博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理

关于MySql的DBHelper类以及数据分页

Posted on 2016-07-03 12:22  坐峰怀雪灬  阅读(1809)  评论(0编辑  收藏  举报

前端:

1 <%@ Register Assembly="AspNetPager" Namespace="Wuqi.Webdiyer" TagPrefix="webdiyer" %>
2  <webdiyer:AspNetPager ID="AspNetPager1" FirstPageText="首页" LastPageText="尾页" NextPageText="下一页" PrevPageText="上一页" runat="server" CssClass="pages" PagingButtonSpacing="0" CurrentPageButtonClass="cpb" PageSize="10" OnPageChanged="AspNetPager1_PageChanged">
3             </webdiyer:AspNetPager>

后台方法:

 1 protected void BindGrid()
 2         {
 3             StringBuilder strWhere = new StringBuilder();
 4             BLL.base_servicecount countBll = new BLL.base_servicecount();
 5             strWhere.AppendFormat(" UserID='{0}' ", LoginUserModel.ID);
 6             if (!string.IsNullOrEmpty(txtInput.Value.Trim()))
 7             {
 8                 strWhere.AppendFormat(" AND (ProName like '%{0}%' or Brand like '%{0}%') ", txtInput.Value.Trim());
 9             }
10             AspNetPager1.RecordCount = countBll.GetRecordCount(strWhere.ToString());
11             int itemStart = (AspNetPager1.CurrentPageIndex - 1) * AspNetPager1.PageSize;
12             gridProduct.DataSource = countBll.GetMvListByPage_MySql(strWhere.ToString(), "", itemStart, AspNetPager1.PageSize);
13             gridProduct.DataBind();
14         }
15         protected void AspNetPager1_PageChanged(object sender, EventArgs e)
16         {
17             BindGrid();
18         }

DAL方法:

 1  /// <summary>
 2         /// 分页
 3         /// </summary>
 4         public DataTable GetMvListByPage_MySql(string strWhere, string orderBy, int startIndex, int PageCount)
 5         {
 6             StringBuilder strSql = new StringBuilder();
 7             strSql.Append("SELECT * FROM mv_service_product ");
 8             if (!string.IsNullOrEmpty(strWhere.Trim()))
 9             {
10                 strSql.Append(" WHERE " + strWhere);
11             }
12             if (!string.IsNullOrEmpty(orderBy.Trim()))
13             {
14                 strSql.Append(" order by " + orderBy + " ");
15             }
16             else
17             {
18                 strSql.Append(" order by Count desc ");
19             }
20             strSql.AppendFormat(" LIMIT {0},{1} ", startIndex, PageCount);
21             return DbHelperMySQL.Query(strSql.ToString()).Tables[0] != null ? DbHelperMySQL.Query(strSql.ToString()).Tables[0] : null;
22         }

DBHelperMySql类:

  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 ZHUAO.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         /// 执行多条SQL语句,实现数据库事务。
150         /// </summary>
151         /// <param name="SQLStringList">多条SQL语句</param>        
152         public static int ExecuteSqlTran(List<String> SQLStringList)
153         {
154             using (MySqlConnection conn = new MySqlConnection(connectionString))
155             {
156                 conn.Open();
157                 MySqlCommand cmd = new MySqlCommand();
158                 cmd.Connection = conn;
159                 MySqlTransaction tx = conn.BeginTransaction();
160                 cmd.Transaction = tx;
161                 try
162                 {
163                     int count = 0;
164                     for (int n = 0; n < SQLStringList.Count; n++)
165                     {
166                         string strsql = SQLStringList[n];
167                         if (strsql.Trim().Length > 1)
168                         {
169                             cmd.CommandText = strsql;
170                             count += cmd.ExecuteNonQuery();
171                         }
172                     }
173                     tx.Commit();
174                     return count;
175                 }
176                 catch
177                 {
178                     tx.Rollback();
179                     return 0;
180                 }
181             }
182         }
183         /// <summary>
184         /// 执行带一个存储过程参数的的SQL语句。
185         /// </summary>
186         /// <param name="SQLString">SQL语句</param>
187         /// <param name="content">参数内容,比如一个字段是格式复杂的文章,有特殊符号,可以通过这个方式添加</param>
188         /// <returns>影响的记录数</returns>
189         public static int ExecuteSql(string SQLString, string content)
190         {
191             using (MySqlConnection connection = new MySqlConnection(connectionString))
192             {
193                 MySqlCommand cmd = new MySqlCommand(SQLString, connection);
194                 MySql.Data.MySqlClient.MySqlParameter myParameter = new MySql.Data.MySqlClient.MySqlParameter("@content", SqlDbType.NText);
195                 myParameter.Value = content;
196                 cmd.Parameters.Add(myParameter);
197                 try
198                 {
199                     connection.Open();
200                     int rows = cmd.ExecuteNonQuery();
201                     return rows;
202                 }
203                 catch (MySql.Data.MySqlClient.MySqlException e)
204                 {
205                     throw e;
206                 }
207                 finally
208                 {
209                     cmd.Dispose();
210                     connection.Close();
211                 }
212             }
213         }
214         /// <summary>
215         /// 执行带一个存储过程参数的的SQL语句。
216         /// </summary>
217         /// <param name="SQLString">SQL语句</param>
218         /// <param name="content">参数内容,比如一个字段是格式复杂的文章,有特殊符号,可以通过这个方式添加</param>
219         /// <returns>影响的记录数</returns>
220         public static object ExecuteSqlGet(string SQLString, string content)
221         {
222             using (MySqlConnection connection = new MySqlConnection(connectionString))
223             {
224                 MySqlCommand cmd = new MySqlCommand(SQLString, connection);
225                 MySql.Data.MySqlClient.MySqlParameter myParameter = new MySql.Data.MySqlClient.MySqlParameter("@content", SqlDbType.NText);
226                 myParameter.Value = content;
227                 cmd.Parameters.Add(myParameter);
228                 try
229                 {
230                     connection.Open();
231                     object obj = cmd.ExecuteScalar();
232                     if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
233                     {
234                         return null;
235                     }
236                     else
237                     {
238                         return obj;
239                     }
240                 }
241                 catch (MySql.Data.MySqlClient.MySqlException e)
242                 {
243                     throw e;
244                 }
245                 finally
246                 {
247                     cmd.Dispose();
248                     connection.Close();
249                 }
250             }
251         }
252         /// <summary>
253         /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
254         /// </summary>
255         /// <param name="strSQL">SQL语句</param>
256         /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
257         /// <returns>影响的记录数</returns>
258         public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
259         {
260             using (MySqlConnection connection = new MySqlConnection(connectionString))
261             {
262                 MySqlCommand cmd = new MySqlCommand(strSQL, connection);
263                 MySql.Data.MySqlClient.MySqlParameter myParameter = new MySql.Data.MySqlClient.MySqlParameter("@fs", SqlDbType.Image);
264                 myParameter.Value = fs;
265                 cmd.Parameters.Add(myParameter);
266                 try
267                 {
268                     connection.Open();
269                     int rows = cmd.ExecuteNonQuery();
270                     return rows;
271                 }
272                 catch (MySql.Data.MySqlClient.MySqlException e)
273                 {
274                     throw e;
275                 }
276                 finally
277                 {
278                     cmd.Dispose();
279                     connection.Close();
280                 }
281             }
282         }
283 
284         /// <summary>
285         /// 执行一条计算查询结果语句,返回查询结果(object)。
286         /// </summary>
287         /// <param name="SQLString">计算查询结果语句</param>
288         /// <returns>查询结果(object)</returns>
289         public static object GetSingle(string SQLString)
290         {
291             using (MySqlConnection connection = new MySqlConnection(connectionString))
292             {
293                 using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
294                 {
295                     try
296                     {
297                         connection.Open();
298                         object obj = cmd.ExecuteScalar();
299                         if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
300                         {
301                             return null;
302                         }
303                         else
304                         {
305                             return obj;
306                         }
307                     }
308                     catch (MySql.Data.MySqlClient.MySqlException e)
309                     {
310                         connection.Close();
311                         throw e;
312                     }
313                 }
314             }
315         }
316         public static object GetSingle(string SQLString, int Times)
317         {
318             using (MySqlConnection connection = new MySqlConnection(connectionString))
319             {
320                 using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
321                 {
322                     try
323                     {
324                         connection.Open();
325                         cmd.CommandTimeout = Times;
326                         object obj = cmd.ExecuteScalar();
327                         if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
328                         {
329                             return null;
330                         }
331                         else
332                         {
333                             return obj;
334                         }
335                     }
336                     catch (MySql.Data.MySqlClient.MySqlException e)
337                     {
338                         connection.Close();
339                         throw e;
340                     }
341                 }
342             }
343         }
344         /// <summary>
345         /// 执行查询语句,返回MySqlDataReader ( 注意:调用该方法后,一定要对MySqlDataReader进行Close )
346         /// </summary>
347         /// <param name="strSQL">查询语句</param>
348         /// <returns>MySqlDataReader</returns>
349         public static MySqlDataReader ExecuteReader(string strSQL)
350         {
351             MySqlConnection connection = new MySqlConnection(connectionString);
352             MySqlCommand cmd = new MySqlCommand(strSQL, connection);
353             try
354             {
355                 connection.Open();
356                 MySqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
357                 return myReader;
358             }
359             catch (MySql.Data.MySqlClient.MySqlException e)
360             {
361                 throw e;
362             }   
363 
364         }
365         /// <summary>
366         /// 执行查询语句,返回DataSet
367         /// </summary>
368         /// <param name="SQLString">查询语句</param>
369         /// <returns>DataSet</returns>
370         public static DataSet Query(string SQLString)
371         {
372             using (MySqlConnection connection = new MySqlConnection(connectionString))
373             {
374                 DataSet ds = new DataSet();
375                 try
376                 {
377                     connection.Open();
378                     MySqlDataAdapter command = new MySqlDataAdapter(SQLString, connection);
379                     command.Fill(ds, "ds");
380                 }
381                 catch (MySql.Data.MySqlClient.MySqlException ex)
382                 {
383                     throw new Exception(ex.Message);
384                 }
385                 return ds;
386             }
387         }
388         public static DataSet Query(string SQLString, int Times)
389         {
390             using (MySqlConnection connection = new MySqlConnection(connectionString))
391             {
392                 DataSet ds = new DataSet();
393                 try
394                 {
395                     connection.Open();
396                     MySqlDataAdapter command = new MySqlDataAdapter(SQLString, connection);
397                     command.SelectCommand.CommandTimeout = Times;
398                     command.Fill(ds, "ds");
399                 }
400                 catch (MySql.Data.MySqlClient.MySqlException ex)
401                 {
402                     throw new Exception(ex.Message);
403                 }
404                 return ds;
405             }
406         }
407 
408 
409 
410         #endregion
411 
412         #region 执行带参数的SQL语句
413 
414         /// <summary>
415         /// 执行SQL语句,返回影响的记录数
416         /// </summary>
417         /// <param name="SQLString">SQL语句</param>
418         /// <returns>影响的记录数</returns>
419         public static int ExecuteSql(string SQLString, params MySqlParameter[] cmdParms)
420         {
421             using (MySqlConnection connection = new MySqlConnection(connectionString))
422             {
423                 using (MySqlCommand cmd = new MySqlCommand())
424                 {
425                     try
426                     {
427                         PrepareCommand(cmd, connection, null, SQLString, cmdParms);
428                         int rows = cmd.ExecuteNonQuery();
429                         cmd.Parameters.Clear();
430                         return rows;
431                     }
432                     catch (MySql.Data.MySqlClient.MySqlException e)
433                     {
434                         throw e;
435                     }
436                 }
437             }
438         }
439 
440 
441         /// <summary>
442         /// 执行多条SQL语句,实现数据库事务。
443         /// </summary>
444         /// <param name="SQLStringList">SQL语句的哈希表(key为sql语句,value是该语句的MySqlParameter[])</param>
445         public static void ExecuteSqlTran(Hashtable SQLStringList)
446         {
447             using (MySqlConnection conn = new MySqlConnection(connectionString))
448             {
449                 conn.Open();
450                 using (MySqlTransaction trans = conn.BeginTransaction())
451                 {
452                     MySqlCommand cmd = new MySqlCommand();
453                     try
454                     {
455                         //循环
456                         foreach (DictionaryEntry myDE in SQLStringList)
457                         {
458                             string cmdText = myDE.Key.ToString();
459                             MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Value;
460                             PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
461                             int val = cmd.ExecuteNonQuery();
462                             cmd.Parameters.Clear();
463                         }
464                         trans.Commit();
465                     }
466                     catch
467                     {
468                         trans.Rollback();
469                         throw;
470                     }
471                 }
472             }
473         }
474         /// <summary>
475         /// 执行多条SQL语句,实现数据库事务。
476         /// </summary>
477         /// <param name="SQLStringList">SQL语句的哈希表(key为sql语句,value是该语句的MySqlParameter[])</param>
478         public static int ExecuteSqlTran(System.Collections.Generic.List<CommandInfo> cmdList)
479         {
480             using (MySqlConnection conn = new MySqlConnection(connectionString))
481             {
482                 conn.Open();
483                 using (MySqlTransaction trans = conn.BeginTransaction())
484                 {
485                     MySqlCommand cmd = new MySqlCommand();
486                     try
487                     { int count = 0;
488                         //循环
489                         foreach (CommandInfo myDE in cmdList)
490                         {
491                             string cmdText = myDE.CommandText;
492                             MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Parameters;
493                             PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
494                            
495                             if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)
496                             {
497                                 if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
498                                 {
499                                     trans.Rollback();
500                                     return 0;
501                                 }
502 
503                                 object obj = cmd.ExecuteScalar();
504                                 bool isHave = false;
505                                 if (obj == null && obj == DBNull.Value)
506                                 {
507                                     isHave = false;
508                                 }
509                                 isHave = Convert.ToInt32(obj) > 0;
510 
511                                 if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
512                                 {
513                                     trans.Rollback();
514                                     return 0;
515                                 }
516                                 if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
517                                 {
518                                     trans.Rollback();
519                                     return 0;
520                                 }
521                                 continue;
522                             }
523                             int val = cmd.ExecuteNonQuery();
524                             count += val;
525                             if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
526                             {
527                                 trans.Rollback();
528                                 return 0;
529                             }
530                             cmd.Parameters.Clear();
531                         }
532                         trans.Commit();
533                         return count;
534                     }
535                     catch
536                     {
537                         trans.Rollback();
538                         throw;
539                     }
540                 }
541             }
542         }
543         /// <summary>
544         /// 执行多条SQL语句,实现数据库事务。
545         /// </summary>
546         /// <param name="SQLStringList">SQL语句的哈希表(key为sql语句,value是该语句的MySqlParameter[])</param>
547         public static void ExecuteSqlTranWithIndentity(System.Collections.Generic.List<CommandInfo> SQLStringList)
548         {
549             using (MySqlConnection conn = new MySqlConnection(connectionString))
550             {
551                 conn.Open();
552                 using (MySqlTransaction trans = conn.BeginTransaction())
553                 {
554                     MySqlCommand cmd = new MySqlCommand();
555                     try
556                     {
557                         int indentity = 0;
558                         //循环
559                         foreach (CommandInfo myDE in SQLStringList)
560                         {
561                             string cmdText = myDE.CommandText;
562                             MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Parameters;
563                             foreach (MySqlParameter q in cmdParms)
564                             {
565                                 if (q.Direction == ParameterDirection.InputOutput)
566                                 {
567                                     q.Value = indentity;
568                                 }
569                             }
570                             PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
571                             int val = cmd.ExecuteNonQuery();
572                             foreach (MySqlParameter q in cmdParms)
573                             {
574                                 if (q.Direction == ParameterDirection.Output)
575                                 {
576                                     indentity = Convert.ToInt32(q.Value);
577                                 }
578                             }
579                             cmd.Parameters.Clear();
580                         }
581                         trans.Commit();
582                     }
583                     catch
584                     {
585                         trans.Rollback();
586                         throw;
587                     }
588                 }
589             }
590         }
591         /// <summary>
592         /// 执行多条SQL语句,实现数据库事务。
593         /// </summary>
594         /// <param name="SQLStringList">SQL语句的哈希表(key为sql语句,value是该语句的MySqlParameter[])</param>
595         public static void ExecuteSqlTranWithIndentity(Hashtable SQLStringList)
596         {
597             using (MySqlConnection conn = new MySqlConnection(connectionString))
598             {
599                 conn.Open();
600                 using (MySqlTransaction trans = conn.BeginTransaction())
601                 {
602                     MySqlCommand cmd = new MySqlCommand();
603                     try
604                     {
605                         int indentity = 0;
606                         //循环
607                         foreach (DictionaryEntry myDE in SQLStringList)
608                         {
609                             string cmdText = myDE.Key.ToString();
610                             MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Value;
611                             foreach (MySqlParameter q in cmdParms)
612                             {
613                                 if (q.Direction == ParameterDirection.InputOutput)
614                                 {
615                                     q.Value = indentity;
616                                 }
617                             }
618                             PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
619                             int val = cmd.ExecuteNonQuery();
620                             foreach (MySqlParameter q in cmdParms)
621                             {
622                                 if (q.Direction == ParameterDirection.Output)
623                                 {
624                                     indentity = Convert.ToInt32(q.Value);
625                                 }
626                             }
627                             cmd.Parameters.Clear();
628                         }
629                         trans.Commit();
630                     }
631                     catch
632                     {
633                         trans.Rollback();
634                         throw;
635                     }
636                 }
637             }
638         }
639         /// <summary>
640         /// 执行一条计算查询结果语句,返回查询结果(object)。
641         /// </summary>
642         /// <param name="SQLString">计算查询结果语句</param>
643         /// <returns>查询结果(object)</returns>
644         public static object GetSingle(string SQLString, params MySqlParameter[] cmdParms)
645         {
646             using (MySqlConnection connection = new MySqlConnection(connectionString))
647             {
648                 using (MySqlCommand cmd = new MySqlCommand())
649                 {
650                     try
651                     {
652                         PrepareCommand(cmd, connection, null, SQLString, cmdParms);
653                         object obj = cmd.ExecuteScalar();
654                         cmd.Parameters.Clear();
655                         if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
656                         {
657                             return null;
658                         }
659                         else
660                         {
661                             return obj;
662                         }
663                     }
664                     catch (MySql.Data.MySqlClient.MySqlException e)
665                     {
666                         throw e;
667                     }
668                 }
669             }
670         }
671 
672         /// <summary>
673         /// 执行查询语句,返回MySqlDataReader ( 注意:调用该方法后,一定要对MySqlDataReader进行Close )
674         /// </summary>
675         /// <param name="strSQL">查询语句</param>
676         /// <returns>MySqlDataReader</returns>
677         public static MySqlDataReader ExecuteReader(string SQLString, params MySqlParameter[] cmdParms)
678         {
679             MySqlConnection connection = new MySqlConnection(connectionString);
680             MySqlCommand cmd = new MySqlCommand();
681             try
682             {
683                 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
684                 MySqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
685                 cmd.Parameters.Clear();
686                 return myReader;
687             }
688             catch (MySql.Data.MySqlClient.MySqlException e)
689             {
690                 throw e;
691             }
692             //            finally
693             //            {
694             //                cmd.Dispose();
695             //                connection.Close();
696             //            }    
697 
698         }
699 
700         /// <summary>
701         /// 执行查询语句,返回DataSet
702         /// </summary>
703         /// <param name="SQLString">查询语句</param>
704         /// <returns>DataSet</returns>
705         public static DataSet Query(string SQLString, params MySqlParameter[] cmdParms)
706         {
707             using (MySqlConnection connection = new MySqlConnection(connectionString))
708             {
709                 MySqlCommand cmd = new MySqlCommand();
710                 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
711                 using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
712                 {
713                     DataSet ds = new DataSet();
714                     try
715                     {
716                         da.Fill(ds, "ds");
717                         cmd.Parameters.Clear();
718                     }
719                     catch (MySql.Data.MySqlClient.MySqlException ex)
720                     {
721                         throw new Exception(ex.Message);
722                     }
723                     return ds;
724                 }
725             }
726         }
727 
728 
729         private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, string cmdText, MySqlParameter[] cmdParms)
730         {
731             if (conn.State != ConnectionState.Open)
732                 conn.Open();
733             cmd.Connection = conn;
734             cmd.CommandText = cmdText;
735             if (trans != null)
736                 cmd.Transaction = trans;
737             cmd.CommandType = CommandType.Text;//cmdType;
738             if (cmdParms != null)
739             {
740 
741 
742                 foreach (MySqlParameter parameter in cmdParms)
743                 {
744                     if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
745                         (parameter.Value == null))
746                     {
747                         parameter.Value = DBNull.Value;
748                     }
749                     cmd.Parameters.Add(parameter);
750                 }
751             }
752         }
753 
754         #endregion
755 
756         
757 
758     }
759 
760 }