Photos and Database

Question

Hi Friends, I am working on a website, where user can upload photos of product they want to advertise. I am saving photos in a folder on the web. In the table where I keep reference of photos, there is a key field photoid which is Identity field(primary key).

My repository has following methods

Photo photo = rep.NewPhoto();
photo.Title="Some Title";
rep.InsertPhoto(photo); 
rep.SaveAll();

rep.SavePhoto(photo,uploadedPhoto);
rep.SaveAll();

I am using Linq to SQL for my data model. Now my problem is, if I want to save my files with a name which is coming from photoid, I have to Call the rep.SaveAll() method to get the new created photoid and then save the photo with new id and then I have to Call SaveAll() method to update it again with the changes happend in SavePhoto() method.

Other option is I save the file with some random file number first and then Save the photo record in one step.

This is second approach.

Photo photo = rep.NewPhoto();
photo.Title="Some Title";
string filename = rep.SavePhoto(uploadedPhoto);
photo.FileName=filename;
rep.InsertPhoto(photo); 
rep.SaveAll();

Saving files with photoid has one good point, photos can be easily loaded using its id.

What is a good approach to achieve this kind of functionality.

Help will be appritiate.

Cheers Parminder

Answer 1

A couple of things:

1) Consider storing the photos as VARBINARY(MAX) in the database. It will be much easier for people to manage as far as backups and restores go, and it's better for your database's integrity.

2) Consider using the partial methods of your L2SQL Photo class in order to achieve what you want. You should be able to change your Photo class's Update method to save your photo as well. Hope this helps.

Answer 2

Second approach is definitely more efficient; other option could be to create a unique in your application layer(you can use guids ) and then use this as key DB record and same as file name.

Answer 3

If you are using the latest SQL Server, you might want to look into the new FILESTREAM field type:

http://technet.microsoft.com/en-us/library/bb933993.aspx

It's for exactly this kind of thing -- trying to get large blobs out of the table structure and onto the filesystem.

Other than that, I think you have the right idea with either way you go -- if you need two saves, you might want to consider a transaction if you care about what happens if the second save or SavePhoto fails.