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