【转】Oracle,SqlServer,Access数据库通用访问类设计

本文转自：http://blog.csdn.net/mydriverc/archive/2007/06/14/1652128.aspx

本项目除用到"实时数据库"外, 还需要用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;

(完)