Geek asks and answers

In MSSQL (TSQL), can I specify a contextual variable that lives in the connection string but does not affect the join?

To explain the need for further, consider this scenario:

In an old system that relies heavily on TRIGGERS, we need to provide some kind of token (let it be called "SessionID") that will be inserted into some security log tables. This token is created in C # on the application server and will be passed to all SQL commands .

GARDEN, THIS REQUIREMENT FOR TRIGGERS IS NECESSARY

So, since I have access to change the connection string, I can (and have successfully proven that I can) use the "Application Name" token to provide this information.

string connectionString = string.Format("SERVER=sql.example.com; "
    + "DATABASE=someDB; User ID=xyz; Password=123; Application Name={0}", sID);

So how does it work, what's the problem?

The problem is very simple ... the above works, but since we have thousands of users ... our connection pool is destroyed (since connection pools are created based on the connection string ... basically, I need the connection pool should be based on everything except the property Application Name).

So, do you know how I can:

  • Set a property in the connection string that will NOT be included in the union.
  • Set the context property for this connection in some other way that is not incredibly heavy in performance.

As a side note ... I could immediately open the join and create a temporary table with a single value:

SELECT 12345 AS SessionID INTO #context

But this seems like an incredible excess!

+3
source share
4 answers

, , SET CONTEXT_INFO/CONTEXT_INFO() / ( ).

/ .., CONTEXT_INFO , .

+7

, ,

WorkstationId SqlConnection - ?

            SqlConnection cn = new SqlConnection("CONNECTION_STRING");
            string identifier = cn.WorkstationId;
+2

, .

  • .
  • " ".

, 50 , ... ... 0,005 .

- , .

Oh, since the side point - the connection pool has BASIC performance limitations if all connections are currently in use, since the 101st person in the queue must wait ... so in SOME instances ... pooling is 1000 times slower than using Pula!

+1
source

Unfortunately, everything in the connection string will be taken for joining (I think they will use GetHashCode () to check if the string is already in the pool).

0
source

More articles:


All Articles