protected virtual void SaveSegment( SqlTransaction tran, DetachedSegment segment, int positionInInterchange, object interchangeId, object functionalGroupId = null, object transactionSetId = null, object parentLoopId = null, object loopId = null, int?revisionId = null, int?previousRevisionId = null, bool deleted = false) { if (!revisionId.HasValue || SegmentHasChanged(segment, positionInInterchange, interchangeId, previousRevisionId) || deleted) { _segmentBatch.AddSegment( tran, interchangeId, positionInInterchange, revisionId ?? 0, ConvertT(functionalGroupId), ConvertT(transactionSetId), ConvertT(parentLoopId), ConvertT(loopId), deleted, segment, _specs.ContainsKey(segment.SegmentId) ? _specs[segment.SegmentId] : null); if (tran != null || _segmentBatch._segmentTable.Rows.Count >= _batchSize) { ExecuteBatch(tran); } } }
private bool SegmentHasChanged( DetachedSegment segment, int positionInInterchange, object interchangeId, int?previousRevisionId) { using (var conn = new SqlConnection(this.Dsn)) { var cmd = new SqlCommand( string.Format( @"SELECT RevisionId, Deleted, Segment, r.RevisedBy, r.RevisionDate FROM [{0}].Segment s LEFT JOIN [{1}].Revision r ON s.RevisionId = r.Id WHERE InterchangeId = @interchangeId AND PositionInInterchange = @positionInInterchange ORDER BY RevisionId DESC", this.Schema, this.CommonDb.Schema), conn); cmd.Parameters.AddWithValue("@interchangeId", interchangeId); cmd.Parameters.AddWithValue("@positionInInterchange", positionInInterchange); conn.Open(); var reader = cmd.ExecuteReader(); if (reader.Read()) { if (Convert.ToBoolean(reader["Deleted"])) { throw new InvalidOperationException( string.Format( Resources.SegmentAlreadyDeletedError, segment.SegmentId, interchangeId, positionInInterchange, reader["RevisedBy"], reader["RevisionDate"])); } if (previousRevisionId.HasValue && Convert.ToInt64(reader["RevisionId"]) != Convert.ToInt64(previousRevisionId)) { throw new InvalidOperationException( string.Format( Resources.SegmentAlreadyRevisedError, segment.SegmentId, interchangeId, positionInInterchange, reader["RevisedBy"], reader["RevisionDate"])); } return(Convert.ToString(reader["Segment"]) != segment.SegmentString); } throw new InvalidOperationException( string.Format( Resources.SegmentDoesNotExist, interchangeId, positionInInterchange)); } }
private bool SegmentHasChanged( DetachedSegment segment, int positionInInterchange, object interchangeId, int?previousRevisionId) { using (var conn = new SqlConnection(_dsn)) { var cmd = new SqlCommand(string.Format(@" select RevisionId, Deleted, Segment, r.RevisedBy, r.RevisionDate from [{0}].Segment s left join [{1}].Revision r on s.RevisionId = r.Id where InterchangeId = @interchangeId and PositionInInterchange = @positionInInterchange order by RevisionId desc", _schema, _commonDb.Schema), conn); cmd.Parameters.AddWithValue("@interchangeId", interchangeId); cmd.Parameters.AddWithValue("@positionInInterchange", positionInInterchange); conn.Open(); var reader = cmd.ExecuteReader(); // only need to read first row if (reader.Read()) { if (Convert.ToBoolean(reader["Deleted"])) { throw new InvalidOperationException( string.Format( "Segment {0} of interchange {1} in position {2} has already been deleted by {3} at {4}.", segment.SegmentId, interchangeId, positionInInterchange, reader["RevisedBy"], reader["RevisionDate"])); } if (previousRevisionId.HasValue && Convert.ToInt64(reader["RevisionId"]) != Convert.ToInt64(previousRevisionId)) { throw new InvalidOperationException( string.Format( "Segment {0} of interchange {1} in position {2} has already been revised by {3} at {4}.", segment.SegmentId, interchangeId, positionInInterchange, reader["RevisedBy"], reader["RevisionDate"])); } return(Convert.ToString(reader["Segment"]) != segment.SegmentString); } throw new InvalidOperationException( string.Format( "A segment does not exist for interchange {0} at position {1}.", interchangeId, positionInInterchange)); } }
public void AddSegment( SqlTransaction tran, object interchangeId, int positionInInterchange, int revisionId, object functionalGroupId, object transactionSetId, object parentLoopId, object loopId, bool deleted, DetachedSegment segment, SegmentSpecification spec) { this.SegmentTable.Rows.Add( interchangeId, positionInInterchange, revisionId, functionalGroupId, transactionSetId, parentLoopId, loopId, deleted, segment.SegmentId, segment.SegmentString); if (spec != null) { var parsingError = new StringBuilder(); var fieldNames = new List <string>(); int maxElements = spec.Elements.Count; for (var i = 1; i == 1 || i <= maxElements; i++) { fieldNames.Add($"{i:00}"); } if (!this.ParsedTables.ContainsKey(segment.SegmentId)) { this.ParsedTables.Add(segment.SegmentId, new DataTable()); this.ParsedTables[segment.SegmentId].Columns.Add("InterchangeId", this.identityType); this.ParsedTables[segment.SegmentId].Columns.Add("PositionInInterchange", typeof(int)); this.ParsedTables[segment.SegmentId].Columns.Add("TransactionSetId", this.identityType); this.ParsedTables[segment.SegmentId].Columns.Add("ParentLoopId", this.identityType); this.ParsedTables[segment.SegmentId].Columns.Add("LoopId", this.identityType); this.ParsedTables[segment.SegmentId].Columns.Add("RevisionId", typeof(int)); this.ParsedTables[segment.SegmentId].Columns.Add("Deleted", typeof(bool)); foreach (string f in fieldNames) { this.ParsedTables[segment.SegmentId].Columns.Add(f, typeof(string)); } this.ParsedTables[segment.SegmentId].Columns.Add("ErrorId", this.identityType); } DataRow row = this.ParsedTables[segment.SegmentId].NewRow(); row["InterchangeId"] = interchangeId; row["PositionInInterchange"] = positionInInterchange; row["TransactionSetId"] = transactionSetId ?? DBNull.Value; row["ParentLoopId"] = parentLoopId ?? DBNull.Value; row["LoopId"] = loopId ?? DBNull.Value; row["RevisionId"] = revisionId; row["Deleted"] = deleted; for (var i = 1; i <= segment.ElementCount && i <= maxElements; i++) { try { string val = segment.GetElement(i); var elementSpec = spec.Elements[i - 1]; int maxLength = elementSpec.MaxLength; var column = $"{i:00}"; if (maxLength > 0 && val.Length > maxLength) { var message = string.Format( Resources.ElementTruncatedWarning, interchangeId, positionInInterchange, segment.SegmentId, i, maxLength); Trace.TraceInformation(message); parsingError.AppendLine(message); val = val.Substring(0, maxLength); } if (elementSpec.Type == ElementDataType.Numeric && elementSpec.ImpliedDecimalPlaces > 0) { int intVal; if (string.IsNullOrWhiteSpace(val)) { row[column] = null; } else if (int.TryParse(val, out intVal)) { var denominator = (decimal)Math.Pow(10, elementSpec.ImpliedDecimalPlaces); row[column] = intVal / denominator; } else { var message = string.Format( "Element {2}{3:00} in position {1} of interchange {0} cannot be indexed because '{4}' could not be parsed into an implied decimal with precision {5}.", interchangeId, positionInInterchange, segment.SegmentId, i, val, elementSpec.ImpliedDecimalPlaces); Trace.TraceInformation(message); parsingError.AppendLine(message); row[column] = null; } } else if (elementSpec.Type == ElementDataType.Numeric || elementSpec.Type == ElementDataType.Decimal) { decimal decVal; if (string.IsNullOrWhiteSpace(val)) { row[column] = null; } else if (decimal.TryParse(val, out decVal)) { row[column] = decVal; } else { var message = string.Format( "Element {2}{3:00} in position {1} of interchange {0} cannot be indexed because '{4}' could not be parsed into a decimal.", interchangeId, positionInInterchange, segment.SegmentId, i, val); Trace.TraceInformation(message); parsingError.AppendLine(message); row[column] = null; } } else if (elementSpec.Type == ElementDataType.Date) { if (string.IsNullOrWhiteSpace(val)) { row[column] = null; } else { DateTime date; if (val.Length == 8 && DateTime.TryParse( $"{val.Substring(0, 4)}-{val.Substring(4, 2)}-{val.Substring(6, 2)}", out date)) { row[column] = date; } else { var message = string.Format( "Element {2}{3:00} in position {1} of interchange {0} cannot be indexed because '{4}' could not be parsed into a date.", interchangeId, positionInInterchange, segment.SegmentId, i, val); Trace.TraceInformation(message); parsingError.AppendLine(message); row[column] = null; } } } else { row[column] = val; } } catch (Exception e) { var message = string.Format( "Error parsing '{0}' using spec {1} with {2} elements: {3}", segment.SegmentString, spec.SegmentId, spec.Elements.Count(), e.Message); Trace.TraceInformation(message); parsingError.AppendLine(message); } } if (parsingError.Length > 0) { row["ErrorId"] = this.errorRepo.PersistParsingError( interchangeId, positionInInterchange, revisionId, parsingError.ToString()); } this.ParsedTables[segment.SegmentId].Rows.Add(row); } }
public RepoSegment(string segmentString, char segmentTerminator, char elementSeparator, char componentSeparator) { Segment = new DetachedSegment( new X12DelimiterSet(segmentTerminator, elementSeparator, componentSeparator), segmentString); }
protected virtual void SaveSegment(SqlTransaction tran, DetachedSegment segment, int positionInInterchange, T interchangeId, T?functionalGroupId = null, T?transactionSetId = null, T?parentLoopId = null, T?loopId = null, int?revisionId = null, int?previousRevisionId = null, bool deleted = false) { if (!revisionId.HasValue || SegmentHasChanged(segment, positionInInterchange, interchangeId, previousRevisionId) || deleted) { string segmentSql = string.Format(@" INSERT INTO [{0}].[Segment] (InterchangeId, FunctionalGroupId, TransactionSetId, ParentLoopId, LoopId, RevisionId, Deleted, PositionInInterchange, SegmentId, Segment) VALUES ({1}, {2}, {3}, {4}, {5}, isnull({6},0), {7}, {8}, '{9}', '{10}') ", _schema, string.Format("'{0}'", interchangeId), (object)functionalGroupId == null ? "NULL" : string.Format("'{0}'", functionalGroupId), (object)transactionSetId == null ? "NULL" : string.Format("'{0}'", transactionSetId), (object)parentLoopId == null ? "NULL" : string.Format("'{0}'", parentLoopId), (object)loopId == null ? "NULL" : string.Format("'{0}'", loopId), (object)revisionId == null ? "NULL" : string.Format("'{0}'", revisionId), deleted ? "1" : "0", positionInInterchange, segment.SegmentId.Replace("'", "''"), segment.SegmentString.Replace("'", "''")).Replace("{", "{{").Replace("}", "}}"); if (tran != null) { SqlCommand cmd = new SqlCommand(segmentSql); cmd.Connection = tran.Connection; cmd.Transaction = tran; ExecuteCmd(cmd); } else { AddSqlToBatch(segmentSql); } if (_specs.ContainsKey(segment.SegmentId)) { StringBuilder parsingError = new StringBuilder(); List <string> fieldNames = new List <string>(); List <string> parameterNames = new List <string>(); var spec = _specs[segment.SegmentId]; int maxElements = spec != null ? spec.Elements.Count : 0; for (int i = 1; i == 1 || i <= segment.ElementCount; i++) { if (i <= maxElements) { fieldNames.Add(string.Format("[{0:00}]", i)); } else { string val = segment.GetElement(i); string message = string.Format("Element {2}{3:00} in position {1} of interchange {0} with value {4} will NOT be indexed because it exceeds the {5} specified number of elements.", interchangeId, positionInInterchange, segment.SegmentId, i, val, maxElements); Trace.TraceInformation(message); parsingError.AppendLine(message); } } StringBuilder sql = new StringBuilder(); sql.AppendFormat(@"INSERT INTO [{0}].[{1}] (InterchangeId, PositionInInterchange, TransactionSetId, ParentLoopId, LoopId, RevisionId, Deleted, {2}, ErrorId) VALUES ({3}, {4}, {5}, {6}, {7}, isnull({8},0), {9}, ", _schema, segment.SegmentId, string.Join(",", fieldNames), string.Format("'{0}'", interchangeId), positionInInterchange, (object)transactionSetId == null ? "NULL" : string.Format("'{0}'", transactionSetId), (object)parentLoopId == null ? "NULL" : string.Format("'{0}'", parentLoopId), (object)loopId == null ? "NULL" : string.Format("'{0}'", loopId), (object)revisionId == null ? "NULL" : string.Format("'{0}'", revisionId), deleted ? "1" : "0"); if (segment.ElementCount == 0) { sql.AppendFormat("NULL, "); } else { for (int i = 1; i <= segment.ElementCount && i <= maxElements; i++) { string val = segment.GetElement(i); if (spec != null) { var elementSpec = spec.Elements[i - 1]; int maxLength = elementSpec.MaxLength; if (maxLength > 0 && val.Length > maxLength) { string message = string.Format("Element {2}{3:00} in position {1} of interchange {0} will be truncated because {4} exceeds the max length of {5}.", interchangeId, positionInInterchange, segment.SegmentId, i, val, maxLength); Trace.TraceInformation(message); parsingError.AppendLine(message); val = val.Substring(0, maxLength); } if (elementSpec.Type == ElementDataTypeEnum.Numeric && elementSpec.ImpliedDecimalPlaces > 0) { int intVal = 0; if (string.IsNullOrWhiteSpace(val)) { sql.Append("NULL, "); } else if (int.TryParse(val, out intVal)) { decimal denominator = (decimal)Math.Pow(10, elementSpec.ImpliedDecimalPlaces); sql.AppendFormat("{0}, ", (decimal)intVal / denominator); } else { string message = string.Format("Element {2}{3:00} in position {1} of interchange {0} cannot be indexed because '{4}' could not be parsed into an implied decimal with precision {5}.", interchangeId, positionInInterchange, segment.SegmentId, i, val, elementSpec.ImpliedDecimalPlaces); Trace.TraceInformation(message); parsingError.AppendLine(message); sql.AppendFormat("NULL, "); } } else if (elementSpec.Type == ElementDataTypeEnum.Numeric || elementSpec.Type == ElementDataTypeEnum.Decimal) { decimal decVal = 0; if (string.IsNullOrWhiteSpace(val)) { sql.Append("NULL, "); } else if (decimal.TryParse(val, out decVal)) { sql.AppendFormat("{0}, ", val); } else { string message = string.Format("Element {2}{3:00} in position {1} of interchange {0} cannot be indexed because '{4}' could not be parsed into a decimal.", interchangeId, positionInInterchange, segment.SegmentId, i, val); Trace.TraceInformation(message); parsingError.AppendLine(message); sql.AppendFormat("NULL, "); } } else if (elementSpec.Type == ElementDataTypeEnum.Date) { DateTime date = DateTime.MinValue; if (val.Length == 8 && DateTime.TryParse(string.Format("{0}-{1}-{2}", val.Substring(0, 4), val.Substring(4, 2), val.Substring(6, 2)), out date)) { sql.AppendFormat("'{0}', ", val.Replace("'", "''")); } else { string message = string.Format("Element {2}{3:00} in position {1} of interchange {0} cannot be indexed because '{4}' could not be parsed into a date.", interchangeId, positionInInterchange, segment.SegmentId, i, val); Trace.TraceInformation(message); parsingError.AppendLine(message); sql.AppendFormat("NULL, "); } } else { sql.AppendFormat("'{0}', ", val.Replace("'", "''")); } } } } T? errorId = null; string errorMessage = parsingError.ToString(); if (!string.IsNullOrWhiteSpace(errorMessage)) { var errSql = string.Format(@" INSERT INTO [{0}].ParsingError (InterchangeId, PositionInInterchange, RevisionId, Message) VALUES (@interchangeId, @positionInInterchange, isnull(@revisionId,0), @message) SELECT scope_identity()", _schema); if (typeof(T) == typeof(Guid)) { errSql = string.Format(@"DECLARE @id uniqueidentifier SET @id = newid() INSERT INTO [{0}].ParsingError (Id, InterchangeId, PositionInInterchange, RevisionId, Message) VALUES (@id,@interchangeId, @positionInInterchange, isnull(@revisionId,0), @message) SELECT @id", _schema); } SqlCommand errCmd = new SqlCommand(errSql.Replace("{", "{{").Replace("}", "}}")); errCmd.Parameters.AddWithValue("@interchangeId", interchangeId); errCmd.Parameters.AddWithValue("@positionInInterchange", positionInInterchange); errCmd.Parameters.AddWithValue("@revisionId", (object)revisionId ?? DBNull.Value); errCmd.Parameters.AddWithValue("@message", errorMessage); errorId = ConvertT(ExecuteScalar(errCmd)); } sql.AppendFormat("{0})", (object)errorId == null ? "NULL" : string.Format("'{0}'", errorId)); if (tran != null) { var cmd = new SqlCommand(sql.ToString().Replace("{", "{{").Replace("}", "}}")); cmd.Connection = tran.Connection; cmd.Transaction = tran; ExecuteCmd(cmd); } else { AddSqlToBatch(sql.ToString().Replace("{", "{{").Replace("}", "}}")); } } } }