Last post May 08, 2011 04:44 PM by mrsea001
Apr 10, 2007 08:00 AMemail@example.com|LINK
Can you store images in a sql database and if so, does it store the images as an image file or does it store it in a binary format reducing the size of the image. If it can be stored in an sql database as a image or a binary format what are the advantages/disadvantages
or is it better to store the url in the database and then bind the image holder to the url link in the database.
Apr 10, 2007 04:40 PM|Jerome Cui - MSFT|LINK
These two ways are both ok. My suggestion is to store images into sql database if there are no much more images, otherwise, it's better store them into file system because you can store them no matter how many there are.
Apr 10, 2007 05:02 PM|kbrocksi_SEC|LINK
a commonly used alternative approach to handling BLOB data is to store the BLOB data in the file system, and store a pointer (preferably a Uniform Resource Locator [URL] link) in a database column to reference the appropriate file.
Advantages of Storing BLOB Data in the Database
Storing BLOB data in the database offers a number of advantages:
Disadvantages of Storing BLOB Data in the Database
Carefully consider what resources might be better stored on the file system rather than in a database. Good examples are images that are typically referenced via HTTP HREF. This is because:
The following code shows how to use ADO.NET to write binary data obtained from a file to an
image field in SQL Server.
public void StorePicture( string filename )
// Read the file into a byte array
using(FileStream fs = new FileStream(filename, FileMode.Open, FileAccess.Read))
byte imageData = new Byte[fs.Length];
fs.Read( imageData, 0, (int)fs.Length );
using( SqlConnection conn = new SqlConnection(connectionString) )
SqlCommand cmd = new SqlCommand("StorePicture", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@filename", filename );
cmd.Parameters["@filename"].Direction = ParameterDirection.Input;
cmd.Parameters["@blobdata"].Direction = ParameterDirection.Input;
// Store the byte array within the image field
cmd.Parameters["@blobdata"].Value = imageData;
Apr 10, 2007 05:05 PM|Max01|LINK
I too faced a similar problem not that long ago, and as an ASP.NET novice, agonised over what course of action should be taken. In the end, and as Jerome quite rightly points out, I decided to place the images in a seperate folder due to my SQL Server 2000
account - the host charges me per 150MB clip. The URL strings are mapped to the database and are retrieved/displayed via the <asp:ImageField /> control.
Apr 10, 2007 05:49 PM|josephjames|LINK
Storing Image in a folder and using URL or relative path in the database is the way I would recommend. They are having
many advantages and here are some of them.
Apr 11, 2007 06:21 AMfirstname.lastname@example.org|LINK
Thanks for the response. All of you have supplied me with the information I have required. I am familiar with storing the url link to an image in a seperate folder as I have done it in ASP 3, I think storing the images seperate will be a better method for
me as my clients usually have up to 600 images stored on there site at one time. It will also be easier to create a script that will remove the image from the folder so that it is not orphaned when a record is deleted.
There is however one more question that i need answered and that is if the image is a high resolution picture and is 800 x 600 in size, will I be able create thumbnail views of those pictures without storing 2 images, 1 being a smaller version for a thumbnail
and the other being the original or can I use a (image place holder " I use to develop in Dreamweaver 8 Im using WVD 2005) which is locked to a specific size. What would be the best option.
Apr 11, 2007 06:25 AMemail@example.com|LINK
Thanks you have all answered my question, I will be storing the images in a seperate folder.
What would be best, storing the image in a seperate folder as an image or convert it to binary and store it in a folder as a binary file. Then read the binary file to display the image in http. I think binary will save on space and will load faster. Please
let me no what you guys think.
Apr 11, 2007 07:16 AM|duttavr|LINK
This information is very useful. Thanking all providing a good information to choose the best way of maintaining the images.
Hi wstevens: as per my knowledge any image format either jpg, bmp are binary files only. no need to convert into binary again.
Please let me know I am wrong.
Apr 11, 2007 08:24 AMfirstname.lastname@example.org|LINK
I dont think image files are binary in nature as I have worked with software that converts images to binary. Lets see if anybody else answer, maybe somebody else knows.
Apr 11, 2007 08:24 AM|omerkamal|LINK
Apr 11, 2007 12:14 PM|kbrocksi_SEC|LINK
... just an additional note ...
SQL Server defines several types that translate to binary storage under the covers. There are the
binary and varbinary types, which store up to 8KB of binary data. The binary type has a fixed allocation size that you specify when creating the column, and varbinary fields will only require storage based on the actual size
of the data put into each row. More commonly, what you will want to use are the
image, text, and ntext types, each of which allows variable length storage of data from 0 to 2GB. The image type is for storing raw bytes, whether they correspond to a graphic image, some bits from a file, character
data, or whatever you want. The text and ntext fields are optimized for storage and retrieval of large blocks of text. The text type will store the text in a format determined by the code page used by the database in question. The ntext type always stores
characters in Unicode format. ADO.NET eases the process of getting binary data into and out of the database. An ADO.NET-managed provider is responsible for defining a mapping between the underlying database type system and the .NET type system. For SQL Server,
the text and ntext types just map to a string. This is a natural fit since .NET strings are Unicode under the covers and are only limited in size by the amount of memory your process can allocate. The image type in SQL Server maps to a byte array, again making
it easy to work with since most binary operations in .NET deal with byte arrays as well. Specifically, whenever dealing with streams, it is easy to push a byte array into a stream or read it back out.
Jun 12, 2007 08:02 AM|yonahw|LINK
Thank you for clarifying these matters it has helped me quite a bit as a newcomer to the scene!
Jun 12, 2007 08:46 AM|samirindia|LINK
i think you have to do it on folder .. it is best bcoz it can help you so much.. for SEO, and also for replace image directly sometime.. etc..
May 08, 2011 04:44 PM|mrsea001|LINK
As per my experience about image storing, that to storing BLOB data in the database I go with Karsten. Yeh, if you talk about web application then it is best way to strore image in a folder, which offers many advantages as Joseph James described.
But when we talk about desktop/ windows application so it very hard to manage folder specially in a mix environment domain, workgroup, docking stations, lap tops etc. everyone has to have different rights and requirements. Nevertheless, at administration
hand it more complex task to manage folder, sometimes we need to move the database server or application server then we have to modify the application too.
Anyway, I suggest to save image in database specially when working on windows application.