SqlDataReader: Read varbinary into byte array

Question

I have a SQL Server table with a varbinary(max) column. I use it to store images in it. The images are selected with an OpenFileDialog, translated into a byte[] like this

public byte[] ConvertImageToByteArray(String filepath)
{
    try
    {
        return File.ReadAllBytes(filepath);
    }
    catch (Exception) 
    {
        throw; 
    }
}

and then stored into the database using this line of code:

sqlCmd.Parameters.Add("@image", SqlDbType.VarBinary).Value = image;

It looks like this stored in the database, so I guess all seems to work like expected.

Unfortunately I am unable to load the images back from the datatable.

I am using a SqlDataReader to do so:

DbSql db = new DbSql();

SqlDataReader dr = db.GetDataReader(sqlCmd);

if (dr.Read())
{
    if (!dr.IsDBNull(1))
        productName = dr.GetString(1);

    if (!dr.IsDBNull(2))
        identNumber = dr.GetString(2);

    [...]

    if (!dr.IsDBNull(23))
        comment = dr.GetString(23);

    if (!dr.IsDBNull(24))
    {
        byte[] image = dr.GetSqlBytes(24).Value;  // <- This is where I try to grab the image
    }
}

It seems like I am not able to create a proper byte[] with

image = dr.GetSqlBytes(24).Value;

because my next step is not able to turn it into an image again:

public Image ConvertImageFromByteArray(byte[] array)
{
        try
        {
            MemoryStream ms = new MemoryStream(array);
            return Image.FromStream(ms);
        }
        catch (Exception) { throw; }
    }

EDIT: When trying something like pictureBox.Image = ConvertImageFromByteArray(image) I get an error saying "Invalid parameter" (self translated, saying "Ungültiger Parameter" in german)

Anyone able to offer a solution?

Why are you read data using GetString which will corrupt the data. dr.Read() reads one row of the database which contains the entire binary object. So just use : MemoryStream ms = new MemoryStream((byte[])dr.GetValue(23)); which will read the contents of the 24th column. — jdweng, Jul 08 '18 at 10:22
@Nkosi `image` is a `byte[]`. Just added that to my question and also the error message I get. — farosch, Jul 08 '18 at 12:55
Can you copy the complete error. I am certain it provided more details than that — Nkosi, Jul 08 '18 at 12:58
@ChrisCatignani Unfortunately @zohar-peled's solution is not working. It throws exactly the same error as `image = dr.GetSqlBytes(24).Value` — farosch, Jul 08 '18 at 13:17

Chris Catignani · Accepted Answer · 2018-07-08T13:56:59.527

2

Once you cast your varbinary(MAX) to a byte array

image = (byte[])dr[24];

Try this..

    MemoryStream ms = new MemoryStream(image);
    imagePictureBox.Image = System.Drawing.Image.FromStream(ms);

This is how I create the byte array...

    if (File.Exists(sLogoName) == false)
       throw new Exception("File Not Found: " + sLogoName);
    FileStream sourceStream = new FileStream(sLogoName, FileMode.Open, FileAccess.Read);
    int streamLength = (int)sourceStream.Length;
    Byte[] byLogo = new Byte[streamLength];
    sourceStream.Read(byLogo, 0, streamLength);
    sourceStream.Close();

edited Jul 08 '18 at 13:56

answered Jul 08 '18 at 13:25

Chris Catignani

5,040
16
42
49

1

Thanks a lot! The problem was not how I tried to load the image from the database but how I stored it. Using the code you provided to write the byte array I am now able to load the image. Awesome :) – farosch Jul 08 '18 at 15:36
1

Glad that worked...I had problems with it originally. – Chris Catignani Jul 08 '18 at 17:17

SqlDataReader: Read varbinary into byte array

1 Answers1