SQL Server CLR集成是否支持配置文件？ [英] Does SQL Server CLR Integration support configuration files?

问题描述

我使用SQL Server CLR集成来创建组装。

I use SQL Server CLR Integration to create an ASSEMBLY.

Load命令：
从'G：＼创建组装TcpClr cpTcpClrTest.dll'WITH PERMISSION_SET = UNSAFE

没有App.Config

Without App.Config

dll代码包含：
string ip = ConfigurationManager.AppSettings [ connection]。ToString（）;

App.config还包含：

Also the App.config contains :

< appSettings>
< add key = connection value = 127.0.0.1 />
< / appSettings>

但是当我执行该过程时，SQL Server显示错误 System.NullReferenceException

But when I execute the PROCEDURE,the SQL Server shows an error System.NullReferenceException

SQL Server CLR集成是否支持App.config文件？

Does SQL Server CLR Integration support App.config files?

推荐答案

您需要将 sqlservr.exe.config 文件放置在 \中该实例的根文件夹的Binn 文件夹。例如：

You need to place a sqlservr.exe.config file in the \Binn folder of that instance's root folder. For example:

C：\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn

如果您使用的是SQL Server 2008 R2（SP1）或更高版本，则应该能够通过以下查询找到确切的位置，该查询显示了 sqlservr的完整路径.exe ：

If you are using SQL Server 2008 R2 (SP1) or newer, you should be able to find the exact location via the following query, which shows the full path to sqlservr.exe:

SELECT [filename] FROM sys.dm_server_services WHERE servicename LIKE N'SQL Server (%';

在您的代码中，您需要在最上方添加以下行：

In your code, you need this line at the top:

using System.Configuration;

然后这将起作用：

[SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true)]
public static SqlString GetConfig(SqlString WhichOne)
{
    ConfigurationManager.RefreshSection("connectionStrings");
    return ConfigurationManager.ConnectionStrings[WhichOne.Value].ToString();
}

sqlservr.exe.config 文件的内容：

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
   <connectionStrings>
      <add name="Stuff" connectionString="Trusted_Connection=true; Enlist=false;" />
      <add name="ClrTest" connectionString="boo hoo" />
   </connectionStrings>
</configuration>

请务必注意，如使用应用程序配置...链接中所述，对配置文件所做的更改不会立即可用。 但是，您执行该文章中提到的其中一种方法（即 DBCC FREESYSTEMCACHE 不需要 >，然后重新启动SQL Server）。获取当前信息所需要做的就是通过 ConfigurationManager.RefreshSection（字符串sectionName），如上例所示。 请参阅下面有关使用和性能的注释。

It is important to note that, as stated in the "Using an Application Configuration..." link, changes made to the config file are not immediately available. HOWEVER, you do not need to force a reload by doing one of the methods mentioned in that article (i.e. DBCC FREESYSTEMCACHE, and restarting SQL Server). All that is required to get current information is to reload the particular section you are using via ConfigurationManager.RefreshSection(string sectionName) as shown in the example above. Please see note below regarding usage and performance.

资源：

• 在.NET proc和UDF中使用System.Configuration.dll


• 在SQL Server CLR集成中使用应用程序配置（app.config / web.config）文件

• Using System.Configuration.dll in .NET sprocs and UDFs
• Using an Application Configuration (app.config/web.config) File in SQL Server CLR Integration

此外，除非您绝对需要，否则不应该将您的程序集创建为 UNSAFE 。如果您只是尝试与其他计算机建立TCP连接，则只需要 EXTERNAL_ACCESS 。

Also, unless you absolutely need to, you shouldn't create your assembly as UNSAFE. If you are just trying to make TCP connections to other machines, that should only require EXTERNAL_ACCESS.

使用和性能

Joe B在下面的评论， RefreshSection 的性能稍有下降code>操作。如果每两分钟要多次调用包含刷新的代码，那么它将产生明显的影响（鉴于配置文件更改频率不足，这种影响是不必要的）。在这种情况下，您将希望从经常调用的代码中删除对 RefreshSection 的调用，并独立处理刷新。

