Click here to Skip to main content
15,867,308 members
Articles / Database Development / SQL Server
Article

Load/Unload images into/from DB table

Rate me:
Please Sign up or sign in to vote.
3.88/5 (26 votes)
23 Aug 2005 218.1K   3.5K   104   28
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.

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

That's all you need.

Store Image into DB table

C#
...
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.

C#
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

C#
// 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


Written By
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 Pin
RedDk29-Sep-11 8:15
RedDk29-Sep-11 8:15 
GeneralMy vote of 4 Pin
Amir Mehrabi-Jorshari23-Jul-10 2:27
Amir Mehrabi-Jorshari23-Jul-10 2:27 
GeneralСпасибо! Pin
skiner13-Oct-08 11:51
skiner13-Oct-08 11:51 
GeneralSave an Image to SQL2000 server using OLEDB connection Pin
mahalirajesh13-Mar-07 17:13
mahalirajesh13-Mar-07 17:13 
GeneralHelp reagarding inserting Image in Oracle Databse using OLEDB Pin
rupangupta28-Jul-06 3:07
rupangupta28-Jul-06 3:07 
GeneralHelp reagarding inserting Image in Oracle Databse using OLEDB Pin
rupangupta28-Jul-06 2:49
rupangupta28-Jul-06 2:49 
GeneralThe Code for VB.net Pin
nazrulislam21-Jul-06 2:15
nazrulislam21-Jul-06 2:15 
GeneralRe: The Code for VB.net Pin
Maxim Alekseykin21-Jul-06 6:09
Maxim Alekseykin21-Jul-06 6:09 
GeneralRe: The Code for VB.net Pin
torabis874-Aug-09 4:24
torabis874-Aug-09 4:24 
QuestionError Saving the Image Pin
mig166-Mar-06 10:23
mig166-Mar-06 10:23 
AnswerRe: Error Saving the Image Pin
mig166-Mar-06 10:37
mig166-Mar-06 10:37 
GeneralRe: Error Saving the Image Pin
maziarm29-May-07 10:08
maziarm29-May-07 10:08 
GeneralSHow image .net Pin
Hanieef27-Feb-06 20:44
Hanieef27-Feb-06 20:44 
GeneralOne problem... Pin
Antonio Dias31-Aug-05 10:06
Antonio Dias31-Aug-05 10:06 
GeneralRe: One problem... Pin
Lev Vayner.5-Oct-07 8:51
professionalLev Vayner.5-Oct-07 8:51 
Questionwhy does it has error when running? Pin
uumeme12-Aug-05 3:03
uumeme12-Aug-05 3:03 
AnswerRe: why does it has error when running? Pin
Anonymous12-Aug-05 4:08
Anonymous12-Aug-05 4:08 
GeneralRe: why does it has error when running? Pin
uumeme16-Aug-05 19:05
uumeme16-Aug-05 19:05 
GeneralRe: why does it has error when running? Pin
Maxim Alekseykin18-Aug-05 4:35
Maxim Alekseykin18-Aug-05 4:35 
GeneralThank! Pin
JaimirG20-Jun-05 5:48
JaimirG20-Jun-05 5:48 
GeneralLoad Pin
rm_babar28-Apr-05 20:40
rm_babar28-Apr-05 20:40 
GeneralError Pin
lyptus23-Feb-05 11:53
lyptus23-Feb-05 11:53 
GeneralRe: Error Pin
lyptus23-Feb-05 11:57
lyptus23-Feb-05 11:57 
Nevermind.
GeneralRe: Error Pin
Maxim Alekseykin24-Feb-05 4:00
Maxim Alekseykin24-Feb-05 4:00 
GeneralGood simple artcile Pin
Nirosh9-Feb-05 16:41
professionalNirosh9-Feb-05 16:41 

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

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