![Add Data from Database to PDF in C#](images/Create-Tables-in-PDF.jpg#center)
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#.
- .NET API to Add Data from Database to PDF
- Add Data from Database to PDF
- Add Data from Database to PDF in Entity Framework
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#.
- Load the data into a DataTable from database.
- Create a new PDF or load existing one using Document class.
- Create an instance of Table class and set its properties, i.e. column width, borders, etc.
- Import data from database to PDF table using Table.ImportDataTable() method.
- Add the table to the page using **Document.Pages[index].Paragraphs.Add(Table)** method.
- Save the PDF file using Document.Save(string) method.
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#](images/Import-Data-from-Database-to-PDF.png#center)
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.