Peronal Template in SQL Server Management Studio

It has been a while since my last post. Well, there are so many jobs need to be done during this busy season. When we are busy, we need to find a way to improve our productivity, or simply free us from repeated jobs. Today let’s see a very important function in SSMS Template.

When we have SSMS installed, by default there will be standard system templates available. You can open the Template Browser by using the hot key “Ctrl + Alt + T”, or by using the command item from the menu View -> Template Explorer.



Applying the template is quite simple, simply find the template you want to use, double click it, and then replace the variables in the template. Let’s see an example:

Assume I want to create a new function by applying the function template. To do so, I double click the “Create Inline Function (new menu)” template under the Function category.



At this stage a new query window with default pattern should appear.



As you can see the template pattern contains comments section, script section, and lots of “<>”. These are the parameters can be quickly replaced. As specified by the instruction comments, we can use hot key “Ctrl + Shift + M” to replace the parameters, as shown below.



After we input the values, click OK button and we will have the function populated within the query window.



This is a standard function provided in SSMS. Obviously it can save us lots of time. But we can make it even better, i.e. we can create our own parameterised template!

Firstly, we need to have a blank folder. In below screenshots, I created a personal template folder by right clicking top-most node “SQL Server Templates” and selecting New -> Folder, and then named it “Personal Template”.



Right click the newly created folder and this time, I selected New -> Template. Now we will have an empty template available in the folder.

To fill in our template, we need to right click the template, and then select Edit, as shown below.



Now the template should be opened in SSMS. Just like example shown below, to indicate the parameter, we need to use the format “<name="" type="" example="">”. To make it simple, in this example I just created a "If exists, select" pattern.

 USE <database_name, sysname, master>  
 GO  
 IF EXISTS (SELECT 1 FROM sys.tables where name = '<table_name, sysname, dummy_table>')  
 BEGIN  
      SELECT * FROM <table_name, sysname, dummy_table>  
 END  

After we done everything, we can save it by using “Ctrl + S” or using the menu bar Save command. Close the template query window, now double click our personal template and press “Ctrl + Shift + M”, you should see parameter window appears.



Fill in values for the parameters, close the popup window by pressing OK button, you should see the result as expected. Handy function, isn’t it?

Power BI & Telstra Wifi Hotspot API

A while ago I wrote the post to show how to call Telstra Wi-Fi Hotspot API through C#. Today let’s see how easy we can achieve the same result (even better) by using Microsoft Power BI.


Power BI, in short, is an powerful BI tool for data extraction, transformation, and visualisation.  I would highly recommend it if you haven’t tried it yet.  At this stage you can sign it up for free, by applying a trial office 365 license.


Now back to the topic, today my plan is to call the API by using Power Query embedded in Power BI, and then display the result on the workspace. To do so let’s open Power BI Desktop, and press Edit Queries button on the ribbon.

On the Query Editor window (Power Query), open dropdown menu by press New Source button, then select Blank Query option.

Now click Advanced Editor button on the ribbon to open the editor window

Now Advanced Editor window pops up, and we can start to type in Power Query statements. To demonstrate how powerful the power query is, I decided to split the procedure into two: create a function to get oAuth token, and then call the Wi-Fi Hotspot API.


