Generate PDF reports from SQL Server data using iText 7 Core library in ASP.NET Core MVC 6.0

PDF stands for "Portable Document Format". It is, indeed, the standard for exchanging formatted documents on the internet. PDF documents are read by Adobe Acrobat Reader, most browsers, and even some popular word processors like Microsoft Word. It is a common used-case to generate PDF documents from live data. In this tutorial, I shall show you how you can easily generate a PDF report in an ASP.NET Core MVC app and display data that originates from the sample SQL Server database named Northwind. We shall use the iText 7 library to accomplish this task. 

Source Code: https://github.com/medhatelmasry/MvcPdfDemo

The environment I am using is:

• Windows 11
• Docker Desktop for Windows
• .NET version 6.0.100
• Visual Studio Code

Start Northwind database in a Docker container

To pull & run the Northwind database in a Docker container, run the following command in a terminal window:

docker run -d --name nw -p 1444:1433 kcornwall/sqlnorthwind

The above command does the following:

Docker image:	kcornwall/sqlnorthwind
Container Name (--name):	nw
Ports (-p):	Port 1433 in container is exposed as port 1444 on the host computer
Password:	The sa password is Passw0rd2018. This was determined from the Docker Hub page for the image.
-d:	Starts the container in detached mode

This is what I experienced after I ran the above command:

Let us make sure that the container is running. Execute this command to ensure that the container is indeed running.

docker ps

The following confirmed to me that the container is running:

Project setup

Run the following command to create an ASP.NET Core MVC application using .NET 6.0 in a folder named MvcPdfDemo:

dotnet new mvc -f net6.0 -o MvcPdfDemo

Change directory into the new folder and open the project inside VS Code with the following commands:

cd MvcPdfDemo

code .

We will need to install an Entity Framework command-line utility. If you have not done so already, install dotnet-ef with this command:

dotnet tool install -g dotnet-ef 

It does not hurt to upgrade this tool to the latest version with:

dotnet tool update -g dotnet-ef

Also, from within the root folder of your project, add some SQL-Server and Entity Framework related packages with the following terminal-window commands:

dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Tools

Also, add the iText 7 Core package so that we have the ability to generate PDF output.

dotnet add package itext7 --version 7.2.0

In appsettings.json, add this to ConnectionStrings block just before “Logging”:

"ConnectionStrings": {

    "NW": "Data Source=localhost,1444;Initial Catalog=Northwind;Persist Security Info=True;User ID=sa;Password=Passw0rd2018"

},

Next, let us reverse engineer the Products entity in the Northwind database. Execute this command from the root of your project:

dotnet-ef dbcontext scaffold "Data Source=localhost,1444;Initial Catalog=Northwind;Persist Security Info=True;User ID=sa;Password=Passw0rd2018" Microsoft.EntityFrameworkCore.SqlServer -c NorthwindContext -o Models/NW --table Products

This creates a Models/NW folder in your project with entity Product. It also adds the database context class NorthwindContext.

Delete the OnConfiguring() method in NorthwindContext.cs so that there is no hard-coded connection string in our C# code.

Add the following code to Program.cs right after where the variable builder is declared around line 2:

var connectionString = builder.Configuration.GetConnectionString("NW");

builder.Services.AddDbContext<NorthwindContext>(options => {

  options.UseSqlServer(connectionString);

});

Generating PDF report

Let's take advantage of dependency injection to access the database through an instance of the NorthwindContext. Add the following instance variable declaration to the top of the HomeController class:

private readonly NorthwindContext _northwindContext;

Update the HomeController constructor so it looks like this:

public HomeController(

  ILogger<HomeController> logger, 

  NorthwindContext northwindContext) {

  _logger = logger;

  _northwindContext = northwindContext;

}

We will next add an action method to the HomeController that will generate and download a PDF report. Add the following helper method that returns a table:

private Table GetPdfTable() {

    // Table

    Table table = new Table(4, false);

    // Headings

    Cell cellProductId = new Cell(1, 1)

       .SetBackgroundColor(ColorConstants.LIGHT_GRAY)

       .SetTextAlignment(TextAlignment.CENTER)

       .Add(new Paragraph("Product ID"));

    Cell cellProductName = new Cell(1, 1)

       .SetBackgroundColor(ColorConstants.LIGHT_GRAY)

       .SetTextAlignment(TextAlignment.LEFT)

       .Add(new Paragraph("Product Name"));

    Cell cellQuantity = new Cell(1, 1)

       .SetBackgroundColor(ColorConstants.LIGHT_GRAY)

       .SetTextAlignment(TextAlignment.CENTER)

       .Add(new Paragraph("Quantity"));

    Cell cellUnitPrice = new Cell(1, 1)

       .SetBackgroundColor(ColorConstants.LIGHT_GRAY)

       .SetTextAlignment(TextAlignment.CENTER)

       .Add(new Paragraph("Unit Price"));

    table.AddCell(cellProductId);

    table.AddCell(cellProductName);

    table.AddCell(cellQuantity);

    table.AddCell(cellUnitPrice);

    var qry = _northwindContext.Products

        .Select(_ => new {

            _.ProductId,

            _.ProductName,

            _.UnitPrice,

            _.UnitsInStock

        });

    foreach (var item in qry) {

        Cell cId = new Cell(1, 1)

            .SetTextAlignment(TextAlignment.CENTER)

            .Add(new Paragraph(item.ProductId.ToString()));

        Cell cName = new Cell(1, 1)

            .SetTextAlignment(TextAlignment.LEFT)

            .Add(new Paragraph(item.ProductName));

        Cell cQty = new Cell(1, 1)

            .SetTextAlignment(TextAlignment.RIGHT)

            .Add(new Paragraph(item.UnitsInStock.ToString()));

        Cell cPrice = new Cell(1, 1)

            .SetTextAlignment(TextAlignment.RIGHT)

            .Add(new Paragraph(String.Format("{0:C2}", item.UnitPrice)));

        table.AddCell(cId);

        table.AddCell(cName);

        table.AddCell(cQty);

        table.AddCell(cPrice);

    }

    return table;

}

 

