EchoTex_Payroll/HRM.BO/Common/ImportHelper.cs

using Ease.Core.Model;
using Ease.Core.Utility;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;
using System.Xml;

namespace HRM.BO
{
    #region Import Helper

    public sealed class ImportHelper
    {
        private DataSet _dataSet;
        private static char _delimiter;

        #region Constructor & Desctructor

        public ImportHelper()
        {
            _delimiter = '\0';
            _dataSet = new DataSet("ImportHelper");
        }

        ~ImportHelper()
        {
            _dataSet.Dispose();
            _dataSet = null;
            GC.Collect();
        }

        #endregion

        #region Formats Class

        public sealed class Formats
        {
            public readonly static ImportHelper.IFormatter Text = new TextFormatter();
            public readonly static ImportHelper.IFormatter Excel = new ExcelFormatter();

            private Formats()
            {
            }
        }

        #endregion

        #region IFormater Interface

        public interface IFormatter
        {
            /// <summary>
            /// Return the dataset after successful operation.
            /// </summary>
            /// <param name="fileSpec">Valid name of the file.</param>
            /// <param name="firstLineColumnHeader">Used the first line as header.</param>
            /// <returns>Dataset if successfu.</returns>
            DataSet Import(string fileSpec, bool firstLineColumnHeader);
            DataSet Import(System.IO.Stream fileStream, bool firstLineColumnHeader);
        }

        #endregion

        #region Excel Formatter Implementation

        private class ExcelFormatter : IFormatter
        {
            private DataSet _ds;
            public ExcelFormatter()
            {
                _ds = new DataSet("XlsReader");
            }

            public DataSet Import(string fileSpec, bool firstLineColumnHeader)
            {
                try
                {
                    _ds = GetDataSetFromFile(fileSpec, firstLineColumnHeader);
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message, e);
                }

                return _ds;
            }
            public DataSet Import(System.IO.Stream fileStream, bool firstLineColumnHeader)
            {
                try
                {
                    _ds = GetDataSetFromFile(fileStream, firstLineColumnHeader);
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message, e);
                }

