小编典典

如何在SQL连接字符串中添加自定义属性?

sql

我想在SqlServer连接字符串中添加一些自定义属性,如下所示:

Integrated Security=SSPI;Extended Properties="SomeAttr=SomeValue";Persist Security Info=False;Initial Catalog=DB;Data Source=SERVER

然后在sql中获取该属性。例如SELECT SOME_FUNCTION('SomeAttr')


阅读 47

收藏
2021-04-22

共1个答案

小编典典

没有通用的方法可以通过客户端API传递自定义连接字符串属性并使用T-SQL进行检索。但是,您有许多选择。以下是一些。

方法1:在连接字符串中使用Application Name关键字最多传递128个字符,并使用APP_NAME()T-SQL函数进行检索:

Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DB;Data Source=SERVER;Application Name="SomeAttr=SomeValue"

SELECT APP_NAME();

请注意,此限制为128个字符,您将需要解析有效负载。而且,由于ADO.NET为每个不同的连接字符串创建了一个单独的连接池,因此请考虑实际上几乎没有数据库连接池或没有数据库连接池。

方法2:在连接后执行SET CONTEXT_INFO并分配最多128个字节,可以使用CONTEXT_INFO)T-SQL函数检索该字节:

DECLARE @context_info varbinary(128) = CAST('SomeAttr=SomeValue' AS varbinary(128));
SET CONTEXT_INFO @context_info;

SELECT CAST(CONTEXT_INFO() AS varchar(128));

请注意,此限制为128个字节,您将需要解析有效负载。

方法3:在连接后创建会话级临时表,并插入可使用SELECT查询检索的名称/值对:

CREATE TABLE #CustomSessionAttributes(
      AttributeName varchar(128) PRIMARY KEY
    , AttributeValue varchar(1000));
INSERT INTO #CustomSessionAttributes VALUES('SomeAttr', 'SomeValue');

SELECT AttributeValue 
FROM #CustomSessionAttributes 
WHERE AttributeName = 'SomeAttr';

请注意,您可以根据需要增加属性值的大小和类型,并且不需要解析。

方法4:创建一个以会话ID和属性名称为键的永久表,在连接后插入名称/值对,可使用SELECT查询检索该名称/值对:

CREATE TABLE dbo.CustomSessionAttributes(
      SessionID smallint
    , AttributeName varchar(128)
    , AttributeValue varchar(1000)
    , CONSTRAINT PK_CustomSessionAttributes PRIMARY KEY (SessionID, AttributeName)
    );
--clean up previous session
DELETE FROM dbo.CustomSessionAttributes WHERE SessionID = @@SPID; 
--insert values for this session
INSERT INTO dbo.CustomSessionAttributes VALUES(@@SPID, 'SomeAttr', 'SomeValue');

--retreive attribute value
SELECT AttributeValue 
FROM dbo.CustomSessionAttributes 
WHERE
    SessionID = @@SPID 
    AND AttributeName = 'SomeAttr';

请注意,您可以根据需要增加属性值的大小和类型,并且不需要解析。

编辑:

方法5:使用存储过程sp_set_session_context存储会话范围的名称/值对,并使用SESSION_CONTEXT()函数检索值。SQL Server 2016Azure SQL数据库中引入了此功能。

EXEC sp_set_session_context 'SomeAttr', 'SomeValue';
SELECT SESSION_CONTEXT(N'SomeAttr');
2021-04-22