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.


2 Responses to “Write data from a database to PDF”

  • Andrew says:

    Hi Docotic Team,

    This is great, and definitely what I am looking for, but I was wondering whether you support Groups and Repeating Fields – see this document (just about half way down) for the section I am referring to – http://docs.oracle.com/cd/E10091_01/doc/bip.1013/b40017/T421739T481159.htm.

    I need to add multiple textboxes to a PDF form alongside an image and repeat that for however many images there are in our database.

    Any guidance you can provide would be much appreciated.

  • Shibaev says:

    Hello Andrew,

    There is no built-in support for Groups and Repeating Fields. Note that this feature is not a part of PDF specification, so PDF software (like BI Publisher) usually smulate this somehow.

    You can also create groups of controls using Docotic.Pdf – just add textboxes with images in the loop and be careful with textbox names (different textboxes should have different names). You need to layout controls manually, but it will give you a full control :-)

    You can provide us more detail of your task in support. We will be glad to help you with this.

Leave a Reply