// ---------------------------------------------------------------------------------------- /// <!-- AddTwoValuesTo --> /// <summary> /// Adds two column values (and perhaps two columns if needed) to the table /// </summary> /// <param name="errorTable"></param> /// <param name="col1"></param> /// <param name="col2"></param> /// <param name="data"></param> /// <param name="value1"></param> /// <param name="value2"></param> /// <returns></returns> public static bool AddTwoValuesTo(DataTable errorTable, DataRow data , DataColumn col1, DataColumn col2, string value1, string value2, bool isRowInsertedIntoErrorTable, int level) { InfoAspect.Measuring("AddTwoValuesTo(7)"); col1.AllowDBNull = true; col2.AllowDBNull = true; if ((!errorTable.Columns.Contains(col1.ToString())) && (!errorTable.Columns.Contains(col2.ToString()))) { errorTable.Columns.Add(col1); errorTable.Columns.Add(col2); } if (isRowInsertedIntoErrorTable == false) { errorTable.ImportRow(data); isRowInsertedIntoErrorTable = true; } else { // isrowinserted = true; } foreach (DataRow dtRow in errorTable.Rows) { if (dtRow["FileLineNumber"].ToString() == data["FileLineNumber"].ToString()) { AddErrorCell(value1, level + 1); dtRow[col1.ToString()] = value1; dtRow[col2.ToString()] = value2; } } InfoAspect.Measured("AddTwoValuesTo(7)"); return(isRowInsertedIntoErrorTable); }
// ---------------------------------------------------------------------------------------- /// <!-- ImportErrorsInsert --> /// <summary> /// Inserts all module import errors into the database /// </summary> /// <param name="statusErrorLog"></param> /// <param name="itemIdByLineNum"></param> /// <param name="connection"></param> /// <returns></returns> public static int ImportErrorsInsert(DataTable statusErrorLog, Dictionary <int, int> itemIdByLineNum , string moduleName, InfoAspect aspect) { int errorCount = 0; try { string query = ImportDalCommon.ImportErrorQuery(moduleName); for (int row = 0; row < statusErrorLog.Rows.Count; ++row) { // ------------------------------------------------------------------ // Process the row // ------------------------------------------------------------------ string statusErrorCode = statusErrorLog.Rows[row]["Status_Error_Code"].ToString(); if (!Regex.IsMatch(statusErrorCode, "0[01]$")) { ImportDalCommon.ImportErrorInsert(query, statusErrorLog, row, statusErrorCode , itemIdByLineNum, moduleName, aspect.Enter(moduleName, "ImportErrorInsert")); aspect--; errorCount++; } } } catch (Exception ex) { Pause(); aspect.Rethrow(ex); } return(errorCount); }
// ---------------------------------------------------------------------------------------- /// <!-- GetAllImportFilesMatching --> /// <summary> /// Returns all the files in each directory matching the pattern and not matching the avoid pattern /// </summary> /// <param name="filePattern"></param> /// <param name="dirList"></param> /// <param name="avoidPattern">the pattern starting with the beginning of the file name (leave out starting ^ and \\)</param> /// <returns></returns> public static List <string> GetAllImportFilesMatching(string filePattern, List <DirectoryInfo> dirList , string avoidPattern, InfoAspect aspect) { List <string> fileName = new List <string>(); try { foreach (DirectoryInfo directory in dirList) { List <FileInfo> fi = new List <FileInfo>(directory.GetFiles()); List <FileInfo> orderedFileList = fi.OrderBy(x => x.LastWriteTime).ToList(); foreach (FileInfo file in orderedFileList) { if (Regex.IsMatch(file.Name, filePattern, RegexOptions.IgnoreCase)) { if (!Regex.IsMatch(file.Name, "^" + avoidPattern, RegexOptions.IgnoreCase) && !Regex.IsMatch(file.Name, @"\\" + avoidPattern, RegexOptions.IgnoreCase)) { fileName.Add(directory.Name + "\\" + file.Name); } } } } } catch (Exception ex) { Pause(); aspect.Rethrow(ex); } return(fileName); }
// ---------------------------------------------------------------------------------------- /// <!-- EndemeAccess_HappyPath_tests --> /// <summary> /// Methods used as intended, no null, empty or border tests /// </summary> /// <remarks></remarks> private void EndemeAccess_HappyPath_tests() { string toEndemeTable = "Endeme"; Assert.ThingsAbout("EndemeAccess", "HappyPath"); InfoAspect aspect = new InfoAspect("Test", null, "", "", "", 0); EndemeAccess e = new EndemeAccess(); EndemeSet enSet1 = EndemeTests.WetlandAnimals; Guid setID1 = Guid.NewGuid(); int didOk1 = e.ToteEndemeSet(enSet1.WithId(setID1), connection, trx); Guid setID2 = e.GetEndemeSetId(enSet1.Label, connection, trx); EndemeSet enSet2 = e.OnEndemeSet(setID2, connection, trx); Endeme en1 = new Endeme(enSet2, "ABC", true); long endemeId = e.ToEndeme(en1, toEndemeTable, connection, trx); Endeme en2 = e.GetEndeme(endemeId, toEndemeTable, connection, trx); Endeme en3 = en2.Copy(); en3.Add("DEF"); int status = e.UpdateEndeme(en3, endemeId, toEndemeTable, aspect); Assert.That(setID2, Is.equal_to, setID1); Assert.That(enSet2.Count, Is.equal_to, 22); Assert.That(enSet2.Label, Is.equal_to, enSet1.Label); Assert.That(en2.ToString(), Is.equal_to, en1); Assert.That(en3.ToString(), Is.equal_to, "ABCDEF"); Assert.That(enSet1.SetId, Is.not_equal_to, Guid.Empty); _result += Assert.Conclusion; }
// ---------------------------------------------------------------------------------------- /// <!-- ElementExists --> /// <summary> /// Looks for element exists on direct id transfer transformations (instructions) /// </summary> /// <param name="module"></param> /// <param name="field"></param> /// <param name="tableName"></param> /// <param name="aspect"></param> /// <returns></returns> protected bool ElementExists(string tableName, DataRow field, InfoAspect aspect) { bool exists = false; try { if (tableName == "Level_1") // "V" { TransformField transform = Transform["Level_1_ID"]; SqlInt32 extractPkValue = InData.GetSqlInt32(transform.XrefLookup); string loadPkColumn = ColumnName_LoadId(tableName, aspect.Enter()); aspect--; exists = CheckElementExists(tableName, loadPkColumn, extractPkValue, aspect.Enter()); aspect--; } else { // ------------------------------------------------------------------ // The normal direct 1 to 1 conversion case // ------------------------------------------------------------------ string extractPkColumn = ColumnName_ExtractPkFromLoad(tableName, aspect.Enter()); aspect--; SqlInt32 extractPkValue = InData.GetSqlInt32(field, extractPkColumn); string loadPkColumn = ColumnName_LoadId(tableName, aspect.Enter()); aspect--; exists = CheckElementExists(tableName, loadPkColumn, extractPkValue, aspect.Enter()); aspect--; } } catch (Exception ex) { Pause(); aspect.Rethrow(ex); } return(exists); }
// ---------------------------------------------------------------------------------------- /// <!-- AddTwoValuesTo --> /// <summary> /// Adds two values and possibly two columns to an error table /// </summary> /// <param name="errorTable"></param> /// <param name="data"></param> /// <param name="column1"></param> /// <param name="column2"></param> /// <param name="value1"></param> /// <param name="value2"></param> /// <returns></returns> public static bool AddTwoValuesTo(DataTable errorTable, DataRow data , string column1, string column2, string value1, string value2, bool isRowInsertedIntoErrorTable, int level) { InfoAspect.Measuring("AddTwoValuesTo"); DataColumn col1 = new DataColumn(column1, typeof(string)); DataColumn col2 = new DataColumn(column2, typeof(string)); isRowInsertedIntoErrorTable = AddTwoValuesTo(errorTable, data, col1, col2, value1, value2, isRowInsertedIntoErrorTable, level + 1); InfoAspect.Measured("AddTwoValuesTo"); return(isRowInsertedIntoErrorTable); }
// ---------------------------------------------------------------------------------------- /// <!-- InitializeTable --> /// <summary> /// Initializes the error log table /// </summary> /// <param name="importData"></param> /// <returns></returns> public static DataTable InitializeStatusErrorStorage(DataTable importData, string tableName, int level) { InfoAspect.Measuring("InitializeStatusErrorStorage"); DataTable statusErrorLog = importData.Clone(); statusErrorLog.TableName = "ErrorLog"; statusErrorLog.Columns.Add("Status_Error_Code", typeof(string)); statusErrorLog.Columns.Add("Error_Message", typeof(string)); statusErrorLog.TableName = "StatusErrorLog"; importData.TableName = tableName; importData.Columns.Add(new DataColumn("IsValid", typeof(bool))); importData.Columns.Add(new DataColumn("IsUpdate", typeof(bool))); InfoAspect.Measured("InitializeStatusErrorStorage"); return(statusErrorLog); }
// ---------------------------------------------------------------------------------------- /// <!-- Import --> /// <summary> /// Imports a DataTable of location elements into one of the location tables /// </summary> /// <param name="importData"></param> /// <param name="aspect"></param> /// <returns></returns> public DataTable Import(DataTable importData, string tableName, InfoAspect aspect) { // -------------------------------------------------------------------------- // Initialize variables // -------------------------------------------------------------------------- DataTable importedData = ImportDalCommon.InitializeImportedDataTable(importData, tableName + "DbId", aspect.Enter()); aspect--; // the output table string insertQuery = InsertQuery(tableName, aspect.Enter()); aspect--; string updateQuery = UpdateQuery(tableName, aspect.Enter()); aspect--; for (int row = 0; row < importData.Rows.Count; ++row) { DataRow field = importData.Rows[row]; try { // ------------------------------------------------------------------ // Insert or Update a valid location record in one of four location tables // ------------------------------------------------------------------ bool isValid = (bool)field["IsValid"]; SqlInt32 id = SqlInt32.Null; if (isValid) { bool isUpdate = (bool)field["IsUpdate"]; if (!isUpdate) { isUpdate = ElementExists(tableName, field, aspect.Enter()); aspect--; } if (isUpdate) { id = Update(tableName, field, importedData, aspect.Enter(tableName, "Update")); aspect--; } else { id = Insert(insertQuery, tableName, field, importedData, aspect.Enter(tableName, "Insert")); aspect--; } } } catch (Exception ex) { Pause(); aspect.Rethrow(ex); } } return(importedData); }
// ---------------------------------------------------------------------------------------- /// <!-- CheckForError --> /// <summary> /// Checks for an error in a numeric value field and returns an error code /// if an error is found /// </summary> /// <param name="required"></param> /// <param name="field"></param> /// <param name="fieldName"></param> /// <param name="lo"></param> /// <param name="hi"></param> /// <param name="columnErrorCode"></param> /// <param name="nullErrorCode">null or blank but required</param> /// <param name="typeErrorCode">not a number</param> /// <param name="rangeErrorCode">out of range</param> /// <returns></returns> public static string CheckForError(int required, DataRow field, string fieldName, long lo, long hi , string columnErrorCode, string nullErrorCode, string typeErrorCode, string rangeErrorCode) { InfoAspect.Measuring("CheckForError(9)"); string errorCode = string.Empty; if (field.Table.Columns.Contains(fieldName)) // Check column name existence { SqlInt64 num = InData.GetSqlInt64(field, fieldName); if (num.IsNull) // Check numeric type validity { string strScore = field[fieldName].ToString(); if (string.IsNullOrEmpty(strScore)) { if (required >= 2) { errorCode = nullErrorCode; } // Check required data existence } else { errorCode = typeErrorCode; } } else { if (num < lo || num > hi) { errorCode = rangeErrorCode; } } // Check data range validity } else { errorCode = columnErrorCode; } InfoAspect.Measured("CheckForError(9)"); return(errorCode); }
// ---------------------------------------------------------------------------------------- /// <!-- CheckForError --> /// <summary> /// Checks for an error in a numeric code field and returns an error code /// if there is an error detected, otherwise returns blank /// </summary> /// <param name="required"></param> /// <param name="field"></param> /// <param name="fieldName"></param> /// <param name="options"></param> /// <param name="columnErrorCode"></param> /// <param name="nullErrorCode">null or blank but required</param> /// <param name="typeErrorCode">not a number</param> /// <param name="optionErrorCode">not in list</param> /// <returns></returns> public static string CheckForError(int required, DataRow field, string fieldName, Dictionary <int, int> options , string columnErrorCode, string nullErrorCode, string typeErrorCode, string optionErrorCode) { InfoAspect.Measuring("CheckForError(8a)"); string errorCode = string.Empty; if (field.Table.Columns.Contains(fieldName)) // Check column name existence { SqlInt32 num = InData.GetSqlInt32(field, fieldName); if (num.IsNull) // Check numeric type validity { string strScore = field[fieldName].ToString(); if (string.IsNullOrEmpty(strScore)) { if (required >= 2) { errorCode = nullErrorCode; } // Check required data existence } else { errorCode = typeErrorCode; } } else { if (!options.ContainsKey((int)num)) { errorCode = optionErrorCode; } } // Check data range validity } else { errorCode = columnErrorCode; } InfoAspect.Measured("CheckForError(8a)"); return(errorCode); }
// ---------------------------------------------------------------------------------------- /// <!-- CheckForError --> /// <summary> /// Check in database /// </summary> /// <param name="required"></param> /// <param name="field"></param> /// <param name="fieldName"></param> /// <param name="dataTable"></param> /// <param name="column"></param> /// <param name="columnErrorCode"></param> /// <param name="nullErrorCode"></param> /// <param name="formatErrorCode"></param> /// <param name="optionErrorCode"></param> /// <returns></returns> public static string CheckForError(int required, DataRow field, string fieldName, DataTable dataTable, string column , string columnErrorCode, string nullErrorCode, string formatErrorCode, string optionErrorCode) { InfoAspect.Measuring("CheckForError(10)"); string errorCode = string.Empty; if (field.Table.Columns.Contains(fieldName)) // Check column name existence { SqlString code = InData.GetSqlString(field, fieldName); if (code.IsNull || string.IsNullOrEmpty(code.ToString().Trim())) { if (required >= 2) { errorCode = nullErrorCode; } // Check required data existence } else { if (Regex.IsMatch(code.ToString(), "^'?[V0-9][0-9][0-9][.]?[0-9]*$", RegexOptions.IgnoreCase)) // Check data format validity { if (!InData.ContainsValue(dataTable, column, code)) { errorCode = optionErrorCode; } } else { errorCode = formatErrorCode; } } } else { errorCode = columnErrorCode; } InfoAspect.Measured("CheckForError(10)"); return(errorCode); }
// ---------------------------------------------------------------------------------------- /// <!-- CheckForError --> /// <summary> /// Checks for error on a string field and returns an error code /// if there is an error detected, otherwise returns blank /// </summary> /// <param name="required"></param> /// <param name="field"></param> /// <param name="fieldName"></param> /// <param name="pattern"></param> /// <param name="columnErrorCode"></param> /// <param name="nullErrorCode">null or blank but required</param> /// <param name="typeErrorCode">not matching pattern</param> /// <param name="optionErrorCode">unused</param> /// <returns></returns> public static string CheckForError(int required, DataRow field, string fieldName, string pattern , string columnErrorCode, string nullErrorCode, string typeErrorCode, string optionErrorCode) { InfoAspect.Measuring("CheckForError(8)"); string errorCode = string.Empty; if (field.Table.Columns.Contains(fieldName)) // Check column name existence { string str = field[fieldName].ToString(); // ---------------------------------------------------------------------- // Check for data existence and format validity // ---------------------------------------------------------------------- if (!string.IsNullOrEmpty(str.Trim())) { if (!Regex.IsMatch(str, pattern)) { errorCode = typeErrorCode; } } else { if (required >= 2) { errorCode = nullErrorCode; } } } else { errorCode = columnErrorCode; } InfoAspect.Measured("CheckForError(8)"); return(errorCode); }
// ---------------------------------------------------------------------------------------- /// <!-- RecordStatusError --> /// <summary> /// Adds an error row to the status-error log /// </summary> /// <param name="errorCode"></param> /// <param name="importRow">The row to be imported</param> /// <param name="statusErrorLog"></param> public static void RecordStatusError(string errorCode, DataRow importRow, DataTable statusErrorLog) { InfoAspect.Measuring("RecordStatusError"); int row = statusErrorLog.Rows.Count; if (Regex.IsMatch(errorCode, "03")) { Pause(); } statusErrorLog.ImportRow(importRow); statusErrorLog.Rows[row]["Status_Error_Code"] = errorCode; if (StatusError.ErrorCodeList.ContainsKey(errorCode)) { statusErrorLog.Rows[row]["Error_Message"] = StatusError.ErrorCodeList[errorCode]; } else { Pause(); } InfoAspect.Measured("RecordStatusError"); }
// ---------------------------------------------------------------------------------------- /// <!-- CheckElementExists --> /// <summary> /// /// </summary> /// <param name="tableName"></param> /// <param name="exportIdColumName"></param> /// <param name="id"></param> /// <param name="aspect"></param> /// <returns></returns> protected bool CheckElementExists(string tableName, string exportIdColumName, object id, InfoAspect aspect) { bool found = false; if (!InData.IsNull(id)) { try { string query = " SELECT * FROM " + tableName + " WHERE " + exportIdColumName + " = " + id.ToString(); // TODO: parameterize this DataTable table = InData.GetTable(tableName, query, aspect.SecondaryConnection); found = (table.Rows.Count > 0); } catch (Exception ex) { Pause(); aspect.Rethrow(ex); } } else { throw new DataException("null key not findable"); } return(found); }
} // In SOA when you cache something in a static variable you may need to add this to the application refresh code public static Guid MentalCharacteristics_insert(InfoAspect aspect) { _mind = null; return(new EndemeAccess().ToteEndemeSet(MentalCharacteristics.WithId(Guid.NewGuid()), aspect.SecondaryConn)); }
// ---------------------------------------------------------------------------------------- /// <!-- CheckForError --> /// <summary> /// looks to see if the particular field value is found in the particular table column etc. /// </summary> /// <param name="required"></param> /// <param name="field"></param> /// <param name="fieldName"></param> /// <param name="pattern"></param> /// <param name="tableName"></param> /// <param name="columnName"></param> /// <param name="errorCodeForColumnNameNotFound" ></param> /// <param name="errorCodeForRequiredDataMissing" ></param> /// <param name="errorCodeForValueIncorrectFormat"></param> /// <param name="errorCodeForValueNotFoundInTable"></param> /// <param name="errorCodeForExceedsDataLength" ></param> /// <param name="aspect"></param> /// <returns></returns> internal static string CheckForError(int required, int maxLength , DataRow field, string fieldName, string pattern, string tableName, string columnName , string errorCodeForColumnNameNotFound, string errorCodeForRequiredDataMissing , string errorCodeForValueIncorrectFormat, string errorCodeForValueNotFoundInTable , string errorCodeForExceedsDataLength, InfoAspect aspect) { string errorCode = string.Empty; try { string str = ""; if (field.Table.Columns.Contains(fieldName)) { if (field[fieldName] != null) { str = field[fieldName].ToString(); } if (!string.IsNullOrEmpty(str.Trim())) { if (Regex.IsMatch(str, pattern)) { if (str.Length <= maxLength) { DataTable table = InData.GetTable(tableName , " SELECT *" + " FROM " + tableName + " WHERE " + columnName + " = '" + str + "'" , aspect.SecondaryConnection); if (table.Rows.Count == 0) { errorCode = errorCodeForValueNotFoundInTable; } } else { errorCode = errorCodeForExceedsDataLength; } } else { errorCode = errorCodeForValueIncorrectFormat; } } else { if (required > 0) { errorCode = errorCodeForRequiredDataMissing; } } } else { errorCode = errorCodeForColumnNameNotFound; } } catch (Exception ex) { Pause(); aspect.Rethrow(ex); } return(errorCode); }
// ---------------------------------------------------------------------------------------- /// <!-- CheckForError --> /// <summary> /// looks to see if the particular two part field value is found in the particular table column etc. /// </summary> /// <param name="required"></param> /// <param name="field"></param> /// <param name="fieldName1"></param> /// <param name="delim"></param> /// <param name="fieldName2"></param> /// <param name="lookup"></param> /// <param name="pattern"></param> /// <param name="tableName"></param> /// <param name="columnName"></param> /// <param name="errorCodeForColumnNameNotFound"></param> /// <param name="errorCodeForRequiredDataMissing"></param> /// <param name="errorCodeForValueIncorrectFormat"></param> /// <param name="errorCodeForValueNotFoundInTable"></param> /// <param name="aspect"></param> /// <returns></returns> public static string CheckForError(int required, DataRow field , string fieldName1, string delim, string fieldName2, NumericLookup lookup, string pattern , string tableName, string columnName , string errorCodeForColumnNameNotFound, string errorCodeForRequiredDataMissing , string errorCodeForValueIncorrectFormat, string errorCodeForValueNotFoundInTable, InfoAspect aspect) { string errorCode = string.Empty; try { string str1 = ""; if (field.Table.Columns.Contains(fieldName1) && field.Table.Columns.Contains(fieldName2)) { object obj1 = field[fieldName1]; if (obj1 != null) { str1 = obj1.ToString(); } string str2 = ""; //field[fieldName2].ToString(); object obj2 = field[fieldName2]; if (obj2 != null) { str2 = obj2.ToString(); } if (!string.IsNullOrEmpty(str1.Trim())) { if (Regex.IsMatch(str1, pattern)) { SqlInt32 importId = InData.GetSqlInt32(field, fieldName2); int realId = lookup[(int)importId]; string fullCode = str1 + delim + realId; DataTable table = InData.GetTable(tableName , " SELECT *" + " FROM " + tableName + " WHERE " + columnName + " = '" + fullCode + "'" , aspect.SecondaryConnection); if (table.Rows.Count == 0) { errorCode = errorCodeForValueNotFoundInTable; } } else { errorCode = errorCodeForValueIncorrectFormat; } } else { if (required > 0) { errorCode = errorCodeForRequiredDataMissing; } } } else { errorCode = errorCodeForColumnNameNotFound; } } catch (Exception ex) { Pause(); aspect.Rethrow(ex); } return(errorCode); }
// ---------------------------------------------------------------------------------------- /// <!-- CheckForError --> /// <summary> /// Checks an imported string for errors /// </summary> /// <param name="nullValue"></param> /// <param name="field"></param> /// <param name="extract"></param> /// <param name="aspect"></param> /// <returns></returns> public static string CheckForError(string nullValue, DataRow field, ExtractField extract, InfoAspect aspect) { string errorCode = string.Empty; if (field.Table.Columns.Contains(extract.Column)) { string str = field[extract.Column].ToString(); // ---------------------------------------------------------------------- // Check data validity // ---------------------------------------------------------------------- if (!string.IsNullOrEmpty(str.Trim()) && str != nullValue && str != "NULL") { // ------------------------------------------------------------------ // Check data validity // ------------------------------------------------------------------ string str2 = Regex.Replace(str, "^'", ""); if (!string.IsNullOrEmpty(extract.Pattern)) { if (!Regex.IsMatch(str.Trim(), extract.Pattern) && !Regex.IsMatch(str2.Trim(), extract.Pattern)) { errorCode = extract.ErrorFormat; } } if (str2.Length > extract.Length) { errorCode = extract.ErrorLength; } } else { if (extract.ImportField >= 2) { errorCode = extract.ErrorRequired; } } } else { errorCode = extract.ErrorFieldName; } return(errorCode); }
// ---------------------------------------------------------------------------------------- /// <!-- CheckForError --> /// <summary> /// Checks a date field in a DataTable for errors /// </summary> /// <param name="required"></param> /// <param name="field"></param> /// <param name="fieldName"></param> /// <param name="datePattern"></param> /// <param name="mustBeAfterDate"></param> /// <param name="mustBeBeforeDate"></param> /// <param name="errorCodeForColumnNameNotFound"></param> /// <param name="errorCodeForRequiredDataMissing"></param> /// <param name="errorCodeForValueIncorrectFormat"></param> /// <param name="errorCodeForValueOutOfRange"></param> /// <returns></returns> public static string CheckForError(int required, DataRow field, string fieldName , string datePattern, DateTime?mustBeAfterDate, DateTime?mustBeBeforeDate , string errorCodeForColumnNameNotFound, string errorCodeForRequiredDataMissing , string errorCodeForValueIncorrectFormat, string errorCodeForValueOutOfRange, InfoAspect aspect) { string errorCode = string.Empty; if (field.Table.Columns.Contains(fieldName)) { string str = field[fieldName].ToString(); // -------------------------------------------------------------------------- // Check data validity // -------------------------------------------------------------------------- if (string.IsNullOrEmpty(str.Trim())) { if (required >= 2) { errorCode = errorCodeForRequiredDataMissing; } } else { // ------------------------------------------------------------------ // Check data validity // ------------------------------------------------------------------ string str2 = Regex.Replace(str, "^'", ""); if (!string.IsNullOrEmpty(datePattern)) { if (!Regex.IsMatch(str, datePattern) && !Regex.IsMatch(str2, datePattern) ) { errorCode = errorCodeForValueIncorrectFormat; } } if (string.IsNullOrEmpty(errorCode.Trim())) { DateTime date; if (DateTime.TryParse(Regex.Replace(str2, "^'", ""), out date)) { if (mustBeAfterDate != null && date <= mustBeAfterDate) { errorCode = errorCodeForValueOutOfRange; } if (mustBeBeforeDate != null && date >= mustBeBeforeDate) { errorCode = errorCodeForValueOutOfRange; } } else { errorCode = errorCodeForValueIncorrectFormat; } } } } else { errorCode = errorCodeForColumnNameNotFound; } return(errorCode); }
// ---------------------------------------------------------------------------------------- /// <!-- CheckForErrors --> /// <summary> /// /// </summary> /// <param name="required"></param> /// <param name="field"></param> /// <param name="fieldName"></param> /// <param name="lookup"></param> /// <param name="errorCodeForColumnNameNotFound"></param> /// <param name="errorCodeForRequiredDataMissing"></param> /// <param name="errorCodeForValueIsNotAnInteger"></param> /// <param name="errorCodeForValueNotFoundInXref"></param> /// <returns></returns> public static string CheckForErrors(int required, DataRow field, string fieldName, NumericLookup lookup , string errorCodeForColumnNameNotFound, string errorCodeForRequiredDataMissing , string errorCodeForValueIsNotAnInteger, string errorCodeForValueNotFoundInXref, InfoAspect aspect) { string errorCode = string.Empty; if (field.Table.Columns.Contains(fieldName)) { string str = field[fieldName].ToString(); // ---------------------------------------------------------------------- // Check data validity // ---------------------------------------------------------------------- if (!string.IsNullOrEmpty(str.Trim()) && str != "NULL") { // ------------------------------------------------------------------ // Check data validity // ------------------------------------------------------------------ int id = 0; if (int.TryParse(str, out id)) { if (!lookup.ContainsKey(id)) { errorCode = errorCodeForValueNotFoundInXref; } } else { errorCode = errorCodeForValueIsNotAnInteger; } } else { if (required >= 2) { errorCode = errorCodeForRequiredDataMissing; } } } else { errorCode = errorCodeForColumnNameNotFound; } return(errorCode); }
// ---------------------------------------------------------------------------------------- /// <!-- CalculateImportStatus --> /// <summary> /// /// </summary> /// <param name="field"></param> /// <param name="statusErrorLog"></param> /// <returns></returns> internal static string CalculateImportStatus(DataRow field, DataTable statusErrorLog, InfoAspect aspect) { string importStatus = "Not Imported"; try { string fileLineNumber = field["FileLineNumber"].ToString(); DataRow[] statusErrorRow = statusErrorLog.Select("FileLineNumber = '" + fileLineNumber + "'"); if (statusErrorRow.Length > 0) { string statusErrorCode = (string)statusErrorRow[0]["Status_Error_Code"]; SqlBoolean isUpdate = InData.GetSqlBoolean(field, "IsUpdate"); if (isUpdate) { if (Regex.IsMatch(statusErrorCode, "00$")) { throw new DataMisalignedException("Conflicting information about row update vs insert."); } } else { if (Regex.IsMatch(statusErrorCode, "01$")) { throw new DataMisalignedException("Conflicting information about row update vs insert."); } } if (Regex.IsMatch(statusErrorCode, "00$")) { importStatus = "Inserted"; } if (Regex.IsMatch(statusErrorCode, "01$")) { importStatus = "Updated"; } if (Regex.IsMatch(statusErrorCode, "35[01]$")) { importStatus = "Imported"; } } } catch (Exception ex) { Pause(); aspect.Rethrow(ex); } return(importStatus); }
public static int MentalCharacteristics_update(InfoAspect aspect) { _mind = null; return(new EndemeAccess().ReUpEndemeSet(MentalCharacteristics.WithId(MentalCharacteristics_id(aspect)), aspect)); }
// ---------------------------------------------------------------------------------------- /// <!-- Update --> /// <summary> /// Updates a row in one of the location related tables /// </summary> /// <param name="updateQuery"></param> /// <param name="field"></param> /// <param name="importedData"></param> /// <param name="aspect"></param> private SqlInt32 Update(string tableName, DataRow field, DataTable importedData, InfoAspect aspect) { if (tableName == "Instructions") { string updateQuery = ""; object obj = null; using (SqlCommand cmd = new SqlCommand(updateQuery, aspect.SecondaryConnection)) { try { // -------------------------------------------------------------- // Fill the command // -------------------------------------------------------------- //UpdateQueryFill(cmd, tableName, field, aspect.Enter(Name, "QueryFill")); aspect--; foreach (string transformKey in Transform.Keys) { AddParameterToLoad(cmd, tableName, field, transformKey, aspect.Enter()); aspect--; } // -------------------------------------------------------------- // Perform the insert // -------------------------------------------------------------- obj = ExecuteScalar(cmd, tableName, aspect.Enter()); aspect--; string report = InData.AsciiNewQuery(cmd); if (obj != null) { SqlInt32 id = (SqlInt32)obj; // ------------------------------------------------------------------ // Record the insert // ------------------------------------------------------------------ if (id > 0) { int row = importedData.Rows.Count; importedData.ImportRow(field); importedData.Rows[row][tableName + "DbId"] = id; field[RealIdColumn] = id; } } else { string report2 = InData.AsciiNewQuery(cmd); } } catch (SqlException ex) { Pause(); string report = InData.AsciiNewQuery(cmd); aspect.Rethrow(ex); } catch (Exception ex) { Pause(); string report = InData.AsciiNewQuery(cmd); aspect.Rethrow(ex); } } } return(0); }
// ---------------------------------------------------------------------------------------- /// <!-- GetMostRecentImportFilesMatching --> /// <summary> /// Returns the most recent file in each directory matching the pattern and not matching the avoid pattern /// </summary> /// <param name="filePattern">files to include pattern</param> /// <param name="dirList"></param> /// <param name="avoidPattern">the pattern starting with the beginning of the file name (leave out ^ and \\)</param> /// <returns></returns> public static List <string> GetMostRecentImportFilesMatching(string filePattern , List <DirectoryInfo> dirList, string avoidPattern, InfoAspect aspect) { List <string> fileName = new List <string>(); try { Dictionary <string, string> newestFile = new Dictionary <string, string>(); foreach (DirectoryInfo directory in dirList) { List <FileInfo> fi = new List <FileInfo>(directory.GetFiles()); List <FileInfo> orderedFileList = fi.OrderBy(x => x.LastWriteTime).ToList(); foreach (FileInfo file in orderedFileList) { if (Regex.IsMatch(file.Name, filePattern, RegexOptions.IgnoreCase)) { if (!Regex.IsMatch(file.Name, "^" + avoidPattern, RegexOptions.IgnoreCase) && !Regex.IsMatch(file.Name, @"\\" + avoidPattern, RegexOptions.IgnoreCase)) { // ---------------------------------------------------------- // Replace older files (earlier in the list) with newer files of the same directory // ---------------------------------------------------------- string dirKey = directory.Name; if (!newestFile.ContainsKey(dirKey)) { newestFile.Add(dirKey, ""); } newestFile[dirKey] = directory.Name + "\\" + file.Name; } } } } foreach (string file in newestFile.Values) { fileName.Add(file); } } catch (Exception ex) { Pause(); aspect.Rethrow(ex); } return(fileName); }
// ---------------------------------------------------------------------------------------- /// <!-- InitializeInsertedDataTable --> /// <summary> /// /// </summary> /// <param name="importData"></param> /// <param name="idColumnName"></param> /// <returns></returns> public static DataTable InitializeImportedDataTable(DataTable importData, string idColumnName, InfoAspect aspect) { DataTable importedData = new DataTable(); importedData = importData.Clone(); importedData.TableName = "ImportedData"; importedData.Columns.Add(new DataColumn(idColumnName, typeof(SqlInt32))); return(importedData); }
// ---------------------------------------------------------------------------------------- /// <!-- ImportErrorInsert --> /// <summary> /// Inserts a module import error row /// </summary> /// <param name="query"></param> /// <param name="statusErrorLog"></param> /// <param name="row"></param> /// <param name="itemIdByLineNum"></param> /// <param name="connection"></param> /// <returns></returns> public static void ImportErrorInsert(string query, DataTable statusErrorLog, int row , string statusErrorCode, Dictionary <int, int> itemIdByLineNum, string moduleName, InfoAspect aspect) { SqlCommand errorInsert = new SqlCommand(query, aspect.SecondaryConnection); using (errorInsert) { // ------------------------------------------------------------------ // Insert the error into the error table // ------------------------------------------------------------------ int lineNumber = int.Parse(statusErrorLog.Rows[row]["FileLineNumber"].ToString()); int itemId = itemIdByLineNum[lineNumber]; ImportDalCommon.ImportErrorFill(errorInsert, itemId, statusErrorCode, statusErrorLog.Rows[row], moduleName); int errorId = (int)errorInsert.ExecuteScalar(); } }
// ---------------------------------------------------------------------------------------- /// <!-- Insert --> /// <summary> /// If the location element does not exist then insert into one of the four location tables /// </summary> /// <param name="insertQuery"></param> /// <param name="field"></param> /// <param name="importedData">adds row to this table if the location element is actually saved</param> /// <param name="aspect"></param> /// <returns></returns> private SqlInt32 Insert(string insertQuery, string tableName, DataRow field, DataTable importedData, InfoAspect aspect) { SqlInt32 id = SqlInt32.Null; object obj = null; using (SqlCommand cmd = new SqlCommand(insertQuery, aspect.SecondaryConnection)) { try { // ------------------------------------------------------------------ // Fill the command // ------------------------------------------------------------------ //InsertQueryFill(cmd, tableName, field, aspect.Enter(Name,"QueryFill")); aspect--; foreach (string transformKey in Transform.Keys) { AddParameterToLoad(cmd, tableName, field, transformKey, aspect.Enter()); aspect--; } // ------------------------------------------------------------------ // Sanity check // ------------------------------------------------------------------ string err = ParameterSanityCheck(cmd); if (!string.IsNullOrEmpty(err.Trim())) { throw new Exception(err); } // ------------------------------------------------------------------ // Perform the insert // ------------------------------------------------------------------ obj = ExecuteScalar(cmd, tableName, aspect.Enter()); aspect--; string report = InData.AsciiNewQuery(cmd); if (obj != null) { id = InData.GetSqlInt32(obj); // ------------------------------------------------------------------ // Record the insert // ------------------------------------------------------------------ if (id > 0) { int row = importedData.Rows.Count; importedData.ImportRow(field); importedData.Rows[row][tableName + "DbId"] = id; field[RealIdColumn] = id; } } else { string report2 = InData.AsciiNewQuery(cmd); } } catch (SqlException ex) { Pause(); string report = InData.AsciiNewQuery(cmd); aspect.Rethrow(ex); } catch (Exception ex) { Pause(); string report = InData.AsciiNewQuery(cmd); aspect.Rethrow(ex); } finally { // ------------------------------------------------------------------ // Make sure the database identity structure is set properly // ------------------------------------------------------------------ string query = " SET IDENTITY_INSERT [" + tableName + "] OFF;"; SqlCommand reset = new SqlCommand(query, aspect.SecondaryConnection); reset.ExecuteNonQuery(); } } return(id); }
// ---------------------------------------------------------------------------------------- /// <!-- InsertRunError --> /// <summary> /// Inserts a row into the Import_Run_Error table /// </summary> /// <param name="err"></param> /// <param name="importRunId"></param> public static int InsertRunError(string err, Exception ex, string contentType, int importRunId, InfoAspect aspect) { int importErrorId = 0; // -------------------------------------------------------------------------- // Build the parameterized import summary insert query // -------------------------------------------------------------------------- string query = " INSERT INTO dbo.Import_Run_Error" + " ( Level_2_ID" + " , Agency_ID" + " , Level_4_ID" + " , Content_Type" + " , Error_Type" + " , Error_Description" + " , Import_Run_ID" + " ) OUTPUT INSERTED.Import_Error_ID" + " VALUES " + " ( @Level_2_ID" + " , @Agency_ID" + " , @Level_4_ID" + " , @Content_Type" + " , @Error_Type" + " , @Error_Description" + " , @Import_Run_ID" + " )"; // -------------------------------------------------------------------------- // Run the insert // -------------------------------------------------------------------------- using (SqlCommand command = new SqlCommand(query, aspect.SecondaryConnection)) { // ------------------------------------------------------------------ // Fill the parameterized import error insert query // ------------------------------------------------------------------ string errorType = ""; if (ex != null) { errorType = ex.GetType().ToString(); } command.Parameters.AddWithValue("@Level_2_ID", 0); command.Parameters.AddWithValue("@Agency_ID", 0); command.Parameters.AddWithValue("@Level_4_ID", 0); command.Parameters.AddWithValue("@Content_Type", contentType); command.Parameters.AddWithValue("@Error_Type", errorType); command.Parameters.AddWithValue("@Error_Description", err); command.Parameters.AddWithValue("@Import_Run_ID", importRunId); // ------------------------------------------------------------------ // Insert the import error row // ------------------------------------------------------------------ importErrorId = (int)command.ExecuteScalar(); } return(importErrorId); }
// ---------------------------------------------------------------------------------------- /// <!-- Check --> /// <summary> /// /// </summary> /// <param name="listFromXref"></param> /// <param name="label"></param> /// <param name="tableName"></param> /// <param name="keyColumn"></param> /// <param name="valueColumn"></param> /// <param name="aspect"></param> /// <returns></returns> public static string Check(NumericLookup listFromXref, string label , string tableName, string keyColumn, string valueColumn, InfoAspect aspect) { Dictionary <int, int> listFromTable = InData.GetDictionaryFrom(tableName, keyColumn, valueColumn, aspect.SecondaryConnection); string err = ""; string delim = ""; foreach (int id in listFromXref.Values) { if (!listFromTable.ContainsKey(id)) { err += delim + label + " Xref ID missing from " + tableName + " table: " + id; delim = "\r\n"; } } return(err); }
private static EndemeSet _mind = null; //ABCDEFGHIJKLMNOPQRSTUV| // ------------------------------------------------------------------------------ // Methods // ------------------------------------------------------------------------------ public static Guid MentalCharacteristics_id(InfoAspect aspect) { return(new EndemeAccess().InEndemeSetOfEndemeSetLabel(MentalCharacteristicsLabel, aspect).GuidValue(0, "EndemeSetId", Guid.NewGuid())); }