public static void CopyMatchProperties(this object sourceObject, object targetInstance,
                                           params string[] exceptions)
    {
        List <string> exceptionslower = exceptions.ToListSafely <string>();

        exceptionslower.ToLower();
        Type sourceType = sourceObject.GetType();
        Type targetType = targetInstance.GetType();

        PropertyInfo[]  sourceProps = sourceType.GetProperties();                //s prop
        PropertyInfo[]  targetProps = targetType.GetProperties();                //t prop
        ConstructorInfo construct   = targetType.GetConstructor(new Type[] { }); //target constructor

        foreach (PropertyInfo pi in sourceProps)
        {
            var exceptional = from i in exceptionslower
                              where i == pi.Name.ToLower()
                              select i;
            if (exceptional.Any())
            {
                continue;
            }
            var q = from p in targetProps
                    where p.Name == pi.Name
                    select p;
            if (q.Any())
            {
                var p = q.First <PropertyInfo>();
                try
                {
                    object valueToSet = (object)pi.GetValue(sourceObject, null);
                    p.SetValue(targetInstance, valueToSet, null);
                }
                catch (Exception error)
                {
                    try
                    {
                        object valueToSetErr  = pi.GetValue(sourceObject, null);
                        var    manualConvert  = new ManualConvert(valueToSetErr);
                        var    convertedValue = manualConvert.ConvertType(p.PropertyType);
                        p.SetValue(targetInstance, convertedValue, null);
                    }
                    catch {
                    }
                }
            }
        }
    }
