Click here to Skip to main content
Click here to Skip to main content

Load/Unload images into/from DB table

, 23 Aug 2005
Rate this:
Please Sign up or sign in to vote.
Explains how to load a BLOB data into a DB table and how to get it from the DB table.

Sample Image - imageStorage.jpg

Introduction

We all often need to store Binary Large Objects (BLOBs) into a DB table and then get them from there. Now I'm going to explain the easiest way to do this.

Prepare Database

Run SQL Server Enterprise Manager and create a new database, call it 'Test'. Create a new table and call it Images.

CREATE TABLE Images ([stream] [image] NULL)

That's all you need.

Store Image into DB table

...
byte[] content = ReadBitmap2ByteArray(fileName);
StoreBlob2DataBase(content);
...
protected static byte[] ReadBitmap2ByteArray(string fileName)
{
  using(Bitmap image = new Bitmap(fileName))
  {
    MemoryStream stream = new MemoryStream();
    image.Save(stream, System.Drawing.Imaging.ImageFormat.Bmp);
    return stream.ToArray();
  }
}

protected static void StoreBlob2DataBase(byte[] content)
{
   SqlConnection con = Connection;
   con.Open();
   try
   {
     // insert new entry into table
     SqlCommand insert = new SqlCommand(
     "insert into Images ([stream]) values (@image)",con);
     SqlParameter imageParameter = 
     insert.Parameters.Add("@image", SqlDbType.Binary);
     imageParameter.Value = content;
     imageParameter.Size  = content.Length;
     insert.ExecuteNonQuery();
   }
   finally
   {
      con.Close();
   }
}

Store Images for OLEDB provider

Some of us use OLEDB provider to communicate with SQL Server. In this case you should use the code below to store images into your DB. Pay attention to using '?' instead of '@image' in the SQL query.

protected static void StoreBlob2DataBaseOleDb(byte[] content)
{
   try
   {
      using(OleDbConnection con = Connection)
      {
         con.Open();

         // insert new entry into table
         using(OleDbCommand insert = new OleDbCommand(
             "insert into Images ([stream]) values (?)",con))
         {
            OleDbParameter imageParameter = 
            insert.Parameters.Add("@image", OleDbType.Binary);
            imageParameter.Value = content;
            imageParameter.Size  = content.Length;
            insert.ExecuteNonQuery();
         }
      }
   }
   catch(Exception ex)
   {
      // some exception processing
   }
}

Get Image from DB table and show it

 // get image
 DataRowView drv = (DataRowView) _cm.Current;
 byte[] content = (byte[])drv["stream"];
 MemoryStream stream = new MemoryStream(content);
 Bitmap image = new Bitmap(stream);
 
 ShowImageForm f = new ShowImageForm();
 f._viewer.Image = image;
 f.ShowDialog(this);

Conclusion

You can use this technique to work with any type of binary data without using storage procedures. Good Luck.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

About the Author

Maxim Alekseykin
Team Leader
Russian Federation Russian Federation
MCAD
 
Now is looking for remote job.
 
- C++/C#, VB/VBA, SQL Server/Access databases.
- automatic testing, code review
- performance tuning
max.uk2005@gmail.com
-

Comments and Discussions

 
QuestionThis simple solution to stuffing image data in an SQL database compiles @ VS2010 PinmemberRedDK29-Sep-11 8:15 
GeneralMy vote of 4 PinmemberAmir Mehrabi-Jorshary23-Jul-10 2:27 
GeneralСпасибо! Pinmemberskiner13-Oct-08 11:51 
GeneralExample with SQL Server Pinmembermayurmv5-Sep-08 17:21 
GeneralSave an Image to SQL2000 server using OLEDB connection Pinmembermahalirajesh13-Mar-07 17:13 
GeneralHelp reagarding inserting Image in Oracle Databse using OLEDB Pinmemberrupangupta28-Jul-06 3:07 
GeneralHelp reagarding inserting Image in Oracle Databse using OLEDB Pinmemberrupangupta28-Jul-06 2:49 
GeneralThe Code for VB.net Pinmembernazrulislam21-Jul-06 2:15 
GeneralRe: The Code for VB.net PinmemberMaxim Alekseikin21-Jul-06 6:09 
GeneralRe: The Code for VB.net Pinmembertorabis874-Aug-09 4:24 
QuestionError Saving the Image Pinmembermig166-Mar-06 10:23 
AnswerRe: Error Saving the Image Pinmembermig166-Mar-06 10:37 
GeneralRe: Error Saving the Image Pinmembermaziarm29-May-07 10:08 
GeneralSHow image .net PinmemberHanieef27-Feb-06 20:44 
GeneralOne problem... PinsussAntonio Dias31-Aug-05 10:06 
GeneralRe: One problem... PinmemberLev Vayner.5-Oct-07 8:51 
Questionwhy does it has error when running? Pinmemberuumeme12-Aug-05 3:03 
AnswerRe: why does it has error when running? PinsussAnonymous12-Aug-05 4:08 
GeneralRe: why does it has error when running? Pinmemberuumeme16-Aug-05 19:05 
GeneralRe: why does it has error when running? PinmemberMaxim Alekseikin18-Aug-05 4:35 
GeneralThank! PinmemberJaimirG20-Jun-05 5:48 
GeneralLoad Pinmemberrm_babar28-Apr-05 20:40 
GeneralError Pinmemberlyptus23-Feb-05 11:53 
GeneralRe: Error Pinmemberlyptus23-Feb-05 11:57 
GeneralRe: Error PinmemberMaxim Alekseikin, MCAD24-Feb-05 4:00 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140709.1 | Last Updated 24 Aug 2005
Article Copyright 2005 by Maxim Alekseykin
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid