Featured

Uploading and downloading files to/from a SQL Server database in ASP.NET 5

Last modified: June 15, 2021

Watermark background image in ASP.NET 5 >

Step 1

Create an ASP.NET Core MVC application.

Create a class file called File.cs in Model folder. We use Id as a primary key, FileName, and MimeType (type of a file, e.g. pdf) as string and Content as bytes[] for storing the file content in the SQL database. File.cs file will be used to create a database table upon data migration.

public class File
{
    public string Id { get; set; }
    public string FileName { get; set; }
    public string MimeType { get; set; }
    public byte[] Content { get; set; }
}

Step 2

Right-click the project name and click "Manage Nuget Packages" and search for Microsoft.EntityFrameworkCore NuGet package. Install it, it is ORM (Object-relational mapping) to talk to the database.

We are going to use the In-Memory database for demo purposes, hence we also need to install Microsoft.EntityFrameworkCore.InMemory NuGet library.

upload-file

Step 3

Create a Data folder in the root level of your application and then create a FileContext class file in it. There we set up the ORM (Entity Framework Core) setting.

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

    }

    public DbSet<File> File { get; set; }

    protected override void OnModelCreating(ModelBuilder builder)
    {
        base.OnModelCreating(builder);
        builder.ApplyConfigurationsFromAssembly(Assembly.GetExecutingAssembly());
    }
}

Step 4

Create an Interfaces folder in the root level of your application and the create IFileService interface file in it. An interface is similar to a class except it is purely represents a contract between an object and user. We do not put the implementation of a method in the interface file, just the signature.

public interface IFileService
{
    public Task<bool> AddFile(File file);
    public Task<IEnumerable<File>> GetAll();
    public Task<File> GetFile(string Id);
}

Step 5

Create a Services folder in the root level of your application and then create a FileService class file in it. The business logic of the application lives here. It connects to the Model and performs actions such as adding a file record to a database or listing the file records. We inherit IFileService interface to FileService class.

We inject FileContext as dependency injection (DI) in the constructor. We are using asynchronous programming in c# with the help of the Task, async, and await.

In the GetAll() method, we use Linq to retrieve all the files from the database. In the GetFile() method, we get a single record of the file, filter by file id. In the AddFile() method, we assign id to new Guid string to primary key, file Id, and save the record to the database.

public class FileService: IFileService
{
    private readonly FileContext _context;
    public FileService(FileContext context)
    {
        _context = context;
    }

    public async Task<IEnumerable<File>> GetAll()
    {
        return await (from c in _context.File
                        select c).ToListAsync();
    }

    public async Task<File> GetFile(string Id)
    {
        return await (from c in _context.File
                        where c.Id == Id
                        select c).FirstOrDefaultAsync();
    }

    public async Task<bool> AddFile(File file)
    {
        try
        {
            file.Id = Guid.NewGuid().ToString();
            _context.File.Add(file);
            await _context.SaveChangesAsync();

            return true;
        }
        catch
        {
            return false;
        }
    }
}

Step 6

Create a FileController controller file with the template MVC Controller - Empty template. We inject IFileService as dependency injection (DI) in the constructor.

We retrieve bytes[] from files with the help of the MemoryStream function and converting it to Array. The file.ContentType gives us Mime Type, for example, image/jpeg, and Path.GetFileName(file.FileName) give us filename.

public class FileController : Controller
{
    private readonly IFileService _fileService;
    public FileController(IFileService fileService)
    {
        _fileService = fileService;
    }

    public async Task<IActionResult> Index()
    {
        var files = await _fileService.GetAll();
        return View(files);
    }

    public IActionResult Upload()
    {
        return View();
    }

    [HttpPost]
    public async Task UploadFile(IFormFile file)
    {
        if(file.Length > 0)
        {
            using(var ms = new MemoryStream())
            {
                file.CopyTo(ms);

                var newFile = new Models.File();
                newFile.MimeType = file.ContentType;
                newFile.FileName = Path.GetFileName(file.FileName);
                newFile.Content = ms.ToArray();

                await _fileService.AddFile(newFile);
            }             
        }

        Response.Redirect("/File");
    }

    public async Task<FileResult> Download(string fileId)
    {
        var file = await _fileService.GetFile(fileId);
        return File(file.Content, file.MimeType);
    }
}

Step 7

We right-click on the Index method and choose Add View, and then Razor View - Empty which will generate a blank view. We do the same thing for the Upload method. For index method, give the view name Index.cshtml and for upload method use Upload.cshtml.

We put a model list of File (IEnumerable<Upload_File_To_SQL_Server.Models.File>) to match view input and controller output.

@model IEnumerable<Upload_File_To_SQL_Server.Models.File>

<a class="btn btn-primary" asp-controller="File" asp-action="Upload">Upload</a>

<table class="table">
    <thead>
        <tr>
            <th>@Html.DisplayNameFor(model => model.FileName)</th>
            <th>Download</th>
        </tr>
    </thead>
    <tbody>
        @foreach(var file in Model)
        {
            <tr>
                <td>
                    @Html.DisplayFor(modelItem => file.FileName)
                </td>
                <td>
                    @Html.ActionLink("Download", "Download", "File", new { fileId = file.Id }, null)

                </td>
            </tr>
        }
    </tbody>
</table>

Step 8

Below is a simple form, where we have a file input and submit button. There is one thing which must be present that is enctype="multipart/form-data"

<form asp-controller="File" asp-action="UploadFile" method="post" enctype="multipart/form-data">
    <input type="file" name="file" />
    <button type="submit">Upload File</button>
</form>

Step 9

For quick access to file view, we add this in Home Controller Index View.

<a class="btn btn-primary" asp-area="" asp-controller="File" asp-action="Index">File List</a>

Step 10

We also need to add few lines in the startup.cs in ConfigureServices method for FileService DI. Without it, the application will not work.

We add the dbContext (i.e. FileContext) to use in memory database. and we need to register FileService so that we can use it as DI(dependency injection).

services.AddDbContext<FileContext>(c =>
        c.UseInMemoryDatabase("demo"));

services.AddScoped<IFileService, FileService>();

The complete source code can be found here

< Setup Serilog logging on ASP.NET 5