Write data from a database to PDF

Hello!

Our clients often ask us how to read some data from a database and then write that data to a PDF document. Common use case is to get people names and a PDF template and create personalized documents for clients.

Let’s look how to do this with help of Docotic.Pdf. The demo application from this article will read names from database and then modify template PDF by putting the names on to the first page.

There are two obvious parts in the application. The first one is to read data from a database and the second one is to open PDF template and write data from the first step into it.

For the demo application I prepared simple Microsoft Access Database stored in clients.accdb file. The database consist of a single table “clients” with just two columns – “id” and “name”. The table is filled with some fake data:

Reading client names from database is pretty simple:

private static IEnumerable getClientsFromDatabase()
{
    using (IDbConnection connection = openDbConnection())
    {
        using (IDbCommand command = connection.CreateCommand())
        {
            command.CommandText = "SELECT name FROM clients;";
            using (IDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    yield return reader.GetString(0);
                }
            }
        }
    }
}

private static IDbConnection openDbConnection()
{
    IDbConnection connection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=clients.accdb");
    connection.Open();

    return connection;
}

Which database to use is really doesn’t matter; you can connect to any database you want. To connect to another database just change the code in openDbConnection method. In my case MS Access 2010 is required to be installed because Microsoft.ACE.OLEDB.12.0 provider is used.

Now let’s modify a PDF template with the data from database. For a PDF template I took first eligible PDF found on Google. It is not advertising, I promise 🙂

I put this PDF template to resources of the demo application. Following is a complete code for producing personalized PDF documents using names from the database and the template.

using BitMiracle.Docotic.Pdf;
...
using (PdfDocument pdf = new PdfDocument(Resources.PdfTemplate))
{
    PdfTextBox textBox = pdf.Pages[0].AddTextBox("clientname", new PdfPoint(20, 50), new PdfSize(200, 40));
    textBox.HasBorder = false;
    textBox.ReadOnly = true;
    textBox.FontSize = 20;

    foreach (string clientName in getClientsFromDatabase())
    {
        textBox.Text = clientName;

        pdf.Save(clientName + ".pdf");
    }
}

First, I add a read-only text box without border to the template document. Then in a loop I put next client name into the box and save document using client name as the output file name.

When run, this code will create a PDF file for every client name stored in the database. In my case the code will produce “Alexander Petrov.pdf”, “John Smith.pdf” and “Mike Adams.pdf”. For example, “John Smith.pdf” will look like on the picture below.

That’s it!

There are also other approaches to the task. A client name can be used to personalize template with a watermark. Or name can be just drawn on PDF page canvas.

If your PDF template already contains a field where client name or other data is supposed to be stored then the field should be found and its text changed.

Somewhat similar use case our clients ask us about is to read form data stored in a database and fill template PDF form with the data. I’ll try to cover this case in one of the next articles.

You can download full sources of the sample here.


Posted in