public class DataBaseClass : IDisposable
    {
        private IntPtr nativeResource = Marshal.AllocHGlobal(100);
        private OleDbConnection connection;
        private OleDbCommand command;
        private OleDbDataAdapter adapter;
        private OleDbTransaction trans;
        private OleDbCommandBuilder builder;
        private DataSet dataSet;
        private CommonDTO commonDTO;
        private int check;

        public DataBaseClass()
        {
            try
            {
                string connectionstring = ConfigurationManager.AppSettings ["CONN"];
                connection = new OleDbConnection(connectionstring);
                if (connection.State == ConnectionState.Closed)
                    connection.Open();
            }
            catch (ConfigurationErrorsException ex)
            {
                throw ex;
            }
            catch (OleDbException ex)
            {
                throw ex;
            }
            catch (InvalidOperationException ex)
            {
                throw ex;
            }
        }

       
               public DataSet SelectFromDataBase(string SelectQuery, string tblTablename)
        {
            try
            {
                dataSet = new DataSet();
                command = new OleDbCommand(SelectQuery, connection);
                adapter = new OleDbDataAdapter(command);
                trans = connection.BeginTransaction();
                command.Transaction = trans;
                check = adapter.Fill(dataSet, tblTablename);
                trans.Commit();
            }
            catch (InvalidOperationException ex)
            {
                if (trans != null)
                    trans.Rollback();
                throw ex;
            }
            catch (SystemException ex)
            {
                if (trans != null)
                    trans.Rollback();
                throw ex;
            }
            catch (Exception ex)
            {
                if (trans != null)
                    trans.Rollback();
                throw ex;
            }
            finally
            {
                if (connection != null)
                {
                    if (connection.State == ConnectionState.Open)
                        connection.Close();
                }
                Dispose(true);
            }
            return dataSet;
        }

        internal int DbInsert(string Insertquery)
        {
            check = DbOperation(Insertquery);
            return check;
        }

        internal int DbUpdate(string Updatequery)
        {
            check = DbOperation(Updatequery);
            return check;
        }

        internal int DbDelete(string Deletequery)
        {
            check = DbOperation(Deletequery);
            return check;
        }

        public int DbOperation(string query)
        {
            try
            {
                command = new OleDbCommand(query, connection);
                trans = connection.BeginTransaction();
                command.Transaction = trans;
                check = command.ExecuteNonQuery();
                trans.Commit();
            }
            catch (InvalidOperationException ex)
            {
                if (trans != null)
                    trans.Rollback();
                throw ex;
            }
            catch (SystemException ex)
            {
                if (trans != null)
                    trans.Rollback();
                throw ex;
            }
            catch (Exception ex)
            {
                if (trans != null)
                    trans.Rollback();
                throw ex;
            }
            finally
            {
                if (connection != null)
                {
                    if (connection.State == ConnectionState.Open)
                        connection.Close();
                }
                Dispose(true);
            }
            return check;
        }

        internal int InsertDataInOneTime(DataSet report, string tableName)
        {
            try
            {
                command = new OleDbCommand(Resource.SelectQuery + tableName, connection);
                adapter = new OleDbDataAdapter(command);
                builder = new OleDbCommandBuilder(adapter);
                trans = connection.BeginTransaction();
                command.Transaction = trans;
                adapter.InsertCommand = builder.GetInsertCommand();
                check = adapter.Update(report, tableName);
                trans.Commit();
            }
            catch (InvalidOperationException ex)
            {
                if (trans != null)
                    trans.Rollback();
                throw ex;
            }
            catch (SystemException ex)
            {
                if (trans != null)
                    trans.Rollback();
                throw ex;
            }
            catch (Exception ex)
            {
                if (trans != null)
                    trans.Rollback();
                throw ex;
            }
            finally
            {
                if (connection != null)
                {
                    if (connection.State == ConnectionState.Open)
                        connection.Close();
                }
                Dispose(true);
            }
            return check;
        }

       
        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        ~DataBaseClass()
        {
            Dispose(false);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (disposing)
            {
                // free managed resources
                if (connection != null)
                {
                    connection.Dispose();
                    connection = null;
                }
                if (trans != null)
                {
                    trans.Dispose();
                    trans = null;
                }
                if (command != null)
                {
                    command.Dispose();
                    command = null;
                }
                if (adapter != null)
                {
                    adapter.Dispose();
                    adapter = null;
                }
                if (builder != null)
                {
                    builder.Dispose();
                    builder = null;
                }
            }
            // free native resources if there are any.
            if (nativeResource != IntPtr.Zero)
            {
                Marshal.FreeHGlobal(nativeResource);
                nativeResource = IntPtr.Zero;
            }
        }
    }