Flexible ADO.Net

Class Diagram

Download on GitHub

 using System.Collections.Generic;  
 using System.Data;  
 namespace POC.Common.DAL  
 {  
   public interface IDbManager  
   {  
     DataProvider ProviderType { get; set; }  
     string ConnectionString { get; set; }  
     IDbConnection Connection { get; }  
     IDbTransaction Transaction { get; }  
     IDataReader DataReader { get; }  
     IDbCommand Command { get; }  
     List<IDbDataParameter> ParameterList { get; }  
     void Open();  
     void BeginTransaction();  
     void CommitTransaction();  
     void AddParameters(IDbDataParameter param);  
     IDataReader ExecuteReader(CommandType commandType, string commandText);  
     DataSet ExecuteDataSet(CommandType commandType, string commandText);  
     object ExecuteScalar(CommandType commandType, string commandText);  
     int ExecuteNonQuery(CommandType commandType, string commandText);  
     void CloseReader();  
     void Close();  
     void Dispose();  
   }  
 }  
 using System;  
 using System.Data;  
 using System.Data.Odbc;  
 using System.Data.OleDb;  
 using System.Data.SqlClient;  
 namespace POC.Common.DAL  
 {  
   public static class DbManagerFactory  
   {  
     public static IDbConnection GetConnection(DataProvider providerType)  
     {  
       IDbConnection iDbConnection;  
       switch (providerType)  
       {  
         case DataProvider.SqlServer:  
           iDbConnection = new SqlConnection();  
           break;  
         case DataProvider.OleDb:  
           iDbConnection = new OleDbConnection();  
           break;  
         case DataProvider.Odbc:  
           iDbConnection = new OdbcConnection();  
           break;  
         case DataProvider.Oracle:  
           throw new NotImplementedException();  
         default:  
           return null;  
       }  
       return iDbConnection;  
     }  
     public static IDbCommand GetCommand(DataProvider providerType)  
     {  
       switch (providerType)  
       {  
         case DataProvider.SqlServer:  
           return new SqlCommand();  
         case DataProvider.OleDb:  
           return new OleDbCommand();  
         case DataProvider.Odbc:  
           return new OdbcCommand();  
         case DataProvider.Oracle:  
           throw new NotImplementedException();  
         default:  
           return null;  
       }  
     }  
     public static IDbDataAdapter GetDataAdapter(DataProvider providerType)  
     {  
       switch (providerType)  
       {  
         case DataProvider.SqlServer:  
           return new SqlDataAdapter();  
         case DataProvider.OleDb:  
           return new OleDbDataAdapter();  
         case DataProvider.Odbc:  
           return new OdbcDataAdapter();  
         case DataProvider.Oracle:  
           throw new NotImplementedException();  
         default:  
           return null;  
       }  
     }  
     public static IDbTransaction GetTransaction(DataProvider providerType)  
     {  
       IDbConnection iDbConnection = GetConnection(providerType);  
       IDbTransaction iDbTransaction = iDbConnection.BeginTransaction();  
       return iDbTransaction;  
     }  
     public static IDataParameter GetParameter(DataProvider providerType)  
     {  
       IDataParameter iDataParameter = null;  
       switch (providerType)  
       {  
         case DataProvider.SqlServer:  
           iDataParameter = new SqlParameter();  
           break;  
         case DataProvider.OleDb:  
           iDataParameter = new OleDbParameter();  
           break;  
         case DataProvider.Odbc:  
           iDataParameter = new OdbcParameter();  
           break;  
         case DataProvider.Oracle:  
           throw new NotImplementedException();  
       }  
       return iDataParameter;  
     }  
   }  
 }  
 using System;  
 using System.Collections.Generic;  
 using System.Data;  
 namespace POC.Common.DAL  
 {  
   public sealed class DbManager : IDbManager, IDisposable  
   {  
     private IDbCommand idbCommand;  
     private DataProvider providerType;  
     private IDbTransaction idbTransaction;  
     public DbManager(DataProvider providerType)  
     {  
       this.providerType = providerType;  
     }  
     public DbManager(DataProvider providerType, string connectionString)  
     {  
       this.providerType = providerType;  
       ConnectionString = connectionString;  
     }  
     public IDbConnection Connection { get; private set; }  
     public IDataReader DataReader { get; set; }  
     public DataProvider ProviderType  
     {  
       get { return providerType; }  
       set { providerType = value; }  
     }  
     public string ConnectionString { get; set; }  
     public IDbCommand Command  
     {  
       get { return idbCommand; }  
     }  
     public IDbTransaction Transaction  
     {  
       get { return idbTransaction; }  
     }  
     public void Open()  
     {  
       Connection =  
         DbManagerFactory.GetConnection(providerType);  
       Connection.ConnectionString = ConnectionString;  
       if (Connection.State != ConnectionState.Open)  
         Connection.Open();  
       idbCommand = DbManagerFactory.GetCommand(ProviderType);  
     }  
     public void Close()  
     {  
       if (Connection.State != ConnectionState.Closed)  
         Connection.Close();  
     }  
     public void Dispose()  
     {  
       GC.SuppressFinalize(this);  
       Close();  
       idbCommand = null;  
       idbTransaction = null;  
       Connection = null;  
     }  
     public void AddParameters(IDbDataParameter param)  
     {  
       ParameterList.Add(param);  
     }  
     public void BeginTransaction()  
     {  
       if (idbTransaction == null)  
         idbTransaction =  
           DbManagerFactory.GetTransaction(ProviderType);  
       idbCommand.Transaction = idbTransaction;  
     }  
     public void CommitTransaction()  
     {  
       if (idbTransaction != null)  
         idbTransaction.Commit();  
       idbTransaction = null;  
     }  
     public IDataReader ExecuteReader(CommandType commandType, string commandText)  
     {  
       idbCommand = DbManagerFactory.GetCommand(ProviderType);  
       idbCommand.Connection = Connection;  
       PrepareCommand(idbCommand, Connection, Transaction,  
               commandType,  
               commandText, ParameterList);  
       DataReader = idbCommand.ExecuteReader();  
       idbCommand.Parameters.Clear();  
       return DataReader;  
     }  
     public void CloseReader()  
     {  
       if (DataReader != null)  
         DataReader.Close();  
     }  
     private static void AttachParameters(IDbCommand command, IEnumerable<IDbDataParameter> commandParameters)  
     {  
       foreach (var idbParameter in commandParameters)  
       {  
         if ((idbParameter.Direction == ParameterDirection.InputOutput)  
           &&  
           (idbParameter.Value == null))  
         {  
           idbParameter.Value = DBNull.Value;  
         }  
         command.Parameters.Add(idbParameter);  
       }  
     }  
     private static void PrepareCommand(IDbCommand command, IDbConnection        connection, IDbTransaction transaction, CommandType commandType, string commandText,  
  IEnumerable<IDbDataParameter> commandParameters)  
     {  
       command.Connection = connection;  
       command.CommandText = commandText;  
       command.CommandType = commandType;  
       if (transaction != null)  
       {  
         command.Transaction = transaction;  
       }  
       if (commandParameters != null)  
       {  
         AttachParameters(command, commandParameters);  
       }  
     }  
     public int ExecuteNonQuery(CommandType commandType, string commandText)  
     {  
       idbCommand = DbManagerFactory.GetCommand(ProviderType);  
       PrepareCommand(idbCommand, Connection, Transaction,  
               commandType, commandText, ParameterList);  
       var returnValue = idbCommand.ExecuteNonQuery();  
       idbCommand.Parameters.Clear();  
       return returnValue;  
     }  
     public object ExecuteScalar(CommandType commandType, string commandText)  
     {  
       idbCommand = DbManagerFactory.GetCommand(ProviderType);  
       PrepareCommand(idbCommand, Connection, Transaction,  
               commandType,  
               commandText,  ParameterList);  
       var returnValue = idbCommand.ExecuteScalar();  
       idbCommand.Parameters.Clear();  
       return returnValue;  
     }  
     public DataSet ExecuteDataSet(CommandType commandType, string commandText)  
     {  
       idbCommand = DbManagerFactory.GetCommand(ProviderType);  
       PrepareCommand(idbCommand, Connection, Transaction,  
               commandType,  
               commandText,  ParameterList);  
       var dataAdapter = DbManagerFactory.GetDataAdapter(ProviderType);  
       dataAdapter.SelectCommand = idbCommand;  
       var dataSet = new DataSet();  
       dataAdapter.Fill(dataSet);  
       idbCommand.Parameters.Clear();  
       return dataSet;  
     }  
     public List<IDbDataParameter> ParameterList  
     {  
       get { return parameterList ??   
 (parameterList = new List<IDbDataParameter>()); }  
     }  
     private List<IDbDataParameter> parameterList;  
   }  
 }  
 namespace POC.Common.DAL  
 {  
   public enum DataProvider  
   {  
     Oracle,  
     SqlServer,  
     OleDb,  
     Odbc  
   }  
 }