Best way to save/load pictures with .Net & SQL Server 2005?

Question

Hey,

What is the typical way to handle product pictures in a web-page selling products? Say I had a database with books or computer components etc, all of which have their own sample pictures for example... Should I save them into the DB as binary data, or somehow handle them in the codebehind, saving them into a directory with the appropriate link to the picture file being saved into the product table in the DB?

I'm trying to make a sales company example, and while I can come up with ways to do this, I wonder what is the typical norm used in the programming business?

Update: Storing on another system or alternate DB's is not an option, while the information on possible alternatives for future reference is appreciated. All I have available to are Visual Studio 2008 and SQL Server 2005 / 2008, on one computer, with one ASP.Net project. So the files would have to be saved into a directory within the web-page project, or into the one DB being used by it.

Answer 1

Please see: Storing a file in a database as opposed to the file system?

I have working C# code for SQL Server 2005 if you decide to store in the Database here: Save and Restore Files/Images to SQL Server Database

This MS Research paper by the late Jim Gray: To BLOB or Not To BLOB: Large Object Storage in a Database or a Filesystem has the following advice:

As expected from the common wisdom, objects smaller than 256K are best stored in a database while objects larger than 1M are best stored in the filesystem. Between 256K and 1M, the read:write ratio and rate of object overwrite or replacement are important factors.

Answer 2

Don't load DBMS by binary data! Let SQL Server do its specific work. It is better when files stored in file system and even on other computer.

Answer 3

My preferred solution is always to save them to a directory on your file system and save the url / location in the database. I find this the easiest to maintain and it's easy to change databases, datastorage without affecting the files.

This method, is not dependent on the database.

I had a situation recently where a legacy system stored images and files as binary, which caused time and effort retrieving these to upgrade database software.

Answer 4

Saving files on filesystem but inside a DB
Saving files into DB makes it a single backup point for all your application data and makes a smaller security surface for your app because you don't need a write permission folder for storing files.

SQL Server 2008 FILESTREAM storage to the rescue

With SQL Server 2008 Microsoft introduced a new data storage mechanism called FILESTREAM that stores data in folders as regular files. But you can access them (and store their metadata) via tables. You can store files as varbinary(max) and they get stored on the filesystem as configured. These files will also get picked up by DB Backup procedure. This way you get the best of both worlds in a single solution.

Read about it here.

Post-festum note
I have to point out that authors of the question did mention they may use SQL Server 2008 as well when there are any benefits. That's the only reason this answer is related to version 2008, because this really is the optimal solution for their problem.