VS连接数据库的通用方法（SQL/MySql）

在vs里面连接数据库的方法有很多，这里是通用的方法和基本操作

SQL

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

MySql

    public class DataHelper
    {
        //数据库连接字符串(web.config来配置)，可以动态更改connectionString支持多数据库.
        public static string connectionString = ConfigurationManager.ConnectionStrings["ReadContextMysql"].ConnectionString;
        //public DbHelperMySQL()
        //{
        //}

        #region 公用方法
        /// <summary>
        /// 得到最大值
        /// </summary>
        /// <param name="FieldName"></param>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public static int GetMaxID(string FieldName, string TableName)
        {
            string strsql = "select max(" + FieldName + ")+1 from " + TableName;
            object obj = GetSingle(strsql);
            if (obj == null)
            {
                return 1;
            }
            else
            {
                return int.Parse(obj.ToString());
            }
        }
        /// <summary>
        /// 是否存在
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public static bool Exists(string strSql)
        {
            object obj = GetSingle(strSql);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        /// <summary>
        /// 是否存在（基于MySqlParameter）
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public static bool Exists(string strSql, params MySqlParameter[] cmdParms)
        {
            object obj = GetSingle(strSql, cmdParms);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        #endregion

        #region  执行简单SQL语句

        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string SQLString)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                      
                        int rows = cmd.ExecuteNonQuery();
                        return rows;
                    }
                    catch (MySql.Data.MySqlClient.MySqlException e)
                    {
                        connection.Close();
                        throw e;
                    }
                }
            }
        }

        public static int ExecuteSqlByTime(string SQLString, int Times)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        cmd.CommandTimeout = Times;
                        int rows = cmd.ExecuteNonQuery();
                        return rows;
                    }
                    catch (MySql.Data.MySqlClient.MySqlException e)
                    {
                        connection.Close();
                        throw e;
                    }
                }
            }
        }

        /// <summary>
        /// 执行MySql和Oracle滴混合事务
        /// </summary>
        /// <param name="list">SQL命令行列表</param>
        /// <param name="oracleCmdSqlList">Oracle命令行列表</param>
        /// <returns>执行结果 0-由于SQL造成事务失败 -1 由于Oracle造成事务失败 1-整体事务执行成功</returns>
        //public static int ExecuteSqlTran(List<CommandInfo> list, List<CommandInfo> oracleCmdSqlList)
        //{
        //    using (MySqlConnection conn = new MySqlConnection(connectionString))
        //    {
        //        conn.Open();
        //        MySqlCommand cmd = new MySqlCommand();
        //        cmd.Connection = conn;
        //        MySqlTransaction tx = conn.BeginTransaction();
        //        cmd.Transaction = tx;
        //        try
        //        {
        //            foreach (CommandInfo myDE in list)
        //            {
        //                string cmdText = myDE.CommandText;
        //                MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Parameters;
        //                PrepareCommand(cmd, conn, tx, cmdText, cmdParms);
        //                if (myDE.EffentNextType == EffentNextType.SolicitationEvent)
        //                {
        //                    if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
        //                    {
        //                        tx.Rollback();
        //                        throw new Exception("违背要求"+myDE.CommandText+"必须符合select count(..的格式");
        //                        //return 0;
        //                    }

        //                    object obj = cmd.ExecuteScalar();
        //                    bool isHave = false;
        //                    if (obj == null && obj == DBNull.Value)
        //                    {
        //                        isHave = false;
        //                    }
        //                    isHave = Convert.ToInt32(obj) > 0;
        //                    if (isHave)
        //                    {
        //                        //引发事件
        //                        myDE.OnSolicitationEvent();
        //                    }
        //                }
        //                if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)
        //                {
        //                    if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
        //                    {
        //                        tx.Rollback();
        //                        throw new Exception("SQL:违背要求" + myDE.CommandText + "必须符合select count(..的格式");
        //                        //return 0;
        //                    }

        //                    object obj = cmd.ExecuteScalar();
        //                    bool isHave = false;
        //                    if (obj == null && obj == DBNull.Value)
        //                    {
        //                        isHave = false;
        //                    }
        //                    isHave = Convert.ToInt32(obj) > 0;

        //                    if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
        //                    {
        //                        tx.Rollback();
        //                        throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须大于0");
        //                        //return 0;
        //                    }
        //                    if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
        //                    {
        //                        tx.Rollback();
        //                        throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须等于0");
        //                        //return 0;
        //                    }
        //                    continue;
        //                }
        //                int val = cmd.ExecuteNonQuery();
        //                if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
        //                {
        //                    tx.Rollback();
        //                    throw new Exception("SQL:违背要求" + myDE.CommandText + "必须有影响行");
        //                    //return 0;
        //                }
        //                cmd.Parameters.Clear();
        //            }
        //            string oraConnectionString = PubConstant.GetConnectionString("ConnectionStringPPC");
        //            bool res = OracleHelper.ExecuteSqlTran(oraConnectionString, oracleCmdSqlList);
        //            if (!res)
        //            {
        //                tx.Rollback();
        //                throw new Exception("执行失败");
        //                // return -1;
        //            }
        //            tx.Commit();
        //            return 1;
        //        }
        //        catch (MySql.Data.MySqlClient.MySqlException e)
        //        {
        //            tx.Rollback();
        //            throw e;
        //        }
        //        catch (Exception e)
        //        {
        //            tx.Rollback();
        //            throw e;
        //        }
        //    }
        //}        
        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>        
        public static int ExecuteSqlTran(List<String> SQLStringList)
        {
            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = conn;
                MySqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    int count = 0;
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n];
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            count += cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                    return count;
                }
                catch
                {
                    tx.Rollback();
                    return 0;
                }
            }
        }
        /// <summary>
        /// 执行带一个存储过程参数的的SQL语句。
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string SQLString, string content)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                MySqlCommand cmd = new MySqlCommand(SQLString, connection);
                MySql.Data.MySqlClient.MySqlParameter myParameter = new MySql.Data.MySqlClient.MySqlParameter("@content", SqlDbType.NText);
                myParameter.Value = content;
                cmd.Parameters.Add(myParameter);
                try
                {
                    connection.Open();
                    int rows = cmd.ExecuteNonQuery();
                    return rows;
                }
                catch (MySql.Data.MySqlClient.MySqlException e)
                {
                    throw e;
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
        }
        /// <summary>
        /// 执行带一个存储过程参数的的SQL语句。
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
        /// <returns>影响的记录数</returns>
        public static object ExecuteSqlGet(string SQLString, string content)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                MySqlCommand cmd = new MySqlCommand(SQLString, connection);
                MySql.Data.MySqlClient.MySqlParameter myParameter = new MySql.Data.MySqlClient.MySqlParameter("@content", SqlDbType.NText);
                myParameter.Value = content;
                cmd.Parameters.Add(myParameter);
                try
                {
                    connection.Open();
                    object obj = cmd.ExecuteScalar();
                    if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                    {
                        return null;
                    }
                    else
                    {
                        return obj;
                    }
                }
                catch (MySql.Data.MySqlClient.MySqlException e)
                {
                    throw e;
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
        }
        /// <summary>
        /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
        /// </summary>
        /// <param name="strSQL">SQL语句</param>
        /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                MySqlCommand cmd = new MySqlCommand(strSQL, connection);
                MySql.Data.MySqlClient.MySqlParameter myParameter = new MySql.Data.MySqlClient.MySqlParameter("@fs", SqlDbType.Image);
                myParameter.Value = fs;
                cmd.Parameters.Add(myParameter);
                try
                {
                    connection.Open();
                    int rows = cmd.ExecuteNonQuery();
                    return rows;
                }
                catch (MySql.Data.MySqlClient.MySqlException e)
                {
                    throw e;
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
        }

        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public static object GetSingle(string SQLString)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                connection.Open();
                using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
                {
                    try
                    {
                    
                        object obj = cmd.ExecuteScalar();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (MySql.Data.MySqlClient.MySqlException e)
                    {
                        connection.Close();
                        throw e;
                    }
                }
            }
        }
        public static object GetSingle(string SQLString, int Times)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        cmd.CommandTimeout = Times;
                        object obj = cmd.ExecuteScalar();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (MySql.Data.MySqlClient.MySqlException e)
                    {
                        connection.Close();
                        throw e;
                    }
                }
            }
        }
        /// <summary>
        /// 执行查询语句，返回MySqlDataReader ( 注意：调用该方法后，一定要对MySqlDataReader进行Close )
        /// </summary>
        /// <param name="strSQL">查询语句</param>
        /// <returns>MySqlDataReader</returns>
        public static MySqlDataReader ExecuteReader(string strSQL)
        {
            MySqlConnection connection = new MySqlConnection(connectionString);
            MySqlCommand cmd = new MySqlCommand(strSQL, connection);
            try
            {
                connection.Open();
                MySqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return myReader;
            }
            catch (MySql.Data.MySqlClient.MySqlException e)
            {
                throw e;
            }

        }
        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet Query(string SQLString)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                connection.Open();
                try
                {
                    using (MySqlCommand cmd=new MySqlCommand(SQLString,connection))
                    {
                        using (MySqlDataAdapter command = new MySqlDataAdapter(cmd))
                        {
                            DataSet ds = new DataSet();
                            command.Fill(ds);
                            return ds;
                        }
                    }
                   
                }
                catch (MySql.Data.MySqlClient.MySqlException ex)
                {
                    throw new Exception(ex.Message);
                }
                
            }
        }
        public static DataSet Query(string SQLString, int Times)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    MySqlDataAdapter command = new MySqlDataAdapter(SQLString, connection);
                    command.SelectCommand.CommandTimeout = Times;
                    command.Fill(ds, "ds");
                }
                catch (MySql.Data.MySqlClient.MySqlException ex)
                {
                    throw new Exception(ex.Message);
                }
                return ds;
            }
        }



        #endregion

        #region 执行带参数的SQL语句

        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string SQLString, params MySqlParameter[] cmdParms)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                using (MySqlCommand cmd = new MySqlCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                        int rows = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                        return rows;
                    }
                    catch (MySql.Data.MySqlClient.MySqlException e)
                    {
                        throw e;
                    }
                }
            }
        }


        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的MySqlParameter[]）</param>
        public static void ExecuteSqlTran(Hashtable SQLStringList)
        {
            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                conn.Open();
                using (MySqlTransaction trans = conn.BeginTransaction())
                {
                    MySqlCommand cmd = new MySqlCommand();
                    try
                    {
                        //循环
                        foreach (DictionaryEntry myDE in SQLStringList)
                        {
                            string cmdText = myDE.Key.ToString();
                            MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Value;
                            PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
                            int val = cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                        }
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
        }
        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的MySqlParameter[]）</param>
        //public static int ExecuteSqlTran(System.Collections.Generic.List<CommandInfo> cmdList)
        //{
        //    using (MySqlConnection conn = new MySqlConnection(connectionString))
        //    {
        //        conn.Open();
        //        using (MySqlTransaction trans = conn.BeginTransaction())
        //        {
        //            MySqlCommand cmd = new MySqlCommand();
        //            try
        //            { int count = 0;
        //                //循环
        //                foreach (CommandInfo myDE in cmdList)
        //                {
        //                    string cmdText = myDE.CommandText;
        //                    MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Parameters;
        //                    PrepareCommand(cmd, conn, trans, cmdText, cmdParms);

        //                    if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)
        //                    {
        //                        if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
        //                        {
        //                            trans.Rollback();
        //                            return 0;
        //                        }

        //                        object obj = cmd.ExecuteScalar();
        //                        bool isHave = false;
        //                        if (obj == null && obj == DBNull.Value)
        //                        {
        //                            isHave = false;
        //                        }
        //                        isHave = Convert.ToInt32(obj) > 0;

        //                        if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
        //                        {
        //                            trans.Rollback();
        //                            return 0;
        //                        }
        //                        if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
        //                        {
        //                            trans.Rollback();
        //                            return 0;
        //                        }
        //                        continue;
        //                    }
        //                    int val = cmd.ExecuteNonQuery();
        //                    count += val;
        //                    if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
        //                    {
        //                        trans.Rollback();
        //                        return 0;
        //                    }
        //                    cmd.Parameters.Clear();
        //                }
        //                trans.Commit();
        //                return count;
        //            }
        //            catch
        //            {
        //                trans.Rollback();
        //                throw;
        //            }
        //        }
        //    }
        //}
        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的MySqlParameter[]）</param>
        //public static void ExecuteSqlTranWithIndentity(System.Collections.Generic.List<CommandInfo> SQLStringList)
        //{
        //    using (MySqlConnection conn = new MySqlConnection(connectionString))
        //    {
        //        conn.Open();
        //        using (MySqlTransaction trans = conn.BeginTransaction())
        //        {
        //            MySqlCommand cmd = new MySqlCommand();
        //            try
        //            {
        //                int indentity = 0;
        //                //循环
        //                foreach (CommandInfo myDE in SQLStringList)
        //                {
        //                    string cmdText = myDE.CommandText;
        //                    MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Parameters;
        //                    foreach (MySqlParameter q in cmdParms)
        //                    {
        //                        if (q.Direction == ParameterDirection.InputOutput)
        //                        {
        //                            q.Value = indentity;
        //                        }
        //                    }
        //                    PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
        //                    int val = cmd.ExecuteNonQuery();
        //                    foreach (MySqlParameter q in cmdParms)
        //                    {
        //                        if (q.Direction == ParameterDirection.Output)
        //                        {
        //                            indentity = Convert.ToInt32(q.Value);
        //                        }
        //                    }
        //                    cmd.Parameters.Clear();
        //                }
        //                trans.Commit();
        //            }
        //            catch
        //            {
        //                trans.Rollback();
        //                throw;
        //            }
        //        }
        //    }
        //}
        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的MySqlParameter[]）</param>
        public static void ExecuteSqlTranWithIndentity(Hashtable SQLStringList)
        {
            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                conn.Open();
                using (MySqlTransaction trans = conn.BeginTransaction())
                {
                    MySqlCommand cmd = new MySqlCommand();
                    try
                    {
                        int indentity = 0;
                        //循环
                        foreach (DictionaryEntry myDE in SQLStringList)
                        {
                            string cmdText = myDE.Key.ToString();
                            MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Value;
                            foreach (MySqlParameter q in cmdParms)
                            {
                                if (q.Direction == ParameterDirection.InputOutput)
                                {
                                    q.Value = indentity;
                                }
                            }
                            PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
                            int val = cmd.ExecuteNonQuery();
                            foreach (MySqlParameter q in cmdParms)
                            {
                                if (q.Direction == ParameterDirection.Output)
                                {
                                    indentity = Convert.ToInt32(q.Value);
                                }
                            }
                            cmd.Parameters.Clear();
                        }
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
        }
        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public static object GetSingle(string SQLString, params MySqlParameter[] cmdParms)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                using (MySqlCommand cmd = new MySqlCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                        object obj = cmd.ExecuteScalar();
                        cmd.Parameters.Clear();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (MySql.Data.MySqlClient.MySqlException e)
                    {
                        throw e;
                    }
                }
            }
        }

        /// <summary>
        /// 执行查询语句，返回MySqlDataReader ( 注意：调用该方法后，一定要对MySqlDataReader进行Close )
        /// </summary>
        /// <param name="strSQL">查询语句</param>
        /// <returns>MySqlDataReader</returns>
        public static MySqlDataReader ExecuteReader(string SQLString, params MySqlParameter[] cmdParms)
        {
            MySqlConnection connection = new MySqlConnection(connectionString);
            MySqlCommand cmd = new MySqlCommand();
            try
            {
                PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                MySqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return myReader;
            }
            catch (MySql.Data.MySqlClient.MySqlException e)
            {
                throw e;
            }
            //            finally
            //            {
            //                cmd.Dispose();
            //                connection.Close();
            //            }    

        }

        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet Query(string SQLString, params MySqlParameter[] cmdParms)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                MySqlCommand cmd = new MySqlCommand();
                PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
                {
                    DataSet ds = new DataSet();
                    try
                    {
                        da.Fill(ds, "ds");
                        cmd.Parameters.Clear();
                    }
                    catch (MySql.Data.MySqlClient.MySqlException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    return ds;
                }
            }
        }


        private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, string cmdText, MySqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;//cmdType;
            if (cmdParms != null)
            {


                foreach (MySqlParameter parameter in cmdParms)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parameter);
                }
            }
        }

        #endregion
    }

web.config

  <connectionStrings>
    <add name="TingZiUnique" connectionString="Data Source=服务器地址;Initial Catalog=AssignSystem;Integrated Security=false;User ID=链接数据库用户名（sa）;Password=链接数据库密码（123456）)!*;MultipleActiveResultSets=True" providerName="System.Data.SqlClient"/>
  </connectionStrings>

其实上面那句是些着玩的小面这句就够了

<connectionStrings>

<add name="TingZiUnique" connectionString="Data Source=.;database=数据库名称;uid=用户名;pwd=密码"/>  

</connectionStrings>

PS：小菜一只请多多指教，可能有错请指出

posted @ 2017-10-20 10:43 TingZi_Unique 阅读(5225) 评论(0) 编辑收藏举报

刷新页面返回顶部

TingZi

越努力,越幸运

VS连接数据库的通用方法（SQL/MySql）

公告