reading session_context in .NET / C#

Question

question

andrew_ink asked Mar 11, '19

reading session_context in .NET / C#

Hello, I had this working a few months ago but i cant read back a session variable set
in code but i can in the query analyzer..
this C# code sets the variable
String.Format("EXEC sp_set_session_context N'TENANTID', '{0}'", tenantId); SPSDatabaseInfo.ExecuteScalar(SPSDatabaseInfo.DBType.MSSQLServer, _connString, sql, null);

but when i read it back immediately with
sql = "select SESSION_CONTEXT(N'TENANTID') AS SESSION_ID"; var retVal = SPSDatabaseInfo.ExecuteScalar(SPSDatabaseInfo.DBType.MSSQLServer, _connString, sql, null);

it comes back null but i can set and read it no problem in the management studio

All comments welcome!

Andy

c#.net sessions

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2019-03-11T21:28:18Z

KenJ answered Mar 11, '19

It seems like you're reading it back immediately, but you are doing so in the context of a different session in SQL Server.

Each invocation of SPSDatabaseInfo.ExecuteScalar has its own session, so each has a separate SESSION_CONTEXT. The session context from the first execution is not available from second execution of ExecuteScalar.

If you were to combine your queries into a single call to ExecuteScalar, the queries would share a session and you would see a value returned from the SESSION_CONTEXT

String.Format("EXEC sp_set_session_context N'TENANTID', '{0}'; select SESSION_CONTEXT(N'TENANTID') as SESSION_ID", tenantId); 
var retVal =SPSDatabaseInfo.ExecuteScalar(SPSDatabaseInfo.DBType.MSSQLServer, _connString, sql, null);

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question