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>
  30. public class ProviderFactory
  31. {
  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
  41. #region ctors
  43. private ProviderFactory() { } // force user to specify provider
  44. public ProviderFactory(ProviderType provider)
  45. {
  46. _provider = provider;
  47. }
  48. #endregion
  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
  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
  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
  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
  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. }