public override DbImportResult Import(bool canCreateProperties = false)
        {
            if (MetaData.ImportType != CommonUtils.ImportType.CreatePipeComponent && MetaData.ImportType != CommonUtils.ImportType.UpdatePipeComponent)
            {
                DbImportResult.ErrorMessages.Add(IMPORT_TYPE_NOT_COMPATIBLE);
                return DbImportResult;
            }

            CanCreateProperties = canCreateProperties;

            DbImportResult = new DbImportResult();

            mExistingComponents = (from x in Cee.PipeComponents
                .Include("PipeComponentType")
                select x).ToList();

            mExistingComponentTypes = Cee.PipeComponentTypes.ToList();
            mExistingComponentProperties = Cee.PipeProperties.ToList();
            mExistingEquipmentComponentTypeProperty = (from x in Cee.PipeComponentTypeProperties.Include("PipeComponentType") select x).ToList();
            mExistingPropertyValues = Cee.PipePropertyValues.ToList();
            mExistingManufacturers = (from x in Cee.Manufacturers.Include("Models") where x.EquipmentTypeId == (int) CommonUtils.EquipmentTypeCode.PIPE select x).ToList();

            string connString = BuildConnectionString(MetaData.FullFileName);

            using (var excelConn = new OleDbConnection(connString))
            {
                IList<PipeComponentDataAdapter> importData = new List<PipeComponentDataAdapter>();

                try
                {
                    using (var cmd = new OleDbCommand())
                    {
                        cmd.CommandTimeout = 600;
                        cmd.Connection = excelConn;
                        cmd.CommandText = PipeComponentCommandText;

                        if (excelConn.State != ConnectionState.Open)
                        {
                            excelConn.Open();
                        }

                        List<string> spreadSheetPropertyNames = GetPropertyColumnHeadersFromDataSet(cmd, (int) PipeComponentColumn.Model + 1);

                        if (spreadSheetPropertyNames.Count != spreadSheetPropertyNames.Distinct().Count())
                        {
                            DbImportResult.ErrorMessages.Add(DuplicatePropertyNamesExistMessage());
                            return DbImportResult;
                        }

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

                        using (OleDbDataReader dr = cmd.ExecuteReader())
                        {
                            mEmptyPipeLineNbrCount = 0;
                            int rowNumber = 1;

                            while (dr.Read())
                            {
                                rowNumber++;
                                var adapter = new PipeComponentDataAdapter(dr, mExistingComponentProperties, spreadSheetPropertyNames, rowNumber, CanCreateProperties);

                                if (adapter.HasEmptyPipeComponentError)
                                {
                                    mEmptyPipeLineNbrCount++;

                                    if (mEmptyPipeLineNbrCount == 3)
                                    {
                                        RaiseMessage(CommonUtils.MessageType.Error, string.Format("Worksheet {0}, row {1}. The third blank 'Pipe Name' in a row. Skipping work sheet.", WorkSheetName, rowNumber));
                                    }
                                }

                                if (!adapter.IsValid())
                                {
                                    foreach (string errorMessage in adapter.ErrorMessages)
                                    {
                                        RaiseMessage(CommonUtils.MessageType.Error, errorMessage);
                                    }
                                }
                                else
                                {
                                    importData.Add(adapter);
                                }
                            }

                            excelConn.Close();
                        }
                    }

                    if (!importData.Any())
                    {
                        RaiseMessage(CommonUtils.MessageType.Warning, NoDataFoundForWorkSheetMessage());
                        return DbImportResult;
                    }

                    if (MetaData.ImportType == CommonUtils.ImportType.CreatePipeComponent)
                    {
                        InsertData(importData);
                    }
                    else if (MetaData.ImportType == CommonUtils.ImportType.UpdatePipeComponent)
                    {
                        UpdateData(importData);
                    }
                }
                catch (OleDbException ex)
                {
                    DbImportResult.ErrorMessages.Add(ex.ToString());
                    return DbImportResult;
                }
                finally
                {
                    if (excelConn.State == ConnectionState.Open)
                    {
                        excelConn.Close();
                    }
                }
            }

            DbImportResult.ImportedCount = mSavedResults.Count;
            return DbImportResult;
        }
        private bool GetPipe(PipeComponentDataAdapter adapter, int rowIndex, out Pipe pipeOut)
        {
            pipeOut = null;

            int areaNumber = int.Parse(adapter.PipeAreaNumber);
            int seq = int.Parse(adapter.PipeSequence);

            Pipe pipe = (from x in Cee.Pipes
                .Include("Area")
                .Include("PipeClass")
                .Include("PipeSize")
                .Include("PipeFluidCode")
                .Include("PipeSpecialFeature")
                where
                    x.CategoryId == PIPECAT_NOTUSED
                    && x.Area.AreaNumber == areaNumber
                    && x.SequenceNo == seq
                    && String.Equals(x.PipeClass.Name, adapter.PipeClass, StringComparison.CurrentCultureIgnoreCase)
                    && String.Equals(x.PipeSize.Name, adapter.PipeSize, StringComparison.CurrentCultureIgnoreCase)
                    && String.Equals(x.PipeFluidCode.Name, adapter.PipeFluidCode, StringComparison.CurrentCultureIgnoreCase)
                    && String.Equals(x.PipeSpecialFeature.Name, adapter.PipeSpecialFeature, StringComparison.CurrentCultureIgnoreCase)
                select x).FirstOrDefault();

            if (pipe != null)
            {
                pipeOut = pipe;
            }
            else
            {
                RaiseMessage(CommonUtils.MessageType.Error, string.Format("WorkSheet '{0}' Line '{1}': Could not find pipe '{2}' in database. Skipping this row.", WorkSheetName, rowIndex, adapter.PipeTag));
                return true;
            }

            return false;
        }
        private void BuildPropertyValues(PipeComponent componentIn, PipeComponentDataAdapter adapter, int index)
        {
            foreach (var pair in adapter.PropertyValues)
            {
                //DOES PROPERTY EXIST?
                PipeProperty property = (from x in mExistingComponentProperties where x.Name.ToLower() == pair.Key.ToLower() select x).FirstOrDefault();
                if (property == null)
                {
                    if (CanCreateProperties)
                    {
                        property = new PipeProperty {Name = pair.Key, DefaultValue = pair.Value, Description = " (created by importer)."};
                        mExistingComponentProperties.Add(property); //update cache
                    }
                    else
                    {
                        //ERROR!
                        RaiseMessage(CommonUtils.MessageType.Error, string.Format("WorkSheet '{0}' Line '{1} Tag {2} Component Name {3}' : The property does not exist.", WorkSheetName, index, adapter.Tag, adapter.ComponentName));
                        continue;
                    }
                }

                //CHECK PipeEquipmentComponentTypeProperty Exists
                PipeComponentTypeProperty equipmentComponentTypeProperty = null;
                if (mExistingEquipmentComponentTypeProperty.Any())
                {
                    equipmentComponentTypeProperty =
                        (from x in mExistingEquipmentComponentTypeProperty
                            where x.PipeComponentType.Name.ToLower() == componentIn.PipeComponentType.Name.ToLower()
                                  && x.PipeProperty.Name.ToLower() == property.Name.ToLower()
                            select x).FirstOrDefault();
                }

                if (equipmentComponentTypeProperty == null)
                {
                    if (CanCreateProperties)
                    {
                        //CREATE JOIN ROW
                        equipmentComponentTypeProperty = new PipeComponentTypeProperty();
                        equipmentComponentTypeProperty.PipeComponentType = componentIn.PipeComponentType; //note: set the object!
                        equipmentComponentTypeProperty.PipeProperty = property; //not set the object!
                        mExistingEquipmentComponentTypeProperty.Add(equipmentComponentTypeProperty); //update cache
                    }
                    else
                    {
                        //ERROR!
                        RaiseMessage(CommonUtils.MessageType.Warning, string.Format("WorkSheet '{0}' Row '{1} Tag {2} Component Type {3}' : The property {4} does not belong to the Component Type.",
                            WorkSheetName, adapter.RowNumber, adapter.Tag, componentIn.PipeComponentType.Name, property.Name));
                        continue;
                    }
                }
                property.PipeComponentTypeProperties.Add(equipmentComponentTypeProperty);

                //CHECK PROPERTYVALUE EXISTS
                PipePropertyValue propertyValue = null;
                if (mExistingPropertyValues.Any())
                {
                    propertyValue = (from x in mExistingPropertyValues
                        where x.PipeComponent.Name.ToLower() == componentIn.Name.ToLower()
                              && x.PipeProperty.Name.ToLower() == property.Name.ToLower()
                        select x).FirstOrDefault();
                }

                if (propertyValue == null)
                {
                    propertyValue = new PipePropertyValue();
                    propertyValue.PipeComponent = componentIn;
                    propertyValue.PipeProperty = property;
                    mExistingPropertyValues.Add(propertyValue); //update cache
                }

                //set value
                if (!string.IsNullOrEmpty(pair.Value))
                {
                    propertyValue.Value = pair.Value.ChangeNullToEmptyString();
                }
                componentIn.PipePropertyValues.Add(propertyValue);
            }
        }
        private bool ChangeManufacturerModel(PipeComponentDataAdapter adapter, PipeComponent newComponent)
        {
            //CHANGING Manufacturer
            if (!string.IsNullOrEmpty(adapter.Manufacturer))
            {
                if (adapter.Manufacturer.ToLower() != "null")
                {
                    Manufacturer manufactuer = (from x in mExistingManufacturers where x.Name.ToLower() == adapter.Manufacturer.ToLower() select x).FirstOrDefault();

                    if (manufactuer == null)
                    {
                        RaiseMessage(CommonUtils.MessageType.Error, string.Format(BuildItemNotFoundInDatabaseMessage("Manufacturer", adapter.Manufacturer, adapter.RowNumber)));
                        return false;
                    }

                    newComponent.Manufacturer = manufactuer;
                    newComponent.ManufacturerId = manufactuer.Id;

                    if (adapter.Model.ToLower() != "null")
                    {
                        //CHANGING Model
                        if (!string.IsNullOrEmpty(adapter.Model))
                        {
                            Model model = (from x in manufactuer.Models where x.Name.ToLower() == adapter.Model.ToLower() select x).FirstOrDefault();

                            if (model == null)
                            {
                                RaiseMessage(CommonUtils.MessageType.Error, string.Format(BuildItemNotFoundInDatabaseMessage("Model", adapter.Model, adapter.RowNumber)));
                                return false;
                            }

                            newComponent.Model = model;
                            newComponent.ModelId = model.Id;
                        }
                    }
                    else
                    {
                        newComponent.Model = null;
                        newComponent.ModelId = null;
                    }
                }
                else
                {
                    newComponent.Manufacturer = null;
                    newComponent.ManufacturerId = null;
                    newComponent.Model = null;
                    newComponent.ModelId = null;
                }
            }
            return false;
        }
        private int GetParentId(BaseAttachmentDto dto, CmsEntities cee)
        {
            if (dto.Type.ToLower() == CommonUtils.EntityType.Document.ToString().ToLower())
            {
                int match = (from x in cee.Documents where x.Name.ToLower() == dto.Tag.ToLower() select x.Id).FirstOrDefault();

                if (match > 0)
                {
                    return match;
                }
            }
            else if (dto.Type.ToLower() == CommonUtils.EntityType.Electrical.ToString().ToLower())
            {
                int match = (from x in cee.ElectricalEquipments where x.Name.ToLower() == dto.Tag.ToLower() select x.Id).FirstOrDefault();

                if (match > 0)
                {
                    return match;
                }
            }
            else if (dto.Type.ToLower() == CommonUtils.EntityType.Mechanical.ToString().ToLower())
            {
                int match = (from x in cee.MechanicalEquipments where x.Name.ToLower() == dto.Tag.ToLower() select x.Id).FirstOrDefault();

                if (match > 0)
                {
                    return match;
                }
            }
            else if (dto.Type.ToLower() == CommonUtils.EntityType.MobilePlant.ToString().ToLower())
            {
                int match = (from x in cee.MobilePlants where x.Name.ToLower() == dto.Tag.ToLower() select x.Id).FirstOrDefault();

                if (match > 0)
                {
                    return match;
                }
            }
            else if (dto.Type.ToLower() == CommonUtils.EntityType.Instrument.ToString().ToLower())
            {
                int match = (from x in cee.Instruments where x.Name.ToLower() == dto.Tag.ToLower() select x.Id).FirstOrDefault();

                if (match > 0)
                {
                    return match;
                }
            }
            else if (dto.Type.ToLower() == CommonUtils.EntityType.Issue.ToString().ToLower())
            {
                int result;
                int match = 0;

                if (int.TryParse(dto.Tag, out result))
                {
                    match = (from x in cee.Issues where x.Id == result select x.Id).FirstOrDefault();
                }

                if (match > 0)
                {
                    return match;
                }
            }
            else if (dto.Type.ToLower() == CommonUtils.EntityType.Pipe.ToString().ToLower())
            {

                PipeComponentDataAdapter adapter = new PipeComponentDataAdapter(dto.Tag);
                if (!adapter.ErrorMessages.Any())
                {

                    int pipeAreaNumber = int.Parse(adapter.PipeAreaNumber);
                    int pipeSequence = int.Parse(adapter.PipeSequence);

                    int match = (from x in cee.Pipes 
                                 where x.PipeClass.Name == adapter.PipeClass.ToLower() 
                                 && x.PipeFluidCode.Name==adapter.PipeFluidCode.ToLower()
                                 && x.PipeSize.Name == adapter.PipeSize
                                 && x.PipeSpecialFeature.Name==adapter.PipeSpecialFeature
                                 && x.Area.AreaNumber == pipeAreaNumber
                                 && x.SequenceNo == pipeSequence
                                 select x.Id).FirstOrDefault();

                    if (match > 0)
                    {
                        return match;
                    }
                }
                else
                {
                    DbImportResult.ErrorMessages.AddRange(adapter.ErrorMessages);
                }



              
            }
            else if (dto.Type.ToLower() == CommonUtils.EntityType.Control.ToString().ToLower())
            {
                int match = (from x in cee.ControlSystems where x.Name.ToLower() == dto.Tag.ToLower() select x.Id).FirstOrDefault();

                if (match > 0)
                {
                    return match;
                }
            }
            return 0;
        }