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);
}
