Adding images to SQL Server when creating a record - is there a better way to do this?

Question

One of my web pages allows the user to create a new record, and, at the same time, associate any number of images with that record. The way I am thinking of doing this is to upload each image to the server when the user click the 'upload' button in conjunction with a fileupload control. Then, when the user clicks the 'update' button for the entire record, I will create the record, then, using the id of the new record, add each image to the images table using the id of the record just added.

Oh, and I will be deleting all temp image files from the server each time the application starts. I figure I can get away doing it this way since adding images to a record will not be used very much, so there is little danger of filling up the server with temp images.

Is there a better way to do this? I'm fairly new to ASP.NET and SQLServer, so I'm completely open to any suggestions on how to improve this.

Answer 1

Generally your approach seems fine to me - but are pitfalls with uploading images to a database, depending on how your application is meant to work.

First of all, unless you're using the FILESTREAM feature of Sql Server 2008, it's not really recommended to be storing images in your database. Streaming an image as data from a database is a much slower operation than serving it from the filesystem (the FILESTREAM feature I mentioned uses the filesystem!) so it's considered much better practice to just store a URL to the image in the database rather than the image itself, especially if you need performance from your application. The drawback to this is that you have to remember to back up your images as part of your backup process, since they don't live in the database. (FILESTREAM solves that issue as well.)

Depending on how often your application restarts, your deletion approach may be ok, but it isn't one I'd recommend. You could run a cleanup routine on every upload, or even as a windows scheduled task.