Adding images to a database (C#/ SQL)

[braBenj]

Hi all

I'm developing a program that requires users to store and retrieve images from a database. I'm using C# and I currently have some images stored directly on the database, but I hear that this is probably a bad idea. What is the correct way of storing and retrieving images(or any other multimedia file) from a database with C#? Also if anyone can provide me with some material that explains best practices for interacting with databases in C# or java, this would be greatly appreciated.

Thank you

 

[Hamster]

/braces for Holy War of opinions

Microsoft actually did a study and found that if a file is under a certain size it is better to save the byte data in a table. I can't remember the size but it was small. Let's take a step back though...

What you want is an index table - ID, Name, Type, Size, Whatever - which contains all the needed metadata for the file.

From here you need to decide whether or not you want to save the files in the DB (new table, FK link between this data table and the above mentioned index table), or save the files on disk in which case you either use convention and assume the file to be in a certain directory or store the file path in the DB.

When you store it on disk it is probably not wise to dump everything in one directory though (assuming we are talking about many many files here). What some do is create sub directories using the first couple of characters of the file name:

/b/a/t/batmanisawesome.avi
/s/p/i/spidermansucks****.avi

 

[DominionZA]

Did something similar to this a few years back and was close to Hamsters approach.
For uniqueness in the filename, the original filename was stored In the DB, and the physical filename was a GUID.
To prevent all files going in one folder, I used a year\month sub folder structure.

 

[Joe Average]

SQL Server has evolved a lot over the last few years. Ideal way to store the image/file is in a FileTable. Just make sure the hierarchy of the FileTable has enough folders to not host too many files in a single folder. Then via simple T-SQL you can retrieve the Byte[] of the file from the FileTable again. You need to link the FileTable storage to a FileStream filegroup, but once setup it is the cleanest and fastest way of storing documents in SQL. You can go bonkers and store it as Cloud BlobStorage on something like Azure (not in SA yet) and CRUD the files using a WCF/API.

 

[VG008]

Did something similar to this a few years back and was close to Hamsters approach.
For uniqueness in the filename, the original filename was stored In the DB, and the physical filename was a GUID.
To prevent all files going in one folder, I used a year\month sub folder structure.

How did you manage backups and DR/Failover?
We have around +200 Million small images and decided to store these as blobs on the DB. We found it easier to use the DB system to manage the replication across sites and the backups.

 

[craiglotter]

Just be prepared for a very, very large database to deal with though. We abandoned this approach once the database became a little too unwieldy because of the saved images (I think we stored them as base64 encoded blobs if I remember correctly).

 

[Necropolis]

If you are storing to disk just remember to include the directory where the files are stored in your backup strategy...

 

[halfmoonforever]

I think you are using google wrong

 

[gkm]

Agreed, if the images are small, DB is the easiest, but as they get bigger, DB disk space and backups start to become a PITA.

For larger images, I would recommend using blob storage as was also mentioned above, otherwise you are going to keep on buying disks. Using something like S3 is pretty easy:
http://docs.ceph.com/docs/jewel/radosgw/s3/csharp/
https://aws.amazon.com/sdk-for-net/

Even on S3 is is probably a good idea to keep the files split in folders. For me it makes it easier to find stuff when I want to manually look at something.

Then as Hamster says, just keep your file locations in the DB, if you store them somewhere else.

 

[LPCPT]

Make sure that if you do store it in the DB (SQL Server I assume), Create a separate filegroup for the images. That way you can easily track the size and the space used for them. And If you have Enterprise edition, you can use more flexible backup and restore methods.

If it's on disk, very important, use multiple directories and do not forget about archiving down the line.
It's also nice if the filename contains the key of the record in the db. That way you can use the filename and reference back to a record in the database.
Just keep your options as wide as possible. I had some painful experience with this.

 

[braBenj]

Thank you all for your reply, this isn't really a big database I'm just doing this program so I can better understand how to work with databases. I think I'll go with Hamsters approach and store the file on the disk, but I'm not exactly sure how to create the index table
I'll also look into Filetables that Joe Average suggested.

 

[braBenj]

Thank you all for your reply, this isn't really a big database I'm just doing this program so I can better understand how to work with databases. I think I'll go with Hamsters approach and store the file on the disk, but I'm not exactly sure how to create the index table
I'll also look into Filetables that Joe Average suggested.

 

[Johnatan56]

Thanks for the OP question, was going to ask opinions on this as well in a couple of months for a project.

 

[Hamster]

Thank you all for your reply, this isn't really a big database I'm just doing this program so I can better understand how to work with databases. I think I'll go with Hamsters approach and store the file on the disk, but I'm not exactly sure how to create the index table
I'll also look into Filetables that Joe Average suggested.

That "index table" is basically a manifest of your files. Literally a table that lists the files available.

ID    NAME                DATE               PATH
------------------------------------------------------------------
 1     batman.pdf        2016/05/19     store/files/batman.pdf
 2     spiderloser.pdf   2016/02/14     store/files/spiderdumbass.pdf

And then your code

var batDoc = getDocumentByID(1);
var loserDoc = getDocumentByName("spiderloser.pdf");

etc

 

[CamiKaze]

store the file path in the DB.

This.

 

[braBenj]

That "index table" is basically a manifest of your files. Literally a table that lists the files available.

ID    NAME                DATE               PATH
------------------------------------------------------------------
 1     batman.pdf        2016/05/19     store/files/batman.pdf
 2     spiderloser.pdf   2016/02/14     store/files/spiderdumbass.pdf

And then your code

var batDoc = getDocumentByID(1);
var loserDoc = getDocumentByName("spiderloser.pdf");

etc

Okay I see, thanks a lot.



Spiderman's awesome btw

 

[semaphore]

Amazon S3.

 

[animal531]

Thank you all for your reply, this isn't really a big database I'm just doing this program so I can better understand how to work with databases. I think I'll go with Hamsters approach and store the file on the disk, but I'm not exactly sure how to create the index table
I'll also look into Filetables that Joe Average suggested.

The most basic solution (that fits your little app) is to just make a DB column in whatever table of type varbinary(MAX), then read the image from disk and store the byte[] to the column.

 

[Spacerat]

wont a mongodb be better for this type of thing?

 

[Solarion]

Why not just store the location of the images in SQL and the images themselves in the drive? Then your app just uses the location to load the pics?