首页新闻找找看学习计划

C#关于自己封装的SQLHelper

0
悬赏园豆:20 [已解决问题] 解决于 2013-05-23 23:46

下面这个sqlhelper第一次执行数据库的CURD都可以,第二次就不行了,好像是事务重复提交导致的,可能是我把Connection和Transcation都写成单列的问题,求大神们指点修改修改!非常感谢

        private readonly string constr = ConfigurationManager.ConnectionStrings["sql"].ConnectionString;

        private SqlConnection Connection;

        private SqlTransaction trans;

        //得到连接字符窜
        public SqlConnection getConntion()
        {
            if (Connection == null)
            {
                Connection = new SqlConnection(constr);
            }
            return Connection;
        }
        //关闭连接字符串
        public void conntionClose()
        {
            if (getConntion().State == ConnectionState.Open)
            {
                getConntion().Close();
            }
        }

        public SqlTransaction getTransaction()
        {
            if (getConntion().State != ConnectionState.Open)
                getConntion().Open();
            if (trans == null)
            {
                trans = Connection.BeginTransaction();
            }
            return trans;
        }

        public void TranscationCommit()
        {
            getTransaction().Commit();          
        }


        public void TranscationRollBack()
        {
            getTransaction().Rollback();
        }
        public int ExecuteNonQuery(string sql, params SqlParameter[] pms)
        {
            SqlConnection con = getConntion();
            if (con.State != ConnectionState.Open)
            {
                con.Open();
            }
            SqlCommand cmd = new SqlCommand(sql, con);
            cmd.Transaction = getTransaction();
            if (pms != null)
            {
                cmd.Parameters.AddRange(pms);
            }
            return cmd.ExecuteNonQuery();

        }

我调用这个方法

try

{

......执行CURD

sqlhelper.TranscationCommit();

  sqlhelper.getConnetion().close();

}

catch

 sqlhelper.TranscationRollback();

 

第一次能正常执行,第二次出现异常,但数据可以增加上去。我加上我在try后面增加sqlhelper.getTranscation.Dispose的时候第一次也可以,第二次出现异常,而且数据也加不上去,这是什么原因,求大神解决!小弟初学,很多不懂

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

FYI~

收获园豆:10
数据酷软件 | 初学一级 |园豆:41 | 2013-05-23 16:33
其他回答(2)
0

trans 使用的时候new。后面得到的trans虽然不为空,但是数据因为之前提交了,后续的再次提交就会抛出数据库异常。

推荐使用微软官方的或者一些大牛写的一些sqlhelper。比如discuz!nt的这些数据库帮助类。这代代码问题实在太多了。

收获园豆:5
````` | 园豆:14268 (专家六级) | 2013-05-23 09:49

请问一下哦!同一个con的连接对象 trans1 = Connection.BeginTransaction();

 trans2 = Connection.BeginTransaction();得到的是同一个Trancation么

支持(0) 反对(0) 学海无涯_小波 | 园豆:162 (初学一级) | 2013-05-23 12:24

@学海无涯_小波: 是同一个。用过了之后 这个对象是不能够再此使用的。

支持(1) 反对(0) ````` | 园豆:14268 (专家六级) | 2013-05-23 12:53
0

代码的重用也不是这样的呀,同一个SqlTransaction实例用多次,是不允许的,事物一旦BeginTransaction或Commit或Rollback。

收获园豆:5
yyutudou | 园豆:997 (小虾三级) | 2013-05-23 10:32

请问一下哦!同一个con的连接对象 trans1 = Connection.BeginTransaction();

 trans2 = Connection.BeginTransaction();得到的是同一个Trancation么

支持(0) 反对(0) 学海无涯_小波 | 园豆:162 (初学一级) | 2013-05-23 12:24

@学海无涯_小波: 是的,同一个sqhelper对象得到的也是同一个Trancation,最好是每次的操作都用新的conn和tran

支持(0) 反对(0) yyutudou | 园豆:997 (小虾三级) | 2013-05-23 12:32
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册