Storing images in SQL Server using C# [closed]

Closed. This question is opinion-based. It is not currently accepting answers.

---

Want to improve this question? Update the question s开发者_运维知识库o it can be answered with facts and citations by editing this post.

Closed 8 years ago.

Improve this question

I want to store images of my employees with their profile details in SQL in a SQL Server database, but I have following reservations:

Should I compress images or not and if yes, please can the community provide me sample code or direct me to a guide that can assist me with this.

How should I retrieve images efficiently? I am afraid of asp.net application performance issues. I think with thousands of employee records, the system may halt or slow down.

---

I would suggest storing the path of the images on your sql table, and actually store the image in a secure folder. This has worked for me perfectly.

---

Sql Server is well equipped to handle large amounts of binary data, but as always, the devil is in the details and the design:

To avoid any performance issues that can happen with reporting or access from other applications due the Sql Server's paging process (all db engine use pages as a discrete unit of data manipulation) I would place the image data in a separate table that is linked 1-to1 to your employee table. Doing so will keep the binary data from being moved around with employee queries unnecessarily when accessed from other parts of your system. Your image display logic will of course have to join on the two tables to match up employee and image records.

---

First thing, ten thousand records is nothing ... as long as you have the right indexes in place SQL Server will handle that with ease.

However, consider just using the file system to store things like images. The SQL Server record could just contain a pointer (ie. path) to the file and the asp.net application could just use that to read the file from disk and stream it to the browser.

---

Most image formats (such as JPEG, or PNG) already employ some compression algorithm, so unless your source is a RAW image or an uncompressed bitmap, additional compression won't help much. What might help is limiting the size of the image, say a maximum of 400 x 400 pixels. I don't know what the intended purpose is so I don't know if this will work well for you. Obviously, if you are storing the data directly from a digital camera, the images will be very large and you might want to scale them down first, to something more reasonable.

---

If you are on SQL server 2008 or above then you can use the filestream feature that is like the best of both worlds. It stores the image in the filesystem but has it under transactional control and is also included in your backups when they are taken.

If you are not on 2008 or above then I would say to keep the images in the DB, see this old Microsoft white paper for my reasons why.

http://arxiv.org/pdf/cs.DB/0701168