Saving Images in Databases

VB version here.

This question gets asked all the time so I thought an example was in order.

Loading an image from a database field:

vb.net Code:

SqlConnection connection = new SqlConnection("connection string here");
SqlCommand command = new SqlCommand("SELECT Picture FROM MyTable WHERE ID = 1", connection);
 
connection.Open();
 
byte[] pictureData = (byte[])command.ExecuteScalar();
 
connection.Close();
 
Image picture = null;
 
// Create a stream in memory containing the bytes that comprise the image.
using (MemoryStream stream = new MemoryStream(pictureData))
{
    // Read the stream and create an Image object from the data.
    picture = Image.FromStream(stream);
}

Saving an image to a database field:

CSharp Code:

SqlConnection connection = new SqlConnection("connection string here");
SqlCommand command = new SqlCommand("UPDATE MyTable SET Picture = @Picture WHERE ID = 1", connection);
 
// Create an Image object.
using (Image picture = Image.FromFile("file path here"))
{
    // Create an empty stream in memory.
    using (MemoryStream stream = new MemoryStream())
    {
        // Fill the stream with the binary data from the Image.
        picture.Save(stream, ImageFormat.Jpeg);
 
        // Get an array of Bytes from the stream and assign to the parameter.
        command.Parameters.Add("@Picture", SqlDbType.VarBinary).Value = stream.GetBuffer();
    }
}
 
connection.Open();
command.ExecuteNonQuery();
connection.Close();

Please take note: this is example code only. Please don't paste it into your project and then ask why it doesn't work. Note also that while this code uses ExecuteScalar to get a single image's worth of data and ExecuteNonQuery to update a single existing record, the principles are the same no matter how you're retrieving and saving your data. The important part is the MemoryStream. That is the link between the Byte array that gets stored in the database and the binary Image object in your C# app. For example, if you have 100 rows in a DataTable then you just use a MemoryStream for each row and assign the Byte array to the appropriate field of each row. You then just call the Update method of your DataAdapter as normal.

Quick question:

What do I do if the user decided not to choose an image?

I have an Access 2007 database that can store an image using your code, the image is optional. If the user decides not to choose an image then I don't know what to store in the database.

In the GUI I have a Picturebox that shows the image the user has chosen. They use an OpenFileDialog to choose an image location, and I then set the ImageLocation property of the PictureBox to that path.
Then, when saving, I take the Image property of the PictureBox directly as the Image object you use in your code.

But when the user did not chose an image, the Image is null.


I am using this code to load:

csharp Code:

private Image LoadImage(byte[] picData)
        {
            Image pic = null;
            using (MemoryStream stream = new MemoryStream(picData))
            {
                pic = Image.FromStream(stream);
            }
            return pic;
        }

And I am using this code to save:

csharp Code:

public void ExecuteNonQuery(string sql, Image pic)
        {
            if (this.IsOpen())
            {
                using (OleDbCommand cmd = new OleDbCommand())
                {
                    cmd.Connection = _connection;
                    cmd.CommandType = System.Data.CommandType.Text;
                    cmd.CommandText = sql;
 
                    using (MemoryStream stream = new MemoryStream())
                    {
                        cmd.Parameters.Clear();
                        if (pic != null)
                        {
                            pic.Save(stream, ImageFormat.Jpeg);
                            cmd.Parameters.Add("@foto", OleDbType.Binary).Value = stream.GetBuffer();
                        }
                        else
                        {
                            // what to do?
                            cmd.Parameters.Add("@foto", OleDbType.Binary).Value = new byte[] {};
                        }
                    }
                    cmd.ExecuteNonQuery();
                }
            }
            else
            {
                throw new Exception(strNO_DB_CONNECTION);
            }
        }

(The @foto parameter will always be present in the sql that I pass to this function.)

I tried various things for the value of the @foto parameter:
- null
- 0
- new byte[] {}
- simply stream.GetBuffer() as if there was a picture.

None of them work. I keep getting an exception "Parameter is not valid." on the Image.FromStream method in the Load function...

I can't simply not save it when the user didn't chose an image, because it is possible for the user to edit a record and remove the image that was previously chosen. So if I don't do anything that change wouldn't be recorded as the old image would still be in the database.


To fix this I can check if the picData array has zero length. That works when I simply store stream.GetBuffer on a new (empty) stream, but I'm not really sure if that's the 'accepted' way of doing it. What am I storing in the database in that case?

Quote:

---

Originally Posted by NickThissen

What do I do if the user decided not to choose an image?

---

Regardless of the data type of the column, if you want to store a null value into a database from .NET code then you use DBNull.Value.

Thanks, I never knew that.


Now I got another problem. I can save an image to the database just fine. When I later load the record, I can also load and display the image. But, if I then save the record again (without changing the image), then it says "A generic error occured in GDI+" on the Save line:

Code:

---

pic.Save(stream, ImageFormat.Jpeg);

---

It works the first time (when the user picks an image), but when I load the image and then try to save it again, without having changed it, then this error occurs.

I suppose I could build some kind of check to see if the image has changed, and simply don't save it if it hasn't, but that feels like a cheap workaround. I must be doing something wrong, perhaps not disposing some object properly?

What can cause this error?

Quote:

---

Originally Posted by NickThissen

Now I got another problem

---

I'd have to know a bit more about exactly what you're doing. Please start a new thread in the VB.NET forum and provide a full description, including all the relevant code. Feel free to send me a PM with a link and I'll have a closer look.