A primer on connection pooling

using System;
using System.Data;
using System.Data.SqlClient;
 
namespace Craft
{
    class Mate
    {
        // Connection pooling 101: http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx
 
        // To check the number of connections made:
 
        static string checkPooledConnections =
@"SELECT spid, uid=rtrim(loginame), Program_name=rtrim(Program_name),
dbname=db_name(dbid), status=rtrim(status) 
FROM master.dbo.sysprocesses
WHERE program_name = '.Net SqlClient Data Provider'";
 
        static void Main(string[] args)
        {
            using (var connection = new SqlConnection("Integrated Security=SSPI;Initial Catalog=test"))
            {
                connection.Open();
                // Pool A is created.
 
                using (var cmd = connection.CreateCommand())
                {
                    string languageCode = "zh";
                    Console.WriteLine("Set Language: {0}", languageCode);
 
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.CommandText = "SetLanguage";
                    var prm = cmd.CreateParameter();
                    prm.ParameterName = "@LanguageCode";
                    prm.Value = languageCode;
                    cmd.Parameters.Add(prm);
                    cmd.ExecuteNonQuery();                   
                }
 
                ShowLanguage(connection);
                connection.Close();
            }
 
            using (var connection = new SqlConnection("Integrated Security=SSPI;Initial Catalog=Test"))
            {
                connection.Open();
                // Pool B is created because the connection strings differ.
 
                // Even the database is the same, the casing of the database name is different(uppercased T), hence connection pooler treating this connection as different connection                
 
                ShowLanguage(connection);
                connection.Close();
            }
 
            using (var connection = new SqlConnection("Integrated Security=SSPI;Initial Catalog=test"))
            {
                connection.Open();
                // The connection string matches pool A.
 
                Console.WriteLine("Shall show no language even this connection matches the connection pool A.");
                ShowLanguage(connection);
                connection.Close();
            }
 
            Console.WriteLine("\nPlease execute the following query on SSMS before hitting the Enter key. You shall see that there are only two connections made instead of three. Connection pooling is in effect\n\n{0}",
                checkPooledConnections);
            Console.ReadKey();
 
      
        }
 
        static void ShowLanguage(SqlConnection connection)
        {
            using (var getLang = connection.CreateCommand())
            {
                getLang.CommandType = CommandType.Text;
                getLang.CommandText = "select cast(CONTEXT_INFO() as varchar(5))";
               
                object userLanguageCode = getLang.ExecuteScalar();
                Console.WriteLine("Get Language: {0}\n", userLanguageCode);
                Console.ReadKey();
            }           
        }
       
    } // classmate
} // namespacecraft
 

SetLanguage SP:

create procedure SetLanguage(@LanguageCode varchar(5)) as
begin
    declare @binvar varbinary(128);
    set @binvar = cast(@LanguageCode as varbinary(128));
    set context_info @binvar;
end;