SqlHelper

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.Data.OracleClient;
using System.Data.SqlClient;
using Sybase.Data.AseClient;
using JustinCodeHelper.Model.Enum;
using System.Configuration;

namespace JustinCodeHelper
{
    public class SqlHelper
    {
        public IDbConnection Connection { get; set; }

        #region ***** Private Field *****

        public IDbTransaction Transaction;

        private IDbCommand cmd;

        private Dictionary<SqlProvider, Func<IDbConnection>> dicConnection;

        private Dictionary<string, Func<IDbCommand>> dicCmd;

        private Dictionary<string, Func<DbDataAdapter>> dicDa;
        #endregion

        #region ***** 建構子 *****
        public SqlHelper(SqlProvider sqlType, string connectionString)
        {
            dicConnection = new Dictionary<SqlProvider, Func<IDbConnection>>();
            dicCmd = new Dictionary<string, Func<IDbCommand>>();
            dicDa = new Dictionary<string, Func<DbDataAdapter>>();

            dicConnection.Add(SqlProvider.SqlConnection, () => new SqlConnection());
            dicConnection.Add(SqlProvider.OleDb, () => new OleDbConnection());
            dicConnection.Add(SqlProvider.OracleClient, () => new OracleConnection());
            dicConnection.Add(SqlProvider.AseClient, () => new AseConnection());


            dicCmd.Add(typeof(SqlConnection).Name, () => new SqlCommand());
            dicCmd.Add(typeof(OleDbConnection).Name, () => new OleDbCommand());
            dicCmd.Add(typeof(OracleConnection).Name, () => new OracleCommand());
            dicCmd.Add(typeof(AseConnection).Name, () => new AseCommand());


            dicDa.Add(typeof(SqlConnection).Name, () => new SqlDataAdapter());
            dicDa.Add(typeof(OleDbConnection).Name, () => new OleDbDataAdapter());
            dicDa.Add(typeof(OracleConnection).Name, () => new OracleDataAdapter());
            dicDa.Add(typeof(AseConnection).Name, () => new AseDataAdapter());


            this.Connection = DecisionConnection(sqlType);
            this.Connection.ConnectionString = connectionString;
            this.cmd = DecisionCommand();

        }
        #endregion

        public static string GetConnectionStringByName(string name)
        {
            return ConfigurationManager.ConnectionStrings[name].ConnectionString;
        }

        #region ***** 資料庫連線開啟、關閉、釋放 *****
        public void Open()
        {
            this.Connection.Open();
        }

        public void Close()
        {
            this.Connection.Close();
        }

        public void Dispose()
        {
            this.Connection.Dispose();
        }
        #endregion

        public IDbTransaction BeginTransaction()
        {
            this.cmd = DecisionCommand();
            this.cmd.Transaction = this.Connection.BeginTransaction();
            Transaction = this.cmd.Transaction ;
            return Transaction;
        }

        public void TransactionCommit()
        {
            this.Transaction.Commit();
        }

        public void TransactionRollback()
        {
            this.Transaction.Rollback();
        }

        #region ***** DataReader *****
        public IDataReader DataReader(string cmdText)
        {
            return DataReader(cmdText, null, CommandType.Text);
        }

        public IDataReader DataReader(string cmdText, DbParameter[] para)
        {
            return DataReader(cmdText, para, CommandType.Text);
        }

        public IDataReader DataReader(string cmdText, CommandType cmdType)
        {
            return DataReader(cmdText, null, cmdType);
        }

        public IDataReader DataReader(string cmdText, DbParameter[] parameters, CommandType cmdType)
        {
            IDataReader result = null;
            SetCommand(cmdText, parameters, cmdType);
            result = cmd.ExecuteReader();
            return result;
        }
        #endregion

        public object ExecuteScalar(string cmdText, DbParameter[] parameters, CommandType cmdType)
        {
            object result = null;
            SetCommand(cmdText, parameters, cmdType);
            result = cmd.ExecuteScalar();
            return result;
        }

        #region ***** DataTable *****
        public DataTable DataTable(string cmdText)
        {
            return DataTable(cmdText, null, CommandType.Text);
        }

        public DataTable DataTable(string cmdText, DbParameter[] para)
        {
            return DataTable(cmdText, para, CommandType.Text);
        }

        public DataTable DataTable(string cmdText, CommandType cmdType)
        {
            return DataTable(cmdText, null, cmdType);
        }

        public DataTable DataTable(string cmdText, DbParameter[] parameters, CommandType cmdType)
        {
            DataTable result = new DataTable();
            DbDataAdapter da;
            AseCommand aseCommand;

            SetCommand(cmdText, parameters, cmdType);
            da = DecisionDataAdapter();
            if (this.Connection.GetType().Name == typeof(AseConnection).Name)
            {
                aseCommand = (AseCommand)cmd;
                (da as AseDataAdapter).SelectCommand = aseCommand;
            }
            else
            {
                da.SelectCommand = (DbCommand)cmd;
            }
            da.Fill(result);

            return result;
        }
        #endregion

        #region ***** ExecuteNonQuery *****
        public int ExecuteNonQuery(string cmdText)
        {
            return this.ExecuteNonQuery(cmdText, null, CommandType.Text);
        }

        public int ExecuteNonQuery(string cmdText, DbParameter[] para)
        {
            return this.ExecuteNonQuery(cmdText, para, CommandType.Text);
        }

        public int ExecuteNonQuery(string cmdText, CommandType cmdType)
        {
            return this.ExecuteNonQuery(cmdText, null, cmdType);
        }

        public int ExecuteNonQuery(string cmdText, DbParameter[] parameters, CommandType cmdType)
        {
            int result = 0;
            SetCommand(cmdText, parameters, cmdType);
            result = cmd.ExecuteNonQuery();
            return result;
        }
        #endregion

        public List<T> Query<T>(string cmdText, DbParameter[] parameters, CommandType cmdType)
        {
            IDataReader dr = this.DataReader(cmdText, parameters, cmdType);

            DataTable dt= this.DataTable(cmdText, parameters, cmdType);

            throw new NotImplementedException();
        }

        #region ***** Decision *****
        private IDbConnection DecisionConnection(SqlProvider sqlProvider)
        {
            foreach (var item in dicConnection)
            {
                if (sqlProvider == item.Key)
                    return item.Value.Invoke();
            }

            return null;
        }

        private IDbCommand DecisionCommand()
        {
            foreach (var item in dicCmd)
            {
                if (this.Connection.GetType().Name == item.Key)
                    return item.Value.Invoke();
            }

            return null;
        }

        private DbDataAdapter DecisionDataAdapter()
        {
            foreach (var item in dicDa)
            {
                if (this.Connection.GetType().Name == item.Key)
                    return item.Value.Invoke();
            }

            return null;
        }
        #endregion

        private void SetCommand(string cmdText, DbParameter[] parameters, CommandType cmdType)
        {
            this.cmd.Connection = this.Connection;
            this.cmd.CommandText = cmdText;
            SetParamer(this.cmd, parameters);
            this.cmd.CommandType = cmdType;
        }

        private void SetParamer(IDbCommand cmd, DbParameter[] parameters)
        {
            if (parameters == null) return;

            foreach (DbParameter item in parameters)
            {
                cmd.Parameters.Add(item);
            }
        }

    }
}