using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace OleDbHelper
{
    public sealed class OleDbHelper
    {
        static string cnstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Application.StartupPath + "\\records.mdb";
        //const string="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Application.StartupPath + "\\records.mdb;Jet OLEDB:Database Password=eowc";
        private OleDbHelper() { }
        private static void AttachParameters(OleDbCommand command, OleDbParameter[] commandParameters)
        {
            foreach (OleDbParameter p in commandParameters)
            {
                if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null))
                {
                    p.Value = DBNull.Value;
                }
                command.Parameters.Add(p);
            }
        }
        private static void AssignParameterValues(OleDbParameter[] commandParameters, object[] parameterValues)
        {
            if ((commandParameters == null) || (parameterValues == null))
            {
                return;
            }
            if (commandParameters.Length != parameterValues.Length)
            {
                throw new ArgumentException("Parameter count does not match Parameter Value count.");
            }
            for (int i = 0, j = commandParameters.Length; i < j; i++)
            {
                commandParameters[i].Value = parameterValues[i];
            }
        }
        private static void PrepareCommand(OleDbCommand command, OleDbConnection connection, OleDbTransaction transaction, CommandType commandType, string commandText, OleDbParameter[] commandParameters)
        {
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }
            command.Connection = connection;
            command.CommandText = commandText;
            if (transaction != null)
            {
                command.Transaction = transaction;
            }
            command.CommandType = commandType;
            if (commandParameters != null)
            {
                AttachParameters(command, commandParameters);
            }
            return;
        }
        public static int ExecuteNonQuery(string sql)
        {
            return ExecuteNonQuery(sql, (OleDbParameter[])null);
        }
        public static int ExecuteNonQuery(string sql, params OleDbParameter[] commandParameters)
        {
            using (OleDbConnection cn = new OleDbConnection(cnstr))
            {
                cn.Open();
                return ExecuteNonQuery(cn, CommandType.Text, sql, commandParameters);
            }
        }
        private static int ExecuteNonQuery(OleDbConnection connection, CommandType commandType, string commandText, params OleDbParameter[] commandParameters)
        {
            OleDbCommand cmd = new OleDbCommand();
            PrepareCommand(cmd, connection, (OleDbTransaction)null, commandType, commandText, commandParameters);
            return cmd.ExecuteNonQuery();
        }
        public static int ExecuteProcedure(string Proc, params object[] parameterValues)
        {
            if ((parameterValues != null) && (parameterValues.Length > 0))
            {
                OleDbParameter[] commandParameters = OleDbHelperParameterCache.GetSpParameterSet(cnstr, Proc);
                AssignParameterValues(commandParameters, parameterValues);
                using (OleDbConnection cn = new OleDbConnection(cnstr))
                {
                    cn.Open();
                    return ExecuteNonQuery(cn, CommandType.StoredProcedure, Proc, commandParameters);
                }
            }
            else
            {
                using (OleDbConnection cn = new OleDbConnection(cnstr))
                {
                    cn.Open();
                    return ExecuteNonQuery(cn, CommandType.StoredProcedure, Proc);
                }
            }
        }
        public static object ExecuteScalar(string sql)
        {
            return ExecuteScalar(sql, (OleDbParameter[])null);
        }
        public static object ExecuteScalar(string sql, params OleDbParameter[] commandParameters)
        {
            using (OleDbConnection cn = new OleDbConnection(cnstr))
            {
                cn.Open();
                return ExecuteScalar(cn, CommandType.Text, sql, commandParameters);
            }
        }
        private static object ExecuteScalar(OleDbConnection connection, CommandType commandType, string commandText, params OleDbParameter[] commandParameters)
        {
            OleDbCommand cmd = new OleDbCommand();
            PrepareCommand(cmd, connection, (OleDbTransaction)null, commandType, commandText, commandParameters);
            return cmd.ExecuteScalar();
        }
        public static DataTable FillDataTable(string sql, params OleDbParameter[] cmdParms)
        {
            OleDbCommand cmd = new OleDbCommand();
            using (OleDbConnection conn = new OleDbConnection(cnstr))
            {
                DataTable dt = new DataTable();
                PrepareCommand(cmd, conn, (OleDbTransaction)null, CommandType.Text, sql, cmdParms);
                OleDbDataAdapter sda = new OleDbDataAdapter(cmd);
                sda.Fill(dt);
                cmd.Parameters.Clear();
                sda.Dispose();
                return dt;
            }
        }
        public static void ExecuteSqlList(List<string> SQLStringList)
        {
            using (OleDbConnection conn = new OleDbConnection(constr))
            {
                conn.Open();
                OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = conn;
                OleDbTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strSQL = SQLStringList[n].ToString();
                        if (strSQL.Trim().Length > 1)
                        {
                            cmd.CommandText = strSQL;
                            cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                }
                catch (Exception E)
                {
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
            }
        }
    }
    public sealed class OleDbHelperParameterCache
    {
        private OleDbHelperParameterCache() { }
        private static Hashtable paramCache = Hashtable.Synchronized(new Hashtable());
        private static OleDbParameter[] DiscoverSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter)
        {
            using (OleDbConnection cn = new OleDbConnection(connectionString))
            using (OleDbCommand cmd = new OleDbCommand(spName, cn))
            {
                cn.Open();
                cmd.CommandType = CommandType.StoredProcedure;
                OleDbCommandBuilder.DeriveParameters(cmd);
                if (!includeReturnValueParameter)
                {
                    if (ParameterDirection.ReturnValue == cmd.Parameters[0].Direction)
                        cmd.Parameters.RemoveAt(0);
                }
                OleDbParameter[] discoveredParameters = new OleDbParameter[cmd.Parameters.Count];
                cmd.Parameters.CopyTo(discoveredParameters, 0);
                return discoveredParameters;
            }
        }
        private static OleDbParameter[] CloneParameters(OleDbParameter[] originalParameters)
        {
            OleDbParameter[] clonedParameters = new OleDbParameter[originalParameters.Length];
            for (int i = 0, j = originalParameters.Length; i < j; i++)
            {
                clonedParameters[i] = (OleDbParameter)((ICloneable)originalParameters[i]).Clone();
            }
            return clonedParameters;
        }
        public static void CacheParameterSet(string connectionString, string commandText, params OleDbParameter[] commandParameters)
        {
            string hashKey = connectionString + ":" + commandText;
            paramCache[hashKey] = commandParameters;
        }
        public static OleDbParameter[] GetCachedParameterSet(string connectionString, string commandText)
        {
            string hashKey = connectionString + ":" + commandText;
            OleDbParameter[] cachedParameters = (OleDbParameter[])paramCache[hashKey];
            if (cachedParameters == null)
            {
                return null;
            }
            else
            {
                return CloneParameters(cachedParameters);
            }
        }
        public static OleDbParameter[] GetSpParameterSet(string connectionString, string spName)
        {
            return GetSpParameterSet(connectionString, spName, true);
        }
        public static OleDbParameter[] GetSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter)
        {
            string hashKey = connectionString + ":" + spName + (includeReturnValueParameter ? ":include ReturnValue Parameter" : "");
            OleDbParameter[] cachedParameters;
            cachedParameters = (OleDbParameter[])paramCache[hashKey];
            if (cachedParameters == null)
            {
                cachedParameters = (OleDbParameter[])(paramCache[hashKey] = DiscoverSpParameterSet(connectionString, spName, includeReturnValueParameter));
            }
            return CloneParameters(cachedParameters);
        }
    }
}