从Console App或SQLCLR对象调用函数时处理连接的最佳方法（“Context Connection = true”）

I have the following type of code in my data layer, which can be called from a console app, windows app, etc, with the proper connection string being read from the corresponding caller's App.Config file:

我的数据层中有以下类型的代码，可以从控制台应用程序，Windows应用程序等调用，并从相应的调用者的App.Config文件中读取正确的连接字符串：

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();
   }    
}

I now want to call this same code from a SQLCLR stored procedure (within the database where these tables exist), where you would typically use a context connection:

我现在想从SQLCLR存储过程（在存在这些表的数据库中）调用相同的代码，您通常使用上下文连接：

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

The most obvious approach that comes to mind is to overload the function:

想到的最明显的方法是重载函数：

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();
}

So apps with an App.Config could call the connection-less version and SQLCLR could call the version requiring a SqlConnection.

因此，使用App.Config的应用程序可以调用无连接版本，SQLCLR可以调用需要SqlConnection的版本。

This "seems ok", but having to write the exact same style of overload for every single similar function makes it feel wrong.

这“似乎没问题”，但是必须为每个相似的函数编写完全相同的重载样式，这会让人觉得不对。

1 个解决方案

#1

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

以面值来表达问题（及其评论），你为什么需要：

the option of passing in an existing connection when calling from a SQLCLR procedure

从SQLCLR过程调用时传递现有连接的选项

? You should treat the Context Connection the same as any other connection with regards to Open and Dispose. It sounds like you are thinking that the SqlConnection, when using a Connection String of "Context Connection = true;", needs to be opened only once and then not disposed until completely done, whereas you would Open / Dispose of it several times otherwise. I don't see any reason to have differing behavior in these two scenarios.

？对于Open和Dispose，您应该将Context Connection视为与任何其他连接相同。听起来你认为SqlConnection在使用“Context Connection = true;”的连接字符串时，只需要打开一次，然后直到完全完成才会被释放，否则你会打开/处理它几次。我认为没有理由在这两种情况下有不同的行为。

All of that aside, how to best handle detecting the change in environment (between Console App and SQLCLR object)? You have two choices, both being probably easier than you are expecting:

除此之外，如何最好地处理检测环境变化（在Console App和SQLCLR对象之间）？你有两个选择，两者都可能比你期望的更容易：

Make no changes to the app code, but rely on an additional config file:

不对应用程序代码进行任何更改，但依赖于其他配置文件：

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

您可以在C：\ Program Files \ Microsoft SQL Server \ MSSQL {SqlVersion}。{SqlServerInstanceName} \ MSSQL \ Binn文件夹（例如C：\ Program Files \ Microsoft SQL Server \ MSSQL11）中创建名为sqlservr.exe.Config的文件。 MSSQLSERVER \ MSSQL \ Binn，其中MSSQL11中的11用于SQL Server 2012）。该文件的格式应该如预期的那样，如下所示：
```
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <connectionStrings>
        <add name="CoolioAppDB" connectionString="Context Connection = true;" />
    </connectionStrings>
</configuration>
```
This might be considered "cleaner" code, but does introduce an external dependency that your DBA might be ok with, might dislike but tolerate, or might ask your manager to write you up for ;-).

这可能被认为是“更干净”的代码，但确实引入了一个外部依赖，你的DBA可能会好，可能不喜欢但容忍，或者可能会要求你的经理给你写信;-)。
Make a very minor change to the app code, but don't rely on an additional config file:

对应用程序代码进行非常小的更改，但不要依赖其他配置文件：

You can easily auto-detect whether or not you are currently running in SQL Servers's CLR host by using the IsAvailable property of the SqlContext class. Just update your original code as follows:

通过使用SqlContext类的IsAvailable属性，您可以轻松地自动检测您当前是否正在SQL Server的CLR主机中运行。只需更新您的原始代码，如下所示：
```
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 of that linked MSDN page for the IsAvailable property.

顺便提一下，这种用法在IsAvailable属性的链接MSDN页面的“备注”部分中注明。

#1