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.

CREATE DATABASE Trello
GO
USE Trello
GO
CREATE TABLE [dbo].[Configuration](
[ApplicationKey] [varchar](32) NULL,
[Token] [varchar](64) NULL
) ON [PRIMARY]
GO
INSERT INTO Configuration (ApplicationKey, Token)
VALUES ('xxxxxxxxxxe7966ef15c27xxxxxxxxxx', 
     'xxxxxxxxxxe7966ef15c27xxxxxxxxxxxxxxxxxxxxe7966ef15c27xxxxxxxxxx')
GO

Next I fired up VS2010 and started a new CLR project. 

and set my database reference to the DB that I have just created

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)

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Net;
using System.IO;
using System.Text;
using System.Collections;
using System.Globalization;
public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void CreateCard(SqlString AppKey, SqlString Token, SqlString BoardName, SqlString ListName, 
        SqlString CardName, SqlString CardDescription, out SqlString CardId)
    {
        ArrayList Boards, Lists;
        Hashtable Card;
        //retreive json string containing all the boards token has access to
        string BoardsJSON = GetBoards("https://trello.com/1/members/my/boards?", AppKey, Token);
        //parse the json string into an arraylist of hashtables
        Boards = (ArrayList)JSON.JsonDecode(BoardsJSON);
        //find the id of the board that we are interested
        string BoardId = FindValueInArrayList(Boards, "name", (string)BoardName, "id");
        if (BoardId != null)
        {
            //retrieve json string containing all the lists in the board
            string ListsJSON = GetLists(String.Format("https://trello.com/1/boards/{0}/lists?", BoardId), AppKey, Token);
            //parse the json string into an arraylist of hashtables
            Lists = (ArrayList)JSON.JsonDecode(ListsJSON);
            //find the id of the list that we want to create the card in
            string ListId = FindValueInArrayList(Lists, "name", (string)ListName, "id");
            if (ListId == null)
            {
                throw new Exception("List not found. Check that list name is correct and that the token has access to the list");
            }
            //create the card in the correct list and retrieve a json string containing the new card
            string CardJSON = PostCard(String.Format("https://api.trello.com/1/lists/{0}/cards?", ListId), AppKey, 
                Token, CardName, CardDescription);
            //parse the json string into a hashtable
            Card = (Hashtable)JSON.JsonDecode(CardJSON);
            //extract the id of the card and return it through the output paramter
            CardId = (string)Card["id"];
            return;
        }
        else
        {
            throw new Exception("Board not found. Check that board name is correct and that the token has access to the board");
        }
    }
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void AddCommentsToCard(SqlString AppKey, SqlString Token, SqlString CardId, SqlString CardComments)
    {
        Hashtable Comment;
        //create the comment against the card and retrieve a json string
        string CommentsJSON = PostComment(String.Format("https://trello.com/1/cards/{0}/actions/comments?", CardId), 
            AppKey, Token, CardComments);
        //parse the json string into a hashtable
        Comment = (Hashtable)JSON.JsonDecode(CommentsJSON);
    }
    static string GetBoards(string GetBoardsUrl, SqlString AppKey, SqlString Token)
    {
        //build up correct url + querystring and make request
        var Boards = SendRequest(GetBoardsUrl + String.Format("key={0}&token={1}", AppKey, Token) + 
            "&fields=name,closed,url", "GET");
        return Boards;
    }
    static string GetLists(string GetListsUrl, SqlString AppKey, SqlString Token)
    {
        //build up correct url + querystring and make request
        var Lists = SendRequest(GetListsUrl + String.Format("key={0}&token={1}", AppKey, Token) + 
            "&fields=name,closed&cards=none", "GET");
        return Lists;
    }
    static string PostCard(string PostCardUrl, SqlString AppKey, SqlString Token, SqlString CardName, 
        SqlString CardDescription)
    {
        //build up correct url + querystring and make request
        var Card = SendRequest(PostCardUrl + String.Format("key={0}&token={1}", AppKey, Token) + 
            String.Format("&name={0}&desc={1}", CardName, CardDescription), "POST");
        return Card;
    }
    static string PostComment(string PostCommentsUrl, SqlString AppKey, SqlString Token, SqlString CardComments)
    {
        //build up correct url + querystring and make request
        var Card = SendRequest(PostCommentsUrl + String.Format("key={0}&token={1}", AppKey, Token) + 
            String.Format("&text={0}", CardComments), "POST");
        return Card;
    }
    static string FindValueInArrayList(ArrayList Hashtables, string SearchKey, string SearchValue, string ReturnKey)
    {
        foreach (Hashtable h in Hashtables)
        {
            if ((string)h[SearchKey] == SearchValue)
            {
                return (string)h[ReturnKey];
            }
        }
        return null;
    }
    static string SendRequest(string Url, string Method)
    {
        //create new web request
        WebRequest wc = WebRequest.Create(Url);
        wc.Method = Method;
        //get response stream from the request and read it into a string. this will be a json string
        WebResponse wr = wc.GetResponse();
        Stream receiveStream = wr.GetResponseStream();
        StreamReader readStream = new StreamReader(receiveStream, Encoding.UTF8);
        string json = readStream.ReadToEnd();
        //tidy up
        receiveStream.Close();
        readStream.Close();
        wr.Close();
        readStream.Dispose();
        receiveStream.Dispose();
        return json;
    }
}
public class JSON
{
   //removed for brevity. See http://techblog.procurios.nl/k/news/view/14605/14863/How-do-I-write-my-own-parser-for-JSON.html about parser and http://www.opensource.org/licenses/mit-license.php about license
}

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

