Transformalizing NorthWind

#region License
// /*
// See license included in this library folder.
// */
#endregion

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using Transformalize.Libs.FileHelpers.Enums;
using Transformalize.Libs.FileHelpers.ErrorHandling;
using Transformalize.Libs.FileHelpers.Helpers;

namespace Transformalize.Libs.FileHelpers.DataLink.Storage
{

    #region "  Delegates  "

    /// <summary>
    ///     Delegate used by the <see cref="DatabaseStorage" /> to get the SQL for the insert or update statement.
    /// </summary>
    /// <param name="record">The record to insert</param>
    /// <return>The Sql string to insert the record.</return>
    public delegate string InsertSqlHandler(object record);

    /// <summary>
    ///     Delegate used by the <see cref="DatabaseStorage" /> to fill the values of a new record from the db (you only need to assing hte values.
    /// </summary>
    /// <param name="record">The record to fill.</param>
    /// <param name="fieldValues">The values read from the database, you need to use these to fill the record.</param>
    public delegate void FillRecordHandler(object record, object[] fieldValues);

    #endregion

    /// <summary>
    ///     This class implements the <see cref="DataStorage" /> and is the base class for Data Base storages.
    /// </summary>
    [EditorBrowsable(EditorBrowsableState.Advanced)]
    public abstract class DatabaseStorage : DataStorage
    {
        #region Constructors

        /// <summary>Default constructor.</summary>
        /// <param name="recordType">The Record Type.</param>
        protected DatabaseStorage(Type recordType) : base(recordType)
        {
        }

        #endregion

        #region FillRecord

        /// <summary>This method recives the fields values as an array and must return a record object.</summary>
        /// <param name="fieldValues">The record fields values.</param>
        /// <returns>The record object.</returns>
        private object FillRecord(object[] fieldValues)
        {
            if (FillRecordCallback == null)
                throw new BadUsageException("You can�t extract records a null FillRecordCallback. Check the docs for help.");

            var res = mRecordInfo.CreateRecordObject();
            FillRecordCallback(res, fieldValues);
            return res;
        }

        #endregion

        #region SelectSql

        private string mSelectSql = string.Empty;

        /// <summary>Indicates the Sql statment used to get the records.</summary>
        public string SelectSql
        {
            get { return mSelectSql; }
            set { mSelectSql = value; }
        }

        /// <summary>Must return the Select Sql used to Fetch the records to Extract.</summary>
        /// <returns>The SQL statement.</returns>
        private string GetSelectSql()
        {
            if (mSelectSql == null || mSelectSql == string.Empty)
                throw new BadUsageException("The SelectSql property is empty, please set it before try to get the records.");

            return mSelectSql;
        }

        #endregion

        #region GetInsertSql

        /// <summary>Must return a SQL string with the insert statement for the records.</summary>
        /// <param name="record">The record to insert.</param>
        /// <returns>The Sql string to used to insert the record.</returns>
        private string GetInsertSql(object record)
        {
            if (mInsertSqlCallback == null)
                throw new BadUsageException("You can�t insert records with a null GetInsertSqlCallback. Check the docs for help.");

            return mInsertSqlCallback(record);
        }

        #endregion

        #region MustOverride Methods

        /// <summary>Must create an abstract connection object.</summary>
        /// <returns>An Abstract Connection Object.</returns>
        protected abstract IDbConnection CreateConnection();

        #endregion

        #region Connections

        private IDbConnection mConn;

        private void InitConnection()
        {
            if (mConn == null)
            {
                mConn = CreateConnection();
            }
        }

        #endregion

        #region "  SelectRecords  "

        /// <summary>Must Return the records from the DataSource (DB, Excel, etc)</summary>
        /// <returns>The extracted records.</returns>
        public override object[] ExtractRecords()
        {
            InitConnection();

            var res = new ArrayList();

            try
            {
                if (mConn.State != ConnectionState.Open)
                    mConn.Open();

                var command = mConn.CreateCommand();
                command.Connection = mConn;
                command.CommandText = GetSelectSql();

                var reader = command.ExecuteReader();

                object currentObj;
                var values = new object[reader.FieldCount];

                ProgressHelper.Notify(mNotifyHandler, mProgressMode, 0, -1);

                var recordNumber = 0;

                while (reader.Read())
                {
                    recordNumber++;
                    ProgressHelper.Notify(mNotifyHandler, mProgressMode, recordNumber, -1);


                    reader.GetValues(values);
                    currentObj = FillRecord(values);
                    res.Add(currentObj);
                }

                reader.Close();
            }
            finally
            {
                if (mConn.State != ConnectionState.Closed)
                    mConn.Close();
            }

            return (object[]) res.ToArray(RecordType);
        }

        #endregion

        #region ExecuteInBatch

        /// <summary>Indicates if the underlaying Conexion allow more than one instruction per execute.</summary>
        protected virtual bool ExecuteInBatch
        {
            get { return false; }
        }

        #endregion

        #region "  InsertRecords  "

