数据库操作组件

         本组件目前只支持Oracle和SQL Server数据库的操作，对于其他数据库的支持将在以后续写扩充，目前可以说只是个Demo版。
         引入相应的namespace:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.OracleClient;
using System.Reflection;

          编写创建数据库对象的工厂类：

/**//// <summary>
    /// DBOperationFactory 的摘要说明。
    /// </summary>
    public abstract class DBOperationFactory
    {
        public static DBOperationFactory getInstance()
        {
            string factoryName = System.Configuration.ConfigurationSettings.AppSettings["DBOperationType"];
            DBOperationFactory instance;

            if(factoryName != "")
            {
                instance = (DBOperationFactory)Assembly.Load("ClassLibrary").CreateInstance("ClassLibrary."+factoryName);
            }                
            else
            {
                instance = null;
            }            
            return instance;
        }

        public abstract DBOperation Create();

    }

    public class OracleOperationFactory : DBOperationFactory
    {
        public override DBOperation Create()
        {
            return new OracleDBOperation();
        }
    }

    public class MSSQLOperationFactory : DBOperationFactory
    {

        public override DBOperation Create()
        {
            return new MSSQLDBOperation();
        }
    }

       接下来可以将OracleDBOperation和MSSQLDBOperation看作是我们将要创建的产品类：

public abstract class DBOperation
    {
        //Open DB Connection
        public abstract void OpenConnection();
        //Close DB Connection
        public abstract void CloseConnection();
        //return DataTable object
        public abstract DataTable GetDataTable(string strSql);
        //return DataView object
        public abstract DataView GetDataView(string strSql);
        //return DataSet object
        public abstract DataSet GetDataSet(string strSql);
        //present a transaction, Convert isTransaction to true 
        public abstract void BeginTransaction();
        //close a transaction, Convert isTransaction to true 
        public abstract void EndTransaction();
        //is transaction success?
        public abstract bool TransactionSucceed
        {
            get;
        }

        //Execute the SQL, and return the effective rows
        public abstract int ExecuteNonQuery(string sql);
        //Execute the stored procedure, and return the effective rows
        public abstract int ExcuteStoredProcedure(string StoredProcedureName, ref IDbDataParameter[] parameters);
        //return datareader object
        public abstract IDataReader GetDataReader(string sql);
        //execute the SQL as transaction
        public abstract int ExecuteTransactionNonQuery(string sql);
        //execute the stored procedure as transaction
        public abstract int ExcuteTransactionStoredProcedure(string StoredProcedureName, ref IDbDataParameter[] parameters);
    }

OracleDBOperation 具体实现类：

