FoolWeb 各层代码实例

未命名

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: }
posted @ 2011-11-21 16:42  地狱在左  阅读(582)  评论(0编辑  收藏  举报