Report SSRS report definition from TSQL

Within this holiday season, it is really difficult to bring on a topic relates to techs. So I decided to make it simple and short: let’s talk about how to manage reports by T-SQL.

If you had read my previous posts, I had demonstrated how to read and modify report definitions by C# codes. But there could be situations that we want to read report definitions for specific purpose, e.g. report documentation.

To document the report definition, try below codes (assume there is a report named "test report"):  

 

USE ReportServer
go

;WITH XMLNAMESPACES   
 (   
 DEFAULT   
 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition',   
 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'  
 AS rd   
 )  
 SELECT cte.name as ReportName  
   , ds.value('(@Name)[1]', 'VARCHAR(250)') as DatasetName  
      , ds.value('(Query/DataSourceName)[1]','VARCHAR(250)') as DataSourceName  
      , ds.value('(Query/CommandText)[1]','VARCHAR(250)') as Query  
      , fd.value('(@Name)[1]','VARCHAR(250)') as FieldName  
      , fd.value('(DataField)[1]','VARCHAR(250)') as DataField  
      , fd.value('(rd:TypeName)[1]','VARCHAR(250)') as DataType  
  FROM   
  (   
      SELECT C.Name  
           ,CAST(CAST(C.Content as VARBINARY(MAX)) as xml) AS reportXML  
      FROM ReportServer.dbo.Catalog C  
      WHERE C.Content is not null  
      AND C.Type = 2 and c.Path like '%test report%'  
 ) cte  
      CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet') r(ds)  
      CROSS APPLY ds.nodes('Fields/Field') f(fd)   
 ORDER BY name   

It them will give you result like below screenshot:

The only thing needs to be noticed is about the XML namespace declaration. As shown above, the "2010/01" is for SSRS 2012. Depends on the edition of the SSRS you are using, you might need to change the declaration a bit. For instance, if the report was created from SSRS 2008, you need to change the definition to "2008/01".

Simple and easy, isn't it?

So that is it. The last day of 2015, and last post of 2015.  Happy New Year!

SQL - custom log schema via XML

