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

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

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

Leave a Comment

(required) 
(required) 
Submit

About Peter W. DeBetta

Peter DeBetta works for Microsoft as an Application Platform Technology Strategist, specializing in strategies, design, implementation, and deployment of Microsoft SQL Server and .NET solutions. Peter writes courseware, articles, and books – most recently the title Introducing SQL Server 2008 from Microsoft Press. Peter speaks 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 (click here to hear an original song by Peter), taking pictures, or simply enjoying life with his wife, son, and daughter.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement