I have been stuck all day on this issue and cannot seem to find anything online pointing me to what might be causing it.
I have the below logging method in a Logger class and the below code calling the logger. When no exception occurs all the log statements work perfectly, however when an exception occurs the log statements do not run at all (however they do run from the web service call).
Logger Log Method:
public static Guid WriteToSLXLog(string ascendId, string masterDataId, string masterDataType, int? status,
string request, string requestRecieved, Exception ex, bool isError)
{
var connection = ConfigurationManager.ConnectionStrings["AscendConnectionString"];
string connectionString = "context connection=true";
// define INSERT query with parameters
var query =
"INSERT INTO " + AscendTable.SmartLogixLogDataTableName +
" (LogID, LogDate, AscendId, MasterDataId, MasterDataType, Status, Details, Request, RequestRecieved, StackTrace, IsError) " +
"VALUES (@LogID, @LogDate, @AscendId, @MasterDataId, @MasterDataType, @Status, @Details, @Request, @RequestRecieved, @StackTrace, @IsError)";
var logId = Guid.NewGuid();
using (var cn = new SqlConnection(connectionString))
{
if (!cn.State.Equals(ConnectionState.Open))
{
cn.Open();
}
// create command
using (var cmd = new SqlCommand(query, cn))
{
try
{
// define parameters and their values
cmd.Parameters.Add("@LogID", SqlDbType.UniqueIdentifier).Value = logId;
cmd.Parameters.Add("@LogDate", SqlDbType.DateTime).Value = DateTime.Now;
if (ascendId != null)
{
cmd.Parameters.Add("@AscendId", SqlDbType.VarChar, 24).Value = ascendId;
}
else
{
cmd.Parameters.Add("@AscendId", SqlDbType.VarChar, 24).Value = DBNull.Value;
}
cmd.Parameters.Add("@MasterDataId", SqlDbType.VarChar, 50).Value = masterDataId;
cmd.Parameters.Add("@MasterDataType", SqlDbType.VarChar, 50).Value = masterDataType;
if (ex == null)
{
cmd.Parameters.Add("@Status", SqlDbType.VarChar, 50).Value = status.ToString();
}
else
{
cmd.Parameters.Add("@Status", SqlDbType.VarChar, 50).Value = "2";
}
if (ex != null)
{
cmd.Parameters.Add("@Details", SqlDbType.VarChar, -1).Value = ex.Message;
if (ex.StackTrace != null)
{
cmd.Parameters.Add("@StackTrace", SqlDbType.VarChar, -1).Value =
ex.StackTrace;
}
else
{
cmd.Parameters.Add("@StackTrace", SqlDbType.VarChar, -1).Value = DBNull.Value;
}
}
else
{
cmd.Parameters.Add("@Details", SqlDbType.VarChar, -1).Value = "Success";
cmd.Parameters.Add("@StackTrace", SqlDbType.VarChar, -1).Value = DBNull.Value;
}
if (!string.IsNullOrEmpty(request))
{
cmd.Parameters.Add("@Request", SqlDbType.VarChar, -1).Value = request;
}
else
{
cmd.Parameters.Add("@Request", SqlDbType.VarChar, -1).Value = DBNull.Value;
}
if (!string.IsNullOrEmpty(requestRecieved))
{
cmd.Parameters.Add("@RequestRecieved", SqlDbType.VarChar, -1).Value = requestRecieved;
}
else
{
cmd.Parameters.Add("@RequestRecieved", SqlDbType.VarChar, -1).Value = DBNull.Value;
}
if (isError)
{
cmd.Parameters.Add("@IsError", SqlDbType.Bit).Value = 1;
}
else
{
cmd.Parameters.Add("@IsError", SqlDbType.Bit).Value = 0;
}
// open connection, execute INSERT, close connection
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
// Do not want to throw an error if something goes wrong logging
}
}
}
return logId;
}
My Method where the logging issues occur:
public static void CallInsertTruckService(string id, string code, string vinNumber, string licPlateNo)
{
Logger.WriteToSLXLog(id, code, MasterDataType.TruckType, 4, "1", "", null, false);
try
{
var truckList = new TruckList();
var truck = new Truck();
truck.TruckId = code;
if (!string.IsNullOrEmpty(vinNumber))
{
truck.VIN = vinNumber;
}
else
{
truck.VIN = "";
}
if (!string.IsNullOrEmpty(licPlateNo))
{
truck.Tag = licPlateNo;
}
else
{
truck.Tag = "";
}
if (!string.IsNullOrEmpty(code))
{
truck.BackOfficeTruckId = code;
}
truckList.Add(truck);
Logger.WriteToSLXLog(id, code, MasterDataType.TruckType, 4, "2", "", null, false);
if (truckList.Any())
{
// Call SLX web service
using (var client = new WebClient())
{
var uri = SmartLogixConstants.LocalSmartLogixIntUrl;
uri += "SmartLogixApi/PushTruck";
client.Headers.Clear();
client.Headers.Add("content-type", "application/json");
client.Headers.Add("FirestreamSecretToken", SmartLogixConstants.FirestreamSecretToken);
var serialisedData = JsonConvert.SerializeObject(truckList, new JsonSerializerSettings
{
ReferenceLoopHandling = ReferenceLoopHandling.Serialize
});
// HTTP POST
var response = client.UploadString(uri, serialisedData);
var result = JsonConvert.DeserializeObject<SmartLogixResponse>(response);
Logger.WriteToSLXLog(id, code, MasterDataType.TruckType, 4, "3", "", null, false);
if (result == null || result.ResponseStatus != 1)
{
// Something went wrong
throw new ApplicationException("Error in SLX");
}
Logger.WriteToSLXLog(id, code, MasterDataType.TruckType, result.ResponseStatus, serialisedData,
null, null, false);
}
}
}
catch (Exception ex)
{
Logger.WriteToSLXLog(id, code, MasterDataType.TruckType, 4, "4", "", null, false);
throw;
}
finally
{
Logger.WriteToSLXLog(id, code, MasterDataType.TruckType, 4, "5", "", null, false);
}
}
As you can see I have added several log statements throughout the method. All of these log statements except the one in the catch block are successful if no exception is thrown. If an exception is thrown then none of them are successful. For most of them the values are exactly the same whether or not there is an exception so I know its not an issue with the values being passed. I am thinking something weird is happening that causes a rollback or something, but I am not using a transaction or anything here. One last thing this DLL is being run through the SQL CLR which is why I am using "context connection=true" for my connection string.
Thanks in advance.
Edit:
I tried adding the following as my connection string but I get an exception when trying to .Open the connection now that says "Transaction context in use by another session". I am thinking this has to do with me calling this SQL CLR procedure through a trigger. The connection string I tried is
connectionString = "Trusted_Connection=true;Data Source=(local)\\AARONSQLSERVER;Initial Catalog=Demo409;Integrated Security=True;";
Also here is the trigger:
CREATE TRIGGER [dbo].[PushToSLXOnVehicleInsert]
ON [dbo].[Vehicle] AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @returnValue int
DECLARE @newLastModifiedDate datetime = null
DECLARE @currentId bigint = null
DECLARE @counter int = 0;
DECLARE @maxCounter int
DECLARE @currentCode varchar(24) = null
DECLARE @currentVinNumber varchar(24)
DECLARE @currentLicPlateNo varchar(30)
declare @tmp table
(
id int not null
primary key(id)
)
insert @tmp
select VehicleID from INSERTED
SELECT @maxCounter = Count(*) FROM INSERTED GROUP BY VehicleID
BEGIN TRY
WHILE (@counter < @maxCounter)
BEGIN
select top 1 @currentId = id from @tmp
SELECT @currentCode = Code, @currentVinNumber = VINNumber, @currentLicPlateNo = LicPlateNo FROM INSERTED WHERE INSERTED.VehicleID = @currentId
if (@currentId is not null)
BEGIN
EXEC dbo.SLX_CallInsertTruckService
@id = @currentId,
@code = @currentCode,
@vinNumber = @currentVinNumber,
@licPlateNo = @currentLicPlateNo
END
delete from @tmp where id = @currentId
set @counter = @counter + 1;
END
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
IF (@ErrorMessage like '%Error in SLX%')
BEGIN
SET @ErrorMessage = 'Error in SLX. Please contact SLX for more information.'
END
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH;
END
GO