USE master
GO
--Create the key by referencing the dll file
CREATE ASYMMETRIC KEY TrelloAsymmetricKey 
FROM EXECUTABLE FILE = 'c:\temp\SQLTrelloExample.dll'
GO
--Create a SQL login from the key that was generated
CREATE LOGIN TrelloCLRLogin 
FROM ASYMMETRIC KEY TrelloAsymmetricKey
GO
--Grant the required level of access to the key
GRANT EXTERNAL ACCESS ASSEMBLY TO TrelloCLRLogin
GO
USE Trello
GO
--Import the assembly in the database from the dll
CREATE ASSEMBLY [SQLTrelloExample]
    AUTHORIZATION [dbo]
    FROM 'C:\temp\SQLTrelloExample.dll'
    WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO
--Create the two procedures that reference the methods in the assembly
CREATE PROCEDURE [dbo].[CreateCard]
    @AppKey NVARCHAR (32), 
    @Token NVARCHAR (64), 
    @BoardName NVARCHAR (100), 
    @ListName NVARCHAR (100), 
    @CardName NVARCHAR (100), 
    @CardDescription NVARCHAR (4000), 
    @CardId NVARCHAR (32) OUTPUT
AS EXTERNAL NAME [SQLTrelloExample].[StoredProcedures].[CreateCard]
GO
CREATE PROCEDURE [dbo].[AddCommentsToCard]
    @AppKey NVARCHAR (32), 
    @Token NVARCHAR (64), 
    @CardId NVARCHAR (32), 
    @CardComments NVARCHAR (4000)
AS EXTERNAL NAME [SQLTrelloExample].[StoredProcedures].[AddCommentsToCard]
GO

And that was it, all done and created within my production instance.

Now I can execute the newly created procedures and they in turn will create new cards in Trello for me.

DECLARE @AppKey NVARCHAR(32), @Token NVARCHAR(64)
DECLARE @CardId NVARCHAR(32), @CardName NVARCHAR(100)
SELECT @AppKey = ApplicationKey, 
    @Token = Token, 
    @CardName = @@SERVERNAME + ': Some key info'
FROM dbo.Configuration
EXEC CreateCard 
@AppKey = @AppKey, 
@Token = @Token, 
@BoardName = 'Production Monitoring', 
@ListName = 'Unfiled', 
@CardName = @CardName, 
@CardDescription = 'Some more information relating to event', 
@CardId = @CardId OUTPUT
EXEC AddCommentsToCard
@AppKey = @AppKey, 
@Token = @Token, 
@CardId = @CardId,
@CardComments = 'adding some extra comments to the card that would be useful'

and the resultant card with comment on the Trello board

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.

Downloads:

CLR Project Source Code

SQL to deploy CLR - TRUSTWORTHY ON version (this is easier to deploy if you just want to play with it)

SQL to deploy CLR + dll file version

Just to state the obvious here. The application key and token that I have used in this article are fake and do not work. You will need to generate your own key/token from the Trello site first

Enjoy!