Oracle,SqlServer,Access数据库通用访问类设计

本项目除用到"实时数据库"外, 还需要用Oracle数据库存储大量的配置信息和生成的数据,而且对 Oracle的读取相当的频繁,在项目开始之处,数据访问就是一个很令人烦恼的问题,仅仅数据访问类就修改了好多版本,直到目前正在使用的这个版本.同时为了应付开发过程中不时需要读取SqlServer和Access数据库,所以就写成三种数据源的通用访问类,虽然有点四不象,不过挺省事的,嘻嘻!

此模块分为两个CS文件:
DataFactory.cs

using  System; 
using  System.Data; 
using  System.Data.Common; 
using  System.Data.SqlClient; 
using  System.Data.OleDb; 
using  System.Data.OracleClient; 
using  System.Collections;

namespace  REAP.Utility
... {
     public   enum  DataBaseType
     ... {
        Access,
        SQLServer,
        Oracle
    }

     /**/ ///   <summary>
     ///  DataFactory  的摘要说明。
     ///   </summary>
     class  DataFactory
     ... {
         public  DataFactory()
         ... { }

         public   static  IDbConnection CreateConnection( string  ConnectionString, DataBaseType dbtype)
         ... {
            IDbConnection cnn;

             switch  (dbtype)
             ... {
                 case  DataBaseType.Access:
                    cnn  =   new  OleDbConnection(ConnectionString);
                     break ;

                 case  DataBaseType.SQLServer:
                    cnn  =   new  SqlConnection(ConnectionString);
                     break ;

                 case  DataBaseType.Oracle:
                    cnn  =   new  OracleConnection(ConnectionString);
                     break ;

                 default :
                    cnn  =   new  SqlConnection(ConnectionString);
                     break ;
            }
             return  cnn;
        }

         public   static  IDbCommand CreateCommand(DataBaseType dbtype, IDbConnection cnn)
         ... {
            IDbCommand cmd;
             switch  (dbtype)
             ... {
                 case  DataBaseType.Access:
                    cmd  =   new  OleDbCommand( "" , (OleDbConnection)cnn);
                     break ;

                 case  DataBaseType.SQLServer:
                    cmd  =   new  SqlCommand( "" , (SqlConnection)cnn);
                     break ;

                 case  DataBaseType.Oracle:
                    cmd  =   new  OracleCommand( "" , (OracleConnection)cnn);
                     break ;
                 default :
                    cmd  =   new  SqlCommand( "" , (SqlConnection)cnn);
                     break ;
            }

             return  cmd;
        }

         public   static  IDbCommand CreateCommand( string  CommandText, DataBaseType dbtype, IDbConnection cnn)
         ... {
            IDbCommand cmd;
             switch  (dbtype)
             ... {
                 case  DataBaseType.Access:
                    cmd  =   new  OleDbCommand(CommandText, (OleDbConnection)cnn);
                     break ;

                 case  DataBaseType.SQLServer:
                    cmd  =   new  SqlCommand(CommandText, (SqlConnection)cnn);
                     break ;

                 case  DataBaseType.Oracle:
                    cmd  =   new  OracleCommand(CommandText, (OracleConnection)cnn);
                     break ;
                 default :
                    cmd  =   new  SqlCommand(CommandText, (SqlConnection)cnn);
                     break ;
            }

             return  cmd;
        }

         public   static  DbDataAdapter CreateAdapter(IDbCommand cmd, DataBaseType dbtype)
         ... {
            DbDataAdapter da;
             switch  (dbtype)
             ... {
                 case  DataBaseType.Access:
                    da  =   new  OleDbDataAdapter((OleDbCommand)cmd);
                     break ;

                 case  DataBaseType.SQLServer:
                    da  =   new  SqlDataAdapter((SqlCommand)cmd);
                     break ;

                 case  DataBaseType.Oracle:
                    da  =   new  OracleDataAdapter((OracleCommand)cmd);
                     break ;

                 default :
                    da  =   new  SqlDataAdapter((SqlCommand)cmd);
                     break ;
            }

             return  da;
        }

         public   static  IDataParameter CreateParameter(DataBaseType dbtype)
         ... {
            IDataParameter param  =   null ;
             switch  (dbtype)
             ... {
                 case  DataBaseType.Access:
                    param  =   new  OleDbParameter();
                     break ;

                 case  DataBaseType.SQLServer:
                    param  =   new  SqlParameter();
                     break ;

                 case  DataBaseType.Oracle:
                    param  =   new  OracleParameter();
                     break ;

                 default :
                    param  =   new  SqlParameter();
                     break ;
            }

             return  param;
        }
    }
}

