本項(xiàng)目除用到"實(shí)時(shí)數(shù)據(jù)庫(kù)"外,?還需要用Oracle數(shù)據(jù)庫(kù)存儲(chǔ)大量的配置信息和生成的數(shù)據(jù),而且對(duì) Oracle的讀取相當(dāng)?shù)念l繁,在項(xiàng)目開(kāi)始之處,數(shù)據(jù)訪問(wèn)就是一個(gè)很令人煩惱的問(wèn)題,僅僅數(shù)據(jù)訪問(wèn)類就修改了好多版本,直到目前正在使用的這個(gè)版本.同時(shí)為了應(yīng)付開(kāi)發(fā)過(guò)程中不時(shí)需要讀取SqlServer和Access數(shù)據(jù)庫(kù),所以就寫成三種數(shù)據(jù)源的通用訪問(wèn)類,雖然有點(diǎn)四不象,不過(guò)挺省事的,嘻嘻!

此模塊分為兩個(gè)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??的摘要說(shuō)明。
???? /// ? </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>
???? /// ?由于可能會(huì)在多種數(shù)據(jù)源，如ORACLE，SQLSERVER，ACCESS等之間進(jìn)行切換，
???? /// ?所以將數(shù)據(jù)源連接字符串和數(shù)據(jù)源類型定義為類屬性，在默認(rèn)情況下有配置文件定義；
???? /// ?當(dāng)需要在兩種不同的數(shù)據(jù)源之間進(jìn)行切換時(shí)，可以重新為屬性賦值。
???? /// ? </summary>
???? public ? class ?DBAccess
???? ... {
???????? 屬性設(shè)置 #region ?屬性設(shè)置
???????? private ? string ?_ConnectionString? = ? "" ;
???????? private ?DataBaseType?_DataSourceType? = ?DataBaseType.Oracle;

???????? /**/ /// ? <summary>
???????? /// ?數(shù)據(jù)源連接字符串
???????? /// ? </summary>
???????? public ? string ?ConnectionString
???????? ... {
???????????? get
???????????? ... {
???????????????? if ?(_ConnectionString? == ? "" )
???????????????? ... {
????????????????????_ConnectionString? = ?ConfigurationSettings.AppSettings[ " StrConn " ];
????????????????}
???????????????? return ?_ConnectionString;
????????????}
???????????? set
???????????? ... {
????????????????_ConnectionString? = ?value;
????????????}
????????}

???????? /**/ /// ? <summary>
???????? /// ?數(shù)據(jù)庫(kù)庫(kù)類型（默認(rèn)情況下為Oracle）
???????? /// ? </summary>
???????? public ?DataBaseType?DataSourceType
???????? ... {
???????????? get
???????????? ... {
???????????????? return ?_DataSourceType;
????????????}
???????????? set
???????????? ... {
????????????????_DataSourceType? = ?value;
????????????}
????????}

???????? public ?DBAccess()
???????? ... {}
???????? #endregion

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

???????? /**/ /// ? <summary>
???????? /// ?根據(jù)SQL語(yǔ)句創(chuàng)建DataSet數(shù)據(jù)集；
???????? /// ?可以執(zhí)行多條SELECT查詢語(yǔ)句，查詢語(yǔ)句之間用分號(hào)標(biāo)記，如下所示：
???????? /// ?SELECT?*?FROM?TABLE1;SELECT?*?FROM?TABLE2
???????? /// ? </summary>
???????? /// ? <param?name="sqlQuery"> SQL語(yǔ)句 </param>
???????? /// ? <returns> 返回DataSet數(shù)據(jù)集 </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>
???????? /// ?執(zhí)行SELECT查詢語(yǔ)句，并將結(jié)果以TABLE的形式加入到指定DataSet數(shù)據(jù)集；
???????? /// ?可以執(zhí)行多條SELECT查詢語(yǔ)句，查詢語(yǔ)句之間用分號(hào)標(biāo)記，如下所示：
???????? /// ?SELECT?*?FROM?TABLE1;SELECT?*?FROM?TABLE2
???????? /// ? </summary>
???????? /// ? <param?name="sqlQuery"> SQL語(yǔ)句 </param>
???????? /// ? <param?name="dsTarget"> 已存在的DataSet數(shù)據(jù)集 </param>
???????? /// ? <returns> 返回DataSet數(shù)據(jù)集 </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執(zhí)行操作 #region ?SQL執(zhí)行操作

???????? /**/ /// ? <summary>
???????? /// ?根據(jù)SQL語(yǔ)句執(zhí)行ExecuteNonQuery操作
???????? /// ? </summary>
???????? /// ? <param?name="sqlQuery"> SQL語(yǔ)句 </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>
???????? /// ?根據(jù)SQL語(yǔ)句創(chuàng)建DataReader
???????? /// ? </summary>
???????? /// ? <param?name="sqlQuery"> SQL語(yǔ)句 </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

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

舉例如下:

默認(rèn)情況下是訪問(wèn)Oracle數(shù)據(jù)庫(kù),數(shù)據(jù)庫(kù)連接字符串已經(jīng)在Config文件中定義,所以不需要再設(shè)置其ConnectionString和DataSourceType屬性,此時(shí)返回一個(gè)DataSet的代碼如下:

DBAccess?db? = ? new ?DBAccess();
// 同時(shí)執(zhí)行兩條查詢語(yǔ)句
string ?strSql? = ? " SELECT?*?FROM?TABLE1;SELECT?*?FROM?TABLE2 " ;
DataSet?ds? = ?db.GetDataSet(strSql);

但是如果在程序中需要臨時(shí)訪問(wèn)SqlServer數(shù)據(jù)庫(kù),則需要設(shè)置屬性,此時(shí)代碼如下:

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

(完)