![]() 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. Further research then lead me to the DataContractJsonSerializer class within. 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. NET to use it to make myself more productive from time to time. NET developer, I just happen to know enough about. Although I knew about JSON, I have never really worked with JSON before as I'm not really a full on. 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. The last setting I changed is more of a personal preference and that was not to deploy the code my development server.Īfter that, I proceeded to add a new stored procedure classĪnd that gave me a basic shell to start doing some code. It is easy enough to create a key/login for the assembly when it comes to deployment time. 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. 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. 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. 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.Īs 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". Once the empty project shell was created I went into project properties and set the. Next I fired up VS2010 and started a new CLR project.Īnd set my database reference to the DB that I have just created 'xxxxxxxxxxe7966ef15c27xxxxxxxxxxxxxxxxxxxxe7966ef15c27xxxxxxxxxx') VALUES ('xxxxxxxxxxe7966ef15c27xxxxxxxxxx', INSERT INTO Configuration (ApplicationKey, Token) In my SQL instances the keys are encrypted, but I've removed all of that logic to keep this article simple. Note: These keys are very sensitive pieces of information as they effectively allow someone to impersonate the user that created the token. 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. All of the source code and the compiled CLR are available for download at the bottom of this article. With these two keys, I was able to start work on the project.īy 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. Because of the way I intend to use/access the API, I created a token that was set to never expire. You can set a token to expire after a certain amount of time or to never expire. Next on the list is to get a token which authenticates you within Trello and again this is all explained within their website. ![]() This is straight forward and can be done from their website. The first thing that you have to do with Trello to access the API is to get an application key. So one large coffee later, I was off creating my CLR. ![]() 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. 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.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |