How To Insert An Image Into Sql Server Database?

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('InsertPro

Solution 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 })" />

Solution 2:

Change the Image type on the sql sever to Byte[] and use something like this. This is how I have stored images in the past.

http://www.codeproject.com/Articles/15460/C-Image-to-Byte-Array-and-Byte-Array-to-Image-Conv

If not, you can always just store the image locally and pass the image location through a string into the SQL data base, this method works well and is quick to set up.

Solution 3:

So, here are the modifications to do :

1. To insert data in the database :

   [HttpPost]

   public ActionResult InsertProduct(PRODUCT ord, HttpPostedFileBase image) { MigrationEntities1 sent = new MigrationEntities1(); if (image != null) { ord.PRODUCT_IMAGE = new byte[image.ContentLength]; image.InputStream.Read(ord.PRODUCT_IMAGE, 0, image.ContentLength); } sent.PRODUCT.Add(ord); sent.SaveChanges(); List Products = sent.PRODUCT.ToList(); return View("Products", Products); }

Note: this is the "light" way, for something that is more complete, have a look to Amin answer.

1. For displaying :

In the view

<img src="LoadImage?id=@Model.product.PRODUCT_ID"/>

And in the controller :

public FileContentResult 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();

        return new FileContentResult(product.PRODUCT_IMAGE, "image/jpeg");
    }

And everything is ok now, thanks !