public class OracleDBOperation : DBOperation
    {
        public   OracleConnection    _Conn; 
        private  string              _ConnectionString;
        private  OracleTransaction   _Trans;
        private  OracleCommand       _Command;
        private  OracleCommand       _TransactionCommand;
        private  string              _TransactionMessage;
        private  bool                _TransactionSucceed;
        private  OracleDataAdapter   _Da;
        private  DataSet             _Ds;
        private  OracleDataReader    _DataReader;
        private  string              _SQL;
        private  int                 _RowEffected;
        private  bool                _IsTransaction=false;

        public OracleDBOperation()
        {
            _ConnectionString=System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
        }
    
        public OracleDBOperation(string strConn)
        {

            if(strConn==null || strConn.Trim()=="")
                _ConnectionString=System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
            else
                _ConnectionString=strConn;
        
        }

        public override void OpenConnection()
        {
            try
            {
                if(_Conn==null)
                {
                    _Conn=new OracleConnection(); 
                    _Conn.ConnectionString=_ConnectionString;
                }
                if(_Conn.State!=ConnectionState.Open)
                    _Conn.Open();
            }
            catch(Exception e)
            {            
                Console.Out.Write(e.Message);
            }
        }
    
        public override void CloseConnection()
        {
            if(_Conn != null)
            {                        
                if(_Conn.State==ConnectionState.Open)
                {
                    _Conn.Close();            
                }
            }
        }

        public override DataView GetDataView(string sql)
        {
            if(_IsTransaction==false)
            {
                DataView view;
                try
                {
                    _SQL=sql;
                    OpenConnection();
                    _Da=new OracleDataAdapter(sql ,_Conn);
                    _Ds=new DataSet();
                    _Da.Fill(_Ds ,"tmp_table");    
                    view=new DataView(_Ds.Tables["tmp_table"]);
                }            
                finally
                {
                    _Ds.Dispose();
                    _Da.Dispose();
                    CloseConnection();
                }
                return view;
            }
            else
            {
                _TransactionMessage="Once database transaction execute,the interface [GetDataView()] is not supported!";
                this.EndTransaction();
                return null;
            }
            
        }
      
        public override DataTable GetDataTable(string sql)
        {
            if(_IsTransaction==false)
            {
                DataTable table;
                try
                {
                    _SQL=sql;
                    OpenConnection();
                    _Da=new OracleDataAdapter(sql ,_Conn);
                    _Ds=new DataSet();
                    _Da.Fill(_Ds ,"tmp_table");    
                    table=_Ds.Tables["tmp_table"]; 
                }        
                finally
                {
                    _Ds.Dispose();
                    _Da.Dispose();
                    CloseConnection();
                }
                return table;
            }
            else
            {
                _TransactionMessage="Once database transaction execute,the interface [GetDataView()] is not supported!";
                this.EndTransaction();
                return null;
            }
        }

        public override void BeginTransaction()
        {
            _IsTransaction = true;
            _TransactionSucceed=false;
            OpenConnection();             
            if(_Trans==null)
                _Trans=_Conn.BeginTransaction(IsolationLevel.ReadCommitted);
            if(_TransactionCommand==null)
                _TransactionCommand=_Conn.CreateCommand();
            if(_TransactionCommand.Transaction==null)
                _TransactionCommand.Transaction=_Trans;        
        }

        public override void EndTransaction()
        {
            if(_IsTransaction==true)
            {
                _IsTransaction=false;
                if(_Trans!=null)
                {
                    if(_TransactionMessage==null)
                    {
                        _Trans.Commit();
                        _TransactionSucceed=true;
                    }
                    else
                    {
                        _Trans.Rollback();
                        _TransactionMessage=null;
                    }
                }
                if(_Trans!=null)
                    _Trans.Dispose();
                if(_TransactionCommand!=null)  
                    _TransactionCommand.Dispose();
                CloseConnection();
            }
        }
        
        public override bool TransactionSucceed
        {
            get          
            {
                return _TransactionSucceed;
            }
        }

        public override int ExecuteNonQuery(string sql)
        {
            if(_IsTransaction==false)
            {
                _RowEffected=0;
                _SQL=sql;
                try
                {
                    OpenConnection();                
                    _Command = new OracleCommand(_SQL,_Conn);
                    _RowEffected=_Command.ExecuteNonQuery();
                }        
                finally
                {                
                    _Command.Dispose();
                    CloseConnection();
                }
                return _RowEffected;
            }
            else
            {
                return this.ExecuteTransactionNonQuery(sql);
            }            
        }
    
        public override int ExcuteStoredProcedure(string StoredProcedureName, ref IDbDataParameter[] Oracle_parameters)
        {
            if(_IsTransaction==false)
            {
                try
                {                
                    OpenConnection();
                    _SQL = StoredProcedureName;
                    _RowEffected = 0;
                    _Command = new OracleCommand(_SQL,_Conn);
                    for(int i=0; i< Oracle_parameters.Length; i++)
                    {
                        _Command.Parameters.Add(Oracle_parameters[i]);
                    }
                    _Command.CommandType = CommandType.StoredProcedure;
                    _RowEffected = _Command.ExecuteNonQuery();    
                }            
                finally
                {
                    _Command.Dispose();
                    CloseConnection();
                }

                return _RowEffected;
            }
            else
            {
                return this.ExcuteTransactionStoredProcedure(StoredProcedureName,ref Oracle_parameters);
            }
                        
        }

        public override DataSet GetDataSet(string sql)
        {
            DataSet tmp_Ds;    
            try
            {
                OpenConnection();
                _Da=new OracleDataAdapter(sql ,_Conn);
                _Ds=new DataSet();
                _Da.Fill(_Ds ,"tmp_table");
                tmp_Ds=_Ds;
            }        
            finally
            {
                _Ds.Dispose();
                _Da.Dispose();
                CloseConnection();
            }
            return tmp_Ds; 
        }

        //当调用GetDataReader时,而且之后又没调用非事务性的method时,一定要显示调用CloseConnection();
        //否则连接释放不掉.
        public override IDataReader GetDataReader(string sql)
        {   
            if(_IsTransaction==false)
            {
                _DataReader=null;
                _SQL=sql;            
                try
                {
                    OpenConnection();
                    _Command = new OracleCommand(sql,_Conn);
                    _DataReader=_Command.ExecuteReader();
                }            
                finally
                {
                    _Command.Dispose();
                }
                return _DataReader;
            }
            else
            {
                _TransactionMessage="Once database transaction execute,the interface [GetDataReader()] is not supported!";
                this.EndTransaction();
                return null;
            }        
        }

        public override int ExecuteTransactionNonQuery(string sql)
        {
            _RowEffected=0;
            _SQL=sql;
            try
            {
                _TransactionCommand.CommandText=_SQL;
                _TransactionCommand.CommandType=CommandType.Text;
                _RowEffected=_TransactionCommand.ExecuteNonQuery();
            }            
            catch(Exception e)
            {
                _TransactionMessage=e.Message;            
            }
            return _RowEffected;    
        }


        public override int ExcuteTransactionStoredProcedure(string StoredProcedureName, ref IDbDataParameter[] Oracle_parameters)
        {
            try
            {                
                _SQL = StoredProcedureName;
                _RowEffected = 0;
                _TransactionCommand.CommandText=_SQL;
                for(int i=0; i< Oracle_parameters.Length; i++)
                {
                    _Command.Parameters.Add(Oracle_parameters[i]);
                }
                _TransactionCommand.CommandType = CommandType.StoredProcedure;
                _RowEffected = _TransactionCommand.ExecuteNonQuery();    
            }            
            catch(Exception e)
            {
                _TransactionMessage=e.Message;                
            }
            return _RowEffected;            
        }

    }