Ejemplo n.º 2
0
        //Import full database from excel file
        //If sheet ends with .x that sheet will not be imported
        public List <string> BootstrapDatabaseFromExcelSheet(string filePath, ExcelVersion version)
        {
            var jsDeserializerForOptions = new JavaScriptSerializer();

            TruncateFullDatabase();

            List <string> listOfProcessedTables = new List <string>();

            var datasetFromExcel = new ExcelReader().GetDataSet(version, filePath, true);

            if (this.OnExcelDataSetRead != null)
            {
                this.OnExcelDataSetRead(datasetFromExcel);
            }

            var dataTableImporter = new DataImport(this.ConnectionString);

            foreach (DataTable currentTable in datasetFromExcel.Tables)
            {
                var tableOptionsObject = new ExcelTableOptions();

                #region Exclude table
                if (currentTable.TableName.ToLower().EndsWith(".x"))
                {
                    continue;
                }
                #endregion

                var currentTableName = currentTable.TableName;

                #region Read table options

                if (currentTableName.Contains("{") &&
                    currentTableName.Contains("}"))
                {
                    var tableOptionsText = currentTableName.Substring(currentTableName.IndexOf("{"));
                    //In table options JSON text, We use ^ instead of : and here we replace it
                    tableOptionsText      = tableOptionsText.Replace("^", ":");
                    tableOptionsObject    = jsDeserializerForOptions.Deserialize <ExcelTableOptions>(tableOptionsText);
                    currentTableName      = currentTableName.Substring(0, currentTableName.IndexOf("{"));
                    tableOptionsObject.Id = currentTableName;
                }
                else
                {
                    tableOptionsObject.Id = currentTable.TableName;
                }

                #endregion

                listOfProcessedTables.Add(currentTable.TableName);

                //event Pre Column Options Work
                if (this.OnExcelPreColumnOptionsWork != null)
                {
                    this.OnExcelPreColumnOptionsWork(currentTable, tableOptionsObject);
                }

                foreach (DataColumn currentColumn in currentTable.Columns)
                {
                    #region Column driven by options
                    if (currentColumn.ColumnName.Contains("{") &&
                        currentColumn.ColumnName.Contains("}"))
                    {
                        var columnOptionsText   = currentColumn.ColumnName.Substring(currentColumn.ColumnName.IndexOf("{"));
                        var columnOptionsObject = jsDeserializerForOptions.Deserialize <ExcelColumnOptions>(columnOptionsText);
                        var columnNameB4Options = currentColumn.ColumnName.Substring(0, currentColumn.ColumnName.IndexOf("{"));

                        #region Column data Type conversion In case of Type option present in column options

                        if (columnOptionsObject.Type.IsNotNullOrEmpty())
                        {
                            var targetDotNetType = Type.GetType(columnOptionsObject.Type);
                            foreach (DataRow row4DataTypeChange in currentTable.Rows)
                            {
                                var typeConverter4ValueInColumn = new ManualConvert(row4DataTypeChange[currentColumn.ColumnName]);
                                try
                                {
                                    //set value
                                    row4DataTypeChange[currentColumn.ColumnName] = typeConverter4ValueInColumn.ConvertType(columnOptionsObject.Type);
                                }
                                catch (Exception err)
                                {
                                    //Set Db null
                                    row4DataTypeChange[currentColumn.ColumnName] = DBNull.Value;
                                }
                            }
                        }
                        #endregion

                        #region Query for foreign keys or alternative values
                        if (columnOptionsObject.Query.IsNotNullOrEmpty())
                        {
                            var nonDistinctColumnValues = new List <string>();

                            foreach (DataRow row4NonDistinctValues in currentTable.Rows)
                            {
                                nonDistinctColumnValues.Add(row4NonDistinctValues[currentColumn.ColumnName].Text());
                            }

                            var distinctColumnValues = nonDistinctColumnValues.Distinct <string>().ToList <string>();

                            var sbDistinctColumValues = new StringBuilder();

                            var indexForeachOnDistinctValues = 0;

                            foreach (var distinctColumnValue in distinctColumnValues)
                            {
                                if (indexForeachOnDistinctValues > 0)
                                {
                                    sbDistinctColumValues.Append(",");
                                }

                                sbDistinctColumValues.Append("'" + distinctColumnValue + "'");

                                indexForeachOnDistinctValues++;
                            }

                            var pairsTableFromDB = GetDataTable(columnOptionsObject.Query.Replace("@Values", sbDistinctColumValues.ToString()));

                            //Insert non existing in db
                            if (columnOptionsObject.Insert.IsNotNullOrEmpty())
                            {
                                var listNonExistingItems = new List <string>();
                                foreach (DataRow rowInExcel in currentTable.Rows)
                                {
                                    var recordExists = false;
                                    foreach (DataRow rowInDBToReplace in pairsTableFromDB.Rows)
                                    {
                                        if (rowInExcel[currentColumn.ColumnName].Text() == rowInDBToReplace[0].Text())//first column matched
                                        {
                                            recordExists = true;
                                            break;
                                        }
                                    }
                                    if (!recordExists)
                                    {
                                        listNonExistingItems.Add(rowInExcel[currentColumn.ColumnName].Text());
                                    }
                                }

                                if (listNonExistingItems.Any())
                                {
                                    listNonExistingItems = listNonExistingItems.Distinct <string>().ToList <string>();

                                    foreach (var nonExistingValue in listNonExistingItems)
                                    {
                                        ExecuteQuery(columnOptionsObject.Insert, "@Value", nonExistingValue);
                                    }

                                    #region Fetch again

                                    indexForeachOnDistinctValues = 0;

                                    sbDistinctColumValues = new StringBuilder();

                                    foreach (var distinctColumnValue in distinctColumnValues)
                                    {
                                        if (indexForeachOnDistinctValues > 0)
                                        {
                                            sbDistinctColumValues.Append(",");
                                        }

                                        sbDistinctColumValues.Append("'" + distinctColumnValue + "'");

                                        indexForeachOnDistinctValues++;
                                    }

                                    pairsTableFromDB = GetDataTable(columnOptionsObject.Query.Replace("@Values", sbDistinctColumValues.ToString()));
                                    #endregion
                                }
                            }

                            foreach (DataRow rowInExcel in currentTable.Rows)
                            {
                                foreach (DataRow rowInDBToReplace in pairsTableFromDB.Rows)
                                {
                                    if (rowInExcel[currentColumn.ColumnName].Text() == rowInDBToReplace[0].Text()) //first column matched
                                    {
                                        rowInExcel[currentColumn.ColumnName] = rowInDBToReplace[1];                //second column replaces original value
                                        break;
                                    }
                                }
                            }
                        }
                        #endregion

                        //change column name if Options found for current column
                        currentColumn.ColumnName = columnNameB4Options;
                    }
                    #endregion
                }

                //event Pre import
                if (this.OnPreTableImport != null)
                {
                    this.OnPreTableImport(currentTable, tableOptionsObject, currentTableName);
                }

                currentTable.TableName = currentTableName;

                dataTableImporter.FromDataTable(currentTable, true);

                //Pre table import event
                if (this.OnPostTableImport != null)
                {
                    this.OnPostTableImport(currentTable, tableOptionsObject, currentTableName);
                }
            }

            //Last event Post import
            if (this.OnExcelFileImported != null)
            {
                this.OnExcelFileImported();
            }

            return(listOfProcessedTables);
        }