• Oracle,SQL Server,Access万能数据库通用类！

    view source
    print?
    0001 using System;
    0002 using System.Collections;
    0003 using System.Collections.Specialized;
    0004 using System.Data;
    0005 using System.Data.SqlClient;
    0006 using System.Data.OleDb;
    0007 using System.Data.OracleClient;
    0008 using System.Configuration;
    0009 using System.Reflection;
    0010   
    0011 namespace SystemFramework.DAL
    0012 {
    0013     /**//// <summary>
    0014     /// All rights reserved
    0015     /// 数据访问基础类
    0016     /// 用户可以修改满足自己项目的需要。
    0017     /// </summary>
    0018     public class DataBaseLayer 
    0019     {
    0020         //数据库连接字符串(web.config来配置)
    0021         //<add key="ConnectionString" value="server=127.0.0.1;database=DATABASE;uid=sa;pwd=" />        
    0022         private string connectionString;
    0023         public string ConntionString 
    0024         {
    0025             get 
    0026             {
    0027                 return connectionString ; 
    0028             }
    0029             set 
    0030             {
    0031                 connectionString = value;
    0032             }
    0033         }
    0034   
    0035   
    0036         public DataBaseLayer(string strConnect,string dataType)
    0037         {            
    0038             this.ConntionString = strConnect;
    0039             this.DbType = dataType;
    0040         }
    0041   
    0042   
    0043         public DataBaseLayer()
    0044         {
    0045             this.connectionString = ConfigurationSettings.AppSettings["ConnectionString"] ;
    0046             this.dbType = ConfigurationSettings.AppSettings["DataType"] ;
    0047         }
    0048   
    0049         /**//// <summary>
    0050         /// 数据库类型 
    0051         /// </summary>
    0052         private string dbType;
    0053         public string DbType
    0054         {
    0055             get 
    0056             {
    0057                 if ( dbType == string.Empty || dbType == null )
    0058                 {
    0059                     return "Access";
    0060                 }
    0061                 else
    0062                 {
    0063                     return dbType;
    0064                 }
    0065             }
    0066             set  
    0067             {
    0068                 if ( value != string.Empty  &&  value != null )
    0069                 {
    0070                     dbType = value;
    0071                 }
    0072                 if (dbType ==string.Empty || dbType == null)
    0073                 {
    0074                     dbType = ConfigurationSettings.AppSettings["DataType"];
    0075                 }
    0076                 if ( dbType == string.Empty || dbType == null )
    0077                 {
    0078                     dbType = "Access";
    0079                 }
    0080             }      
    0081         }
    0082   
    0083   
    0084   
    0085           
    0086         转换参数#region 转换参数
    0087         private System.Data.IDbDataParameter iDbPara(string ParaName,string DataType)
    0088         {
    0089             switch(this.DbType)
    0090             {
    0091                 case "SqlServer":
    0092                     return GetSqlPara(ParaName,DataType);
    0093   
    0094                 case "Oracle":
    0095                     return GetOleDbPara(ParaName,DataType);
    0096   
    0097                 case "Access":
    0098                     return GetOleDbPara(ParaName,DataType);
    0099   
    0100                 default :
    0101                     return GetSqlPara(ParaName,DataType);
    0102   
    0103             }
    0104         }
    0105   
    0106         private System.Data.SqlClient.SqlParameter  GetSqlPara( string ParaName , string DataType)
    0107         {
    0108             switch(DataType)
    0109             {
    0110                 case "Decimal":
    0111                     return  new System.Data.SqlClient.SqlParameter ( ParaName, System.Data.SqlDbType.Decimal );
    0112                 case "Varchar":
    0113                     return new System.Data.SqlClient.SqlParameter ( ParaName, System.Data.SqlDbType.VarChar );
    0114                 case "DateTime":
    0115                     return new System.Data.SqlClient.SqlParameter ( ParaName, System.Data.SqlDbType.DateTime );
    0116                 case "Iamge":
    0117                     return new System.Data.SqlClient.SqlParameter ( ParaName, System.Data.SqlDbType.Image );
    0118                 case "Int":
    0119                     return new System.Data.SqlClient.SqlParameter ( ParaName, System.Data.SqlDbType.Int );
    0120                 case "Text":
    0121                     return new System.Data.SqlClient.SqlParameter ( ParaName, System.Data.SqlDbType.NText );
    0122                 default :
    0123                     return new System.Data.SqlClient.SqlParameter ( ParaName, System.Data.SqlDbType.VarChar );
    0124             }
    0125         }
    0126   
    0127         private System.Data.OracleClient.OracleParameter  GetOraclePara( string ParaName , string DataType)
    0128         {
    0129             switch(DataType)
    0130             {
    0131                 case "Decimal":
    0132                     return  new System.Data.OracleClient.OracleParameter( ParaName, System.Data.OracleClient.OracleType.Double);
    0133       
    0134                 case "Varchar":
    0135                     return  new System.Data.OracleClient.OracleParameter ( ParaName, System.Data.OracleClient.OracleType.VarChar );
    0136   
    0137                 case "DateTime":
    0138                     return  new System.Data.OracleClient.OracleParameter ( ParaName, System.Data.OracleClient.OracleType.DateTime );
    0139   
    0140                 case "Iamge":
    0141                     return  new System.Data.OracleClient.OracleParameter ( ParaName, System.Data.OracleClient.OracleType.BFile );
    0142   
    0143                 case "Int":
    0144                     return  new System.Data.OracleClient.OracleParameter ( ParaName, System.Data.OracleClient.OracleType.Int32 );
    0145   
    0146                 case "Text":
    0147                     return  new System.Data.OracleClient.OracleParameter ( ParaName, System.Data.OracleClient.OracleType.LongVarChar );
    0148   
    0149                 default:
    0150                     return  new System.Data.OracleClient.OracleParameter ( ParaName, System.Data.OracleClient.OracleType.VarChar );
    0151   
    0152             }
    0153         }
    0154   
    0155         private System.Data.OleDb.OleDbParameter  GetOleDbPara( string ParaName , string DataType)
    0156         {
    0157             switch(DataType)
    0158             {
    0159                 case "Decimal":
    0160                     return  new System.Data.OleDb.OleDbParameter( ParaName, System.Data.DbType.Decimal);
    0161   
    0162                 case "Varchar":
    0163                     return  new System.Data.OleDb.OleDbParameter ( ParaName, System.Data.DbType.String );
    0164   
    0165                 case "DateTime":
    0166                     return  new System.Data.OleDb.OleDbParameter ( ParaName, System.Data.DbType.DateTime );
    0167   
    0168                 case "Iamge":
    0169                     return   new System.Data.OleDb.OleDbParameter( ParaName, System.Data.DbType.Binary );
    0170   
    0171                 case "Int":
    0172                     return  new System.Data.OleDb.OleDbParameter ( ParaName, System.Data.DbType.Int32 );
    0173   
    0174                 case "Text":
    0175                     return  new System.Data.OleDb.OleDbParameter ( ParaName, System.Data.DbType.String );                    
    0176   
    0177                 default:
    0178                     return  new System.Data.OleDb.OleDbParameter ( ParaName, System.Data.DbType.String );
    0179   
    0180             }
    0181         }
    0182   
    0183         #endregion
    0184           
    0185         创建 Connection 和 Command#region 创建 Connection 和 Command
    0186   
    0187         private IDbConnection GetConnection()
    0188         {
    0189             switch(this.DbType)
    0190             {
    0191                 case "SqlServer":
    0192                     return new System.Data.SqlClient.SqlConnection(this.ConntionString);
    0193   
    0194                 case "Oracle":
    0195                     return new System.Data.OracleClient.OracleConnection(this.ConntionString);
    0196   
    0197                 case "Access":
    0198                     return new System.Data.OleDb.OleDbConnection(this.ConntionString);
    0199                 default:
    0200                     return new System.Data.SqlClient.SqlConnection(this.ConntionString);
    0201             }
    0202         }
    0203   
    0204           
    0205         private IDbCommand GetCommand(string Sql,IDbConnection iConn)
    0206         {
    0207             switch(this.DbType)
    0208             {
    0209                 case "SqlServer":
    0210                     return new System.Data.SqlClient.SqlCommand(Sql,(SqlConnection)iConn);
    0211   
    0212                 case "Oracle":
    0213                     return new System.Data.OracleClient.OracleCommand(Sql,(OracleConnection)iConn);
    0214       
    0215                 case "Access":
    0216                     return new System.Data.OleDb.OleDbCommand(Sql,(OleDbConnection)iConn);
    0217                 default:
    0218                     return new System.Data.SqlClient.SqlCommand(Sql,(SqlConnection)iConn);
    0219             }    
    0220         }
    0221   
    0222         private IDbCommand GetCommand()
    0223         {
    0224             switch(this.DbType)
    0225             {
    0226                 case "SqlServer":
    0227                     return new System.Data.SqlClient.SqlCommand();
    0228   
    0229                 case "Oracle":
    0230                     return new System.Data.OracleClient.OracleCommand();
    0231   
    0232                 case "Access":
    0233                     return new System.Data.OleDb.OleDbCommand();
    0234                 default:
    0235                     return new System.Data.SqlClient.SqlCommand();
    0236             }    
    0237         }
    0238   
    0239         private IDataAdapter GetAdapater(string Sql,IDbConnection iConn)
    0240         {
    0241             switch(this.DbType)
    0242             {
    0243                 case "SqlServer":
    0244                     return new System.Data.SqlClient.SqlDataAdapter(Sql,(SqlConnection)iConn);
    0245   
    0246                 case "Oracle":
    0247                     return new System.Data.OracleClient.OracleDataAdapter(Sql,(OracleConnection)iConn);
    0248   
    0249                 case "Access":
    0250                     return new System.Data.OleDb.OleDbDataAdapter(Sql,(OleDbConnection)iConn);
    0251   
    0252                 default:
    0253                     return new System.Data.SqlClient.SqlDataAdapter(Sql,(SqlConnection)iConn);;
    0254             }
    0255               
    0256         }
    0257   
    0258         private IDataAdapter GetAdapater()
    0259         {
    0260             switch(this.DbType)
    0261             {
    0262                 case "SqlServer":
    0263                     return new System.Data.SqlClient.SqlDataAdapter();
    0264   
    0265                 case "Oracle":
    0266                     return new System.Data.OracleClient.OracleDataAdapter();
    0267   
    0268                 case "Access":
    0269                     return new System.Data.OleDb.OleDbDataAdapter();
    0270   
    0271                 default:
    0272                     return new System.Data.SqlClient.SqlDataAdapter();
    0273             }
    0274         }
    0275   
    0276         private IDataAdapter GetAdapater(IDbCommand iCmd)
    0277         {
    0278             switch(this.DbType)
    0279             {
    0280                 case "SqlServer":
    0281                     return new System.Data.SqlClient.SqlDataAdapter((SqlCommand)iCmd);
    0282   
    0283                 case "Oracle":
    0284                     return new System.Data.OracleClient.OracleDataAdapter((OracleCommand)iCmd);
    0285   
    0286                 case "Access":
    0287                     return new System.Data.OleDb.OleDbDataAdapter((OleDbCommand)iCmd);
    0288   
    0289                 default:
    0290                     return new System.Data.SqlClient.SqlDataAdapter((SqlCommand)iCmd);
    0291             }
    0292         }
    0293         #endregion
    0294   
    0295         执行简单SQL语句#region  执行简单SQL语句
    0296         /**//// <summary>
    0297         /// 执行SQL语句，返回影响的记录数
    0298         /// </summary>
    0299         /// <param name="SQLString">SQL语句</param>
    0300         /// <returns>影响的记录数</returns>
    0301         public int ExecuteSql(string SqlString)
    0302         {
    0303             using (System.Data.IDbConnection  iConn = this.GetConnection())
    0304             {                
    0305                 using (System.Data.IDbCommand iCmd  = GetCommand(SqlString,iConn))
    0306                 {
    0307                     iConn.Open();
    0308                     try
    0309                     {        
    0310                           
    0311                         int rows=iCmd.ExecuteNonQuery();
    0312                         return rows;
    0313                     }
    0314                     catch(System.Exception E)
    0315                     {                    
    0316                         throw new Exception(E.Message);
    0317                     }
    0318                     finally
    0319                     {
    0320                         if(iConn.State != ConnectionState.Closed)
    0321                         {
    0322                             iConn.Close();
    0323                         }
    0324                     }
    0325                 }                
    0326             }
    0327         }
    0328           
    0329         /**//// <summary>
    0330         /// 执行多条SQL语句，实现数据库事务。
    0331         /// </summary>
    0332         /// <param name="SQLStringList">多条SQL语句</param>        
    0333         public  void ExecuteSqlTran(ArrayList SQLStringList)
    0334         {
    0335             using (System.Data.IDbConnection  iConn = this.GetConnection())
    0336             {
    0337                 iConn.Open();
    0338                 using(System.Data.IDbCommand iCmd  =  GetCommand())
    0339                 {
    0340                     iCmd.Connection=iConn;                
    0341                     using(System.Data.IDbTransaction iDbTran = iConn.BeginTransaction())
    0342                     {
    0343                         iCmd.Transaction=iDbTran;    
    0344                         try
    0345                         {           
    0346                             for(int n=0;n<SQLStringList.Count;n++)
    0347                             {
    0348                                 string strsql = SQLStringList[n].ToString();
    0349                                 if ( strsql.Trim().Length>1) 
    0350                                 {
    0351                                     iCmd.CommandText = strsql;
    0352                                     iCmd.ExecuteNonQuery();
    0353                                 }
    0354                             }                                        
    0355                             iDbTran.Commit();                    
    0356                         }
    0357                         catch(System.Exception E)
    0358                         {        
    0359                             iDbTran.Rollback();
    0360                             throw new Exception(E.Message);
    0361                         }
    0362                         finally
    0363                         {
    0364                             if(iConn.State != ConnectionState.Closed)
    0365                             {
    0366                                 iConn.Close();
    0367                             }
    0368                         }
    0369                     }
    0370   
    0371                 }
    0372   
    0373             }
    0374         }
    0375         /**//// <summary>
    0376         /// 执行带一个存储过程参数的的SQL语句。
    0377         /// </summary>
    0378         /// <param name="SQLString">SQL语句</param>
    0379         /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
    0380         /// <returns>影响的记录数</returns>
    0381         public int ExecuteSql(string SqlString,string content)
    0382         {                
    0383             using (System.Data.IDbConnection  iConn = this.GetConnection())
    0384             {
    0385                 using(System.Data.IDbCommand iCmd  = GetCommand(SqlString,iConn))
    0386                 {
    0387                     System.Data.IDataParameter  myParameter = this.iDbPara( "@content", "Text");
    0388                     myParameter.Value = content ;
    0389                     iCmd.Parameters.Add(myParameter);
    0390                     iConn.Open();
    0391                     try
    0392                     {
    0393                           
    0394                         int rows = iCmd.ExecuteNonQuery();
    0395                         return rows;
    0396                     }
    0397                     catch( System.Exception e )
    0398                     {                
    0399                         throw new Exception(e.Message);
    0400                     }
    0401                     finally
    0402                     {
    0403                         if(iConn.State != ConnectionState.Closed)
    0404                         {
    0405                             iConn.Close();
    0406                         }
    0407                     }
    0408                 }
    0409             }
    0410         }        
    0411   
    0412   
    0413         /**//// <summary>
    0414         /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
    0415         /// </summary>
    0416         /// <param name="strSQL">SQL语句</param>
    0417         /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
    0418         /// <returns>影响的记录数</returns>
    0419         public int ExecuteSqlInsertImg(string SqlString,byte[] fs)
    0420         {        
    0421             using (System.Data.IDbConnection  iConn = this.GetConnection())
    0422             {
    0423                 using(System.Data.IDbCommand iCmd  = GetCommand(SqlString,iConn))
    0424                 {
    0425                     System.Data.IDataParameter  myParameter = this.iDbPara( "@content", "Image");
    0426                     myParameter.Value = fs ;
    0427                     iCmd.Parameters.Add(myParameter);
    0428                     iConn.Open();
    0429                     try
    0430                     {
    0431                         int rows = iCmd.ExecuteNonQuery();
    0432                         return rows;
    0433                     }
    0434                     catch( System.Exception e )
    0435                     {                
    0436                         throw new Exception(e.Message);
    0437                     }
    0438                     finally
    0439                     {
    0440                         if(iConn.State != ConnectionState.Closed)
    0441                         {
    0442                             iConn.Close();
    0443                         }
    0444                     }
    0445                 }    
    0446             }
    0447         }
    0448           
    0449         /**//// <summary>
    0450         /// 执行一条计算查询结果语句，返回查询结果（object）。
    0451         /// </summary>
    0452         /// <param name="SQLString">计算查询结果语句</param>
    0453         /// <returns>查询结果（object）</returns>
    0454         public  object GetSingle(string SqlString)
    0455         {
    0456             using (System.Data.IDbConnection  iConn = GetConnection())
    0457             {
    0458                 using (System.Data.IDbCommand iCmd  =  GetCommand(SqlString,iConn))
    0459                 {
    0460                     iConn.Open();
    0461                     try
    0462                     {
    0463                         object obj = iCmd.ExecuteScalar();
    0464                         if((Object.Equals(obj,null))||(Object.Equals(obj,System.DBNull.Value)))
    0465                         {                    
    0466                             return null;
    0467                         }
    0468                         else
    0469                         {
    0470                             return obj;
    0471                         }                
    0472                     }
    0473                     catch(System.Exception e)
    0474                     {                        
    0475                         throw new Exception(e.Message);
    0476                     }    
    0477                     finally
    0478                     {
    0479                         if(iConn.State != ConnectionState.Closed)
    0480                         {
    0481                             iConn.Close();
    0482                         }
    0483                     }
    0484                 }
    0485             }
    0486         }
    0487         /**//// <summary>
    0488         /// 执行查询语句，返回IDataAdapter
    0489         /// </summary>
    0490         /// <param name="strSQL">查询语句</param>
    0491         /// <returns>IDataAdapter</returns>
    0492         public IDataAdapter ExecuteReader(string strSQL)
    0493         {
    0494             using (System.Data.IDbConnection  iConn = this.GetConnection())    
    0495             {
    0496                 iConn.Open();    
    0497                 try
    0498                 {    
    0499                     System.Data.IDataAdapter iAdapter = this.GetAdapater(strSQL,iConn);
    0500                     return iAdapter;
    0501                 }
    0502                 catch(System.Exception e)
    0503                 {                                
    0504                     throw new Exception(e.Message);
    0505                 }        
    0506                 finally
    0507                 {
    0508                     if(iConn.State != ConnectionState.Closed)
    0509                     {
    0510                         iConn.Close();
    0511                     }
    0512                 }    
    0513             }
    0514         }        
    0515         /**//// <summary>
    0516         /// 执行查询语句，返回DataSet
    0517         /// </summary>
    0518         /// <param name="SQLString">查询语句</param>
    0519         /// <returns>DataSet</returns>
    0520         public DataSet Query(string sqlString)
    0521         {
    0522             using (System.Data.IDbConnection  iConn = this.GetConnection())    
    0523             {    
    0524                 using(System.Data.IDbCommand iCmd  =  GetCommand(sqlString,iConn))
    0525                 {
    0526                     DataSet ds = new DataSet();
    0527                     iConn.Open();    
    0528                     try
    0529                     {
    0530                         System.Data.IDataAdapter iAdapter = this.GetAdapater(sqlString,iConn);
    0531                         iAdapter.Fill(ds);
    0532                         return ds;
    0533                     }
    0534                     catch(System.Exception ex)
    0535                     {                
    0536                         throw new Exception(ex.Message);
    0537                     }
    0538                     finally
    0539                     {
    0540                         if(iConn.State != ConnectionState.Closed)
    0541                         {
    0542                             iConn.Close();
    0543                         }
    0544                     }
    0545                 }
    0546             }            
    0547         }
    0548   
    0549         /**//// <summary>
    0550         /// 执行查询语句，返回DataSet
    0551         /// </summary>
    0552         /// <param name="sqlString">查询语句</param>
    0553         /// <param name="dataSet">要填充的DataSet</param>
    0554         /// <param name="tableName">要填充的表名</param>
    0555         /// <returns>DataSet</returns>
    0556         public DataSet Query(string sqlString,DataSet dataSet,string tableName)
    0557         {
    0558             using (System.Data.IDbConnection  iConn = this.GetConnection())    
    0559             {    
    0560                 using(System.Data.IDbCommand iCmd  =  GetCommand(sqlString,iConn))
    0561                 {
    0562                     iConn.Open();    
    0563                     try
    0564                     {
    0565                         System.Data.IDataAdapter iAdapter = this.GetAdapater(sqlString,iConn);
    0566                         ((OleDbDataAdapter)iAdapter).Fill(dataSet,tableName);
    0567                         return dataSet;
    0568                     }
    0569                     catch(System.Exception ex)
    0570                     {                
    0571                         throw new Exception(ex.Message);
    0572                     }
    0573                     finally
    0574                     {
    0575                         if(iConn.State != ConnectionState.Closed)
    0576                         {
    0577                             iConn.Close();
    0578                         }
    0579                     }
    0580                 }    
    0581             }            
    0582         }
    0583   
    0584   
    0585         /**//// <summary>
    0586         /// 执行SQL语句 返回存储过程
    0587         /// </summary>
    0588         /// <param name="sqlString">Sql语句</param>
    0589         /// <param name="dataSet">要填充的DataSet</param>
    0590         /// <param name="startIndex">开始记录</param>
    0591         /// <param name="pageSize">页面记录大小</param>
    0592         /// <param name="tableName">表名称</param>
    0593         /// <returns>DataSet</returns>
    0594         public DataSet Query(string sqlString , DataSet dataSet ,int  startIndex ,int pageSize, string tableName )
    0595         {
    0596             using (System.Data.IDbConnection iConn = this.GetConnection())
    0597             {                    
    0598                 iConn.Open();
    0599                 try
    0600                 {
    0601                     System.Data.IDataAdapter iAdapter = this.GetAdapater(sqlString,iConn);
    0602   
    0603                     ((OleDbDataAdapter)iAdapter).Fill(dataSet,startIndex,pageSize,tableName);        
    0604           
    0605                     return dataSet;
    0606                 }
    0607                 catch(Exception ex)
    0608                 {
    0609                     throw new Exception(ex.Message);
    0610                 }
    0611                 finally
    0612                 {
    0613                     if(iConn.State != ConnectionState.Closed)
    0614                     {
    0615                         iConn.Close();
    0616                     }
    0617                 }
    0618             }
    0619         }
    0620   
    0621   
    0622         /**//// <summary>
    0623         /// 执行查询语句，向XML文件写入数据
    0624         /// </summary>
    0625         /// <param name="sqlString">查询语句</param>
    0626         /// <param name="xmlPath">XML文件路径</param>
    0627         public void WriteToXml(string sqlString,string xmlPath)
    0628         {
    0629             Query(sqlString).WriteXml(xmlPath);
    0630         }
    0631   
    0632         /**//// <summary>
    0633         /// 执行查询语句
    0634         /// </summary>
    0635         /// <param name="SqlString">查询语句</param>
    0636         /// <returns>DataTable </returns>
    0637         public DataTable ExecuteQuery(string sqlString)
    0638         {
    0639             using (System.Data.IDbConnection  iConn = this.GetConnection())    
    0640             {    
    0641                 //System.Data.IDbCommand iCmd  =  GetCommand(sqlString,iConn);
    0642                 DataSet ds = new DataSet();
    0643                 try
    0644                 {
    0645                     System.Data.IDataAdapter iAdapter = this.GetAdapater(sqlString,iConn);            
    0646                     iAdapter.Fill(ds);
    0647                 }
    0648                 catch(System.Exception e)
    0649                 {                
    0650                     throw new Exception(e.Message);
    0651                 }    
    0652                 finally
    0653                 {
    0654                     if(iConn.State != ConnectionState.Closed)
    0655                     {
    0656                         iConn.Close();
    0657                     }
    0658                 }
    0659                 return ds.Tables[0];
    0660             }
    0661         }
    0662   
    0663         /**//// <summary>
    0664         /// 执行查询语句
    0665         /// </summary>
    0666         /// <param name="SqlString">查询语句</param>
    0667         /// <returns>DataTable </returns>
    0668         public DataTable ExecuteQuery(string SqlString,string Proc)
    0669         {
    0670             using (System.Data.IDbConnection  iConn = this.GetConnection())    
    0671             {    
    0672                 using(System.Data.IDbCommand iCmd  =  GetCommand(SqlString,iConn))
    0673                 {
    0674                     iCmd.CommandType = CommandType.StoredProcedure;
    0675                     DataSet ds = new DataSet();
    0676                     try
    0677                     {
    0678                         System.Data.IDataAdapter iDataAdapter = this.GetAdapater(SqlString,iConn);            
    0679                         iDataAdapter.Fill(ds);
    0680                     }
    0681                     catch(System.Exception e)
    0682                     {                
    0683                         throw new Exception(e.Message);
    0684                     }    
    0685                     finally
    0686                     {
    0687                         if(iConn.State != ConnectionState.Closed)
    0688                         {
    0689                             iConn.Close();
    0690                         }
    0691                     }
    0692                     return ds.Tables[0];
    0693                 }
    0694           
    0695                   
    0696             }
    0697         }
    0698   
    0699         /**//// <summary>
    0700         /// 
    0701         /// </summary>
    0702         /// <param name="Sql"></param>
    0703         /// <returns></returns>
    0704         public DataView ExeceuteDataView(string Sql)
    0705         {
    0706             using (System.Data.IDbConnection  iConn = this.GetConnection())    
    0707             {    
    0708                 using(System.Data.IDbCommand iCmd  =  GetCommand(Sql,iConn))
    0709                 {
    0710                     DataSet ds = new DataSet();
    0711                     try
    0712                     {
    0713                         System.Data.IDataAdapter iDataAdapter = this.GetAdapater(Sql,iConn);            
    0714                         iDataAdapter.Fill(ds);
    0715                         return ds.Tables[0].DefaultView;
    0716                     }
    0717                     catch(System.Exception e)
    0718                     {                
    0719                         throw new Exception(e.Message);
    0720                     }    
    0721                     finally
    0722                     {
    0723                         if(iConn.State != ConnectionState.Closed)
    0724                         {
    0725                             iConn.Close();
    0726                         }
    0727                     }
    0728                 }    
    0729             }
    0730         }
    0731   
    0732         #endregion
    0733   
    0734         执行带参数的SQL语句#region 执行带参数的SQL语句
    0735         /**//// <summary>
    0736         /// 执行SQL语句，返回影响的记录数
    0737         /// </summary>
    0738         /// <param name="SQLString">SQL语句</param>
    0739         /// <returns>影响的记录数</returns>
    0740         public  int ExecuteSql(string SQLString,params IDataParameter[] iParms)
    0741         {
    0742             using (System.Data.IDbConnection  iConn = this.GetConnection())    
    0743             {    
    0744                 System.Data.IDbCommand iCmd  =  GetCommand();
    0745                 {
    0746                     try
    0747                     {        
    0748                         PrepareCommand(out iCmd, iConn, null , SQLString, iParms );
    0749                         int rows=iCmd.ExecuteNonQuery();
    0750                         iCmd.Parameters.Clear();
    0751                         return rows;
    0752                     }
    0753                     catch(System.Exception E)
    0754                     {                
    0755                         throw new Exception( E.Message );
    0756                     }
    0757                     finally
    0758                     {
    0759                         iCmd.Dispose();
    0760                         if(iConn.State != ConnectionState.Closed)
    0761                         {
    0762                             iConn.Close();
    0763                         }
    0764                     }
    0765                 }                
    0766             }
    0767         }
    0768           
    0769               
    0770         /**//// <summary>
    0771         /// 执行多条SQL语句，实现数据库事务。
    0772         /// </summary>
    0773         /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
    0774         public void ExecuteSqlTran(Hashtable SQLStringList)
    0775         {            
    0776             using (System.Data.IDbConnection  iConn = this.GetConnection())    
    0777             {
    0778                 iConn.Open();
    0779                 using (IDbTransaction  iTrans = iConn.BeginTransaction()) 
    0780                 {
    0781                     System.Data.IDbCommand iCmd  =  GetCommand();
    0782                     try 
    0783                     {
    0784                         //循环
    0785                         foreach ( DictionaryEntry myDE in SQLStringList)
    0786                         {    
    0787                             string     cmdText = myDE.Key.ToString();
    0788                             IDataParameter[] iParms=( IDataParameter[] ) myDE.Value;
    0789                             PrepareCommand( out iCmd , iConn , iTrans , cmdText , iParms );
    0790                             int val = iCmd.ExecuteNonQuery();
    0791                             iCmd.Parameters.Clear();
    0792                         }
    0793                         iTrans.Commit();                
    0794                     }
    0795                     catch 
    0796                     {
    0797                         iTrans.Rollback();
    0798                         throw;
    0799                     }
    0800                     finally
    0801                     {
    0802                         iCmd.Dispose();
    0803                         if(iConn.State != ConnectionState.Closed)
    0804                         {
    0805                             iConn.Close();
    0806                         }
    0807                     }
    0808   
    0809                 }                
    0810             }
    0811         }
    0812       
    0813                   
    0814         /**//// <summary>
    0815         /// 执行一条计算查询结果语句，返回查询结果（object）。
    0816         /// </summary>
    0817         /// <param name="SQLString">计算查询结果语句</param>
    0818         /// <returns>查询结果（object）</returns>
    0819         public object GetSingle(string SQLString,params IDataParameter[] iParms)
    0820         {
    0821             using (System.Data.IDbConnection  iConn = this.GetConnection())    
    0822             {
    0823                 System.Data.IDbCommand iCmd  =  GetCommand();    
    0824                 {
    0825                     try
    0826                     {
    0827                         PrepareCommand( out iCmd, iConn, null , SQLString, iParms );
    0828                         object obj = iCmd.ExecuteScalar();
    0829                         iCmd.Parameters.Clear();
    0830                         if((Object.Equals(obj,null))||(Object.Equals(obj,System.DBNull.Value)))
    0831                         {                    
    0832                             return null;
    0833                         }
    0834                         else
    0835                         {
    0836                             return obj;
    0837                         }                
    0838                     }
    0839                     catch(System.Exception e)
    0840                     {                
    0841                         throw new Exception(e.Message);
    0842                     }
    0843                     finally
    0844                     {
    0845                         iCmd.Dispose();
    0846                         if(iConn.State != ConnectionState.Closed)
    0847                         {
    0848                             iConn.Close();
    0849                         }
    0850                     }
    0851                 }
    0852             }
    0853         }
    0854           
    0855         /**//// <summary>
    0856         /// 执行查询语句，返回IDataReader
    0857         /// </summary>
    0858         /// <param name="strSQL">查询语句</param>
    0859         /// <returns> IDataReader </returns>
    0860         public IDataReader ExecuteReader(string SQLString,params IDataParameter[] iParms)
    0861         {        
    0862             System.Data.IDbConnection  iConn = this.GetConnection();
    0863             {
    0864                 System.Data.IDbCommand iCmd  =  GetCommand();
    0865                 {
    0866                     try
    0867                     {
    0868                         PrepareCommand(out iCmd, iConn , null , SQLString , iParms);
    0869                         System.Data.IDataReader iReader = iCmd.ExecuteReader();
    0870                         iCmd.Parameters.Clear();
    0871                         return iReader;
    0872                     }
    0873                     catch(System.Exception e)
    0874                     {                                
    0875                         throw new Exception(e.Message);
    0876                     }        
    0877                     finally
    0878                     {
    0879                         iCmd.Dispose();
    0880                         if(iConn.State != ConnectionState.Closed)
    0881                         {
    0882                             iConn.Close();
    0883                         }
    0884                     }            
    0885                 }
    0886             }    
    0887         }        
    0888           
    0889         /**//// <summary>
    0890         /// 执行查询语句，返回DataSet
    0891         /// </summary>
    0892         /// <param name="SQLString">查询语句</param>
    0893         /// <returns>DataSet</returns>
    0894         public DataSet Query(string sqlString,params IDataParameter[] iParms)
    0895         {
    0896             using (System.Data.IDbConnection  iConn = this.GetConnection())    
    0897             {
    0898                 IDbCommand iCmd = GetCommand();
    0899                 {
    0900                     PrepareCommand(out iCmd , iConn , null , sqlString , iParms );
    0901                     try
    0902                     {    
    0903                         IDataAdapter iAdapter =  this.GetAdapater(sqlString,iConn);
    0904                         DataSet ds = new DataSet();                                                
    0905                         iAdapter.Fill(ds);
    0906                         iCmd.Parameters.Clear();
    0907                         return ds;
    0908                     }
    0909                     catch(System.Exception ex)
    0910                     {                
    0911                         throw new Exception(ex.Message);
    0912                     }
    0913                     finally
    0914                     {
    0915                         iCmd.Dispose();
    0916                         if(iConn.State != ConnectionState.Closed)
    0917                         {
    0918                             iConn.Close();
    0919                         }
    0920                     }
    0921                 }    
    0922             }
    0923         }
    0924           
    0925           
    0926         /**//// <summary>
    0927         /// 初始化Command
    0928         /// </summary>
    0929         /// <param name="iCmd"></param>
    0930         /// <param name="iConn"></param>
    0931         /// <param name="iTrans"></param>
    0932         /// <param name="cmdText"></param>
    0933         /// <param name="iParms"></param>
    0934         private void PrepareCommand(out IDbCommand iCmd,IDbConnection iConn,System.Data.IDbTransaction iTrans, string cmdText, IDataParameter[] iParms) 
    0935         {
    0936             if (iConn.State != ConnectionState.Open)
    0937                 iConn.Open();
    0938             iCmd = this.GetCommand();
    0939             iCmd.Connection =  iConn;
    0940             iCmd.CommandText = cmdText;
    0941             if (iTrans != null)
    0942                 iCmd.Transaction = iTrans;
    0943             iCmd.CommandType = CommandType.Text;//cmdType;
    0944             if (iParms != null) 
    0945             {
    0946                 foreach (IDataParameter parm in iParms)
    0947                     iCmd.Parameters.Add(parm);
    0948             }
    0949         }
    0950   
    0951         #endregion
    0952   
    0953         存储过程操作#region 存储过程操作
    0954   
    0955         /**//// <summary>
    0956         /// 执行存储过程
    0957         /// </summary>
    0958         /// <param name="storedProcName">存储过程名</param>
    0959         /// <param name="parameters">存储过程参数</param>
    0960         /// <returns>SqlDataReader</returns>
    0961         public SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters )
    0962         {
    0963             System.Data.IDbConnection  iConn = this.GetConnection();
    0964             {
    0965                 iConn.Open();
    0966                   
    0967                 using(SqlCommand sqlCmd = BuildQueryCommand(iConn,storedProcName, parameters))
    0968                 {
    0969                     return  sqlCmd.ExecuteReader(CommandBehavior.CloseConnection);    
    0970                 }
    0971             }
    0972         }
    0973   
    0974         /**//// <summary>
    0975         /// 执行存储过程
    0976         /// </summary>
    0977         /// <param name="storedProcName">存储过程名</param>
    0978         /// <param name="parameters">存储过程参数</param>
    0979         /// <param name="tableName">DataSet结果中的表名</param>
    0980         /// <returns>DataSet</returns>
    0981         public  DataSet RunProcedure(string storedProcName, IDataParameter[] parameters ,string tableName)
    0982         {
    0983   
    0984             using (System.Data.IDbConnection   iConn = this.GetConnection())    
    0985             {
    0986                 DataSet dataSet = new DataSet();
    0987                 iConn.Open();
    0988                 System.Data.IDataAdapter iDA = this.GetAdapater();
    0989                 iDA = this.GetAdapater( BuildQueryCommand(iConn, storedProcName, parameters ) );
    0990   
    0991                 ((SqlDataAdapter)iDA).Fill( dataSet,tableName);
    0992                 if(iConn.State != ConnectionState.Closed)
    0993                 {
    0994                     iConn.Close();
    0995                 }
    0996                 return dataSet;
    0997             }
    0998         }
    0999   
    1000   
    1001   
    1002         /**//// <summary>
    1003         /// 执行存储过程
    1004         /// </summary>
    1005         /// <param name="storedProcName">存储过程名</param>
    1006         /// <param name="parameters">存储过程参数</param>
    1007         /// <param name="tableName">DataSet结果中的表名</param>
    1008         /// <param name="startIndex">开始记录索引</param>
    1009         /// <param name="pageSize">页面记录大小</param>
    1010         /// <returns>DataSet</returns>
    1011         public  DataSet RunProcedure(string storedProcName, IDataParameter[] parameters ,int startIndex,int pageSize,string tableName)
    1012         {
    1013   
    1014             using (System.Data.IDbConnection   iConn = this.GetConnection())    
    1015             {
    1016                 DataSet dataSet = new DataSet();
    1017                 iConn.Open();
    1018                 System.Data.IDataAdapter iDA = this.GetAdapater();
    1019                 iDA = this.GetAdapater( BuildQueryCommand(iConn, storedProcName, parameters ) );
    1020   
    1021                 ((SqlDataAdapter)iDA).Fill( dataSet,startIndex,pageSize,tableName);
    1022                 if(iConn.State != ConnectionState.Closed)
    1023                 {
    1024                     iConn.Close();
    1025                 }
    1026                 return dataSet;
    1027             }
    1028         }
    1029   
    1030         /**//// <summary>
    1031         /// 执行存储过程 填充已经存在的DataSet数据集 
    1032         /// </summary>
    1033         /// <param name="storeProcName">存储过程名称</param>
    1034         /// <param name="parameters">存储过程参数</param>
    1035         /// <param name="dataSet">要填充的数据集</param>
    1036         /// <param name="tablename">要填充的表名</param>
    1037         /// <returns></returns>
    1038         public DataSet RunProcedure(string storeProcName,IDataParameter[] parameters,DataSet dataSet,string tableName)
    1039         {
    1040             using (System.Data.IDbConnection iConn = this.GetConnection())
    1041             {
    1042                 iConn.Open();
    1043                 System.Data.IDataAdapter iDA = this.GetAdapater();
    1044                 iDA = this.GetAdapater(BuildQueryCommand(iConn,storeProcName,parameters));
    1045                   
    1046                 ((SqlDataAdapter)iDA).Fill(dataSet,tableName);
    1047                   
    1048                 if(iConn.State != ConnectionState.Closed)
    1049                 {
    1050                     iConn.Close();
    1051                 }
    1052   
    1053                 return dataSet;
    1054             }
    1055         }
    1056   
    1057         /**//// <summary>
    1058         /// 执行存储过程并返回受影响的行数
    1059         /// </summary>
    1060         /// <param name="storedProcName"></param>
    1061         /// <param name="parameters"></param>
    1062         /// <returns></returns>
    1063         public int RunProcedureNoQuery(string storedProcName, IDataParameter[] parameters )
    1064         {
    1065   
    1066             int result = 0;
    1067             using (System.Data.IDbConnection  iConn = this.GetConnection())    
    1068             {
    1069                 iConn.Open();
    1070                 using(SqlCommand scmd = BuildQueryCommand(iConn,storedProcName,parameters))
    1071                 {
    1072                     result = scmd.ExecuteNonQuery();
    1073                 }    
    1074   
    1075                 if(iConn.State != ConnectionState.Closed)
    1076                 {
    1077                     iConn.Close();
    1078                 }
    1079             }
    1080   
    1081             return result ;
    1082         }
    1083   
    1084         public string RunProcedureExecuteScalar(string storeProcName,IDataParameter[] parameters)
    1085         {
    1086             string result = string.Empty;
    1087             using (System.Data.IDbConnection iConn = this.GetConnection())
    1088             {
    1089                   
    1090                 iConn.Open();
    1091                 using(SqlCommand scmd = BuildQueryCommand(iConn,storeProcName,parameters))
    1092                 {
    1093                     object obj = scmd.ExecuteScalar();
    1094                     if(obj == null)
    1095                         result = null;
    1096                     else
    1097                         result = obj.ToString();
    1098                 }
    1099   
    1100                 if(iConn.State != ConnectionState.Closed)
    1101                 {
    1102                     iConn.Close();
    1103                 }
    1104   
    1105             }
    1106   
    1107             return result;
    1108         }
    1109           
    1110         /**//// <summary>
    1111         /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
    1112         /// </summary>
    1113         /// <param name="connection">数据库连接</param>
    1114         /// <param name="storedProcName">存储过程名</param>
    1115         /// <param name="parameters">存储过程参数</param>
    1116         /// <returns>SqlCommand</returns>
    1117         private SqlCommand BuildQueryCommand(IDbConnection iConn,string storedProcName, IDataParameter[] parameters)
    1118         {            
    1119               
    1120             IDbCommand iCmd = GetCommand(storedProcName,iConn);
    1121             iCmd.CommandType = CommandType.StoredProcedure;
    1122             if (parameters == null)
    1123             {
    1124                 return (SqlCommand)iCmd;
    1125             }
    1126             foreach (IDataParameter parameter in parameters)
    1127             {
    1128                 iCmd.Parameters.Add( parameter );
    1129             }
    1130             return (SqlCommand)iCmd;            
    1131         }
    1132           
    1133         /**//// <summary>
    1134         /// 执行存储过程，返回影响的行数        
    1135         /// </summary>
    1136         /// <param name="storedProcName">存储过程名</param>
    1137         /// <param name="parameters">存储过程参数</param>
    1138         /// <param name="rowsAffected">影响的行数</param>
    1139         /// <returns></returns>
    1140         public int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected )
    1141         {
    1142             using (System.Data.IDbConnection  iConn = this.GetConnection())    
    1143             {
    1144                 int result;
    1145                 iConn.Open();
    1146                 using(SqlCommand sqlCmd = BuildIntCommand(iConn,storedProcName, parameters ))
    1147                 {
    1148                     rowsAffected = sqlCmd.ExecuteNonQuery();
    1149                     result = (int)sqlCmd.Parameters["ReturnValue"].Value;
    1150   
    1151                     if(iConn.State != ConnectionState.Closed)
    1152                     {
    1153                         iConn.Close();
    1154                     }
    1155                     return result;                
    1156                 }
    1157             }
    1158         }
    1159           
    1160         /**//// <summary>
    1161         /// 创建 SqlCommand 对象实例(用来返回一个整数值)    
    1162         /// </summary>
    1163         /// <param name="storedProcName">存储过程名</param>
    1164         /// <param name="parameters">存储过程参数</param>
    1165         /// <returns>SqlCommand 对象实例</returns>
    1166         private SqlCommand BuildIntCommand(IDbConnection iConn,string storedProcName, IDataParameter[] parameters)
    1167         {
    1168             SqlCommand sqlCmd = BuildQueryCommand(iConn,storedProcName, parameters );
    1169             sqlCmd.Parameters.Add( new SqlParameter ( "ReturnValue",
    1170                 SqlDbType.Int,4,ParameterDirection.ReturnValue,
    1171                 false,0,0,string.Empty,DataRowVersion.Default,null ));
    1172             return sqlCmd;
    1173         }
    1174         #endregion    
    1175   
    1176   
    1177     }
    1178 }
  • 相关阅读:
    html5实现GIF图效果
    响应式网页设计简单入门（强烈推薦！！！！）
    form表单提交和ajax表单提交，关于移动端如何通过软键盘上的【搜索】和【前进】进行提交操作
    【JavaScript】Write和Writeln的区别
    HTML表格中各元素标签的位置对style属性有效性的影响
    HTML textarea 莫名其妙出现几个空格的原因
    PHP 正则匹配手机号
    极简主义法编写JavaScript类
    jQuery on绑定事件
    jQuery ajax() 参数,回调函数，数据类型，发送数据到服务器，高级选项
  • 原文地址：https://www.cnblogs.com/moss_tan_jun/p/1847386.html
Copyright © 2020-2023  润新知