THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Peter DeBetta's SQL Programming Blog

Peter DeBetta's blog about programming in SQL Server using technologies such as T-SQL, .NET, CLR, C#, VB, Visual Studio, and SQL Server Management Studio.

SQL Server Connection Level Parameters

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

Published Thursday, July 19, 2007 11:17 AM by Peter W. DeBetta

Comments

 

Adam Machanic said:

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

July 19, 2007 12:52 PM
 

Peter W. DeBetta said:

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...

July 19, 2007 2:38 PM
New Comments to this post are disabled

About Peter W. DeBetta

Peter DeBetta works as a Data and Cloud Solution Manager at DB Best, a Microsoft gold partner company. Peter has written courseware, articles, and books on topics such as development and SQL Server. Peter has spoken at conferences around the world, including TechEd, SQL PASS Community Summit, DevTeach, SQL Connections, DevWeek, and VSLive!

When Peter isn’t working, you can find him singing and playing guitar, taking pictures, working on DIY projects, woodworking, or simply enjoying life with his kids.
Privacy Statement