Add Data from Database to PDF in C#

Databases are almost everywhere to store and manage the data. It is a common practice of the programmers to retrieve the data from the databases and load it into the applications. When generating PDF files programmatically, one may need to populate the document with the data in database. To accomplish that in .NET applications, this article shows how to add data from database to PDF files in C#.

C# .NET API to Add Data from Database to PDF

We will use Aspose.PDF for .NET to add data from database to PDF files. It is a popular PDF generation and manipulation API that allows you to create PDF files of simple and complex layouts seamlessly. You can either download the API’s binaries or install it using NuGet.

PM> Install-Package Aspose.PDF

Add Data from Database to PDF in C#

In most of the cases, the data is fetched from a database table into a DataTable or DataView. Therefore, for demonstration, we will use a DataTable to add data to a PDF file. To keep the things simpler, we will create and populate the DataTable programmatically without using a database. The following are the steps to add data to a PDF file from database in C#.

The following code sample shows how to import data from database to PDF in C#.

// Get data from database to DataTable
DataTable dt = new DataTable("Employee");
dt.Columns.Add("Employee_ID", typeof(Int32));
dt.Columns.Add("Employee_Name", typeof(string));
dt.Columns.Add("Gender", typeof(string));
// Add 2 rows into the DataTable object programmatically
DataRow dr = dt.NewRow();
dr[0] = 1;
dr[1] = "John Smith";
dr[2] = "Male";
dt.Rows.Add(dr);
dr = dt.NewRow();
dr[0] = 2;
dr[1] = "Mary Miller";
dr[2] = "Female";
dt.Rows.Add(dr);
// Create Document instance
Document doc = new Document();
doc.Pages.Add();
// Initializes a new instance of the Table
Aspose.Pdf.Table table = new Aspose.Pdf.Table();
// Set column widths of the table
table.ColumnWidths = "40 100 100 100";
// Set the table border color as LightGray
table.Border = new Aspose.Pdf.BorderInfo(Aspose.Pdf.BorderSide.All, .5f, Aspose.Pdf.Color.FromRgb(System.Drawing.Color.LightGray));
// Set the border for table cells
table.DefaultCellBorder = new Aspose.Pdf.BorderInfo(Aspose.Pdf.BorderSide.All, .5f, Aspose.Pdf.Color.FromRgb(System.Drawing.Color.LightGray));
// Import data
table.ImportDataTable(dt, true, 0, 1, 3, 3);
// Add table object to first page of input document
doc.Pages[1].Paragraphs.Add(table);
// Save updated document containing table object
doc.Save("output.pdf");

The following is the output of the above code sample.

Add Data from Database to PDF in C#

Add Data from Database to PDF in Entity Framework

These days, Entity Framework (EF) is being commonly used by the developers. Therefore, it would be handy to extend the Table class to populate PDF documents with lists or grouped data in EF. The following is the implementation of how to populate a PDF table using a list and grouped data. In both methods, the Table and data is passed as arguments of the method.

public static class PdfHelper
{
public static void ImportEntityList<TSource>(this Pdf.Table table, IList<TSource> data)
{
var headRow = table.Rows.Add();
var props = typeof(TSource).GetProperties(BindingFlags.Public | BindingFlags.Instance);
foreach (var prop in props)
{
headRow.Cells.Add(prop.GetCustomAttribute(typeof(DisplayAttribute)) is DisplayAttribute dd ? dd.Name : prop.Name);
}
foreach (var item in data)
{
// Add row to table
var row = table.Rows.Add();
// Add table cells
foreach (var t in props)
{
var dataItem = t.GetValue(item, null);
if (t.GetCustomAttribute(typeof(DataTypeAttribute)) is DataTypeAttribute dataType)
switch (dataType.DataType)
{
case DataType.Currency:
row.Cells.Add(string.Format("{0:C}", dataItem));
break;
case DataType.Date:
var dateTime = (DateTime)dataItem;
if (t.GetCustomAttribute(typeof(DisplayFormatAttribute)) is DisplayFormatAttribute df)
{
row.Cells.Add(string.IsNullOrEmpty(df.DataFormatString)
? dateTime.ToShortDateString()
: string.Format(df.DataFormatString, dateTime));
}
break;
default:
row.Cells.Add(dataItem.ToString());
break;
}
else
{
row.Cells.Add(dataItem.ToString());
}
}
}
}
public static void ImportGroupedData<TKey,TValue>(this Pdf.Table table, IEnumerable<Models.GroupViewModel<TKey, TValue>> groupedData)
{
var headRow = table.Rows.Add();
var props = typeof(TValue).GetProperties(BindingFlags.Public | BindingFlags.Instance);
foreach (var prop in props)
{
headRow.Cells.Add(prop.GetCustomAttribute(typeof(DisplayAttribute)) is DisplayAttribute dd ? dd.Name : prop.Name);
}
foreach (var group in groupedData)
{
// Add group row to table
var row = table.Rows.Add();
var cell = row.Cells.Add(group.Key.ToString());
cell.ColSpan = props.Length;
cell.BackgroundColor = Pdf.Color.DarkGray;
cell.DefaultCellTextState.ForegroundColor = Pdf.Color.White;
foreach (var item in group.Values)
{
// Add data row to table
var dataRow = table.Rows.Add();
// Add cells
foreach (var t in props)
{
var dataItem = t.GetValue(item, null);
if (t.GetCustomAttribute(typeof(DataTypeAttribute)) is DataTypeAttribute dataType)
switch (dataType.DataType)
{
case DataType.Currency:
dataRow.Cells.Add(string.Format("{0:C}", dataItem));
break;
case DataType.Date:
var dateTime = (DateTime)dataItem;
if (t.GetCustomAttribute(typeof(DisplayFormatAttribute)) is DisplayFormatAttribute df)
{
dataRow.Cells.Add(string.IsNullOrEmpty(df.DataFormatString)
? dateTime.ToShortDateString()
: string.Format(df.DataFormatString, dateTime));
}
break;
default:
dataRow.Cells.Add(dataItem.ToString());
break;
}
else
{
dataRow.Cells.Add(dataItem.ToString());
}
}
}
}
}
}
public class GroupViewModel<K,T>
{
public K Key;
public IEnumerable<T> Values;
}

Get a Free License

You can get a free temporary license in order to use Aspose.PDF for .NET without evaluation limitations.

Conclusion

In this article, you have learned how to add data from database into PDF files in C#. You have seen how to import data from a DataTable into a table in PDF file. In addition, we have covered the implementation of importing data into PDF using Entity Framework. Besides, you can explore more about the C# PDF API using the documentation. In case you would have any questions or queries, you can contact us via our forum.

See Also