Oracle,SqlServer,Access數(shù)據(jù)庫通用訪問類設(shè)計(jì)
系統(tǒng)
1755 0
本文轉(zhuǎn)自:
http://blog.csdn.net/mydriverc/archive/2007/06/14/1652128.aspx
本項(xiàng)目除用到"實(shí)時(shí)數(shù)據(jù)庫"外,?還需要用Oracle數(shù)據(jù)庫存儲大量的配置信息和生成的數(shù)據(jù),而且對 Oracle的讀取相當(dāng)?shù)念l繁,在項(xiàng)目開始之處,數(shù)據(jù)訪問就是一個(gè)很令人煩惱的問題,僅僅數(shù)據(jù)訪問類就修改了好多版本,直到目前正在使用的這個(gè)版本.同時(shí)為了應(yīng)付開發(fā)過程中不時(shí)需要讀取SqlServer和Access數(shù)據(jù)庫,所以就寫成三種數(shù)據(jù)源的通用訪問類,雖然有點(diǎn)四不象,不過挺省事的,嘻嘻!
此模塊分為兩個(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??的摘要說明。
????
///
?
</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>
????
///
?由于可能會在多種數(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ù)庫庫類型(默認(rèn)情況下為Oracle)
????????
///
?
</summary>
????????
public
?DataBaseType?DataSourceType
????????
...
{
????????????
get
????????????
...
{
????????????????
return
?_DataSourceType;
????????????}
????????????
set
????????????
...
{
????????????????_DataSourceType?
=
?value;
????????????}
????????}
????????
public
?DBAccess()
????????
...
{}
????????
#endregion
????????
DataSet生成操作
#region
?DataSet生成操作
????????
/**/
///
?
<summary>
????????
///
?根據(jù)SQL語句創(chuàng)建DataSet數(shù)據(jù)集;
????????
///
?可以執(zhí)行多條SELECT查詢語句,查詢語句之間用分號標(biāo)記,如下所示:
????????
///
?SELECT?*?FROM?TABLE1;SELECT?*?FROM?TABLE2
????????
///
?
</summary>
????????
///
?
<param?name="sqlQuery">
SQL語句
</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查詢語句,并將結(jié)果以TABLE的形式加入到指定DataSet數(shù)據(jù)集;
????????
///
?可以執(zhí)行多條SELECT查詢語句,查詢語句之間用分號標(biāo)記,如下所示:
????????
///
?SELECT?*?FROM?TABLE1;SELECT?*?FROM?TABLE2
????????
///
?
</summary>
????????
///
?
<param?name="sqlQuery">
SQL語句
</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語句執(zhí)行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>
????????
///
?根據(jù)SQL語句創(chuàng)建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
????????
//
其他功能,故意省略
????}
}
舉例如下:
默認(rèn)情況下是訪問Oracle數(shù)據(jù)庫,數(shù)據(jù)庫連接字符串已經(jīng)在Config文件中定義,所以不需要再設(shè)置其ConnectionString和DataSourceType屬性,此時(shí)返回一個(gè)DataSet的代碼如下:
DBAccess?db?
=
?
new
?DBAccess();
//
同時(shí)執(zhí)行兩條查詢語句
string
?strSql?
=
?
"
SELECT?*?FROM?TABLE1;SELECT?*?FROM?TABLE2
"
;
DataSet?ds?
=
?db.GetDataSet(strSql);
但是如果在程序中需要臨時(shí)訪問SqlServer數(shù)據(jù)庫,則需要設(shè)置屬性,此時(shí)代碼如下:
DBAccess?db?
=
?
new
?DBAccess();
db.ConnectionString?
=
?
"
server=localhost;UID=sa;PWD=123456;DATABASE=Money;connect?timeout=120
"
;
db.DataSourceType?
=
?DataBaseType.SQLServer;
(完)
Oracle,SqlServer,Access數(shù)據(jù)庫通用訪問類設(shè)計(jì)
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索:z360901061
微信掃一掃加我為好友
QQ號聯(lián)系: 360901061
您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點(diǎn)擊下面給點(diǎn)支持吧,站長非常感激您!手機(jī)微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點(diǎn)擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元