Hands-On ASP.NET Core 3.1 : Learn MVC, Razor Pages, Web API, Entity Framework Core, and Blazor. Private online coaching for software developers. Click here to know more.

Store images in SQL Server using EF Core and ASP.NET Core

At times you need to store images in a data store rather than storing them as physical files. You can store them in and retrieve them from SQL Server using Entity Framework Core and ASP.NET Core. This article shows you how.

In order to work through this example, you need a SQL Server table called Images as shown below: 

As you can see there are three columns namely Id, ImageTitle, and ImageData. The ImageTitle column stores a string containing title or name of an image. The ImageData column stores the actual image data. This column has its type set to varbinary(MAX) since image is a binary data.

Ok. Now ad NuGet package for EF Core provider for SQL Server - Microsoft.EntityFrameworkCore.SqlServer.

Once you add the EF Core provider for SQL Server, you need to create Entity Framework Core model consisting of a DbContext class and an entity class. The Image entity class is shown below:

public class Image
{
    public int Id { get; set; }
    public string ImageTitle { get; set; }
    public byte[] ImageData { get; set; }
}

The Image class is a POCO and follows conventions to map with the underlying Images table schema. Notice that ImageData property is a byte array that stores binary image data.

The AppDbContext class is shown below:

public class AppDbContext:DbContext
{
    public AppDbContext(DbContextOptions
<AppDbContext> options) : base(options)
    {
    }

    public DbSet<Image> Images { get; set; }
}

You also need to register AppDbContext with ASP.NET Core's DI framework. This is done inside ConfigureServices() method of the Startup class as shown below:

public void ConfigureServices(IServiceCollection services)
{
    services.AddControllersWithViews();

    services.AddDbContext<AppDbContext>(o =>
    {
        o.UseSqlServer("data source=.;
        initial catalog=MyImagesDb;
        integrated security=true");
    });
}

So far so good. Next, we will create a <form> that allows us to upload images files to the server. This form is shown below:

<form asp-action="UploadImage" 
      asp-controller="Home" 
      method="post" 
      enctype="multipart/form-data">
    <input type="file" id="file1" 
           name="file1" 
           multiple="multiple" />
    <button type="submit">Upload File(s)</button>
</form>

This form is quite simple and straightforward. Notice that the enctype attribute is set to multipart/form-data since you are uploading files from the client to the server. Clicking on the Upload Files button submits the form to the UploadImage() action. The UploadImage() action is showb below:

[HttpPost]
public IActionResult UploadImage()
{
    foreach(var file in Request.Form.Files)
    {
        Image img = new Image();
        img.ImageTitle = file.FileName;

        MemoryStream ms = new MemoryStream();
        file.CopyTo(ms);
        img.ImageData = ms.ToArray();

        ms.Close();
        ms.Dispose();

        db.Images.Add(img);
        db.SaveChanges();
    }
    ViewBag.Message = "Image(s) stored in 
database!";
    return View("Index");
}

The UploadImage() action iterates through the uploaded files. This is done using the Request.Form.Files collection. Inside the foreach loop, an Image object is created and its ImageTitle property is set to the file name of the image. The file name of an image is obtained from the FileName property of IFormFile object.

We are more interested in the image data. To grab the image data the code creates a MemoryStream and copies the uploaded image data into it using the CopyTo() method. The MemoryStream is converted into a byte array using its ToArray() method. This byte array is stored in the ImageData property of the Image object.

The newly created Image entity is added to the Images DbSet and SaveChanges() method is called to save the data to the underlying database. This saves the uploaded image into the Images table of SQL Server.

Now let's retrieve the image and display it on a view.

Add another <form> to the view as shown below:

<form asp-action="RetrieveImage" 
      asp-controller="Home" 
      method="post">
    <button type="submit">Show Latest Image</button>
</form>

It's a simple form that has a submit button and POSTs to RetrieveImage() action. The RetrieveImage() action is shown below:

[HttpPost]
public ActionResult RetrieveImage()
{
    Image img = db.Images.OrderByDescending
(i=>i.Id).SingleOrDefault();
    string imageBase64Data = 
Convert.ToBase64String(img.ImageData);
    string imageDataURL = 
string.Format("data:image/jpg;base64,{0}", 
imageBase64Data);
    ViewBag.ImageTitle = img.ImageTitle;
    ViewBag.ImageDataUrl = imageDataURL;
    return View("Index");
}

Notice what's going on inside the RetrieveImage() action. First, the code grabs a latest Image added to the database using SingleOrDefault() method. This image is is the form of a byte array and can't be displayed on the view directly. So, the code converts the byte array into Base64 encoded string. This string is then used to form what is known as Image Data URL. Notice that image type is mentioned as jpg. You should change it as per your requirement.

The image data URL is stored in ViewBag's ImageDataUrl property. To display this image you can use the following markup.

<h1>@ViewBag.Message</h1>
<h1>@ViewBag.ImageTitle</h1>
<img src="@ViewBag.ImageDataUrl" />

As you can see the src attribute of <img> tag is assigned the ImageDataUrl value.

A sample run of this application is shown below:

That's it for now! Keep coding!!


Bipin Joshi is an independent software consultant, trainer, author, yoga mentor, and meditation teacher. He has been programming, meditating, and teaching for 24+ years. He conducts instructor-led online training courses in ASP.NET family of technologies for individuals and small groups. He is a published author and has authored or co-authored books for Apress and Wrox press. Having embraced the Yoga way of life he also teaches Ajapa Yoga to interested individuals. To know more about him click here.

Get connected : Facebook  Twitter  LinkedIn  YouTube

Posted On : 09 December 2019


Tags : ASP.NET ASP.NET Core Data Access MVC C# Visual Studio


Subscribe to our newsletter

Get monthly email updates about new articles, tutorials, code samples, and how-tos getting added to our knowledge base.

  

Receive Weekly Updates