Using Reflection to Create a Data Access Class
by Jon Wojtowicz

In most applications database access is restricted to a single database type. If you are connecting to SQL Server Microsoft has the Data Access Application Block to eliminate most of the mundane code of database access. If you are connecting to a database other then SQL Server you can use the same pattern as Microsoft.
This works well if you are connecting to a single type of data store. But what happens if you need to connect to multiple types of data store? Do you write a data access class for each? When faced with this dilemma working on a project I decided to use a single class and create the data access objects through reflection.


Data Access Class Internals

Since it is obvious that we need the basics of filling a DataSet, executing a query and executing a non-query I started with the basics of a data access class. This provided the basic items I needed. Starting with a concrete implementation also allowed me to test the actually data access steps and eliminate any bugs.
Since I had a data access class for SQL Server which provided the basics that was my starting point. This had the benefit of already having transaction support. An enumeration was added for the type of provider to use.
public enum ProviderType { Odbc = 0, OleDb, Oracle, SqlClient }
I also rewrote the methods to use the interfaces in the System.Data namespace. I changed all the SQLConnections to IDbConnection, SQLCommand to IDbCommand, SqlDataAdapter to IDbDataAdapter and SqlTransAction to IDbTransaction.
The basics were in place but how to create the object dynamically using reflection. It was decided to cache the types in static arrays. This was done for the connection, command and data adapter types. The ProviderType enumeration would act as the index into the type array for instantiation. It was then a matter of using the Activator.CreateInstance to create the various objects. Obviously the transaction did not need this pattern as it is created from the connection.
A factory was used to create the various types. The factory itself was marked as internal to encapsulate the design.
The code for the factory and for the data access class is as follows. There is a VB.Net version as well as a C# sharp version. The VB.Net version actually has more functionality as I was working n VB.Net projects when the code was created. Writing in C# was for practice.

C# DataAccessor

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data.Odbc;
using System.Data.OracleClient;

/*-----------------------------------------------------------------------------
Copyright �2001-2003 Jon Wojtowicz, All Rights Reserved.
 Some code may also contain other copyrights by the author.
-----------------------------------------------------------------------------
 Distribution: You can freely use this code in your own
               applications, but you may not reproduce 
               or publish this code on any web site,
               online service, or distribute as source 
               on any media without express permission.
-----------------------------------------------------------------------------

*/
namespace CSDataAccess
{
 /// <summary>
 /// The data provider types available
 /// </summary>
 public enum ProviderType
 {
  Odbc = 0,
  OleDb,
  Oracle,
  SqlClient
 };

 /// <summary>
 /// Summary description for DataAccessor.
 /// </summary>
 public class DataAccessor: IDisposable
 {
  private const String PARAMS_FAILED = "Could not attach parameters.";
  
  private static Type[] _connectionTypes = {typeof(OdbcConnection), typeof(OleDbConnection), 
             typeof(OracleConnection), typeof(SqlConnection)};
  private static Type[] _dataAdapterTypes = {typeof(OdbcDataAdapter), typeof(OleDbDataAdapter), 
              typeof(OracleDataAdapter), typeof(SqlDataAdapter)};
  private static Type[] _parameterTypes = {typeof(OdbcParameter), typeof(OleDbParameter), 
               typeof(OracleParameter), typeof(SqlParameter)};
        private static Type[] _commandTypes = {typeof(OdbcCommand), typeof(OleDbCommand), 
             typeof(OracleCommand), typeof(SqlCommand)};
        
  private IDbConnection _connection;   //connection used by the class
  private IDbTransaction _transaction;  //transaction used by the class
  private String _connectionString;   //connection string used for the connection
  private ProviderType _provider;   //provider type to use for the data objects
  private int _commandTimeout = 120;   // timeout interval for the command execution


  /// <summary>
  /// constructor for the class
  /// </summary>
  /// <param name="provider">provider type for the class</param>
  public DataAccessor(ProviderType provider)
  {
   _provider = provider;
  } // New

  /// <summary>
  /// constructor for the class
  /// </summary>
  /// <param name="connectionString">connection string for the connection</param>
  /// <param name="provider">provider type for the class</param>
  public DataAccessor(String connectionString, ProviderType provider)
  {
   _provider = provider;
   _connectionString = connectionString;
  } // New

  /// <summary>
  /// gets the connection string from the configuration file based on the key 
  /// string passed in
  /// </summary>
  /// <param name="provider">provider type for the class</param>
  /// <param name="key">name of the app settings key to use for the connection 
  /// string</param>
  public DataAccessor(ProviderType provider, String key)
  {
   //get the connection string from the configuration file
   System.Configuration.AppSettingsReader appRead = new System.Configuration.AppSettingsReader();
   _connectionString = (String)appRead.GetValue(key, typeof(System.String));
   _provider = provider;
  } // New

  /// <summary>
  /// Implementation for the IDisposable Interface 
  /// Release unmanaged resources
  /// </summary>
  public void Dispose()
  {
   // dispose of the transaction if it exists
   if( _transaction  != null)
    _transaction.Dispose();

   // dispose of the connection
   if( _connection != null)
   {
    if(_connection.State == ConnectionState.Open)
     _connection.Close();
    _connection.Dispose();
   }
  } // Dispose
  
  /// <summary>
  /// Gets/sets the provider type to use for the data access. This value is an 
  /// enumerated list
  /// </summary>
  virtual public ProviderType Provider
  {
   get
   {
    return _provider;
   }
   set
   {
    _provider = value;
   }
  } // Provider

  /// <summary>
  /// Gets/sets the connection string to use for the connection
  /// </summary>
  virtual public String ConnectionString
  {
   get
   {
    return _connectionString;
   }
   set
   {
    _connectionString = value;
   }
  } // ConnectionString

  /// <summary>
  /// Gets/sets the timeout for the command
  /// </summary>
  virtual public int CommandTimeout
  {
   get
   {
    return _commandTimeout;
   }
   set
   {
    _commandTimeout = value;
   }
  } // CommandTimeout