                return _ds;
            }
        }

        public static DataSet GetDataSetFromFile(string fileSpec, bool firstLineColumnHeader)
        {
            try
            {
                DataSet ds = new DataSet("XlsReader");
                FileStream fs = new FileStream(fileSpec, FileMode.Open, FileAccess.Read, FileShare.Read);
                IWorkbook wb = WorkbookFactory.Create(fs);
                for (int shtIdx = 0; shtIdx < wb.NumberOfSheets; shtIdx++)
                    ds.Tables.Add(getTable(wb.GetSheetAt(shtIdx), firstLineColumnHeader));

                if (fs != null)
                {
                    fs.Close();
                    fs.Dispose();
                    fs = null;
                }
                wb = null;
                GC.Collect();

                return ds;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }
        }

        public static DataSet GetDataSetFromFile(System.IO.Stream fileStream, bool firstLineColumnHeader)
        {
            try
            {
                DataSet ds = new DataSet("XlsReader");
                IWorkbook wb = WorkbookFactory.Create(fileStream);
                for (int shtIdx = 0; shtIdx < wb.NumberOfSheets; shtIdx++)
                    ds.Tables.Add(getTable(wb.GetSheetAt(shtIdx), firstLineColumnHeader));

                wb = null;
                GC.Collect();

                return ds;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }
        }
        public static DataTable getTable(ISheet sheet, bool firstColIsHeader)
        {
            try
            {
                int startRow = sheet.FirstRowNum, idx = 0;
                short minCol = short.MaxValue, maxCol = 0;
                for (int rowIdx = startRow; rowIdx <= sheet.LastRowNum; rowIdx++)
                {
                    NPOI.SS.UserModel.IRow row = sheet.GetRow(rowIdx);
                    if (row == null)
                        continue;

                    maxCol = maxCol >= row.LastCellNum ? maxCol : row.LastCellNum;
                    minCol = (minCol >= 0 && minCol < row.FirstCellNum) ? minCol : row.FirstCellNum;
                }

                DataTable dt = new DataTable(sheet.SheetName);
                if (firstColIsHeader)
                {
                    IRow row = sheet.GetRow(startRow);
                    if (row != null)
                    {
                        for (int colIdx = minCol; colIdx < maxCol; colIdx++)
                        {
                            string value = row.GetCell(colIdx).StringCellValue;
                            dt.Columns.Add(value);
                        }
                    }
                    startRow++;
                }
                else
                {
                    idx = 0;
                    for (int colIdx = minCol; colIdx < maxCol; colIdx++)
                    {
                        idx++;
                        string value = string.Format("Column{0}", idx);
                        dt.Columns.Add(value);
                    }
                }

                for (int rowIdx = startRow; rowIdx <= sheet.LastRowNum; rowIdx++)
                {
                    DataRow newRow = dt.NewRow();
                    IRow row = sheet.GetRow(rowIdx);
                    if (row == null)
                        continue;

                    idx = -1;
                    for (int colIdx = minCol; colIdx < maxCol; colIdx++)
                    {
                        idx++;
                        ICell cell = row.GetCell(colIdx);
                        if (cell == null)
                            continue;

                        if (cell.CellType == CellType.Blank)
                            newRow[idx] = string.Empty;
                        else if (cell.CellType == CellType.Boolean)
                            newRow[idx] = cell.BooleanCellValue;
                        else if (cell.CellType == CellType.Error)
                            newRow[idx] = cell.ErrorCellValue;
                        else if (cell.CellType == CellType.Formula)
                            newRow[idx] = cell.CellFormula;
                        else if (cell.CellType == CellType.Numeric)
                        {
                            bool isDate = DateUtil.IsCellDateFormatted(cell);
                            if (isDate)
                                newRow[idx] = cell.DateCellValue;
                            else
                                newRow[idx] = cell.NumericCellValue;
                        }
                        else
                            newRow[idx] = cell.StringCellValue;
                    }
                    dt.Rows.Add(newRow);
                }

                return dt;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }
        }

        #endregion

        #region Text Formatter Implementation

        private class TextFormatter : IFormatter
        {
            private DataSet _ds;
            private List<object[]> _items;
            private readonly char CharQlfr = '"';

            public TextFormatter()
            {
                _ds = new DataSet("TextReader");
                _items = new List<object[]>();
            }

            private int EndColumnIndex { get; set; }
            private int StartColumnIndex { get; set; }

            void makeColumn(DataTable dt, bool hasHeader, object[] columns)
            {
                try
                {
                    this.StartColumnIndex = 0;
                    this.EndColumnIndex = columns.Length - 1;
                    if (hasHeader)
                    {
                        for (int i = 0; i <= this.EndColumnIndex; i++)
                        {
                            DataColumn dc = new DataColumn(columns[i].ToString());
                            dc.ReadOnly = true;
                            dc.DataType = typeof(string);
                            dt.Columns.Add(dc);
                        }
                    }
                    else
                    {
                        for (int i = 0; i <= this.EndColumnIndex; i++)
                        {
                            DataColumn dc = new DataColumn(string.Format("Col{0}", i));
                            dc.ReadOnly = true;
                            dc.DataType = typeof(string);
                            dt.Columns.Add(dc);
                        }
                    }
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message, e);
                }
            }

            public DataSet Import(string fileSpec, bool firstLineColumnHeader)
            {
                _items = new List<object[]>();
                try
                {
                    DataTable dt = _ds.Tables.Add();

                    //Read from file
                    using (StreamReader sr = new StreamReader(fileSpec))
                    {
                        #region New method

                        CsvParser parser = new CsvParser()
                        {
                            MaxColumnsToRead = 1000, Qualifier = CharQlfr, Delimiter = _delimiter,
                            TrimTrailingEmptyLines = true
                        };
                        _items = parser.Parse(sr);

                        //Make column header
                        if (_items.Count > 0)
                            this.makeColumn(dt, firstLineColumnHeader, _items[0]);

                        //Remove first line
                        if (firstLineColumnHeader)
                            _items.RemoveAt(0);

                        #endregion
                    }

                    //Adding in the dataset
                    foreach (object[] cols in _items)
                    {
                        DataRow row = dt.NewRow();
                        row.ItemArray = cols;
                        dt.Rows.Add(row);
                    }
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message, e);
                }

                return _ds;
            }

            public DataSet Import(System.IO.Stream filestream, bool firstLineColumnHeader)
            {
                _items = new List<object[]>();
                try
                {
                    DataTable dt = _ds.Tables.Add();

                    //Read from file
                    using (StreamReader sr = new StreamReader(filestream))
                    {
                        #region New method

                        CsvParser parser = new CsvParser()
                        {
                            MaxColumnsToRead = 1000,
                            Qualifier = CharQlfr,
                            Delimiter = _delimiter,
                            TrimTrailingEmptyLines = true
                        };
                        _items = parser.Parse(sr);

                        //Make column header
                        if (_items.Count > 0)
                            this.makeColumn(dt, firstLineColumnHeader, _items[0]);

                        //Remove first line
                        if (firstLineColumnHeader)
                            _items.RemoveAt(0);

                        #endregion
                    }

                    //Adding in the dataset
                    foreach (object[] cols in _items)
                    {
                        DataRow row = dt.NewRow();
                        row.ItemArray = cols;
                        dt.Rows.Add(row);
                    }
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message, e);
                }

                return _ds;
            }

            #region CsvParser

            public class CsvParser
            {
                #region Nested types

                private abstract class ParserState
                {
                    public static readonly LineStartState LineStartState = new LineStartState();
                    public static readonly ValueStartState ValueStartState = new ValueStartState();
                    public static readonly ValueState ValueState = new ValueState();
                    public static readonly QuotedValueState QuotedValueState = new QuotedValueState();
                    public static readonly QuoteState QuoteState = new QuoteState();

                    public abstract ParserState AnyChar(char ch, ParserContext context);
                    public abstract ParserState Comma(ParserContext context);
                    public abstract ParserState Quote(ParserContext context);
                    public abstract ParserState EndOfLine(ParserContext context);
                }

                private class LineStartState : ParserState
                {
                    public override ParserState AnyChar(char ch, ParserContext context)
                    {
                        context.AddChar(ch);
                        return ValueState;
                    }

                    public override ParserState Comma(ParserContext context)
                    {
                        context.AddValue();
                        return ValueStartState;
                    }

                    public override ParserState Quote(ParserContext context)
                    {
                        return QuotedValueState;
                    }

                    public override ParserState EndOfLine(ParserContext context)
                    {
                        context.AddLine();
                        return LineStartState;
                    }
                }

                private class ValueStartState : LineStartState
                {
                    public override ParserState EndOfLine(ParserContext context)
                    {
                        context.AddValue();
                        context.AddLine();
                        return LineStartState;
                    }
                }

                private class ValueState : ParserState
                {
                    public override ParserState AnyChar(char ch, ParserContext context)
                    {
                        context.AddChar(ch);
                        return ValueState;
                    }

                    public override ParserState Comma(ParserContext context)
                    {
                        context.AddValue();
                        return ValueStartState;
                    }

                    public override ParserState Quote(ParserContext context)
                    {
                        context.AddChar(context.Qualifier);
                        return ValueState;
                    }

                    public override ParserState EndOfLine(ParserContext context)
                    {
                        context.AddValue();
                        context.AddLine();
                        return LineStartState;
                    }
                }

                private class QuotedValueState : ParserState
                {
                    public override ParserState AnyChar(char ch, ParserContext context)
                    {
                        context.AddChar(ch);
                        return QuotedValueState;
                    }

                    public override ParserState Comma(ParserContext context)
                    {
                        context.AddChar(context.Delimiter);
                        return QuotedValueState;
                    }

                    public override ParserState Quote(ParserContext context)
                    {
                        return QuoteState;
                    }

                    public override ParserState EndOfLine(ParserContext context)
                    {
                        context.AddChar('\r');
                        context.AddChar('\n');
                        return QuotedValueState;
                    }
                }

                private class QuoteState : ParserState
                {
                    public override ParserState AnyChar(char ch, ParserContext context)
                    {
                        context.AddChar(ch);
                        return QuotedValueState;
                    }

                    public override ParserState Comma(ParserContext context)
                    {
                        context.AddValue();
                        return ValueStartState;
                    }

                    public override ParserState Quote(ParserContext context)
                    {
                        context.AddChar(context.Qualifier);
                        return QuotedValueState;
                    }

                    public override ParserState EndOfLine(ParserContext context)
                    {
                        context.AddValue();
                        context.AddLine();
                        return LineStartState;
                    }
                }

                private class ParserContext
                {
                    private readonly List<object[]> _lines = new List<object[]>();
                    private readonly List<object> _currentLine = new List<object>();
                    private readonly StringBuilder _currentValue = new StringBuilder();

                    public ParserContext()
                    {
                        if (this.MaxColumnsToRead <= 0)
                            this.MaxColumnsToRead = 1000;
                    }

                    public int MaxColumnsToRead { get; set; }
                    public char Delimiter { get; set; }
                    public char Qualifier { get; set; }

                    public void AddChar(char ch)
                    {
                        _currentValue.Append(ch);
                    }

                    public void AddValue()
                    {
                        if (_currentLine.Count < MaxColumnsToRead)
                            _currentLine.Add(_currentValue.ToString());

                        _currentValue.Remove(0, _currentValue.Length);
                    }

                    public void AddLine()
                    {
                        _lines.Add(_currentLine.ToArray());
                        _currentLine.Clear();
                    }

                    public List<object[]> GetAllLines()
                    {
                        if (_currentValue.Length > 0)
                        {
                            AddValue();
                        }

                        if (_currentLine.Count > 0)
                        {
                            AddLine();
                        }

                        return _lines;
                    }
                }

                #endregion

                public int MaxColumnsToRead { get; set; }
                public char Delimiter { get; set; }
                public char Qualifier { get; set; }
                public bool TrimTrailingEmptyLines { get; set; }

                public List<object[]> Parse(TextReader reader)
                {
                    string next;
                    var context = new ParserContext
                        { Delimiter = Delimiter, Qualifier = Qualifier, MaxColumnsToRead = MaxColumnsToRead };
                    ParserState currentState = ParserState.LineStartState;
                    while ((next = reader.ReadLine()) != null)
                    {
                        foreach (char ch in next)
                        {
                            if (ch == Delimiter)
                                currentState = currentState.Comma(context);
                            else if (ch == Qualifier)
                                currentState = currentState.Quote(context);
                            else
                                currentState = currentState.AnyChar(ch, context);
                        }

                        currentState = currentState.EndOfLine(context);
                    }

                    List<object[]> allLines = context.GetAllLines();
                    if (TrimTrailingEmptyLines && allLines.Count > 0)
                    {
                        bool isEmpty = true;
                        for (int i = allLines.Count - 1; i >= 0; i--)
                        {
                            //ReSharper disable RedundantAssignment
                            isEmpty = true;

                            //ReSharper restore RedundantAssignment
                            for (int j = 0; j < allLines[i].Length; j++)
                            {
                                if (allLines[i][j] != null && !string.IsNullOrEmpty(allLines[i][j].ToString()))
                                {
                                    isEmpty = false;
                                    break;
                                }
                            }

                            if (!isEmpty)
                            {
                                if (i < allLines.Count - 1)
                                    allLines.RemoveRange(i + 1, allLines.Count - i - 1);
                                break;
                            }
                        }

                        if (isEmpty)
                            allLines.RemoveRange(0, allLines.Count);
                    }

                    return allLines;
                }
            }

            #endregion
        }

        #endregion

        #region Properties

        public char Delimiter
        {
            get { return _delimiter; }
            set { _delimiter = value; }
        }

        internal static IWriterService Service
        {
            get { return Services.Factory.CreateService<IWriterService>(typeof(IWriterService)); }
        }

        #endregion

        #region Functions

        public DataSet Import(IFormatter formatter, string fileSpec)
        {
            if (formatter == null)
                throw new ArgumentException("Need to specify a formatter", "formatter");

            try
            {
                return this.Import(formatter, fileSpec, false);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }
        }

        public DataSet Import(IFormatter formatter, string fileSpec, bool firstLineColumnHeader)
        {
            if (formatter == null)
                throw new ArgumentException("Need to specify a formatter", "formatter");

            if (fileSpec.Length <= 0)
                throw new Exception("Provide a valid file name to read.");

            if (formatter == Formats.Text)
            {
                if (_delimiter == '\0')
                    throw new Exception("Provide a field delimiter of the file.");
            }

            if (!File.Exists(fileSpec))
                throw new Exception(string.Format("File: '{0}' does not exist.", fileSpec));

            try
            {
                _dataSet = formatter.Import(fileSpec, firstLineColumnHeader);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }

            return _dataSet;
        }

        public DataSet Import(IFormatter formatter, System.IO.Stream fileStream, bool firstLineColumnHeader)
        {
            if (formatter == null)
                throw new ArgumentException("Need to specify a formatter", "formatter");

            if (fileStream.Length <= 0)
                throw new Exception("Provide a valid file name to read.");

            if (formatter == Formats.Text)
            {
                if (_delimiter == '\0')
                    throw new Exception("Provide a field delimiter of the file.");
            }


            try
            {
                _dataSet = formatter.Import(fileStream, firstLineColumnHeader);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }

            return _dataSet;
        }

        /// <summary>
        /// Write to database in bulk write process.
        /// </summary>
        /// <param name="destTable">Valid database table to bulk write.</param>
        public void BulkWrite(string destTable)
        {
            try
            {
                if (string.IsNullOrEmpty(destTable))
                    throw new Exception("Provide valid table name to save data.");

                if (_dataSet == null || _dataSet.Tables.Count <= 0)
                    throw new Exception("There is no data to save.");

                Service.BulkWrite(destTable, _dataSet);
            }
            catch (ServiceException e)
            {
                throw new ServiceException(e.Message, e);
            }
        }

        /// <summary>
        /// Write to database in bulk write process.
        /// </summary>
        /// <param name="destTable">Valid database table to bulk write.</param>
        /// <param name="tableIndex">Source table index for process.</param>
        public void BulkWrite(string destTable, string srcDataTable)
        {
            try
            {
                if (string.IsNullOrEmpty(destTable))
                    throw new Exception("Provide valid table name to save data.");

                if (_dataSet == null || _dataSet.Tables.Count <= 0)
                    throw new Exception("There is no data to save.");

                if (!_dataSet.Tables.Contains(srcDataTable))
                    throw new Exception(string.Format("Table '{0}' does not belongs to dataset.", srcDataTable));

                Service.BulkWrite(destTable, _dataSet.Tables[srcDataTable]);
            }
            catch (ServiceException e)
            {
                throw new ServiceException(e.Message, e);
            }
        }

        /// <summary>
        /// Write to database in bulk write process.
        /// </summary>
        /// <param name="destTable">Valid database table to bulk write.</param>
        /// <param name="srcDataTable">Source table name for process.</param>
        public void BulkWrite(string destTable, int tableIndex)
        {
            try
            {
                if (string.IsNullOrEmpty(destTable))
                    throw new Exception("Provide valid table name to save data.");

                if (_dataSet == null || _dataSet.Tables.Count <= 0)
                    throw new Exception("There is no data to save.");

                if (tableIndex >= _dataSet.Tables.Count)
                    throw new Exception(string.Format("Table index '{0}' does not belongs to dataset.", tableIndex));

                Service.BulkWrite(destTable, _dataSet.Tables[tableIndex]);
            }
            catch (ServiceException e)
            {
                throw new ServiceException(e.Message, e);
            }
        }

        //void createObject(ObjectTemplate item, ImportConfig.ImportConfigItem setting, int auUserID, DateTime auTime, RowReader rr)
        //{
        //    try
        //    {
        //        PropertyInfo[] props = item.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public | BindingFlags.SetProperty);
        //        foreach (PropertyInfo prop in props)
        //        {
        //            if (prop.Name.ToLower().Equals("ukattribute") && !string.IsNullOrEmpty(setting.UKAttributes))
        //            {
        //                string value = string.Empty;
        //                string[] tokens = setting.UKAttributes.Split(new char[] { '~' });
        //                if (tokens.Length != 3)
        //                    throw new Exception("Unique ckecking is not in correct format at attribute: 'UKAttribute'");

        //                string[] flds = tokens[2].Split(new char[] { ',' });
        //                foreach (string fld in flds)
        //                {
        //                    string[] fas = fld.Split(new char[] { ':' });
        //                    if (fas.Length != 3)
        //                        throw new Exception("Field attribute is not in correct format.");

        //                    value += value.Length > 0 ? " AND " : string.Empty;
        //                    if (fas[2].ToLower().Equals("n"))
        //                        value += string.Format("{0}={1}", fas[0], rr.GetInt64(fas[1]).Value);
        //                    else if (fas[2].ToLower().Equals("d"))
        //                        value += string.Format("{0}='{1}'", fas[0], rr.GetDateTime(fas[1]).Value.ToString("dd MMM yyyy"));
        //                    else
        //                        value += string.Format("{0}='{1}'", fas[0], rr.GetString(fas[1], string.Empty).Trim().Replace("'", "''"));
        //                }
        //                value = string.Format("{0}~{1}~{2}", tokens[0], tokens[1], value);
        //                prop.SetValue(item, value, null);

        //                //Release objects
        //                flds = null;
        //                tokens = null;
        //                value = string.Empty;
        //            }

        //            ImportConfig.ImportConfigItem.FieldAttribute fldAtrb = setting.FieldsAttributes.Find(delegate(ImportConfig.ImportConfigItem.FieldAttribute fa) { return fa.PropertyName.ToLower().Equals(prop.Name.ToLower()); });
        //            if (fldAtrb == null)
        //                continue;

        //            if (fldAtrb.PropertyName.ToLower().Equals("createdby"))
        //                prop.SetValue(item, auUserID.Integer, null);
        //            else if (fldAtrb.PropertyName.ToLower().Equals("createddate"))
        //                prop.SetValue(item, auTime, null);
        //            else if (fldAtrb.PropertyName.ToLower().Equals("modifiedby"))
        //                prop.SetValue(item, auUserID.Integer, null);
        //            else if (fldAtrb.PropertyName.ToLower().Equals("modifieddate"))
        //                prop.SetValue(item, auTime, null);
        //            else if (prop.PropertyType.IsEnum)
        //                prop.SetValue(item, rr.GetInt16(fldAtrb.Name).Value, null);
        //            else if (prop.PropertyType == typeof(ID))
        //            {
        //                int id = null;
        //                object value = rr.GetString(fldAtrb.Name);
        //                if (value != null && value.ToString().Length > 0 && Global.NumericFunctions.IsNumeric(value))
        //                    id = (Convert.ToInt32(value));
        //                prop.SetValue(item, id, null);
        //            }
        //            else if (prop.PropertyType == typeof(bool) || prop.PropertyType == typeof(bool?))
        //                prop.SetValue(item, rr.GetBoolean(fldAtrb.Name), null);
        //            else if (prop.PropertyType == typeof(byte) || prop.PropertyType == typeof(byte?))
        //                prop.SetValue(item, rr.GetByte(fldAtrb.Name), null);
        //            else if (prop.PropertyType == typeof(short) || prop.PropertyType == typeof(short?))
        //                prop.SetValue(item, rr.GetInt16(fldAtrb.Name), null);
        //            else if (prop.PropertyType == typeof(int) || prop.PropertyType == typeof(int?))
        //                prop.SetValue(item, rr.GetInt32(fldAtrb.Name), null);
        //            else if (prop.PropertyType == typeof(Int64) || prop.PropertyType == typeof(Int64?))
        //                prop.SetValue(item, rr.GetInt64(fldAtrb.Name), null);
        //            else if (prop.PropertyType == typeof(double) || prop.PropertyType == typeof(double?))
        //                prop.SetValue(item, rr.GetDouble(fldAtrb.Name), null);
        //            else if (prop.PropertyType == typeof(float) || prop.PropertyType == typeof(float?))
        //                prop.SetValue(item, rr.GetFloat(fldAtrb.Name), null);
        //            else if (prop.PropertyType == typeof(decimal) || prop.PropertyType == typeof(decimal?))
        //                prop.SetValue(item, rr.GetDecimal(fldAtrb.Name), null);
        //            else if (prop.PropertyType == typeof(DateTime) || prop.PropertyType == typeof(DateTime?))
        //            {
        //                object val = rr.GetDateTime(fldAtrb.Name);
        //                if (val != null && val != DBNull.Value && val.ToString().Length > 4)
        //                {
        //                    DateTime date = DateTime.Now;
        //                    if (DateTime.TryParse(val.ToString(), out date))
        //                        prop.SetValue(item, date, null);
        //                    else
        //                        throw new Exception("Date is not in correct format.");
        //                }
        //            }
        //            else if (prop.PropertyType == typeof(char) || prop.PropertyType == typeof(char?))
        //                prop.SetValue(item, rr.GetChar(fldAtrb.Name), null);
        //            else
        //                prop.SetValue(item, rr.GetString(fldAtrb.Name, string.Empty).Trim(), null);
        //        }
        //    }
        //    catch (Exception e)
        //    {
        //        throw new Exception(e.Message, e);
        //    }
        //}

        ////public void InvokeMethod(int processID, DataTable srcData, int auUserID, DateTime auTime, StatusEnum statusFirst)
        ////{
        ////    try
        ////    {
        ////        List<ImportConfig.ImportConfigItem> settings = ImportConfig.Settings;
        ////        if (settings == null || settings.Count <= 0)
        ////            throw new InvalidOperationException("There is no settings for import process.");

        ////        ImportConfig.ImportConfigItem impSetting = settings.Find(delegate(ImportConfig.ImportConfigItem ic) { return ic.ProcessID == processID; });
        ////        if (impSetting == null)
        ////            throw new InvalidOperationException(string.Format("There is no settings for process: '{0}'", processID));

        ////        if (string.IsNullOrEmpty(impSetting.AssemblyName))
        ////            throw new Exception("Provide valid assembly name.");

        ////        if (string.IsNullOrEmpty(impSetting.ClassName))
        ////            throw new Exception("Provide valid class name.");

        ////        if (srcData == null || srcData.Rows.Count <= 0)
        ////            throw new Exception("There is no data to save.");

        ////        List<ObjectTemplate> items = new List<ObjectTemplate>();
        ////        foreach (DataRow row in srcData.Rows)
        ////        {
        ////            ObjectTemplate item = ObjectUtility.CreateInstance(impSetting.AssemblyName, impSetting.ClassName, null) as ObjectTemplate;
        ////            this.createObject(item, impSetting, auUserID, auTime, new RowReader(row));
        ////            items.Add(item);
        ////        }

        ////        if (items.Count > 0)
        ////        {
        ////            ObjectTemplate otInvoker = ObjectUtility.CreateInstance(impSetting.AssemblyName, impSetting.ClassName, null) as ObjectTemplate;
        ////            if (otInvoker != null)
        ////                otInvoker.GetType().InvokeMember("Save", BindingFlags.Instance | BindingFlags.InvokeMethod | BindingFlags.Public | BindingFlags.NonPublic, null, otInvoker, new object[] { items, auUserID, statusFirst });

        ////            otInvoker = null;
        ////        }

        ////        //Release objects and Tells GC to collect
        ////        items = null;
        ////        srcData.Dispose();
        ////        srcData = null;
        ////        auUserID = null;
        ////        impSetting = null;
        ////        settings = null;
        ////        GC.Collect();
        ////    }
        ////    catch (Exception e)
        ////    {
        ////        if (e.InnerException != null)
        ////            throw new Exception(e.InnerException.Message, e.InnerException);
        ////        else
        ////            throw new Exception(e.Message, e);
        ////    }
        ////}

        #endregion
    }

    public interface IWriterService
    {
        void BulkWrite(string destinationTable, DataSet dataSet);
        void BulkWrite(string destinationTable, DataTable dataTable);
    }

    #endregion

    #region Assembly Loader

    //public class AssemblyLoader
    //{
    //    #region Constructor
    //    private AssemblyLoader() { }
    //    #endregion

    //    #region Functions
    //    //public static DataSet LoadAssembly()
    //    //{
    //    //    int objectID = 0;
    //    //    DataSet dtSet = new DataSet();

    //    //    DataTable dtObjects = new DataTable("Objects");
    //    //    DataTable dtProperties = new DataTable("Properties");
    //    //    CreateColumns(dtSet, dtObjects, dtProperties);

    //    //    string path = AppDomain.CurrentDomain.BaseDirectory;
    //    //    string[] asms = Directory.GetFiles(path, "*.dll");

    //    //    foreach (string asm in asms)
    //    //    {
    //    //        if (asm.ToLower().StartsWith("ease.core"))
    //    //            continue;

    //    //        string assemblyName = asm.Substring(asm.LastIndexOf(@"\") + 1);
    //    //        AddData(ref objectID, assemblyName, dtObjects, dtProperties);
    //    //    }
    //    //    return dtSet;
    //    //}

    //    //public static DataSet LoadAssembly(string assemblyName)
    //    //{
    //    //    int objectID = 0;
    //    //    DataSet dtSet = new DataSet();
    //    //    DataTable dtObjects = new DataTable("Objects");
    //    //    DataTable dtProperties = new DataTable("Properties");

    //    //    CreateColumns(dtSet, dtObjects, dtProperties);
    //    //    AddData(ref objectID, assemblyName, dtObjects, dtProperties);

    //    //    return dtSet;
    //    //}

    //    #endregion

    //    #region Helper Functions

    //    private static void CreateColumns(DataSet dtSet, DataTable dtObjects, DataTable dtProperties)
    //    {
    //        //Object List
    //        DataColumn column = new DataColumn("ObjectID", typeof(int));
    //        dtObjects.Columns.Add(column);

    //        column = new DataColumn("ObjectName", typeof(string));
    //        dtObjects.Columns.Add(column);

    //        column = new DataColumn("Properties", typeof(int));
    //        dtObjects.Columns.Add(column);

    //        column = new DataColumn("Methods", typeof(int));
    //        dtObjects.Columns.Add(column);

    //        //Property list
    //        column = new DataColumn("ObjectID", typeof(int));
    //        dtProperties.Columns.Add(column);

    //        column = new DataColumn("PropertyName");
    //        dtProperties.Columns.Add(column);

    //        column = new DataColumn("PropertyType");
    //        dtProperties.Columns.Add(column);

    //        column = new DataColumn("DataType");
    //        dtProperties.Columns.Add(column);

    //        column = new DataColumn("AllowNull", typeof(bool));
    //        dtProperties.Columns.Add(column);

    //        column = new DataColumn("RefObject");
    //        dtProperties.Columns.Add(column);

    //        dtSet.Tables.Add(dtObjects);
    //        dtSet.Tables.Add(dtProperties);
    //    }

    //    //    private static void AddData(ref int objectID, string assemblyName, DataTable dtObjects, DataTable dtProperties)
    //    //    {
    //    //        Assembly assembly = null;
    //    //        if (assemblyName.ToLower().EndsWith(".exe") || assemblyName.ToLower().EndsWith(".dll"))
    //    //            assemblyName = assemblyName.Substring(0, assemblyName.Length - 4);

    //    //        try
    //    //        {
    //    //            assembly = Assembly.Load(assemblyName);
    //    //        }
    //    //        catch (Exception e)
    //    //        {
    //    //            throw new Exception("The assembly specified in SrviceFactory setting could not be Loaded." + e.Message);
    //    //        }

    //    //        foreach (Type type in assembly.GetTypes())
    //    //        {
    //    //            string objectName = type.Name;
    //    //            if (type.IsInterface || type.IsEnum)
    //    //                continue;

    //    //            objectID++;

    //    //            PropertyInfo[] props = type.GetProperties(BindingFlags.Public | BindingFlags.Instance);
    //    //            MethodInfo[] methods = type.GetMethods(BindingFlags.Static | BindingFlags.NonPublic | BindingFlags.Public);

    //    //            DataRow rowObj = dtObjects.NewRow();
    //    //            rowObj["ObjectID"] = objectID;
    //    //            rowObj["ObjectName"] = objectName;
    //    //            rowObj["Properties"] = props.Length;
    //    //            rowObj["Methods"] = methods.Length;
    //    //            dtObjects.Rows.Add(rowObj);

    //    //            foreach (PropertyInfo pInfo in props)
    //    //            {
    //    //                DataRow row = dtProperties.NewRow();

    //    //                row["ObjectID"] = objectID;
    //    //                row["PropertyName"] = pInfo.Name;
    //    //                row["PropertyType"] = "Public";
    //    //                row["DataType"] = pInfo.PropertyType.Name;
    //    //                row["AllowNull"] = false;
    //    //                if (pInfo.PropertyType.IsSubclassOf(typeof(ObjectTemplate)))
    //    //                    row["RefObject"] = pInfo.PropertyType.Name;
    //    //                else
    //    //                    row["RefObject"] = "";

    //    //                dtProperties.Rows.Add(row);
    //    //            }
    //    //        }
    //    //    }

    //    //    private static string GetString(Array src)
    //    //    {
    //    //        string ret = string.Empty;
    //    //        foreach (object o in src)
    //    //        {
    //    //            ret += ret.Length > 0 ? ", " : "";
    //    //            ret += o.ToString();
    //    //        }
    //    //        return ret;
    //    //    }

    //    //    #endregion
    //    //}

    //    #endregion

    //    #region Configuration

    //    public class ImportConfig : ConfigElementTemplate<ImportConfig>
    //    {
    //        private static List<ImportConfigItem> _settings;

    //        protected override void Parse(XmlElement node)
    //        {
    //            _settings = base.ReadChildren<ImportConfigItem>(node, "setting");
    //        }

    //        public static List<ImportConfigItem> Settings
    //        {
    //            get
    //            {
    //                ImportConfig.GetConfig("importSettings");

    //                return _settings;
    //            }
    //        }


    //        public class ImportConfigItem : ConfigChildElementTemplate<ImportConfigItem, ImportConfig>
    //        {
    //            #region properties

    //            private int _processID;
    //            public int ProcessID
    //            {
    //                get { return _processID; }
    //            }

    //            private string _asmName;
    //            public string AssemblyName
    //            {
    //                get { return _asmName; }
    //            }

    //            private string _className;
    //            public string ClassName
    //            {
    //                get { return _className; }
    //            }

    //            private string _propsName;
    //            public string PropertiesName
    //            {
    //                get { return _propsName; }
    //            }

    //            private string _ukAttribs;
    //            public string UKAttributes
    //            {
    //                get { return _ukAttribs; }
    //            }

    //            private string _fldsName;
    //            public string FieldsName
    //            {
    //                get { return _fldsName; }
    //            }

    //            private string _fldsType;
    //            public string FieldsType
    //            {
    //                get { return _fldsType; }
    //            }

    //            private string _fldsLen;
    //            public string FieldsLength
    //            {
    //                get { return _fldsLen; }
    //            }

    //            private string _fldsRemark;
    //            public string FieldsRemark
    //            {
    //                get { return _fldsRemark; }
    //            }

    //            #region Fields

    //            public class FieldAttribute
    //            {
    //                public FieldAttribute()
    //                {
    //                    _visible = true;
    //                    _slNo = string.Empty;
    //                    _name = string.Empty;
    //                    _type = string.Empty;
    //                    _maxLen = string.Empty;
    //                    _remarks = string.Empty;
    //                    _propName = string.Empty;
    //                }

    //                private string _slNo;
    //                public string SerailNo
    //                {
    //                    get { return _slNo; }
    //                    set { _slNo = value; }
    //                }

    //                private string _propName;
    //                public string PropertyName
    //                {
    //                    get { return _propName; }
    //                    set { _propName = value; }
    //                }
    //                private string _name;
    //                public string Name
    //                {
    //                    get { return _name; }
    //                    set { _name = value; }
    //                }

    //                private string _type;
    //                public string DataType
    //                {
    //                    get { return _type; }
    //                    set { _type = value; }
    //                }

    //                private string _maxLen;
    //                public string MaxLength
    //                {
    //                    get { return _maxLen; }
    //                    set { _maxLen = value; }
    //                }

    //                private string _remarks;
    //                public string Remarks
    //                {
    //                    get { return _remarks; }
    //                    set { _remarks = value; }
    //                }

    //                private bool _visible;
    //                public bool Visible
    //                {
    //                    get { return _visible; }
    //                    set { _visible = value; }
    //                }
    //            }

    //            private List<FieldAttribute> _fldAttribs;
    //            public List<FieldAttribute> FieldsAttributes
    //            {
    //                get { return _fldAttribs; }
    //            }

    //            /// <summary>
    //            /// Return no of items according to criteria
    //            /// </summary>
    //            /// <param name="visibility">0=All, 1=Visible only, 2=Invisible only</param>
    //            /// <returns>Return Count</returns>
    //            public int Count(short visibility)
    //            {
    //                if (_fldAttribs == null)
    //                    return 0;

    //                int count = 0;
    //                if (visibility == 1)
    //                {
    //                    List<FieldAttribute> filteredItems = _fldAttribs.FindAll(delegate (FieldAttribute fa) { return fa.Visible == true; });
    //                    if (filteredItems != null)
    //                        count = filteredItems.Count;

    //                    filteredItems = null;
    //                }
    //                else if (visibility == 2)
    //                {
    //                    List<FieldAttribute> filteredItems = _fldAttribs.FindAll(delegate (FieldAttribute fa) { return fa.Visible == false; });
    //                    if (filteredItems != null)
    //                        count = filteredItems.Count;

    //                    filteredItems = null;
    //                }
    //                else
    //                    count = _fldAttribs.Count;

    //                return count;
    //            }

    //            void parseField()
    //            {
    //                _fldAttribs = new List<FieldAttribute>();

    //                if (string.IsNullOrEmpty(_propsName))
    //                    throw new InvalidDataException("There is no property list.");

    //                if (string.IsNullOrEmpty(_fldsName))
    //                    throw new InvalidDataException("There is no field list.");

    //                if (string.IsNullOrEmpty(_fldsType))
    //                    throw new InvalidDataException("There is no data type of fileds.");

    //                if (string.IsNullOrEmpty(_fldsLen))
    //                    throw new InvalidDataException("There is no maximum length of field.");

    //                if (string.IsNullOrEmpty(_fldsRemark))
    //                    throw new InvalidDataException("There is no remarks of field");

    //                //Properties name
    //                List<string> props = new List<string>();
    //                props.AddRange(_propsName.Split(new char[] { ',' }));

    //                //Fields name
    //                List<string> flds = new List<string>();
    //                flds.AddRange(_fldsName.Split(new char[] { ',' }));

    //                //Fields type
    //                List<string> fldsType = new List<string>();
    //                fldsType.AddRange(_fldsType.Split(new char[] { ',' }));

    //                //Fields len
    //                List<string> fldslen = new List<string>();
    //                fldslen.AddRange(_fldsLen.Split(new char[] { ',' }));

    //                //Fields remarks
    //                List<string> fldsRemark = new List<string>();
    //                fldsRemark.AddRange(_fldsRemark.Split(new char[] { ',' }));

    //                //Do process
    //                int idx = 0, slNo = 0;
    //                foreach (string prop in props)
    //                {
    //                    FieldAttribute item = new FieldAttribute();
    //                    item.PropertyName = prop;
    //                    item.SerailNo = slNo.ToString();

    //                    if (idx < flds.Count)
    //                    {
    //                        string[] va = flds[idx].Split(new char[] { ':' });
    //                        item.Name = va[0];
    //                        if (va.Length == 2 && va[1].Equals("0"))
    //                            item.Visible = false;
    //                        else
    //                            slNo++;
    //                    }
    //                    item.SerailNo = slNo.ToString();
    //                    if (idx < fldsType.Count)
    //                        item.DataType = fldsType[idx];

    //                    if (idx < fldslen.Count)
    //                        item.MaxLength = fldslen[idx];

    //                    if (idx < fldsRemark.Count)
    //                        item.Remarks = fldsRemark[idx];


    //                    _fldAttribs.Add(item);
    //                    idx++;
    //                }

    //                //Release objects
    //                flds = null;
    //                props = null;
    //                fldslen = null;
    //                fldsType = null;
    //                fldsRemark = null;
    //                GC.Collect();
    //            }

    //            #endregion

    //            #endregion

    //            protected override void Parse(XmlElement node)
    //            {
    //                int.TryParse(node.GetAttribute("processID"), out _processID);
    //                _asmName = node.GetAttribute("asmName");
    //                _className = node.GetAttribute("className");
    //                _propsName = node.GetAttribute("propsName");
    //                _ukAttribs = node.GetAttribute("ukAttribs");
    //                _fldsName = node.GetAttribute("fldsName");
    //                _fldsType = node.GetAttribute("fldsType");
    //                _fldsLen = node.GetAttribute("fldsLen");
    //                _fldsRemark = node.GetAttribute("fldsRemark");

    //                this.parseField();
    //            }
    //        }
    //    }

    //    #endregion
    //}
}

#endregion