I built a piece of code sometime ago and today I decided to optimize it.

My use case is this: a user can upload some photos of his house for example. Image dimensions (height x width) are not restricted. The user can upload any photo as long as its size is less than or equal to 750 KB.

Then, I have a listing page (Index.cshtml) that has a WebGrid. The grid shows realties available for sell, rent, etc. In this grid I want to display just a thumbnail of a given photo (the first one I grab as per code bellow) for each realty. I placed a constraint such that the thumbnail dimensions must be 100x100 pixels. Of course one shouldn’t load the full size version of a photo just to show it on the grid. The grid has 10 rows per page and if the images are on the range of 500 KB, the load put on the user connection to transmit all those photos from the server to the client would be high: 10 x 500 KB ≃ 5 MB. This would be an unresponsive page! No doubt…

Googling a little bit I stumbled upon something I didn’t know yet. It’s called WebImage and comes in System.Web.Helpers just as the WebGrid I discussed earlier. With this little kid on the block, one can have a controller “action” that does something like this:

public void GetPhotoThumbnail(int realtyId)
{
    // Loading photos’ info from database for specific Realty...
    var photos = DocumentSession.Query<File>().Where(f => f.RealtyId == realtyId);

    if (photos.Any())
    {
        var photo = photos.First();

        new WebImage(photo.Path)
            .Resize(101, 101, false, true) // Resizing the image to 100x100 px on the fly...
            .Crop(1, 1) // Cropping it to remove 1px border at top and left sides (bug in WebImage)
            .Write();
    }

    // Loading a default photo for realties that don't have a Photo
    new WebImage(HostingEnvironment.MapPath(@"~/Content/images/no-photo100x100.png")).Write();
}

Calling the .Write() method does the magic. As you can see, I’m not saving the thumbnail in the disk, that is, a new image is generated on the fly/dynamically and sent to the user’s browser. Amazing…

In a view using the Razor syntax one would have something like this to display the photo:

<img src="@Url.Action("GetPhotoThumbnail", new { realtyId = item.Id })" width="100" height="100"/>

Another useful variation/overload of the above action method is this one (even more dynamic):

public void GetPhotoThumbnail(int realtyId, int width, int height)
{
    // Loading photos’ info from database for specific Realty...
    var photos = DocumentSession.Query<File>().Where(f => f.RealtyId == realtyId);

    if (photos.Any())
    {
        var photo = photos.First();

        new WebImage(photo.Path)
            .Resize(width, height, false, true) // Resizing the image to 100x100 px on the fly...
            .Crop(1, 1) // Cropping it to remove 1px border at top and left sides (bug in WebImage)
            .Write();
    }

    // Loading a default photo for realties that don't have a Photo
        new WebImage(HostingEnvironment.MapPath(@"~/Content/images/no-photo100x100.png")).Write();
}

Then, in the view one would call the action passing the width and height variables as per requirement:

<img src="@Url.Action("GetPhotoThumbnail", new { realtyId = item.Id, width = 100, height = 100 })" />

There are a handful of available functions in a WebImage. You should really check them out.

You can find more insightful info about the WebImage class reading this post by Gunnar Peipman: ASP.NET MVC 3 Beta: Simple image manipulations using WebImage helper

Note: there really are better ways of handling this kind of situation (adding image caching, etc – see ImageResizer) but for the site I’m working on, the solution I show in this post is sufficient and pretty responsive right now.

Hope it helps.

In a SQL Server database I have a table called users which has a column named signature. This column is of type Image.

My production SQL Server is located in a shared hosting environment.

One problem I’ve been facing lately is that I need permission to execute an UPDATE statement to insert a signature image for a given user. This problem occurs because to insert a signature image for a given user I have to execute a BULK statement like this for example:

--Update existing user
UPDATE users SET [signature] =(SELECT MyImage.* from Openrowset
Bulk 'C:\MyProject\trunk\MyCompany.Product\MyCompany.Product.Web\
Images\Signature.jpg', Single_Blob) MyImage) where Id 
= '1111aaaa-1111-11aa-a111-111111a1a1a1'

The query above works fine in my local machine but when I tried to execute it on the remote/production server, I got this beautiful message:You do not have permission to use the bulk load statement.

In the shared hosting environment the execution of Bulk command is disabled by default for security reasons. This is annoying but totally understandable!

Using Bulk load - allows the user to populate a database from a file. It’s not available in shared environment because it is necessary to insert client files on SQL server locally (the production server).

So… great! I need a way to bypass this limitation because I won’t spend tubes of money paying a dedicated server… it’d make sense if and only if I needed a dedicated server.

1st try: import a specific user row from my local SQL Server to the remote instance using SQL Server Management Studio Import task. I got an error about constraint key violation because I already had the same row (for that user) on both databases. It’s just a matter of updating the signature column in the production database. This seemed to be a pain path.

2nd try: consider a dedicated server? No thanks… hehehe

3rd try and solution: a few days later I found myself thinking about this problem again (this signature column updating thing is a recurring task) and so I decided to find another way and it came to light - link the remote server to my local SQL Server Express instance and write a beautiful SQL query that does the job.

First I stopped in this excellent blog post with a step by step guide written by jen: Create Linked Server SQL Server 2008
This post provided everything I needed to link both SQL Server instances.

To make sure you have linked your server correctly, you can execute this query in your local server:

select server_id, name, product, provider, data_source, [catalog], is_linked
from sys.servers

The above query gave me this result:

Figure 1 - Linked servers linked to my local SQL Server Express instance

Then I Googled about Update with Select + SQL Server or something like that and found this StackOverflow question: Updating a table with multiple values from a select statement where the date matches. Lieven’s answer helped. I just had to adapt it to my case. This is the SQL code that does the dirty work:

UPDATE  U
SET     U.[signature] = users.[signature]
FROM    [LOCAWEB].[laudotech].[dbo].[users] U INNER JOIN users
ON users.id = U.id AND
U.id = '1234aaaa-5678-90aa-b123-456789a0a1a2'

The above query must be executed within the context of the local SQL Server instance of course. Where the linked server resides.

To give you a view… this is how all this is configured inside SQL Server Management Studio (SSMS):

Figure 2 - SSMS Object Explorer and the Linked Server LOCAWEB in my local SQL Server Express instance

There’s so many things one can do with SSMS that I feel really happy in learning one more of those things. Last week I blogged about Import/Export SQL Server database data with SSMS. Take a look at it.

Man! Have I said that I Iike working with databases!?

Hope it helps.