What does the above code do?

1. The heading of the table is created. There will be four columns with titles: Product ID, Product Name, Quantity and Unit Price
2. Using Entity Framework, we make a query that reads all the products from the Northwind database.
3. We iterate through each item in the query and add rows of data to the table

Our next task is to add an Action method that uses the above GetPdfTable() helper method and returns a PDF document. Add the following Report() action method:

public IActionResult Report() {

  MemoryStream ms = new MemoryStream();

  PdfWriter writer = new PdfWriter(ms);

  PdfDocument pdfDoc = new PdfDocument(writer);

  Document document = new Document(pdfDoc, PageSize.A4, false);

  writer.SetCloseStream(false);

  Paragraph header = new Paragraph("Northwind Products")

    .SetTextAlignment(TextAlignment.CENTER)

    .SetFontSize(20);

  document.Add(header);

  Paragraph subheader = new Paragraph(DateTime.Now.ToShortDateString())

    .SetTextAlignment(TextAlignment.CENTER)

    .SetFontSize(15);

  document.Add(subheader);

  // empty line

  document.Add(new Paragraph(""));

  // Line separator

  LineSeparator ls = new LineSeparator(new SolidLine());

  document.Add(ls);

  // empty line

  document.Add(new Paragraph(""));

  // Add table containing data

  document.Add(GetPdfTable());

  // Page Numbers

  int n = pdfDoc.GetNumberOfPages();

  for (int i = 1; i <= n; i++) {

    document.ShowTextAligned(new Paragraph(String

      .Format("Page " + i + " of " + n)),

      559, 806, i, TextAlignment.RIGHT,

      VerticalAlignment.TOP, 0);

  }

  document.Close();

  byte[] byteInfo = ms.ToArray();

  ms.Write(byteInfo, 0, byteInfo.Length);

  ms.Position = 0;

  FileStreamResult fileStreamResult = new FileStreamResult(ms, "application/pdf");

  //Uncomment this to return the file as a download

  //fileStreamResult.FileDownloadName = "NorthwindProducts.pdf";

  return fileStreamResult;

}

What does the above code do?

1. The first five lines in the Report() action method sets up all the objects that are needed to generate a PDF document.
2. A header with title "Northwind Products" is placed at the top of the report - center aligned.
3. A sub-header with the current date is placed under the heading - also center aligned.
4. This is followed by an empty line, a solid-line, and another empty line.
5. The table containing product data is then displayed.
6. Paging is added to the top right-side of each page
7. Finally, the report is streamed down to the browser.

Let us add a menu item to the navigation of our web app. Open Views/Shared/_Layout.cshtml and add the following markup code to the bottom of the <ul> .... </ul> navigation block:

<li class="nav-item">
  <a class="nav-link text-dark" asp-area="" asp-controller="Home" asp-action="Report">Products PDF</a>
</li>

At this stage, let's run our web app and verify that we are indeed able to read data from the Northwind database and subsequently generate a PDF report. Run your application with:

dotnet watch run

Point your browser to https://localhost:7052

NOTE: you will need to adjust the port number to suit your environment.

This is what the home page looks like:

Click on "Products PDF". You should soon after see the PDF document being generated in your browser:

You can click on the download icon on the top right-side of the report in order to download a copy of the report. 

Alternatively, you can uncomment the following code if you want the report to get directly downloaded to your computer:

// fileStreamResult.FileDownloadName = "NorthwindProducts.pdf";

If you run the app again and click on the "Products PDF" link, the report named "NorthwindProducts.pdf" gets immediately downloaded to your computer.

Conclusion

I found the iText 7 library pretty straight forward when it comes to generating PDF documents. You can learn more at https://kb.itextpdf.com/home/it7kb/ebooks/itext-7-jump-start-tutorial-for-net.

I hope this provides you with a respectable starting point for generating PDF reports from your ASP.NET MVC web apps.