It has been a long time since my last post: After took my holiday for about one month, it is just so difficult to get myself ready for work :(

But anyway, when I came back immediately I found there were quite a lot works need to be settled: We are changing our whole dialler to a brand new one; we are having some new opportunities from both internal and externals; we are planning to update our data platforms, such as ETL management frameworks, etc, etc.

When I reviewed our team's old works, I found below codes and just want to log here. I could not find the original source of the codes, but if my memory is correct, the credit of the codes should be to one of the SQL books. (professional SQL Server 20xx, probably) So if you know where the source of below codes is, please leave me a message so I can link to codes to original source.

The purpose of below codes is to log DML actions via XML and table triggers. Basically we will turn insert and update actions into xml format through the table trigger, and then log into the DML log table.

Now firstly we have table structure like below

 Create table SysLog.DMLLog  
 (  
 LogKey bigint identity(1,1),  
 TableName varchar(255),  
 TriggerName varchar(255),  
 SpID int,  
 Username varchar(255),  
 DMLAction XML,  
 CreatedOn datetime,  
 Constraint PK_DMLLog primary key(LogKey)  
 )  
 go  

Fields we created are self-explanatory, so I won't spend time to explain it :p.

Now it is time about the trigger, assume we are going to build the trigger on Account table. Something like below:

 Create Trigger TriLogAccountDML   
 On Account  
 After insert, update, delete  
 as  
 BEGIN  
      DECLARE @XmlAction XML, @TriID int, @TblID int  

      SELECT @XmlAction = ISNULL((SELECT * FROM inserted FOR XML AUTO), '<inserted/>') + ISNULL((SELECT * FROM deleted FOR XML AUTO), '<deleted/>') 
 
      SELECT @TriID = @@PROCID  
      SELECT @TblID = parent_id FROM sys.triggers WHERE object_id = @TriID  

      INSERT INTO SysLog.DMLLog(TableName, TriggerName, SpID, Username, DMLAction, CreatedOn)  
      SELECT OBJECT_NAME(@TblID), OBJECT_NAME(@TriID), @@SPID, SYSTEM_USER, @XmlAction, GETDATE();  
 END  

Now we are done. In the end we will have our log table like below:

Quite simple isn't it? :D

SSIS Replace Email Addresses in Send Mail Task

Lazy Sep! 3 weeks to my holiday!!

Business is changing very quick. Couple months ago, I posted topics such as How to replace images in a SSRS report, and How to replace connection strings in a SSRS report. Now, let me show you how to replace email addresses in the Send Mail Task of the SSIS package.

The scenario is simple, my company's domain changed, so we have to change email's from, to, cc, bcc accounts in quite a lot packages. Within the SSIS package, we have two ways to send an email: by using Send Mail Task, or by calling sp_send_dbmail in Execute SQL task.

The later case is simple, as we usually store email addresses in a helper table. So simply we just need to update email addresses stored in the helper table.

The first case is what I would like to show you, so have a look:

First of the first, we need to add the references into the project. Two specific references we need to add for this task, one is ManagedDTS, and another one is the SendMailTask.

Basically the ManagedDTS gives you a global view of the package, and the SendMailTask gives you specific management capacity for the component. So for example, if you want to replace the file path within the File System Task, you need to add Microsoft.SqlServer.FileSystemTask.

Now in the using section we need to add what we just referenced

After we prepared our project, we can start to 1. load the package, 2. loop the components, 3. do some changes.

One thing needs to notice at step 2, every component can be treated as an Executable in a package scope. But the container component, i.e. For Loop, Foreach Loop, and Sequence containers can contain child executables. Therefore within my code, I need to detect object type of the executable, and then do my change email address task.

To make it simple, I just create a recursive method to loop the executable.

     public void LoopExecutables(Executable e)  
     {  
       if (e is TaskHost)  
       {          
         TaskHost th = (TaskHost)e;  
         if (th.InnerObject is Microsoft.SqlServer.Dts.Tasks.SendMailTask.SendMailTask)  
         {  
           SendMailTask smt = (SendMailTask)th.InnerObject;  
           smt.FromLine = Regex.Replace(smt.FromLine, "<old email address>", "<new email address>", RegexOptions.IgnoreCase);  
           smt.ToLine = Regex.Replace(smt.ToLine, "<old email address>", "<new email address>", RegexOptions.IgnoreCase);  
           smt.CCLine = Regex.Replace(smt.CCLine, "<old email address>", "<new email address>", RegexOptions.IgnoreCase);  
           smt.BCCLine = Regex.Replace(smt.BCCLine, "<old email address>", "<new email address>", RegexOptions.IgnoreCase);  
         }  
       }  
       else if (e is Sequence)  
       {  
         Sequence seq = (Sequence)e;  
         foreach (Executable e2 in seq.Executables)  
         {  
           LoopExecutables(e2);  
         }  
       }  
       else if (e is ForEachLoop)  
       {  
         ForEachLoop fel = (ForEachLoop)e;  
         foreach (Executable e2 in fel.Executables)  
         {  
           LoopExecutables(e2);  
         }  
       }  
       else  
       {  
         MessageBox.Show(e.GetType().FullName);  
       }  
     }  

Now remaining task becomes straightforward. We load the package, we loop the executables in the package, and finally we save the package. Below is the Main() method:

           public void Main()  
           {  
       Microsoft.SqlServer.Dts.Runtime.Application App = new Microsoft.SqlServer.Dts.Runtime.Application();  
       Package package = new Microsoft.SqlServer.Dts.Runtime.Package();  
       string PackagePath = Dts.Variables["strPackagePath"].Value.ToString();  
       string NewPackagePath = Dts.Variables["strNewPackagePath"].Value.ToString();  
       if (File.Exists(PackagePath))  
       {  
         package = App.LoadPackage(PackagePath, null);  
         try  
         {  
           foreach (var item in package.Executables)  
           {              
             LoopExecutables(item as Executable);  
           }  
           string Folder = Path.GetDirectoryName(NewPackagePath);  
           if (!Directory.Exists(Folder))  
           {  
             Directory.CreateDirectory(Folder);  
           }  
           App.SaveToXml(NewPackagePath, package, null);  
         }  
         catch (Exception ex)  
         {  
           MessageBox.Show(ex.Message);  
         }  
       }  
                Dts.TaskResult = (int)ScriptResults.Success;  
           }  

One last thing to mention, if you want to replace the variable value of the package, remember to check variable's namespace, as usually you do not want to touch system variables, like below

           foreach (var item in package.Variables)  
           {  
             Variable var = (Variable)item;  
             if (var.Namespace == "User")  
             {  
               //do something  
             }  
           }  

Salesforce - Retrieve Salesforce Entity Data by REST API

In my previous post I have shown how to retrieve Salesforce entity data through SOAP API. The Salesforce platform can also be accessed through REST API.

To get the Salesforce REST API works, the first thing we need to do is to create an app in Salesforce. Go to Setup page, on your left hand side, expand Create node under the Build section, then click Apps link.

Now on the main panel, there is a section named "Connected Apps", click the New button next to it.

On the next screen, firstly we need to fill in some basic information, as shown below

Then go to the API section, tick the checkbox "Enable OAuth Settings". Immediately there will be OAuth settings shown on the screen. To demonstrate the REST API, here I just make the callback URL as a test URL, and select full access as the authentication scope. In a real production environment, the access scope should be limited to specific account/group level.

Now once we save it, we should see the confirmation message as below

The activation of the app should not take too long, but meanwhile, we need to collect some information for the API coding task. Open the App and you should see the App details page like below. The consumer key and consumer secret are what we need for our coding task

Time to look at the coding side. In my Simpro post (part2) and Telstra post (here) I used webclient to post the message. Actually we can also use HttpClient to do the same task.

Assume what I want to do is to get a list of leads in Salesforce. The final output of the work looks like below.

Because we are going to use HttpClient, we need to add System.Net.Http into our reference list of the C# project.

Now the remaining coding is just like other REST API, obtain the access token, and submit the request, then receive the response. One thing could be important to you, is the version of your Salesforce when you build the query string. In a normal scenario, the version should not affect your query string. But it is always a good practise to check the version/document.

Code to access lead entity is shown below

     private async void btnLogin_Click(object sender, EventArgs e)  
     {  
       HttpClient authClient = new HttpClient();  
       string ConsumerKey = "<ConsumerKey>";  
       string ConsumerSecret = "<ConsumerSecret>";  
       string Url = @"https://login.salesforce.com/services/oauth2/token";  
       string username = "<username>";  
       string password = "<password>";  
       string token = "<token>";  
       string loginpassword = password + token;  
       HttpContent httpContent = new FormUrlEncodedContent(new Dictionary<string, string>  
         {  
           {"grant_type", "password"},  
           {"client_id", ConsumerKey},  
           {"client_secret", ConsumerSecret},  
           {"username", username},  
           {"password",loginpassword}  
         }  
       );  
       HttpRequestMessage Request = new HttpRequestMessage()  
       {  
         Method = HttpMethod.Post,  
         RequestUri = new Uri(Url),  
         Content = httpContent  
       };  
       var responsemessage = await authClient.SendAsync(Request);  
       var response = await responsemessage.Content.ReadAsStringAsync();  
       if (responsemessage.IsSuccessStatusCode)  
       {  
         var authToken = JsonConvert.DeserializeObject<object>(response);  
         string instance_url = ((JObject)authToken)["instance_url"].ToString();  
         string oauToken = ((JObject)authToken)["access_token"].ToString();  
         string sfQuery = instance_url + "/services/data/v34.0/query?q=SELECT+name+from+Lead";  
         HttpRequestMessage QueryRequest = new HttpRequestMessage(HttpMethod.Get, sfQuery);  
         QueryRequest.Headers.Add("Authorization", "Bearer " + oauToken);  
         var QueryResponse = await authClient.SendAsync(QueryRequest);  
         string QueryResponseResult = await QueryResponse.Content.ReadAsStringAsync();  
         JObject sfObject = JObject.Parse(QueryResponseResult);  
         JArray sfArray = (JArray)sfObject["records"];  
         foreach (JObject o in sfArray)  
         {  
           listBox1.Items.Add(o["Name"].ToString());  
         }  
       }  
       else  
       {  
         MessageBox.Show(response.ToString());  
       }  
     }  

TSQL - Use bitwise to simplify the design

This quick post is because recently one of my colleagues asked for a design suggestion.

We know that we need to follow relational database design principle, that is, normalise the object to its 3rd normalisation form.

But in real world, it is a common practise to have a lookup table to store small amount of records in a relational database design scenario, and it is a common practise to have a junk dimension to merge miscellaneous values in a dimension modelling scenario.

However instead of creating tables, we can use bitwise function to make the design even simpler, like below codes demostrated

use tempdb

--to demostrate how to use bitwise function
declare @Days table (WeekdayName varchar(50), WeekdayNbr int, WeekdayValue int, Selected bit)
declare @Selected int

insert into @Days
values
('Mon', 1, POWER(2, 1), 0),
('Tue', 2, POWER(2, 2), 0),
('Wed', 3, POWER(2, 3), 0),
('Thu', 4, POWER(2, 4), 0),
('Fri', 5, POWER(2, 5), 0),
('Sat', 6, POWER(2, 6), 0),
('Sun', 7, POWER(2, 7), 0)



--assume we want to run the job on Wed and Sun
update @Days set Selected = 1 where WeekdayName in ('wed', 'sun')


select * from @Days

--then the saved weekday value is sum of selected weekday value
select @Selected =  SUM(WeekdayValue) from @Days where Selected = 1
print @selected

--to get bitwise map, & to the weekday value
select @Selected & t.WeekdayValue, WeekdayName
from @Days t


This is not to say we do not need to follow relational database design principle. But when use it properly, the function could be much faster and easier for maintenance.

SSIS & Salesforce - Retrieve Salesforce Entity Data by SOAP API

As advertised, Salesforce is the world No. 1 on demand CRM platform. It is quite popular nowadays, across different sizes and areas of businesses. From our clients, the platform is being used by sport utilities, government agencies, energy retailers, and computer and service retailers.

Unfortunately it still has its own problems, for example, data quality problem. Due to the openess of the Salesforce platform, it is nearly impossible to control the data quality from its own features, at least from our clients, data report generated from Salesforce is in horrible quality, definitely cannot be used for reporting/analysis purpose.

So what we could do is extracting the raw data from Salesforce, and do some cleansing task, load into SQL Server database, a standard ETL procedure. However it is difficult to find a free plugin/component you can use in your SSIS to extract Salesforce data. So what I would like to do, is to show how to retrieve the data from sales force through its API. (Keep in mind there are many ways to access Salesforce data, here is only one of them)

To begin our work, we need to have the API. Log into the Salesforce platform,  go to the Setup page, on the left hand side, under the Build - Develop list view, you should see the API option.

Click this link, a list of WSDL options should be shown on the main panel. They are for different design purpose, as described on the page. What we need is the Enterprise WSDL, as it contains the most basic types we need in this example. Right click the link "Generate Enterprise WSDL", and save the file to your local folder.

Now before go any further, a quick notification about the WSDL. The downloaded WSDL can NOT be used in the project directly. If we add the WSDL as a web service reference into the project, as shown below

When you compile and run the project, you will get error message like below

In short it is because the ListViewRecord, this complex type defined in the WSDL does not contain a data type. It is fine for some languages, because the columns "ListViewRecordColumn" contains the data type definition. But from .Net, we have to explicitly identify the data type.

So we need to manually fix this problem: Open the downloaded WSDL file, locate the node "ListViewRecord" as shown below

Under the tag </sequence>, we need to declare its data type by adding

 <xsd:attribute name="tmp" type="xsd:string" />  

Then it should look like

Now the API definition part is done. We need to collect some other information before we go to SSIS part.

To access the Salesforce API, we need to provide our login email, password, and the token. To get the token we need to go to My Setting of the Salesforce platform, on the left hand side, expand options under Personal node, you should see the option "Reset My Security Token"

By click it, on the main panel of the page, you will see the warning message

Click the button "Reset Security Token", you will see the confirmation message like below, and a system email will shot into your associated email address.

Now we have all the information we need from Salesforce side. But before we go into SSIS, a quick tip may save your time: you may want to set password never expires under a Dev environment. Otherwise every time you change your password, you need to reset your security token.

Now time to look into SSIS. What I would like to do is to use a script component to get the User data from Salesforce.

Firstly drop a data flow task onto the control flow panel.

Within in the data flow task, drag and drop a script component, and select source on the pop up window.

Double click the script component, and go to "Inputs and Outputs" tab. As you can see, I created four output columns for the default output, FirstName, LastName, Title, and Email. They will be used to hold data retrieved from the User entity in Salesforce. For them, I defined their data type as string, length as 255. (You can also see I created another output "Output_Err". Because Salesforce's API provide the login result class, it can be used to show standard or customised error messages. But it will not be covered in this post, as it could be a huge topic)

Now to use the API, we need to add the downloaded, and modified WSDL as a web service into the script project.

Now turn off serialisation assembly option as shown below. Otherwise we will see "no binary code" error in the package.

At this stage, the project configuration is down. The only remaining job is coding.

Add the name space

Then I declared a SforceService to hold the actual service.

Coding is always simple from my understanding, once we know what we need to do

Then drag a Union All component under the Script component, and add a data viewer between these two components

Execute the task, the user records are exported from Salesforce

So now you have the chance to cleanse the Salesforce data, import into SQL Server, and do your own reporting/analysis task.


Codes within the CreateNewOutputRows method:

 public override void CreateNewOutputRows()  
   {  
     string email = "<login email>";  
     string password = "<login password>";  
     string token = "<security token>";  
     Service = new SforceService();  
     LoginResult LoginResult = Service.login(email, password + token);  
     string authurl = Service.Url;  
     Service.Url = LoginResult.serverUrl;  
     Service.SessionHeaderValue = new SessionHeader();  
     Service.SessionHeaderValue.sessionId = LoginResult.sessionId;  
     string sQuery = "SELECT FirstName, LastName, Title, Email FROM User";  
     QueryResult Qr = Service.query(sQuery);  
     if (Qr.size > 0)  
     {  
       foreach (sObject o in Qr.records)  
       {  
         if (o is User)  
         {  
           User u = (User)o;  
           Output0Buffer.AddRow();  
           Output0Buffer.FirstName = u.FirstName;  
           Output0Buffer.LastName = u.LastName;  
           Output0Buffer.Title = u.Title;  
           Output0Buffer.Email = u.Email;  
         }  
       }  
     }  
   }  

SSRS - Build a "dropdown" box on the report

As we know, SQL Server Reporting Service only provides very limited interactions to end users. Once the report is rendered, the report itself will become a static item. Interactions mostly need to be done from  external applications. Well, there are quite a lot external applications can be used to get interactive result, such as PerformancePoint server in Sharepoint. But except for them, there are some ways to make the RS report looks better. So today I would like to show a "dropdown" box in reporting service.

Firstly let's see what will it look like:

When click the "+" next to the textbox, it will become

Click one of the options in the expanded dropdown box, the selection will appear in the Options textbox

In this case, this "dropdown" box allows multi-selection

To build this type of report, we do not need 3rd party components, customisation of the reporting service, or creation of the CRIs. What we need is just a bit imagination:

The first thing we need to do is to have a report parameter. The purpose of this parameter is to hold the value we selected from the "dropdown" box. In my report, I call it "SelectedValues"

To demonstrat the value we selected, I created a textbox on top of the "dropdown" box, to show the paramter value. Having it or without it will not affect the final result, I created it simply because a long string will be "cut" in the report parameter field, then you cannot see what happened in the design.

As you can see, the expression for this field is quite simple

 =Parameters!SelectedValues.Value  

Now we need have the options for our "dropdown" box. To make it simple, I have the query below

 select 'Option A' as Col  
 UNION ALL  
 select 'Option B' as Col  
 UNION ALL  
 select 'Option C' as Col  

Create a dataset by using this query, then we are ready to format the report:

• Put a tablix component onto the report 
• Change the tablix component to 2 x 2 size
• The row group has the col value from the dataset
• The title row has the expression to show the parameter "SelectedValues" value
• Set the tablix border to none
• Format background color for the first cell of the row group to grey

The final layout of the report looks like below

Now we need to have some special settings. (To make things simple, I will use column number + row number to reference the cell, for example, C1R2 is the grey cell in picture above)

Right click the row group from the bottom panel, go to Visibility tab, and check the radio button "Hide", so by default this row group is hidden from the layout.

Tick the checkbox "Display can be toggled by..." and from the dropdown box, select textbox C2R1. In below screenshot, the item I used to toggle the visibility of the row group, is textbox14

Select the cell C1R1,  set its border style to Solid, border color to light grey, and set attribute "CanGrow" to false (we don't want our "dropdown" box expand by its contents)


Now we have a textbox looks like a drondown box. In preview mode, we can see somthing like below. Time to design the action.


Click cell C1R2 (the cell has the value "[Col]"), and from the pop up context menu, select "Text Box Properties".

Go to Action tab, select radio button "Go to report". In the dropdown box "Specify a report", select the report we are working on.

Click "Add" button under this dropdown box to add a parameter. You should see the parameter "SelectedValues" under the parameter Name dropdown list.

Next to the parameter name, we need to have the parameter value passed into the report. Because I want to make a multi-selection "dropdown" box, what I did is click the small "fx" button, in the pop up expression window, input the expression:

 =IIF(ISNOTHING(Parameters!SelectedValues.Value), Fields!Col.Value, Parameters!SelectedValues.Value + "," + Fields!Col.Value)  

So what we did here, is we created a drill through action on the cell C1R2, when click this cell, it will go to itself with the current report parameter value, and the cell value.

That's it. Now you have a "dropdown" box on your report. The parameter values will be passed into the the report itself continually, like screenshot shows below:

Certainly there are many other ways to improve this "dropdown" box. For example, in this "dropdown" box we can select "Option" multi times, make the result ugly. To fix it, we can fix it by using "replace" function in the parameter expression.

There are lot more to improve, certainly. But what I want to show you, is by using some native functions in the reporting service, we can still build some interaction behavious. Just like when I chat with my friends, the technology could have the boundary, but our imagination is endless :D

Telstra SMS API Quick Demonstration

Telstra recently opened its Dev portal to public. So I just spent some time on its SMS API, and record what I found here.

Overall, Telstra did a good attempt for developers. The SMS API Telstra published allows end user to send 1000 sms for free per month. But there are still quite a lot things need to be improved: documentation, tutorials, API functionality, error handling, etc.

Now time for the demonstration. The application I built is like this:

When I send a SMS from the application, my mobile looks like

And then from the application screenshot, you can see the response I made from my mobile.

To do this, firstly you need to have a developer account. You can register through the link here. In Telstra Dev centre, there are three APIs are available at the moment, as shown below

What we need today is the SMS API. Now click My Apps link, you should be navigated to the My Apps portal.

Click the button "Add a new App" on your right hand side, then you should see the new app registration window like below. Remember in this post we are going to build the SMS App, so just check it. The call back url can be used for application verification in a real world scenario. But in this demonstration, I just create a dummy one.

After you create the App, your request should be shown as pending

Just give it a few hours. When you come back, you should see the Pending status has been changed to Approved. Now expand the App, you can see the consumer key and consumer secret.

Now in Visual Studio, I created a winform project, and add a reference for Newtonsoft.Json (find here), as we need to parse json for the API.

The code itself is quite simple, step by step, we need to

• Create a Token by using consumer key and consumer secret;
• Send SMS;
• Refresh to get the response;

Long in short, the complete code looks like this:

 public partial class Form1 : Form  
   {  
     private TelstraToken TheToken;  
     public Form1()  
     {  
       InitializeComponent();  
     }      
     private async void button1_Click(object sender, EventArgs e)  
     {  
       string ConsumerKey = "<consumer key>";  
       string ConsumerSecret = "<consumer secret>";  
       TheToken = await GetAccessToken(ConsumerKey, ConsumerSecret);  
       string Mobile = txtMobile.Text;  
       string Sms = txtSMS.Text;  
       SMSMessage SMS = new SMSMessage();  
       SMS = SendSMS(Mobile, Sms, TheToken);  
       lstSMS.Items.Add(SMS);  
       lstSMS.DisplayMember = "Mobile";        
     }  
     private void btnRefresh_Click(object sender, EventArgs e)  
     {  
       SMSMessage item = (SMSMessage)lstSMS.SelectedItem;  
       CheckStatus(item, TheToken);  
       if (!string.IsNullOrEmpty(item.ResponseContent))  
       {  
         txtResponse.Text = "Response: " + item.ResponseContent + Environment.NewLine + "RepondTime: " + item.AcknowledgedDt.ToShortDateString();  
       }  
       MessageBox.Show(item.Status);  
     }  
     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", "SMS"}  
       });  
       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.ExpiryDt = DateTime.Now.AddSeconds(double.Parse(jObj["expires_in"].ToString()));  
         }  
       }  
       catch (Exception ex)  
       {  
         MessageBox.Show(ex.Message);  
       }  
       return Token;   
     }  
     private SMSMessage SendSMS(string mobile, string message, TelstraToken token)   
     {  
       SMSMessage msg = new SMSMessage();  
       if (token.ExpiryDt <= DateTime.Now)  
       {  
         MessageBox.Show("Session Expired!");  
         return null;  
       }  
       WebClient Client = new WebClient();  
       Client.Headers.Clear();  
       Client.Headers.Add("Authorization: Bearer " + token.AccessToken);  
       Client.Headers.Add("Content-Type", "application/json");  
       string command = "{\"to\":\"" + mobile + "\", \"body\":\"" + message + "\"}";  
       try  
       {  
         byte[] buffer = Client.UploadData(@"https://api.telstra.com/v1/sms/messages", "POST", Encoding.Default.GetBytes(command));  
         string bufferstring = Encoding.Default.GetString(buffer);  
         JObject o = JObject.Parse(bufferstring);  
         msg.MessageID = o["messageId"].ToString();  
         msg.SendDt = DateTime.Now;  
         msg.Status = "PEND";  
         msg.Mobile = txtMobile.Text;  
         msg.Message = txtSMS.Text;  
       }  
       catch (Exception ex)  
       {  
         MessageBox.Show(ex.Message);  
       }  
       return msg;  
     }  
     private void CheckStatus(SMSMessage msg, TelstraToken token)  
     {  
       string status = string.Empty;  
       if (token.ExpiryDt < DateTime.Now)  
       {  
         MessageBox.Show("Session expired!");  
         return;  
       }  
       WebClient Client = new WebClient();  
       Client.Headers.Clear();  
       Client.Headers.Add("Authorization: Bearer " + token.AccessToken);  
       try  
       {  
         byte[] buffer = Client.DownloadData(string.Format("https://api.telstra.com/v1/sms/messages/{0}", msg.MessageID));  
         string bufferstring = Encoding.Default.GetString(buffer);  
         JObject o = JObject.Parse(bufferstring);  
         msg.Status = o["status"].ToString();  
         byte[] Response = Client.DownloadData(string.Format("https://api.telstra.com/v1/sms/messages/{0}/response", msg.MessageID));  
         string ResponseString = Encoding.Default.GetString(Response);  
         //JObject oR = JObject.Parse(ResponseString);  
         JArray a = JArray.Parse(ResponseString);  
         foreach (JObject jo in a)  
         {  
           if (jo["content"].ToString() != "N/A")  
           {  
             msg.ResponseContent = jo["content"].ToString();  
             msg.AcknowledgedDt = DateTime.Parse(jo["acknowledgedTimestamp"].ToString());  
           }  
         }  
       }  
       catch (Exception ex)  
       {  
         MessageBox.Show(ex.Message);  
       }  
     }  
     private class SMSMessage  
     {  
       //unique id of the message  
       public string MessageID { get; set; }  
       //datetime of the message was sent  
       public DateTime SendDt { get; set; }  
       //current status of the message  
       public string Status { get; set; }  
       //message body  
       public string Message { get; set; }  
       //sms recipient  
       public string Mobile { get; set; }  
       //datetime when the message acknowledged  
       public DateTime AcknowledgedDt { get; set; }  
       //response  
       public string ResponseContent { get; set; }  
     }  
     private class TelstraToken  
     {  
       public string AccessToken { get; set; }  
       public DateTime ExpiryDt { get; set; }  
     }  
   }  

Just some quick explanation:

• The token will be expired after certain time (3600), therefore when I create the Telstra token, I will record the expiry date time of the token.
• There is 160 characters limitation of the SMS API, but we need to handle by ourselves, e.g. read amount of characters in the application. Otherwise it will return server error 400.
• When send the message, I will store the message into the SMSMessage class, and list it into the listbox. For the listed message, I can check its status by click refresh button.

The problem of this API is that it will send the message via a random Telstra owned mobile number. As you can imagine, we could treat it like a fraud SMS. So hopefully it can be improved in its future release.