本项目除用到"实时数据库"外, 还需要用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;
}
}
}
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
//其他功能,故意省略
}
}
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);
// 同时执行两条查询语句
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;
db.ConnectionString = " server=localhost;UID=sa;PWD=123456;DATABASE=Money;connect timeout=120 " ;
db.DataSourceType = DataBaseType.SQLServer;
(完)