DEV Community

Cover image for Upload and Download Pdf files to/from MS SQL Database using Razor Pages
Zoltan Halasz
Zoltan Halasz

Posted on

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:

My sources for learning were:

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
Enter fullscreen mode Exit fullscreen mode

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)) );
Enter fullscreen mode Exit fullscreen mode

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" }; } }
Enter fullscreen mode Exit fullscreen mode

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(); }
Enter fullscreen mode Exit fullscreen mode

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>
Enter fullscreen mode Exit fullscreen mode

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"); } }
Enter fullscreen mode Exit fullscreen mode

The end result will be like this:
Alt Text

Top comments (0)