SQL Server : Context_Info

 

While working one of my project, need for connection specific setting required to enable and disable functionality. Project has very large database in terms of entities and many of entities have triggers that affect other entity and also done task for sending notification.

Sometime there is need to disable certain trigger required during data mining work without affecting application that use data.

While searching for solution to that issue I came across Context_Info functionality available in SQL Server.It is varbinary field and its value available only for particular connection session.

You can use it following way.

Step 1. SET CONTEXT_INFO 0x1

Step 2. If 0x01 = Context_Info()
    Print ‘ContextInfo Set’
else
    print ‘ContextInfo Not Set’

If you have SQL Server Management Studio then open two new query window.

In first window put code specified in Step1 and Step2 and In Second window only put code specified in Step 2.

You can also use above functionality during coding as well. You just have to call that function with value. Following is the sample with C#. ( Test application that I created for this)

 class SqlContextInfoSample
    {
        public void ExecuteSample()
        {
            using (var conn = GetConnection())
            {
                SetContextInfomationToConnection(conn);
                Console.WriteLine(GetContextInformationFromConnection(conn));
            }

            using (var conn = GetConnection())
            {
                Console.WriteLine(GetContextInformationFromConnection(conn));
            }
        }

        SqlConnection GetConnection()
        {
            var conn = new SqlConnection();
            conn.ConnectionString = @"<>";
            conn.Open();
            return conn;
        }

        void SetContextInfomationToConnection(SqlConnection conn)
        {
            using (SqlCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = "SET CONTEXT_INFO 0x1";
                cmd.ExecuteNonQuery();
            }
        }

        string GetContextInformationFromConnection(SqlConnection conn)
        {
            using (SqlCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = "Select CONTEXT_INFO()";
                object result = cmd.ExecuteScalar();
                return result.ToString();
            }
        }
    }

Hope this will help.