关键字: 通用数据库类
在数据库应用程序的开发中,往往一个应用程序要从多个数据库中取得所要的业务数据。对于ADO.NET更是如此,一个DataSet,可能是从几个数据库中取得的数据集合。为此开发一个访问数据库通用接口显得更为方便、灵活。下面是一个针对.NET提供的四种访问数据库的通用类,每个类都有具体实现了一些常用访问数据库的方法。
DataProvider.cs 此类返回一个访问数据库的接口实例。
c# 代码
- #define DEBUG
- using System;
- namespace DataProviders
- {
- ///
- /// 提供对数据库访问的通用类。
- ///
- public class DataProvider
- {
- ///
- /// 数据库枚举类型
- ///
- public enum DataProviderType
- {
- OdbcDataProvider = 0,
- OleDbDataProvider = 1,
- OracleDataProvider = 2,
- SqlDataProvider = 3
- }
- ///
- /// 建立访问数据库的实例
- ///
- /// 数据库枚举类型
- ///
- public static IDataProvider CreateDataProvider(DataProviderType dataProviderType)
- {
- switch (dataProviderType)
- {
- case DataProviderType.OdbcDataProvider:
- return new OdbcDataProvider();
- case DataProviderType.OleDbDataProvider:
- return new OleDbDataProvider();
- case DataProviderType.OracleDataProvider:
- return new OracleDataProvider();
- case DataProviderType.SqlDataProvider:
- return new SqlDataProvider();
- default:
- #if DEBUG
- System.Diagnostics.Debug.WriteLine("dataProviderType 类型不存在!");
- #endif
- return null;
- }
- }
- }
- }
IDataProvider.cs 访问数据库的接口类
c# 代码
- using System;
- namespace DataProviders
- {
- ///
- /// 对数据库访问的通用接口
- ///
- public interface IDataProvider
- {
- ///
- /// 执行 UPDATE、INSERT 和 DELETE 语句,返回值为该命令所影响的行数
- ///
- int ExecuteNonQuery(string sql);
- ///
- /// 执行查询,并将查询返回的结果集中第一行的第一列作为 .NET Framework 数据类型返回。忽略额外的列或行
- ///
- object ExecuteScalar(string sql);
- ///
- /// 执行单Sql语句查询,并将查询返回的结果作为一个数据集返回
- ///
- System.Data.DataSet RetriveDataSet(string sql);
- ///
- /// 执行Sql数组语句查询,并将查询返回的结果作为一个数据集返回
- ///
- System.Data.DataSet RetriveDataSet(string[] sql, params string[] tableName);
- ///
- /// 更新库
- ///
- ///
- ///
- ///
- System.Data.DataSet UpdateDataSet(string sql, System.Data.DataSet hasChangesDataSet);
- ///
- /// 执行Dispose
- ///
- void Dispose();
- }
- }
OracleDataProvider.cs 访问Oracle的类
c# 代码
- #define DEBUG
- using System;
- using System.Data;
- using System.Data.OracleClient;
- namespace DataProviders
- {
- ///
- /// OracleDataProvider 的摘要说明。
- ///
- internal class OracleDataProvider : IDataProvider
- {
- private System.Data.OracleClient.OracleConnection oracleConnection;
- private System.Data.OracleClient.OracleCommand oracleCommand;
- private string connectionString;
- public OracleDataProvider() : this(null)
- {
- //
- // TODO: 在此处添加构造函数逻辑
- //
- }
- public OracleDataProvider(string connectionString)
- {
- if (connectionString == null || connectionString.Trim() == string.Empty)
- {
- System.Configuration.AppSettingsReader configurationAppSettings = new System.Configuration.AppSettingsReader();
- this.connectionString = (string)(configurationAppSettings.GetValue("oracleConnectionString", typeof(string)));
- }
- else
- {
- this.connectionString = connectionString;
- }
- }
- ///
- /// Oracle 连接字符串 "User Id=southfence;Data Source=FENCEORA;Password=southfence;Persist Security Info=true;"
- ///
- public string ConnectionString
- {
- get{
- return this.connectionString;
- }
- set{
- this.connectionString = value;
- }
- }
- ///
- /// 返回一个带有连接字符串的Oracle Connection.
- ///
- /// OracleConnection
- private OracleConnection GetOracleConnection()
- {
- try
- {
- return new OracleConnection(this.connectionString);
- }
- catch (Exception ex)
- {
- #if DEBUG
- System.Diagnostics.Debug.WriteLine(ex.ToString());
- #endif
- return null;
- }
- }
- ///
- /// 对于 UPDATE、INSERT 和 DELETE 语句,返回值为该命令所影响的行数。对于其他所有类型的语句,返回值为 -1
- ///
- /// UPDATE、INSERT 和 DELETE 语句
- public int ExecuteNonQuery(string sql)
- {
- using(oracleConnection = this.GetOracleConnection())
- {
- if (oracleConnection == null)
- return -1;
- int rv = -1;
- OracleTransaction oracleTransaction = null;
- try
- {
- if (oracleConnection.State == System.Data.ConnectionState.Closed)
- oracleConnection.Open();
- oracleCommand = new OracleCommand(sql, oracleConnection);
- oracleTransaction = oracleConnection.BeginTransaction();
- oracleCommand.Transaction = oracleTransaction;
- rv = oracleCommand.ExecuteNonQuery();
- oracleTransaction.Commit();
- }
- catch (Exception ex)
- {
- #if DEBUG
- System.Diagnostics.Debug.WriteLine(ex.ToString());
- #endif
- oracleTransaction.Rollback();
- rv = -1;
- }
- return rv;
- }
- }
- ///
- /// 执行查询,并将查询返回的结果集中第一行的第一列作为 .NET Framework 数据类型返回。忽略额外的列或行。
- ///
- /// SELECT 语句
- /// .NET Framework 数据类型形式的结果集第一行的第一列;如果结果集为空或结果为 REF CURSOR,则为空引用
- public object ExecuteScalar(string sql)
- {
- using(oracleConnection = this.GetOracleConnection())
- {
- if (oracleConnection == null)
- return null;
- try
- {
- if (oracleConnection.State == System.Data.ConnectionState.Closed)
- oracleConnection.Open();
- oracleCommand = new OracleCommand(sql, oracleConnection);
- return oracleCommand.ExecuteScalar();
- }
- catch (Exception ex)
- {
- #if DEBUG
- System.Diagnostics.Debug.WriteLine(ex.ToString());
- #endif
- return null;
- }
- }
- }
- ///
- /// 执行单Sql语句查询,并将查询返回的结果作为一个数据集返回
- ///
- /// SELECT 语句
- /// 数据集 DataSet
- public DataSet RetriveDataSet(string sql)
- {
- if (sql == null || sql == string.Empty)
- {
- #if DEBUG
- System.Diagnostics.Debug.WriteLine("sql 为空");
- #endif
- return null;
- }
- using(oracleConnection = this.GetOracleConnection())
- {
- if (oracleConnection == null)
- return null;
- using( OracleDataAdapter da = new OracleDataAdapter(sql, oracleConnection))
- {
- DataSet ds = new DataSet();
- try
- {
- da.Fill(ds);
- }
- catch (Exception ex)
- {
- #if DEBUG
- System.Diagnostics.Debug.WriteLine(ex.ToString());
- #endif
- }
- return ds;
- }
- }
- }
- ///
- /// 执行Sql数组语句查询,并将查询返回的结果作为一个数据集返回
- ///
- /// Select 语句数组
- /// TableName
- /// 数据集 DataSet
- public DataSet RetriveDataSet(string[] sql, params string[] tableName)
- {
- int sqlLength;
- sqlLength = sql.Length;
- if ( sqlLength == 0)
- {
- #if DEBUG
- System.Diagnostics.Debug.WriteLine("sql 为空");
- #endif
- return null;
- }
- using(oracleConnection = this.GetOracleConnection())
- {
- if (oracleConnection == null)
- return null;
- DataSet ds = new DataSet();
- int tableNameLength = tableName.Length;
- for (int i = 0; i < sqlLength; i++)
- {
- using(OracleDataAdapter da = new OracleDataAdapter(sql[i], oracleConnection))
- {
- try
- {
- if (i < tableNameLength)
- da.Fill(ds, tableName[i]);
- else
- da.Fill(ds, "table" + i);
- }
- catch (Exception ex)
- {
- #if DEBUG
- System.Diagnostics.Debug.WriteLine(ex.ToString());
- #endif
- return null;
- }
- }
- }
- return ds;
- }
- }
- ///
- /// 更新数据集.
- /// 过程:客户层(dataSet.GetChanges()) -- 修改 --> 数据服务层(hasChangesDataSet.update()) -- 更新--> 数据层(hasChangesDataSet) ...
- /// 数据层(hasChangesDataSet) -- 新数据 --> 数据服务层 (hasChangesDataSet) -- 合并 -- > 客户层(dataSet.Merge(hasChangesDataSet))
- ///
- ///
- ///
- public DataSet UpdateDataSet(string sql, DataSet hasChangesDataSet)
- {
- }
- ///
- /// 执行Sql数组语句查询,并将查询返回的结果作为一个数据读取器返回
- ///
- ///
- /// OracleDataReader
- public OracleDataReader RetriveDataReader(string sql)
- {
- if (sql == null || sql == string.Empty)
- {
- #if DEBUG
- System.Diagnostics.Debug.WriteLine("sql 为空");
- #endif
- return null;
- }
- using(oracleConnection = this.GetOracleConnection())
- {
- if (oracleConnection == null)
- return null;
- using(oracleCommand = new OracleCommand(sql, oracleConnection))
- {
- try
- {
- OracleDataReader oracleDataReader = oracleCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
- return oracleDataReader;
- }
- catch (Exception ex)
- {
- #if DEBUG
- System.Diagnostics.Debug.WriteLine(ex.ToString());
- #endif
- return null;
- }
- }
- }
- }
- public void Dispose()
- {
- this.connectionString = null;
- this.oracleCommand.Dispose();
- this.oracleConnection.Dispose();
- }
- }
- }
(未完待续。。。)