Thursday 24 December 2009

ASP.NET: Retreive ID of inserted record

I needed to be able to access the returned identifier value from an Insert operation on my ASP page, so that I could use the value in other controls. You can do this by adding an event handler to your ObjectDataSource:

Protected Sub ObjectDataSource1_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.ObjectDataSourceStatusEventArgs) Handles ObjectDataSource1.Inserted
Dim returnvalue As Int32
returnvalue = Convert.ToInt32(e.ReturnValue)
' Do what you need to do with the ID here
Session.Add("CustomerID", returnvalue)

When your ObjectDataSource fires its Insert method, the value that the underlying BLL method returns is available for you to use in the above method. You can then set a session variable with this ID or do something else with it.

Just make sure that your BLL is returning the ID properly, or this won't work.

Job done!

Wednesday 23 December 2009

ASP.NET: Uploading Documents to SQL Server and Retrieving them

I've just completed a new function within one of my applications that allows users to upload binary files (such as Word, Excel, PDF, Powerpoint and images) and store them within SQL Server itself, rather than loading the files onto the file system of the web server. This reduces the complexity of the system and means that a backup will take care of all user data, including their documents.

There are several steps in getting it set up, none of which are particularly complicated but the documentation I found online was a bit sketchy at best.

Aside from the database components, the whole lot was done in Visual Studio Express 2008 with Visual Basic.

SQL Server Database Layer
The requirement was to be able to upload documents and associate them with individual customers. In order for the browser to know what type of file is being returned, you have to store the MIME type as well as the other bits and bobs:

CREATE TABLE [dbo].[Client_Document](
[Client_Document_ID] [int] IDENTITY(1,1) NOT NULL,
[Filename] [varchar](128) NOT NULL,
[Description] [varchar](2048) NULL,
[Binary_Document] [varbinary] (MAX) NOT NULL,
[MIME_Type] [varchar](50) NULL,
[Upload_Date] [datetime] NULL,
[Uploaded_By] [varchar](255) NULL,
[User_ID] [varchar](7) NULL,
[Lob_ID] [numeric](38,0) NOT NULL

The actual file is in the column [Binary_Document] [varbinary] (MAX). Previously, to store files in the database itself, you were encouraged to use the old 'Image' data type, but since SQL Server 2005 this has been deprecated. Instead, use varbinary(MAX) which automatically scales depending on the size of the file inserted (up to 2Gb - you may want to check the file size in code to keep it to a sensible level).

Then you just need the usual CRUD stored procedures to INSERT, UPDATE, DELETE.

Data Access Layer (DAL)
First, add your TableAdapter to your XSD file, then add your methods which tie up to your stored procedures. Check that the data type of the Binary_Document column has been set to System.Byte().

Business Logic Layer (BLL)
Create a BLL for the Client Document which defines an adapter based on the Client_Document datatable. Add your BLL methods which map to the methods defined in your DAL. You can add some validation in here or just call the DAL methods directly, passing the arguments which have come from the calling ASP page.

Presentation Layer
On my ASP page, I have a GridView which displays the current documents associated with the selected customer, as well as a FormView which allows new documents to be added, as well as allowing updates to existing document details (or to overwrite the file with a new version).

Passing the documents into the database and retrieving them is pretty much the same as anything else you've done, except that you need to capture the binary stream of the selected file and pass it into the database. The file selector control comes for free from the .NET framework:

<asp:FileUpload ID="FileUploadClientDocument" runat="server" Width="320px" />

Then, when you call your methods to save or update you can check the attributes of the selected file, extract its filename, size and MIME type, and save them into the database using your BLL methods, e.g:

' If a file has been selected, load it into the database with its details
If FileUploadDocument.HasFile = True Then
Dim fileBytes(FileUploadDocument.PostedFile.InputStream.Length) As Byte
FileUploadDocument.PostedFile.InputStream.Read(fileBytes, 0, fileBytes.Length)

' Display the uploaded file's details
UploadDetails.Text = String.Format( _
"Uploaded file: {0}<br />" & _
"File size (in bytes): {1:N0}<br />" & _
"Content-type: {2}", _
FileUploadDocument.FileName, _
FileUploadDocument.FileBytes.Length, _
FileUploadDocument.PostedFile.ContentType)

' Insert
clientDocumentInfo.AddClientDocument(cloverClientId, FileUploadDocument.FileName, strDescription, fileBytes, FileUploadDocument.PostedFile.ContentType, strAddedBy, strAddedBySid, Session("lobId").ToString)


Make sure that you not only define the file's length (line 2), but actually use the Read method of the InputStream to add the data to the variable (I forgot to do this and got files in the database which were the right size but completely blank!).

UploadDetails is just a label which displays the attributes of the file which has been loaded (size, MIME etc).

Getting the files back is just a case of retreiving the file from the database using the stored proc, then setting the Response to match the MIME type and the file's binary stream.

In this case, clicking on the filename in the GridView fires the below code and returns the file to the browser:

If e.CommandName.CompareTo("GetClientDocument") = 0 Then
' Since we are not using the Select command, we have to get the selected Client Document ID from the command arguments.
Dim btn As LinkButton = DirectCast(e.CommandSource, LinkButton)
Dim gvr As GridViewRow = DirectCast(btn.NamingContainer, GridViewRow)
' Get the selected document ID
Dim clientDocumentId As Integer = GridViewClientDocuments.DataKeys(gvr.RowIndex)("Client_Document_ID").ToString()
' Invoke the DocumentBLL.AddDocument method to get the file from the database.
Dim clientDocumentInfo As New ClientDocumentBLL()
Dim clientDocuments As cleartrak.cleartrak.Client_DocumentDataTable
Dim clientDocument As cleartrak.cleartrak.Client_DocumentRow

' Get a table with the appropriate row in it
clientDocuments = clientDocumentInfo.GetClientDocumentById(clientDocumentId)
clientDocument = clientDocuments(0)
' Set the response to the MIME type of the document and its binary contents.
Response.ContentType = clientDocument.MIME_Type.ToString
Response.BinaryWrite(clientDocument.Binary_Document)
End If


That's it - the files are now saved in the database and can be retrieved by a single click.

Friday 18 December 2009

Dell Studio 17: Resuming itself from Hibernate

I use my laptop for development, so I often have ten to fifteen programs open at once, each with various documents open within them. That, combined with the fact that development tools can often be heavy on CPU and memory means that restarting my PC is a pain in the neck.

So rather than restart or leave it on and waste power, I like to put it into Hibernate mode where it will quickly resume where I left off - no need to reload everything.

The problem was that if I left it overnight, I'd often come downstairs the next morning to find the Windows welcome screen staring back at me - the laptop had woken itself from Hibernate. This is not ideal, because it might cause it to overheat and its a monumental waste of power.

I could not figure out why this was happening so contacted Dell support to see what they could do. They were very keen to help, but didn't offer many useful suggestions. They suggested I flash the BIOS, which I did. No luck.

Then I devoted some time to googling the issue and discovered the problem.

The setting I had selected in Windows Update appeared to be the sensible option, for Windows to 'Automatically download and install updates for me'. Given the swiss cheese nature of Windows security, I thought this would afford me the best protection.

But it turns out that with this setting chosen, Windows Update was causing the system to wake from Hibernate to look for updates. How stupid is that? If I wanted it to do that, I would deliberately select such a setting if it was available. And surely if you did want it to resume to download updates, you would want it to automatically go back into Hibernate? But oh no.

There was no indication in the Windows Update settings that the system would resume from Hibernate

Anyway, I now have it set to automatically download updates, but instead of installing them automatically it will ask me to select which ones to install. At the cost of being able to hibernate my machine, I have to ensure that I remember to periodically install the available updates when they download.

Not the best, Microsoft, not the best.

Review: Dell Studio 17

Being an I.T. boy, if you believe in stereotypes you might think I'd have loads of computers everywhere, whirring away, lights flashing. But since my PC blew up last year, I've only had my work laptop to use.

So the time came when I got sick of typing my password into the encryption program, then waiting five hours for it to boot up and then to get logged into the laptop, only to forget why I switched it on in the first place. Grudgingly at first, I decided that now was the time to get a new computer all of my own.

It took me ages to choose a laptop. I wanted a fairly high spec machine, since I'll be using it for software development as well as some image editing, and I didn't want to have to replace it after just a year. Plus, if I decide to buy a PC game or two I'd like it if it could run it (even in lower resolutions).

After hours poring over which machine to choose, it came down to a short list between the Sony Vaio VGN-AW11M/H, the HP HDX18 and the Dell Studio 17.


I was tempted by the Sony, but the Vaio brand comes at a premium and the reviews said it was too slow to be a proper desktop replacement machine (plus, it's just a big black slab of a thing). The HP machine was very nice to look at and very quick, but too expensive (a rip off, actually), and in the end I decided an 18 inch screen was a little too large anyway. So I spec'd up my machine at Dell:

CPU: Intel Core 2 Duo P8400 (2.26GHz, 1066MHz FSB, 3MB cache)
Display: 17in Widescreen WUXGA with Truelife - CCFL (1900x1200)
Memory: 4096MB (2x2048) 800MHz DDR2 Dual Channel
Storage: 500GB Serial ATA (5.400RPM) Dual Hard Drive (2x 250GB)
Graphics: 256 MB ATI Mobility RADEON HD 3650
Optical Drive: Slot loading Blu-ray Disc (DVD+/-RW + BD-ROM) Drive
Network: WiFi (802.11 a/b/g/n), Bluetooth, built in Vodafone mobile broadband
Other: Backlit keyboard, 2.0 megapixel webcam

I plumped for the spec above, and managed to get 16% off.

The spec is very similar to that of the HP HDX18, but cost me about £200 less. When it arrived on Friday, I was immediately impressed by it. To be honest, I wasn't expecting to like the appearance of the machine too much since Dell's previous offerings have been as dull as a day locked in a room with John Major. But it's nice. The very solid build quality, the nice plastics, the backlit keyboard, a glossy screen and a decent pattern on the lid come together to form a handsome machine.

The first thing I did was turn the dreaded User Account Control off, and delete all the crap which Dell preinstalled on the machine. And so far, I'm more than happy with the performance.

Most of the other Vista machines I've seen (none have been particularly high spec, to be fair) have shown signs of poor performance when loading up the windows sidebar, and some of the property screens on Control Panel, among other things. But this machine doesn't do that - it loads pretty much everything nice and quickly, fast enough to avoid an irritating wait for windows to render. One benchmark test I had concocted was to see how fast it could convert a 70*40cm GIMP XCF image I'd been working on from RGB to Grayscale. On my work machine (Dell D610, 1.8Ghz CPU, 2 GB RAM), it took about twenty seconds, but on the Studio 17 it took about half that.

I paid a bit more and got the upgraded screen (the 1900x1200 WUXGA with Truelife one), and I was not disappointed. It's incredible. Being an extremely high resolution panel, everything on the screen is small, so you can fit a lot on it. Some people hate that, but I like it since I have loads of windows open at once. When playing a video or looking at photos, it really comes into its own, and if you were considering a Studio 17, then I'd upgrade the screen every day of the week. If you don't like the small text of the high resolution screens, you can increase the DPI settings to make everything a bit bigger (but this kind of defeats the purpose of having the high res panel!).

The battery life is alright, but I didn't expect much from a machine of this size. I think I'm getting about an hour and a half from the battery, which is acceptable to me since it'll be plugged in most of the time anyway.

It's heavy though. Heavier than Michelle McManus leaving McDonald's on a JCB. So it's not really all that portable, but I think if you get a decent messenger bag style case, you'd be able to carry it about without any problems. The weight doesn't really bother me since I don't intend to travel around with my laptop too much, just when travelling sometimes and maybe the odd client visit.

My only gripe with it is that there's too much light leakage from underneath the keys, it gets a bit irritating when you're working on the laptop when its on a desk. But the backlit keyboard is still worth the extra cash.

So in summary, it's a fast, well built machine with a stunning screen and nice features. Fortunately, with my wallet being several hundred pounds lighter, I'd recommend it to others.

Monday 7 December 2009

Debugging CSS in Internet Explorer: Firebug Lite

Firefox is absolutely brilliant for checking your CSS styles and layout when you've installed Firebug.

It allows you to see where styles have come from in your CSS, and to switch them on or off, or to adjust them to see what changes you need to make to sit your site nicely.

But when trying to figure out why things look the way they do in the abysmal Internet Explorer, there isn't really an equivalent which is as good. You can try the IE Developer Toolbar which is ok, but like the browser it supports it's very clunky and pretty annoying.

I'd been waiting for a Firebug equivalent for IE for ages, and then I discovered that there is a version of Firebug itself which is designed for IE, Safari and Opera: Firebug Lite.

All you do is stick a reference to a little remote js in your page, load it up and you have most of the functions of the full, Firefox version of Firebug!

Nice.