SQL Server CLR集成支持配置文件吗？

Question

SQL Server CLR集成支持配置文件吗？

c#sql-serverconfiguration-filessqlclrconfigurationmanager

8

我使用SQL Server CLR Integration创建了一个ASSEMBLY。

加载命令如下: CREATE ASSEMBLY TcpClr FROM 'G:\TcpClrTest.dll' WITH PERMISSION_SET = UNSAFE 没有App.Config文件。

dll代码包含： string ip=ConfigurationManager.AppSettings["connection"].ToString(); 同时，App.config包含以下内容:

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

但是，当我执行存储过程时，SQL Server显示一个错误：System.NullReferenceException。

SQL Server CLR Integration支持App.config文件吗？

- allen

4个回答

3

您显然会收到一个NullReferenceException，因为ConfigurationManager.AppSettings["connection"]返回null，然后您在null上调用ToString()。

在您发布问题之前，谷歌搜索CLR Integration app.config的排名第一的页面是{{link1：Jonathan Kehayias}}撰写的这篇文章：在SQL Server CLR集成中使用应用程序配置（app.config/web.config）文件。

在相关文章中，他说：

.NET编程的常见部分是使用配置文件将配置信息存储在易于修改的位置。 app.config或web.config文件是大多数.NET项目中不可或缺的组成部分，开发人员可能需要将此功能作为逻辑共享的一部分维护在数据库对象和应用程序之间。 问题在于，SQL CLR不允许在SQLCLR项目中使用System.Configuration类。

他接着详细介绍了一个解决方法，涉及编辑项目文件以将新密钥注入到ItemGroup中，将你的app.config与项目链接起来。从那里，你可以在代码内进行一些基本操作，创建一个UserDefinedFunction来返回你需要的连接字符串或appSetting。建议阅读他在上述网址发布的文章，因为这不是我的领域，我只会照搬他的内容提供一步一步的操作说明。

- rism

1

你可以随意使用 System.Configuration；只要通过 ConfigurationManager.OpenExeConfiguration() 显式获取即可。静态的 ConfigurationManager 方法显然不适用，但是该声明的 粗体部分 是不准确的。唯一的技巧是确定 app.config 的位置，这就是 SQL CLR 发挥“诡计”的地方，因为 AppDomain 从 Assembly.GetExecutingAssembly() 中遮蔽了所有位置信息。然而，如果你可以使用固定路径来定位你的 app.config，那么你就成功了！唯一的细节是不使用 CM 的静态方法。 - escape-llc

1

你没有包含代码，现在链接已经失效了。这就是使用链接回答问题最终总会发生的事情。 - toddmo

1

你是否正在尝试访问CLR运行的同一台SQL Server？如果是，你可以使用一种特殊类型的连接字符串，称为“上下文连接”。

https://msdn.microsoft.com/en-us/library/ms131053.aspx

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

基本上，您告诉代码应该在该上下文中使用底层的SQL Server，并且甚至不需要指定连接信息。如果您正在尝试将任意可重用代码作为CLR运行，则可能正在犯某种软件犯罪，因此最好不要这样做。

- Joe Phillips

太好了！多么讽刺啊，所有看到的例子都与连接字符串有关... - escape-llc

0

这是我的做法。对我来说，它完美地运作着，现在我可以在任何实例上安装我的程序集，而且它也会完美地工作。

   SqlConnectionStringBuilder sqlb = new SqlConnectionStringBuilder("");
        sqlb.ContextConnection = true;
        string newConnStr = sqlb.ToString();

        using (SqlConnection c = new SqlConnection(newConnStr))

        {
            c.Open();

            //This is the database name
            DatabaseName = c.Database;

            //We need to use some simple SQL to get the server and instance name.
            //Returned in the format of SERVERNAME\INSTANCENAME
            SqlCommand cmd = new SqlCommand("SELECT @@SERVERNAME [Server]", c);
            SqlDataReader rdr = cmd.ExecuteReader();
            if (rdr.Read())
            {
                ServerName = rdr["Server"].ToString();
            }
        }

- Stuart

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Solomon Rutzky · Accepted Answer

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

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

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

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

在你的代码中，你需要在顶部加上这一行：

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(string sectionName)重新加载您正在使用的特定部分即可。请参见下面有关用法和性能的说明。

资源:

此外，除非你绝对需要，否则不应将程序集创建为UNSAFE。如果你只是想建立到其他机器的TCP连接，那么只需要EXTERNAL_ACCESS。

用法和性能

正如Joe B在下面的评论所建议的那样，RefreshSection操作会有一点性能损失。如果包含刷新的代码会每两分钟调用一次以上，则会有明显的影响（这是由于配置文件变化的频率不高而不必要的）。在这种情况下，您需要从经常被调用的代码中删除对RefreshSection的调用，并独立处理刷新。

一种方法是创建一个SQLCLR存储过程或标量函数，仅执行刷新操作。每当更改配置文件时，可以执行此操作。

另一种方法是卸载应用程序域，这将在下一次引用该数据库中的任何SQLCLR对象时重新加载配置文件。一个相当简单的方法是重新加载特定数据库中的所有应用程序域（而不是整个实例），即启用然后再次关闭TRUSTWORTHY设置或关闭然后再次启用，具体取决于该设置的当前状态。以下代码将检查该设置的当前状态并相应地进行更改：

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会删除整个实例的所有缓存数据，这影响的远不止SQLCLR。

关于Linux上的SQL Server的更新

从版本2017开始，SQL Server现在可以在Linux上使用（哇呼！）。然而，似乎在Linux上从应用程序配置文件中读取不起作用。我已经尝试了许多组合，例如sqlservr.exe.[Cc]onfig和sqlservr.[Cc]onfig等，并没有得到任何结果。指定配置文件是无法工作的，因为这需要EXTERNAL_ACCESS权限，而目前只允许在Linux上使用SAFE程序集（至少目前是这样的）。如果我找到了解决方法，我会在这里发布详细信息。