   /// <summary>
   /// Gets/Sets the connection to use for data access
   /// </summary>
   virtual public IDbConnection Connection
  {
   get
   {
    // if the connection does not exist, create and open it
    if(_connection == null)
    {
     _connection = DataProviderFactory.CreateConnection(_connectionTypes[(int)_provider], _connectionString);
     _connection.Open();
    }
    else
     if( _connection.State != ConnectionState.Open)
     _connection.Open();
    return _connection;
   }
   set
   {
    _connection = value;
   }
  } // Connection

  /// <summary>
  /// Gets/sets the transaction to use for the operations
  /// </summary>
  virtual public IDbTransaction Transaction
  {
   get
   {
    return _transaction;
   }
   set
   {
    _transaction = value;
   }
  } // Transaction

  /// <summary>
  /// Starts a transaction on the connection. This is a separate function from the
  ///  property accessor due to the automatic initialization of the transaction 
  ///  if this code is placed in the property.
  /// </summary>
  /// <returns>the transaction</returns>
  public IDbTransaction BeginTransaction()
  {
   if(_transaction == null)
    _transaction = Connection.BeginTransaction();
   return _transaction;
  } // BeginTransaction

  /// <summary>
  /// Helper function encapsulating the routine code for the ExecuteNonQuery ADO 
  /// function.
  /// </summary>
  /// <param name="query">the query string</param>
  /// <param name="queryType">the type of query</param>
  /// <param name="param">parameter array for the query</param>
  /// <returns>error code from the call</returns>
  public int ExecuteNonQuery(String query, CommandType queryType, 
   IDbDataParameter[] param)
  {
   IDbCommand cmd = Connection.CreateCommand();
   cmd.CommandText = query;
   cmd.CommandType = queryType;
   cmd.CommandTimeout = _commandTimeout;
   AppendParameters(param, cmd);

   cmd.Transaction = _transaction;

   int retVal = cmd.ExecuteNonQuery();

   if( _transaction == null)
    _connection.Close();

   cmd.Dispose();

   return retVal;

  } // 'ExecuteNonQuery

  /// <summary>
  /// Helper function encapsulating the routine code or the ExecuteQuery ADO function.
  /// </summary>
  /// <param name="query">the query string</param>
  /// <param name="queryType">the type of query</param>
  /// <param name="param">parameter array for the query</param>
  /// <param name="ds">the dataset to fill</param>
  /// <returns>error code from the call</returns>
  public int ExecuteQuery(String query, CommandType queryType, 
   IDbDataParameter[] param, ref DataSet ds) 
  {
   IDbCommand cmd = Connection.CreateCommand();
   cmd.CommandText = query;
   cmd.CommandType = queryType;
   cmd.CommandTimeout = _commandTimeout;

   AppendParameters(param, cmd);
   IDbDataAdapter da = DataProviderFactory.CreateDataAdapter(_dataAdapterTypes[(int)_provider]);

   da.SelectCommand = cmd;
   cmd.Transaction = _transaction;
   da.Fill(ds);
   if( _transaction == null)
    _connection.Close();
            
   Object retVal = ((IDbDataParameter)cmd.Parameters[0]).Value;
   cmd.Dispose();
   return int.Parse(retVal.ToString());
  } // ExecuteQuery

  /// <summary>
  /// Helper function encapsulating the routine code for the ExecuteScalar ADO function.
  /// </summary>
  /// <param name="query">the query string</param>
  /// <param name="queryType">the type of query</param>
  /// <param name="param">parameter array for the query</param>
  /// <returns>object reulting from the call</returns>
  public Object ExecuteScalar(String query, CommandType queryType, 
   IDbDataParameter[] param)
  {
   IDbCommand cmd  = Connection.CreateCommand();
   cmd.CommandText = query;
   cmd.CommandType = queryType;
   cmd.CommandTimeout = _commandTimeout;

   AppendParameters(param, cmd);

   cmd.Transaction = _transaction;

   Object retVal = cmd.ExecuteScalar();

   if(_transaction == null)
    _connection.Close();

   cmd.Dispose();
   return retVal;
  } // ExecuteScalar

  /// <summary>
  /// Helper function encapsulating the routine code for the GetReader ADO function.
  /// </summary>
  /// <param name="query">the query string</param>
  /// <param name="queryType">the type of query</param>
  /// <param name="param">parameter array for the query</param>
  /// <returns>the opened reader</returns>
  public IDataReader GetReader(String query, CommandType queryType, 
   IDbDataParameter[] param)
  {
   IDbCommand cmd = Connection.CreateCommand();
   cmd.CommandText = query;
   cmd.CommandType = queryType;
   cmd.CommandTimeout = _commandTimeout;

   AppendParameters(param, cmd);

   IDataReader dr = cmd.ExecuteReader();
 
   cmd.Dispose();

   return dr;
  } // GetReader

  /// <summary>
  /// Helper function for adding parameters to a command
  /// </summary>
  /// <param name="param"><array of parameters/param>
  /// <param name="cmd">Command to append the parameters to</param>
  private void AppendParameters(IDbDataParameter[] param, IDbCommand cmd)
  {
   // add the parameters to the command
   try
   {
    if (param != null)
    {
     foreach (IDbDataParameter parameter in param)
     {
      cmd.Parameters.Add(parameter);
     }
    }
   }
   catch (Exception e)
   {
    throw new Exception(PARAMS_FAILED, e);
   }
            
  } // AppendParameters
 }
}

VB.Net DataAccessor

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports System.Data.Odbc
Imports System.Data.OracleClient

'-----------------------------------------------------------------------------
'Copyright �2001-2003 Jon Wojtowicz, All Rights Reserved.
' Some code may also contain other copyrights by the author.
'-----------------------------------------------------------------------------
' Distribution: You can freely use this code in your own
'               applications, but you may not reproduce 
'               or publish this code on any web site,
'               online service, or distribute as source 
'               on any media without express permission.
'-----------------------------------------------------------------------------

