Hello,

I hope this message finds you well. We are currently encountering some issues with our application and I hope to getting this resolved with your help. We are utilizing SQL Server 2019 database along with the .NET framework for our Angular-based ERP Web Application. The API is implemented using the C# adapter method in LLBL Version 5.9.

Our application relies heavily on a user table that contains a list of individuals using the system. Most of our business logic and data retrieval processes are handled on the SQL Server side through stored procedures.

To accurately track the user responsible for each SQL call after successful login, we have implemented a stored procedure called SpSessionContextTsk. This procedure utilizes the "sys.sp_set_session_context" function to set the value for the User. An example of this is: EXEC sp_set_session_context 'PersonId', 4.

In the code snippet marked as #1, the GetAdapter() method is where we execute the **SpSessionContextTsk **procedure alongside the actual stored procedure that handles our business logic. The actual stored procedure, marked as #2, retrieves the PersonId using the statement: SELECT SESSION_CONTEXT(N'PersonId').

Upon analyzing the SQL Server profiler, we anticipated observing two calls followed by a connection reset. However, we have noticed an irregular number of connection resets and multiple executions of the** SpSessionContextTsk** procedure, as indicated by #3.

In light of these observations, we have the following questions:

1. Is the current approach we're using to retrieve the PersonId on the SQL side correct? If not, could you suggest a better solution?
2. Why is the SpSessionContextTsk procedure being executed multiple times? What can we do to making it a single call?
3. Instead of occurring after each call, why are we witnessing multiple executions of "exec sp_reset_connection"?
4. We have encountered situations where the "SELECT SESSION_CONTEXT" statement returns incorrect values. How could this occur within the GetAdapter method, and what steps can we take to address this issue?

Thank you for your assistance. We look forward to your prompt response.

Best regards,

#1

using SD.LLBLGen.Pro.ORMSupportClasses;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using Zen.Application.Service.LogProvider;
using Zen.Application.SqlServer;

namespace Zen.Application.Service
{
    public class DataAccessHelper : IDataAccessHelper
    {
        public int CurrentPersonId { get; set; }
        public string Json { get; set; }

        public DataAccessManager GetAdapter()
        {
            try
            {
                if (Json != null)
                {
                    var adapter = new DataAccessManager(true)
                    {
                        CompatibilityLevel = SqlServerCompatibilityLevel.SqlServer2012,
                        CommandTimeOut = 600 //10 mins
                    };
                    adapter.ConnectionString += ";Application Name=" + CurrentPersonId.ToString() + ";";
                    ActionProcedures.SpSessionContextTsk(Json, adapter);
                    return adapter;
                }
                return null;
            }
            catch (Exception ex)
            {
                CommonLog.log.Info($"EX: GetAdapter Message: {ex.Message}");
                return null;
            }
        }

        public List<T> FetchDerivedModel<T>(IRetrievalQuery qry)
        {
            List<T> pmv = new List<T>();
             try
            {
                using (DataAccessManager adapter = GetAdapter())
                {
                    pmv = adapter.FetchProjection<T>(qry);
                    adapter.CloseConnection();
                    adapter.Dispose();
                }

                return pmv;
            }
            catch (Exception ex)
            {
                CommonLog.log.Info($"EX: FetchDerivedModelAsyc Message: {ex.Message}");
                return null;
            }
        }

        public string PersonTaxIns(string json)
        {
            string jData = json;
            using (var adapter = GetAdapter())
            {
                ActionProcedures.SpPersonTaxIns(ref jData, adapter);
                adapter.CloseConnection();
                return jData;
            }
        }
    }
}

#2

CREATE PROCEDURE [dbo].[SpPersonTaxIns]
(
    @Json VARCHAR (MAX) OUTPUT )
