Getting Session ID in SQL Azure programmatically

There is a Session Id assigned to each connection to SQL Azure. Session Id may be very useful in debugging and can be logged by the developers. In this article, I will show “How could fetch Session Id programmatically?

First step to create a class with the properties

1. Server Name

2. Login Name

3. Password

So let us create a class called SessionID with below properties

clip_image002

This class will contain function to fetch the session Id. Essentially Session Id for a connection is returned using Context_Info(). We will fetch Context_Info() using SqlCommand.

clip_image004

After fetching Context_Info() as string , I am converting that to Guid. Full code for SessionID class is as below,

SessionID.CS


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace GettingSessionID
{
 public   class SessionID
 {
 public string ServerName { get; set; }
 public string LoginName { get; set; }
 public string Password { get; set; }

public SqlConnection GetSessionId(out Guid sessionId)
 {
 sessionId = Guid.Empty;
 SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
 builder.DataSource = string.Format("tcp:{0}.database.windows.net,1433", this.ServerName);
 builder.InitialCatalog = "master";
 builder.UserID = this.LoginName;
 builder.Password = this.Password;
 builder.Pooling = true;
 SqlConnection conn = new SqlConnection(builder.ToString());
 conn.Open();
 using (SqlCommand cmd = conn.CreateCommand())
 {
 cmd.CommandText = "SELECT CONVERT(NVARCHAR(36), CONTEXT_INFO())";
 string contextInfo = (string)cmd.ExecuteScalar();
 sessionId = new Guid(contextInfo);
 }
 return conn;
 }

}
}

 

In above code, I am creating connection string using SqlConnectionStringBuilder

To get the Session Id, what all you need to do is only create instance of SessionID class and call the function GetSessionId().

I am getting the Session Id from a console program as below. If you want you can log session id from here for debugging purpose.

Program.cs


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace GettingSessionID
{
 class Program
 {
 static void Main(string[] args)
 {
 SessionID session = new SessionID { ServerName = "Your SQL Azure Database server Name", Password = "Your SQL Azure Password", LoginName = "Your SQL Azure Login" };
 Guid sessionId;
 session.GetSessionId(out sessionId);
 Console.WriteLine(sessionId.ToString());
 Console.ReadKey(true);
 }
 }
}

 

Expected output would be,
clip_image006

Published by

Dhananjay Kumar

Dhananjay Kumar is Developer Evangelist for Infragistics. He is a 8 times Microsoft MVP and well respected Developer Advocate in India.He is the author of 900+ Blog Posts, and can often be found speaking around India at conferences and hosting free workshops for programmers across the country. So far, he has hosted 60 free workshops on various topics like JavaScript, Angular, WCF, ASP.NET MVC, C#, Azure etc. Follow him on twitter @debug_mode for all the updates about his blog posts and workshops. You can send him email at debugmode [at] outlook [dot] com

One thought on “Getting Session ID in SQL Azure programmatically”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s