public override DbImportResult Import(bool canCreateProperties = false)
{
DbImportResult = new DbImportResult();
if (MetaData.ImportType != CommonUtils.ImportType.CreateCalibrationComponent && MetaData.ImportType != CommonUtils.ImportType.UpdateCalibrationComponent)
{
DbImportResult.ErrorMessages.Add(IMPORT_TYPE_NOT_COMPATIBLE);
return DbImportResult;
}
LoadCachedData();
CanCreateProperties = canCreateProperties;
//Include Headers so we can match up the component properties (HDR=YES).
string connString = BuildConnectionString(MetaData.FullFileName);
using (OleDbConnection excelConn = new OleDbConnection(connString))
{
IList<CalibrationComponentDataAdapter> importData = new List<CalibrationComponentDataAdapter>();
try
{
using (OleDbCommand cmd = new OleDbCommand())
{
cmd.CommandTimeout = 600;
cmd.Connection = excelConn;
cmd.CommandText = CalibrationCommandText;
if (excelConn.State != ConnectionState.Open)
{
excelConn.Open();
}
List<string> spreadSheetPropertyNames = GetPropertyColumnHeadersFromDataSet(cmd, (int)CalibrationColumn.Notes + 1);
if (spreadSheetPropertyNames.Count != spreadSheetPropertyNames.Distinct().Count())
{
DbImportResult.ErrorMessages.Add(DuplicatePropertyNamesExistMessage());
return DbImportResult;
}
if (!WorkSheetCheckColumnNamesAreValid<CalibrationColumn>(GetColumnHeadersFromDataSet(cmd, (int)CalibrationColumn.Notes)))
{
DbImportResult.ErrorMessages.Add(ExcelWorkSheetColumnsNotValidMessage());
return DbImportResult;
}
using (OleDbDataReader dr = cmd.ExecuteReader())
{
int rowNumber = 1;
while (dr.Read())
{
rowNumber++;
CalibrationComponentDataAdapter adapter = new CalibrationComponentDataAdapter(dr, mExistingProperties, spreadSheetPropertyNames,
rowNumber, CanCreateProperties, MetaData.ImportType);
if (!adapter.IsValid())
{
foreach (string errorMessage in adapter.ErrorMessages)
{
RaiseMessage(CommonUtils.MessageType.Error, errorMessage);
}
}
else
{
importData.Add(adapter);
}
}
excelConn.Close();
}
}
if (importData.Count == 0)
{
RaiseMessage(CommonUtils.MessageType.Warning, NoDataFoundForWorkSheetMessage());
return DbImportResult;
}
if (MetaData.ImportType == CommonUtils.ImportType.CreateCalibrationComponent)
{
InsertData(importData);
}
else if (MetaData.ImportType == CommonUtils.ImportType.UpdateCalibrationComponent)
{
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 BuildPropertyValues(CalibrationComponent componentIn, CalibrationComponentDataAdapter adapter)
{
foreach (CalibrationPropertyDto dto in adapter.PropertyValues)
{
CalibrationProperty property = (from x in mExistingProperties where x.Name.ToLower() == dto.PropertyName.ToLower() select x).FirstOrDefault();
if (property == null)
{
if (CanCreateProperties)
{
property = new CalibrationProperty { Name = dto.PropertyName, Type = "not used", Description = "(created by importer.)" };
mExistingProperties.Add(property);
//JOIN TABLE - joins the Property with the Type
CalibrationComponentTypeProperty typePropertyJoin = new CalibrationComponentTypeProperty();
typePropertyJoin.CalibrationComponentType = componentIn.CalibrationComponentType;
//EngineeringUnit
if (dto.NewUnit != null)
{
CalibrationEngineeringUnit unit = (from x in mExistingUnits where x.Name == dto.NewUnit.Name select x).FirstOrDefault();
if (unit == null)
{
typePropertyJoin.CalibrationEngineeringUnit = dto.NewUnit;
}
else
{
typePropertyJoin.CalibrationEngineeringUnit = unit;
}
}
property.CalibrationComponentTypeProperties.Add(typePropertyJoin);
mExistingProperties.Add(property);
}
else
{
RaiseMessage(CommonUtils.MessageType.Error, string.Format("WorkSheet '{0}' Line '{1} Tag {2} Component Name {3}' : The property does not exist.",
WorkSheetName, adapter.RowNumber, adapter.Tag, adapter.CalibrationName));
continue;
}
}
else
{
//do we have a record in the join table for this property and comp type?
CalibrationComponentTypeProperty equipmentComponentTypeProperty =
(from x in Cee.CalibrationComponentTypeProperties
where x.CalibrationComponentType.Name.ToLower() == componentIn.CalibrationComponentType.Name.ToLower()
&& x.CalibrationProperty.Name.ToLower() == property.Name.ToLower()
select x).FirstOrDefault();
if (equipmentComponentTypeProperty == null)
{
////no row in join table - so create one.
CalibrationComponentTypeProperty typePropertyJoin = new CalibrationComponentTypeProperty();
if (dto.NewUnit != null)
{
CalibrationEngineeringUnit unit = (from x in mExistingUnits where x.Name == dto.NewUnit.Name select x).FirstOrDefault();
if (unit == null)
{
typePropertyJoin.CalibrationEngineeringUnit = dto.NewUnit;
}
else
{
typePropertyJoin.CalibrationEngineeringUnit = unit;
}
}
typePropertyJoin.CalibrationComponentType = componentIn.CalibrationComponentType;
property.CalibrationComponentTypeProperties.Add(typePropertyJoin);
}
}
CalibrationComponentPropertyValue propertyValue = (from x in Cee.CalibrationComponentPropertyValues
where x.CalibrationComponentId == componentIn.Id
&& x.CalibrationPropertyId == property.Id
select x).FirstOrDefault();
if (propertyValue == null)
{
propertyValue = new CalibrationComponentPropertyValue {CalibrationComponent = componentIn, CalibrationProperty = property};
property.Type = "Text";//not used but not nullable
property.EnableAsFound = false; //not used but not nullable
}
//Set AsFoundValue
if (!string.IsNullOrEmpty(dto.AsFoundValue))
{
propertyValue.Value = dto.AsFoundValue;
}
//Set AsLeftValue
if (!string.IsNullOrEmpty(dto.AsLeftValue))
{
propertyValue.Value2 = dto.AsLeftValue;
}
componentIn.CalibrationComponentPropertyValues.Add(propertyValue);
}
}
