using System;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Data;
using System.Collections;
using System.Reflection;
using System.Runtime.InteropServices;

namespace Tool
{
    /// <summary>
    /// Access helper
    /// </summary>
    public class AccessHelper
    {
        private string accesspath = string.Empty;
        private string accesspwd = string.Empty;

        private string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=db.mdb";
        OleDbConnection connect = null;
        OleDbCommand command = null;

        /// <summary>
        /// 
        /// </summary>
        public string ConnectionString
        {
            get { return connectionString; }
            set { connectionString = value; }
        }

        /// <summary>
        /// 
        /// </summary>
        public string AccessPath
        {
            get { return accesspath; }
            set { accesspath = value; }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="FilePath">Access path</param>
        public AccessHelper(string FilePath)
        {
            ToFullRootPath(FilePath);
            ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + AccessPath;
            connect = new OleDbConnection(ConnectionString);
        }

        /// <summary>
        /// 
        /// </summary>
        public AccessHelper()
        {
            connect = new OleDbConnection(ConnectionString);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="FilePath">Access文件路徑</param>
        /// <param name="PassWord">密碼</param>
        public AccessHelper(string FilePath, string PassWord)
        {
            accesspwd = PassWord;
            ToFullRootPath(FilePath);
            ConnectionString = string.Format("Provider=Microsoft.Jet.OleDb.4.0;Data Source={0};User ID=Admin;Jet OLEDB:Database Password={1};", AccessPath, accesspwd);
            connect = new OleDbConnection(ConnectionString);
        }

        /// <summary>
        /// 
        /// </summary>
        private void Open()
        {
            try
            {
                if (connect.State != System.Data.ConnectionState.Open)
                {
                    connect.Open();
                }

            }
            catch (Exception ex)
            {
                throw (new Exception(ex.Message));
            }
        }

        /// <summary>
        /// 
        /// </summary>
        private void Close()
        {
            try
            {
                if (connect.State != System.Data.ConnectionState.Closed)
                {
                    connect.Close();
                }

            }
            catch (Exception ex)
            {
                throw (new Exception(ex.Message));
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="path"></param>
        private void ToFullRootPath(string path)
        {
            if (System.IO.Path.IsPathRooted(path))
            {
                AccessPath = path;
            }
            else
            {
                AccessPath = AppDomain.CurrentDomain.BaseDirectory + path;
            }
        }

        
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public bool ConnectTest()
        {
            try
            {
                connect.Open();
            }
            catch
            {
                connect.Close();
                return false;
            }
            return true;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sqlstr"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sqlstr)
        {
            try
            {
                Open();
                command = new OleDbCommand(sqlstr, connect);
                int num = command.ExecuteNonQuery();
                command.Parameters.Clear();
                Close();
                return num;
            }
            catch
            { throw; }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="tableName"></param>
        public void DropTableIfExists(string tableName)
        {
            try
            {
            if (DoesTableExist(tableName) != "")
            {
                Open();
                command = new OleDbCommand("DROP TABLE "+tableName, connect);
                int num = command.ExecuteNonQuery();
                command.Parameters.Clear();
                Close();
                return;
            }
            }
            catch
            { throw; }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sqlstr"></param>
        /// <returns></returns>
        public string ExecuteScalar(string sqlstr)
        {
            try
            {
                Open();
                command = new OleDbCommand(sqlstr, connect);
                object value = command.ExecuteScalar();
                command.Parameters.Clear();
                Close();
                if (value != null)
                {
                    return value.ToString();
                }
                return "";
            }
            catch
            { throw; }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sqlstr">Sql</param>
        /// <returns></returns>
        public DataSet ReturnDataSet(string sqlstr)
        {
            DataSet ds = new DataSet();
            try
            {
                OleDbDataAdapter adapter = new OleDbDataAdapter(sqlstr, connect);
                adapter.Fill(ds, "Obj");
                adapter.Dispose();
            }
            catch (Exception)
            {
                throw;
            }
            return ds;

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sqlstr">Sqk</param>
        /// <returns></returns>
        public DataTable GetDataTable(string sqlstr)
        {
            DataTable dt = new DataTable();
            try
            {
                Open();
                command = new OleDbCommand(sqlstr, connect);
                OleDbDataReader myReader = command.ExecuteReader();
                dt.Load(myReader);
                myReader.Close();
                Close();
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sqlstr">Sql</param>
        /// <returns></returns>
        public OleDbDataReader ReturnDataReader(string sqlstr)
        {
            try
            {
                Open();
                command = new OleDbCommand(sqlstr, connect);
                OleDbDataReader myReader = command.ExecuteReader();
                command.Parameters.Clear();
                Close();
                return myReader;
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw new Exception(e.Message, e);
            }

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="SQLStringList"></param>
        public void ExecuteSqlTran(ArrayList SQLStringList)
        {

            Open();
            command = new OleDbCommand();
            command.Connection = connect;
            OleDbTransaction tx = connect.BeginTransaction();
            command.Transaction = tx;
            try
            {
                for (int n = 0; n < SQLStringList.Count; n++)
                {
                    string strsql = SQLStringList[n].ToString();
                    if (strsql.Trim().Length > 1)
                    {
                        command.CommandText = strsql;
                        command.ExecuteNonQuery();
                    }
                }
                tx.Commit();
                Close();
            }
            catch (Exception)
            {
                tx.Rollback();
                Close();
                throw;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public string[] GetTables()
        {
            List<string> Ls = new List<string>();

            Open();
            DataTable dt = connect.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "table" });
            Close();
            foreach (DataRow dr in dt.Rows)
            {
                Ls.Add((string)dr["table_name"]);
            }

            return Ls.ToArray();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public string DoesTableExist(string tableName)
        {
            string[] names = GetTables();
            for (int i = 0; i < names.Length; i++)
            {
                if (names[i] == tableName)
                    return "true";
            }
            return "";
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public string[] GetColumns(string TableName)
        {
            Open();
            DataTable dt = connect.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, TableName, null });
            Close();
            List<string> Ls = new List<string>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                Ls.Add(dt.Rows[i]["COLUMN_NAME"].ToString());//["COLUMN_NAME"]);
            }
            return Ls.ToArray();
        }
    }
}