AS
    BEGIN
        SET NOCOUNT ON;
        BEGIN TRY
            BEGIN TRANSACTION;

            DECLARE @UserPersonId INT 
            SELECT @UserPersonId = SESSION_CONTEXT(N'PersonId');  

            IF ( @UserPersonId = 0 )
                BEGIN
                    RAISERROR ('User not found.', 16, 1);
                END;
            ELSE
                BEGIN
                    PRINT 'BUSINESS LOGIC'
                END;
            COMMIT TRANSACTION;
        END TRY
        BEGIN CATCH
            IF @@TRANCOUNT > 0
                ROLLBACK TRANSACTION;
            THROW;
        END CATCH;
    END;

#3

exec sp_reset_connection
go
exec [ZenDemo].[dbo].[SpSessionContextTsk] @Json=N'{"roleId":"200002","applicationId":"200006","organizationId":"200004","officeId":"200004","deviceType":0,"deviceInfo":"{\"location\":{\"ip\":\"47.187.18.224\"},\"specification\":{\"userAgent\":\"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36 Edg/114.0.1823.67\",\"os\":\"Windows\",\"browser\":\"MS-Edge-Chromium\",\"device\":\"Unknown\",\"os_version\":\"windows-10\",\"browser_version\":\"114.0.1823.67\",\"deviceType\":\"desktop\",\"orientation\":\"landscape\",\"uuid\":\"25010064645373611400053736114018236751080192024\"}}","PersonId":"6"}'
go
exec [ZenDemo].[dbo].[SpPersonApplicationDataSel] @Json=N'{"name": "All","applicationId":"200006","roleId":"200002","currentPath":"newhire/dashboard"}'
go
exec sp_reset_connection
go
exec sp_reset_connection
go
exec [ZenDemo].[dbo].[SpSessionContextTsk] @Json=N'{"roleId":"200002","applicationId":"200006","organizationId":"200004","officeId":"200004","deviceType":0,"deviceInfo":"{\"location\":{\"ip\":\"47.187.18.224\"},\"specification\":{\"userAgent\":\"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36 Edg/114.0.1823.67\",\"os\":\"Windows\",\"browser\":\"MS-Edge-Chromium\",\"device\":\"Unknown\",\"os_version\":\"windows-10\",\"browser_version\":\"114.0.1823.67\",\"deviceType\":\"desktop\",\"orientation\":\"landscape\",\"uuid\":\"25010064645373611400053736114018236751080192024\"}}","PersonId":"6"}'
go
exec [ZenDemo].[dbo].[SpSessionContextTsk] @Json=N'{"roleId":200002,"applicationId":200006,"organizationId":200004,"officeId":200004,"deviceType":0,"deviceInfo":"{\"location\":{\"ip\":\"47.187.18.224\"},\"specification\":{\"userAgent\":\"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36 Edg/114.0.1823.67\",\"os\":\"Windows\",\"browser\":\"MS-Edge-Chromium\",\"device\":\"Unknown\",\"os_version\":\"windows-10\",\"browser_version\":\"114.0.1823.67\",\"deviceType\":\"desktop\",\"orientation\":\"landscape\",\"uuid\":\"25010064645373611400053736114018236751080192024\"}}","PersonId":"6"}'
go
exec sp_executesql N'SELECT * From dbo.TfOptionSel(@p0,@p1,@p2,@p3)',N'@p0 nvarchar(14),@p1 nvarchar(28),@p2 nvarchar(6),@p3 int',@p0=N'ProfilePicture',@p1=N'DisallowProfilePictureUpload',@p2=N'Office',@p3=200004
go
exec [ZenDemo].[dbo].[SpSessionContextTsk] @Json=N'{"roleId":200002,"applicationId":200006,"organizationId":200004,"officeId":200004,"deviceType":0,"deviceInfo":"{\"location\":{\"ip\":\"47.187.18.224\"},\"specification\":{\"userAgent\":\"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36 Edg/114.0.1823.67\",\"os\":\"Windows\",\"browser\":\"MS-Edge-Chromium\",\"device\":\"Unknown\",\"os_version\":\"windows-10\",\"browser_version\":\"114.0.1823.67\",\"deviceType\":\"desktop\",\"orientation\":\"landscape\",\"uuid\":\"25010064645373611400053736114018236751080192024\"}}","PersonId":"6"}'
go
exec sp_reset_connection
go
exec [ZenDemo].[dbo].[SpSessionContextTsk] @Json=N'{"roleId":200002,"applicationId":200006,"organizationId":200004,"officeId":200004,"deviceType":0,"deviceInfo":"{\"location\":{\"ip\":\"47.187.18.224\"},\"specification\":{\"userAgent\":\"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36 Edg/114.0.1823.67\",\"os\":\"Windows\",\"browser\":\"MS-Edge-Chromium\",\"device\":\"Unknown\",\"os_version\":\"windows-10\",\"browser_version\":\"114.0.1823.67\",\"deviceType\":\"desktop\",\"orientation\":\"landscape\",\"uuid\":\"25010064645373611400053736114018236751080192024\"}}","PersonId":"6"}'
go
exec [ZenDemo].[dbo].[SpApplicationTaskChartSel] @Json='{"applicationName":"NHO"}'
go
exec [ZenDemo].[dbo].[SpOrganizationSMSProfileSel] @Json=N'{}'
go
exec sp_reset_connection
go
exec sp_reset_connection
go
exec [ZenDemo].[dbo].[SpSessionContextTsk] @Json=N'{"roleId":200002,"applicationId":200006,"organizationId":200004,"officeId":200004,"deviceType":0,"deviceInfo":"{\"location\":{\"ip\":\"47.187.18.224\"},\"specification\":{\"userAgent\":\"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36 Edg/114.0.1823.67\",\"os\":\"Windows\",\"browser\":\"MS-Edge-Chromium\",\"device\":\"Unknown\",\"os_version\":\"windows-10\",\"browser_version\":\"114.0.1823.67\",\"deviceType\":\"desktop\",\"orientation\":\"landscape\",\"uuid\":\"25010064645373611400053736114018236751080192024\"}}","PersonId":"6"}'
go
exec [ZenDemo].[dbo].[SpSessionContextTsk] @Json=N'{"roleId":200002,"applicationId":200006,"organizationId":200004,"officeId":200004,"deviceType":0,"deviceInfo":"{\"location\":{\"ip\":\"47.187.18.224\"},\"specification\":{\"userAgent\":\"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36 Edg/114.0.1823.67\",\"os\":\"Windows\",\"browser\":\"MS-Edge-Chromium\",\"device\":\"Unknown\",\"os_version\":\"windows-10\",\"browser_version\":\"114.0.1823.67\",\"deviceType\":\"desktop\",\"orientation\":\"landscape\",\"uuid\":\"25010064645373611400053736114018236751080192024\"}}","PersonId":"6"}'
go
exec [ZenDemo].[dbo].[SpSessionContextTsk] @Json=N'{"roleId":200002,"applicationId":200006,"organizationId":200004,"officeId":200004,"deviceType":0,"deviceInfo":"{\"location\":{\"ip\":\"47.187.18.224\"},\"specification\":{\"userAgent\":\"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36 Edg/114.0.1823.67\",\"os\":\"Windows\",\"browser\":\"MS-Edge-Chromium\",\"device\":\"Unknown\",\"os_version\":\"windows-10\",\"browser_version\":\"114.0.1823.67\",\"deviceType\":\"desktop\",\"orientation\":\"landscape\",\"uuid\":\"25010064645373611400053736114018236751080192024\"}}","PersonId":"6"}'
go
exec [ZenDemo].[dbo].[SpDirectoryTsk] @Json=N'{"output":"s","directory":"NewHire","workflowStageId":""}'
go
exec [ZenDemo].[dbo].[SpDirectoryFilterTsk] @Directory='newhire'
go
exec [ZenDemo].[dbo].[SpSessionContextTsk] @Json=N'{"roleId":200002,"applicationId":200006,"organizationId":200004,"officeId":200004,"deviceType":0,"deviceInfo":"{\"location\":{\"ip\":\"47.187.18.224\"},\"specification\":{\"userAgent\":\"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36 Edg/114.0.1823.67\",\"os\":\"Windows\",\"browser\":\"MS-Edge-Chromium\",\"device\":\"Unknown\",\"os_version\":\"windows-10\",\"browser_version\":\"114.0.1823.67\",\"deviceType\":\"desktop\",\"orientation\":\"landscape\",\"uuid\":\"25010064645373611400053736114018236751080192024\"}}","PersonId":"6"}'
go
exec [ZenDemo].[dbo].[SpOrganizationSMSProfileSel] @Json=N'{}'
go
exec [ZenDemo].[dbo].[SpTileSel] @Json='{"application":"NHO","personTileId":0,"tile":"All","tileId":0,"type":"chart","filters":[],"action":"load"}'
go
exec [ZenDemo].[dbo].[SpTileSel] @Json='{"application":"NHO","personTileId":0,"tile":"All","tileId":0,"type":"box","filters":[],"action":"load"}'
go
exec sp_reset_connection
go
exec sp_reset_connection
go
exec [ZenDemo].[dbo].[SpSessionContextTsk] @Json=N'{"roleId":200002,"applicationId":200006,"organizationId":200004,"officeId":200004,"deviceType":0,"deviceInfo":"{\"location\":{\"ip\":\"47.187.18.224\"},\"specification\":{\"userAgent\":\"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36 Edg/114.0.1823.67\",\"os\":\"Windows\",\"browser\":\"MS-Edge-Chromium\",\"device\":\"Unknown\",\"os_version\":\"windows-10\",\"browser_version\":\"114.0.1823.67\",\"deviceType\":\"desktop\",\"orientation\":\"landscape\",\"uuid\":\"25010064645373611400053736114018236751080192024\"}}","PersonId":"6"}'
go
exec [ZenDemo].[dbo].[SpSessionContextTsk] @Json=N'{"roleId":200002,"applicationId":200006,"organizationId":200004,"officeId":200004,"deviceType":0,"deviceInfo":"{\"location\":{\"ip\":\"47.187.18.224\"},\"specification\":{\"userAgent\":\"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36 Edg/114.0.1823.67\",\"os\":\"Windows\",\"browser\":\"MS-Edge-Chromium\",\"device\":\"Unknown\",\"os_version\":\"windows-10\",\"browser_version\":\"114.0.1823.67\",\"deviceType\":\"desktop\",\"orientation\":\"landscape\",\"uuid\":\"25010064645373611400053736114018236751080192024\"}}","PersonId":"6"}'
go
exec [ZenDemo].[dbo].[SpTileParameterSel] @Json='{"tileId":200053}'
go
exec [ZenDemo].[dbo].[SpTileParameterSel] @Json='{"tileId":200054}'
go
exec sp_reset_connection
go

You use a class DataAccessManager, I assume that's just a DataAccessAdapter with a different name?

Calling a stored proc opens the connection, executes the proc, and closes the connection again. What you should do is at the start of the logical session for a user, call OpenConnection, and at the end of the logical session of the user, call CloseConnection. After calling OpenConnection set the property KeepConnectionOpen to true.

All work between these two methods with the same adapter will then re-use the same connection for which you set the session context. I think that's the main thing I can think of what might be wrong in your code.

Also, you don't need to call dispose on the adapter if you create it in a using block.

So in short:

• Call OpenConnection
• Set KeepConnectionOpen to true
• Call SpSessionContextTsk, passing the adapter instance
• Call your proc/other logic you want to do for this user, using the same adapter
• Call CloseConnection
• Optionally call Dispose, but on sqlserver that's not strictly necessary.

Excellent! I sincerely appreciate your assistance.