May
18
2012

MSSQL 2008 Try Catch and Transactions

Generally in my day to day duties I have no need for SQL based error catching but lately I have been working on a newspaper subscription system and a lot of key business logic is held in stored procedures for processing of subscriptions week by week and issuing lost or non-delivered vouchers as required.   Now as I am talking about customers actually receiving a product I needed to be sure that everything that needed to happen, actually happened and if an error occurred I would first like to try and resolve any of these issues and carry on but when or if an error occurred that would make me less than 100% confident that everything that needed to happen actually happened then I want all changes to be reverted and to be notified.

Firstly I started off with a simple TRY CATCH statement, because this is a mission critical procedure, if any errors occurred I want everything to revert so I only need to capture a generic exception.

BEGIN TRY

        --   SQL COMMANDS

END TRY
	
BEGIN CATCH

      -- ERROR HANDELING, IN THIS CASE I SENT AN EMAIL

      EXEC msdb.dbo.sp_send_dbmail @profile_name='DB Error Email',
			@recipients='email@yourdomain.com',
			@subject='Procedure ABC Failed',
			@body='The stored procedure #ABC# Failed, all changes have been rolled back.'

END CATCH

Now that I am catching all errors I need to commit all changes if my procedure executed successfully or I need to revert my changes back.  Luckily MSSQL 2008 makes this very simple.  Taking the above example I did the following.

BEGIN TRY
BEGIN TRANSACTION

        --   SQL COMMANDS

        EXEC msdb.dbo.sp_send_dbmail @profile_name='DB Email',
			@recipients='email@yourdomain.com',
			@subject='Procedure ABC Successful',
			@body='The stored procedure #ABC# completed successfully, all changes have been comited.'

COMMIT TRANSACTION
END TRY
	
BEGIN CATCH
ROLLBACK TRANSACTION

      -- ERROR HANDELING, IN THIS CASE I SENT AN EMAIL

      EXEC msdb.dbo.sp_send_dbmail @profile_name='DB Email',
			@recipients='email@yourdomain.com',
			@subject='Procedure ABC Failed',
			@body='The stored procedure #ABC# Failed, all changes have been rolled back.'

END CATCH

This now ensures that my procedure runs successfully, commits changes and then emails me to let me know, or it rollbacks any changes upon an error and lets me know.  Very cool stuff and very very simple.

May
16
2012
ASP.NET // C# // MVC // Nuget

MVC & Attribute Routing

I have been developing using MVC 2 and most recently MVC 3 for the past year and a bit, when I first started I really struggled without the onclick event of a button linked to a method.. Ahhhh how easy webforms can be. Doing the same in MVC is easy enough if you only have one submit button but if you have multiple buttons and want to fire different methods depending on which button is clicked can be a bit of a nightmare, I won't go into too much detail now but I will be posting about ways you can do this in a later post.


One cool package I have come accross on nuget is AttributeRouting, this allows you to specify routes using attributes on your controllers and actions, pretty cool eh? Not only is it cool it is so much easier and cleaner to create your routes this way, before using AttributeRouting my Global file was a complete mess, I've also found debugging routes can be a pain. AttributeRouting comes to the rescue on all these points.

Once you installed AttributeRouting you just simple put an attribute on your controllers/actions.

[GET("Home")]
public ActionResult Home()
{
    return this.View();
}

If you are posting to a method you can do the following.

[POST("Login")]
public ActionResult Login(string username, string password)
{
    // Do something
   return this.View();
}
Apr
10
2011

google+ vs facebook

The Battle between Facebook and Google has taken one more step closer to full blown war of the social network with the field trial of Google+

Today I was sent an invite to the Google Plus system. My first impression was that Google had just basically re-skinned the Facebook design as you have the navigation at the top which includes notifications and links to modify your profile. There is then a 3 column setup just like Facebook with the left hand column used for filtering the "Stream" - Which is Google's name for the "News Feed", and a Chat feature below. The middle column is made up of all your friends posts which you can 'like' or 'comment' on. Finally the right hand column contains suggestions to who you may know, a list of people in your 'Circles' and 'Hangout'.

Circles I can only describe as Groups. By default you have a 'Friends', 'Family', 'Acquaintances' and 'Following'. You can also create your own Circle. When you are posting anything to your Stream you can pick which Circles can see the post. See the photo gallery for some pictures of the awesome drag and drop UI the Circles system uses.

Hangout allows group video chat, I have not tried this yet but it looks like it could be a key factor in persuading Facebook faithful over to Google Plus.

Huddle is the Goolge Plus version of Group Chatting. Again it is all based off your Circles.

For more information see the Google Plus website:- http://www.google.com/intl/en_uk/+/learnmore/