SqlHelper工具类

 1  public class SqlHlper
 2         {
 3             public static readonly string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
 4             //执行增删改
 5             public static object ExecuteNonQuery(string sql, params SqlParameter[] pms)//SqlParameter需要调用Data.SqlClient
 6             {
 7                 using (SqlConnection con = new SqlConnection(constr))
 8                 {
 9                     using (SqlCommand cmd = new SqlCommand(sql, con))
10                     {
11                         if (pms != null)
12                         {
13                             cmd.Parameters.AddRange(pms);
14                         }
15                         con.Open();
16                         return cmd.ExecuteNonQuery();
17 
18                     }
19                 }
20             }
21             //获取单个数据
22             public static object ExecteScalar(string sql, params SqlParameter[] pms)
23             {
24                 object obj = null;
25                 using (SqlConnection conn = new SqlConnection(constr))
26                 {
27                     SqlCommand cmd = new SqlCommand(sql, conn);
28                     conn.Open();
29                     if (pms != null)
30                     {
31                         cmd.Parameters.AddRange(pms);
32                     }
33                     obj = cmd.ExecuteScalar();
34                 }
35                 return obj;
36             }
37             //获取多个数据
38             public static SqlDataReader GetDataReader(string sql, params SqlParameter[] sps)
39             {
40                 SqlConnection conn = new SqlConnection(constr);
41                 using (SqlCommand cmd = new SqlCommand(sql, conn))
42                 {
43                     if (sps != null)
44                     {
45                         cmd.Parameters.AddRange(sps);
46                     }
47                     conn.Open();
48                     return cmd.ExecuteReader(CommandBehavior.CloseConnection);
49                 }
50             }
51             //查询多条数据
52             public static DataTable GetDataTable(string sql, params SqlParameter[] sps)
53             {
54                 DataTable dt = new DataTable();
55                 using (SqlDataAdapter da = new SqlDataAdapter(sql, constr))
56                 {
57                     if (sps != null)
58                     {
59                         da.SelectCommand.Parameters.AddRange(sps);
60                     }
61                     da.Fill(dt);
62                 }
63                 return dt;
64             }
65         }

 1   /// <summary>
 2         /// 把对象序列化 JSON 字符串 
 3         /// </summary>
 4         /// <typeparam name="T">对象类型</typeparam>
 5         /// <param name="obj">对象实体</param>
 6         /// <returns>JSON字符串</returns>
 7         public static string GetJson<T>(T obj)
 8         {
 9             DataContractJsonSerializer json = new DataContractJsonSerializer(typeof(T));
10             using (MemoryStream ms = new MemoryStream())
11             {
12                 json.WriteObject(ms, obj);
13                 string szJson = Encoding.UTF8.GetString(ms.ToArray());
14                 return szJson;
15             }
16         }
17         /// <summary>  
18         /// 256位AES加密  
19         /// </summary>  
20         /// <param name="toEncrypt"></param>  
21         /// <returns></returns> 
22         public static string Encrypt(string toEncrypt, string skey, string IV)
23         {
24             //256-AES key      
25             //byte[] keyArray = UTF8Encoding.UTF8.GetBytes("12345678123456781234567812345678");
26             byte[] keyArray = UTF8Encoding.UTF8.GetBytes(skey);
27             byte[] toEncryptArray = UTF8Encoding.UTF8.GetBytes(toEncrypt);
28             byte[] ivArray = UTF8Encoding.UTF8.GetBytes(IV);//1234567812345678
29 
30             RijndaelManaged rDel = new RijndaelManaged();
31             rDel.Key = keyArray;
32             rDel.Mode = CipherMode.CBC;
33             rDel.Padding = PaddingMode.PKCS7;
34             rDel.IV = ivArray;
35 
36             ICryptoTransform cTransform = rDel.CreateEncryptor();
37             byte[] resultArray = cTransform.TransformFinalBlock(toEncryptArray, 0, toEncryptArray.Length);
38 
39             return Convert.ToBase64String(resultArray, 0, resultArray.Length);
40         }
41         ///保留小数       
42         public static double Change(double data, int num)
43         {
44             string str1 = "";
45             if (num == 2)
46             {
47                 str1 = String.Format("{0:F2}", data);//默认为保留两位
48             }
49             else if (num == 8)
50             {
51                 str1 = String.Format("{0:F8}", data);//默认为保留8位
52 
53             }
54             return Convert.ToDouble(str1);
55         }

View Code

 1 /// <summary>
 2         /// 随机产生10位数
 3         /// </summary>
 4         /// <returns></returns>
 5         public static string GenerateRandomCode()
 6         {
 7             string chars = "0123456789abcdefghijklmnopqrstuvwxyz";
 8             Random randrom = new Random((int)DateTime.Now.Ticks);
 9             string str = "";
10             for (int i = 0; i < 10; i++)
11             {
12                 str += chars[randrom.Next(chars.Length)];
13             }
14             return str;
15         }

随机产生10位数

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

View Code