Cannot set/get SQL session_context in .NET C#

Question

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

EXEC sys.sp_set_session_context @key = N'TENANTID', @value = 'TEST_ID'
select SESSION_CONTEXT(N'TENANTID')

Any ideas why ?

Can't really help without knowing how `sp_set_session_context` works or how the two commands are executed and when. — mxmissile, Mar 11 '19 at 13:57
@CyrilDurand the execute scalars are wrappers to the Microsoft.ApplicationBlocks.Data.SqlHelper functions — ink169, Mar 11 '19 at 14:07
@mxmissile - session_context is standard sql2016 and above functionality https://learn.microsoft.com/en-us/sql/t-sql/functions/session-context-transact-sql — ink169, Mar 11 '19 at 14:07

Daniel Brughera · Accepted Answer · 2019-03-11T14:28:21.103

You are using SPSDatabaseInfo.ExecuteScalar method sending a connection string instead of an open SQLConnection, that method internally is creating/opening/closing it's own connection.

That means that your calls are running under different sessions.

Use this overload of ExecuteScalar method using the same connection for both queries

public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText)
{
    //pass through the call providing null for the set of SqlParameters
    return ExecuteScalar(connection, commandType, commandText, (SqlParameter[])null);
}

this way

using (SqlConnection cn = new SqlConnection(_connString))
{
    cn.Open();
    String.Format("EXEC sp_set_session_context N'TENANTID', '{0}'", tenantId); 
    SPSDatabaseInfo.ExecuteScalar(cn, CommandType.Text, sql);

    sql = "select SESSION_CONTEXT(N'TENANTID') AS SESSION_ID"; 
    var retVal = SPSDatabaseInfo.ExecuteScalar(cn, CommandType.Text, sql);
}

Thanks @Daniel Brughera - indeed, that is the issue! :) – ink169 Mar 11 '19 at 15:59 — ink169, Mar 11 '19 at 15:59

Cannot set/get SQL session_context in .NET C#

1 Answers1