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