Click here to Skip to main content
11,705,763 members (57,946 online)
Click here to Skip to main content

Load/Unload images into/from DB table

, 23 Aug 2005 181.6K 3.2K 103
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

Share

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
-

You may also be interested in...

Comments and Discussions

 
QuestionThis simple solution to stuffing image data in an SQL database compiles @ VS2010 Pin
RedDK29-Sep-11 8:15
memberRedDK29-Sep-11 8:15 
GeneralMy vote of 4 Pin
Amir Mehrabi-Jorshary23-Jul-10 2:27
memberAmir Mehrabi-Jorshary23-Jul-10 2:27 
GeneralСпасибо! Pin
skiner13-Oct-08 11:51
memberskiner13-Oct-08 11:51 
GeneralExample with SQL Server Pin
mayurmv5-Sep-08 17:21
membermayurmv5-Sep-08 17:21 
GeneralSave an Image to SQL2000 server using OLEDB connection Pin
mahalirajesh13-Mar-07 17:13
membermahalirajesh13-Mar-07 17:13 
GeneralHelp reagarding inserting Image in Oracle Databse using OLEDB Pin
rupangupta28-Jul-06 3:07
memberrupangupta28-Jul-06 3:07 
GeneralHelp reagarding inserting Image in Oracle Databse using OLEDB Pin
rupangupta28-Jul-06 2:49
memberrupangupta28-Jul-06 2:49 
GeneralThe Code for VB.net Pin
nazrulislam21-Jul-06 2:15
membernazrulislam21-Jul-06 2:15 
GeneralRe: The Code for VB.net Pin
Maxim Alekseikin21-Jul-06 6:09
memberMaxim Alekseikin21-Jul-06 6:09 
GeneralRe: The Code for VB.net Pin
torabis874-Aug-09 4:24
membertorabis874-Aug-09 4:24 
QuestionError Saving the Image Pin
mig166-Mar-06 10:23
membermig166-Mar-06 10:23 
AnswerRe: Error Saving the Image Pin
mig166-Mar-06 10:37
membermig166-Mar-06 10:37 
GeneralRe: Error Saving the Image Pin
maziarm29-May-07 10:08
membermaziarm29-May-07 10:08 
GeneralSHow image .net Pin
Hanieef27-Feb-06 20:44
memberHanieef27-Feb-06 20:44 
GeneralOne problem... Pin
Antonio Dias31-Aug-05 10:06
sussAntonio Dias31-Aug-05 10:06 
GeneralRe: One problem... Pin
Lev Vayner.5-Oct-07 8:51
memberLev Vayner.5-Oct-07 8:51 
Questionwhy does it has error when running? Pin
uumeme12-Aug-05 3:03
memberuumeme12-Aug-05 3:03 
AnswerRe: why does it has error when running? Pin
Anonymous12-Aug-05 4:08
sussAnonymous12-Aug-05 4:08 
GeneralRe: why does it has error when running? Pin
uumeme16-Aug-05 19:05
memberuumeme16-Aug-05 19:05 
GeneralRe: why does it has error when running? Pin
Maxim Alekseikin18-Aug-05 4:35
memberMaxim Alekseikin18-Aug-05 4:35 
GeneralThank! Pin
JaimirG20-Jun-05 5:48
memberJaimirG20-Jun-05 5:48 
GeneralLoad Pin
rm_babar28-Apr-05 20:40
memberrm_babar28-Apr-05 20:40 
GeneralError Pin
lyptus23-Feb-05 11:53
memberlyptus23-Feb-05 11:53 
GeneralRe: Error Pin
lyptus23-Feb-05 11:57
memberlyptus23-Feb-05 11:57 
GeneralRe: Error Pin
Maxim Alekseikin, MCAD24-Feb-05 4:00
memberMaxim Alekseikin, MCAD24-Feb-05 4:00 
GeneralGood simple artcile Pin
Nirosh9-Feb-05 16:41
memberNirosh9-Feb-05 16:41 
GeneralRe: Good simple artcile Pin
Maxim Alekseikin, MCAD22-Feb-05 0:18
memberMaxim Alekseikin, MCAD22-Feb-05 0:18 
GeneralRe: Good simple artcile Pin
Nirosh1-Nov-06 2:34
memberNirosh1-Nov-06 2:34 

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 | Terms of Use | Mobile
Web02 | 2.8.150819.1 | Last Updated 24 Aug 2005
Article Copyright 2005 by Maxim Alekseykin
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid