ADO.NET  

A Generic Data Access Component using Factory Pattern

I just read Mahesh's article Writing a Generic Data Access Component.

Another way to solve this problem is to utilize the System.Activator class and a factory pattern to create the concrete provider classes as was pointed-out in Dan Fox's article "Design an Effective Data-Access Architecture" (.netmagazine, vol. 2, no. 7).  I took this idea and refined it a bit so that the only steps necessary to add a new provider to the factory is to add a new enum and the associated type values to the factory's static type arrays (a total of 5 lines of code).

Here is the sample code. See the attached source code for a test application.

  1. using System;  
  2. using System.Reflection;  
  3. using System.Data;  
  4. using System.Data.OleDb;  
  5. using System.Data.SqlClient;  
  6. namespace CSharpCorner.ProviderFactory  
  7. {  
  8.     /// <summary>  
  9.     /// The collection of ADO.NET data providers that are supported by <see cref="ProviderFactory"/>.  
  10.     /// </summary>  
  11.     public enum ProviderType  
  12.     {  
  13.         /// <summary>  
  14.         /// The OLE DB (<see cref="System.Data.OleDb"/>) .NET data provider.  
  15.         /// </summary>  
  16.         OleDb = 0,  
  17.         /// <summary>  
  18.         /// The SQL Server (<see cref="System.Data.SqlClient"/>) .NET data provider.  
  19.         /// </summary>  
  20.         SqlClient  
  21.     };  
  22.     /// <summary>  
  23.     /// The <b>ProviderFactory</b> class abstracts ADO.NET relational data providers through creator methods which return  
  24.     /// the underlying <see cref="System.Data"/> interface.  
  25.     /// </summary>  
  26.     /// <remarks>  
  27.     /// This code was inspired by "Design an Effective Data-Access Architecture" by Dan Fox (.netmagazine, vol. 2, no. 7)  
  28.     /// </remarks>  
  29.   
  30.     public class ProviderFactory  
  31.     {  
  32.  
  33.         #region private variables  
  34.         private static Type[] _connectionTypes = new Type[] { typeof(OleDbConnection), typeof(SqlConnection) };  
  35.         private static Type[] _commandTypes = new Type[] { typeof(OleDbCommand), typeof(SqlCommand) };  
  36.         private static Type[] _dataAdapterTypes = new Type[] { typeof(OleDbDataAdapter), typeof(SqlDataAdapter) };  
  37.         private static Type[] _dataParameterTypes = new Type[] { typeof(OleDbParameter), typeof(SqlParameter) };  
  38.         private ProviderType _provider;  
  39.         #endregion  
  40.  
  41.         #region ctors  
  42.   
  43.         private ProviderFactory() { } // force user to specify provider  
  44.         public ProviderFactory(ProviderType provider)  
  45.         {  
  46.             _provider = provider;  
  47.         }  
  48.         #endregion  
  49.  
  50.         #region Provider property  
  51.         public ProviderType Provider  
  52.         {  
  53.             get  
  54.             {  
  55.                 return _provider;  
  56.             }  
  57.             set  
  58.             {  
  59.                 _provider = value;  
  60.             }  
  61.         }  
  62.         #endregion  
  63.  
  64.         #region IDbConnection methods  
  65.         public IDbConnection CreateConnection()  
  66.         {  
  67.             IDbConnection conn = null;  
  68.             try  
  69.             {  
  70.                 conn = (IDbConnection)Activator.CreateInstance(_connectionTypes[(int)_provider]);  
  71.             }  
  72.             catch (TargetInvocationException e)  
  73.             {  
  74.                 throw new SystemException(e.InnerException.Message, e.InnerException);  
  75.             }  
  76.             return conn;  
  77.         }  
  78.         public IDbConnection CreateConnection(string connectionString)  
  79.         {  
  80.             IDbConnection conn = null;  
  81.             object[] args = { connectionString };  
  82.             try  
  83.             {  
  84.                 conn = (IDbConnection)Activator.CreateInstance(_connectionTypes[(int)_provider], args);  
  85.             }  
  86.             catch (TargetInvocationException e)  
  87.             {  
  88.                 throw new SystemException(e.InnerException.Message, e.InnerException);  
  89.             }  
  90.             return conn;  
  91.         }  
  92.         #endregion  
  93.  
  94.         #region IDbCommand methods  
  95.         public IDbCommand CreateCommand()  
  96.         {  
  97.             IDbCommand cmd = null;  
  98.             try  
  99.             {  
  100.                 cmd = (IDbCommand)Activator.CreateInstance(_commandTypes[(int)_provider]);  
  101.             }  
  102.             catch (TargetInvocationException e)  
  103.             {  
  104.                 throw new SystemException(e.InnerException.Message, e.InnerException);  
  105.             }  
  106.             return cmd;  
  107.         }  
  108.         public IDbCommand CreateCommand(string cmdText)  
  109.         {  
  110.             IDbCommand cmd = null;  
  111.             object[] args = { cmdText };  
  112.             try  
  113.             {  
  114.                 cmd = (IDbCommand)Activator.CreateInstance(_commandTypes[(int)_provider], args);  
  115.             }  
  116.             catch (TargetInvocationException e)  
  117.             {  
  118.                 throw new SystemException(e.InnerException.Message, e.InnerException);  
  119.             }  
  120.             return cmd;  
  121.         }  
  122.         public IDbCommand CreateCommand(string cmdText, IDbConnection connection)  
  123.         {  
  124.             IDbCommand cmd = null;  
  125.             object[] args = { cmdText, connection };  
  126.             try  
  127.             {  
  128.                 cmd = (IDbCommand)Activator.CreateInstance(_commandTypes[(int)_provider], args);  
  129.             }  
  130.             catch (TargetInvocationException e)  
  131.             {  
  132.                 throw new SystemException(e.InnerException.Message, e.InnerException);  
  133.             }  
  134.             return cmd;  
  135.         }  
  136.         public IDbCommand CreateCommand(string cmdText, IDbConnection connection, IDbTransaction transaction)  
  137.         {  
  138.             IDbCommand cmd = null;  
  139.             object[] args = { cmdText, connection, transaction };  
  140.             try  
  141.             {  
  142.                 cmd = (IDbCommand)Activator.CreateInstance(_commandTypes[(int)_provider], args);  
  143.             }  
  144.             catch (TargetInvocationException e)  
  145.             {  
  146.                 throw new SystemException(e.InnerException.Message, e.InnerException);  
  147.             }  
  148.             return cmd;  
  149.         }  
  150.         #endregion  
  151.  
  152.         #region IDbDataAdapter methods  
  153.         public IDbDataAdapter CreateDataAdapter()  
  154.         {  
  155.             IDbDataAdapter da = null;  
  156.             try  
  157.             {  
  158.                 da = (IDbDataAdapter)Activator.CreateInstance(_dataAdapterTypes[(int)_provider]);  
  159.             }  
  160.             catch (TargetInvocationException e)  
  161.             {  
  162.                 throw new SystemException(e.InnerException.Message, e.InnerException);  
  163.             }  
  164.             return da;  
  165.         }  
  166.         public IDbDataAdapter CreateDataAdapter(IDbCommand selectCommand)  
  167.         {  
  168.             IDbDataAdapter da = null;  
  169.             object[] args = { selectCommand };  
  170.             try  
  171.             {  
  172.                 da = (IDbDataAdapter)Activator.CreateInstance(_dataAdapterTypes[(int)_provider], args);  
  173.             }  
  174.             catch (TargetInvocationException e)  
  175.             {  
  176.                 throw new SystemException(e.InnerException.Message, e.InnerException);  
  177.             }  
  178.             return da;  
  179.         }  
  180.         public IDbDataAdapter CreateDataAdapter(string selectCommandText, IDbConnection selectConnection)  
  181.         {  
  182.             IDbDataAdapter da = null;  
  183.             object[] args = { selectCommandText, selectConnection };  
  184.             try  
  185.             {  
  186.                 da = (IDbDataAdapter)Activator.CreateInstance(_dataAdapterTypes[(int)_provider], args);  
  187.             }  
  188.             catch (TargetInvocationException e)  
  189.             {  
  190.                 throw new SystemException(e.InnerException.Message, e.InnerException);  
  191.             }  
  192.             return da;  
  193.         }  
  194.         public IDbDataAdapter CreateDataAdapter(string selectCommandText, string selectConnectionString)  
  195.         {  
  196.             IDbDataAdapter da = null;  
  197.             object[] args = { selectCommandText, selectConnectionString };  
  198.             try  
  199.             {  
  200.                 da = (IDbDataAdapter)Activator.CreateInstance(_dataAdapterTypes[(int)_provider], args);  
  201.             }  
  202.             catch (TargetInvocationException e)  
  203.             {  
  204.                 throw new SystemException(e.InnerException.Message, e.InnerException);  
  205.             }  
  206.             return da;  
  207.         }  
  208.         #endregion  
  209.  
  210.         #region IDbDataParameter methods  
  211.         public IDbDataParameter CreateDataParameter()  
  212.         {  
  213.             IDbDataParameter param = null;  
  214.             try  
  215.             {  
  216.                 param = (IDbDataParameter)Activator.CreateInstance(_dataParameterTypes[(int)_provider]);  
  217.             }  
  218.             catch (TargetInvocationException e)  
  219.             {  
  220.                 throw new SystemException(e.InnerException.Message, e.InnerException);  
  221.             }  
  222.             return param;  
  223.         }  
  224.         public IDbDataParameter CreateDataParameter(string parameterName, object value)  
  225.         {  
  226.             IDbDataParameter param = null;  
  227.             object[] args = { parameterName, value };  
  228.             try  
  229.             {  
  230.                 param = (IDbDataParameter)Activator.CreateInstance(_dataParameterTypes[(int)_provider], args);  
  231.             }  
  232.             catch (TargetInvocationException e)  
  233.             {  
  234.                 throw new SystemException(e.InnerException.Message, e.InnerException);  
  235.             }  
  236.             return param;  
  237.         }  
  238.         public IDbDataParameter CreateDataParameter(string parameterName, DbType dataType)  
  239.         {  
  240.             IDbDataParameter param = CreateDataParameter();  
  241.             if (param != null)  
  242.             {  
  243.                 param.ParameterName = parameterName;  
  244.                 param.DbType = dataType;  
  245.             }  
  246.             return param;  
  247.         }  
  248.         public IDbDataParameter CreateDataParameter(string parameterName, DbType dataType, int size)  
  249.         {  
  250.             IDbDataParameter param = CreateDataParameter();  
  251.             if (param != null)  
  252.             {  
  253.                 param.ParameterName = parameterName;  
  254.                 param.DbType = dataType;  
  255.                 param.Size = size;  
  256.             }  
  257.             return param;  
  258.         }  
  259.         public IDbDataParameter CreateDataParameter(string parameterName, DbType dataType, int size, string sourceColumn)  
  260.         {  
  261.             IDbDataParameter param = CreateDataParameter();  
  262.             if (param != null)  
  263.             {  
  264.                 param.ParameterName = parameterName;  
  265.                 param.DbType = dataType;  
  266.                 param.Size = size;  
  267.                 param.SourceColumn = sourceColumn;  
  268.             }  
  269.             return param;  
  270.         }  
  271.         #endregion  
  272.     }  
  273. } 

Membership not found