If you need an module that helps you setting up quick access to multiple database providers, you will be very lucky with this code. I recognized it on code4forever.blogspot.com
Here is the DBHelper class:
/* Copyright Code4Forever 2012. All rights reserved. Visit code4forever.blogspot.com for more information about us. */ using System; using System.Collections.Generic; using System.Text; using System.Data; using System.Data.Common; using System.Data.SqlClient; using System.Data.Odbc; using System.Data.OleDb; using System.Data.OracleClient; namespace YourNamespace { ////// dbHelper is a helper class that takes the common data classes and allows you /// to specify the provider to use, execute commands, add parameters, and return datasets. /// See examples for usage. /// public class DBHelper { #region private members private string _connectionstring = ""; private DbConnection _connection; private DbCommand _command; private DbProviderFactory _factory = null; #endregion #region properties ////// Gets or Sets the connection string for the database /// public string connectionstring { get { return _connectionstring; } set { if (value != "") { _connectionstring = value; } } } ////// Gets the connection object for the database /// public DbConnection connection { get { return _connection; } } ////// Gets the command object for the database /// public DbCommand command { get { return _command; } } #endregion # region methods ////// Determines the correct provider to use and sets up the connection and command /// objects for use in other methods /// /// The full connection string to the database /// The enum value of providers from dbutilities.Providers public void CreateDBObjects(string connectString, Providers providerList) { //CreateDBObjects(connectString, providerList, null); switch (providerList) { case Providers.SqlServer: _factory = SqlClientFactory.Instance; break; case Providers.Oracle: _factory = OracleClientFactory.Instance; break; case Providers.OleDB: _factory = OleDbFactory.Instance; break; case Providers.ODBC: _factory = OdbcFactory.Instance; break; } _connection = _factory.CreateConnection(); _command = _factory.CreateCommand(); _connection.ConnectionString = connectString; _command.Connection = connection; } #region parameters ////// Creates a parameter and adds it to the command object /// /// The parameter name /// The paremeter value ///public int AddParameter(string name, object value) { DbParameter parm = _factory.CreateParameter(); parm.ParameterName = name; parm.Value = value; return command.Parameters.Add(parm); } /// /// Creates a parameter and adds it to the command object /// /// A parameter object ///public int AddParameter(DbParameter parameter) { return command.Parameters.Add(parameter); } #endregion #region transactions /// /// Starts a transaction for the command object /// private void BeginTransaction() { if (connection.State == System.Data.ConnectionState.Closed) { connection.Open(); } command.Transaction = connection.BeginTransaction(); } ////// Commits a transaction for the command object /// private void CommitTransaction() { command.Transaction.Commit(); connection.Close(); } ////// Rolls back the transaction for the command object /// private void RollbackTransaction() { command.Transaction.Rollback(); connection.Close(); } #endregion #region execute database functions ////// Executes a statement that does not return a result set, such as an INSERT, UPDATE, DELETE, or a data definition statement /// /// The query, either SQL or Procedures /// The command type, text, storedprocedure, or tabledirect /// The connection state ///An integer value public int ExecuteNonQuery(string query, CommandType commandtype, ConnectionState connectionstate) { command.CommandText = query; command.CommandType = commandtype; int i = -1; try { if (connection.State == System.Data.ConnectionState.Closed) { connection.Open(); } BeginTransaction(); i = command.ExecuteNonQuery(); } catch (Exception ex) { RollbackTransaction(); throw (ex); } finally { CommitTransaction(); command.Parameters.Clear(); if (connection.State == System.Data.ConnectionState.Open) { connection.Close(); connection.Dispose(); //command.Dispose(); } } return i; } ////// Executes a statement that returns a single value. /// If this method is called on a query that returns multiple rows and columns, only the first column of the first row is returned. /// /// The query, either SQL or Procedures /// The command type, text, storedprocedure, or tabledirect /// The connection state ///An object that holds the return value(s) from the query public object ExecuteScaler(string query, CommandType commandtype, ConnectionState connectionstate) { command.CommandText = query; command.CommandType = commandtype; object obj = null; try { if (connection.State == System.Data.ConnectionState.Closed) { connection.Open(); } BeginTransaction(); obj = command.ExecuteScalar(); } catch (Exception ex) { RollbackTransaction(); throw (ex); } finally { CommitTransaction(); command.Parameters.Clear(); if (connection.State == System.Data.ConnectionState.Open) { connection.Close(); connection.Dispose(); command.Dispose(); } } return obj; } ////// Executes a SQL statement that returns a result set. /// /// The query, either SQL or Procedures /// The command type, text, storedprocedure, or tabledirect /// The connection state ///A datareader object public DbDataReader ExecuteReader(string query, CommandType commandtype, ConnectionState connectionstate) { command.CommandText = query; command.CommandType = commandtype; DbDataReader reader = null; try { if (connection.State == System.Data.ConnectionState.Closed) { connection.Open(); } if (connectionstate == System.Data.ConnectionState.Open) { reader = command.ExecuteReader(CommandBehavior.CloseConnection); } else { reader = command.ExecuteReader(); } } catch (Exception ex) { throw (ex); } finally { command.Parameters.Clear(); } return reader; } ////// Generates a dataset /// /// The query, either SQL or Procedures /// The command type, text, storedprocedure, or tabledirect /// The connection state ///A dataset containing data from the database public DataSet GetDataSet(string query, CommandType commandtype, ConnectionState connectionstate) { DbDataAdapter adapter = _factory.CreateDataAdapter(); command.CommandText = query; command.CommandType = commandtype; adapter.SelectCommand = command; DataSet ds = new DataSet(); try { adapter.Fill(ds); } catch (Exception ex) { throw ex; } finally { command.Parameters.Clear(); if (connection.State == System.Data.ConnectionState.Open) { connection.Close(); connection.Dispose(); command.Dispose(); } } return ds; } #endregion #endregion #region enums ////// A list of data providers /// public enum Providers { SqlServer, OleDB, ODBC, Oracle, } #endregion } }
Here is a simple example how to use it e.g. in combination with MS Access.
// Create an instance of DBHelper. DBHelper v_DBHelper = new DBHelper(); v_DBHelper.CreateDBObjects(Constants.CONNECTION_ACCESS_DB, DBHelper.Providers.OleDB); DbDataReader v_DataReader; // Try to query something for eache item of a listbox. try { for (int i = 0; i < lst_Listbox.Count; i++) { // Substitute an Attribute (Product_ID) in a given SQL-String (Constants.SQL_SELECT_ProductByMaterialNr) with your needs. v_DBHelper.AddParameter("@PRODUCT_ID", lst_Listbox.Count.Items[i].SubItems[1].Text); v_DataReader = v_DBHelper.ExecuteReader(Constants.SQL_SELECT_ProductByMaterialNr, CommandType.Text, ConnectionState.Closed); if (v_DataReader.HasRows) { while (v_DataReader.Read()) { // Add the result. lst_Listbox.Items[i].SubItems[2].Text = v_DataReader["PRODUCTNAME"].ToString(); } } else { // No result returned } // Close only the reader within the loop, connection is left open. v_DataReader.Close(); } } finally { // Tidy up, now close the whole connection v_DBHelper.connection.Close(); }