How to Export Data from Database to PDF Document using Asp.net Core MVC C#

In Any Web API project and Web Application, the most common requirement is to generate a report in PDF format. In this article, we will learn, How to Export Data from a Database to PDF Document using Asp.net Core MVC C#.

We are going to use the DinkToPDF library. In this library, we can easily generate PDF documents while working on the .Net Core Web API and Web Application.

Previous Article Create Dynamic Menu

Table of Contents

Prerequisites

Download and install .Net Core 6.0 SDK from here
Download and Install Microsoft Visual Studio 2022 from here
Sql-Server

Generate PDF From Database

Create an Asp.Net Core Web App

Open Visual Studio and click the Create New Project Option
Select the Template

Export Data into PDF Document using Asp.net Core MVC C#

Enter the name of the Application

Install NuGet Packages

Microsoft Entity Framework Core Sql Server.
Microsoft Entity Framework Core Tools
DinkToPdf Library

After Installation, we need to import the native library file to the project root Path.

Configure appsettings.json

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "ConnectionStrings": {
    "sqlconnection": "Server=(local)\\MSSQL;Database=Inventory;Trusted_Connection=True;MultipleActiveResultSets=true",
    

  },
  "AllowedHosts": "*"
}

Create Model

Create Model class and DbContext Class

Customer.cs

using System.ComponentModel.DataAnnotations;
namespace Asp.netcore_Tutorials.Models
{
    public class Customer
    {
        [Key]
        public int id { get; set; }
        public string firstName { get; set; }
        public string lastName { get; set; }
        public string job { get; set; }
        public float amount { get; set; }
        public DateTime tdate { get; set; }
    }
}

CustDbcontext.cs

using Microsoft.EntityFrameworkCore;

namespace Asp.netcore_Tutorials.Models
{
    public class CustDbcontext :DbContext 
    {

        public CustDbcontext(DbContextOptions<CustDbcontext> options):base(options)
        {
        }
        public virtual DbSet<Customer> Customers { get; set; } 
    }
}

Create Interface and Concrete Class

ICustomer.cs

using Asp.netcore_Tutorials.Models;
using Microsoft.AspNetCore.Mvc.Rendering;
using System.Collections;
using System.Data;

namespace Asp.netcore_Tutorials.Repository
{
    public interface ICustomer
    { 
        Task<IEnumerable<Customer>> GetCustomer(); 
    } 
}

Concrete Class CustomerDetail.cs

using Microsoft.AspNetCore.Http;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
using ClosedXML.Excel;
using Microsoft.AspNetCore.Mvc;
using System.Collections;
using Asp.netcore_Tutorials.Models;
using Microsoft.EntityFrameworkCore;
using DocumentFormat.OpenXml.Wordprocessing;
using Microsoft.AspNetCore.Mvc.Rendering;

namespace Asp.netcore_Tutorials.Repository
{
    public class CustomerDetail : ICustomer
    {       
        private readonly CustDbcontext _custDbcontext;
        public CustomerDetail(CustDbcontext custDbcontext)
        {   
            _custDbcontext = custDbcontext;
        }      

        public async Task<IEnumerable<Customer>> GetCustomer()
        {
            return  await _custDbcontext.Customers.ToListAsync();
        } 
    }
}

Configure Program.cs

using Asp.netcore_Tutorials.Models;
using Asp.netcore_Tutorials.Repository;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using System.Configuration;
using Microsoft.AspNetCore.Session;
using DinkToPdf;
using DinkToPdf.Contracts;

var builder = WebApplication.CreateBuilder(args);

// Add services to the container. 
builder.Services.AddDbContext<CustDbcontext>(conn => conn.UseSqlServer(builder.Configuration.GetConnectionString("sqlconnection"))); 

builder.Services.AddSingleton(typeof(IConverter), new SynchronizedConverter(new PdfTools()));

builder.Services.AddControllersWithViews();

builder.Services.AddSession();

var app = builder.Build();

// Configure the HTTP request pipeline.
if (!app.Environment.IsDevelopment())
{
    app.UseExceptionHandler("/Home/Error");
    // The default HSTS value is 30 days. You may want to change this for production scenarios, see https://aka.ms/aspnetcore-hsts.
    app.UseHsts();
}

app.UseHttpsRedirection();

app.UseStaticFiles();

app.UseRouting();