'''-----------------------------------------------------------------------------
''' Project  : DataAccess
''' Enum  : ProviderType
'''
'''-----------------------------------------------------------------------------
''' <summary>
''' The data provider types available
''' </summary>
''' <remarks></remarks>
''' <history>
'''  J. Wojtowicz  7/2002 Created
''' </history>
'''-----------------------------------------------------------------------------
Public Enum ProviderType
    Odbc = 0
    OleDb
    Oracle
    SqlClient
End Enum

'''-----------------------------------------------------------------------------
''' Project  : DataAccess
''' Class  : DataAccessor
'''
'''-----------------------------------------------------------------------------
''' <summary>
''' Helper for data access
''' Wraps the database access calls
''' </summary>
''' <remarks>this class requires a reference to System.Data.OracleClient.dll
''' This class is dependent on the DataProviderFactory
''' </remarks>
''' <history>
'''  1.0     1/2002  J. Wojtowicz    Original 
'''     1.1     3/2002  J. Wojtowicz    Added support for using objects
'''                                     from the DBHelper class to merge
'''                                     the two code bases
'''     2.0     7/2002  J. Wojtowicz    Added use of a factory pattern for the
'''                                     data access, removed DBHelper code and
'''                                     Sql Server specific methods. Removed 
'''                                     constructor that assumed string parameter
'''                                     was configuration key
'''     2.1     10/2002 J. Wojtowicz    Added static functions to create arrays
'''                                     of container objects from a DataReader.
'''                                     Added function to get the parameter array 
'''                                     from the command object.
'''                                     Added private CopyParameters and 
'''                                     CreateCommand functions
''' </history>
'''-----------------------------------------------------------------------------
Public Class DataAccessor
    Implements IDisposable

#Region "Private Fields"


    Private Const PARAMS_FAILED As String = "Could not attach parameters."
    Private Const COMMAND_TIMEOUT As Int32 = 120

    Private Shared m_connectionTypes() As Type =
{GetType(OdbcConnection), GetType(OleDbConnection), GetType(OracleConnection), GetType(SqlConnection)} Private Shared m_dataAdapterTypes() As Type = {GetType(OdbcDataAdapter), GetType(OleDbDataAdapter), GetType(OracleDataAdapter), GetType(SqlDataAdapter)} Private Shared m_parameterTypes() As Type = {GetType(OdbcParameter), GetType(OleDbParameter), GetType(OracleParameter), GetType(SqlParameter)} Private Shared m_commandTypes() As Type = {GetType(OdbcCommand), GetType(OleDbCommand), GetType(OracleCommand), GetType(SqlCommand)} Private Shared m_commandBuilderTypes() As Type = {GetType(OdbcCommandBuilder), GetType(OleDbCommandBuilder), GetType(OracleCommandBuilder), GetType(SqlCommandBuilder)} Private m_connection As IDbConnection ' connection used by the class Private m_transaction As IDbTransaction ' transaction used by the class Private m_connectionString As String ' connection string used for the connection Private m_provider As ProviderType ' provider type to use for the data objects Private m_commandTimeout As Integer = COMMAND_TIMEOUT ' Timeout for the command execution #End Region #Region "Constructors" '''----------------------------------------------------------------------------- ''' <summary> ''' default constructor for the class ''' </summary> ''' <remarks></remarks> ''' <history> ''' J. Wojtowicz 7/2002 Created ''' </history> '''----------------------------------------------------------------------------- Public Sub New(ByVal provider As ProviderType) m_provider = provider End Sub 'New '''----------------------------------------------------------------------------- ''' <summary> ''' default constructor for the class ''' </summary> ''' <remarks></remarks> ''' <history> ''' J. Wojtowicz 7/2002 Created ''' </history> '''----------------------------------------------------------------------------- Public Sub New(ByVal connectionString As String, ByVal provider As ProviderType) m_provider = provider m_connectionString = connectionString End Sub 'New '''----------------------------------------------------------------------------- ''' <summary> ''' gets the connection string from the configuration file based on the key ''' string passed in ''' </summary> ''' <param name="key">name of the app settings key to use for the connection ''' string</param> ''' <remarks></remarks> ''' <history> ''' J. Wojtowicz 7/2002 Created ''' </history> '''----------------------------------------------------------------------------- Public Sub New(ByVal provider As ProviderType, ByVal key As String) 'get the connection string from the configuration file Dim appRead As New System.Configuration.AppSettingsReader m_connectionString = appRead.GetValue(key, GetType(System.String)) m_provider = provider End Sub 'New #End Region #Region "Properties" '''----------------------------------------------------------------------------- ''' <summary> ''' Gets/sets the provider type to use for the data access. This value is an ''' enumerated list ''' </summary> ''' <value></value> ''' <remarks></remarks> ''' <history> ''' J. Wojtowicz 7/2002 Created ''' </history> '''----------------------------------------------------------------------------- Public Property Provider() As ProviderType Get Return m_provider End Get Set(ByVal Value As ProviderType) m_provider = Value End Set End Property 'Provider '''----------------------------------------------------------------------------- ''' <summary> ''' Gets/sets the connection string to use for the connection ''' </summary> ''' <value></value> ''' <remarks></remarks> ''' <history> ''' J. Wojtowicz 7/2002 Created ''' </history> '''----------------------------------------------------------------------------- Public Property ConnectionString() As String Get Return m_connectionString End Get Set(ByVal Value As String) m_connectionString = Value End Set End Property 'ConnectionString '''----------------------------------------------------------------------------- ''' <summary> ''' Gets/Sets the connection to use for data access ''' </summary> ''' <value></value> ''' <remarks></remarks> ''' <history> ''' J. Wojtowicz 1/2002 Created ''' J. Wojtowicz 7/2002 Modified to use the IDbConnection interface ''' </history> '''----------------------------------------------------------------------------- Public Property Connection() As IDbConnection Get ' if the connection does not exist, create and open it If m_connection Is Nothing Then m_connection = DataProviderFactory.CreateConnection(m_connectionTypes(m_provider), m_connectionString) m_connection.Open() Else If Not m_connection.State = ConnectionState.Open Then m_connection.Open() End If Return m_connection End Get Set(ByVal Value As IDbConnection) m_connection = Value End Set End Property 'Connection '''----------------------------------------------------------------------------- ''' <summary> ''' Gets/sets the transaction to use for the operations ''' </summary> ''' <value></value> ''' <remarks></remarks> ''' <history> ''' J. Wojtowicz 1/2002 Created ''' J. Wojtowicz 7/2002 Modified to use the IDbTransaction interface ''' </history> '''----------------------------------------------------------------------------- Public Property Transaction() As IDbTransaction Get Return m_transaction End Get Set(ByVal Value As IDbTransaction) m_transaction = Value End Set End Property 'Transaction '''----------------------------------------------------------------------------- ''' <summary> ''' Gets/Sets the timeout for the command ''' </summary> ''' <value></value> ''' <remarks></remarks> ''' <history> ''' [CodeMeister] 10/14/2002 Created ''' </history> '''----------------------------------------------------------------------------- Public Property CommandTimeout() As Int32 Get Return m_commandTimeout End Get Set(ByVal Value As Int32) m_commandTimeout = Value End Set End Property ' CommandTimeout #End Region #Region "Public Methods" '''----------------------------------------------------------------------------- ''' <summary> ''' Implementation for the IDisposable Interface ''' Release unmanaged resources ''' </summary> ''' <remarks></remarks> ''' <history> ''' J. Wojtowicz 1/2002 Created ''' </history> '''----------------------------------------------------------------------------- Public Sub Dispose() Implements IDisposable.Dispose ' dispose of the transaction if it exists If Not m_transaction Is Nothing Then m_transaction.Dispose() ' dispose of the connection If Not m_connection Is Nothing Then If m_connection.State = ConnectionState.Open Then m_connection.Close() End If m_connection.Dispose() End If End Sub 'Dispose '''----------------------------------------------------------------------------- ''' <summary> ''' Starts a transaction on the connection. ''' This is a separate function from the property accessor ''' due to the automatic initialization of the transaction ''' if this code is placed in the property. ''' </summary> ''' <returns></returns> ''' <remarks></remarks> ''' <history> ''' J. Wojtowicz 1/2002 Created ''' J. Wojtowicz 7/2002 Modified to use the IDbTransaction interface ''' </history> '''----------------------------------------------------------------------------- Public Function BeginTransaction() As IDbTransaction If m_transaction Is Nothing Then m_transaction = Connection.BeginTransaction() Return m_transaction End Function 'BeginTransaction '''----------------------------------------------------------------------------- ''' <summary> ''' Helper function encapsulating the routine code ''' for the ExecuteNonQuery ADO function. ''' </summary> ''' <param name="query">name of the stored procedure</param> ''' <param name="param">parameters for the stored procedure</param> ''' <returns>value returned from the stored procedure</returns> ''' <remarks></remarks> ''' <history> ''' J. Wojtowicz 1/2002 Created ''' J. Wojtowicz 7/2002 Modified to use the interfaces ''' </history> '''----------------------------------------------------------------------------- Public Function ExecuteNonQuery(ByVal query As String, ByVal queryType As CommandType, _ ByVal param As IDbDataParameter()) As Int32 Dim cmd As IDbCommand = CreateCommand(query, queryType) AppendParameters(param, cmd) cmd.Transaction = m_transaction Dim retVal As Int32 = cmd.ExecuteNonQuery() If m_transaction Is Nothing Then m_connection.Close() End If cmd.Dispose() Return retVal End Function 'ExecuteNonQuery '''----------------------------------------------------------------------------- ''' <summary> ''' Helper function encapsulating the routine code ''' for the ExecuteQuery ADO function. ''' </summary> ''' <param name="query">name of the stored procedure</param> ''' <param name="param">parameters for the stored procedure</param> ''' <param name="ds">dataset to populate with the returned data</param> ''' <returns>value returned from the stored procedure</returns> ''' <remarks></remarks> ''' <history> ''' J. Wojtowicz 1/2002 Created ''' J. Wojtowicz 7/2002 Modified to use the interfaces ''' </history> '''----------------------------------------------------------------------------- Public Function ExecuteQuery(ByVal query As String, ByVal queryType As CommandType, _ ByVal param As IDbDataParameter(), ByRef ds As DataSet) As Int32 Dim cmd As IDbCommand = CreateCommand(query, queryType) AppendParameters(param, cmd) Dim da As IDbDataAdapter = DataProviderFactory.CreateDataAdapter(m_dataAdapterTypes(m_provider)) da.SelectCommand = cmd cmd.Transaction = m_transaction Dim retVal As Int32 = da.Fill(ds) If m_transaction Is Nothing Then m_connection.Close() End If cmd.Dispose() Return retVal End Function 'ExecuteQuery '''----------------------------------------------------------------------------- ''' <summary> ''' Helper function encapsulating the routine code ''' for the ExecuteScalar ADO function. ''' </summary> ''' <param name="query">name of the stored procedure</param> ''' <param name="param">parameters for the stored procedure</param> ''' <returns>the return value from the call</returns> ''' <remarks></remarks> ''' <history> ''' J. Wojtowicz 1/2002 Created ''' J. Wojtowicz 7/2002 Modified to use the interfaces ''' </history> '''----------------------------------------------------------------------------- Public Function ExecuteScalar(ByVal query As String, ByVal queryType As CommandType, _ ByVal param As IDbDataParameter()) As Object Dim cmd As IDbCommand = CreateCommand(query, queryType) AppendParameters(param, cmd) cmd.Transaction = m_transaction Dim retVal As Object = cmd.ExecuteScalar() If m_transaction Is Nothing Then m_connection.Close() End If cmd.Dispose() Return retVal End Function 'ExecuteScalar '''----------------------------------------------------------------------------- ''' <summary> ''' Helper function encapsulating the routine code ''' for the GetReader ADO function. ''' </summary> ''' <param name="query">name of the stored procedure</param> ''' <param name="param">parameters for the stored procedure</param> ''' <returns>SqlDataReader</returns> ''' <remarks></remarks> ''' <history> ''' J. Wojtowicz 1/2002 Created ''' J. Wojtowicz 7/2002 Modified to use the interfaces ''' </history> '''----------------------------------------------------------------------------- Public Function GetReader(ByVal query As String, ByVal queryType As CommandType, _ ByVal param As IDbDataParameter()) As IDataReader Dim cmd As IDbCommand = CreateCommand(query, queryType) AppendParameters(param, cmd) Dim dr As IDataReader dr = cmd.ExecuteReader() cmd.Dispose() Return dr End Function 'GetReader '''----------------------------------------------------------------------------- ''' <summary> ''' This functions queries for the parameters from the command and returns an ''' array of IDbDataParameters. ''' This function uses reflection on the CommandBuilder type since the command ''' builders for the various data access types do not inherit from a single ''' standard interface althought the functionality of each type of command ''' builder is very similar. This has to be an oversight in the creation of the ''' CommandBuilder classes. ''' </summary> ''' <param name="query">the query string to use for retrieving the parameters</param> ''' <param name="queryType">the type of query</param> ''' <returns>an array of parameters for the command</returns> ''' <remarks></remarks> ''' <history> ''' [CodeMeister] 10/20/2002 Created ''' </history> '''----------------------------------------------------------------------------- Public Function GetParameters(ByVal query As String, ByVal queryType As CommandType) As IDbDataParameter() Dim cmd As IDbCommand = CreateCommand(query, queryType) m_commandBuilderTypes(m_provider).GetMethod("DeriveParameters").Invoke(Nothing, New Object() {cmd}) Dim parameters(cmd.Parameters.Count - 1) As IDbDataParameter cmd.Parameters.CopyTo(parameters, 0) Return CloneParameters(parameters) End Function #End Region #Region "Static Methods" '''----------------------------------------------------------------------------- ''' <summary> ''' Create container objects of the type specified and loads with data from ''' a dataReader by mapping the property name to the dataReader's field name. ''' This must be an exact match or it will fail ''' </summary> ''' <param name="objectType">the type of objects to create</param> ''' <param name="reader">the dataReader to use as a data source</param> ''' <returns>and arraylist of the objects from the reader</returns> ''' <remarks></remarks> ''' <history> ''' [CodeMeister] 10/14/2002 Created ''' </history> '''----------------------------------------------------------------------------- Public Shared Function LoadObjectsByPropertyName(ByVal objectType As Type, ByVal reader As IDataReader) As ArrayList Dim props As System.Reflection.PropertyInfo() = objectType.GetProperties(Reflection.BindingFlags.Instance Or Reflection.BindingFlags.Public Or Reflection.BindingFlags.SetProperty) Dim returnList As New ArrayList While reader.Read() Dim instance As Object = Activator.CreateInstance(objectType) For count As Int32 = 0 To props.Length() - 1 props(count).SetValue(instance, reader.Item(props(count).Name), Nothing) Next returnList.Add(instance) End While Return returnList End Function 'LoadObjectsByPropertyName '''----------------------------------------------------------------------------- ''' <summary> ''' Create container objects of the type specified and loads with data from ''' a dataReader by mapping the property field posistion dataReader's field ''' position. ''' </summary> ''' <param name="objectType">the type of objects to create</param> ''' <param name="reader">the dataReader to use as a data source</param> ''' <returns>and arraylist of the objects from the reader</returns> ''' <remarks></remarks> ''' <history> ''' [CodeMeister] 10/14/2002 Created ''' </history> '''----------------------------------------------------------------------------- Public Shared Function LoadObjectsByPropertyOrdinal(ByVal objectType As Type, ByVal reader As IDataReader) As ArrayList Dim props As System.Reflection.PropertyInfo() = objectType.GetProperties(Reflection.BindingFlags.Instance Or Reflection.BindingFlags.Public Or Reflection.BindingFlags.SetProperty) Dim returnList As New ArrayList While reader.Read() Dim instance As Object = Activator.CreateInstance(objectType) For count As Int32 = 0 To props.Length - 1 props(count).SetValue(instance, reader.Item(count), Nothing) Next returnList.Add(instance) End While Return returnList End Function 'LoadObjectsByPropertyOrdinal '''----------------------------------------------------------------------------- ''' <summary> ''' Create container objects of the type specified and loads with data from ''' a dataReader by mapping the field name to the dataReader's field name. ''' This must be an exact match or it will fail ''' </summary> ''' <param name="objectType">the type of objects to create</param> ''' <param name="reader">the dataReader to use as a data source</param> ''' <returns>and arraylist of the objects from the reader</returns> ''' <remarks></remarks> ''' <history> ''' [CodeMeister] 10/17/2002 Created ''' </history> '''----------------------------------------------------------------------------- Public Shared Function LoadObjectsByFieldName(ByVal objectType As Type, ByVal reader As IDataReader) As ArrayList Dim fields As System.Reflection.FieldInfo() = objectType.GetFields(Reflection.BindingFlags.Instance Or Reflection.BindingFlags.Public) Dim returnList As New ArrayList While reader.Read() Dim instance As Object = Activator.CreateInstance(objectType) For count As Int32 = 0 To fields.GetLength(0) - 1 fields(count).SetValue(instance, reader.Item(fields(count).Name)) Next returnList.Add(instance) End While Return returnList End Function 'LoadObjectsByFieldName '''----------------------------------------------------------------------------- ''' <summary> ''' Create container objects of the type specified and loads with data from ''' a dataReader by mapping the field position dataReader's field ''' position. ''' </summary> ''' <param name="objectType">the type of objects to create</param> ''' <param name="reader">the dataReader to use as a data source</param> ''' <returns>and arraylist of the objects from the reader</returns> ''' <remarks></remarks> ''' <history> ''' [CodeMeister] 10/17/2002 Created ''' </history> '''----------------------------------------------------------------------------- Public Shared Function LoadObjectsByFieldOrdinal(ByVal objectType As Type, ByVal reader As IDataReader) As ArrayList Dim fields As System.Reflection.FieldInfo() = objectType.GetFields(Reflection.BindingFlags.Instance Or Reflection.BindingFlags.Public) Dim returnList As New ArrayList While reader.Read() Dim instance As Object = Activator.CreateInstance(objectType) For count As Int32 = 0 To fields.GetLength(0) - 1 fields(count).SetValue(instance, reader.Item(count)) Next returnList.Add(instance) End While Return returnList End Function 'LoadObjectsByFieldOrdinal #End Region #Region "Private Methods" '''----------------------------------------------------------------------------- ''' <summary> ''' Creates a copy of the parameter array passed in. This function's primary purpose ''' is to clone the parameters attached to a command object since parameters cannot ''' be shared by multiple commands. ''' </summary> ''' <param name="originalParameters">the original parameter array to copy</param> ''' <returns>a cloned array of parameters</returns> ''' <remarks></remarks> ''' <history> ''' [CodeMeister] 10/20/2002 Created ''' </history> '''----------------------------------------------------------------------------- Private Function CloneParameters(ByVal originalParameters() As IDbDataParameter) As IDbDataParameter() Dim i As Integer Dim j As Integer = originalParameters.Length - 1 Dim clonedParameters(j) As IDbDataParameter For i = 0 To j clonedParameters(i) = CType(CType(originalParameters(i), ICloneable).Clone, IDbDataParameter) Next Return clonedParameters End Function ' CloneParameters '''----------------------------------------------------------------------------- ''' <summary> ''' Helper function for adding parameters to a command ''' </summary> ''' <param name="params">array of parameters, either SqlParameter or DBHelperParam</param> ''' <param name="cmd">Sql Command to append the parameters to</param> ''' <remarks></remarks> ''' <history> ''' J. Wojtowicz 1/2002 Created ''' J. Wojtowicz 7/2002 Modified to use the interfaces ''' </history> '''----------------------------------------------------------------------------- Private Sub AppendParameters(ByVal params As IDbDataParameter(), ByVal cmd As IDbCommand) ' add the parameters to the command Try If Not (params Is Nothing) Then For Each parameter As IDbDataParameter In params cmd.Parameters.Add(parameter) Next End If Catch e As Exception Throw New Exception(PARAMS_FAILED, e) End Try End Sub 'AppendParameters '''----------------------------------------------------------------------------- ''' <summary> ''' Creates an IDbCommand from the connection and assigns the command text, ''' command type and command timeout ''' </summary> ''' <param name="query">the command text for the command</param> ''' <param name="queryType">the command type for the command</param> ''' <returns>the created command</returns> ''' <remarks></remarks> ''' <history> ''' [CodeMeister] 10/20/2002 Created ''' </history> '''----------------------------------------------------------------------------- Private Function CreateCommand(ByVal query As String, ByVal queryType As CommandType) As IDbCommand Dim cmd As IDbCommand = Connection.CreateCommand() cmd.CommandText = query cmd.CommandType = queryType cmd.CommandTimeout = m_commandTimeout Return cmd End Function 'CreateCommand #End Region End Class 'DataAccessor

