FoolWeb.DateEngine 數據庫操作類庫

這里現在只寫了一個類用于操作mssql.將來支持別的數據庫試直接擴展就行了.

下來帶個代碼段

1: /// <summary>通用數據庫接口 2: /// </summary> 3: using System; 4: using System.Collections; 5: using System.Collections.Generic; 6: using System.Data; 7: using System.Data.SqlClient; 8: using System.Configuration; 9:? 10: namespace FoolWeb.DateEngine { 11:? 12: #region 事務屬性 13: public enum EffentNextType { 14: /// <summary> 15: /// 對其他語句無任何影響 16: /// </summary> 17: None, 18: /// <summary> 19: /// 當前語句必須為"SELECT COUNT(1) FROM .."格式，如果存在則繼續執行，不存在回滾事務 20: /// </summary> 21: WhenHaveContine, 22: /// <summary> 23: /// 當前語句必須為"SELECT COUNT(1) FROM .."格式，如果不存在則繼續執行，存在回滾事務 24: /// </summary> 25: WhenNoHaveContine, 26: /// <summary> 27: /// 當前語句影響到的行數必須大于0，否則回滾事務 28: /// </summary> 29: ExcuteEffectRows, 30: /// <summary> 31: /// 引發事件-當前語句必須為"SELECT COUNT(1) FROM .."格式，如果不存在則繼續執行，存在回滾事務 32: /// </summary> 33: SolicitationEvent 34: } 35:? 36: #endregion 37: #region command定義 38: public class CommandInfo { 39: public object ShareObject=null; 40: public object OriginalData=null; 41: event EventHandler _solicitationEvent; 42:? 43: public event EventHandler SolicitationEvent { 44: add { 45: _solicitationEvent+=value; 46: } 47: remove { 48: _solicitationEvent-=value; 49: } 50: } 51:? 52: public void OnSolicitationEvent() { 53: if( _solicitationEvent!=null ) { 54: _solicitationEvent(this, new EventArgs()); 55: } 56: } 57:? 58: public string CommandText; 59: public System.Data.Common.DbParameter[] Parameters; 60: public EffentNextType EffentNextType=EffentNextType.None; 61:? 62: public CommandInfo() { 63: } 64:? 65: public CommandInfo( string sqlText, SqlParameter[] para ) { 66: this.CommandText=sqlText; 67: this.Parameters=para; 68: } 69:? 70: public CommandInfo( string sqlText, SqlParameter[] para, EffentNextType type ) { 71: this.CommandText=sqlText; 72: this.Parameters=para; 73: this.EffentNextType=type; 74: } 75: } 76: #endregion 77: /// <summary> 78: /// 數據訪問抽象基礎類 79: /// </summary> 80: public abstract class MsSQLHelper { 81: #region 屬性定義 82: //數據庫連接字符串(web.config來配置)，可以動態更改connectionString支持多數據庫. 83: public static string connectionString=""; 84: //構造函數默認數據庫 85: public MsSQLHelper() { 86: connectionString=ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; 87: } 88: //構造函數指定數據庫 89: public MsSQLHelper( string _connstr ) { 90: connectionString=ConfigurationManager.ConnectionStrings[_connstr].ConnectionString; 91: } 92: #endregion 93: #region 公用方法 94: /// <summary> 95: /// 判斷是否存在某表的某個字段 96: /// </summary> 97: /// <param name="tableName">表名稱</param> 98: /// <param name="columnName">列名稱</param> 99: /// <returns>是否存在</returns> 100: public static bool ColumnExists( string tableName, string columnName ) { 101: string sql="select count(1) from syscolumns where [id]=object_id(\""+tableName+"\") and [name]=\""+columnName+"\""; 102: object res=GetSingle(sql); 103: if( res==null ) { 104: return false; 105: } 106: return Convert.ToInt32(res)>0; 107: } 108: /// <summary> 109: /// 獲取表中數字字段的最大值 110: /// </summary> 111: /// <param name="FieldName">字段名</param> 112: /// <param name="TableName">表名</param> 113: /// <returns>返回字段最大值max(空的話返回0)</returns> 114: public static int GetMaxID( string FieldName, string TableName ) { 115: string strsql="select max("+FieldName+") from "+TableName; 116: object obj=MsSQLHelper.GetSingle(strsql); 117: if( obj==null ) { 118: return 0; 119: } else { 120: return int.Parse(obj.ToString()); 121: } 122: } 123: /// <summary> 124: /// 判斷知否存在值 125: /// </summary> 126: /// <param name="strSql">sql語句</param> 127: /// <returns>存在true不存在false</returns> 128: public static bool Exists( string strSql ) { 129: object obj=MsSQLHelper.GetSingle(strSql); 130: int cmdresult; 131: if( ( Object.Equals(obj, null) )||( Object.Equals(obj, System.DBNull.Value) ) ) { 132: cmdresult=0; 133: } else { 134: cmdresult=int.Parse(obj.ToString()); 135: } 136: if( cmdresult==0 ) { 137: return false; 138: } else { 139: return true; 140: } 141: } 142: /// <summary> 143: /// 表是否存在 144: /// </summary> 145: /// <param name="TableName">表名</param> 146: /// <returns>存在true不存在false</returns> 147: public static bool TabExists( string TableName ) { 148: string strsql="select count(*) from sysobjects where id = object_id(N\"["+TableName+"]\") and OBJECTPROPERTY(id, N\"IsUserTable\") = 1"; 149: object obj=MsSQLHelper.GetSingle(strsql); 150: int cmdresult; 151: if( ( Object.Equals(obj, null) )||( Object.Equals(obj, System.DBNull.Value) ) ) { 152: cmdresult=0; 153: } else { 154: cmdresult=int.Parse(obj.ToString()); 155: } 156: if( cmdresult==0 ) { 157: return false; 158: } else { 159: return true; 160: } 161: } 162: public static bool Exists( string strSql, params SqlParameter[] cmdParms ) { 163: object obj=MsSQLHelper.GetSingle(strSql, cmdParms); 164: int cmdresult; 165: if( ( Object.Equals(obj, null) )||( Object.Equals(obj, System.DBNull.Value) ) ) { 166: cmdresult=0; 167: } else { 168: cmdresult=int.Parse(obj.ToString()); 169: } 170: if( cmdresult==0 ) { 171: return false; 172: } else { 173: return true; 174: } 175: } 176: #endregion 177: #region 執行簡單SQL語句 178: /// <summary> 179: /// 執行SQL語句，返回影響的記錄數 180: /// </summary> 181: /// <param name="SQLString">SQL語句</param> 182: /// <returns>影響的記錄數</returns> 183: public static int ExecuteSql( string SQLString ) { 184: using( SqlConnection connection=new SqlConnection(connectionString) ) { 185: using( SqlCommand cmd=new SqlCommand(SQLString, connection) ) { 186: try { 187: connection.Open(); 188: int rows=cmd.ExecuteNonQuery(); 189: return rows; 190: } catch( System.Data.SqlClient.SqlException e ) { 191: connection.Close(); 192: throw e; 193: } 194: } 195: } 196: } 197: /// <summary> 198: /// 執行SQL語句，返回影響的記錄數(延遲執行) 199: /// </summary> 200: /// <param name="SQLString">SQL語句</param> 201: /// <param name="Times">延遲時間</param> 202: /// <returns>影響的記錄數</returns> 203: public static int ExecuteSqlByTime( string SQLString, int Times ) { 204: using( SqlConnection connection=new SqlConnection(connectionString) ) { 205: using( SqlCommand cmd=new SqlCommand(SQLString, connection) ) { 206: try { 207: connection.Open(); 208: cmd.CommandTimeout=Times; 209: int rows=cmd.ExecuteNonQuery(); 210: return rows; 211: } catch( System.Data.SqlClient.SqlException e ) { 212: connection.Close(); 213: throw e; 214: } 215: } 216: } 217: } 218:? 219: /// <summary> 220: /// 執行多條SQL語句，實現數據庫事務。 221: /// </summary> 222: /// <param name="SQLStringList">多條SQL語句</param> 223: /// <returns>影響的記錄數</returns> 224: public static int ExecuteSqlTran( List<String> SQLStringList ) { 225: using( SqlConnection conn=new SqlConnection(connectionString) ) { 226: conn.Open(); 227: SqlCommand cmd=new SqlCommand(); 228: cmd.Connection=conn; 229: SqlTransaction tx=conn.BeginTransaction(); 230: cmd.Transaction=tx; 231: try { 232: int count=0; 233: for( int n=0 ; n<SQLStringList.Count ; n++ ) { 234: string strsql=SQLStringList[n]; 235: if( strsql.Trim().Length>1 ) { 236: cmd.CommandText=strsql; 237: count+=cmd.ExecuteNonQuery(); 238: } 239: } 240: tx.Commit(); 241: return count; 242: } catch { 243: tx.Rollback(); 244: return 0; 245: } 246: } 247: } 248: /// <summary> 249: /// 執行帶一個存儲過程參數的的SQL語句。 250: /// </summary> 251: /// <param name="SQLString">SQL語句</param> 252: /// <param name="content">參數內容,比如一個字段是格式復雜的文章，有特殊符號，可以通過這個方式添加</param> 253: /// <returns>影響的記錄數</returns> 254: public static int ExecuteSql( string SQLString, string content ) { 255: using( SqlConnection connection=new SqlConnection(connectionString) ) { 256: SqlCommand cmd=new SqlCommand(SQLString, connection); 257: System.Data.SqlClient.SqlParameter myParameter=new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText); 258: myParameter.Value=content; 259: cmd.Parameters.Add(myParameter); 260: try { 261: connection.Open(); 262: int rows=cmd.ExecuteNonQuery(); 263: return rows; 264: } catch( System.Data.SqlClient.SqlException e ) { 265: throw e; 266: } finally { 267: cmd.Dispose(); 268: connection.Close(); 269: } 270: } 271: } 272: /// <summary> 273: /// 執行帶一個存儲過程參數的的SQL語句。 274: /// </summary> 275: /// <param name="SQLString">SQL語句</param> 276: /// <param name="content">參數內容,比如一個字段是格式復雜的文章，有特殊符號，可以通過這個方式添加</param> 277: /// <returns>返回查詢結果第一列</returns> 278: public static object ExecuteSqlGet( string SQLString, string content ) { 279: using( SqlConnection connection=new SqlConnection(connectionString) ) { 280: SqlCommand cmd=new SqlCommand(SQLString, connection); 281: System.Data.SqlClient.SqlParameter myParameter=new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText); 282: myParameter.Value=content; 283: cmd.Parameters.Add(myParameter); 284: try { 285: connection.Open(); 286: object obj=cmd.ExecuteScalar(); 287: if( ( Object.Equals(obj, null) )||( Object.Equals(obj, System.DBNull.Value) ) ) { 288: return null; 289: } else { 290: return obj; 291: } 292: } catch( System.Data.SqlClient.SqlException e ) { 293: throw e; 294: } finally { 295: cmd.Dispose(); 296: connection.Close(); 297: } 298: } 299: } 300: /// <summary> 301: /// 向數據庫里插入圖像格式的字段(和上面情況類似的另一種實例) 302: /// </summary> 303: /// <param name="strSQL">SQL語句</param> 304: /// <param name="fs">圖像字節,數據庫的字段類型為image的情況</param> 305: /// <returns>影響的記錄數</returns> 306: public static int ExecuteSqlInsertImg( string strSQL, byte[] fs ) { 307: using( SqlConnection connection=new SqlConnection(connectionString) ) { 308: SqlCommand cmd=new SqlCommand(strSQL, connection); 309: System.Data.SqlClient.SqlParameter myParameter=new System.Data.SqlClient.SqlParameter("@fs", SqlDbType.Image); 310: myParameter.Value=fs; 311: cmd.Parameters.Add(myParameter); 312: try { 313: connection.Open(); 314: int rows=cmd.ExecuteNonQuery(); 315: return rows; 316: } catch( System.Data.SqlClient.SqlException e ) { 317: throw e; 318: } finally { 319: cmd.Dispose(); 320: connection.Close(); 321: } 322: } 323: } 324: /// <summary> 325: /// 執行一條計算查詢結果語句，返回查詢結果（object）。 326: /// </summary> 327: /// <param name="SQLString">計算查詢結果語句</param> 328: /// <returns>查詢結果（object）</returns> 329: public static object GetSingle( string SQLString ) { 330: using( SqlConnection connection=new SqlConnection(connectionString) ) { 331: using( SqlCommand cmd=new SqlCommand(SQLString, connection) ) { 332: try { 333: connection.Open(); 334: object obj=cmd.ExecuteScalar(); 335: if( ( Object.Equals(obj, null) )||( Object.Equals(obj, System.DBNull.Value) ) ) { 336: return null; 337: } else { 338: return obj; 339: } 340: } catch( System.Data.SqlClient.SqlException e ) { 341: connection.Close(); 342: throw e; 343: } 344: } 345: } 346: } 347: public static object GetSingle( string SQLString, int Times ) { 348: using( SqlConnection connection=new SqlConnection(connectionString) ) { 349: using( SqlCommand cmd=new SqlCommand(SQLString, connection) ) { 350: try { 351: connection.Open(); 352: cmd.CommandTimeout=Times; 353: object obj=cmd.ExecuteScalar(); 354: if( ( Object.Equals(obj, null) )||( Object.Equals(obj, System.DBNull.Value) ) ) { 355: return null; 356: } else { 357: return obj; 358: } 359: } catch( System.Data.SqlClient.SqlException e ) { 360: connection.Close(); 361: throw e; 362: } 363: } 364: } 365: } 366: /// <summary> 367: /// 執行查詢語句，返回SqlDataReader ( 注意：調用該方法后，一定要對SqlDataReader進行Close ) 368: /// </summary> 369: /// <param name="strSQL">查詢語句</param> 370: /// <returns>SqlDataReader</returns> 371: public static SqlDataReader ExecuteReader( string strSQL ) { 372: SqlConnection connection=new SqlConnection(connectionString); 373: SqlCommand cmd=new SqlCommand(strSQL, connection); 374: try { 375: connection.Open(); 376: SqlDataReader myReader=cmd.ExecuteReader(CommandBehavior.CloseConnection); 377: return myReader; 378: } catch( System.Data.SqlClient.SqlException e ) { 379: throw e; 380: } 381: } 382: /// <summary> 383: /// 執行查詢語句，返回DataSet 384: /// </summary> 385: /// <param name="SQLString">查詢語句</param> 386: /// <returns>DataSet</returns> 387: public static DataSet Query( string SQLString ) { 388: using( SqlConnection connection=new SqlConnection(connectionString) ) { 389: DataSet ds=new DataSet(); 390: try { 391: connection.Open(); 392: SqlDataAdapter command=new SqlDataAdapter(SQLString, connection); 393: command.Fill(ds, "ds"); 394: } catch( System.Data.SqlClient.SqlException ex ) { 395: throw new Exception(ex.Message); 396: } 397: return ds; 398: } 399: } 400: /// <summary> 401: /// 查詢并得到數據集DataSet 402: /// </summary> 403: /// <param name="SQLString">查詢語句</param> 404: /// <param name="Times"></param> 405: /// <returns></returns> 406: public static DataSet Query( string SQLString, int Times ) { 407: using( SqlConnection connection=new SqlConnection(connectionString) ) { 408: DataSet ds=new DataSet(); 409: try { 410: connection.Open(); 411: SqlDataAdapter command=new SqlDataAdapter(SQLString, connection); 412: command.SelectCommand.CommandTimeout=Times; 413: command.Fill(ds, "ds"); 414: } catch( System.Data.SqlClient.SqlException ex ) { 415: throw new Exception(ex.Message); 416: } 417: return ds; 418: } 419: } 420: #endregion 421: #region 執行帶參數的SQL語句 422: /// <summary> 423: /// 執行SQL語句，返回影響的記錄數 424: /// </summary> 425: /// <param name="SQLString">SQL語句</param> 426: /// <returns>影響的記錄數</returns> 427: public static int ExecuteSql( string SQLString, params SqlParameter[] cmdParms ) { 428: using( SqlConnection connection=new SqlConnection(connectionString) ) { 429: using( SqlCommand cmd=new SqlCommand() ) { 430: try { 431: PrepareCommand(cmd, connection, null, SQLString, cmdParms); 432: int rows=cmd.ExecuteNonQuery(); 433: cmd.Parameters.Clear(); 434: return rows; 435: } catch( System.Data.SqlClient.SqlException e ) { 436: throw e; 437: } 438: } 439: } 440: } 441: /// <summary> 442: /// 執行多條SQL語句，實現數據庫事務。 443: /// </summary> 444: /// <param name="SQLStringList">SQL語句的哈希表（key為sql語句，value是該語句的SqlParameter[]）</param> 445: public static void ExecuteSqlTran( Hashtable SQLStringList ) { 446: using( SqlConnection conn=new SqlConnection(connectionString) ) { 447: conn.Open(); 448: using( SqlTransaction trans=conn.BeginTransaction() ) { 449: SqlCommand cmd=new SqlCommand(); 450: try { 451: //循環 452: foreach( DictionaryEntry myDE in SQLStringList ) { 453: string cmdText=myDE.Key.ToString(); 454: SqlParameter[] cmdParms=(SqlParameter[])myDE.Value; 455: PrepareCommand(cmd, conn, trans, cmdText, cmdParms); 456: int val=cmd.ExecuteNonQuery(); 457: cmd.Parameters.Clear(); 458: } 459: trans.Commit(); 460: } catch { 461: trans.Rollback(); 462: throw; 463: } 464: } 465: } 466: } 467: /// <summary> 468: /// 執行多條SQL語句，實現數據庫事務。 469: /// </summary> 470: /// <param name="SQLStringList">SQL語句的哈希表（key為sql語句，value是該語句的SqlParameter[]）</param> 471: public static int ExecuteSqlTran( System.Collections.Generic.List<CommandInfo> cmdList ) { 472: using( SqlConnection conn=new SqlConnection(connectionString) ) { 473: conn.Open(); 474: using( SqlTransaction trans=conn.BeginTransaction() ) { 475: SqlCommand cmd=new SqlCommand(); 476: try { 477: int count=0; 478: //循環 479: foreach( CommandInfo myDE in cmdList ) { 480: string cmdText=myDE.CommandText; 481: SqlParameter[] cmdParms=(SqlParameter[])myDE.Parameters; 482: PrepareCommand(cmd, conn, trans, cmdText, cmdParms); 483: if( myDE.EffentNextType==EffentNextType.WhenHaveContine||myDE.EffentNextType==EffentNextType.WhenNoHaveContine ) { 484: if( myDE.CommandText.ToLower().IndexOf("count(")==-1 ) { 485: trans.Rollback(); 486: return 0; 487: } 488: object obj=cmd.ExecuteScalar(); 489: bool isHave=false; 490: if( obj==null&&obj==DBNull.Value ) { 491: isHave=false; 492: } 493: isHave=Convert.ToInt32(obj)>0; 494: if( myDE.EffentNextType==EffentNextType.WhenHaveContine&&!isHave ) { 495: trans.Rollback(); 496: return 0; 497: } 498: if( myDE.EffentNextType==EffentNextType.WhenNoHaveContine&&isHave ) { 499: trans.Rollback(); 500: return 0; 501: } 502: continue; 503: } 504: int val=cmd.ExecuteNonQuery(); 505: count+=val; 506: if( myDE.EffentNextType==EffentNextType.ExcuteEffectRows&&val==0 ) { 507: trans.Rollback(); 508: return 0; 509: } 510: cmd.Parameters.Clear(); 511: } 512: trans.Commit(); 513: return count; 514: } catch { 515: trans.Rollback(); 516: throw; 517: } 518: } 519: } 520: } 521: /// <summary> 522: /// 執行多條SQL語句，實現數據庫事務。 523: /// </summary> 524: /// <param name="SQLStringList">SQL語句的哈希表（key為sql語句，value是該語句的SqlParameter[]）</param> 525: public static void ExecuteSqlTranWithIndentity( System.Collections.Generic.List<CommandInfo> SQLStringList ) { 526: using( SqlConnection conn=new SqlConnection(connectionString) ) { 527: conn.Open(); 528: using( SqlTransaction trans=conn.BeginTransaction() ) { 529: SqlCommand cmd=new SqlCommand(); 530: try { 531: int indentity=0; 532: //循環 533: foreach( CommandInfo myDE in SQLStringList ) { 534: string cmdText=myDE.CommandText; 535: SqlParameter[] cmdParms=(SqlParameter[])myDE.Parameters; 536: foreach( SqlParameter q in cmdParms ) { 537: if( q.Direction==ParameterDirection.InputOutput ) { 538: q.Value=indentity; 539: } 540: } 541: PrepareCommand(cmd, conn, trans, cmdText, cmdParms); 542: int val=cmd.ExecuteNonQuery(); 543: foreach( SqlParameter q in cmdParms ) { 544: if( q.Direction==ParameterDirection.Output ) { 545: indentity=Convert.ToInt32(q.Value); 546: } 547: } 548: cmd.Parameters.Clear(); 549: } 550: trans.Commit(); 551: } catch { 552: trans.Rollback(); 553: throw; 554: } 555: } 556: } 557: } 558: /// <summary> 559: /// 執行多條SQL語句，實現數據庫事務。 560: /// </summary> 561: /// <param name="SQLStringList">SQL語句的哈希表（key為sql語句，value是該語句的SqlParameter[]）</param> 562: public static void ExecuteSqlTranWithIndentity( Hashtable SQLStringList ) { 563: using( SqlConnection conn=new SqlConnection(connectionString) ) { 564: conn.Open(); 565: using( SqlTransaction trans=conn.BeginTransaction() ) { 566: SqlCommand cmd=new SqlCommand(); 567: try { 568: int indentity=0; 569: //循環 570: foreach( DictionaryEntry myDE in SQLStringList ) { 571: string cmdText=myDE.Key.ToString(); 572: SqlParameter[] cmdParms=(SqlParameter[])myDE.Value; 573: foreach( SqlParameter q in cmdParms ) { 574: if( q.Direction==ParameterDirection.InputOutput ) { 575: q.Value=indentity; 576: } 577: } 578: PrepareCommand(cmd, conn, trans, cmdText, cmdParms); 579: int val=cmd.ExecuteNonQuery(); 580: foreach( SqlParameter q in cmdParms ) { 581: if( q.Direction==ParameterDirection.Output ) { 582: indentity=Convert.ToInt32(q.Value); 583: } 584: } 585: cmd.Parameters.Clear(); 586: } 587: trans.Commit(); 588: } catch { 589: trans.Rollback(); 590: throw; 591: } 592: } 593: } 594: } 595: /// <summary> 596: /// 執行一條計算查詢結果語句，返回查詢結果（object）。 597: /// </summary> 598: /// <param name="SQLString">計算查詢結果語句</param> 599: /// <returns>查詢結果（object）</returns> 600: public static object GetSingle( string SQLString, params SqlParameter[] cmdParms ) { 601: using( SqlConnection connection=new SqlConnection(connectionString) ) { 602: using( SqlCommand cmd=new SqlCommand() ) { 603: try { 604: PrepareCommand(cmd, connection, null, SQLString, cmdParms); 605: object obj=cmd.ExecuteScalar(); 606: cmd.Parameters.Clear(); 607: if( ( Object.Equals(obj, null) )||( Object.Equals(obj, System.DBNull.Value) ) ) { 608: return null; 609: } else { 610: return obj; 611: } 612: } catch( System.Data.SqlClient.SqlException e ) { 613: throw e; 614: } 615: } 616: } 617: } 618: /// <summary> 619: /// 執行查詢語句，返回SqlDataReader ( 注意：調用該方法后，一定要對SqlDataReader進行Close ) 620: /// </summary> 621: /// <param name="strSQL">查詢語句</param> 622: /// <returns>SqlDataReader</returns> 623: public static SqlDataReader ExecuteReader( string SQLString, params SqlParameter[] cmdParms ) { 624: SqlConnection connection=new SqlConnection(connectionString); 625: SqlCommand cmd=new SqlCommand(); 626: try { 627: PrepareCommand(cmd, connection, null, SQLString, cmdParms); 628: SqlDataReader myReader=cmd.ExecuteReader(CommandBehavior.CloseConnection); 629: cmd.Parameters.Clear(); 630: return myReader; 631: } catch( System.Data.SqlClient.SqlException e ) { 632: throw e; 633: } 634: } 635: /// <summary> 636: /// 執行查詢語句，返回DataSet 637: /// </summary> 638: /// <param name="SQLString">查詢語句</param> 639: /// <returns>DataSet</returns> 640: public static DataSet Query( string SQLString, params SqlParameter[] cmdParms ) { 641: using( SqlConnection connection=new SqlConnection(connectionString) ) { 642: SqlCommand cmd=new SqlCommand(); 643: PrepareCommand(cmd, connection, null, SQLString, cmdParms); 644: using( SqlDataAdapter da=new SqlDataAdapter(cmd) ) { 645: DataSet ds=new DataSet(); 646: try { 647: da.Fill(ds, "ds"); 648: cmd.Parameters.Clear(); 649: } catch( System.Data.SqlClient.SqlException ex ) { 650: throw new Exception(ex.Message); 651: } 652: return ds; 653: } 654: } 655: } 656: private static void PrepareCommand( SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms ) { 657: if( conn.State!=ConnectionState.Open ) 658: conn.Open(); 659: cmd.Connection=conn; 660: cmd.CommandText=cmdText; 661: if( trans!=null ) 662: cmd.Transaction=trans; 663: cmd.CommandType=CommandType.Text;//cmdType; 664: if( cmdParms!=null ) { 665: foreach( SqlParameter parameter in cmdParms ) { 666: if( ( parameter.Direction==ParameterDirection.InputOutput||parameter.Direction==ParameterDirection.Input )&& 667: ( parameter.Value==null ) ) { 668: parameter.Value=DBNull.Value; 669: } 670: cmd.Parameters.Add(parameter); 671: } 672: } 673: } 674: #endregion 675: #region 存儲過程操作 676: /// <summary> 677: /// 執行存儲過程，返回SqlDataReader ( 注意：調用該方法后，一定要對SqlDataReader進行Close ) 678: /// </summary> 679: /// <param name="storedProcName">存儲過程名</param> 680: /// <returns>SqlDataReader</returns> 681: public static SqlDataReader RunProcedure( string storedProcName ) { 682: SqlConnection connection=new SqlConnection(connectionString); 683: SqlDataReader returnReader; 684: connection.Open(); 685: SqlCommand command=new SqlCommand(storedProcName, connection); 686: command.CommandType=CommandType.StoredProcedure; 687: returnReader=command.ExecuteReader(CommandBehavior.CloseConnection); 688: return returnReader; 689: } 690:? 691: /// <summary> 692: /// 執行存儲過程，返回SqlDataReader ( 注意：調用該方法后，一定要對SqlDataReader進行Close ) 693: /// </summary> 694: /// <param name="storedProcName">存儲過程名</param> 695: /// <param name="parameters">存儲過程參數</param> 696: /// <returns>SqlDataReader</returns> 697: public static SqlDataReader RunProcedure( string storedProcName, IDataParameter[] parameters ) { 698: SqlConnection connection=new SqlConnection(connectionString); 699: SqlDataReader returnReader; 700: connection.Open(); 701: SqlCommand command=BuildQueryCommand(connection, storedProcName, parameters); 702: command.CommandType=CommandType.StoredProcedure; 703: returnReader=command.ExecuteReader(CommandBehavior.CloseConnection); 704: return returnReader; 705: } 706: /// <summary> 707: /// 執行存儲過程 708: /// </summary> 709: /// <param name="storedProcName">存儲過程名</param> 710: /// <param name="parameters">存儲過程參數</param> 711: /// <param name="tableName">DataSet結果中的表名</param> 712: /// <returns>DataSet</returns> 713: public static DataSet RunProcedure( string storedProcName, string tableName ) { 714: using( SqlConnection connection=new SqlConnection(connectionString) ) { 715: DataSet dataSet=new DataSet(); 716: connection.Open(); 717: SqlDataAdapter sqlDA=new SqlDataAdapter(); 718: sqlDA.SelectCommand=new SqlCommand(storedProcName, connection); 719: sqlDA.Fill(dataSet, tableName); 720: connection.Close(); 721: return dataSet; 722: } 723: } 724: /// <summary> 725: /// 執行存儲過程 726: /// </summary> 727: /// <param name="storedProcName">存儲過程名</param> 728: /// <param name="parameters">存儲過程參數</param> 729: /// <param name="tableName">DataSet結果中的表名</param> 730: /// <returns>DataSet</returns> 731: public static DataSet RunProcedure( string storedProcName, IDataParameter[] parameters, string tableName ) { 732: using( SqlConnection connection=new SqlConnection(connectionString) ) { 733: DataSet dataSet=new DataSet(); 734: connection.Open(); 735: SqlDataAdapter sqlDA=new SqlDataAdapter(); 736: sqlDA.SelectCommand=BuildQueryCommand(connection, storedProcName, parameters); 737: sqlDA.Fill(dataSet, tableName); 738: connection.Close(); 739: return dataSet; 740: } 741: } 742: public static DataSet RunProcedure( string storedProcName, IDataParameter[] parameters, string tableName, int Times ) { 743: using( SqlConnection connection=new SqlConnection(connectionString) ) { 744: DataSet dataSet=new DataSet(); 745: connection.Open(); 746: SqlDataAdapter sqlDA=new SqlDataAdapter(); 747: sqlDA.SelectCommand=BuildQueryCommand(connection, storedProcName, parameters); 748: sqlDA.SelectCommand.CommandTimeout=Times; 749: sqlDA.Fill(dataSet, tableName); 750: connection.Close(); 751: return dataSet; 752: } 753: } 754: /// <summary> 755: /// 構建 SqlCommand 對象(用來返回一個結果集，而不是一個整數值) 756: /// </summary> 757: /// <param name="connection">數據庫連接</param> 758: /// <param name="storedProcName">存儲過程名</param> 759: /// <param name="parameters">存儲過程參數</param> 760: /// <returns>SqlCommand</returns> 761: private static SqlCommand BuildQueryCommand( SqlConnection connection, string storedProcName, IDataParameter[] parameters ) { 762: SqlCommand command=new SqlCommand(storedProcName, connection); 763: command.CommandType=CommandType.StoredProcedure; 764: foreach( SqlParameter parameter in parameters ) { 765: if( parameter!=null ) { 766: // 檢查未分配值的輸出參數,將其分配以DBNull.Value. 767: if( ( parameter.Direction==ParameterDirection.InputOutput||parameter.Direction==ParameterDirection.Input )&& 768: ( parameter.Value==null ) ) { 769: parameter.Value=DBNull.Value; 770: } 771: command.Parameters.Add(parameter); 772: } 773: } 774: return command; 775: } 776: /// <summary> 777: /// 執行存儲過程，返回影響的行數 778: /// </summary> 779: /// <param name="storedProcName">存儲過程名</param> 780: /// <param name="parameters">存儲過程參數</param> 781: /// <param name="rowsAffected">影響的行數</param> 782: /// <returns></returns> 783: public static int RunProcedure( string storedProcName, out int rowsAffected ) { 784: using( SqlConnection connection=new SqlConnection(connectionString) ) { 785: int result; 786: connection.Open(); 787: SqlCommand command=new SqlCommand(storedProcName, connection); 788: rowsAffected=command.ExecuteNonQuery(); 789: result=(int)command.Parameters["ReturnValue"].Value; 790: //Connection.Close(); 791: return result; 792: } 793: } 794: /// <summary> 795: /// 執行存儲過程，返回影響的行數 796: /// </summary> 797: /// <param name="storedProcName">存儲過程名</param> 798: /// <param name="parameters">存儲過程參數</param> 799: /// <param name="rowsAffected">影響的行數</param> 800: /// <returns></returns> 801: public static int RunProcedure( string storedProcName, IDataParameter[] parameters, out int rowsAffected ) { 802: using( SqlConnection connection=new SqlConnection(connectionString) ) { 803: int result; 804: connection.Open(); 805: SqlCommand command=BuildIntCommand(connection, storedProcName, parameters); 806: rowsAffected=command.ExecuteNonQuery(); 807: result=(int)command.Parameters["ReturnValue"].Value; 808: //Connection.Close(); 809: return result; 810: } 811: } 812: /// <summary> 813: /// 創建 SqlCommand 對象實例(用來返回一個整數值) 814: /// </summary> 815: /// <param name="storedProcName">存儲過程名</param> 816: /// <param name="parameters">存儲過程參數</param> 817: /// <returns>SqlCommand 對象實例</returns> 818: private static SqlCommand BuildIntCommand( SqlConnection connection, string storedProcName, IDataParameter[] parameters ) { 819: SqlCommand command=BuildQueryCommand(connection, storedProcName, parameters); 820: command.Parameters.Add(new SqlParameter("ReturnValue", 821: SqlDbType.Int, 4, ParameterDirection.ReturnValue, 822: false, 0, 0, string.Empty, DataRowVersion.Default, null)); 823: return command; 824: } 825: #endregion 826: } 827: }

轉載于:https://www.cnblogs.com/binghe875/archive/2011/11/21/2257399.html

總結

以上是生活随笔為你收集整理的FoolWeb 各层代码实例的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。