Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Can I use varbinary type to store image in SQL Server database?

I am trying to store an image in my SQL Server database, what datatype should I use?

In the below code aspx.cs, I am trying to read all the bytes from the request inputstream and store it in the database, but the byte[] array is not updated properly in table. Am I missing something?

protected void Page_Load(object sender, EventArgs e)
{
        Request.InputStream.Position = 0;

        byte[] Contents = new byte[Request.InputStream.Length];

        Request.InputStream.Read(Contents, 0, (int)Request.InputStream.Length);

        con.Open();

        try
        {
            string query = "update tblImageUpload set " + IMAGE_ID + " = @imageBytes where Image_ID='" + CID + "'";

            int i = 0;

            using (cmd = new SqlCommand(query, con))
            {
                cmd.Parameters.Add("@imageBytes", SqlDbType.VarBinary, Contents.Length).Value = Contents;

                 i = cmd.ExecuteNonQuery();
            }

            Response.Write("Upload Query = " + query);
            Response.Write("Upload Code = " + i);
        } catch (Exception ex) {
            Response.Write("Upload Code=" + ex);
        }
like image 500
deepak Avatar asked Mar 26 '13 19:03

deepak


People also ask

What is the difference between VARBINARY and image?

VARBINARY(MAX) - Binary strings with a variable length can store up to 2^31-1 bytes. IMAGE - Binary strings with a variable length up to 2^31-1 (2,147,483,647) bytes.

How can we store image in SQL database?

Insert one image into SQL Server This table will have an integer (int) id and the image column named img. The data type that we are going to use to store images is the varbinary(max). The INSERT statement inserts the value 1 as the id and then inserts the image named 1. png from the folder img in the c drive.

What is VARBINARY data type in SQL Server?

varbinary [ ( n | max ) ]Variable-length binary data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of the data entered + 2 bytes. The data that is entered can be 0 bytes in length.


1 Answers

You can use VARBINARY yes. You're probably best off going with VARBINARY(MAX) to store them.

You can use it like this:

cmd.Parameters.Add("@imageBytes", SqlDbType.VarBinaryMax);
cmd.Parameters["@imageBytes"].Value = Contents;
like image 195
mattytommo Avatar answered Oct 07 '22 01:10

mattytommo