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!!