MSSQLDBOperation具体实现类：

public class MSSQLDBOperation : DBOperation
    {
        public   SqlConnection     _Conn; 
        private  string            _ConnectionString;
        private  SqlTransaction    _Trans;
        private  SqlCommand        _Command;
        private  SqlCommand        _TransactionCommand;
        private  string            _TransactionMessage;
        private  bool              _TransactionSucceed;
        private  SqlDataAdapter    _Da;
        private  DataSet           _Ds;
        private  SqlDataReader     _DataReader;
        private  string            _SQL;
        private  int               _RowEffected;
        private  bool              _IsTransaction=false;

        public MSSQLDBOperation()
        {
            _ConnectionString=System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
        }
    
        public MSSQLDBOperation(string strConn)
        {

            if(strConn==null || strConn.Trim()=="")
                _ConnectionString=System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
            else
                _ConnectionString=strConn;
        
        }

        public override void OpenConnection()
        {
            try
            {
                if(_Conn==null)
                {
                    _Conn=new SqlConnection(); 
                    _Conn.ConnectionString=_ConnectionString;
                }
                if(_Conn.State!=ConnectionState.Open)
                    _Conn.Open();
            }
            catch(Exception e)
            {            
                Console.Out.Write(e.Message);
            }
        }
    
        public override void CloseConnection()
        {
            if(_Conn != null)
            {                        
                if(_Conn.State==ConnectionState.Open)
                {
                    _Conn.Close();            
                }
            }
        }

        public override DataView GetDataView(string sql)
        {
            if(_IsTransaction==false)
            {
                DataView view;
                try
                {
                    _SQL=sql;
                    OpenConnection();
                    _Da=new SqlDataAdapter(sql ,_Conn);
                    _Ds=new DataSet();
                    _Da.Fill(_Ds ,"tmp_table");    
                    view=new DataView(_Ds.Tables["tmp_table"]);
                }        
                finally
                {
                    _Ds.Dispose();
                    _Da.Dispose();
                    CloseConnection();
                }
                return view;
            }
            else
            {
                _TransactionMessage="Once database transaction execute,the interface [GetDataView()] is not supported!";
                this.EndTransaction();
                return null;
            }
        }

        public override DataTable GetDataTable(string sql)
        {
            if(_IsTransaction==false)
            {
                DataTable table;
                try
                {
                    _SQL=sql;
                    OpenConnection();
                    _Da=new SqlDataAdapter(sql ,_Conn);
                    _Ds=new DataSet();
                    _Da.Fill(_Ds ,"tmp_table");    
                    table=_Ds.Tables["tmp_table"]; 
                }            
                finally
                {
                    _Ds.Dispose();
                    _Da.Dispose();
                    CloseConnection();
                }
                return table;
            }
            else
            {
                _TransactionMessage="Once database transaction execute,the interface [GetDataTable()] is not supported!";
                this.EndTransaction();
                return null;
            }
        }

        public override void BeginTransaction()
        {
            _IsTransaction = true;
            _TransactionSucceed=false;
            OpenConnection();             
            if(_Trans==null)
                _Trans=_Conn.BeginTransaction(IsolationLevel.ReadCommitted);
            if(_TransactionCommand==null)
                _TransactionCommand=_Conn.CreateCommand();
            if(_TransactionCommand.Transaction==null)
                _TransactionCommand.Transaction=_Trans;        
        }
 
        public override void EndTransaction()
        {
            if(_IsTransaction==true)
            {
                _IsTransaction=false;
                if(_Trans!=null)
                {
                    if(_TransactionMessage==null)
                    {
                        _Trans.Commit();
                        _TransactionSucceed=true;
                    }
                    else
                    {
                        _Trans.Rollback();
                        _TransactionMessage=null;
                    }
                }
                if(_Trans!=null)
                    _Trans.Dispose();
                if(_TransactionCommand!=null)  
                    _TransactionCommand.Dispose();
                CloseConnection();
            }
        }
        
        public override bool TransactionSucceed
        {
            get          
            {
                return _TransactionSucceed;
            }
        }

        public override int ExecuteNonQuery(string sql)
        {
            if(_IsTransaction==false)
            {
                _RowEffected=0;
                _SQL=sql;
                try
                {
                    OpenConnection();                
                    _Command = new SqlCommand(_SQL,_Conn);
                    _RowEffected=_Command.ExecuteNonQuery();
                }
                catch(Exception ex)
                {
                    throw(ex);
                }
                finally
                {                
                    _Command.Dispose();
                    CloseConnection();
                }
                return _RowEffected;
            }
            else
            {
                return this.ExecuteTransactionNonQuery(sql);
            }
        }
        public override int ExcuteStoredProcedure(string StoredProcedureName, ref IDbDataParameter[] Sql_parameters)
        {
            if(_IsTransaction==false)
            {
                try
                {                
                    OpenConnection();
                    _SQL = StoredProcedureName;
                    _RowEffected = 0;
                    _Command = new SqlCommand(_SQL,_Conn);
                    for(int i=0; i< Sql_parameters.Length; i++)
                    {
                        _Command.Parameters.Add(Sql_parameters[i]);
                    }
                    _Command.CommandType = CommandType.StoredProcedure;
                    _RowEffected = _Command.ExecuteNonQuery();    
                }            
                finally
                {
                    _Command.Dispose();
                    CloseConnection();
                }

                return _RowEffected;    
            }
            else
            {
                return this.ExcuteTransactionStoredProcedure(StoredProcedureName,ref Sql_parameters);
            }
        }

        public override DataSet GetDataSet(string sql)
        {
            if(_IsTransaction==false)
            {
                DataSet tmp_Ds;    
                try
                {
                    OpenConnection();
                    _Da=new SqlDataAdapter(sql ,_Conn);
                    _Ds=new DataSet();
                    _Da.Fill(_Ds ,"tmp_table");
                    tmp_Ds=_Ds;
                }            
                finally
                {
                    _Ds.Dispose();
                    _Da.Dispose();
                    CloseConnection();
                }
                return tmp_Ds; 
            }
            else
            {
                _TransactionMessage="Once database transaction execute,the interface [GetDataSet()] is not supported!";
                this.EndTransaction();
                return null;
            }
        }

        //当调用GetDataReader时,而且之后又没调用非事务性的method时,一定要显示调用CloseConnection();
        //否则连接释放不掉.
        public override IDataReader GetDataReader(string sql)
        {
            if(_IsTransaction==false)
            {
                _DataReader=null;
                _SQL=sql;  
                try
                {
                    OpenConnection();
                    _Command = new SqlCommand(sql,_Conn);
                    _DataReader=_Command.ExecuteReader();
                }            
                finally
                {
                    _Command.Dispose();
                }
                return _DataReader;
            }
            else
            {
                _TransactionMessage="Once database transaction execute,the interface [GetDataReader()] is not supported!";
                this.EndTransaction();
                return null;
            }
        }

        public override int ExecuteTransactionNonQuery(string sql)
        {
            _RowEffected=0;
            _SQL=sql;
            try
            {
                _TransactionCommand.CommandText=_SQL;
                _TransactionCommand.CommandType=CommandType.Text;
                _RowEffected=_TransactionCommand.ExecuteNonQuery();
            }            
            catch(Exception e)
            {
                _TransactionMessage=e.Message;
                throw(e);//DL20060522B.N
            }
            return _RowEffected;    
        }


        public override int ExcuteTransactionStoredProcedure(string StoredProcedureName, ref IDbDataParameter[] Sql_parameters)
        {
            try
            {                
                _SQL = StoredProcedureName;
                _RowEffected = 0;
                _TransactionCommand.CommandText=_SQL;
                for(int i=0; i< Sql_parameters.Length; i++)
                {
                    _Command.Parameters.Add(Sql_parameters[i]);
                }
                _TransactionCommand.CommandType = CommandType.StoredProcedure;
                _RowEffected = _TransactionCommand.ExecuteNonQuery();    
            }            
            catch(Exception e)
            {
                _TransactionMessage=e.Message;
            }
            return _RowEffected;            
        }
    }

最后在配置文件中添入：

<configuration>
  <appSettings>
      
     <add key="DBOperationType" value="MSSQLOperationFactory"/>
      
  </appSettings>
</configuration>