没有网站可以做百度推广吗,网站开发所使用的浏览器,公司简介模板怎么做,电子产品外包加工项目FoolWeb.DateEngine 数据库操作类库 这里现在只写了一个类用于操作mssql.将来支持别的数据库试直接扩展就行了. 下来带个代码段 1: /// summary通用数据库接口 2: /// /summary 3: using System; 4: using System.Collections; 5: using System.Collections.G… 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 ShareObjectnull; 40: public object OriginalDatanull; 41: event EventHandler _solicitationEvent; 42: 43: public event EventHandler SolicitationEvent { 44: add { 45: _solicitationEventvalue; 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 EffentNextTypeEffentNextType.None; 61: 62: public CommandInfo() { 63: } 64: 65: public CommandInfo( string sqlText, SqlParameter[] para ) { 66: this.CommandTextsqlText; 67: this.Parameterspara; 68: } 69: 70: public CommandInfo( string sqlText, SqlParameter[] para, EffentNextType type ) { 71: this.CommandTextsqlText; 72: this.Parameterspara; 73: this.EffentNextTypetype; 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: connectionStringConfigurationManager.ConnectionStrings[ConnectionString].ConnectionString; 87: } 88: //构造函数指定数据库 89: public MsSQLHelper( string _connstr ) { 90: connectionStringConfigurationManager.ConnectionStrings[_connstr].ConnectionString; 91: } 92: #endregion 93: #region 公用方法 94: /// summary 95: /// 判断是否存在某表的某个字段 96: /// /summary 97: /// param nametableName表名称/param 98: /// param namecolumnName列名称/param 99: /// returns是否存在/returns 100: public static bool ColumnExists( string tableName, string columnName ) { 101: string sqlselect count(1) from syscolumns where [id]object_id(\tableName\) and [name]\columnName\; 102: object resGetSingle(sql); 103: if( resnull ) { 104: return false; 105: } 106: return Convert.ToInt32(res)0; 107: } 108: /// summary 109: /// 获取表中数字字段的最大值 110: /// /summary 111: /// param nameFieldName字段名/param 112: /// param nameTableName表名/param 113: /// returns返回字段最大值max(空的话返回0)/returns 114: public static int GetMaxID( string FieldName, string TableName ) { 115: string strsqlselect max(FieldName) from TableName; 116: object objMsSQLHelper.GetSingle(strsql); 117: if( objnull ) { 118: return 0; 119: } else { 120: return int.Parse(obj.ToString()); 121: } 122: } 123: /// summary 124: /// 判断知否存在值 125: /// /summary 126: /// param namestrSqlsql语句/param 127: /// returns存在true不存在false/returns 128: public static bool Exists( string strSql ) { 129: object objMsSQLHelper.GetSingle(strSql); 130: int cmdresult; 131: if( ( Object.Equals(obj, null) )||( Object.Equals(obj, System.DBNull.Value) ) ) { 132: cmdresult0; 133: } else { 134: cmdresultint.Parse(obj.ToString()); 135: } 136: if( cmdresult0 ) { 137: return false; 138: } else { 139: return true; 140: } 141: } 142: /// summary 143: /// 表是否存在 144: /// /summary 145: /// param nameTableName表名/param 146: /// returns存在true不存在false/returns 147: public static bool TabExists( string TableName ) { 148: string strsqlselect count(*) from sysobjects where id object_id(N\[TableName]\) and OBJECTPROPERTY(id, N\IsUserTable\) 1; 149: object objMsSQLHelper.GetSingle(strsql); 150: int cmdresult; 151: if( ( Object.Equals(obj, null) )||( Object.Equals(obj, System.DBNull.Value) ) ) { 152: cmdresult0; 153: } else { 154: cmdresultint.Parse(obj.ToString()); 155: } 156: if( cmdresult0 ) { 157: return false; 158: } else { 159: return true; 160: } 161: } 162: public static bool Exists( string strSql, params SqlParameter[] cmdParms ) { 163: object objMsSQLHelper.GetSingle(strSql, cmdParms); 164: int cmdresult; 165: if( ( Object.Equals(obj, null) )||( Object.Equals(obj, System.DBNull.Value) ) ) { 166: cmdresult0; 167: } else { 168: cmdresultint.Parse(obj.ToString()); 169: } 170: if( cmdresult0 ) { 171: return false; 172: } else { 173: return true; 174: } 175: } 176: #endregion 177: #region 执行简单SQL语句 178: /// summary 179: /// 执行SQL语句返回影响的记录数 180: /// /summary 181: /// param nameSQLStringSQL语句/param 182: /// returns影响的记录数/returns 183: public static int ExecuteSql( string SQLString ) { 184: using( SqlConnection connectionnew SqlConnection(connectionString) ) { 185: using( SqlCommand cmdnew SqlCommand(SQLString, connection) ) { 186: try { 187: connection.Open(); 188: int rowscmd.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 nameSQLStringSQL语句/param 201: /// param nameTimes延迟时间/param 202: /// returns影响的记录数/returns 203: public static int ExecuteSqlByTime( string SQLString, int Times ) { 204: using( SqlConnection connectionnew SqlConnection(connectionString) ) { 205: using( SqlCommand cmdnew SqlCommand(SQLString, connection) ) { 206: try { 207: connection.Open(); 208: cmd.CommandTimeoutTimes; 209: int rowscmd.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 nameSQLStringList多条SQL语句/param 223: /// returns影响的记录数/returns 224: public static int ExecuteSqlTran( ListString SQLStringList ) { 225: using( SqlConnection connnew SqlConnection(connectionString) ) { 226: conn.Open(); 227: SqlCommand cmdnew SqlCommand(); 228: cmd.Connectionconn; 229: SqlTransaction txconn.BeginTransaction(); 230: cmd.Transactiontx; 231: try { 232: int count0; 233: for( int n0 ; nSQLStringList.Count ; n ) { 234: string strsqlSQLStringList[n]; 235: if( strsql.Trim().Length1 ) { 236: cmd.CommandTextstrsql; 237: countcmd.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 nameSQLStringSQL语句/param 252: /// param namecontent参数内容,比如一个字段是格式复杂的文章有特殊符号可以通过这个方式添加/param 253: /// returns影响的记录数/returns 254: public static int ExecuteSql( string SQLString, string content ) { 255: using( SqlConnection connectionnew SqlConnection(connectionString) ) { 256: SqlCommand cmdnew SqlCommand(SQLString, connection); 257: System.Data.SqlClient.SqlParameter myParameternew System.Data.SqlClient.SqlParameter(content, SqlDbType.NText); 258: myParameter.Valuecontent; 259: cmd.Parameters.Add(myParameter); 260: try { 261: connection.Open(); 262: int rowscmd.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 nameSQLStringSQL语句/param 276: /// param namecontent参数内容,比如一个字段是格式复杂的文章有特殊符号可以通过这个方式添加/param 277: /// returns返回查询结果第一列/returns 278: public static object ExecuteSqlGet( string SQLString, string content ) { 279: using( SqlConnection connectionnew SqlConnection(connectionString) ) { 280: SqlCommand cmdnew SqlCommand(SQLString, connection); 281: System.Data.SqlClient.SqlParameter myParameternew System.Data.SqlClient.SqlParameter(content, SqlDbType.NText); 282: myParameter.Valuecontent; 283: cmd.Parameters.Add(myParameter); 284: try { 285: connection.Open(); 286: object objcmd.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 namestrSQLSQL语句/param 304: /// param namefs图像字节,数据库的字段类型为image的情况/param 305: /// returns影响的记录数/returns 306: public static int ExecuteSqlInsertImg( string strSQL, byte[] fs ) { 307: using( SqlConnection connectionnew SqlConnection(connectionString) ) { 308: SqlCommand cmdnew SqlCommand(strSQL, connection); 309: System.Data.SqlClient.SqlParameter myParameternew System.Data.SqlClient.SqlParameter(fs, SqlDbType.Image); 310: myParameter.Valuefs; 311: cmd.Parameters.Add(myParameter); 312: try { 313: connection.Open(); 314: int rowscmd.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 nameSQLString计算查询结果语句/param 328: /// returns查询结果object/returns 329: public static object GetSingle( string SQLString ) { 330: using( SqlConnection connectionnew SqlConnection(connectionString) ) { 331: using( SqlCommand cmdnew SqlCommand(SQLString, connection) ) { 332: try { 333: connection.Open(); 334: object objcmd.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 connectionnew SqlConnection(connectionString) ) { 349: using( SqlCommand cmdnew SqlCommand(SQLString, connection) ) { 350: try { 351: connection.Open(); 352: cmd.CommandTimeoutTimes; 353: object objcmd.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 namestrSQL查询语句/param 370: /// returnsSqlDataReader/returns 371: public static SqlDataReader ExecuteReader( string strSQL ) { 372: SqlConnection connectionnew SqlConnection(connectionString); 373: SqlCommand cmdnew SqlCommand(strSQL, connection); 374: try { 375: connection.Open(); 376: SqlDataReader myReadercmd.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 nameSQLString查询语句/param 386: /// returnsDataSet/returns 387: public static DataSet Query( string SQLString ) { 388: using( SqlConnection connectionnew SqlConnection(connectionString) ) { 389: DataSet dsnew DataSet(); 390: try { 391: connection.Open(); 392: SqlDataAdapter commandnew 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 nameSQLString查询语句/param 404: /// param nameTimes/param 405: /// returns/returns 406: public static DataSet Query( string SQLString, int Times ) { 407: using( SqlConnection connectionnew SqlConnection(connectionString) ) { 408: DataSet dsnew DataSet(); 409: try { 410: connection.Open(); 411: SqlDataAdapter commandnew SqlDataAdapter(SQLString, connection); 412: command.SelectCommand.CommandTimeoutTimes; 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 nameSQLStringSQL语句/param 426: /// returns影响的记录数/returns 427: public static int ExecuteSql( string SQLString, params SqlParameter[] cmdParms ) { 428: using( SqlConnection connectionnew SqlConnection(connectionString) ) { 429: using( SqlCommand cmdnew SqlCommand() ) { 430: try { 431: PrepareCommand(cmd, connection, null, SQLString, cmdParms); 432: int rowscmd.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 nameSQLStringListSQL语句的哈希表key为sql语句value是该语句的SqlParameter[]/param 445: public static void ExecuteSqlTran( Hashtable SQLStringList ) { 446: using( SqlConnection connnew SqlConnection(connectionString) ) { 447: conn.Open(); 448: using( SqlTransaction transconn.BeginTransaction() ) { 449: SqlCommand cmdnew SqlCommand(); 450: try { 451: //循环 452: foreach( DictionaryEntry myDE in SQLStringList ) { 453: string cmdTextmyDE.Key.ToString(); 454: SqlParameter[] cmdParms(SqlParameter[])myDE.Value; 455: PrepareCommand(cmd, conn, trans, cmdText, cmdParms); 456: int valcmd.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 nameSQLStringListSQL语句的哈希表key为sql语句value是该语句的SqlParameter[]/param 471: public static int ExecuteSqlTran( System.Collections.Generic.ListCommandInfo cmdList ) { 472: using( SqlConnection connnew SqlConnection(connectionString) ) { 473: conn.Open(); 474: using( SqlTransaction transconn.BeginTransaction() ) { 475: SqlCommand cmdnew SqlCommand(); 476: try { 477: int count0; 478: //循环 479: foreach( CommandInfo myDE in cmdList ) { 480: string cmdTextmyDE.CommandText; 481: SqlParameter[] cmdParms(SqlParameter[])myDE.Parameters; 482: PrepareCommand(cmd, conn, trans, cmdText, cmdParms); 483: if( myDE.EffentNextTypeEffentNextType.WhenHaveContine||myDE.EffentNextTypeEffentNextType.WhenNoHaveContine ) { 484: if( myDE.CommandText.ToLower().IndexOf(count()-1 ) { 485: trans.Rollback(); 486: return 0; 487: } 488: object objcmd.ExecuteScalar(); 489: bool isHavefalse; 490: if( objnullobjDBNull.Value ) { 491: isHavefalse; 492: } 493: isHaveConvert.ToInt32(obj)0; 494: if( myDE.EffentNextTypeEffentNextType.WhenHaveContine!isHave ) { 495: trans.Rollback(); 496: return 0; 497: } 498: if( myDE.EffentNextTypeEffentNextType.WhenNoHaveContineisHave ) { 499: trans.Rollback(); 500: return 0; 501: } 502: continue; 503: } 504: int valcmd.ExecuteNonQuery(); 505: countval; 506: if( myDE.EffentNextTypeEffentNextType.ExcuteEffectRowsval0 ) { 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 nameSQLStringListSQL语句的哈希表key为sql语句value是该语句的SqlParameter[]/param 525: public static void ExecuteSqlTranWithIndentity( System.Collections.Generic.ListCommandInfo SQLStringList ) { 526: using( SqlConnection connnew SqlConnection(connectionString) ) { 527: conn.Open(); 528: using( SqlTransaction transconn.BeginTransaction() ) { 529: SqlCommand cmdnew SqlCommand(); 530: try { 531: int indentity0; 532: //循环 533: foreach( CommandInfo myDE in SQLStringList ) { 534: string cmdTextmyDE.CommandText; 535: SqlParameter[] cmdParms(SqlParameter[])myDE.Parameters; 536: foreach( SqlParameter q in cmdParms ) { 537: if( q.DirectionParameterDirection.InputOutput ) { 538: q.Valueindentity; 539: } 540: } 541: PrepareCommand(cmd, conn, trans, cmdText, cmdParms); 542: int valcmd.ExecuteNonQuery(); 543: foreach( SqlParameter q in cmdParms ) { 544: if( q.DirectionParameterDirection.Output ) { 545: indentityConvert.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 nameSQLStringListSQL语句的哈希表key为sql语句value是该语句的SqlParameter[]/param 562: public static void ExecuteSqlTranWithIndentity( Hashtable SQLStringList ) { 563: using( SqlConnection connnew SqlConnection(connectionString) ) { 564: conn.Open(); 565: using( SqlTransaction transconn.BeginTransaction() ) { 566: SqlCommand cmdnew SqlCommand(); 567: try { 568: int indentity0; 569: //循环 570: foreach( DictionaryEntry myDE in SQLStringList ) { 571: string cmdTextmyDE.Key.ToString(); 572: SqlParameter[] cmdParms(SqlParameter[])myDE.Value; 573: foreach( SqlParameter q in cmdParms ) { 574: if( q.DirectionParameterDirection.InputOutput ) { 575: q.Valueindentity; 576: } 577: } 578: PrepareCommand(cmd, conn, trans, cmdText, cmdParms); 579: int valcmd.ExecuteNonQuery(); 580: foreach( SqlParameter q in cmdParms ) { 581: if( q.DirectionParameterDirection.Output ) { 582: indentityConvert.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 nameSQLString计算查询结果语句/param 599: /// returns查询结果object/returns 600: public static object GetSingle( string SQLString, params SqlParameter[] cmdParms ) { 601: using( SqlConnection connectionnew SqlConnection(connectionString) ) { 602: using( SqlCommand cmdnew SqlCommand() ) { 603: try { 604: PrepareCommand(cmd, connection, null, SQLString, cmdParms); 605: object objcmd.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 namestrSQL查询语句/param 622: /// returnsSqlDataReader/returns 623: public static SqlDataReader ExecuteReader( string SQLString, params SqlParameter[] cmdParms ) { 624: SqlConnection connectionnew SqlConnection(connectionString); 625: SqlCommand cmdnew SqlCommand(); 626: try { 627: PrepareCommand(cmd, connection, null, SQLString, cmdParms); 628: SqlDataReader myReadercmd.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 nameSQLString查询语句/param 639: /// returnsDataSet/returns 640: public static DataSet Query( string SQLString, params SqlParameter[] cmdParms ) { 641: using( SqlConnection connectionnew SqlConnection(connectionString) ) { 642: SqlCommand cmdnew SqlCommand(); 643: PrepareCommand(cmd, connection, null, SQLString, cmdParms); 644: using( SqlDataAdapter danew SqlDataAdapter(cmd) ) { 645: DataSet dsnew 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.Connectionconn; 660: cmd.CommandTextcmdText; 661: if( trans!null ) 662: cmd.Transactiontrans; 663: cmd.CommandTypeCommandType.Text;//cmdType; 664: if( cmdParms!null ) { 665: foreach( SqlParameter parameter in cmdParms ) { 666: if( ( parameter.DirectionParameterDirection.InputOutput||parameter.DirectionParameterDirection.Input ) 667: ( parameter.Valuenull ) ) { 668: parameter.ValueDBNull.Value; 669: } 670: cmd.Parameters.Add(parameter); 671: } 672: } 673: } 674: #endregion 675: #region 存储过程操作 676: /// summary 677: /// 执行存储过程返回SqlDataReader ( 注意调用该方法后一定要对SqlDataReader进行Close ) 678: /// /summary 679: /// param namestoredProcName存储过程名/param 680: /// returnsSqlDataReader/returns 681: public static SqlDataReader RunProcedure( string storedProcName ) { 682: SqlConnection connectionnew SqlConnection(connectionString); 683: SqlDataReader returnReader; 684: connection.Open(); 685: SqlCommand commandnew SqlCommand(storedProcName, connection); 686: command.CommandTypeCommandType.StoredProcedure; 687: returnReadercommand.ExecuteReader(CommandBehavior.CloseConnection); 688: return returnReader; 689: } 690: 691: /// summary 692: /// 执行存储过程返回SqlDataReader ( 注意调用该方法后一定要对SqlDataReader进行Close ) 693: /// /summary 694: /// param namestoredProcName存储过程名/param 695: /// param nameparameters存储过程参数/param 696: /// returnsSqlDataReader/returns 697: public static SqlDataReader RunProcedure( string storedProcName, IDataParameter[] parameters ) { 698: SqlConnection connectionnew SqlConnection(connectionString); 699: SqlDataReader returnReader; 700: connection.Open(); 701: SqlCommand commandBuildQueryCommand(connection, storedProcName, parameters); 702: command.CommandTypeCommandType.StoredProcedure; 703: returnReadercommand.ExecuteReader(CommandBehavior.CloseConnection); 704: return returnReader; 705: } 706: /// summary 707: /// 执行存储过程 708: /// /summary 709: /// param namestoredProcName存储过程名/param 710: /// param nameparameters存储过程参数/param 711: /// param nametableNameDataSet结果中的表名/param 712: /// returnsDataSet/returns 713: public static DataSet RunProcedure( string storedProcName, string tableName ) { 714: using( SqlConnection connectionnew SqlConnection(connectionString) ) { 715: DataSet dataSetnew DataSet(); 716: connection.Open(); 717: SqlDataAdapter sqlDAnew SqlDataAdapter(); 718: sqlDA.SelectCommandnew SqlCommand(storedProcName, connection); 719: sqlDA.Fill(dataSet, tableName); 720: connection.Close(); 721: return dataSet; 722: } 723: } 724: /// summary 725: /// 执行存储过程 726: /// /summary 727: /// param namestoredProcName存储过程名/param 728: /// param nameparameters存储过程参数/param 729: /// param nametableNameDataSet结果中的表名/param 730: /// returnsDataSet/returns 731: public static DataSet RunProcedure( string storedProcName, IDataParameter[] parameters, string tableName ) { 732: using( SqlConnection connectionnew SqlConnection(connectionString) ) { 733: DataSet dataSetnew DataSet(); 734: connection.Open(); 735: SqlDataAdapter sqlDAnew SqlDataAdapter(); 736: sqlDA.SelectCommandBuildQueryCommand(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 connectionnew SqlConnection(connectionString) ) { 744: DataSet dataSetnew DataSet(); 745: connection.Open(); 746: SqlDataAdapter sqlDAnew SqlDataAdapter(); 747: sqlDA.SelectCommandBuildQueryCommand(connection, storedProcName, parameters); 748: sqlDA.SelectCommand.CommandTimeoutTimes; 749: sqlDA.Fill(dataSet, tableName); 750: connection.Close(); 751: return dataSet; 752: } 753: } 754: /// summary 755: /// 构建 SqlCommand 对象(用来返回一个结果集而不是一个整数值) 756: /// /summary 757: /// param nameconnection数据库连接/param 758: /// param namestoredProcName存储过程名/param 759: /// param nameparameters存储过程参数/param 760: /// returnsSqlCommand/returns 761: private static SqlCommand BuildQueryCommand( SqlConnection connection, string storedProcName, IDataParameter[] parameters ) { 762: SqlCommand commandnew SqlCommand(storedProcName, connection); 763: command.CommandTypeCommandType.StoredProcedure; 764: foreach( SqlParameter parameter in parameters ) { 765: if( parameter!null ) { 766: // 检查未分配值的输出参数,将其分配以DBNull.Value. 767: if( ( parameter.DirectionParameterDirection.InputOutput||parameter.DirectionParameterDirection.Input ) 768: ( parameter.Valuenull ) ) { 769: parameter.ValueDBNull.Value; 770: } 771: command.Parameters.Add(parameter); 772: } 773: } 774: return command; 775: } 776: /// summary 777: /// 执行存储过程返回影响的行数 778: /// /summary 779: /// param namestoredProcName存储过程名/param 780: /// param nameparameters存储过程参数/param 781: /// param namerowsAffected影响的行数/param 782: /// returns/returns 783: public static int RunProcedure( string storedProcName, out int rowsAffected ) { 784: using( SqlConnection connectionnew SqlConnection(connectionString) ) { 785: int result; 786: connection.Open(); 787: SqlCommand commandnew SqlCommand(storedProcName, connection); 788: rowsAffectedcommand.ExecuteNonQuery(); 789: result(int)command.Parameters[ReturnValue].Value; 790: //Connection.Close(); 791: return result; 792: } 793: } 794: /// summary 795: /// 执行存储过程返回影响的行数 796: /// /summary 797: /// param namestoredProcName存储过程名/param 798: /// param nameparameters存储过程参数/param 799: /// param namerowsAffected影响的行数/param 800: /// returns/returns 801: public static int RunProcedure( string storedProcName, IDataParameter[] parameters, out int rowsAffected ) { 802: using( SqlConnection connectionnew SqlConnection(connectionString) ) { 803: int result; 804: connection.Open(); 805: SqlCommand commandBuildIntCommand(connection, storedProcName, parameters); 806: rowsAffectedcommand.ExecuteNonQuery(); 807: result(int)command.Parameters[ReturnValue].Value; 808: //Connection.Close(); 809: return result; 810: } 811: } 812: /// summary 813: /// 创建 SqlCommand 对象实例(用来返回一个整数值) 814: /// /summary 815: /// param namestoredProcName存储过程名/param 816: /// param nameparameters存储过程参数/param 817: /// returnsSqlCommand 对象实例/returns 818: private static SqlCommand BuildIntCommand( SqlConnection connection, string storedProcName, IDataParameter[] parameters ) { 819: SqlCommand commandBuildQueryCommand(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
