本項目除用到"實時數據庫"外,?還需要用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;

(完)