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