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