Size of Image while storing it in database

Question

I'm facing an odd problem regarding image size.

I've made a simple application which stores and retrieves images in a database. When I'm reading an image from the file, its size is in kB(kilobytes) and so is the length of the byte array.

There are two pictureboxes. pb1 for storing, and pb2 for loading.

My store() and load() methods are given below:

note: openConnState() and CloseConnState() are methods for closing and opening connections. And the byte[] img_byte and imgfilelength = 0 are defined publicly in the class.

Store:

private void StoreImage(string ChosenFile)
{
    try
    {
        //MemoryStream ms = new MemoryStream();
        //pb1.Image.Save(ms, ImageFormat.Jpeg);
        //img_byte = new byte[ms.Length];
        //ms.Position = 0;
        //ms.Read(img_byte, 0, img_byte.Length);

        FileInfo fileImage = new FileInfo(ChosenFile);
        imgfilelength = fileImage.Length;
        FileStream fs = new FileStream(ChosenFile, FileMode.Open, FileAccess.Read, FileShare.Read);
        img_byte = new Byte[Convert.ToInt32(imgfilelength)];
        int count, sum = 0;

        while ((count = fs.Read(img_byte, 0, Convert.ToInt32(imgfilelength))) > 0)
        {
            sum += count;
        }

        //int byteread = fs.Read(img_byte, 0, Convert.ToInt32(imgfilelength));
        fs.Close();
    }
    catch (Exception e)
    {
        throw e;
    }
}

public void storetoDB()
{
    OpenConnState(conn);
    string str = "use db2 \n insert into TableImg(Image) \n values('" + img_byte + "')";
    SqlCommand cmd = new SqlCommand(str, conn);

    try
    {
        cmd.ExecuteNonQuery();
    }
    catch (Exception e)
    {
        throw e;
    }
    finally
    {
        CloseConnState(conn);
    }
}

Load:

public void Loadimg()
{
    try
    {
        pb2.Image = null;
        byte[] getbyte = LoadImagefromDB(3);

        using (MemoryStream ms = new MemoryStream(getbyte))
        {
            pb2.Image = Image.FromStream(ms);
        }
        pb2.Refresh();

    }
    catch (Exception e)
    {
        throw e;
    }
}

public byte[] LoadImagefromDB(long pid)
{
    byte[] img = null;
    OpenConnState(conn);
    string str = "use db2 \n select Image from TableImg where P_Id = " + pid;
    SqlCommand cmd = new SqlCommand(str, conn);

    try
    {
        img = (byte[])cmd.ExecuteScalar();

        return img;
    }
    catch (System.Exception e)
    {
        throw e;
    }
    finally
    {
        CloseConnState(conn);
    }
}

I store the image into a database using the storeDB() method given above, but when I retrieve the image using the load() method given above, I get an error saying parameter invalid. I found out the problem is likely to be related to the length of the byte array, because when I retrieve the 'image' datatype value of database into a byte array, the length of byte array will always be 13.

And I even ran the below query to get its size in database, it is still the same, i.e. 13 bytes.

select len(Convert(varbinary(max), Image)) from TableImg where P_Id = 1

Can anyone tell me, why?

This is old but... you basically should convert the image to a base64 string then store the string... http://www.dailycoding.com/posts/convert_image_to_base64_string_and_base64_string_to_image.aspx — Paul Zahra, Feb 25 '16 at 12:05

score 2 · Accepted Answer · edited May 23 '17 at 12:31

2

I retrieve the 'image' datatype value of database into a byte array, the length of byte array will always be 13.

You are trying to do this:

use db2 \n insert into TableImg(Image) \n values('System.Byte[]')

obviously, length of the string System.Byte[] will always be 13.

You have to convert that binary data to other type before insert.

According to this post if your image is quite small in bytes, you can store it as VARBINARY type. If it big, you should store it as a file in the drive.

EDIT

You can use like this:

using (SqlCommand cmd = new SqlCommand("use db2 \n insert into TableImg(Image) \n values(@binaryValue)", conn))
{
    cmd.Parameters.Add("@binaryValue", SqlDbType.VarBinary, img_byte.Length).Value = img_byte;
    cmd.ExecuteNonQuery();
}

edited May 23 '17 at 12:31

Community

1
1

answered Feb 25 '16 at 11:48

NoName

7,940
13
56
108

the conversion is giving me the byte array's length 3 times the original. Damn.. Seriously 3 times!! like if it is 5kB it will return 15kB while loading. Which means in database 15kB is occupied by it. – Sagar Maru Feb 26 '16 at 04:55
and if the conversion is correct, then what should be the datatype of my column in the database!!? I have taken it as "image". – Sagar Maru Feb 26 '16 at 05:00
1

According to [this post](http://stackoverflow.com/a/5613926/1560697) if your image is quite small in bytes, you can store it as `VARBINARY` type. – NoName Feb 26 '16 at 05:09
@SagarMaru still, if you insert image to SQL DB, you can not use plain binary. See my edited answer for a suggest – NoName Feb 26 '16 at 05:17
plus I am unable to do that replace method of byte[], even when System.Linq is already added. – Sagar Maru Feb 26 '16 at 05:24
@SagarMaru sorry I'm unclear what you say, can you explain more clearly? It throw exception or C# just don't let you do like the code before? – NoName Feb 26 '16 at 05:31
It worked atleast to retrieve the image, though the image retrieved isn't correct. I think the reason could be unable to add Replace() part of my byte array, i.e. img_byte. – Sagar Maru Feb 26 '16 at 05:31
from the link http://stackoverflow.com/a/1956004/1560697 there is given the Replace() part in answer by erikkallen – Sagar Maru Feb 26 '16 at 05:32
it throws an error: Error 2 'System.Array' does not contain a definition for 'Replace' and no extension method 'Replace' accepting a first argument of type 'System.Array' could be found (are you missing a using directive or an assembly reference? – Sagar Maru Feb 26 '16 at 05:33
@SagarMaru Can you update your question and add new code you use to `insert` and `select` image? – NoName Feb 26 '16 at 05:33
can you tell me what should I write instead of 8000 in your updated answer in my case? I m not getting what to replace it with? – Sagar Maru Feb 26 '16 at 05:38
forget the previous errors. my bad..!! the insert query is same as you have answered and the select query is same as in the question. – Sagar Maru Feb 26 '16 at 05:39
@SagarMaru you can use like this: `using (SqlCommand cmd = new SqlCommand("use db2 \n insert into TableImg(Image) \n values(@binaryValue)", conn)) { cmd.Parameters.Add("@binaryValue", SqlDbType.VarBinary, img_byte.Length).Value = img_byte; cmd.ExecuteNonQuery(); }` – NoName Feb 26 '16 at 05:42
1

WOW.. thanks.. I got it!!! You update in your answer to img_byte.Length. And I mark it as the answer! – Sagar Maru Feb 26 '16 at 05:47

Size of Image while storing it in database

1 Answers1