DBAccess.cs

using  System; 
using  System.Data; 
using  System.Data.Common; 
using  System.Data.SqlClient; 
using  System.Data.OleDb; 
using  System.Data.OracleClient;
using  System.Configuration;

namespace  REAP.Utility
... {
     /**/ ///   <summary>
     ///  由于可能会在多种数据源，如ORACLE，SQLSERVER，ACCESS等之间进行切换，
     ///  所以将数据源连接字符串和数据源类型定义为类属性，在默认情况下有配置文件定义；
     ///  当需要在两种不同的数据源之间进行切换时，可以重新为属性赋值。
     ///   </summary>
     public   class  DBAccess
     ... {
         属性设置 #region  属性设置
         private   string  _ConnectionString  =   "" ;
         private  DataBaseType _DataSourceType  =  DataBaseType.Oracle;

         /**/ ///   <summary>
         ///  数据源连接字符串
         ///   </summary>
         public   string  ConnectionString
         ... {
             get
             ... {
                 if  (_ConnectionString  ==   "" )
                 ... {
                    _ConnectionString  =  ConfigurationSettings.AppSettings[ " StrConn " ];
                }
                 return  _ConnectionString;
            }
             set
             ... {
                _ConnectionString  =  value;
            }
        }

         /**/ ///   <summary>
         ///  数据库库类型（默认情况下为Oracle）
         ///   </summary>
         public  DataBaseType DataSourceType
         ... {
             get
             ... {
                 return  _DataSourceType;
            }
             set
             ... {
                _DataSourceType  =  value;
            }
        }

         public  DBAccess()
         ... {}
         #endregion

         DataSet生成操作 #region  DataSet生成操作

         /**/ ///   <summary>
         ///  根据SQL语句创建DataSet数据集；
         ///  可以执行多条SELECT查询语句，查询语句之间用分号标记，如下所示：
         ///  SELECT * FROM TABLE1;SELECT * FROM TABLE2
         ///   </summary>
         ///   <param name="sqlQuery"> SQL语句 </param>
         ///   <returns> 返回DataSet数据集 </returns>
         public  DataSet GetDataSet( string  sqlQuery)
         ... {
            IDbConnection cn  =  DataFactory.CreateConnection( _ConnectionString, _DataSourceType );
            IDbCommand cmd  =   null ;
            DbDataAdapter da  =   null ;
            DataSet dsResult  =   new  DataSet();
             try
             ... {
                 string [] strSqls  =  sqlQuery.Split( ' ; ' );
                 foreach  ( string  strSql  in  strSqls)
                 ... {
                    cmd  =  DataFactory.CreateCommand(strSql, _DataSourceType, cn);
                    da  =  DataFactory.CreateAdapter(cmd, _DataSourceType);
                    DataSet ds  =   new  DataSet();
                    da.Fill(ds);

                     if  (strSqls.Length  ==   1 )
                     ... {
                        dsResult  =  ds;
                    }
                     else
                     ... {
                        DataTable dt  =  ds.Tables[ 0 ].Clone();
                         foreach  (DataRow dr  in  ds.Tables[ 0 ].Rows)
                         ... {
                            dt.ImportRow(dr);
                        }
                        dsResult.Tables.Add(dt);
                    }
                }
            }
             finally
             ... { 
                da.Dispose();
                cmd.Dispose();
                cn.Close();
                cn.Dispose();
            }

             return  dsResult;
        }

         /**/ ///   <summary>
         ///  执行SELECT查询语句，并将结果以TABLE的形式加入到指定DataSet数据集；
         ///  可以执行多条SELECT查询语句，查询语句之间用分号标记，如下所示：
         ///  SELECT * FROM TABLE1;SELECT * FROM TABLE2
         ///   </summary>
         ///   <param name="sqlQuery"> SQL语句 </param>
         ///   <param name="dsTarget"> 已存在的DataSet数据集 </param>
         ///   <returns> 返回DataSet数据集 </returns>
         public  DataSet GetDataSet( string  sqlQuery,DataSet dsTarget)
         ... {
            IDbConnection cn  =  DataFactory.CreateConnection(_ConnectionString, _DataSourceType);
            IDbCommand cmd  =   null ;
            DbDataAdapter da  =   null ;
             try
             ... {
                 string [] strSqls  =  sqlQuery.Split( ' ; ' );
                 foreach  ( string  strSql  in  strSqls)
                 ... {
                    cmd  =  DataFactory.CreateCommand(strSql, _DataSourceType, cn);
                    da  =  DataFactory.CreateAdapter(cmd, _DataSourceType);
                    DataSet ds  =   new  DataSet();
                    da.Fill(ds);

                    DataTable dt  =  ds.Tables[ 0 ].Clone();
                     foreach  (DataRow dr  in  ds.Tables[ 0 ].Rows)
                     ... {
                        dt.ImportRow(dr);
                    }
                    dsTarget.Tables.Add(dt);
                }
            }
             finally
             ... {
                da.Dispose();
                cmd.Dispose();
                cn.Close();
                cn.Dispose();
            }
             return  dsTarget;
        }
        
         #endregion

         SQL执行操作 #region  SQL执行操作

         /**/ ///   <summary>
         ///  根据SQL语句执行ExecuteNonQuery操作
         ///   </summary>
         ///   <param name="sqlQuery"> SQL语句 </param>
         ///   <returns> 返回bool表示是否成功 </returns>
         public   bool  ExecuteNonQuery( string  sqlQuery)
         ... {
            IDbConnection cn  =  DataFactory.CreateConnection(_ConnectionString, _DataSourceType);
            cn.Open();
            IDbCommand cmd  =  DataFactory.CreateCommand(sqlQuery, _DataSourceType, cn);

             try
             ... {
                cmd.ExecuteNonQuery();
                 return   true ;
            }
             catch  (Exception ex)
             ... {
                 string  strEx  =  ex.Message;
                 return   false ;
            }
             finally
             ... {
                cmd.Dispose();
                cn.Close();
                cn.Dispose();
            }
        }

         #endregion

         DataReader操作 #region  DataReader操作

         /**/ ///   <summary>
         ///  根据SQL语句创建DataReader
         ///   </summary>
         ///   <param name="sqlQuery"> SQL语句 </param>
         ///   <returns> 返回DataReader </returns>
         public  IDataReader GetDataReader( string  sqlQuery)
         ... {
            IDbConnection cn  =  DataFactory.CreateConnection(_ConnectionString, _DataSourceType);
            IDbCommand cmd  =  DataFactory.CreateCommand(sqlQuery, _DataSourceType, cn);
            IDataReader da  =  cmd.ExecuteReader(CommandBehavior.CloseConnection);

             return  da;
        }

         #endregion

         // 其他功能,故意省略
    }
}

举例如下:

默认情况下是访问Oracle数据库,数据库连接字符串已经在Config文件中定义,所以不需要再设置其ConnectionString和DataSourceType属性,此时返回一个DataSet的代码如下:

DBAccess db  =   new  DBAccess();
// 同时执行两条查询语句
string  strSql  =   " SELECT * FROM TABLE1;SELECT * FROM TABLE2 " ;
DataSet ds  =  db.GetDataSet(strSql);

但是如果在程序中需要临时访问SqlServer数据库,则需要设置属性,此时代码如下:

DBAccess db  =   new  DBAccess();
db.ConnectionString  =   " server=localhost;UID=sa;PWD=123456;DATABASE=Money;connect timeout=120 " ;
db.DataSourceType  =  DataBaseType.SQLServer;

(完)