【转】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;

(完)

转载于:https://www.cnblogs.com/feima-lxl/archive/2008/07/09/1238768.html