To create the function in Power Query, firstly let’s write below query (replace consumer key and consumer secret)

 let  
   authUrl = "https://api.telstra.com/v1/oauth/token",  
   authKey = "client_id={consumerkey}&client_secret={consumer secret}&grant_type=client_credentials&scope=WIFI",  
   getJson = Web.Contents  
       (authUrl,  
         [  
           Headers = [#"Content-Type" = "application/x-www-form-urlencoded"],  
           Content = Text.ToBinary(authKey)  
         ]  
       ),  
   jsonDoc = Json.Document(getJson)  
 in  
   jsonDoc  

Click Done button and the oAuth result should be shown on Power Query window directly, as shown below

So far everything is good. What we need to do now is to introduce parameters into the query, and then parameterise the query. To do so firstly create new parameters

For demonstration purpose, I treat consumer key and consumer secret as parameters, as shown below

Now change our query a bit, I have highlighted changes in the screenshot attached below

Once you done everything and click Done button, the function window appears

Now you can test the function by entering consumer key and consumer secret then pressing the Invoke button.

The function should work as expected. Time to write the 2nd script. Following the same step to open a blank query window and input statements like below.

 let  
   accessToken = fnGetToken("{consumerkey}", "{consumersecret}")[access_token],  
   latitude = "-37.8103713989",  
   longitude = "144.99530273",  
   radius = "1000",  
   wifiUrl = Text.Format("https://api.telstra.com/v1/wifi/hotspots?lat=#{0}&long=#{1}&radius=#{2}", {latitude, longitude, radius}),  
   accessTokenHeader = "Bearer " & accessToken,  
   getWifiJson = Web.Contents(wifiUrl, [Headers = [#"Authorization" = accessTokenHeader]]),  
   wifiJson = Json.Document(getWifiJson),  
   table = Table.FromList(wifiJson, Splitter.SplitByNothing(), null, null, ExtraValues.Error),  
   wifiTable = Table.ExpandRecordColumn(table, "Column1", {"lat", "long", "address", "city", "state"}, {"lat", "long", "address", "city", "state"})  
 in  
      wifiTable   

Now things could be a little bit tricky. Depends on your Power BI desktop setting, you might get the result without any issue. But in most cases, you could see two different types of warnings: connection warning and privacy warning. In short it is because we are passing oAuth token from a different Url (https://api.telstra.com/v1/oauth/token vs https://api.telstra.com/v1/wifi/hotspots). Let's see how we can resolve these conflicts:


The first warning you might see is the connection warning.

Click the button “Edit Credential” will lead us to a popup window. Because we retrieve our token by passing consumer key and consumer secret into the function, we can simply select Anonymous method and then select the Url https://api.telstra.com/v1/oauth/token as the target.

The next window is about data privacy.

On the Privacy Level screen, select the Url https://api.telstra.com/v1/oauth/token, and then select “Public” as the scope.

After all these steps, the data table should be generated automatically

Now we can go back to the Power BI by clicking the button “Close & Apply” at the top left corner. Drag a map visual onto the workspace, drop Lat and Long columns onto the corresponding map fields, and that is it

Telstra WiFi API Quick Demonstration

Update on 16/06/2016: VS 2015 project for this post can be accessed on my personal Github repository.


It has been a while since I blogged the SMS API published by Telstra (you can find it here). After that post I spent quite a long time to blog other stuff. Recently I re-visited Telstra's Dev centre and found this interesting WiFi API. So let's try this API today.

Basically the API will accept query parameters of latitude, longitude, and a radius, then it will give us a list of available Telstra WiFi hotspots around the coordination.


The first step to use this API is to get the API key. Similar to the steps I described in my SMS post, it should be quite simple to get a new app created. Just be aware, the product to be ticked is WiFi API, as demonstrated below.

After the registration, expand the app pannel by pressing its name, you should see the consumer key and consumer secret are available now.

Time to look at C# implementation of the API.


Create a Winform project in Visual Studio, and I have a form created: 3 textboxes to accept latitude, longitude, and radius. A calculate button to call the WiFi API, and finally a list box to display all returned WiFi hotspots' addresses (keep in mind that, at this stage the API will return maximum 10 hot spots).

Before starting of the code task, I added two references into my project: one is System.Net.Http, which will be used to issue request and receive response; another one has been heavily used in my previous posts: Newtonsoft.Json. It will help us parse the json string returned from API call.


One thing we can reuse from previous SMS post is the authentication method: pass in consumer key and consumer secret, and then return a token and its expiry seconds:

     private async Task<TelstraToken> GetAccessToken(string consumerkey, string consumersecret)  
     {  
       TelstraToken Token = new TelstraToken();  
       string AccessUrl = @"https://api.telstra.com/v1/oauth/token";  
       HttpClient authClient = new HttpClient();  
       HttpContent httpContent = new FormUrlEncodedContent(new Dictionary<string, string>  
       {  
         {"client_id", consumerkey},  
         {"client_secret", consumersecret},  
         {"grant_type", "client_credentials"},  
         {"scope", "WIFI"}  
       });  
       HttpRequestMessage Request = new HttpRequestMessage()  
       {  
         Method = HttpMethod.Post,  
         RequestUri = new Uri(AccessUrl),  
         Content = httpContent  
       };  
       try  
       {  
         var ResponseMessage = await authClient.SendAsync(Request);  
         var Response = await ResponseMessage.Content.ReadAsStringAsync();  
         if (ResponseMessage.IsSuccessStatusCode)  
         {  
           var AuthToken = JsonConvert.DeserializeObject<object>(Response);  
           JObject jObj = JObject.Parse(AuthToken.ToString());  
           Token.AccessToken = jObj["access_token"].ToString();  
           Token.ExpiredDt = DateTime.Now.AddSeconds(double.Parse(jObj["expires_in"].ToString()));  
         }  
       }  
       catch (Exception ex)  
       {  
         MessageBox.Show(ex.Message);  
       }  
       return Token;  
     }  

You may question the TelstraToken object from the method above: It is just a simple object to host token value/expiry date time. Beside this simple object, I also created another helper class for Hotspot returned from API. Below are the definitions for these two classes:

     internal class TelstraToken  
     {  
       internal string AccessToken;  
       internal DateTime ExpiredDt;  
       internal TelstraToken() { }  
       internal TelstraToken(string _accessToken, DateTime _expiredDt)  
       {  
         this.AccessToken = _accessToken;  
         this.ExpiredDt = _expiredDt;  
       }        
     }  
     internal class TelstraWifiHotSpot  
     {  
       internal string latitude;  
       internal string longitude;  
       internal string address;  
       internal string city;  
       internal string state;  
       internal TelstraWifiHotSpot(string _latitude, string _longitude, string _address, string _city, string _state)  
       {  
         this.latitude = _latitude;  
         this.longitude = _longitude;  
         this.address = _address;  
         this.city = _city;  
         this.state = _state;  
       }  
       public override string ToString()  
       {  
         string fullAddress = string.IsNullOrWhiteSpace(address) ? string.Empty : address;  
         fullAddress += ", ";  
         fullAddress += string.IsNullOrWhiteSpace(city) ? string.Empty : city;  
         fullAddress += " ";  
         fullAddress += string.IsNullOrWhiteSpace(state) ? string.Empty : state;  
         return fullAddress;  
       }  
     }  

Now all preparations are done. The only missing part is the button click event:

     private async void btnCalculate_Click(object sender, EventArgs e)  
     {  
       List<TelstraWifiHotSpot> lst = new List<TelstraWifiHotSpot>();  
       if (_token == null || _token.ExpiredDt < DateTime.Now)  
       {  
         _token = await GetAccessToken("your consumer key", "your consumer secret");  
       }  
       string latitude = txtLatitude.Text;  
       string longitude = txtLongitude.Text;  
       string radius = txtRadius.Text;  
       string url = string.Format("https://api.telstra.com/v1/wifi/hotspots?lat={0}&long={1}&radius={2}", latitude, longitude, radius);  
       HttpClient client = new HttpClient();  
       client.DefaultRequestHeaders.Authorization = new System.Net.Http.Headers.AuthenticationHeaderValue("Bearer", _token.AccessToken);  
       HttpRequestMessage request = new HttpRequestMessage()  
       {  
         Method = HttpMethod.Get,  
         RequestUri = new Uri(url)  
       };  
       HttpResponseMessage responseMessage = await client.SendAsync(request);  
       var message = await responseMessage.Content.ReadAsStringAsync();  
       var jMsg = JsonConvert.DeserializeObject<object>(message);  
       foreach (var j in (JArray)jMsg)  
       {  
         string wifiLatitude = ((JObject)j)["lat"].ToString();  
         string wifiLongitude = ((JObject)j)["long"].ToString();  
         string wifiAddress = ((JObject)j)["address"].ToString();  
         string wifiCity = ((JObject)j)["city"].ToString();  
         string wifiState = ((JObject)j)["state"].ToString();  
         TelstraWifiHotSpot hotspot = new TelstraWifiHotSpot(wifiLatitude, wifiLongitude, wifiAddress, wifiCity, wifiState);  
         lst.Add(hotspot);  
       }  
       if (lst.Count > 0)  
       {  
         lstHotspots.DataSource = lst;  
         lstHotspots.DisplayMember = lst.ToString();  
       }  
     }  

After we put everything together and run the project, you should be able to find the hotspots Telstra provided: