Omar AL Zabir blog on ASP.NET Ajax and .NET 3.5

Think you know how to write UPDATE statement? Think again.

When I was a kid, my mom used to read me UPDATE queries every night before I went to sleep. I heared many stories about updating objects to database where most of them were the same old:

So, I grew up with the same ideas on how to update objects in tables as other kids do. All the UPDATE queries involved taking all the fields and the update stored procedures used to have all the properties of the objects. If you are using some Code Generators (e.g. Code Smith) and generating data access layer codes and stored procedures for objects, you will see almost all the code generators and ORM tools generate UPDATE statments with all the fields in the SET block. Let me show you with an example how evil this idea is.

Imagine a table like this:

This is a table from Pageflakes database. In this table, we store all the RSS feeds user has read from a particular RSS channel. UserId is a foreign key to User table and ChannelID is a foreign key to Channel table. Pretty straight forward. We had a harmless update stored procedure generated using Code Smith using the famous .NET Tiers template.

Look at the query plan and see how horrible it really is:

There are 2 Clustered Index Seeks, one Table Spool (very expensive), 2 Nested Loops, 1 Assert, 1 Clustered Index Seek. If you look at the IO Statistics, you can see how truly evil this query is:

Table 'RSSChannel'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PageFlakesUser'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ChannelSubscribedByUser'. Scan count 1, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 3, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

It is making SQL Server go to the tables which are refered as foreign key during a single row update!

Generally when we update a row in a table which represents an object, we rarely change the value of the fields which are foreign keys to other tables and has index on them. Most of the time, the updates are on the fields which contain properties, not relations. For example, 99% of the cases, you will update properties of an Employee object like FirstName, LastName, Age etc. 1% case you will modify the CompanyID (because s/he was fired) which is a foreign key to Company Table. But if you go to your database and see the stored procedure which updates the Employee object, you will see this:

Don't be ashamed. I know we all have queries like this every where.

If you remove those unwanted fields which generally have Foreign Key and Index on them, you can gain significant performance improvement. When I just change the UPDATE statement to this:

See the query plan:

There's just one Clustered Index Update. No Table Spool, no Clustered Index seeks, no nested loops, no asserts. The IO statistics shows how significant the improvement really is:

Table 'ChannelSubscribedByUser'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 3, lob physical reads 0, lob read-ahead reads 0.

There's just one update and nothing else. Also the number of Logical Reads is 6 compared to 15.

So, you thought you knew how to write UPDATE statements? Think again.

Enrich your blog & website with cool widgets!

You can now put any Pageflakes Flake (aka Widgets) on your blog or websites! Flakes are fully functional feature rich customizable widgets which you can now take-away with you outside Pageflakes and eat it anywhere you like. There are so many ways you can use the flakes to enrich your blog and websites. I will try to explain some of the cool ways to make really good use of some cool flakes.

Let's play

There are many cool games like Sudoku which you can put on your page and waste endless time on it. Start wasting your time here right now:

Also check out this cool Spider:

Enough fun. Let's get into some work. You can maintain a To-Do-List on Pageflakes and publish it on your website or blog and let others know what you are up to. Check out my To-do-list for writing articles:

Event Calendar

You can maintain a family event calendar or group events on a Calendar flake and then put it up on your website for others to see and participate.

Share files with others

Your blog does not let you share files? No worries, put a Box.net flake on your blog and allow others to download files from it. It's so easy to share files with this cool flake. You can have 1GB free storage right on your blog or website.

Your office on the web

You can put your to-do-list, calendar and mail flake anywhere you like in order to have a "mobile office" for you. But the "Web Office" is incomplete without a powerful authoring tool. Here you go, the great "Notepad" flake. Don't underestimate this flake judging by its size. I have a whole Codeproject article inside it. And that's not all it can hold. Click on the "open" button and see what else I have there. You can only read the files, but I see a full fledged WYSIWYG Html Editor here.

Share Photos

You can show your favorite photos from Flickr by putting a Flickr flake on your website. See my favorite photos:

See how to do all these from this blog post.

You might say, "Big Deal, Google did it long time back." You're wrong. Google Widgets are throw away widgets. They cannot contain your data and show it on another website. You cannot have a to-do-list populated with your tasks and put it on another website. But Pageflakes can. The flakes you put on another website or on your blog are the very flake that is running on your Pageflakes page. If you modify the flake from Pageflakes, others can see the latest data from your blog. It also works the opposite way. You can work on the flake right from your blog or website. You need not go to Pageflakes in order to access the flake. If you don't like to work on Pageflakes all the time, then just put the flake that you need on your website and work from there.

Eat cornflakes, use Pageflakes

This is what I do every morning when I wake up. I take cornflakes and milk in a bowl and start my laptop and visit my "Reading" page. I eat cornflakes and read my Pageflakes page:

This gives me daily nutrition for both my body and mind. Start Page is a really good productivity tool for infotainment. It saves me from going to all these sites in order to see what's new. I read news feeds, stories from Digg, new bookmarks from Del.icio.us - all from the same page. It saves so much of my time every single day and keeps me informed of things which is essential to maintain my core competency.

I have made my "Reading" page public, so you can also see what I am reading everyday by going to this URL:

http://www.pageflakes.com/omar.ashx

If you have some cool pages that you have setup on Pageflakes, please make them public and let me know the URL. We can share our interests with each other and find out cool news things.

Make a surveillance application which captures desktop and emails you as attachment

Some time back I needed to capture a certain computers desktop in order to find out what that user is doing every day. So, I made a .NET 2.0 Winforms Application which stays on system tray (optional) and capture the desktop in given time interval (say every 60 secs) and emailed the captured images to me as message attachment (say every 30 mins). It ensures the captures are small enough and embedded inside HTML email so that I don't need to open hundreds of attachments and see the screenshots. I could just read through the email and see the captures made. You will find this application quite handy in many use cases including:

• Keep an eye on developers who spend too much time on web and chatting. See what they really do.
• Keep an eye on your better half incase s/he is cheating on you.
• Keep an eye on your teenagers and see how they use computer. Find the amount of time they browser porn on web.

All you do is sit back and relax in your office and the app informs you every 30 mins via email what your subject is doing on the computer. You don't need to worry about missing some captures when you are away from your computer. It will be safely kept in your inbox and you can go through all the captures on your weekend.

Configure the following settings from the Visual Studio Settings Designer (if you have Visual Studio):

Configure the path where the screenshots will be stored. Then configure the duration, default is every 1 minute, one screenshot is taken. "To" contains the email address of yours. Use free email services because very soon you will find it's filled up. Username and Password is for the SMTP authentication. "SMTP" contains the SMTP Server name or IP. "MailSendDelay" is the delay between sending emails.

All you need to do is build the app and install/run it once on the computer which you want to keep an eye on. It will hide itself on the system tray as a harmless icon and register itself on the startup and start capturing immediately. After a week or two, cleanup the "C:\temp" folder where the screenshots are kept.

You can also configure the properties at run time after running it once on a computer. This is for those who does not have Visual Studio in order to configure the settings before building it.

In order to launch the configuration dialog box while the application is running, find the icon on the system tray and click mouse buttons exactly in this order:

• Press left and right mouse button on the icon one after another
• Release only the right mouse button while holding the left mouse button down

This will bring up the configuration dialog:

Here you can make changes while it is running.

Let's learn some interesting stuffs from this application:

1. Prevent closing the application when close button is clicked:

2. Capturing the screenshot

First a bitmap object for the whole screen size is created and then the graphics from the screen is copied to the Bitmap object.

3. Convert the Bitmap to low resolution JPEG

Here we configure the JPEG codec with very low resolution (10%). GetEncoderInfo is a function which runs through all available codecs and finds the one we need.

4. Saving bitmap using codec

5. Handling Win32Exception

I noticed sometimes during screen capture, an unknown Win32Exception throws up. There's no way to work around this problem until I restart the application. Here's how I do it:

6. Email the pictures as embedded image in HTML format

First connect to the mail server using System.Net.SmtpClient:

We are going to construct an Html mail where the images will be inline. The tricky part is to build the body of the message. The body requires that we create <img> tag for each image inside the body in this format:

The ID needs to be the ContentID of the LinkedResource instance which is created for each image. Here's the code:

I keep a counter for the last capture file name and the last emailed file number. Then for each capture file which has not been emailed yet, I create a LinkedResource for the image and then add it in the resources list. I also build the body of the message which contains the <img> tag with the LinkedResource ContentID.

Then we create something called AlternateView for the message body which says the message has a HTML view:

This view contains the HTML body and the resource collection.

After this, the email is sent asynchronously so that the screen capture process does not get stuck. It generally takes a while to send the image with all the screenshots embedded. So, you can't do this synchronously.

That's all folks!

Download the source here

Build Google IG like AJAX Start Page in 7 days using ASP.NET AJAX and .NET 3.0

I will show you how I built a start page similar to Google IG in 7 nights using ASP.Net Ajax, .NET 3.0, Linq, DLinq and XLinq. I have logged my day to day development experience in this article and documented all the technical challenges, interesting discoveries and important design & architectural decisions. You will find the implementation quite close to actual Google IG. It has drag & drop enabled widgets, complete personalization of the pages, multi page feature and so on. It's not just a prototype or a sample project. It's a real living and breathing open source start page running at http://www.dropthings.com/ which you can use everyday. You are welcome to participate in the development and make widgets for the project.

You can find the article on CodeProject:

Build Google IG like AJAX Start Page in 7 days using ASP.NET AJAX and .NET 3.0

If you like it, please vote for me.  

What to do when you kill your database with your own hand

Couple of months ago, we had an interesting day. It was a bright summer morning. We were all in the office doing our regular work. Developers writing codes, QA testing the site. We were all happy and smiling as there was no major bug. Some were drinking coffee and having nice chit chat. Our honorable Sys Admin came to office as usual late after noon. We all welcomed him remembering his day and night inhuman effort to keep our systems up and running fighting against germs and bacteria. As usual he started his day logging into servers one by one checking their health. He connected to our maintenance server via Remote Desktop. Did some routine check up and found that there was no space on hard drive. So, he decided to delete the database on the maintenance server as it was quite old. He started SQL Server Management Studio, entered sa and password, selected the database, pressed delete. As his habit, on the confirmation dialog he pressed enter without looking into it. Things went cool, database got deleted. But strangely the maintenance server did not free up any hard drive space.

In the meantime, there were sharp screams from other rooms. We all ran to see whether somebody fell down or had an electric shock or not. Seemed like every one was intact. But with horror, we looked into all the screens on our desktops and saw this:

This is our error handler page where users are redirected when there's any unknown catastrophic failure. This page appears when something goes wrong, really wrong. We do this by having the <customErrors> block in web.config:

If somebody fell down or had electric shock, we would not mind at all but this is generally beginning of a nightmare for us. Then we saw our Sys Admin was shaking and his face turned white out of blood. He somehow stood up trembling with fear and came to me and said, "My blood pressure is going sky high. I think I have deleted production database".

I told him, "no worries, I have done this before", of course on the outside. Let's not say what I felt inside. So, we first looked into Recycle Bin. Nope, no trace. It was a 30 GB file. There's no way Windows is going to store it on Recycle Bin. Then we searched for several undelete utilities both commercial ones and free ones. All failed on regular attempt. Some showed an advanced recovery can restore the file but it will take 2 hours of scanning. This will be disastrous if we go down for 2 hours. But we had no choice. Last backup we took was the previous day. If we restore it, we are going to lose thousands of users and their page setup. We will also lose hundreds of signups. So, we went for the 2 hours scan. After 2 hours, the scan reported it found the MDF file. But when we said recover it, it recovered a 0 byte file. We tried another product, same result. In the meantime 4 hours past by. So, we had to take a decision. We has no other choice but to restore the previous day's backup. So, we did, and the site went live.

