The best way to handle a connection when calling a function from the Console application or SQLCLR object using ("Contextual connection = true")

Question

I have the following type of code in my data layer that can be called from a console application, a Windows application, etc., while the correct connection string is read from the corresponding calling App.Config file:

public static udsDataset GetDataset(int datasetID) { string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; string sql = @"select * from Dataset WHERE DatasetID=@datasetID "; using (SqlConnection conn = new SqlConnection(connectionString)) { // Dapper query: return conn.Query<udsDataset>(sql, new {datasetID } ).First(); } } 

Now I want to call the same code from the SQLCLR stored procedure (in the database where these tables exist), where the contextual connection is usually used:

 using(SqlConnection connection = new SqlConnection("context connection=true")) { connection.Open(); // etc etc etc } 

The most obvious approach that comes to mind is function overloading:

 public static udsDataset GetDataset(int datasetID) { string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; using (SqlConnection conn = new SqlConnection(connectionString)) { return GetDataset(datasetID, conn); } } public static udsDataset GetDataset(int datasetID, SqlConnection conn) { // caller is responsible for closing and disposing connection string sql = @"select * from Dataset WHERE DatasetID=@datasetID "; return conn.Query<udsDataset>(sql, new {datasetID } ).First(); } 

Thus, applications with App.Config can invoke a version without connecting, and SQLCLR can invoke a version that requires SqlConnection.

This “sounds good,” but to write the same overload style for each similar function, this seems wrong.

Answer 1

Accepting the question (and comments on it) at face value, why do you need:

the ability to transfer in an existing connection when called from the SQLCLR procedure

? You should treat Context Connection just like any other connection with respect to Open and Dispose . It looks like you think that SqlConnection when using the connection string "Context Connection = true;" you need to open it only once, and then do not delete it until it is completed, whereas you will Open / Dispose it several times otherwise. I see no reason to have different behaviors in these two scenarios.

---

Otherwise, what is the best way to deal with detecting changes in the environment (between the console application and the SQLCLR object)? You have two options: both are probably simpler than you expect:

• Do not change the application code, but rely on an additional configuration file:

  You can create a file called sqlservr.exe.Config in the C:\Program Files\Microsoft SQL Server\MSSQL{SqlVersion}.{SqlServerInstanceName}\MSSQL\Binn (for example, C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn , where 11 in MSSQL11 for SQL Server 2012). The format of this file, as expected, is as follows:

   <?xml version="1.0" encoding="utf-8" ?> <configuration> <connectionStrings> <add name="CoolioAppDB" connectionString="Context Connection = true;" /> </connectionStrings> </configuration> 

  This may be considered a “cleaner” code, but it introduces an external dependency that your database administrator can work with, may not like it, but it can endure or ask your manager to write to you :-).

• Make very minor changes to the application code, but do not rely on an additional configuration file:

  You can easily automatically determine whether SQL Servers are currently running in the CLR host using the IsAvailable property in the SqlContext class. Just update the source code as follows:

   string connectionString = "Context Connection = true;"; // default = SQLCLR connection if (!SqlContext.IsAvailable) // if not running within SQL Server, get from config file { connectionString = ConfigurationManager.ConnectionStrings["CoolioAppDB"].ConnectionString; } 

  This usage, by the way, is noted in the "Remarks" section on this linked MSDN page for the IsAvailable property.