Skip to Content

Have you ever needed to pass in a value that would be used throughout the lifetime of a connection – perhaps a connection session level value that would be used by multiple procedures, views, functions, etc…?

Well, a colleague of mine, Jay Frysinger, had a great idea – use the connection string itself to pass additional parameters. You can use both “Application Name” and “Workstation ID” to pass in 2 additional 128 character length strings. You can then use the APP_NAME() system function to retrieve the value of the “Application Name” or the HOST_NAME() system function to retrieve the value of “Workstation ID”.

In this example, a GUID is created in the data client, then using ToString() of the GUID, it is added to the connection string. Then,  HOST_NAME() is used to extract that value. Since I wanted a UniqueIdentifier in SQL, I used CAST to convert the string GUID to a UniqueIdentifier. Finally, the UniqueIdentifier is returned and displayed in the console.

using System;
using System.Data;
using System.Data.SqlClient;

namespace ConnectionStringTest
{
    
class Program
    {
        
static void Main(string[] args)
        
{
            Guid guid 
= new Guid("BCAEF7EA-7BD3-4CC8-8288-9B2C8F6BCF28");

            using (SqlConnection c = new SqlConnection(string.Format
               
(@"Data Source=YourServer;Initial Catalog=YourDatabase;Persist Security Info=True;Trusted_Connection=True;Workstation ID={0}"
               
guid.ToString())))
            
{
                
using (SqlCommand cmd = new SqlCommand("SELECT CAST(HOST_NAME() as uniqueidentifier)"c))
                
{
                    cmd.CommandType 
CommandType.Text;
                    
c.Open();
                    
SqlDataReader dr cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    
while (dr.Read())
                    
{
                        Console.WriteLine
(dr.GetSqlGuid(0).ToString());
                    
}
                    dr.Close
();
                
}
                c.Close
();
            
}
            Console.ReadLine
();
        
}
    }
}

2 Comments

  • How about using CONTEXT_INFO instead? The method you propose will, unfortunately, eliminate the benefits of connection pooling (if that matters).

  • That would require an additional call to set the CONTEXT_INFO. But, you raise a good point about the connection pooling. If the connection string value is reused, those connections should get pooled. But if they differ every time…

Receive comment updates via RSS

Leave a Comment