public override bool ToSQL(string tablename, Guid executionID, StringBuilder sb, bool firstItem) { try { Int64 elapsed = 0; // Convert.ToInt64(ResponseTimestamp.Subtract(RequestTimestamp).TotalMilliseconds); if (!firstItem) { sb.Append(","); } // (FDAExecutionID, connectionID, DeviceAddress, Attempt, TimestampUTC1, TimestampUTC2, ElapsedPeriod, TransCode, TransStatus, ApplicationMessage,DBRGUID,DBRGIdx,DBRGSize,Details01,TxSize,Details02,RxSize,ProtocolNote,Protocol) values "); sb.Append("('"); sb.Append(executionID); sb.Append("','"); sb.Append(ConnectionID.ToString()); sb.Append("','"); sb.Append(DeviceAddress); sb.Append("',"); sb.Append(AttemptNum); sb.Append(",'"); sb.Append(DateTimeHelpers.FormatDateTime(EventTime)); sb.Append("','"); sb.Append(DateTimeHelpers.FormatDateTime(EventTime)); sb.Append("',"); sb.Append(elapsed); sb.Append(","); sb.Append(EventType); sb.Append(",CAST(1 as bit),"); sb.Append("'','"); // app message sb.Append(GroupID); sb.Append("','"); sb.Append(GroupIdx); sb.Append("',"); sb.Append(GroupSize); sb.Append(",'Tx: "); sb.Append(BitConverter.ToString(AckBytes)); sb.Append("',"); sb.Append(AckBytes.Length); sb.Append(",'N/A',0,'"); sb.Append(ProtocolNote); sb.Append("','"); sb.Append(Protocol); sb.Append("')"); } catch (Exception ex) { Globals.SystemManager.LogApplicationError(Globals.FDANow(), ex, "Error occurred while adding a comms log comms transaction item to an SQL insert batch"); return(false); } return(true); }
protected override int ExecuteNonQuery(string sql, string dbConnString) { int rowsaffected = -99; int retries = 0; int maxRetries = 3; Retry: using (NpgsqlConnection conn = new NpgsqlConnection(dbConnString)) { try { conn.Open(); } catch (Exception ex) { Globals.SystemManager.LogApplicationError(Globals.FDANow(), ex, "ExecuteNonQuery() Failed to connect to database"); return(-99); } try { using (NpgsqlCommand sqlCommand = conn.CreateCommand()) { retries++; sqlCommand.CommandText = sql; rowsaffected = sqlCommand.ExecuteNonQuery(); } } catch (Exception ex) { retries++; if (retries < maxRetries) { Thread.Sleep(250); goto Retry; } else { Globals.SystemManager.LogApplicationError(Globals.FDANow(), ex, "ExecuteNonQuery() Failed to execute query after " + (maxRetries + 1) + " attempts. Query = " + sql); return(-99); } } conn.Close(); } return(rowsaffected); }
protected override object ExecuteScalar(string sql, string dbConnString) { int maxRetries = 3; int retries = 0; Retry: using (NpgsqlConnection conn = new NpgsqlConnection(dbConnString)) { try { conn.Open(); } catch (Exception ex) { Globals.SystemManager.LogApplicationError(Globals.FDANow(), ex, "ExecuteScalar() Failed to connect to database"); return(null); } using (NpgsqlCommand sqlCommand = conn.CreateCommand()) { sqlCommand.CommandText = sql; try { object result = sqlCommand.ExecuteScalar(); return(result); } catch (Exception ex) { retries++; if (retries < maxRetries) { Thread.Sleep(250); goto Retry; } else { Globals.SystemManager.LogApplicationError(Globals.FDANow(), ex, "ExecuteScalar(" + sql + ") Failed to execute query after " + (maxRetries + 1) + " attempts."); return(null); } } } } }
protected override DataTable ExecuteQuery(string sql, string dbConnString) { int retries = 0; int maxRetries = 3; DataTable result = new DataTable(); Retry: using (NpgsqlConnection conn = new NpgsqlConnection(dbConnString)) { try { conn.Open(); } catch (Exception ex) { Globals.SystemManager.LogApplicationError(Globals.FDANow(), ex, "ExecuteQuery() Failed to connect to database"); return(result); } try { using (NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn)) { da.Fill(result); } } catch (Exception ex) { retries++; if (retries < maxRetries) { Thread.Sleep(250); goto Retry; } else { Globals.SystemManager.LogApplicationError(Globals.FDANow(), ex, "ExecuteQuery() Failed to execute query after " + (maxRetries + 1) + " attempts. Query = " + sql); return(result); } } } return(result); }
public override bool ToSQL(string tablename, Guid executionID, StringBuilder sb, bool firstItem) { // (FDAExecutionID, connectionID, DeviceAddress, Attempt, TimestampUTC1, TimestampUTC2, ElapsedPeriod, TransCode, TransStatus, ApplicationMessage,DBRGUID,DBRGIdx,DBRGSize,Details01,TxSize,Details02,RxSize,ProtocolNote,Protocol) values "); try { DateTime endTime = StartTime.Add(Elapsed); if (!firstItem) { sb.Append(","); } sb.Append("('"); sb.Append(executionID); sb.Append("','"); sb.Append(ConnectionID.ToString()); sb.Append("','',"); sb.Append(Attempt); sb.Append(",'"); sb.Append(DateTimeHelpers.FormatDateTime(StartTime)); sb.Append("','"); sb.Append(DateTimeHelpers.FormatDateTime(endTime)); sb.Append("',"); sb.Append(Elapsed.TotalMilliseconds); sb.Append(","); sb.Append(EventType); sb.Append(","); sb.Append("cast(" + Status + " as bit)"); sb.Append(",'"); sb.Append(Message.Replace("'", "''")); sb.Append("','00000000-0000-0000-0000-000000000000','0',0,'N/A',0,'N/A',0,'N/A','N/A')"); } catch (Exception ex) { Globals.SystemManager.LogApplicationError(Globals.FDANow(), ex, "Error occurred while adding a comms log connection event to an SQL insert batch"); return(false); } return(true); }
public override bool ToSQL(string tablename, Guid executionID, StringBuilder sb, bool firstItem) { // generate the SQL for inserting this log item into the database (will be appended to a larger query for inserting several records in a single query) // the entire insert query looks like this: // Insert into CommsLog (FDAExecutionID, connectionID, DeviceAddress, Attempt, TimestampUTC1, TimestampUTC2, ElapsedPeriod, TransCode, TransStatus, ApplicationMessage,DBRGUID,DBRGIdx,DBRGSize,Details01,TxSize,Details02,RxSize,ProtocolNote,Protocol) values ") // values (log item 1),(log item 2), etc... try { string timestampStr = DateTimeHelpers.FormatDateTime(Timestamp); if (!firstItem) { sb.Append(","); } sb.Append("('"); sb.Append(executionID); sb.Append("','"); sb.Append(ConnectionID.ToString()); sb.Append("','',0,'"); sb.Append(timestampStr); sb.Append("','"); sb.Append(timestampStr); sb.Append("',0,"); sb.Append(EventType); sb.Append(",cast(1 as bit),'"); sb.Append(Message.Replace("'", "''")); sb.Append("','00000000-0000-0000-0000-000000000000','0',0,'N/A',0,'N/A',0,'N/A','N/A')"); } catch (Exception ex) { Globals.SystemManager.LogApplicationError(Globals.FDANow(), ex, "Error occurred while adding a comms log comms event item to an SQL insert batch"); return(false); } return(true); }
public override bool ToSQL(string tablename, Guid executionID, StringBuilder sb, bool firstItem) { // generate the SQL for inserting this log item into the database (will be appended to a larger query for inserting several records in a single query) // the entire insert query looks like this: // Insert into CommsLog (FDAExecutionID, connectionID, DeviceAddress, Attempt, TimestampUTC1, TimestampUTC2, ElapsedPeriod, TransCode, TransStatus, ApplicationMessage,DBRGUID,DBRGIdx,DBRGSize,Details01,TxSize,Details02,RxSize,ProtocolNote,Protocol) // values (log item 1),(log item 2), etc... try { Int64 elapsed = Convert.ToInt64(ResponseTimestamp.Subtract(RequestTimestamp).TotalMilliseconds); if (!firstItem) { sb.Append(","); } sb.Append("('"); sb.Append(executionID); sb.Append("','"); sb.Append(ConnectionID.ToString()); sb.Append("','"); sb.Append(DeviceAddress); sb.Append("',"); sb.Append(AttemptNum); sb.Append(",'"); sb.Append(DateTimeHelpers.FormatDateTime(RequestTimestamp)); sb.Append("','"); sb.Append(DateTimeHelpers.FormatDateTime(ResponseTimestamp)); sb.Append("',"); sb.Append(elapsed); sb.Append(","); sb.Append(EventType); sb.Append(","); if (ResultStatus == DataRequest.RequestStatus.Success) { sb.Append("cast(1 as bit),"); } else { sb.Append("cast(0 as bit),"); } sb.Append("'"); sb.Append(ApplicationMessage); sb.Append("','"); sb.Append(GroupID); sb.Append("','"); sb.Append(GroupIdx); sb.Append("',"); sb.Append(GroupSize); sb.Append(",'Tx: "); sb.Append(BitConverter.ToString(RequestBytes)); sb.Append("',"); sb.Append(RequestBytes.Length); if (ResponseBytes != null) { sb.Append(",'Rx: "); sb.Append(BitConverter.ToString(ResponseBytes)); sb.Append("',"); sb.Append(ResponseBytes.Length); sb.Append(",'"); } else { sb.Append(",'N/A',0,'"); } sb.Append(Details.Replace("'", "''")); sb.Append("','"); sb.Append(Protocol); sb.Append("')"); } catch (Exception ex) { Globals.SystemManager.LogApplicationError(Globals.FDANow(), ex, "Error occurred while adding a comms log comms transaction item to an SQL insert batch"); return(false); } return(true); }