app.UseAuthorization();

app.UseSession();

app.MapControllerRoute(
    name: "default",
    pattern: "{controller=Home}/{action=Index}/{id?}");

app.Run();

Migration

Add-Migration 'Initial-Create'
Update-Database

Create View Model

using Asp.netcore_Tutorials.Models;

namespace Asp.netcore_Tutorials.ViewModel
{
    public class menuViewModel
    { 
        public IEnumerable<Customer> CustomerList { get; set; }
    }
}

Create Controller

ExportToPDFController.cs

using Asp.netcore_Tutorials.Repository;
using Asp.netcore_Tutorials.ViewModel;
using DinkToPdf;
using DinkToPdf.Contracts;
using DocumentFormat.OpenXml.ExtendedProperties;
using Microsoft.AspNetCore.Mvc;
using System.Text;

namespace Asp.netcore_Tutorials.Controllers
{
    public class ExportToPDFController : Controller
    {
        private readonly ICustomer _customer;
        private IWebHostEnvironment _environment;
        private readonly IConverter _converter;
        public ExportToPDFController(ICustomer customer, IWebHostEnvironment environment,IConverter converter)
        {
            _customer = customer;
            _environment = environment;
            _converter = converter;
        }

        public IActionResult Index()
        {
            menuViewModel menuViewModel = new menuViewModel();

            menuViewModel.CustomerList = _customer.GetCustomer().Result;

            return View(menuViewModel);
        }

        [HttpPost]
        [AutoValidateAntiforgeryToken]
        public IActionResult ExporttoPDF()
        {
            string uploadpath = _environment.WebRootPath;
            string dest_path = Path.Combine(uploadpath, "PDF");

            if (!Directory.Exists(dest_path))
            {

                Directory.CreateDirectory(dest_path);
            }           

            var globalSettings = new GlobalSettings
            {
                Orientation = Orientation.Portrait,
                PaperSize = PaperKind.A4,
                ColorMode = ColorMode.Color,
                Margins = new MarginSettings { Top = 20, Bottom = 10 },
                DocumentTitle = "Export to PDF",
                Out  = dest_path + @"\Document.pdf"
            };            
            var objects = new ObjectSettings()
            {
                HtmlContent = GetTablecontextHtml()
            };

            var PdfDoc = new HtmlToPdfDocument
            {
                GlobalSettings= globalSettings,
                Objects = { objects }

            };

            _converter.Convert(PdfDoc);

            return View();
        }

        public  string GetTablecontextHtml()
        {
            var customer = _customer.GetCustomer().Result;

            var customerdata = new StringBuilder();

            customerdata.Append(@"<html>
                                  <head>
                                    </head>
                                  <body>
                                    <label>Custmer Details</label>                                 
                                 <table class='table-striped'>
                                        <thead>
                                            <tr>
                                            <th>Id</th>
                                            <th>Name</th>
                                            <th>Job</th>
                                            <td>Salary</td>
                                            </tr>

                                        </thead>");
                                      
                                    foreach (var item in customer)
                                    {
                                        customerdata.AppendFormat(@"<tr>
                                            <td>{0}</td>
                                            <td>{1}</td>
                                            <td>{2}</td>
                                            <td>{3}</td> 
                                        </tr>",item.id,item.firstName,item.job,item.amount); 
                                       }
                customerdata.Append(@"  
                                    </table> 
                                      </body>                                
                                      </html>");

            return customerdata.ToString();
        }
    }
}

Customize View

 
@model  Asp.netcore_Tutorials.ViewModel.menuViewModel

@{
}
 
    <div class="row">   
    <form method="post" enctype="multipart/form-data" asp-controller="ExportToPDF" asp-action="ExportToPDF">
    <table class="table table-dark">
        <thead>
            <tr>
            <th>Id</th>
            <th>Name</th>
            <th>Designation</th>
            <td>Salary</td>
            </tr>
        </thead>
        <tbody

        @foreach (var item in Model.CustomerList)
        {
            <tr>
                <td>@item.id</td>
                <td>@item.firstName</td>
                <td>@item.job</td>
                <td>@item.amount</td>

            </tr>
        }
        </tbody>
     </table>
        <div class="form-group">
            <div class="col-md-10">
                <input class="btn btn-primary" type="submit" value="Export To PDF" />
            </div>
        </div>
    </form>
</div>