Thursday, 17 December 2009

British Airways Cabin Crew SQL Timeout Exception

Right, before getting to my actual solution to the British Airways SQL Timout Exception, I'll explain where I came accross the problem in the first place.

Its called the news. BA cabin crew are planning to strike over Christmas, I am seriously pissed off with them, not because they want to strike but because of their reasons.

They say: "we are understaffed and we arent paid enough"

All I hear is: "I only get paid at least £20,000 a year to be a barman on a plane serving food and drink with a once-a-day task of waving my arms around while nobody pays attention. In my free time I'm generally somewhere hot, staying in a hotel that the airline pays for, getting lashed on duty free alcohol, smoking duty free fags and shagging my work colleagues. It's not on, I only get to go abroad 40 times a year and in my career I have only seen 100 different countries. Im off on strike, I dont care that I'm ruining the christmas of countless children, some of whom were going to lapland to see santa but now cant and will probably be scarred for life. I also dont care that by striking I am putting millions of customers off flying with BA and from the revenue loss there may be redundancies in which case my job will be gone and this strike will be pointless anyway."

So, the problem here is that this statement times out due to the high number of records returned:

SELECT * FROM BA_CabinCrew WHERE IsBastard = 1

So to get around the problem I found the best soluton to be:

DELETE FROM BA_CabinCrew WHERE IsBastard = 1

In English, sack the lot of them. There's 2.1 million people out of work in this country, run a mass recruitment drive, it won't take long to replace them all and theres 8 days until Christmas so you could probably have them all trained up by then aswell.

Rant Over. My next post will be purely C# or T-SQL, promise :)

Dedicated to BA Victims Lee-Man and Ann

Thursday, 10 December 2009

SQLDataReader and Output Parameters

I have a WPF ListView bound to a Generic List of my custom business objects, I decided to implement a backgroundworker to populate the collection and report progress to a ProgressBar control.

So, I need a record count and, for performance, a SqlDataReader. My thought was to return both results from a stored procedure and the nicest way to do that is with an OUTPUT parameter.

The goal is to return the record count from the output parameter to my progress bar BEFORE reading the SqlDataReader so thet I can set ProgressBar.Maximym, then do a while loop to populate the collection incrementing ProgressBar.Value on each iteration.

Can I do this? NO.

I had it all coded, ran it, and got a result of null in the parameter, no matter what I tried it refused to give me a value. I researched for a couple of hours to no avail - then I found a small note on a web site that with a SqlDataReader, you must complete a full read of the reader before the output parameter becomes available.

Well, I'm screwed, I can revert to using a DataTable and yes it wont make a massive performance difference but for the population of the collection a SqlDataReader is definitely the best option and I am denied it.

Column Header Sorting In WPF - Damn You Intellisense!

Ever since I started using WPF I have been annoyed by the lack of sorting support from its column headers, it didnt even seem to offer a click handler for the headers of any kind!

Having wandered around Google I found some forums where users were talking about the problem, with one MS post saying "sorry its not supported, we will endeavour to add it in the next version" while referencing version 1.

I stumbled upon one post which said that "GridViewColumnHeader.Click" would do the trick, but when I tried to use it there was no sign in intellisense. I use Visual Studio 2008 so I thought that maybe it was something from a 2005 service pack.

At the time I moved on to something else but I had the same problem this week so I started researching again. I found nothing at first, but then I stumbled upon a forum where a user said "its not in intellisense, but if you type it in and hit '=' it will generate the correct event handler as usual and it will work perfectly"

Voila, problem solved