如何将自定义属性添加到SQL连接字符串？ [英] How to add custom attributes to SQL connection string?

问题描述

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

 集成安全性= SSPI;扩展属性=SomeAttr = SomeValue; Persist Security Info = False; Initial Catalog = DB; Data Source = SERVER 
  

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

解决方案

通过客户端API传递自定义连接字符串属性的通用方法，并使用T-SQL进行检索。不过，你有很多选择。方法1 ：在连接字符串中使用Application Name关键字可传递多达128个字符，并通过APP_NAME进行检索。

（）T-SQL函数：

pre $集成安全性= SSPI;持久安全信息=假;初始目录= DB;数据源= 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查询检索的名称/值对之后，创建会话级临时表：

b
$ b

  CREATE TABLE #CustomSessionAttributes（
 AttributeName varchar（128）PRIMARY KEY 
，AttributeValue varchar（1000））; 
 INSERT INTO #CustomSessionAttributes VALUES（'SomeAttr'，'SomeValue'）; 
 
 SELECT AttributeValue 
 FROM #CustomSessionAttributes 
 WHERE AttributeName ='SomeAttr'; 
  

请注意，您可以根据需要增加属性值的大小和类型，不需要解析。 / p>

方法4 ：创建一个由会话标识和属性名称键入的永久表，在连接后插入名称/值对，可以使用SELECT query：

  CREATE TABLE dbo.CustomSessionAttributes（
 SessionID smallint 
，AttributeName varchar（128）
，AttributeValue varchar（1000）
，CONSTRAINT PK_CustomSessionAttributes PRIMARY KEY（SessionID，AttributeName）
）; 
  - 清理上一个会话
 DELETE FROM dbo.CustomSessionAttributes WHERE SessionID = @@ SPID; 
  - 此会话的插入值
 INSERT INTO dbo.CustomSessionAttributes VALUES（@@ SPID，'SomeAttr'，'SomeValue'）; 
 
 --retreive属性值
 SELECT AttributeValue 
 FROM dbo.CustomSessionAttributes 
 WHERE 
 SessionID = @@ SPID 
 AND AttributeName ='SomeAttr ; 
  

请注意，您可以根据需要增加属性值的大小和类型，不需要解析。 / b>

编辑：

方法5 过程 sp_set_session_context 来存储会话范围的名称/值对并检索值使用 SESSION_CONTEXT（）函数。此功能是在SQL Server 2016和Azure SQL数据库中引入的。

  EXEC sp_set_session_context'SomeAttr'，'SomeValue'; 
 SELECT SESSION_CONTEXT（N'SomeAttr'）; 
  

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

And then get that attribute in sql. for example SELECT SOME_FUNCTION('SomeAttr')

解决方案

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.

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

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.

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

Note that this is limited to 128 bytes and you will need to parse the payload.

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.

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.

EDIT:

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连接字符串？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！