Now it was time to find out all those poor souls who lost their data. We maintain a log file where we record all important activities like adding new page, registering, first visit etc. It was a tab delimited file like this:

01.01.2007 08:39:35 e5ca904c-0348-42cf-9d1b-6fb932ec930d Create Anonymous user if necessary 0,25
01.01.2007 08:39:36 e5ca904c-0348-42cf-9d1b-6fb932ec930d Get Page: __RSSFEED__ 0,109375
01.01.2007 08:39:36 e5ca904c-0348-42cf-9d1b-6fb932ec930d Load Modules in page #1413882 0,1875
01.01.2007 08:39:36 e5ca904c-0348-42cf-9d1b-6fb932ec930d GetPageflake 0,546875

It logs date and time of each action and also the duration of the process. This log helps us identify slow running operations and we can see almost in real time what's going on with the site.

After importing this log into database, we ran a query to find out user name of all users who signed up since database delete. Here, we discovered something interesting. We were expecting the date and time will be in server's own time. But Enterprise Library logs are always in GMT. So, we had to figure out what is the time difference from regional settings of the servers. Then we subsctracted it to match GMT. Then we calculated when the database was deleted and started finding the users after that. We got a list of email addresses whose signup was lost, almost around 400. We were lucky it happened on a weekend, so there was not much signup that day. Then we sent that email list to our marketing team and they sent apology letters to those users.

So, what we did wrong which you should make sure you never do:

• Sys Admin became too comfortable with the servers. There was lack of seriousness while working on remote desktop. It became routine monotonous absent minded work to him. This is a real problem with sys admins. On first month, you will see him very serious about his role. Everytime he logs into remote desktop on production or maintenance servers, there's a considerable amount of curves on his forehead. But day by day, it reduces and he starts working on production server as if he is working on his own laptop. At some point, someone needs to make him realize what is the gravity of his actions. He should wash his hands before sitting in front of remote desktop (or perform wudhu if he is muslim) and then say his prayer: "O Lord! I am going to work on remote desktop. Grant me tranquility and absolute concentration and protect me from the devil who whispers in my soul foul words and lures me to cause great harm to production servers"
• All databases had the same "sa" password. If we had different password, at least while typing the password, sys admin could realize where he is connecting to. Although he did connect to remote desktop on maintenance server, but from SQL Server Management Studio, he connected to primary database server as he did last time. SQL Server Management Studio remembered the last machine name and user name. So, all he did was enter password and hit enter and delete the database. Now we have put the server name inside the password. So, while typing the password, we know conciously which server we are going to connect.
• Don't ignore confirmation dialogs on remote desktops as you do on your local machine. Nowadays, we consider ourselves super expert on everything and never read the confirmation dialog. I myself don't remember when was the last time I read any confirmation dialog seriously. Definitely this attitude must change while working on servers. When Sys Admin tried to delete the database, there was a confirmation that there are active connections on the database. SQL Server tried its best to inform him that this is a database being used and don't delete it, please. But as he does hundred times per day on his laptop, clicked OK without reading the confirmation dialog.
• Don't put same administrator password on all servers. This makes life easier while copying files from one server to another, but don't do it. You will accidentally delete file on another server just like we do.
• DO NOT use Administrator user account to do your day to day work. We started using a Power User account for our day to day operation which has limited access on couple of folders only. Using Administrator account on remote desktop means you are opening doors to all possible accidents to happen. If you use a restricted account, there's no possibility of such accidents. 
• Always have someone beside you when you work on production server and do something important like cleaning up free space or running scripts, restoring database etc. Make sure the other person is not sleeping on his chair beside you.