private void UpdateDynamicProperties(ComponentTestingPropertyDataAdapter adapter, ControlSystemComponent matchingComponent, int i)
        {
            foreach (DynamicProperty dynamicProperty in adapter.DynamicProperties.Where(x => !string.IsNullOrEmpty(x.PropertyValue)))
            {
                ControlSystemComponentTestingProperty matchProperty = (from x in Cee.ControlSystemComponentTestingProperties where string.Compare(x.Name, dynamicProperty.PropertyName, true, CultureInfo.CurrentCulture) == 0 select x).FirstOrDefault();

                if (matchProperty == null)
                {
                    RaiseMessage(CommonUtils.MessageType.Error, string.Format(BuildItemNotFoundInDatabaseMessage("ControlSystemComponentTestingProperty", dynamicProperty.PropertyName, i + 1)));
                    continue;
                }

                //is this property assocaited with the comp type?
                ControlSystemComponentTypeTestingProperty matchCompTypeProperty = (from x in Cee.ControlSystemComponentTypeTestingProperties
                                                                            where x.TestPropertyId == matchProperty.Id && x.ComponentTypeId == matchingComponent.ControlSystemComponentTypeId
                                                                            select x).FirstOrDefault();

                if (matchCompTypeProperty == null)
                {
                    string controlSystemTypeName = (from x in Cee.ControlSystemComponentTypes where x.Id == matchingComponent.ControlSystemComponentTypeId select x.Name).FirstOrDefault();
                    string controlSystemPropertyName = (from x in Cee.ControlSystemTestingProperties where x.Id == matchProperty.Id select x.Name).FirstOrDefault();

                    string message = string.Format("Missing a entry in Database for the Table 'ControlSystemComponentTypeTestingProperty' Where ControlSystemComponentTyped = '{0}'({1}) And ControlSystemTestingPropertyId = '{2}' ({3}). Row {4}.",
                        matchingComponent.ControlSystemComponentTypeId, controlSystemTypeName, matchProperty.Id, controlSystemPropertyName, i);
                    RaiseMessage(CommonUtils.MessageType.Error, message);
                    continue;
                }

                ControlSystemComponentTestingPropertyValue matchPropertyValue = (from x in Cee.ControlSystemComponentTestingPropertyValues
                                                                       where (x.TestPropertyId == matchProperty.Id) && (x.ComponentId == matchingComponent.Id)
                                                                       select x).FirstOrDefault();

                if (dynamicProperty.PropertyValue.ToLower().Trim() == NULLTEXT)
                {
                    if (matchPropertyValue == null)
                    {
                        //do nothing
                        continue;
                    }

                    //delete
                    Cee.ControlSystemComponentTestingPropertyValues.Remove(matchPropertyValue);
                }
                else
                {
                    if (PropertyValueToPropertyTypeMismatched(matchProperty.PropertyListId, matchProperty.Type, dynamicProperty, i + 1))
                    {
                        continue;
                    }

                    if (matchPropertyValue == null)
                    {
                        //create
                        matchPropertyValue = new ControlSystemComponentTestingPropertyValue
                        {
                            TestPropertyId = matchProperty.Id,
                            ComponentId = matchingComponent.Id,
                            Value = dynamicProperty.PropertyValue,
                            VerifiedUserDate = string.Format("{0} by {1}", DateTime.Now.ToString(@"dd/MM/yyyy hh:mm"), mUser.FirstLastName)
                        };
                        Cee.ControlSystemComponentTestingPropertyValues.Add(matchPropertyValue);
                    }
                    else
                    {
                        //update
                        matchPropertyValue.Value = dynamicProperty.PropertyValue;
                        matchPropertyValue.VerifiedUserDate = string.Format("{0} by {1}", DateTime.Now.ToString(@"dd/MM/yyyy hh:mm"), mUser.FirstLastName);
                    }
                }

                var pair = new PropertyNameComponentNamePair
                {
                    ComponentName = matchingComponent.Name,
                    PropertyName = matchProperty.Name,
                    Value = dynamicProperty.PropertyValue
                };

                mSavedResults.Add(pair);
            }
        }
        public override DbImportResult Import(bool canCreateProperties = false)
        {
            DbImportResult = new DbImportResult();

            if (MetaData.ImportType != CommonUtils.ImportType.CreateComponentTestingProperties && MetaData.ImportType != CommonUtils.ImportType.UpdateComponentTestingProperties)
            {
                DbImportResult.ErrorMessages.Add(IMPORT_TYPE_NOT_COMPATIBLE);
                return DbImportResult;
            }

            CanCreateProperties = true;

            mUser = (from x in Cee.Users where x.Id == MetaData.UserId select x).FirstOrDefault();

            if (mUser == null)
            {
                DbImportResult.ErrorMessages.Add(string.Format(BuildItemNotFoundInDatabaseMessage("UserId", MetaData.UserId.ToString(CultureInfo.CurrentCulture))));

                return DbImportResult;
            }

            IList<ComponentTestingPropertyDataAdapter> importData = new List<ComponentTestingPropertyDataAdapter>();

            string connString = BuildConnectionString(MetaData.FullFileName);

            using (var excelConn = new OleDbConnection(connString))
            {
                try
                {
                    using (var cmd = new OleDbCommand())
                    {
                        cmd.CommandTimeout = 600;
                        cmd.Connection = excelConn;
                        cmd.CommandText = string.Format(@"SELECT * FROM [{0}$] WHERE [{1}] IS NOT NULL", WorkSheetName, TestingPropertyColumn.ControlSystemName);

                        excelConn.Open();

                        if (!WorkSheetCheckColumnNamesAreValid<TestingPropertyColumn>(GetColumnHeadersFromDataSet(cmd, (int)TestingPropertyColumn.ComponentName)))
                        {
                            DbImportResult.ErrorMessages.Add(ExcelWorkSheetColumnsNotValidMessage());
                            return DbImportResult;
                        }

                        const int STARTCOLUMN = (int)TestingPropertyColumn.ComponentName;
                        List<string> dynamicProperyNames = GetDynamicProperties(cmd, STARTCOLUMN);

                        int k = 1;
                        using (OleDbDataReader dr = cmd.ExecuteReader())
                        {
                            while (dr.Read())
                            {
                                k++;

                                try
                                {
                                    var adapter = new ComponentTestingPropertyDataAdapter(dr, dynamicProperyNames, STARTCOLUMN + 1);
                                    importData.Add(adapter);
                                }
                                catch (Exception ex)
                                {
                                    DbImportResult.ErrorMessages.Add(string.Format("ComponentTestingPropertyDataAdapter row {0} - {1}", k, ex.Message));
                                }
                            }

                            excelConn.Close();
                        }
                    }

                    if (MetaData.ImportType == CommonUtils.ImportType.CreateComponentTestingProperties)
                    {
                        InsertData(importData);
                    }
                    else if (MetaData.ImportType == CommonUtils.ImportType.UpdateComponentTestingProperties)
                    {
                        UpdateData(importData);
                    }

                    DbImportResult.ImportedCount = mSavedResults.Count;
                    return DbImportResult;
                }
                catch (OleDbException ex)
                {
                    DbImportResult.ErrorMessages.Add(ex.ToString());

                    return DbImportResult;
                }

                finally
                {
                    if (excelConn.State == ConnectionState.Open)
                    {
                        excelConn.Close();
                    }
                }
            }
        }
        private void AddDynamicProperties(ComponentTestingPropertyDataAdapter adapter, ControlSystemComponent matchingComponent, int i)
        {
            foreach (DynamicProperty dynamicProperty in adapter.DynamicProperties)
            {
                ControlSystemComponentTestingProperty matchProperty = (from x in Cee.ControlSystemComponentTestingProperties where string.Compare(x.Name, dynamicProperty.PropertyName, true, CultureInfo.CurrentCulture) == 0 select x).FirstOrDefault();

                if (matchProperty == null)
                {
                    RaiseMessage(CommonUtils.MessageType.Error, string.Format(BuildItemNotFoundInDatabaseMessage("ControlSystemTestingProperty", dynamicProperty.PropertyName, i + 1)));
                    continue;
                }

                ControlSystemComponentTypeTestingProperty matchCompTypeProperty = (from x in Cee.ControlSystemComponentTypeTestingProperties
                                                                            where x.TestPropertyId == matchProperty.Id && x.ComponentTypeId == matchingComponent.ControlSystemComponentTypeId
                                                                            select x).FirstOrDefault();

                if (matchCompTypeProperty == null)
                {
                    string controlSystemTypeName = (from x in Cee.ControlSystemComponentTypes where x.Id == matchingComponent.ControlSystemComponentTypeId select x.Name).FirstOrDefault();
                    string controlSystemPropertyName = (from x in Cee.ControlSystemTestingProperties where x.Id == matchProperty.Id select x.Name).FirstOrDefault();

                    string message = string.Format("Missing a entry in Database for the Table 'ControlSystemComponentTypeTestingProperty' Where ControlSystemComponentTyped = '{0}'({1}) And ControlSystemTestingPropertyId = '{2}' ({3}). Row {4}.",
                        matchingComponent.ControlSystemComponentTypeId, controlSystemTypeName, matchProperty.Id, controlSystemPropertyName, i);
                    RaiseMessage(CommonUtils.MessageType.Error, message);
                    continue;
                }

                //check for duplicates properties on this component (not allowed).
                int duplicateCount = (from x in Cee.ControlSystemComponentTestingPropertyValues where x.ComponentId == matchingComponent.ControlSystemComponentTypeId && x.TestPropertyId == matchProperty.Id select x.Id).Count();

                if (duplicateCount > 0)
                {
                    string propertyName = matchProperty.Name;
                    string componentName = matchingComponent.Name;

                    string message = string.Format("An entry already exists in Database for the Table 'ControlSystemComponentTestingPropertyValue' Where ComponentId = '{0}'({1}) And ControlSystemTestingPropertyId = '{2}' ({3}). Row {4}.",
                        matchingComponent.ControlSystemComponentTypeId, componentName, matchProperty.Id, propertyName, i);
                    RaiseMessage(CommonUtils.MessageType.Error, message);
                    continue;
                }

                if (PropertyValueToPropertyTypeMismatched(matchProperty.PropertyListId, matchProperty.Type, dynamicProperty, i + 1))
                {
                    continue;
                }

                var propertyValue = new ControlSystemComponentTestingPropertyValue
                {
                    TestPropertyId = matchProperty.Id,
                    Value = dynamicProperty.PropertyValue,
                    VerifiedUserDate = string.Format("{0} by {1}", DateTime.Now.ToString(@"dd/MM/yyyy hh:mm"), mUser.FirstLastName)
                };

                matchingComponent.ControlSystemComponentTestingPropertyValues.Add(propertyValue);

                var pair = new PropertyNameComponentNamePair
                {
                    ComponentName = matchingComponent.Name,
                    PropertyName = matchProperty.Name,
                    Value = dynamicProperty.PropertyValue
                };

                mSavedResults.Add(pair);
            }
        }