The difference between the connections strings in SQLCLR

Question

I was reviewing some code that a consultant checked in and notice they were using SQLCLR. I don't have any experience with it so thought I would research what it was about. I noticed that they used

Dim cn As New SqlConnection("server=LOCALHOST;integrated security=yes;database=" & sDb)

instead of

DIM conn As New SqlConnection("context connection=true")

I'm wondering what the difference since it's localhost on the first?

Answer 1

The context connection uses the user's already established connection to the server. So you inherit things like their database context, connection options, etc.

Using localhost will connect to the server using a normal shared memory connection. This can be useful if you don't want to use the user's connection (i.e. if you want to connect to a different database, or with different options, etc).

In most cases you should use the context connection, since it doesn't create a separate connection to the server.

Also, be warned that using a separate connection means you are not part of the user's transaction and are subject to normal locking semantics.

Answer 2

Consider a big office phone systems:

My office has an internal phone system. But every phone also has an external phone number (virtual numbers that utilize one of a group of real TELCO lines). I can call another office by dialing their phone extension directly and the call will route through our internal phone system (one hop). Alternatively I could dial that phone's public number and the call routes out from the building's system to the TELCO switching office, then back through the building's system then to the office extension (3 hops).

The first SQL connection behaves as any standard SQL connection would when connecting to the server specified in the connection string. A new connection is created using the standard native SQL connectivity. This behaves like dialing the full public phone number of another office phone. Sure, you are connecting to the local machine, but the connection is routed differently.

The context connection has the new SqlConnection instance using the existing connection that is executing the SQLCLR object. It's using the existing/local context. This is like dialing my office mate's extension directly. Local context and more efficient.

Although I'm not positive, I believe that when using the context connection, the calls to the SQLCLR objects also then participate in the context's transaction. Someone please correct me if I'm wrong.

Peter