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
///
/// Creates a parameter and adds it to the command object
///
/// A parameter object
///
/// 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();
}