As suggested by Joe B in a comment below, there is a slight performance hit for the RefreshSection operation. If the code containing the refresh is going to be called more than once every couple of minutes, then it can have a noticeable impact (an impact that is unnecessary given the lack of frequency of a config file changing). In this case, you will want to remove the call to RefreshSection from the code that is called frequently and handle the refresh independently.

一种方法是让SQLCLR存储过程或标量函数仅执行刷新，而没有其他操作。

One approach would be to have a SQLCLR Stored Procedure or Scalar Function that just does the refresh and nothing else. This can be executed whenever a change it made to the config file.

另一种方法是卸载应用程序域，该域将在下次任何SQLCLR时重新加载配置文件。该数据库中的对象被引用。重新加载特定数据库中的所有App Domain（但不跨整个实例）的一种相当简单的方法是将 TRUSTWORTHY 设置翻转为On，然后再次为Off，或者再次为Off，再次打开，具体取决于该设置的当前状态。下面的代码将检查该设置的当前状态，并进行相应的翻转：

Another approach would be to unload the App Domain which will reload the config file upon the next time any SQLCLR object in that database is referenced. One fairly simple method to reload all App Domains in a particular Database (but not across the entire Instance) is to flip the TRUSTWORTHY setting On and then Off again, or Off and then On again, depending on the current state of that setting. The code below will check the current state of that setting and flip it accordingly:

IF (EXISTS(
    SELECT  sd.*
    FROM    sys.databases sd
    WHERE   sd.[name] = DB_NAME() -- or N'name'
    AND     sd.[is_trustworthy_on] = 0
   ))
BEGIN
    PRINT 'Enabling then disabling TRUSTWORTHY...';
    ALTER DATABASE CURRENT SET TRUSTWORTHY ON;
    ALTER DATABASE CURRENT SET TRUSTWORTHY OFF;
END;
ELSE
BEGIN
    PRINT 'Disabling then enabling TRUSTWORTHY...';
    ALTER DATABASE CURRENT SET TRUSTWORTHY OFF;
    ALTER DATABASE CURRENT SET TRUSTWORTHY ON;
END;

请请不要使用任何更激烈的方法- DBCC FREESYSTEMCACHE ，禁用然后启用 clr enabled 系统设置，重新启动实例，等等-因为几乎不需要这样做。尤其是重新启动实例或 DBCC FREESYSTEMCACHE 会删除整个实例的 all 缓存数据，这不仅会影响SQLCLR，而且会影响更多。

Please do not use any of the more drastic methods -- DBCC FREESYSTEMCACHE, disabling then enabling the clr enabled system setting, restarting the Instance, etc -- as it is almost never necessary to do so. Especially restarting the Instance, or DBCC FREESYSTEMCACHE which drops all cached data for the entire Instance, which affects much more than just SQLCLR.

SQL Server现在从2017版开始在Linux上可用（呼呼！） 。但是，似乎从应用程序配置文件读取在Linux上不起作用。我尝试了 sqlservr.exe。[Cc] onfig 和 sqlservr。[Cc] onfig 的许多组合，等等，之类的东西，却什么也没做。指定配置文件无法正常工作，因为这需要 EXTERNAL_ACCESS 权限，并且在Linux上仅允许 SAFE 程序集（自至少现在）。如果我找到使它起作用的方法，我将在此处发布详细信息。

SQL Server is now, starting with version 2017, available on Linux (woo hoo!). However, it seems that reading from the app config file does not work on Linux. I have tried many combinations of sqlservr.exe.[Cc]onfig and sqlservr.[Cc]onfig, etc, and the like and have not gotten anything to work. Specifying a config file can't work as that requires EXTERNAL_ACCESS permission and only SAFE Assemblies are allowed on Linux (as of right now, at least). If I find a way to get it working I will post the details here.

这篇关于SQL Server CLR集成是否支持配置文件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！