Upload and Download Pdf files to/from MS SQL Database using Razor Pages

#dotnet #tutorial #aspnet #beginners

As most of my project applications are business related, and I still plan to create such applications, thought about the idea to store attached pdf invoices in a database.

Below is a simple sample application, using Asp.Net Core 3.1 and Razor Pages, for an invoice management system with pdf file upload/download.

The Github repo for the application is here. https://github.com/zoltanhalasz/UploadFile

The application is also online, can be tested out: https://uploadfile.zoltanhalasz.net/

Prerequisites:

beginner/intermediate Razor Pages for Asp.Net Core, see study material here https://mydev-journey.blogspot.com/2019/11/razor-pages-not-for-shaving.html
intermediate C# and SQL
some basic Entity Framework Core.

My sources for learning were:

an MVC version for the upload: https://tutexchange.com/how-to-upload-files-and-save-in-database-in-asp-net-core-mvc/
the download, was discovered using various C# resources. Firstly, we create the database named UploadFile and then a table:

CREATE TABLE [dbo].[Invoices](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Number] [int] NOT NULL,
    [Date] [datetime] NOT NULL,
    [Value] [decimal](18, 2) NOT NULL,
    [Attachment] [varbinary](max) NULL,
 CONSTRAINT [PK_Invoices] PRIMARY KEY CLUSTERED ([Id] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Steps for creating the application:
Create an Asp.Net Razor Pages project
.Net Core version 3.1

Scaffold the database into the models using
https://marketplace.visualstudio.com/items?itemName=ErikEJ.EFCorePowerTools or simply copy my Data folder that is adding the necessary data structures
Alternatively, you can use the more traditional caffolding method: https://www.entityframeworktutorial.net/efcore/create-model-for-existing-database-in-ef-core.aspx

Make sure that the model and dbContext (called UploadFileContext) will be stored in Data folder.

Add to Startup.cs, ConfigureServices method:

 services.AddDbContext<UploadfileContext>(options =>
                  options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection"),           
                    sqlServerOptions => sqlServerOptions.CommandTimeout(100))
                    );

Create Index page, that will show the list of invoices

the page cshtml file will contain the listing of the invoices, together with the download, upload, and delete functionalities

https://github.com/zoltanhalasz/UploadFile/blob/master/UploadFile/Pages/Index.cshtml

the PageModel class will contain the methods to deal with:

a. download the pdf file from the database:

public async Task<IActionResult> OnPostDownloadAsync(int? id)
{
            var myInv = await _context.Invoices.FirstOrDefaultAsync(m => m.Id == id);
            if (myInv == null)
            {
                return NotFound();
            }

            if (myInv.Attachment== null)
            {
                return Page();
            }
            else
            {
                byte[] byteArr = myInv.Attachment;
                string mimeType = "application/pdf";
                return new FileContentResult(byteArr, mimeType)
                {
                    FileDownloadName = $"Invoice {myInv.Number}.pdf"
                };
            }
}

b. delete attached file from database:

public async Task<IActionResult> OnPostDeleteAsync(int? id)
{
            var myInv = await _context.Invoices.FirstOrDefaultAsync(m => m.Id == id);
            if (myInv == null)
            {
                return NotFound();
            }

            if (myInv.Attachment == null)
            {
                return Page();
            }
            else
            {
                myInv.Attachment = null;
                _context.Update(myInv);
                await _context.SaveChangesAsync();
            }

            Invoices = await _context.Invoices.ToListAsync();
            return Page();
}

Add a page to create Invoice Data (this can be done via Razor Pages scaffolding)

see https://github.com/zoltanhalasz/UploadFile/blob/master/UploadFile/Pages/Create.cshtml
and
https://github.com/zoltanhalasz/UploadFile/blob/master/UploadFile/Pages/Create.cshtml.cs

Create an Upload Page that will help with pdf file upload

This will have the file with markup, cshtml, containing the html tags for the form:

page
@model UploadFile.Pages.UploadModel
@{
}

<h1>Upload Invoice</h1>


<hr />
<div class="row">
    <div class="col-md-4">
        <form method="post" enctype="multipart/form-data">
            <div class="form-group">
                <div class="col-md-10">
                    <p>Upload file</p>
                    <input type="hidden" asp-for="@Model.ID" value="@Model.myID" />
                    <input asp-for="file" class="form-control" accept=".pdf" type="file" />
                </div>
            </div>
            <div class="form-group">
                <div class="col-md-10">
                    <input class="btn btn-success" type="submit" value="Upload" />
                </div>
            </div>
        </form>
    </div>
</div>

<div>
    <a asp-page="Index">Back to List</a>
</div>

And for the PageModel class, we will have the handler to deal with the file uploaded:

public class UploadModel : PageModel
{                  

        private readonly UploadfileContext _context;

        public UploadModel(UploadfileContext context)
        {

            _context = context;
        }
        public int ? myID { get; set; }

        [BindProperty]
        public IFormFile file { get; set; }

        [BindProperty]
        public int ? ID { get; set; }
        public void OnGet(int? id)
        {
            myID = id;
        }

        public async Task<IActionResult> OnPostAsync()
        {
            if (file != null)
            {
                if (file.Length > 0 && file.Length < 300000)
                {
                    var myInv = _context.Invoices.FirstOrDefault(x => x.Id == ID);

                    using (var target = new MemoryStream())
                    {
                        file.CopyTo(target);
                        myInv.Attachment = target.ToArray();
                    }

                    _context.Invoices.Update(myInv);
                    await _context.SaveChangesAsync();
                }

            }

            return RedirectToPage("./Index");
        }

}