我想在 SqlServer 连接字符串中添加一些自定义属性，如下所示:
I want to add some custom attributes in SqlServer connection string, something like this:
Integrated Security=SSPI;Extended Properties="SomeAttr=SomeValue";Persist Security Info=False;Initial Catalog=DB;Data Source=SERVER
然后在 sql 中获取该属性.例如 SELECT SOME_FUNCTION('SomeAttr')
And then get that attribute in sql. for example SELECT SOME_FUNCTION('SomeAttr')
没有通用的方法可以通过客户端 API 传递自定义连接字符串属性并使用 T-SQL 进行检索.不过，您有多种选择.以下是一些.
There is no generalized method to pass custom connection string attributes via Client APIs and retrieve using T-SQL. You have a number of alternatives, though. Below are a few.
方法 1:在连接字符串中使用 Application Name 关键字最多传递 128 个字符并使用 APP_NAME() T-SQL 函数检索:
Method 1: Use the Application Name keyword in the connection string to pass up to 128 characters and retrieve with the APP_NAME() T-SQL function:
Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DB;Data Source=SERVER;Application Name="SomeAttr=SomeValue"
SELECT APP_NAME();
请注意，这限制为 128 个字符，您需要解析有效负载.此外，由于 ADO.NET 为每个不同的连接字符串创建了一个单独的连接池，请考虑实际上很少或没有数据库连接池.
Note that this is limited to 128 characters and you will need to parse the payload. Also, since ADO.NET creates a separate connection pool for each distinct connection string, consider there will effectively be little or no database connection pooling.
方法二:连接后执行SET CONTEXT_INFO并分配最多128个字节，可以用CONTEXT_INFO检索)T-SQL函数:
Method 2: Execute a SET CONTEXT_INFO after connect and assign up to 128 bytes that can be retreived with the CONTEXT_INFO) T-SQL function:
DECLARE @context_info varbinary(128) = CAST('SomeAttr=SomeValue' AS varbinary(128));
SET CONTEXT_INFO @context_info;
SELECT CAST(CONTEXT_INFO() AS varchar(128));
请注意，这限制为 128 个字节，您需要解析有效负载.
Note that this is limited to 128 bytes and you will need to parse the payload.
方法 3:在连接和插入可以通过 SELECT 查询检索的名称/值对后创建会话级临时表:
Method 3: Create a session-level temporary table after connect and insert name/value pairs that can be retrieved with a SELECT query:
CREATE TABLE #CustomSessionAttributes(
AttributeName varchar(128) PRIMARY KEY
, AttributeValue varchar(1000));
INSERT INTO #CustomSessionAttributes VALUES('SomeAttr', 'SomeValue');
SELECT AttributeValue
FROM #CustomSessionAttributes
WHERE AttributeName = 'SomeAttr';
注意可以根据需要增加属性值大小和类型，不需要解析.
Note that you can increase the attribute value size and type as needed, and no parsing is needed.
方法 4:创建一个以会话 ID 和属性名称为键的永久表，在连接后插入可以使用 SELECT 查询检索的名称/值对:
Method 4: Create a permanent table keyed by session id and attribute name, insert name/value pairs after connect that can be retrieved with a SELECT query:
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';
注意可以根据需要增加属性值大小和类型，不需要解析.
Note that you can increase the attribute value size and type as needed, and no parsing is needed.
方法 5:使用存储过程 sp_set_session_context 存储会话范围的名称/值对并使用 SESSION_CONTEXT() 函数.此功能是在 SQL Server 2016 和 Azure SQL 数据库中引入的.
Method 5: Use stored procedure sp_set_session_context to store session-scoped name/value pairs and retrieve the values with the SESSION_CONTEXT() function. This feature was introduced in SQL Server 2016 and Azure SQL Database.
EXEC sp_set_session_context 'SomeAttr', 'SomeValue';
SELECT SESSION_CONTEXT(N'SomeAttr');
这篇关于如何将自定义属性添加到 SQL 连接字符串?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！