VB.Net DataProviderFactory

'-----------------------------------------------------------------------------
'Copyright �2001-2003 Jon Wojtowicz, All Rights Reserved.
' Some code may also contain other copyrights by the author.
'-----------------------------------------------------------------------------
' Distribution: You can freely use this code in your own
'               applications, but you may not reproduce 
'               or publish this code on any web site,
'               online service, or distribute as source 
'               on any media without express permission.
'-----------------------------------------------------------------------------

'''-----------------------------------------------------------------------------
''' Project  : DataAccess
''' Class  : ProviderFactory
'''
'''-----------------------------------------------------------------------------
''' <summary>
''' Provides static factory methods for creating data access objects
''' </summary>
''' <remarks></remarks>
''' <history>
'''  J. Wojtowicz 7/2002 Created
''' </history>
'''-----------------------------------------------------------------------------
Friend Class DataProviderFactory

    '''-----------------------------------------------------------------------------
    ''' <summary>
    ''' Creates an IDbConnection
    ''' </summary>
    ''' <param name="providerType">the type of connection to create</param>
    ''' <returns>the created connection</returns>
    ''' <remarks></remarks>
    ''' <history>
    '''  J. Wojtowicz 7/2002 Created
    ''' </history>
    '''-----------------------------------------------------------------------------
    Public Shared Function CreateConnection(ByVal providerType As Type) As IDbConnection

        Return CType(Activator.CreateInstance(providerType), IDbConnection)

    End Function 'CreateConnection

    '''-----------------------------------------------------------------------------
    ''' <summary>
    ''' Creates an IDbConnection 
    ''' </summary>
    ''' <param name="providerType">the type of connection to create</param>
    ''' <param name="connectionString">the connection string to use for the 
    ''' connection</param>
    ''' <returns>the created connection</returns>
    ''' <remarks></remarks>
    ''' <history>
    '''  J. Wojtowicz 7/2002 Created
    ''' </history>
    '''-----------------------------------------------------------------------------
    Public Shared Function CreateConnection(ByVal providerType As Type, ByVal connectionString As String) _
                    As IDbConnection

        Dim args As Object() = {connectionString}

        Return CType(Activator.CreateInstance(providerType, args), IDbConnection)

    End Function 'CreateConnection

    '''-----------------------------------------------------------------------------
    ''' <summary>
    ''' Creates an IDbCommand
    ''' </summary>
    ''' <param name="providerType">the type of command to create</param>
    ''' <returns>the created command</returns>
    ''' <remarks></remarks>
    ''' <history>
    '''  J. Wojtowicz 7/2002 Created
    ''' </history>
    '''-----------------------------------------------------------------------------
    Public Shared Function CreateCommand(ByVal providerType As Type) As IDbCommand

        Return CType(Activator.CreateInstance(providerType), IDbCommand)

    End Function 'CreateCommand

    '''-----------------------------------------------------------------------------
    ''' <summary>
    ''' Creates an IDbCommand
    ''' </summary>
    ''' <param name="providerType">the type of command to create</param>
    ''' <param name="cmdText">the text for the command</param>
    ''' <returns>the created command</returns>
    ''' <remarks></remarks>
    ''' <history>
    '''  J. Wojtowicz 7/2002 Created
    ''' </history>
    '''-----------------------------------------------------------------------------
    Public Shared Function CreateCommand(ByVal providerType As Type, ByVal cmdText As String) As IDbCommand

        Dim args As Object() = {cmdText}

        Return CType(Activator.CreateInstance(providerType, args), IDbCommand)

    End Function 'CreateCommand

    '''-----------------------------------------------------------------------------
    ''' <summary>
    ''' Creates an IDbCommand
    ''' </summary>
    ''' <param name="providerType">the type of command to create</param>
    ''' <param name="cmdText">the text for the command</param>
    ''' <param name="connection">the connection to use for the command</param>
    ''' <returns>the created command</returns>
    ''' <remarks></remarks>
    ''' <history>
    '''  J. Wojtowicz 7/2002 Created
    ''' </history>
    '''-----------------------------------------------------------------------------
    Public Shared Function CreateCommand(ByVal providerType As Type, ByVal cmdText As String, _
            ByVal connection As IDbConnection) As IDbCommand

        Dim args As Object() = {cmdText, connection}

        Return CType(Activator.CreateInstance(providerType, args), IDbCommand)

    End Function 'CreateCommand

    '''-----------------------------------------------------------------------------
    ''' <summary>
    ''' Creates an IDbCommand
    ''' </summary>
    ''' <param name="providerType">the type of command to create</param>
    ''' <param name="cmdText">the text for the command</param>
    ''' <param name="connection">the connection to use for the command</param>
    ''' <param name="transaction">the transaction to use for the command</param>
    ''' <returns>the created command</returns>
    ''' <remarks></remarks>
    ''' <history>
    '''  J. Wojtowicz 7/2002 Created
    ''' </history>
    '''-----------------------------------------------------------------------------
    Public Shared Function CreateCommand(ByVal providerType As Type, ByVal cmdText As String, _
            ByVal connection As IDbConnection, ByVal transaction As IDbTransaction) As IDbCommand

        Dim args As Object() = {cmdText, connection, transaction}

        Return CType(Activator.CreateInstance(providerType, args), IDbCommand)

    End Function 'CreateCommand

    '''-----------------------------------------------------------------------------
    ''' <summary>
    ''' Create an IDbDataAdapter
    ''' </summary>
    ''' <param name="providerType">the type of adapter to create</param>
    ''' <returns>the created adapter</returns>
    ''' <remarks></remarks>
    ''' <history>
    '''  J. Wojtowicz 7/2002 Created
    ''' </history>
    '''-----------------------------------------------------------------------------
    Public Shared Function CreateDataAdapter(ByVal providerType As Type) As IDbDataAdapter

        Return CType(Activator.CreateInstance(providerType), IDbDataAdapter)

    End Function 'CreateDataAdapter

    '''-----------------------------------------------------------------------------
    ''' <summary>
    ''' Create an IDbDataAdapter
    ''' </summary>
    ''' <param name="providerType">the type of adapter to create</param>
    ''' <param name="selectCommand">the select command to use for the adapter</param>
    ''' <returns>the created adapter</returns>
    ''' <remarks></remarks>
    ''' <history>
    '''  J. Wojtowicz 7/2002 Created
    ''' </history>
    '''-----------------------------------------------------------------------------
    Public Shared Function CreateDataAdapter(ByVal providerType As Type, ByVal selectCommand As IDbCommand) _
            As IDbDataAdapter

        Dim args As Object() = {selectCommand}

        Return CType(Activator.CreateInstance(providerType, args), IDbDataAdapter)

    End Function 'CreateDataAdapter

    '''-----------------------------------------------------------------------------
    ''' <summary>
    ''' Create an IDbDataAdapter
    ''' </summary>
    ''' <param name="providerType">the type of adapter to create</param>
    ''' <param name="selectCommandText">the select text for the adapter</param>
    ''' <param name="selectConnection">the connection to use for the adapter</param>
    ''' <returns>the created adapter</returns>
    ''' <remarks></remarks>
    ''' <history>
    '''  J. Wojtowicz 7/2002 Created
    ''' </history>
    '''-----------------------------------------------------------------------------
    Public Shared Function CreateDataAdapter(ByVal providerType As Type, ByVal selectCommandText As String, _
            ByVal selectConnection As IDbConnection) As IDbDataAdapter

        Dim args As Object() = {selectCommandText, selectConnection}

        Return CType(Activator.CreateInstance(providerType, args), IDbDataAdapter)

    End Function 'CreateDataAdapter

    '''-----------------------------------------------------------------------------
    ''' <summary>
    ''' Create an IDbDataAdapter
    ''' </summary>
    ''' <param name="providerType">the type of adapter to create</param>
    ''' <param name="selectCommandText">the select text for the adapter</param>
    ''' <param name="selectConnectionString">the connection string to use for the adapter</param>
    ''' <returns>the created adapter</returns>
    ''' <remarks></remarks>
    ''' <history>
    '''  J. Wojtowicz 7/2002 Created
    ''' </history>
    '''-----------------------------------------------------------------------------
    Public Shared Function CreateDataAdapter(ByVal providerType As Type, ByVal selectCommandText As String, _
            ByVal selectConnectionString As String) As IDbDataAdapter

        Dim args As Object() = {selectCommandText, selectConnectionString}

        Return CType(Activator.CreateInstance(providerType, args), IDbDataAdapter)

    End Function 'CreateDataAdapter

    '''-----------------------------------------------------------------------------
    ''' <summary>
    ''' Create an IDbDataParameter
    ''' </summary>
    ''' <param name="providerType">the type of parameter to create</param>
    ''' <returns>the created parameter</returns>
    ''' <remarks></remarks>
    ''' <history>
    '''  J. Wojtowicz 7/2002 Created
    ''' </history>
    '''-----------------------------------------------------------------------------
    Public Shared Function CreateDataParameter(ByVal providerType As Type) As IDbDataParameter

        Return CType(Activator.CreateInstance(providerType), IDbDataParameter)

    End Function 'CreateDataParameter

    '''-----------------------------------------------------------------------------
    ''' <summary>
    ''' Create an IDbDataParameter
    ''' </summary>
    ''' <param name="providerType">the type of parameter to create</param>
    ''' <param name="parameterName">the name of the parameter</param>
    ''' <param name="value">the value of the parameter</param>
    ''' <returns>the created parameter</returns>
    ''' <remarks></remarks>
    ''' <history>
    '''  J. Wojtowicz 7/2002 Created
    ''' </history>
    '''-----------------------------------------------------------------------------
    Public Shared Function CreateDataParameter(ByVal providerType As Type, ByVal parameterName As String, _
            ByVal value As Object) As IDbDataParameter

        Dim args As Object() = {parameterName, value}

        Return CType(Activator.CreateInstance(providerType, args), IDbDataParameter)

    End Function 'CreateDataParameter

    '''-----------------------------------------------------------------------------
    ''' <summary>
    ''' Create an IDbDataParameter
    ''' </summary>
    ''' <param name="providerType">the tyep of parameter to create</param>
    ''' <param name="parameterName">the name of the parameter</param>
    ''' <param name="value">the value of the parameter</param>
    ''' <param name="dataType">the data type of the parameter</param>
    ''' <returns>the created parameter</returns>
    ''' <remarks></remarks>
    ''' <history>
    '''  J. Wojtowicz 7/2002 Created
    ''' </history>
    '''-----------------------------------------------------------------------------
    Public Shared Function CreateDataParameter(ByVal providerType As Type, ByVal parameterName As String, _
            ByVal value As Object, ByVal dataType As DbType) As IDbDataParameter

        Dim param As IDbDataParameter = CreateDataParameter(providerType)

        If Not (param Is Nothing) Then
            With param
                .ParameterName = parameterName
                .DbType = dataType
                .Value = value
            End With
        End If

        Return param

    End Function 'CreateDataParameter

    '''-----------------------------------------------------------------------------
    ''' <summary>
    ''' Create an IDbDataParameter
    ''' </summary>
    ''' <param name="providerType">the type of parameter to create</param>
    ''' <param name="parameterName">the name of the parameter</param>
    ''' <param name="dataType">the data type of the parameter</param>
    ''' <param name="size">the size of the parameter data</param>
    ''' <returns>the created parameter</returns>
    ''' <remarks></remarks>
    ''' <history>
    '''  J. Wojtowicz 7/2002 Created
    ''' </history>
    '''-----------------------------------------------------------------------------
    Public Shared Function CreateDataParameter(ByVal providerType As Type, ByVal parameterName As String, _
            ByVal dataType As DbType, ByVal size As Integer) As IDbDataParameter

        Dim param As IDbDataParameter = CreateDataParameter(providerType)

        If Not (param Is Nothing) Then
            With param
                .ParameterName = parameterName
                .DbType = dataType
                .Size = size
            End With
        End If

        Return param

    End Function 'CreateDataParameter

    '''-----------------------------------------------------------------------------
    ''' <summary>
    ''' Create an IDbDataParameter
    ''' </summary>
    ''' <param name="providerType">the type of parameter to create</param>
    ''' <param name="parameterName">the name of the parameter</param>
    ''' <param name="dataType">the data type of the parameter</param>
    ''' <param name="size">the size of the parameter data</param>
    ''' <param name="sourceColumn">the name of the source column</param>
    ''' <returns>the create parameter</returns>
    ''' <remarks></remarks>
    ''' <history>
    '''  J. Wojtowicz 7/2002 Created
    ''' </history>
    '''-----------------------------------------------------------------------------
    Public Shared Function CreateDataParameter(ByVal providerType As Type, ByVal parameterName As String, _
            ByVal dataType As DbType, ByVal size As Integer, ByVal sourceColumn As String) As IDbDataParameter

        Dim param As IDbDataParameter = CreateDataParameter(providerType)

        If Not (param Is Nothing) Then
            With param
                .ParameterName = parameterName
                .DbType = dataType
                .Size = size
                .SourceColumn = sourceColumn
            End With
        End If

        Return param

    End Function 'CreateDataParameter

    '''-----------------------------------------------------------------------------
    ''' <summary>
    ''' Create an IDbDataParameter
    ''' </summary>
    ''' <param name="providerType">the type of parameter to create</param>
    ''' <param name="parameterName">the name of the parameter</param>
    ''' <param name="dataType">the data type of the parameter</param>
    ''' <param name="size">the size of the parameter data</param>
    ''' <param name="direction">the direction of the parameter</param>
    ''' <returns>the create parameter</returns>
    ''' <remarks></remarks>
    ''' <history>
    '''  J. Wojtowicz 7/2002 Created
    ''' </history>
    '''-----------------------------------------------------------------------------
    Public Shared Function CreateDataParameter(ByVal providerType As Type, ByVal parameterName As String, _
            ByVal dataType As DbType, ByVal size As Integer, ByVal direction As ParameterDirection) As IDbDataParameter

        Dim param As IDbDataParameter = CreateDataParameter(providerType)

        If Not (param Is Nothing) Then
            With param
                .ParameterName = parameterName
                .DbType = dataType
                .Size = size
                .Direction = direction
            End With
        End If

        Return param

    End Function 'CreateDataParameter

    '''-----------------------------------------------------------------------------
    ''' <summary>
    ''' Create an IDbDataParameter
    ''' </summary>
    ''' <param name="providerType">the type of parameter to create</param>
    ''' <param name="parameterName">the name of the parameter</param>
    ''' <param name="dataType">the data type of the parameter</param>
    ''' <param name="value">the value of the parameter</param>
    ''' <param name="size">the size of the parameter data</param>
    ''' <param name="direction">the direction of the parameter</param>
    ''' <returns>the create parameter</returns>
    ''' <remarks></remarks>
    ''' <history>
    '''  J. Wojtowicz 7/2002 Created
    ''' </history>
    '''-----------------------------------------------------------------------------
    Public Shared Function CreateDataParameter(ByVal providerType As Type, ByVal parameterName As String, _
                ByVal dataType As DbType, ByVal value As Object, ByVal size As Integer, _
            ByVal direction As ParameterDirection) As IDbDataParameter

        Dim param As IDbDataParameter = CreateDataParameter(providerType)

        If Not (param Is Nothing) Then
            With param
                .ParameterName = parameterName
                .DbType = dataType
                .Size = size
                .Direction = direction
                .Value = value
            End With
        End If

        Return param

    End Function 'CreateDataParameter

End Class
An interesting item was noticed in that the connection had a CreateCommand for dynamically creating the command object. This made the CreateCommand in my factory irrelevant but they were left in place.

If you have to connect to multiple types of data sources you may find this helpful.

Download the code that accompanies this article

Jon Wojtowicz is a C# MVP and a Systems Analyst at a large insurance company in Chattanooga, TN where he currently provides developer support and internal training. He has worked as a consultant working with Microsoft Technologies. This includes ASP, COM, VB6 and .Net, both C# and VB.Net since Beta 1. He has been an MCSD since 1999 and an MCT since 2000. Prior to getting a degree in Computer science he worked as a process engineer focusing on process automation, programmable controllers and equipment installations. In his spare time he likes woodworking and gardening.
Article Discussion: