How to insert uploaded image into varbinary(max) database column

Question

I want process form submit and save jpg image into varbinary sql column. I have code but it does not work properly, it is saving only empty bytes like 0x00...0000. So no errors are raised and database row is inserted successfully, but varbinary column seems to me corrupted.

The code is following

Models

public class FrontendModel
    {
        public HttpPostedFileBase Photo1 { get; set; }
    }

public class SubmitModel
    {
        public byte[] ImageData { get; set; }
        public decimal ImageSizeB { get; set; }

        public SubmitModel
        (
            HttpPostedFileBase Photo
        )
        {

            this.ImageData = new byte[Photo.ContentLength];
            Photo.InputStream.Read(ImageData, 0, Convert.ToInt32(Photo.ContentLength));

        this.ImageSizeB = Photo.ContentLength;
    }

Controller

[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Index(FrontendModel m)
{

    using (var db = new ABC.Models.ABCDBContext())
    {

        using (var scope = new TransactionScope())
        {
            if (m.Photo1 != null && m.Photo1.ContentLength > 0)
                db.InsertSubmit(new SubmitModel(m.Photo1));

            scope.Complete();
        }
    }

    return View(new FrontendModel());
}

DB Insert Function

public void InsertSubmit(SubmitModel m)
{
    Database.ExecuteSqlCommand(
        "spInsertSubmit @p1",
        new SqlParameter("p1", m.ImageData),
    );
}

SQL DB Procedure

CREATE PROCEDURE [dbo].[spInsertSubmit]
    @ImageData VARBINARY(max)
AS
        INSERT INTO dbo.Images (Image)
        VALUES (@ImageData)

what am I doing wrong ? Thank you

PS:

I also tried something like this but it behave the same

using (var binaryReader = new BinaryReader(Photo.InputStream))
            {
                this.ImageData = binaryReader.ReadBytes(Photo.ContentLength);
            }

then I tried

using (Stream inputStream = Photo.InputStream)
            {
                MemoryStream memoryStream = inputStream as MemoryStream;
                if (memoryStream == null)
                {
                    memoryStream = new MemoryStream();
                    inputStream.CopyTo(memoryStream);
                }
                ImageData = memoryStream.ToArray();
            }

but error shows in calling DB function with error message, Parameter is not valid

i have the same problem as is mentioned here File uploading and saving to database incorrectly

I found that when i assign Input stream to memory stream, the memory stream is empty ?!

score 1 · Answer 1 · edited May 23 '17 at 11:59

1

Your procedure specifies that the parameter is called @ImageData but your code

  Database.ExecuteSqlCommand(
        "spInsertSubmit @p1",
        new SqlParameter("p1", m.ImageData),
    );

seems to be passing a parameter called @p1

Edit

Also I think that you have to specify the type explicitly when working with a byte array larger than 8k. See this link: Inserting a byte array larger than 8k bytes

 Database.ExecuteSqlCommand(
        "spInsertSubmit @p1",
        new SqlParameter("@p1", SqlDbType.VarBinary) { Value = m.ImageData },
    );

edited May 23 '17 at 11:59

Community

1
1

answered Nov 13 '15 at 13:15

Matt Evans

7,113
7
32
64

thank you, it was a typo in a question but in the code it is okay. Also I can upload 2MB data but the problem is that the binary is corrupted. – Muflix Nov 13 '15 at 13:19
Are you specifying the type explicitly as per the edit ? – Matt Evans Nov 13 '15 at 13:20
I am not sure I understand you, in insertion I do not specify the varbinary type, I only past byte[] variable. – Muflix Nov 13 '15 at 13:23
Thank you i tried it but now it was stored only "0x" record and not "0x0000..0" as before. I dont know what iam doing wrong :/ – Muflix Nov 13 '15 at 13:30
Another possible typo: Image instead of ImageData in your stored procedure definition? – Matt Evans Nov 13 '15 at 13:36
I edited it, there are no syntax errors in the code, the data are stored into database successfully, only varbinary column is corrupted :/ contains only "0x" or "0x000...0" values. Source images which are uploaded are okay. – Muflix Nov 13 '15 at 13:40

score 0 · Answer 2 · answered Nov 13 '15 at 15:31

0

Ok i found a solution. This code works!

this.ImageData = new byte[streamLength];
Photo.InputStream.Position = 0;
Photo.InputStream.Read(this.ImageData, 0, this.ImageData.Length);

The line added is Photo.InputStream.Position = 0;

answered Nov 13 '15 at 15:31

Muflix

6,192
17
77
153

How to insert uploaded image into varbinary(max) database column

2 Answers2