资料来源：不详，请见谅
简易型：
C# DBHelper Code 1 using System; 2 using System.Collections.Generic; 3 using System.Text; 4 using System.Data; 5 using System.Data.SqlClient; 6 using System.Configuration; 7 8 namespace ADODoNETDemo 9 { 10 /// 11 /// 针对SQL Server数据库操作的通用类 12 /// 作者：周公 13 /// 日期：2009-01-08 14 /// Version:1.0 15 /// 16 public class SqlDbHelper 17 { 18 private string connectionString; 19 /// 20 /// 设置数据库连接字符串 21 /// 22 public string ConnectionString 23 { 24 set { connectionString = value; } 25 } 26 /// 27 /// 构造函数 28 /// 29 public SqlDbHelper() 30 : this(ConfigurationManager.ConnectionStrings["Conn"].ConnectionString) 31 { 32 33 } 34 /// 35 /// 构造函数 36 /// 37 /// 数据库连接字符串 38 public SqlDbHelper(string connectionString) 39 { 40 this.connectionString = connectionString; 41 } 42 /// 43 /// 执行一个查询，并返回结果集 44 /// 45 /// 要执行的查询SQL文本命令 46 /// 返回查询结果集 47 public DataTable ExecuteDataTable(string sql) 48 { 49 return ExecuteDataTable(sql, CommandType.Text, null); 50 } 51 /// 52 /// 执行一个查询,并返回查询结果 53 /// 54 /// 要执行的SQL语句 55 /// 要执行的查询语句的类型，如存储过程或者SQL文本命令 56 /// 返回查询结果集 57 public DataTable ExecuteDataTable(string sql, CommandType commandType) 58 { 59 return ExecuteDataTable(sql, commandType, null); 60 } 61 /// 62 /// 执行一个查询,并返回查询结果 63 /// 64 /// 要执行的SQL语句 65 /// 要执行的查询语句的类型，如存储过程或者SQL文本命令 66 /// Transact-SQL 语句或存储过程的参数数组 67 /// 93 /// 94 /// 95 /// 要执行的查询SQL文本命令 96 /// 102 /// 103 /// 104 /// 要执行的SQL语句105 /// 要执行的查询语句的类型，如存储过程或者SQL文本命令106 /// 112 /// 113 /// 114 /// 要执行的SQL语句115 /// 要执行的查询语句的类型，如存储过程或者SQL文本命令116 /// Transact-SQL 语句或存储过程的参数数组117 /// 135 /// 136 /// 137 /// 要执行的查询SQL文本命令138 /// 144 /// 145 /// 146 /// 要执行的SQL语句147 /// 要执行的查询语句的类型，如存储过程或者SQL文本命令148 /// 154 /// 155 /// 156 /// 要执行的SQL语句157 /// 要执行的查询语句的类型，如存储过程或者SQL文本命令158 /// Transact-SQL 语句或存储过程的参数数组159 /// 183 /// 对数据库执行增删改操作184 /// 185 /// 要执行的查询SQL文本命令186 /// 192 /// 对数据库执行增删改操作193 /// 194 /// 要执行的SQL语句195 /// 要执行的查询语句的类型，如存储过程或者SQL文本命令196 /// 202 /// 对数据库执行增删改操作203 /// 204 /// 要执行的SQL语句205 /// 要执行的查询语句的类型，如存储过程或者SQL文本命令206 /// Transact-SQL 语句或存储过程的参数数组207 /// 231 /// 返回当前连接的数据库中所有由用户创建的数据库232 /// 233 /// 复杂型：
C# DBUtility Code 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 10 using TL.DataAccess; 11 12 namespace TL.Business 13 { 14 public enum EffentNextType 15 { 16 /// 17 /// 对其他语句无任何影响 18 /// 19 None, 20 /// 21 /// 当前语句必须为"SELECT COUNT(1) FROM .."格式，如果存在则继续执行，不存在回滚事务 22 /// 23 WhenHaveContine, 24 /// 25 /// 当前语句必须为"SELECT COUNT(1) FROM .."格式，如果不存在则继续执行，存在回滚事务 26 /// 27 WhenNoHaveContine, 28 /// 29 /// 当前语句影响到的行数必须大于0，否则回滚事务 30 /// 31 ExcuteEffectRows, 32 /// 33 /// 引发事件-当前语句必须为"SELECT COUNT(1) FROM .."格式，如果不存在则继续执行，存在回滚事务 34 /// 35 SolicitationEvent 36 } 37 public class DBUtility 38 { 39 public object ShareObject = null; 40 public object OriginalData = null; 41 event EventHandler _solicitationEvent; 42 public event EventHandler SolicitationEvent 43 { 44 add 45 { 46 _solicitationEvent += value; 47 } 48 remove 49 { 50 _solicitationEvent -= value; 51 } 52 } 53 public void OnSolicitationEvent() 54 { 55 if (_solicitationEvent != null) 56 { 57 _solicitationEvent(this,new EventArgs()); 58 } 59 } 60 public string CommandText; 61 public System.Data.Common.DbParameter[] Parameters; 62 public EffentNextType EffentNextType = EffentNextType.None; 63 public DBUtility() 64 { 65 } 66 public DBUtility(SqlParameter[] para) 67 { 68 this.Parameters = para; 69 } 70 public DBUtility(string sqlText, SqlParameter[] para) 71 { 72 this.CommandText = sqlText; 73 this.Parameters = para; 74 } 75 public DBUtility(string sqlText, SqlParameter[] para, EffentNextType type) 76 { 77 this.CommandText = sqlText; 78 this.Parameters = para; 79 this.EffentNextType = type; 80 } 81 } 82 /// 83 /// 数据访问抽象基础类 84 /// Copyright (C) 2004-2008 By LiTianPing 85 /// 86 public class SQLHelper 87 { 88 //数据库连接字符串(web.config来配置)，可以动态更改connectionString支持多数据库. 89 public string connectionString = ""; 90 public SQLHelper() 91 { 92 connectionString = @"DATA SOURCE=192.168.0.103,2433;UID=cdteam;PWD=cd-team2011_;DATABASE=TongLing"; 93 } 94 #region 公用方法 95 /// 96 /// 读取指定图片的二进制信息 97 /// 98 /// 99 /// 122 /// 判断是否存在某表的某个字段 123 /// 124 /// 表名称 125 /// 列名称 126 /// 是否存在 127 public bool ColumnExists(string tableName, string columnName) 128 { 129 string sql = "select count(1) from syscolumns where id=object_id(" + tableName + ") and name=" + columnName + ""; 130 object res = GetSingle(sql); 131 if (res == null) 132 { 133 return false; 134 } 135 return Convert.ToInt32(res) > 0; 136 } 137 public int GetMaxID(string FieldName, string TableName) 138 { 139 string strsql = "select max(" + FieldName + ")+1 from " + TableName; 140 object obj = this.GetSingle(strsql); 141 if (obj == null) 142 { 143 return 1; 144 } 145 else 146 { 147 return int.Parse(obj.ToString()); 148 } 149 } 150 public bool Exists(string strSql) 151 { 152 object obj = this.GetSingle(strSql); 153 int cmdresult; 154 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) 155 { 156 cmdresult = 0; 157 } 158 else 159 { 160 cmdresult = int.Parse(obj.ToString()); 161 } 162 if (cmdresult == 0) 163 { 164 return false; 165 } 166 else 167 { 168 return true; 169 } 170 } 171 /// 172 /// 表是否存在 173 /// 174 /// 175 /// 223 /// 执行SQL语句，返回影响的记录数 224 /// 225 /// SQL语句 226 /// 影响的记录数 227 public int ExecuteSql(string SQLString) 228 { 229 using (SqlConnection connection = new SqlConnection(connectionString)) 230 { 231 using (SqlCommand cmd = new SqlCommand(SQLString, connection)) 232 { 233 try 234 { 235 connection.Open(); 236 int rows = cmd.ExecuteNonQuery(); 237 return rows; 238 } 239 catch (System.Data.SqlClient.SqlException e) 240 { 241 connection.Close(); 242 throw e; 243 } 244 } 245 } 246 } 247 public int ExecuteSqlByTime(string SQLString, int Times) 248 { 249 using (SqlConnection connection = new SqlConnection(connectionString)) 250 { 251 using (SqlCommand cmd = new SqlCommand(SQLString, connection)) 252 { 253 try 254 { 255 connection.Open(); 256 cmd.CommandTimeout = Times; 257 int rows = cmd.ExecuteNonQuery(); 258 return rows; 259 } 260 catch (System.Data.SqlClient.SqlException e) 261 { 262 connection.Close(); 263 throw e; 264 } 265 } 266 } 267 } 268 /// 269 /// 执行Sql和Oracle滴混合事务 270 /// 271 /// SQL命令行列表 272 /// Oracle命令行列表 273 /// 执行结果 0-由于SQL造成事务失败 -1 由于Oracle造成事务失败 1-整体事务执行成功 274 public int ExecuteSqlTran(List list, List oracleCmdSqlList) 275 { 276 using (SqlConnection conn = new SqlConnection(connectionString)) 277 { 278 conn.Open(); 279 SqlCommand cmd = new SqlCommand(); 280 cmd.Connection = conn; 281 SqlTransaction tx = conn.BeginTransaction(); 282 cmd.Transaction = tx; 283 try 284 { 285 foreach (DBUtility myDE in list) 286 { 287 string cmdText = myDE.CommandText; 288 SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters; 289 PrepareCommand(cmd, conn, tx, cmdText, cmdParms); 290 if (myDE.EffentNextType == EffentNextType.SolicitationEvent) 291 { 292 if (myDE.CommandText.ToLower().IndexOf("count(") == -1) 293 { 294 tx.Rollback(); 295 throw new Exception("违背要求" + myDE.CommandText + "必须符合select count(..的格式"); 296 //return 0; 297 } 298 object obj = cmd.ExecuteScalar(); 299 bool isHave = false; 300 if (obj == null && obj == DBNull.Value) 301 { 302 isHave = false; 303 } 304 isHave = Convert.ToInt32(obj) > 0; 305 if (isHave) 306 { 307 //引发事件 308 myDE.OnSolicitationEvent(); 309 } 310 } 311 if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine) 312 { 313 if (myDE.CommandText.ToLower().IndexOf("count(") == -1) 314 { 315 tx.Rollback(); 316 throw new Exception("SQL:违背要求" + myDE.CommandText + "必须符合select count(..的格式"); 317 //return 0; 318 } 319 object obj = cmd.ExecuteScalar(); 320 bool isHave = false; 321 if (obj == null && obj == DBNull.Value) 322 { 323 isHave = false; 324 } 325 isHave = Convert.ToInt32(obj) > 0; 326 if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave) 327 { 328 tx.Rollback(); 329 throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须大于0"); 330 //return 0; 331 } 332 if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave) 333 { 334 tx.Rollback(); 335 throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须等于0"); 336 //return 0; 337 } 338 continue; 339 } 340 int val = cmd.ExecuteNonQuery(); 341 if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0) 342 { 343 tx.Rollback(); 344 throw new Exception("SQL:违背要求" + myDE.CommandText + "必须有影响行"); 345 //return 0; 346 } 347 cmd.Parameters.Clear(); 348 } 349 //string oraConnectionString = PubConstant.GetConnectionString("ConnectionStringPPC"); 350 //bool res = OracleHelper.ExecuteSqlTran(oraConnectionString, oracleCmdSqlList); 351 //if (!res) 352 //{ 353 // tx.Rollback(); 354 // throw new Exception("Oracle执行失败"); 355 // return -1; 356 //} 357 tx.Commit(); 358 return 1; 359 } 360 catch (System.Data.SqlClient.SqlException e) 361 { 362 tx.Rollback(); 363 throw e; 364 } 365 catch (Exception e) 366 { 367 tx.Rollback(); 368 throw e; 369 } 370 } 371 } 372 /// 373 /// 执行多条SQL语句，实现数据库事务。 374 /// 375 /// 多条SQL语句 376 public int ExecuteSqlTran(List SQLStringList) 377 { 378 using (SqlConnection conn = new SqlConnection(connectionString)) 379 { 380 conn.Open(); 381 SqlCommand cmd = new SqlCommand(); 382 cmd.Connection = conn; 383 SqlTransaction tx = conn.BeginTransaction(); 384 cmd.Transaction = tx; 385 try 386 { 387 int count = 0; 388 for (int n = 0; n < SQLStringList.Count; n++) 389 { 390 string strsql = SQLStringList[n]; 391 if (strsql.Trim().Length > 1) 392 { 393 cmd.CommandText = strsql; 394 count += cmd.ExecuteNonQuery(); 395 } 396 } 397 tx.Commit(); 398 return count; 399 } 400 catch 401 { 402 tx.Rollback(); 403 return 0; 404 } 405 } 406 } 407 /// 408 /// 执行带一个存储过程参数的的SQL语句。 409 /// 410 /// SQL语句 411 /// 参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加 412 /// 影响的记录数 413 public int ExecuteSql(string SQLString, string content) 414 { 415 using (SqlConnection connection = new SqlConnection(connectionString)) 416 { 417 SqlCommand cmd = new SqlCommand(SQLString, connection); 418 System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText); 419 myParameter.Value = content; 420 cmd.Parameters.Add(myParameter); 421 try 422 { 423 connection.Open(); 424 int rows = cmd.ExecuteNonQuery(); 425 return rows; 426 } 427 catch (System.Data.SqlClient.SqlException e) 428 { 429 throw e; 430 } 431 finally 432 { 433 cmd.Dispose(); 434 connection.Close(); 435 } 436 } 437 } 438 /// 439 /// 执行带一个存储过程参数的的SQL语句。 440 /// 441 /// SQL语句 442 /// 参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加 443 /// 影响的记录数 444 public object ExecuteSqlGet(string SQLString, string content) 445 { 446 using (SqlConnection connection = new SqlConnection(connectionString)) 447 { 448 SqlCommand cmd = new SqlCommand(SQLString, connection); 449 System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText); 450 myParameter.Value = content; 451 cmd.Parameters.Add(myParameter); 452 try 453 { 454 connection.Open(); 455 object obj = cmd.ExecuteScalar(); 456 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) 457 { 458 return null; 459 } 460 else 461 { 462 return obj; 463 } 464 } 465 catch (System.Data.SqlClient.SqlException e) 466 { 467 throw e; 468 } 469 finally 470 { 471 cmd.Dispose(); 472 connection.Close(); 473 } 474 } 475 } 476 /// 477 /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例) 478 /// 479 /// SQL语句 480 /// 图像字节,数据库的字段类型为image的情况 481 /// 影响的记录数 482 public int ExecuteSqlInsertImg(string strSQL, byte[] fs) 483 { 484 using (SqlConnection connection = new SqlConnection(connectionString)) 485 { 486 SqlCommand cmd = new SqlCommand(strSQL, connection); 487 System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@fs", SqlDbType.Image); 488 myParameter.Value = fs; 489 cmd.Parameters.Add(myParameter); 490 try 491 { 492 connection.Open(); 493 int rows = cmd.ExecuteNonQuery(); 494 return rows; 495 } 496 catch (System.Data.SqlClient.SqlException e) 497 { 498 throw e; 499 } 500 finally 501 { 502 cmd.Dispose(); 503 connection.Close(); 504 } 505 } 506 } 507 /// 508 /// 执行一条计算查询结果语句，返回查询结果（object）。 509 /// 510 /// 计算查询结果语句 511 /// 查询结果（object） 512 public object GetSingle(string SQLString) 513 { 514 using (SqlConnection connection = new SqlConnection(connectionString)) 515 { 516 using (SqlCommand cmd = new SqlCommand(SQLString, connection)) 517 { 518 try 519 { 520 connection.Open(); 521 object obj = cmd.ExecuteScalar(); 522 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) 523 { 524 return null; 525 } 526 else 527 { 528 return obj; 529 } 530 } 531 catch (System.Data.SqlClient.SqlException e) 532 { 533 connection.Close(); 534 throw e; 535 } 536 } 537 } 538 } 539 public object GetSingle(string SQLString, int Times) 540 { 541 using (SqlConnection connection = new SqlConnection(connectionString)) 542 { 543 using (SqlCommand cmd = new SqlCommand(SQLString, connection)) 544 { 545 try 546 { 547 connection.Open(); 548 cmd.CommandTimeout = Times; 549 object obj = cmd.ExecuteScalar(); 550 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) 551 { 552 return null; 553 } 554 else 555 { 556 return obj; 557 } 558 } 559 catch (System.Data.SqlClient.SqlException e) 560 { 561 connection.Close(); 562 throw e; 563 } 564 } 565 } 566 } 567 /// 568 /// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close ) 569 /// 570 /// 查询语句 571 /// SqlDataReader 572 public SqlDataReader ExecuteReader(string strSQL) 573 { 574 SqlConnection connection = new SqlConnection(connectionString); 575 SqlCommand cmd = new SqlCommand(strSQL, connection); 576 try 577 { 578 connection.Open(); 579 SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection); 580 return myReader; 581 } 582 catch (System.Data.SqlClient.SqlException e) 583 { 584 throw e; 585 } 586 } 587 /// 588 /// 执行查询语句，返回DataSet 589 /// 590 /// 查询语句 591 /// DataSet 592 public DataSet Query(string SQLString) 593 { 594 using (SqlConnection connection = new SqlConnection(connectionString)) 595 { 596 DataSet ds = new DataSet(); 597 try 598 { 599 connection.Open(); 600 SqlDataAdapter command = new SqlDataAdapter(SQLString, connection); 601 command.Fill(ds, "ds"); 602 } 603 catch (System.Data.SqlClient.SqlException ex) 604 { 605 throw new Exception(ex.Message); 606 } 607 return ds; 608 } 609 } 610 /// 611 /// 查询并得到数据集DataSet 612 /// 613 /// 614 /// 615 /// 638 /// 执行SQL语句，返回影响的记录数 639 /// 640 /// SQL语句 641 /// 影响的记录数 642 public int ExecuteSql(string SQLString, params SqlParameter[] cmdParms) 643 { 644 using (SqlConnection connection = new SqlConnection(connectionString)) 645 { 646 using (SqlCommand cmd = new SqlCommand()) 647 { 648 try 649 { 650 PrepareCommand(cmd, connection, null, SQLString, cmdParms); 651 int rows = cmd.ExecuteNonQuery(); 652 cmd.Parameters.Clear(); 653 return rows; 654 } 655 catch (System.Data.SqlClient.SqlException e) 656 { 657 throw e; 658 } 659 } 660 } 661 } 662 /// 663 /// 执行多条SQL语句，实现数据库事务。 664 /// 665 /// SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]） 666 public void ExecuteSqlTran(Hashtable SQLStringList) 667 { 668 using (SqlConnection conn = new SqlConnection(connectionString)) 669 { 670 conn.Open(); 671 using (SqlTransaction trans = conn.BeginTransaction()) 672 { 673 SqlCommand cmd = new SqlCommand(); 674 try 675 { 676 //循环 677 foreach (DictionaryEntry myDE in SQLStringList) 678 { 679 string cmdText = myDE.Key.ToString(); 680 SqlParameter[] cmdParms = (SqlParameter[])myDE.Value; 681 PrepareCommand(cmd, conn, trans, cmdText, cmdParms); 682 int val = cmd.ExecuteNonQuery(); 683 cmd.Parameters.Clear(); 684 } 685 trans.Commit(); 686 } 687 catch 688 { 689 trans.Rollback(); 690 throw; 691 } 692 } 693 } 694 } 695 /// 696 /// 执行多条SQL语句，实现数据库事务。 697 /// 698 /// SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]） 699 public int ExecuteSqlTran(System.Collections.Generic.List cmdList) 700 { 701 using (SqlConnection conn = new SqlConnection(connectionString)) 702 { 703 conn.Open(); 704 using (SqlTransaction trans = conn.BeginTransaction()) 705 { 706 SqlCommand cmd = new SqlCommand(); 707 try 708 { 709 int count = 0; 710 //循环 711 foreach (DBUtility myDE in cmdList) 712 { 713 string cmdText = myDE.CommandText; 714 SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters; 715 PrepareCommand(cmd, conn, trans, cmdText, cmdParms); 716 if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine) 717 { 718 if (myDE.CommandText.ToLower().IndexOf("count(") == -1) 719 { 720 trans.Rollback(); 721 return 0; 722 } 723 object obj = cmd.ExecuteScalar(); 724 bool isHave = false; 725 if (obj == null && obj == DBNull.Value) 726 { 727 isHave = false; 728 } 729 isHave = Convert.ToInt32(obj) > 0; 730 if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave) 731 { 732 trans.Rollback(); 733 return 0; 734 } 735 if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave) 736 { 737 trans.Rollback(); 738 return 0; 739 } 740 continue; 741 } 742 int val = cmd.ExecuteNonQuery(); 743 count += val; 744 if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0) 745 { 746 trans.Rollback(); 747 return 0; 748 } 749 cmd.Parameters.Clear(); 750 } 751 trans.Commit(); 752 return count; 753 } 754 catch 755 { 756 trans.Rollback(); 757 throw; 758 } 759 } 760 } 761 } 762 /// 763 /// 执行多条SQL语句，实现数据库事务。 764 /// 765 /// SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]） 766 public void ExecuteSqlTranWithIndentity(System.Collections.Generic.List SQLStringList) 767 { 768 using (SqlConnection conn = new SqlConnection(connectionString)) 769 { 770 conn.Open(); 771 using (SqlTransaction trans = conn.BeginTransaction()) 772 { 773 SqlCommand cmd = new SqlCommand(); 774 try 775 { 776 int indentity = 0; 777 //循环 778 foreach (DBUtility myDE in SQLStringList) 779 { 780 string cmdText = myDE.CommandText; 781 SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters; 782 foreach (SqlParameter q in cmdParms) 783 { 784 if (q.Direction == ParameterDirection.InputOutput) 785 { 786 q.Value = indentity; 787 } 788 } 789 PrepareCommand(cmd, conn, trans, cmdText, cmdParms); 790 int val = cmd.ExecuteNonQuery(); 791 foreach (SqlParameter q in cmdParms) 792 { 793 if (q.Direction == ParameterDirection.Output) 794 { 795 indentity = Convert.ToInt32(q.Value); 796 } 797 } 798 cmd.Parameters.Clear(); 799 } 800 trans.Commit(); 801 } 802 catch 803 { 804 trans.Rollback(); 805 throw; 806 } 807 } 808 } 809 } 810 /// 811 /// 执行多条SQL语句，实现数据库事务。 812 /// 813 /// SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]） 814 public void ExecuteSqlTranWithIndentity(Hashtable SQLStringList) 815 { 816 using (SqlConnection conn = new SqlConnection(connectionString)) 817 { 818 conn.Open(); 819 using (SqlTransaction trans = conn.BeginTransaction()) 820 { 821 SqlCommand cmd = new SqlCommand(); 822 try 823 { 824 int indentity = 0; 825 //循环 826 foreach (DictionaryEntry myDE in SQLStringList) 827 { 828 string cmdText = myDE.Key.ToString(); 829 SqlParameter[] cmdParms = (SqlParameter[])myDE.Value; 830 foreach (SqlParameter q in cmdParms) 831 { 832 if (q.Direction == ParameterDirection.InputOutput) 833 { 834 q.Value = indentity; 835 } 836 } 837 PrepareCommand(cmd, conn, trans, cmdText, cmdParms); 838 int val = cmd.ExecuteNonQuery(); 839 foreach (SqlParameter q in cmdParms) 840 { 841 if (q.Direction == ParameterDirection.Output) 842 { 843 indentity = Convert.ToInt32(q.Value); 844 } 845 } 846 cmd.Parameters.Clear(); 847 } 848 trans.Commit(); 849 } 850 catch 851 { 852 trans.Rollback(); 853 throw; 854 } 855 } 856 } 857 } 858 /// 859 /// 执行一条计算查询结果语句，返回查询结果（object）。 860 /// 861 /// 计算查询结果语句 862 /// 查询结果（object） 863 public object GetSingle(string SQLString, params SqlParameter[] cmdParms) 864 { 865 using (SqlConnection connection = new SqlConnection(connectionString)) 866 { 867 using (SqlCommand cmd = new SqlCommand()) 868 { 869 try 870 { 871 PrepareCommand(cmd, connection, null, SQLString, cmdParms); 872 object obj = cmd.ExecuteScalar(); 873 cmd.Parameters.Clear(); 874 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) 875 { 876 return null; 877 } 878 else 879 { 880 return obj; 881 } 882 } 883 catch (System.Data.SqlClient.SqlException e) 884 { 885 throw e; 886 } 887 } 888 } 889 } 890 /// 891 /// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close ) 892 /// 893 /// 查询语句 894 /// SqlDataReader 895 public SqlDataReader ExecuteReader(string SQLString, params SqlParameter[] cmdParms) 896 { 897 SqlConnection connection = new SqlConnection(connectionString); 898 SqlCommand cmd = new SqlCommand(); 899 try 900 { 901 PrepareCommand(cmd, connection, null, SQLString, cmdParms); 902 SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection); 903 cmd.Parameters.Clear(); 904 return myReader; 905 } 906 catch (System.Data.SqlClient.SqlException e) 907 { 908 throw e; 909 } 910 // finally 911 // { 912 // cmd.Dispose(); 913 // connection.Close(); 914 // } 915 } 916 /// 917 /// 执行查询语句，返回DataSet 918 /// 919 /// 查询语句 920 /// DataSet 921 public DataSet Query(string SQLString, params SqlParameter[] cmdParms) 922 { 923 using (SqlConnection connection = new SqlConnection(connectionString)) 924 { 925 SqlCommand cmd = new SqlCommand(); 926 PrepareCommand(cmd, connection, null, SQLString, cmdParms); 927 using (SqlDataAdapter da = new SqlDataAdapter(cmd)) 928 { 929 DataSet ds = new DataSet(); 930 try 931 { 932 da.Fill(ds, "ds"); 933 cmd.Parameters.Clear(); 934 } 935 catch (System.Data.SqlClient.SqlException ex) 936 { 937 throw new Exception(ex.Message); 938 } 939 return ds; 940 } 941 } 942 } 943 private void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms) 944 { 945 if (conn.State != ConnectionState.Open) 946 conn.Open(); 947 cmd.Connection = conn; 948 cmd.CommandText = cmdText; 949 if (trans != null) 950 cmd.Transaction = trans; 951 cmd.CommandType = CommandType.Text;//cmdType; 952 if (cmdParms != null) 953 { 954 foreach (SqlParameter parameter in cmdParms) 955 { 956 if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && 957 (parameter.Value == null)) 958 { 959 parameter.Value = DBNull.Value; 960 } 961 cmd.Parameters.Add(parameter); 962 } 963 } 964 } 965 #endregion 966 #region 存储过程操作 967 /// 968 /// 执行存储过程，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close ) 969 /// 970 /// 存储过程名 971 /// 存储过程参数 972 /// SqlDataReader 973 public SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters) 974 { 975 SqlConnection connection = new SqlConnection(connectionString); 976 SqlDataReader returnReader; 977 connection.Open(); 978 SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters); 979 command.CommandType = CommandType.StoredProcedure; 980 returnReader = command.ExecuteReader(CommandBehavior.CloseConnection); 981 return returnReader; 982 } 983 /// 984 /// 执行存储过程 985 /// 986 /// 存储过程名 987 /// 存储过程参数 988 /// DataSet结果中的表名 989 /// DataSet 990 public DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName) 991 { 992 using (SqlConnection connection = new SqlConnection(connectionString)) 993 { 994 DataSet dataSet = new DataSet(); 995 connection.Open(); 996 SqlDataAdapter sqlDA = new SqlDataAdapter(); 997 sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters); 998 sqlDA.Fill(dataSet, tableName); 999 connection.Close(); 1000 return dataSet; 1001 } 1002 }1003 1004 public DataSet RunProcedure2(string storedProcName, string tableName)1005 {1006 using (SqlConnection connection = new SqlConnection(connectionString))1007 {1008 DataSet dataSet = new DataSet();1009 connection.Open();1010 SqlDataAdapter sqlDA = new SqlDataAdapter();1011 sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName);1012 sqlDA.Fill(dataSet, tableName);1013 connection.Close();1014 return dataSet;1015 }1016 }1017 public DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, int Times) 1018 { 1019 using (SqlConnection connection = new SqlConnection(connectionString)) 1020 { 1021 DataSet dataSet = new DataSet(); 1022 connection.Open(); 1023 SqlDataAdapter sqlDA = new SqlDataAdapter(); 1024 sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters); 1025 sqlDA.SelectCommand.CommandTimeout = Times; 1026 sqlDA.Fill(dataSet, tableName); 1027 connection.Close(); 1028 return dataSet; 1029 } 1030 } 1031 /// 1032 /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值) 1033 /// 1034 /// 数据库连接 1035 /// 存储过程名 1036 /// 存储过程参数 1037 /// SqlCommand 1038 private SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters) 1039 { 1040 SqlCommand command = new SqlCommand(storedProcName, connection); 1041 command.CommandType = CommandType.StoredProcedure; 1042 foreach (SqlParameter parameter in parameters) 1043 { 1044 if (parameter != null) 1045 { 1046 // 检查未分配值的输出参数,将其分配以DBNull.Value. 1047 if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && 1048 (parameter.Value == null)) 1049 { 1050 parameter.Value = DBNull.Value; 1051 } 1052 command.Parameters.Add(parameter); 1053 } 1054 } 1055 return command; 1056 }1057 private SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName)1058 {1059 SqlCommand command = new SqlCommand(storedProcName, connection);1060 command.CommandType = CommandType.StoredProcedure; 1061 return command;1062 }1063 /// 1064 /// 执行存储过程，返回影响的行数 1065 /// 1066 /// 存储过程名 1067 /// 存储过程参数 1068 /// 影响的行数 1069 /// 1112 /// 创建 SqlCommand 对象实例(用来返回一个整数值) 1113 /// 1114 /// 存储过程名 1115 /// 存储过程参数 1116 /// SqlCommand 对象实例 1117 private SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters) 1118 { 1119 SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters); 1120 command.Parameters.Add(new SqlParameter("ReturnValue", 1121 SqlDbType.Int, 4, ParameterDirection.ReturnValue, 1122 false, 0, 0, string.Empty, DataRowVersion.Default, null)); 1123 return command; 1124 } 1125 #endregion 1126 } 1127 }
posted on 2012-05-09 21:53 阅读( ...) 评论( ...)