A C# helper class for database access for various providers

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();
                    }

Leave a Reply

Your email address will not be published. Required fields are marked *