        /// <summary>Must Insert the records in a DataSource (DB, Excel, etc)</summary>
        /// <param name="records">The records to insert.</param>
        public override void InsertRecords(object[] records)
        {
            IDbTransaction trans = null;

            try
            {
                InitConnection();

                if (mConn.State != ConnectionState.Open)
                    mConn.Open();

                var SQL = String.Empty;

                trans = InitTransaction(mConn);

                ProgressHelper.Notify(mNotifyHandler, mProgressMode, 0, records.Length);
                var recordNumber = 0;
                var batchCount = 0;

                foreach (var record in records)
                {
                    // Insert Logic Here, must check duplicates
                    recordNumber++;
                    batchCount++;
                    ProgressHelper.Notify(mNotifyHandler, mProgressMode, recordNumber, records.Length);

                    SQL += GetInsertSql(record) + " ";

                    if (ExecuteInBatch)
                    {
                        if (batchCount >= mExecuteInBatchSize)
                        {
                            ExecuteAndLeaveOpen(SQL);
                            SQL = String.Empty;
                            batchCount = 0;
                        }
                    }
                    else
                    {
                        ExecuteAndLeaveOpen(SQL);
                        SQL = String.Empty;
                    }
                }
                if (SQL != null && SQL.Length != 0)
                {
                    ExecuteAndLeaveOpen(SQL);
                    SQL = String.Empty;
                }

                CommitTransaction(trans);
            }
            catch
            {
                RollBackTransaction(trans);
                throw;
            }
            finally
            {
                try
                {
                    mConn.Close();
                    mConn.Dispose();
                    mConn = null;
                }
                catch
                {
                }
            }
        }

        #endregion

        #region "  ExecuteNonQuery (HelperMethods) "

        private int ExecuteAndLeaveOpen(string sql)
        {
            InitConnection();

            var command = mConn.CreateCommand();
            command.Connection = mConn;
            command.CommandText = sql;

            return command.ExecuteNonQuery();
        }

        private int ExecuteAndClose(string sql)
        {
            var res = -1;

            InitConnection();

            try
            {
                if (mConn.State != ConnectionState.Open)
                    mConn.Open();

                var command = mConn.CreateCommand();
                command.Connection = mConn;
                command.CommandText = sql;

                res = command.ExecuteNonQuery();
            }
            finally
            {
                if (mConn.State != ConnectionState.Closed)
                    mConn.Close();
            }

            return res;
        }

        #endregion

        #region InsertSqlCallback

        private InsertSqlHandler mInsertSqlCallback;

        /// <summary>Delegate used to get the SQL for the insert or update statement.</summary>
        public InsertSqlHandler InsertSqlCallback
        {
            get { return mInsertSqlCallback; }
            set { mInsertSqlCallback = value; }
        }

        #endregion

        #region FillRecordCallback

        /// <summary>Delegate used to fill the values of a new record from the db.</summary>
        public FillRecordHandler FillRecordCallback { get; set; }

        #endregion

        #region ExecuteInBatchSize

        private int mExecuteInBatchSize = 100;

        /// <summary>Indicates the max number of instruction of each execution. High numbers help reduce the round trips to the db and so help tp improve performance.</summary>
        public int ExecuteInBatchSize
        {
            get { return mExecuteInBatchSize; }
            set
            {
                if (value < 1)
                    throw new ArgumentException("ExecuteInBatchSize", "ExecuteInBatchSize must be >= 1");

                mExecuteInBatchSize = value;
            }
        }

        #endregion

        private string mConnectionString = String.Empty;
        private TransactionMode mTransactionMode = TransactionMode.NoTransaction;

        /// <summary>
        ///     Define the Transaction Level used when inserting records.
        /// </summary>
        public TransactionMode TransactionMode
        {
            get { return mTransactionMode; }
            set { mTransactionMode = value; }
        }

        /// <summary>
        ///     The connection string used for this storage.
        /// </summary>
        public string ConnectionString
        {
            get { return mConnectionString; }
            set { mConnectionString = value; }
        }

        private IDbTransaction InitTransaction(IDbConnection conn)
        {
            if (mTransactionMode == TransactionMode.NoTransaction) return null;

            switch (mTransactionMode)
            {
                case TransactionMode.UseDefault:
                    return conn.BeginTransaction();

                case TransactionMode.UseChaosLevel:
                    return conn.BeginTransaction(IsolationLevel.Chaos);

                case TransactionMode.UseReadCommitted:
                    return conn.BeginTransaction(IsolationLevel.ReadCommitted);

                case TransactionMode.UseReadUnCommitted:
                    return conn.BeginTransaction(IsolationLevel.ReadUncommitted);

                case TransactionMode.UseRepeatableRead:
                    return conn.BeginTransaction(IsolationLevel.RepeatableRead);

                case TransactionMode.UseSerializable:
                    return conn.BeginTransaction(IsolationLevel.Serializable);
            }

            return null;
        }

        private void CommitTransaction(IDbTransaction trans)
        {
            if (trans == null) return;
            trans.Commit();
        }

        private void RollBackTransaction(IDbTransaction trans)
        {
            if (trans == null) return;
            trans.Rollback();
        }
    }
}