/// <summary>
/// Logs all name/value pairs as a single collection using concurrent MERGE statements.
/// </summary>
/// <param name="conn"></param>
/// <param name="exCollectionID"></param>
/// <param name="coll"></param>
/// <returns></returns>
async Task LogCollection(SqlConnectionContext conn, SHA1Hash exCollectionID, IDictionary<string, string> coll)
{
// The exCollectionID should be pre-calculated by `CalcCollectionID`.
// Check if the exCollectionID exists already:
int? collectionCount = await conn.ExecReader(
@"SELECT COUNT(exCollectionID) FROM [dbo].[exCollectionKeyValue] WHERE [exCollectionID] = @exCollectionID",
prms =>
prms.AddInParamSHA1("@exCollectionID", exCollectionID),
async dr => await dr.ReadAsync() ? dr.GetInt32(0) : (int?)null
);
// Don't bother logging name-value pairs if the collection already exists:
if (!collectionCount.HasValue) return;
if (collectionCount.Value == coll.Count) return;
const int numTasksPerPair = 2;
// Create an array of tasks to wait upon:
var tasks = new Task[coll.Count * numTasksPerPair];
// Fill out the array of tasks with concurrent MERGE statements for each name/value pair:
using (var en = coll.Keys.GetEnumerator())
for (int i = 0; en.MoveNext(); ++i)
{
string name = en.Current;
string value = coll[name];
var exCollectionValueID = Hash.SHA1(value);
// Merge the Value record:
tasks[i * numTasksPerPair + 0] = conn.ExecNonQuery(
@"MERGE [dbo].[exCollectionValue] WITH (HOLDLOCK) AS target
USING (SELECT @exCollectionValueID) AS source (exCollectionValueID)
ON (target.exCollectionValueID = source.exCollectionValueID)
WHEN NOT MATCHED THEN
INSERT ([exCollectionValueID], [Value])
VALUES (@exCollectionValueID, @Value );",
prms =>
prms.AddInParamSHA1("@exCollectionValueID", exCollectionValueID)
.AddInParamSize("@Value", SqlDbType.VarChar, -1, value)
);
// Merge the Name-Value record:
tasks[i * numTasksPerPair + 1] = conn.ExecNonQuery(
@"MERGE [dbo].[exCollectionKeyValue] WITH (HOLDLOCK) AS target
USING (SELECT @exCollectionID, @Name, @exCollectionValueID) AS source (exCollectionID, Name, exCollectionValueID)
ON (target.exCollectionID = source.exCollectionID AND target.Name = source.Name AND target.exCollectionValueID = source.exCollectionValueID)
WHEN NOT MATCHED THEN
INSERT ([exCollectionID], [Name], [exCollectionValueID])
VALUES (@exCollectionID, @Name, @exCollectionValueID );",
prms =>
prms.AddInParamSHA1("@exCollectionID", exCollectionID)
.AddInParamSize("@Name", SqlDbType.VarChar, 96, name)
.AddInParamSHA1("@exCollectionValueID", exCollectionValueID)
);
}
// Our final task's completion depends on all the tasks created thus far:
await Task.WhenAll(tasks);
}
/// <summary>
/// Writes a URL with query-string to the exURLQuery table.
/// </summary>
/// <param name="conn"></param>
/// <param name="uri"></param>
/// <returns></returns>
async Task LogURLQuery(SqlConnectionContext conn, Uri uri)
{
// Log the base URL:
var urlID = CalcURLID(uri);
// Compute the URLQueryID:
var urlQueryID = CalcURLQueryID(uri);
// Store the exURL record:
var tskLogURL = LogURL(conn, uri);
// Store the exURLQuery record:
var tskLogURLQuery = conn.ExecNonQuery(
@"MERGE [dbo].[exURLQuery] WITH (HOLDLOCK) AS target
USING (SELECT @exURLQueryID) AS source (exURLQueryID)
ON (target.exURLQueryID = source.exURLQueryID)
WHEN NOT MATCHED THEN
INSERT ([exURLQueryID], [exURLID], [QueryString])
VALUES (@exURLQueryID, @exURLID, @QueryString);",
prms =>
prms.AddInParamSHA1("@exURLQueryID", urlQueryID)
.AddInParamSHA1("@exURLID", urlID)
.AddInParamSize("@QueryString", SqlDbType.VarChar, -1, uri.Query)
);
await Task.WhenAll(tskLogURLQuery, tskLogURL);
}
async Task LogWebContext(SqlConnectionContext conn, ExceptionPolicy policy, ExceptionWithCapturedContext ctx, int exInstanceID)
{
var http = ctx.CapturedHttpContext;
var host = ctx.WebHostingContext;
// We require both HTTP and Host context:
if (http == null || host == null)
return;
// Try to get the authenticated user for the HTTP context:
string authUserName = null;
if (http.User != null && http.User.Identity != null)
authUserName = http.User.Identity.Name;
// Compute the IDs:
var requestURLQueryID = CalcURLQueryID(http.Url);
var referrerURLQueryID = http.UrlReferrer == null ? (SHA1Hash?)null : CalcURLQueryID(http.UrlReferrer);
var exWebApplicationID = CalcWebApplicationID(host);
// Log the web application details:
var tskWebApplication = conn.ExecNonQuery(
@"MERGE [dbo].[exWebApplication] WITH (HOLDLOCK) AS target
USING (SELECT @exWebApplicationID) AS source (exWebApplicationID)
ON (target.exWebApplicationID = source.exWebApplicationID)
WHEN NOT MATCHED THEN
INSERT ([exWebApplicationID], [MachineName], [ApplicationID], [PhysicalPath], [VirtualPath], [SiteName])
VALUES (@exWebApplicationID, @MachineName, @ApplicationID, @PhysicalPath, @VirtualPath, @SiteName );",
prms =>
prms.AddInParamSHA1("@exWebApplicationID", exWebApplicationID)
.AddInParamSize("@MachineName", SqlDbType.NVarChar, 96, host.MachineName)
.AddInParamSize("@ApplicationID", SqlDbType.VarChar, 96, host.ApplicationID)
.AddInParamSize("@PhysicalPath", SqlDbType.NVarChar, 256, host.PhysicalPath)
.AddInParamSize("@VirtualPath", SqlDbType.NVarChar, 256, host.VirtualPath)
.AddInParamSize("@SiteName", SqlDbType.VarChar, 96, host.SiteName)
);
// Log the request headers collection, if requested and available:
Task tskCollection = null;
SHA1Hash? exCollectionID = null;
if (policy.LogWebRequestHeaders && http.Headers != null)
{
var tmpDict = new NameValueCollectionDictionary(http.Headers);
// Compute the collection hash (must be done BEFORE `tskContextWeb`):
exCollectionID = CalcCollectionID(tmpDict);
// Store all records for the headers collection:
tskCollection = LogCollection(conn, exCollectionID.Value, tmpDict);
}
// Log the web context:
var tskContextWeb = conn.ExecNonQuery(
@"INSERT INTO [dbo].[exContextWeb]
([exInstanceID], [exWebApplicationID], [AuthenticatedUserName], [HttpVerb], [RequestURLQueryID], [ReferrerURLQueryID], [RequestHeadersCollectionID])
VALUES (@exInstanceID, @exWebApplicationID, @AuthenticatedUserName, @HttpVerb, @RequestURLQueryID, @ReferrerURLQueryID, @RequestHeadersCollectionID );",
prms =>
prms.AddInParam("@exInstanceID", SqlDbType.Int, exInstanceID)
// Hosting environment:
.AddInParamSHA1("@exWebApplicationID", exWebApplicationID)
// Request details:
.AddInParamSize("@AuthenticatedUserName", SqlDbType.VarChar, 96, authUserName)
.AddInParamSize("@HttpVerb", SqlDbType.VarChar, 16, http.HttpMethod)
.AddInParamSHA1("@RequestURLQueryID", requestURLQueryID)
.AddInParamSHA1("@ReferrerURLQueryID", referrerURLQueryID)
.AddInParamSHA1("@RequestHeadersCollectionID", exCollectionID)
);
// Log the URLs:
Task tskRequestURL, tskReferrerURL;
tskRequestURL = LogURLQuery(conn, http.Url);
if (http.UrlReferrer != null)
tskReferrerURL = LogURLQuery(conn, http.UrlReferrer);
else
tskReferrerURL = null;
// Await the completion of the tasks:
await Task.WhenAll(tskRequestURL, tskWebApplication, tskContextWeb);
if (tskReferrerURL != null) await tskReferrerURL;
if (tskCollection != null) await tskCollection;
}
/// <summary>
/// Writes a URL without query-string to the exURL table.
/// </summary>
/// <param name="conn"></param>
/// <param name="uri"></param>
/// <returns></returns>
Task LogURL(SqlConnectionContext conn, Uri uri)
{
var urlID = CalcURLID(uri);
return conn.ExecNonQuery(
@"MERGE [dbo].[exURL] WITH (HOLDLOCK) AS target
USING (SELECT @exURLID) AS source (exURLID)
ON (target.exURLID = source.exURLID)
WHEN NOT MATCHED THEN
INSERT ([exURLID], [HostName], [PortNumber], [AbsolutePath], [Scheme])
VALUES (@exURLID, @HostName, @PortNumber, @AbsolutePath, @Scheme );",
prms =>
prms.AddInParamSHA1("@exURLID", urlID)
.AddInParamSize("@HostName", SqlDbType.VarChar, 128, uri.Host)
.AddInParam("@PortNumber", SqlDbType.Int, (int)uri.Port)
.AddInParamSize("@AbsolutePath", SqlDbType.VarChar, 512, uri.AbsolutePath)
.AddInParamSize("@Scheme", SqlDbType.VarChar, 8, uri.Scheme)
);
}
async Task<HashedLogIdentifier> LogExceptionRecursively(SqlConnectionContext conn, ExceptionWithCapturedContext ctx, int? parentInstanceID = null)
{
// Create the exTargetSite if it does not exist:
var ts = ctx.TargetSite;
SHA1Hash? exTargetSiteID = null;
Task tskTargetSite = null;
if (ts != null)
{
exTargetSiteID = ts.TargetSiteID;
tskTargetSite = conn.ExecNonQuery(
@"MERGE [dbo].[exTargetSite] WITH (HOLDLOCK) AS target
USING (SELECT @exTargetSiteID) AS source (exTargetSiteID)
ON (target.exTargetSiteID = source.exTargetSiteID)
WHEN NOT MATCHED THEN
INSERT ([exTargetSiteID], [AssemblyName], [TypeName], [MethodName], [ILOffset], [FileName], [FileLineNumber], [FileColumnNumber])
VALUES (@exTargetSiteID, @AssemblyName, @TypeName , @MethodName , @ILOffset , @FileName , @FileLineNumber , @FileColumnNumber );",
prms =>
prms.AddInParamSHA1("@exTargetSiteID", exTargetSiteID.GetValueOrDefault())
.AddInParamSize("@AssemblyName", SqlDbType.NVarChar, 256, ts.AssemblyName)
.AddInParamSize("@TypeName", SqlDbType.NVarChar, 256, ts.TypeName)
.AddInParamSize("@MethodName", SqlDbType.NVarChar, 256, ts.MethodName)
.AddInParam("@ILOffset", SqlDbType.Int, ts.ILOffset)
.AddInParamSize("@FileName", SqlDbType.NVarChar, 256, ts.FileName)
.AddInParam("@FileLineNumber", SqlDbType.Int, ts.FileLineNumber)
.AddInParam("@FileColumnNumber", SqlDbType.Int, ts.FileColumnNumber)
);
}
SHA1Hash? userStateCollectionID = null;
if (ctx.UserState != null)
{
userStateCollectionID = CalcCollectionID(ctx.UserState);
}
// Create the exException record if it does not exist:
var tskGetPolicy = conn.ExecReader(
@"MERGE [dbo].[exException] WITH (HOLDLOCK) AS target
USING (SELECT @exExceptionID) AS source (exExceptionID)
ON (target.exExceptionID = source.exExceptionID)
WHEN NOT MATCHED THEN
INSERT ([exExceptionID], [AssemblyName], [TypeName], [StackTrace], [exTargetSiteID])
VALUES (@exExceptionID, @AssemblyName, @TypeName, @StackTrace, @exTargetSiteID );
SELECT excpol.[LogWebContext], excpol.[LogWebRequestHeaders]
FROM [dbo].[exExceptionPolicy] excpol WITH (NOLOCK)
WHERE excpol.[exExceptionID] = @exExceptionID;",
prms =>
prms.AddInParamSHA1("@exExceptionID", ctx.ExceptionID)
.AddInParamSize("@AssemblyName", SqlDbType.NVarChar, 256, ctx.AssemblyName)
.AddInParamSize("@TypeName", SqlDbType.NVarChar, 256, ctx.TypeName)
.AddInParamSize("@StackTrace", SqlDbType.NVarChar, -1, ctx.StackTrace)
.AddInParamSHA1("@exTargetSiteID", exTargetSiteID),
// Read the SELECT result set into an ExceptionPolicy, or use the default policy:
async dr =>
!await dr.ReadAsync()
? ExceptionPolicy.Default
: new ExceptionPolicy(
logWebContext: dr.GetBoolean(dr.GetOrdinal("LogWebContext")),
logWebRequestHeaders: dr.GetBoolean(dr.GetOrdinal("LogWebRequestHeaders"))
)
);
// Create the exException record if it does not exist:
var exApplicationID = CalcApplicationID(cfg);
var tskApplication = conn.ExecNonQuery(
@"MERGE [dbo].[exApplication] WITH (HOLDLOCK) AS target
USING (SELECT @exApplicationID) AS source (exApplicationID)
ON (target.exApplicationID = source.exApplicationID)
WHEN NOT MATCHED THEN
INSERT ([exApplicationID], [MachineName], [ApplicationName], [EnvironmentName], [ProcessPath])
VALUES (@exApplicationID, @MachineName, @ApplicationName, @EnvironmentName, @ProcessPath );",
prms =>
prms.AddInParamSHA1("@exApplicationID", exApplicationID)
.AddInParamSize("@MachineName", SqlDbType.VarChar, 64, cfg.MachineName)
.AddInParamSize("@ApplicationName", SqlDbType.VarChar, 96, cfg.ApplicationName)
.AddInParamSize("@EnvironmentName", SqlDbType.VarChar, 32, cfg.EnvironmentName)
.AddInParamSize("@ProcessPath", SqlDbType.NVarChar, 256, cfg.ProcessPath)
);
// Create the instance record:
var tskInstance = conn.ExecNonQuery(
@"INSERT INTO [dbo].[exInstance]
([exExceptionID], [exApplicationID], [LoggedTimeUTC], [SequenceNumber], [IsHandled], [ApplicationIdentity], [ParentInstanceID], [CorrelationID], [ManagedThreadId], [UserStateCollectionID], [Message])
VALUES (@exExceptionID, @exApplicationID, @LoggedTimeUTC, @SequenceNumber, @IsHandled, @ApplicationIdentity, @ParentInstanceID, @CorrelationID, @ManagedThreadId, @UserStateCollectionID, @Message );
SET @exInstanceID = SCOPE_IDENTITY();",
prms =>
prms.AddOutParam("@exInstanceID", SqlDbType.Int)
.AddInParamSHA1("@exExceptionID", ctx.ExceptionID)
.AddInParamSHA1("@exApplicationID", exApplicationID)
.AddInParam("@LoggedTimeUTC", SqlDbType.DateTime2, ctx.LoggedTimeUTC)
.AddInParam("@SequenceNumber", SqlDbType.Int, ctx.SequenceNumber)
.AddInParam("@IsHandled", SqlDbType.Bit, ctx.IsHandled)
.AddInParamSize("@ApplicationIdentity", SqlDbType.NVarChar, 128, cfg.ApplicationIdentity)
.AddInParam("@ParentInstanceID", SqlDbType.Int, parentInstanceID)
.AddInParam("@CorrelationID", SqlDbType.UniqueIdentifier, ctx.CorrelationID)
.AddInParam("@ManagedThreadId", SqlDbType.Int, ctx.ManagedThreadID)
.AddInParamSHA1("@UserStateCollectionID", userStateCollectionID)
.AddInParamSize("@Message", SqlDbType.NVarChar, 256, ctx.Exception.Message),
(prms, rc) =>
{
return (int)prms["@exInstanceID"].Value;
}
);
// Await the exInstance record creation:
int exInstanceID = await tskInstance;
// Await the exception policy result:
var policy = await tskGetPolicy;
// If logging the web context is enabled and we have a web context to work with, log it:
Task tskLoggingWeb = null;
if (policy.LogWebContext && ctx.CapturedHttpContext != null)
{
tskLoggingWeb = LogWebContext(conn, policy, ctx, exInstanceID);
}
// Log the UserState collection:
if (userStateCollectionID != null)
await LogCollection(conn, userStateCollectionID.Value, ctx.UserState);
// Wait for any outstanding logging tasks:
if (tskLoggingWeb != null) await tskLoggingWeb;
if (tskTargetSite != null) await tskTargetSite;
await tskApplication;
// Recursively log inner exceptions:
var inner = ctx.InnerException;
if (inner != null)
// Return the inner-most exInstanceID because you can easily drill down through the ParentInstanceID columns to reach the root level.
return await LogExceptionRecursively(conn, inner, exInstanceID);
return new HashedLogIdentifier(ctx.ExceptionID, exInstanceID);
}