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的时候第一次也可以，第二次出现异常，而且数据也加不上去，这是什么原因，求大神解决！小弟初学，很多不懂
最佳答案
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
其他回答(1)
0
代码的重用也不是这样的呀，同一个SqlTransaction实例用多次，是不允许的，事物一旦BeginTransaction或Commit或Rollback。
收获园豆：5
请问一下哦！同一个con的连接对象 trans1 = Connection.BeginTransaction();
trans2 = Connection.BeginTransaction();得到的是同一个Trancation么
@学海无涯_小波: 是的，同一个sqhelper对象得到的也是同一个Trancation，最好是每次的操作都用新的conn和tran
