/// <summary>
/// Initializes the connection if it is not already initialized
/// </summary>
/// <returns>The connection string that was initialized</returns>
private async Task <SqlConnection> GetConnectionAsync(CancellationToken cancellation = default)
{
using var _ = _instrumentation.Block("AdminRepo." + nameof(GetConnectionAsync));
if (_conn == null)
{
_conn = new SqlConnection(_connectionString);
await _conn.OpenAsync(cancellation);
}
if (_userInfo == null)
{
// Always call OnConnect SP as soon as you create the connection
var externalUserId = _externalUserAccessor.GetUserId();
var externalEmail = _externalUserAccessor.GetUserEmail();
var culture = CultureInfo.CurrentUICulture.Name;
var neutralCulture = CultureInfo.CurrentUICulture.IsNeutralCulture ? CultureInfo.CurrentUICulture.Name : CultureInfo.CurrentUICulture.Parent.Name;
_userInfo = await OnConnect(externalUserId, externalEmail, culture, neutralCulture, cancellation);
}
// Since we opened the connection once, we need to explicitly enlist it in any ambient transaction
// every time it is requested, otherwise commands will be executed outside the boundaries of the transaction
_conn.EnlistInTransaction(transactionOverride: _transactionOverride);
return(_conn);
}
private async Task <AdminUserInfo> OnConnect(string externalUserId, string userEmail, string culture, string neutralCulture)
{
AdminUserInfo result = null;
using (SqlCommand cmd = _conn.CreateCommand()) // Use the private field _conn to avoid infinite recursion
{
// Parameters
cmd.Parameters.Add("@ExternalUserId", externalUserId);
cmd.Parameters.Add("@UserEmail", userEmail);
cmd.Parameters.Add("@Culture", culture);
cmd.Parameters.Add("@NeutralCulture", neutralCulture);
// Command
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = $"[dal].[{nameof(OnConnect)}]";
// Execute and Load
using var reader = await cmd.ExecuteReaderAsync();
if (await reader.ReadAsync())
{
int i = 0;
// The user Info
result = new AdminUserInfo
{
UserId = reader.Int32(i++),
ExternalId = reader.String(i++),
Email = reader.String(i++),
};
}
else
{
throw new InvalidOperationException($"[dal].[OnConnect] did not return any data from Admin Database, ExternalUserId: {externalUserId}, UserEmail: {userEmail}");
}
}
return(result);
}
