How to insert an image into sql server database?

Question

As said, i'm trying to insert an image in a table, where the type of the field is Varbinary.

What i've done so far :

I've a form with many fields:

@using (Html.BeginForm("InsertProduct", "Home", FormMethod.Post, new { enctype = "multipart/form-data" }))
{
    @Html.ValidationSummary(true)

    <fieldset>
        <legend>PRODUCT</legend>

        <div class="editor-label">
            @Html.LabelFor(model => model.PRODUCT_ID)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.PRODUCT_ID)
            @Html.ValidationMessageFor(model => model.PRODUCT_ID)
        </div>

        <div class="editor-label">
            @Html.LabelFor(model => model.PRODUCT_NAME)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.PRODUCT_NAME)
            @Html.ValidationMessageFor(model => model.PRODUCT_NAME)
        </div>
        <div class="editor-label">
            @Html.LabelFor(model => model.PRODUCT_IMAGE)
        </div>
        <div class="editor-field">
            <input type="file" name="PRODUCT_IMAGE" id="PRODUCT_IMAGE" style="width: 100%;" />
        </div>

        <p>
            <input type="submit" value="Create" class="btn btn-primary"/>
        </p>
    </fieldset>
}

And all these fields allow me to construct a PRODUCT object in my controller :

public ActionResult InsertProduct(PRODUCT ord)
    {
        MigrationEntities1 sent = new MigrationEntities1();
        sent.PRODUCT.Add(ord);
        sent.SaveChanges();
        List<PRODUCT> Products = sent.PRODUCT.ToList();
        return View("Products", Products);
    }

But when i'm trying to upload the image (by clicking on Create button), i've the following :

entry is not a valid base64 string because it contains a character that is not base 64

So first of all : is it the right way to deal with images and second, I think I need to do a pre-treatemant on my image to insert it : how to o that ?

Thanks !

---

Edit :

Thanks to answers received, seems to be good for insertion. But for displaying, I still have issues (only the "not found image" piture is displayed). I've try to do it two ways : 1. <img src="[email protected]_ID"/> and in the controller

public Image LoadImage(int id)
    {
        String serviceAddress = ConfigurationManager.AppSettings["WCFADDRESS"];
        DataServiceContext context = new DataServiceContext(new Uri(serviceAddress));
        PRODUCT product = context.Execute<PRODUCT>(new Uri(serviceAddress + "prod_id?prod_id=" + id)).ToList().FirstOrDefault();

        MemoryStream ms = new MemoryStream(product.PRODUCT_IMAGE);
        Image img = Image.FromStream(ms);
        return img;
    }

And 2. :

@{

    if (Model.product.PRODUCT_IMAGE != null)
    {
        WebImage wi = new WebImage(Model.product.PRODUCT_IMAGE);
        wi.Resize(700, 700,true, true);
        wi.Write();
    }    
}

But none of them are working. What am I doing wrong ?

Answer 1

1) Change your database table to have these columns:

1: ProductImage - varbinary(MAX)
2: ImageMimeType - varchar(50)

2) Change your action method like this:

public ActionResult InsertProduct(PRODUCT ord, 
    HttpPostedFileBase PRODUCT_IMAGE)
{        
    if (ModelState.IsValid)        
        {
            MigrationEntities1 sent = new MigrationEntities1();
            if (image != null)
            {
                ord.ProductImage= new byte[PRODUCT_IMAGE.ContentLength];
                ord.ImageMimeType = PRODUCT_IMAGE.ContentType;
                PRODUCT_IMAGE.InputStream.Read(ord.ProductImage, 0,
                    PRODUCT_IMAGE.ContentLength);
            }

            else
            {
                // Set the default image:
                Image img = Image.FromFile(
                    Server.MapPath(Url.Content("~/Images/Icons/nopic.png")));
                MemoryStream ms = new MemoryStream();
                img.Save(ms, ImageFormat.Png); // change to other format
                ms.Seek(0, SeekOrigin.Begin);
                ord.ProductImage= new byte[ms.Length];
                ord.ImageMimeType= "image/png";
                ms.Read(ord.Pic, 0, (int)ms.Length);
            }

            try
            {
                sent.PRODUCT.Add(ord);
                sent.SaveChanges();

                ViewBag.HasError = "0";
                ViewBag.DialogTitle = "Insert successful";
                ViewBag.DialogText = "...";
            }
            catch
            {
                ViewBag.HasError = "1";
                ViewBag.DialogTitle = "Server Error!";
                ViewBag.DialogText = "...";
            }

            List<PRODUCT> Products = sent.PRODUCT.ToList();
            return View("Products", Products);
        }

        return View(ord);
    }

This action method is just for create. you need some works for edit and index too. If you have problem to doing them, tell me to add codes of them to the answer.

Update: How to show images:

One way to show stored images is as the following:

1) Add this action method to your controller:

    [AllowAnonymous]
    public FileContentResult GetProductPic(int id)
    {
        PRODUCT p = db.PRODUCTS.FirstOrDefault(n => n.ID == id);
        if (p != null)
        {
            return File(p.ProductImage, p.ImageMimeType);
        }

        else
        {
            return null;
        }
    }

2) Add a <img> tag in the @foreach(...) structure of your view (or wherever you want) like this:

<img width="100" height="100" src="@Url.Action("GetProductPic", "Products", routeValues: new { id = item.ID })" />