Creating cards in Trello via the REST API and parsing the returned JSON all in a SQL CLR

2012/03/14

I recently blogged about how good I thought Trello was and shortly afterwards I found out that they did provide a full read/write RESTful API. Awesome! After a bit of thought I realised that I should be able to use the API to create new cards in Trello from within SQL by simply calling a CLR procedure and I could use that feature to help manage my distributed servers by getting the servers to create new cards in a Trello board when I want the servers to notify me of something. So one large coffee later, I was off creating my CLR.

The first thing that you have to do with Trello to access the API is to get an application key. This is straight forward and can be done from their website. Next on the list is to get a token which authenticates you within Trello and again this is all explained within their website. You can set a token to expire after a certain amount of time or to never expire. Because of the way I intend to use/access the API, I created a token that was set to never expire. With these two keys, I was able to start work on the project.

By the way, I used VS2010 professional and SQL 2008R2 for this project, but I can't see why you couldn't use some other versions with some minor tweaks if necessary. All of the source code and the compiled CLR are available for download at the bottom of this article.

So first of all, I created a new database to host the CLR and to store the keys I needed to connect to Trello with. Note: These keys are very sensitive pieces of information as they effectively allow someone to impersonate the user that created the token. In my SQL instances the keys are encrypted, but I've removed all of that logic to keep this article simple.

Once the empty project shell was created I went into project properties and set the .net framework to 3.5.

I then went to Signing, and I created a strong name key for the project as I will need to create an asymmetric key in SQL from the assembly later on.

As this CLR was going to access a remote server, the safety level on the Database tab of Project Properties needs to be defined as "External".

If you set the permission level to External, you do need to sign the dll and create an asymmetric key and login for it inside SQL to deploy it. This is exactly what I did when I took the CLR into production, but during development on my local SQL instance, I actually just set the database property TRUSTWORTHY to ON and this allowed visual studio to happily deploy/debug without the need for an asymmetric key. Setting the trustworthy database setting to ON is not really ideal for a production instance due to the potential security risks and I discourage it. It is easy enough to create a key/login for the assembly when it comes to deployment time.

The last setting I changed is more of a personal preference and that was not to deploy the code my development server.

After that, I proceeded to add a new stored procedure class

and that gave me a basic shell to start doing some code.

I quickly got the guts of the procedure working and was able to make requests against the API and return a JSON string but what I had to overcome next was how to parse the JSON that is returned and retrieve relevant data items. Although I knew about JSON, I have never really worked with JSON before as I'm not really a full on .NET developer, I just happen to know enough about .NET to use it to make myself more productive from time to time. After doing some research, most people were using libraries to do the work, but I didn't want to add an external library dependency to the project as I wanted to have all the code self contained within the CLR. Further research then lead me to the DataContractJsonSerializer class within .NET and after a bit of playing with it in a seperate console app, it looked ideal for what I wanted it to do, namely deserialise the JSON into a class.

However, when I went back to my CLR I quickly learnt that there are only a very small number of trusted references available to add to the CLR project and DataContractJsonSerializer was not one of them and the workarounds seemed quite hacky and I didn't want to do anything like that. Unwilling to do some crude string hacks on the JSON string, I searched a bit more for a JSON parser written in C# that only used refernces deemed safe by SQL that I could use in my project and came across this fantastic article which provided a JSON parser in C#. Perfect!!

After a while of trying different things and testing, I had come up with the following. (i've removed the JSON class code due to its size)

As I mentioned earlier, I'm a SQL developer, not a .net developer so I am sure that some of the techniques I have used could have been better done other ways, but for me it worked exactly how I wanted it to and I was really happy with it.

Next I needed to deploy it to one of my production servers but to do this, I needed to create an asymmetric key and login for it as I wasn't going to change the TRUSTWORTHY setting on the databases on these servers. So I took the dll created by my CLR project and copied it to my SQL server. I then ran the following code on my production box

From here, there are lots of things that can be done. The code can be firmed up to deal with problems better and more procedures can be created to use more of the API such as adding members to cards, labelling, due dates, moving cards around and even deleting them. It would also be good to store the urls in a table so if they change, it will be easier to update them. I'm going to build on this project over time and use it in a couple of ways. Firstly I have lots of remote SQL servers up and down the country with varying types of access. Most of them do have access to the www so I'll be using this CLR as a means for these SQL servers to alert me with any issues from the monitoring scripts that run regularly. Secondly, I'm going to use Trello as a skin for a couple of internal 3rd party SQL based systems that we have that are, how shall I say, not very good looking! Initially just a read-only board mind, but maybe read-write if I'm brave enough.