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.
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.
Please give us feedback no matter whether you get your answer.
Please "mark as answer" if it's useful for you
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:
It is easier to keep the BLOB data synchronized with the remaining items in the row.
BLOB data is backed up with the database. Having a single storage system can ease administration.
BLOB data can be accessed through XML support in SQL Server 2005, which can return a base 64–encoded representation of the data in the XML stream.
SQL Server Full Text Search (FTS) operations can be performed against columns that contain fixed or variable-length character (including Unicode) data. You can also perform FTS operations against formatted text-based data contained within
image fields—for example, Microsoft Word or Microsoft Excel documents.
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:
Retrieving an image from a database incurs significant overhead compared to using the file system.
Disk storage on database SANs is typically more expensive than storage on disks used in Web server farms.
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.Add("@blobdata", SqlDbType.Image);
cmd.Parameters["@blobdata"].Direction = ParameterDirection.Input;
// Store the byte array within the image field
cmd.Parameters["@blobdata"].Value = imageData;
conn.Open();
cmd.ExecuteNonQuery();
}
}
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.
Storing Image in a folder and using URL or relative path in the database is the way I would recommend. They are havingmany advantages and here are some of them.
Normally sql server space is more expensive than ordinary disk space in hosting environment.
You can name images in a Search Engine optimized way
Essay to manage, backup and restore images and database.
More suitable for static pages, (if you optimize some pages for performance).
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.
Thanks you have all answered my question, I will be storing the images in a seperate folder.
Question
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.
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.
wstevens@vod...
Member
185 Points
105 Posts
Storing images in SQL database vs storing images in seperate folder and using URL image path
Apr 10, 2007 08:00 AM|LINK
Hi all
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.
Jerome Cui -...
Star
12018 Points
1080 Posts
Re: Storing images in SQL database vs storing images in seperate folder and using URL image path
Apr 10, 2007 04:40 PM|LINK
Hi,
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.
Please "mark as answer" if it's useful for you
Sincerely,
Jerome
KBrocksi_SEC
Contributor
3382 Points
627 Posts
Re: Storing images in SQL database vs storing images in seperate folder and using URL image path
Apr 10, 2007 05:02 PM|LINK
Hi,
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.Add("@blobdata", SqlDbType.Image); cmd.Parameters["@blobdata"].Direction = ParameterDirection.Input; // Store the byte array within the image field cmd.Parameters["@blobdata"].Value = imageData; conn.Open(); cmd.ExecuteNonQuery(); } }Max01
Member
21 Points
33 Posts
Re: Storing images in SQL database vs storing images in seperate folder and using URL image path
Apr 10, 2007 05:05 PM|LINK
Hi,
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.
josephjames
Member
309 Points
113 Posts
Re: Storing images in SQL database vs storing images in seperate folder and using URL image path
Apr 10, 2007 05:49 PM|LINK
Hello
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.
Regards
wstevens@vod...
Member
185 Points
105 Posts
Re: Storing images in SQL database vs storing images in seperate folder and using URL image path
Apr 11, 2007 06:21 AM|LINK
Hi all
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.
wstevens@vod...
Member
185 Points
105 Posts
Re: Storing images in SQL database vs storing images in seperate folder and using URL image path
Apr 11, 2007 06:25 AM|LINK
Hi All
Thanks you have all answered my question, I will be storing the images in a seperate folder.
Question
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.
Thanks
duttavr
Contributor
4035 Points
1079 Posts
Re: Storing images in SQL database vs storing images in seperate folder and using URL image path
Apr 11, 2007 07:16 AM|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.
Please Mark as Answer, if it answers you, also correct me if I'm wrong.
wstevens@vod...
Member
185 Points
105 Posts
Re: Storing images in SQL database vs storing images in seperate folder and using URL image path
Apr 11, 2007 08:24 AM|LINK
Hi
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.
Thanks
omerkamal
Contributor
2507 Points
513 Posts
Re: Storing images in SQL database vs storing images in seperate folder and using URL image path
Apr 11, 2007 08:24 AM|LINK