How to Save an Image in a SQL Server Database?

Most of the web applications have a lot of images used in it. These images are usually stored in a web server folder and they are accessed by giving the relative path to the file with respect to the root folder of the website. .Net being the platform for distributed application now, ASP.Net can be used to store images that are small to be stored in a database like SQL Server 2000 and later versions. For this purpose the SQL Server database provides a data type called "image" which is used to store images in the database.

To access these images stored in the database we will be using the ADO.Net classes. To find out how to insert and retrieve an image in to the SQL Server database, you can create a .aspx page which can have a HTMLInputFile control which is used to select the image file that is to be saved in the database. You can also create a textbox control in which you can add the image name or some comment or an image id for the image saved. Use a button control to upload the image to the database. Namespaces like System.Data.SqlClient, System.Drawing, System.Data, System.IO, and System.Drawing.Imaging are used in this task.

In the OnClick property of the button you can write the following code to upload an image to the database.

// create a byte[] for the image file that is uploaded
int imagelen = Upload.PostedFile.ContentLength;
byte[] picbyte = new byte[imagelen];
Upload.PostedFile.InputStream.Read (picbyte, 0, imagelen);
// Insert the image and image id into the database
SqlConnection conn = new SqlConnection (@"give the connection string
here...");
try
{
conn.Open ();
SqlCommand cmd = new SqlCommand ("insert into ImageTable "
+ "(ImageField, ImageID) values (@pic, @imageid)",
conn);cmd.Parameters.Add ("@pic", picbyte);
cmd.Parameters.Add ("@imageid", lblImageID.Text);
cmd.ExecuteNonQuery ();
}
finally
{
conn.Close ();
}

You can also write the above code in a function and call that function in the OnClick event of the upload button. The code given above performs the following steps in the process of inserting an image into the database.

1. Get the content length of the image that is to be uploaded
2. Create a byte[] to store the image
3. Read the input stream of the posted file
4. Create a connection object
5. Open the connection object
6. Create a command object
7. Add parameters to the command object
8. Execute the sql command using the ExecuteNonQuery method of the command object
9. Close the connection object

To retrieve the image from the SQL Database you can perform the following steps.

1. Create a MemoryStream object. The code can be something like, MemoryStream mstream = new MemoryStream ();

2. Create a Connection object

3. Open the connection to the database

4. Create a command object to execute the command to retrieve the image

5. Use the command object's ExecuteScalar method to retrieve the image

6. Cast the output of the ExecuteScalar method to that of byte[] byte[] image = (byte[]) command.ExecuteScalar ();

7. Write the stream mstream.Write (image, 0, image.Length);

8. Create a bitmap object to hold the stream Bitmap bitmap = new Bitmap (stream);

9. Set the content type to "image/gif" Response.ContentType = "image/gif";

10. Use the Save method of the bitmap object to output the image to the OutputStream. bitmap.Save (Response.OutputStream, ImageFormat.Gif);

11. Close the connection

12. Close the stream mstream.Close();

Using the above steps you can retrieve and display the image from the database to the web page.

You can use these algorithms and take advantage of the "image" data type available in the SQLServer 2000 database to store small images that correspond to a particular record in the table of the database. This method of storing avoids the tedious task of tracking the path of the web folder if the images are stored in a web folder.

Visit A Guide to .NET for a complete introduction to .NET framework. Learn about ASP.NET, VB.NET, C# and other related technologies.

Related News

Personal tech briefs: Game-to-film trend continues - Salt Lake Tribune Personal tech briefs: Game-to-film trend continues Salt Lake Tribune, United States -Jun 24, 2008 Kim Pyeng-joong, senior manager of Hyundai Japan, wears special glasses to watch Hyundai's 3-D screen. Video game-based movies have been the butt of many a ... Futureshop.ca Enters Mobile Commerce Market with RIM - FOXBusiness Futureshop.ca Enters Mobile Commerce Market with RIM FOXBusiness -3 hours ago "We have hundreds of thousands of tech savvy customers who visit our web site every day and now with this new way to shop futureshop.ca on your BlackBerry ... Bill Gates Steps Down, as Tech Users Reflect - Scoop.co.nz Bill Gates Steps Down, as Tech Users Reflect Scoop.co.nz, New Zealand -18 hours ago MS-DOS, a basic command-line operating system (OS), was integrated into IBM's personal computer in 1981. Microsoft's deal with IBM proved especially ... With everything going for them, athletes still can't behave - Augusta Chronicle With everything going for them, athletes still can't behave Augusta Chronicle, GA -11 hours ago It's been a rough recent stretch for our schools of interest, with alleged crimes at Georgia, Georgia Tech and Clemson making the three-peat underage ... Samsung's judgmental box of tricks gets personal - Inquirer Samsung's judgmental box of tricks gets personal Inquirer, UK -7 hours ago On Thursday in Hammersmith, the Korean tech giant launched a bundle of new products for people who like installing giant plasma screens. ... UPDATE 1-US tech firms in Malaysia see slower sales growth in 08 - Reuters UPDATE 1-US tech firms in Malaysia see slower sales growth in 08 Reuters -Jul 3, 2008 Rising fuel and food prices are expected to crimp global demand for consumer electronics products such as cell phones, MP3 players and personal computers, ... UMKC chancellor to leave for Texas Tech job - Kansas City Star UMKC chancellor to leave for Texas Tech job Kansas City Star, MO -Jul 2, 2008 Students and faculty wished him well but said they would miss the personal touch Bailey brought to mending campus communication disconnects. ... UMKC Chancellor Bailey is finalist for Texas Tech presidentBizjournals.com all 57 news articles Cheap Tech: The 20 Best Products Under $100 - PC Magazine Cheap Tech: The 20 Best Products Under $100 PC Magazine -Jul 2, 2008 An attractive choice as a personal monochrome laser, the HP LaserJet P1006 is both small and light—a perfect fit in any home office or on a desk. ... IDC: Instant messaging to overtake business e-mail - Computerworld Computerworld IDC: Instant messaging to overtake business e-mail Computerworld, Norway -12 hours ago It said that hyperconnectivity varies by industry, from nine per cent of respondents from health care to 25 per cent in high tech industries and 21 per cent ... Personal Tech - Boston Globe Personal Tech Boston Globe, United States -Jun 8, 2008 Identity thieves, for the most part, continue to be low-tech operators. Most of them are garbage pickers and online and telephone scam artists. ...