using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;

public class AccessHelper:IDisposable
    {
        private OleDbConnection m_conn = new OleDbConnection();


        public static string DATABASE = AppDomain.CurrentDomain.BaseDirectory + "rate.accdb";


        public AccessHelper()
        {
            string connstr = "Provider = Microsoft.ACE.OLEDB.12.0;";
            connstr += "Data Source =\"" + DATABASE + "\"";
            m_conn.ConnectionString = connstr;
            m_conn.Open();
        }


        public void ExecuteCommand(string sql)
        {
            OleDbCommand cmd = null;
            lock (cmd=new OleDbCommand())
            {
                cmd.Connection = m_conn;
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();
            }
        }


        public void ExecuteCommand(string sql, OleDbParameter[] ps)
        {
            OleDbCommand cmd = null;
            lock (cmd = new OleDbCommand())
            {
                cmd.Connection = m_conn;
                cmd.CommandText = sql;
                foreach (OleDbParameter p in ps)
                {
                    cmd.Parameters.Add(p);
                }
                cmd.ExecuteNonQuery();
            }
        }


        public object QuerySome(string sql)
        {
            OleDbCommand cmd = null;
            lock (cmd = new OleDbCommand())
            {
                cmd.Connection = m_conn;
                cmd.CommandText = sql;
                using (OleDbDataReader or = cmd.ExecuteReader())
                {
                    if (or.Read())
                    {
                        return or.GetValue(0);
                    }
                }
            }
            return null;
        }


        public DataTable GetSource(string strsql)
        {
            DataTable dt = null;
            OleDbCommand cmd = null;
            OleDbDataAdapter ad = null;
            try
            {
                lock (dt = new DataTable())
                {
                    cmd = new OleDbCommand(strsql, m_conn);
                    ad = new OleDbDataAdapter((OleDbCommand)cmd);
                    dt.Clear();
                    ad.Fill(dt);
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            return dt;
        }


        void IDisposable.Dispose()
        {
            m_conn.Close();
            m_conn.Dispose();
            m_conn = null;
        }
    }