internal void AddCollarData(ModelImportStatus mos, Stream fileStream, FormatSpecification.ImportDataMap importMap, int batchSize, Action <string, double> UpdateStatus, int approxNumLines, string connectionString, List <string> existingHoleNames, Guid NKDProjectID, bool overwrite)
{
bool hasDuplicateHoles = false;
// iterate through the data lines
int ct = 1;
int linesRead = 0;
SqlConnection connection = null;
SqlConnection secondaryConnection = null;
Dictionary <string, int> uniqueHoleNames = new Dictionary <string, int>();
List <List <string> > rejectedLines = new List <List <string> >();
Dictionary <string, string> holeWarningMessages = new Dictionary <string, string>();
// this loop makes sure that any guids are properly types so that a text string for that guid can be passed into the query
foreach (ColumnMap cmap in importMap.columnMap)
{
bool isFKColumn = cmap.hasFKRelation;
if (isFKColumn)
{
cmap.importDataType = ImportDataMap.TEXTDATATYPE;
}
}
// get a connection to the database
try
{
connection = new SqlConnection(connectionString);
connection.Open();
secondaryConnection = new SqlConnection(connectionString);
secondaryConnection.Open();
int numCommits = 0;
SqlTransaction trans;
trans = connection.BeginTransaction();
List <SqlCommand> commands = new List <SqlCommand>();
int tb = 0;
int transactionBatchLimit = batchSize;
// open the filestream and read the first line
StreamReader sr = null;
try
{
sr = new StreamReader(fileStream);
}
catch (Exception ex)
{
mos.AddErrorMessage("Error getting data stream for input data:\n" + ex.ToString());
mos.finalErrorCode = ModelImportStatus.ERROR_LOADING_FILE;
}
string line = null;
float bct = 1;
// report every X blocks
int repCount = 0;
//int reportOnBlock = 1000;
float fNumLines = (float)approxNumLines;
// get the column containing the hole name
ColumnMap cmapHeader = importMap.FindItemsByTargetName("HoleName");
cmapHeader.importDataType = ImportDataMap.TEXTDATATYPE;
int headerIDX = cmapHeader.sourceColumnNumber;
int numberOfHolesAdded = 0;
if (sr != null)
{
while ((line = sr.ReadLine()) != null)
{
repCount++;
bct++;
linesRead++;
if (linesRead == 75)
{
bool breakHere;
int cc = 0;
double ff = Math.PI * 10;
}
if (ct >= importMap.dataStartLine)
{
string statementPart1 = "INSERT INTO " + importMap.mapTargetPrimaryTable + " ";
string clauseValues = "";
string clauseParameters = "";
List <string> items = parseTestLine(line, importMap.inputDelimiter);
// using the column map, pick out the hole name field and see if it is in the database already
string headerNameItem = items[headerIDX];
// check if this holename is a duplicate in the file
if (headerNameItem.Trim().Equals("A11A"))
{
bool b = true;
}
bool hasHolenameEntryInFile = uniqueHoleNames.ContainsKey(headerNameItem.Trim());
if (hasHolenameEntryInFile)
{
int val = uniqueHoleNames[headerNameItem.Trim()];
uniqueHoleNames[headerNameItem.Trim()] = val++;
holeWarningMessages.Add(headerNameItem + " (" + val + ")", "Hole: " + headerNameItem + " at line " + linesRead + " already exists in the input file - skipping.");
mos.recordsFailed++;
continue;
}
else
{
uniqueHoleNames.Add(headerNameItem.Trim(), 1);
}
// check if this hole exists in the database
if (existingHoleNames.Contains(headerNameItem.Trim()))
{
if (!holeWarningMessages.ContainsKey(headerNameItem))
{
holeWarningMessages.Add(headerNameItem, "Hole: " + headerNameItem + " already exists in this project. Skipping record at line " + linesRead + ".");
if (!overwrite)
{
mos.recordsFailed++;
}
}
else
{
}
mos.finalErrorCode = ModelImportStatus.DATA_CONSISTENCY_ERROR;
hasDuplicateHoles = true;
rejectedLines.Add(items);
continue;
}
#region mappsearch
// now pick out all the mapped values
foreach (ColumnMap cmap in importMap.columnMap)
{
bool isFKColumn = cmap.hasFKRelation;
int colID = cmap.sourceColumnNumber;
string columnValue = cmap.defaultValue;
if (colID >= 0)
{
columnValue = items[colID];
}
string targetCol = cmap.targetColumnName;
string targetTable = cmap.targetColumnTable;
clauseValues += "" + targetTable + "." + targetCol + ",";
if (isFKColumn)
{
// go and search for the appropriate value from the foreign key table
string newValue = ForeignKeyUtils.FindFKValueInDictionary(columnValue, cmap, secondaryConnection, true);
if (newValue == null)
{
clauseParameters += "NULL,";
}
else
{
clauseParameters += "\'" + newValue + "\',";
}
}
else
{
if (cmap.importDataType.Equals(ImportDataMap.NUMERICDATATYPE))
{
if (columnValue.Equals("-") || columnValue.Trim().Length == 0)
{
if (cmap.defaultValue != null && cmap.defaultValue.Length > 0)
{
columnValue = cmap.defaultValue;
}
else
{
columnValue = "NULL";
}
}
clauseParameters += columnValue + ",";
}
else if (cmap.importDataType.Equals(ImportDataMap.TIMESTAMPDATATYPE))
{
var parsed = DateUtils.CleanDate(columnValue);
if (parsed.HasValue)
{
columnValue = "\'" + parsed.Value.ToString("yyyy-MM-dd hh:mm:ss tt") + "\'";
}
else
{
columnValue = "NULL";
}
clauseParameters += columnValue + ",";
}
else
{
if (columnValue.Equals("-") || columnValue.Trim().Length == 0)
{
if (cmap.defaultValue != null && cmap.defaultValue.Length > 0)
{
columnValue = cmap.defaultValue;
}
}
clauseParameters += "\'" + columnValue + "\',";
}
}
}
#endregion
// now just a hack to remove the final coma from the query
clauseParameters = clauseParameters.Substring(0, clauseParameters.Length - 1);
clauseValues = clauseValues.Substring(0, clauseValues.Length - 1);
string commandText = statementPart1 + "(" + clauseValues + ") VALUES (" + clauseParameters + ")";
SqlCommand sqc = new SqlCommand(commandText, connection, trans);
numberOfHolesAdded++;
if (commitToDB)
{
try
{
sqc.ExecuteNonQuery();
}
catch (Exception ex) {
string err = "" + ex.ToString();
throw ex;
}
}
tb++;
if (tb == transactionBatchLimit)
{
// commit batch, then renew the transaction
if (commitToDB)
{
trans.Commit();
numCommits++;
// trans = null;
trans = connection.BeginTransaction();
}
// reset counter
tb = 0;
}
}
ct++;
}
}
if (tb > 0)
{
if (commitToDB)
{
trans.Commit();
}
numCommits++;
}
mos.recordsAdded = numberOfHolesAdded;
UpdateStatus("Finished writing collars to database ", 100.0);
}
catch (Exception ex)
{
UpdateStatus("Error writing collars to database ", 0);
mos.AddErrorMessage("Error writing collar data at line " + linesRead + ":\n" + ex.ToString());
mos.finalErrorCode = ModelImportStatus.ERROR_WRITING_TO_DB;
}
finally
{
try {
connection.Close();
secondaryConnection.Close();
fileStream.Close();
}
catch (Exception ex)
{
mos.AddErrorMessage("Error closing conenction to database:\n" + ex.ToString());
mos.finalErrorCode = ModelImportStatus.ERROR_WRITING_TO_DB;
}
}
if (hasDuplicateHoles == true && overwrite == true)
{
OverwriteCollarRecord(mos, rejectedLines, importMap, connectionString, NKDProjectID, UpdateStatus, holeWarningMessages);
}
foreach (KeyValuePair <string, string> kvp in holeWarningMessages)
{
string v = kvp.Value;
mos.AddWarningMessage(v);
}
mos.linesReadFromSource = linesRead;
}
private void OverwriteCollarRecord(ModelImportStatus mos, List <List <string> > rejectedLines, ImportDataMap importMap, string connectionString, Guid NKDProjectID, Action <string, double> UpdateStatus, Dictionary <string, string> holeWarningMessages)
{
SqlConnection connection = null;
SqlConnection secondaryConnection = null;
try
{
connection = new SqlConnection(connectionString);
connection.Open();
secondaryConnection = new SqlConnection(connectionString);
secondaryConnection.Open();
int numCommits = 0;
SqlTransaction trans;
trans = connection.BeginTransaction();
List <SqlCommand> commands = new List <SqlCommand>();
int tb = 0;
int transactionBatchLimit = 10;
// open the filestream and read the first line
float bct = 1;
// report every X blocks
int repCount = 0;
//int reportOnBlock = 1000;
float fNumLines = (float)rejectedLines.Count();
// get the column containing the hole name
ColumnMap cmapHeader = importMap.FindItemsByTargetName("HoleName");
cmapHeader.importDataType = ImportDataMap.TEXTDATATYPE;
int headerIDX = cmapHeader.sourceColumnNumber;
int numberOfHolesAdded = 0;
int linesRead = 0;
int ct = 1;
// get all fo the header IDs in one go before we try the insert
Dictionary <string, Guid> holeIDLookups = CollarQueries.FindHeaderGuidsForProject(NKDProjectID);
// this loop makes sure that any guids are properly types so that a text string for that guid can be passed into the query
foreach (ColumnMap cmap in importMap.columnMap)
{
bool isFKColumn = cmap.hasFKRelation;
if (isFKColumn)
{
cmap.importDataType = ImportDataMap.TEXTDATATYPE;
}
}
foreach (List <string> columnData in rejectedLines)
{
linesRead++;
repCount++;
bct++;
string statementPart1 = "UPDATE " + importMap.mapTargetPrimaryTable + " ";
string clauseValues = "";
// using the column map, pick out the hole name field and see if it is in the database already
string headerNameItem = columnData[headerIDX];
string headerGUID = "";
bool lv = holeIDLookups.ContainsKey(headerNameItem);
if (!lv)
{
// oops - no hole ID with this name - should not happen though!!
}
else
{
Guid holeGuid = new Guid();
holeIDLookups.TryGetValue(headerNameItem, out holeGuid);
headerGUID = holeGuid.ToString();
}
#region mappsearch
// now pick out all the mapped values
foreach (ColumnMap cmap in importMap.columnMap)
{
bool isFKColumn = cmap.hasFKRelation;
int colID = cmap.sourceColumnNumber;
string columnValue = cmap.defaultValue;
if (colID >= 0)
{
columnValue = columnData[colID];
}
string targetCol = cmap.targetColumnName;
// ignore mapped hole name and project ID columns
if (targetCol.Trim().Equals("HoleName") || targetCol.Trim().Equals("ProjectID"))
{
continue;
}
string targetTable = cmap.targetColumnTable;
clauseValues += "" + targetTable + "." + targetCol + "=";
if (isFKColumn)
{
// go and search for the appropriate value from the foreign key table
string newValue = ForeignKeyUtils.FindFKValueInDictionary(columnValue, cmap, secondaryConnection, true);
if (newValue == null)
{
clauseValues += "NULL,";
}
else
{
clauseValues += "\'" + newValue + "\',";
}
}
else
{
if (cmap.importDataType.Equals(ImportDataMap.NUMERICDATATYPE))
{
if (columnValue.Equals("-") || columnValue.Trim().Length == 0)
{
if (cmap.defaultValue != null && cmap.defaultValue.Length > 0)
{
columnValue = cmap.defaultValue;
}
else
{
columnValue = "NULL";
}
}
}
else if (cmap.importDataType.Equals(ImportDataMap.TIMESTAMPDATATYPE))
{
DateTime dtr = new DateTime();
bool parsed = DateTime.TryParse(columnValue, out dtr);
if (parsed)
{
columnValue = "\'" + dtr.ToString("yyyy-MM-dd hh:mm:ss tt") + "\'";
}
else
{
columnValue = "NULL";
}
}
else
{
columnValue = "\'" + columnValue + "\'";
}
clauseValues += columnValue + ",";
}
}
#endregion
// now just a hack to remove the final coma from the query
clauseValues = clauseValues.Substring(0, clauseValues.Length - 1);
string commandText = statementPart1 + "SET " + clauseValues + " WHERE HeaderID=\'" + headerGUID + "\' AND ProjectID=\'" + NKDProjectID.ToString() + "\';";
SqlCommand sqc = new SqlCommand(commandText, connection, trans);
string msg = "";
//holeWarningMessages.TryGetValue(headerNameItem, out msg);
msg = "Hole " + headerNameItem + " was overwritten with new data";
holeWarningMessages[headerNameItem] = msg;
numberOfHolesAdded++;
if (commitToDB)
{
sqc.ExecuteNonQuery();
}
tb++;
if (tb == transactionBatchLimit)
{
// commit batch, then renew the transaction
if (commitToDB)
{
trans.Commit();
numCommits++;
// trans = null;
trans = connection.BeginTransaction();
}
// reset counter
tb = 0;
}
ct++;
}
if (tb > 0)
{
if (commitToDB)
{
trans.Commit();
}
numCommits++;
}
mos.recordsUpdated = numberOfHolesAdded;
UpdateStatus("Finished writing collars to database ", 100.0);
}
catch (Exception ex)
{
UpdateStatus("Error writing collars to database ", 0);
mos.AddErrorMessage("Error writing collar data at line " + rejectedLines.Count + ":\n" + ex.ToString());
mos.finalErrorCode = ModelImportStatus.ERROR_WRITING_TO_DB;
}
finally
{
try
{
connection.Close();
secondaryConnection.Close();
}
catch (Exception ex)
{
mos.AddErrorMessage("Error closing conenction to database:\n" + ex.ToString());
mos.finalErrorCode = ModelImportStatus.ERROR_WRITING_TO_DB;
}
}
}
internal void AddSurveyData(ModelImportStatus mos, Stream fileStream, FormatSpecification.ImportDataMap importMap, int batchSize, Action <string, double> UpdateStatus, int approxNumLines, string connectionString, Guid NKDProjectID, bool overwrite, bool checkForDuplicates)
{
bool duplicateFound = false;
// iterate through the data lines
int ct = 1;
int linesRead = 0;
SqlConnection connection = null;
SqlConnection secondaryConnection = null;
Dictionary <Guid, List <string> > rejectedLines = new Dictionary <Guid, List <string> >();
Dictionary <string, string> holeWarningMessages = new Dictionary <string, string>();
using (var entityObj = new NKDC(connectionString, null))
{
//entityObj.Configuration.AutoDetectChangesEnabled = false;
SurveyQueries sq = new SurveyQueries();
// get a connection to the database
try
{
connection = new SqlConnection(connectionString);
connection.Open();
secondaryConnection = new SqlConnection(connectionString);
secondaryConnection.Open();
int numCommits = 0;
SqlTransaction trans;
//trans = connection.BeginTransaction(System.Data.IsolationLevel.Snapshot);
List <SqlCommand> commands = new List <SqlCommand>();
int tb = 0;
int transactionBatchLimit = batchSize;
// open the filestream and read the first line
StreamReader sr = null;
try
{
sr = new StreamReader(fileStream);
}
catch (Exception ex)
{
mos.AddErrorMessage("Error getting data stream for input data:\n" + ex.ToString());
mos.finalErrorCode = ModelImportStatus.ERROR_LOADING_FILE;
}
string line = null;
float bct = 1;
// report every X blocks
int repCount = 0;
//int reportOnBlock = 1000;
float fNumLines = (float)approxNumLines;
// get the column containing the hole name
Dictionary <string, Guid> holeIDLookups = new Dictionary <string, Guid>();
int numberOfHolesAdded = 0;
ColumnMap headerCmap = importMap.FindItemsByTargetName("HeaderID");
ColumnMap depthCmap = importMap.FindItemsByTargetName("Depth");
float percentComplete = 0;
int headerIDX = headerCmap.sourceColumnNumber;
if (sr != null)
{
while ((line = sr.ReadLine()) != null)
{
repCount++;
percentComplete = ((float)ct / approxNumLines) * 100.0f;
bct++;
linesRead++;
if (ct >= importMap.dataStartLine)
{
string statementPart1 = "INSERT INTO " + importMap.mapTargetPrimaryTable + " ";
string clauseValues = "";
string clauseParameters = "";
List <string> items = parseTestLine(line, importMap.inputDelimiter);
// using the column map, pick out the hole name field and see if it is in the database already
string headerNameItem = items[headerIDX];
// check if this holename is a duplicate in the file
bool foundHole = false;
Guid holeID = new Guid();
bool lv = holeIDLookups.ContainsKey(headerNameItem);
if (!lv)
{
string headerGUID = ForeignKeyUtils.FindFKValueInOther(headerNameItem, headerCmap, secondaryConnection, false, "HoleName", NKDProjectID);
if (headerGUID == null)
{
// this means we have not found the specified records in the header table
// Report on issue and skip line
}
else
{
foundHole = true;
holeID = new Guid(headerGUID);
holeIDLookups.Add(headerNameItem, holeID);
}
}
else
{
holeIDLookups.TryGetValue(headerNameItem, out holeID);
foundHole = true;
}
if (!foundHole)
{
mos.AddWarningMessage("Failed to find hole " + headerNameItem + ". Skipping record at line " + linesRead + ".");
mos.finalErrorCode = ModelImportStatus.DATA_CONSISTENCY_ERROR;
mos.recordsFailed++;
continue;
}
if (checkForDuplicates == true && depthCmap != null)
{
// check for duplicate depths
string d = items[depthCmap.sourceColumnNumber];
decimal dt = 0;
bool isParsed = decimal.TryParse(d, out dt);
if (isParsed)
{
List <Guid> rr = sq.CheckForDuplicate(holeID, dt, secondaryConnection);
//List<Guid> rr = sq.CheckForDuplicate(holeID, dt, entityObj);
if (rr.Count > 0)
{
duplicateFound = true;
if (!rejectedLines.ContainsKey(rr.First()))
{
rejectedLines.Add(rr.First(), items);
mos.AddWarningMessage("Duplicate depth found in survey data for hole " + headerNameItem + " at depth " + d + " on line " + linesRead);
UpdateStatus("Duplicate depth found in survey data for hole " + headerNameItem + " at depth " + d, percentComplete);
}
else
{
mos.AddWarningMessage("Duplicate depth found in survey data file for hole " + headerNameItem + " at depth " + d + " on line " + linesRead);
UpdateStatus("Duplicate depth found in survey data file for hole " + headerNameItem + " at depth " + d, percentComplete);
rejectedLines[rr.First()] = items;
}
if (!overwrite)
{
mos.recordsFailed++;
}
continue;
}
}
}
#region mappsearch
// now pick out all the mapped values
foreach (ColumnMap cmap in importMap.columnMap)
{
if (cmap.targetColumnName.Trim().Equals("HeaderID"))
{
string targetCol = cmap.targetColumnName;
string targetTable = cmap.targetColumnTable;
clauseValues += "" + targetTable + "." + targetCol + ",";
clauseParameters += "\'" + holeID.ToString() + "\',";
}
else
{
bool isFKColumn = cmap.hasFKRelation;
int colID = cmap.sourceColumnNumber;
string columnValue = cmap.defaultValue;
if (colID >= 0)
{
columnValue = items[colID];
}
string targetCol = cmap.targetColumnName;
string targetTable = cmap.targetColumnTable;
clauseValues += "" + targetTable + "." + targetCol + ",";
if (isFKColumn)
{
// go and search for the appropriate value from the foreign key table
string newValue = ForeignKeyUtils.FindFKValueInDictionary(columnValue, cmap, secondaryConnection, true);
columnValue = newValue;
if (newValue != null && newValue.Trim().Length > 0)
{
clauseParameters += "\'" + columnValue + "\',";
}
else
{
clauseParameters += "NULL,";
}
}
else
{
if (cmap.importDataType.Equals(ImportDataMap.NUMERICDATATYPE))
{
if (columnValue.Equals("-") || columnValue.Equals(""))
{
if (cmap.defaultValue != null && cmap.defaultValue.Length > 0)
{
columnValue = cmap.defaultValue;
}
else
{
columnValue = "NULL";
}
}
clauseParameters += columnValue + ",";
}
else
{
//if (columnValue.Equals("-"))
//{
// if (cmap.defaultValue != null && cmap.defaultValue.Length > 0)
// {
// columnValue = cmap.defaultValue;
// }
//}
clauseParameters += "\'" + columnValue + "\',";
}
}
}
}
#endregion
// now just a hack to remove the final coma from the query
clauseParameters = clauseParameters.Substring(0, clauseParameters.Length - 1);
clauseValues = clauseValues.Substring(0, clauseValues.Length - 1);
string commandText = statementPart1 + "(" + clauseValues + ") VALUES (" + clauseParameters + ")";
//SqlCommand sqc = new SqlCommand(commandText, connection, trans);
SqlCommand sqc = new SqlCommand(commandText, connection);
numberOfHolesAdded++;
if (commitToDB)
{
try
{
sqc.ExecuteNonQuery();
}
catch (Exception ex)
{
mos.AddErrorMessage("Failed to insert items on line " + linesRead + ".");
UpdateStatus("Failed to insert items on line " + linesRead + ".", percentComplete);
mos.recordsFailed++;
mos.finalErrorCode = ModelImportStatus.ERROR_WRITING_TO_DB;
}
}
UpdateStatus("Updating from line " + linesRead, percentComplete);
tb++;
//if (tb == transactionBatchLimit)
//{
// // commit batch, then renew the transaction
// if (commitToDB)
// {
// trans.Commit();
// numCommits++;
// // trans = null;
// trans = connection.BeginTransaction(System.Data.IsolationLevel.Snapshot);
// }
// // reset counter
// tb = 0;
//}
}
ct++;
}
}
if (tb > 0)
{
//if (commitToDB)
//{
// trans.Commit();
//}
numCommits++;
}
mos.recordsAdded = numberOfHolesAdded;
UpdateStatus("Finished writing records to database ", 100.0);
}
catch (Exception ex)
{
UpdateStatus("Error writing records to database ", 0);
mos.AddErrorMessage("Error writing records data at line " + linesRead + ":\n" + ex.ToString());
mos.finalErrorCode = ModelImportStatus.ERROR_WRITING_TO_DB;
}
finally
{
try
{
connection.Close();
secondaryConnection.Close();
fileStream.Close();
}
catch (Exception ex)
{
mos.AddErrorMessage("Error closing conenction to database:\n" + ex.ToString());
mos.finalErrorCode = ModelImportStatus.ERROR_WRITING_TO_DB;
}
}
if (duplicateFound == true && overwrite == true)
{
OverwriteSurveyRecord(mos, rejectedLines, importMap, connectionString, NKDProjectID, UpdateStatus, holeWarningMessages);
}
foreach (KeyValuePair <string, string> kvp in holeWarningMessages)
{
string v = kvp.Value;
mos.AddWarningMessage(v);
}
mos.linesReadFromSource = linesRead;
}
}
