Write database data to pdf file

A PDF (Portable Document Format) file is a type of file that carries the .pdf file extension. In modern times, PDF files have become increasingly prevalent and are commonly employed for important documents, including tax papers, bank statements, and various other forms of documents that necessitate user data input. One frequent query revolves around the process of extracting data from a database using SQL statements and subsequently transferring that data to a PDF document.

Database to pdf file

To accomplish this task, several steps need to be followed. Firstly, establish a connection to the database using appropriate credentials and ensure that the required SQL statements are formulated to retrieve the desired data from the database tables. Execute these SQL statements and obtain the resulting dataset or data reader object.

Next, employ a suitable PDF generation library or framework in your C# application. Examples of such libraries include iTextSharp, PdfSharp, or SelectPdf. These libraries offer functionalities to create, modify, and manipulate PDF documents programmatically.

Once you have obtained the data from the database and selected a PDF library, iterate over the retrieved dataset or data reader object. Extract the required information from each row and column and use the PDF library's methods to generate the corresponding PDF document. This involves formatting the retrieved data and placing it in appropriate sections of the PDF document, such as tables, paragraphs, or fields.

PDFsharp is the Open Source library for creating and modifying Adobe PDF documents programmatically.

You can freely download the Assemblies version from the following link: Download PDFsharp Assemblies

After download the zip file, extract it and add the reference to your c# project.

pdf assembly files

If you want to know the step by step tutorial on how to create your first pdf file programmatically, follow the link : How to create PDF file programmatically

Write sql result to pdf file

Here we are using Pdfsharp library to generate the PDF document, before that we have to read the data from Database. So we create connection string and sql for doing this.

connetionString = "Data Source=YourServerName;Initial Catalog=pubs;User ID=sa;Password=zen412"; sql = "select pub_name,city,country from publishers"; connection = new SqlConnection(connetionString);

Write data from Dataset to pdf file

Retrieve the data and store it n a Dataset.

adapter.Fill(ds);

The next step is to create and initialize the PDFSharp Object to create the pdf document.

PdfDocument pdf = new PdfDocument(); PdfPage pdfPage = pdf.AddPage(); XGraphics graph = XGraphics.FromPdfPage(pdfPage); XFont font = new XFont("Verdana", 20, XFontStyle.Regular );

Finally we retrieve each row from the dataset and write to the pdf file.

graph.DrawString(pubname, font, XBrushes.Black, new XRect(40, yPoint, pdfPage.Width.Point, pdfPage.Height.Point), XStringFormats.TopLeft);

output sample display

Sql to PDF

During the PDF generation process, you can customize the appearance and layout of the document, including font styles, colors, and page structure. You may also incorporate additional elements such as images, headers, footers, or watermarks as per your requirements.

Once the PDF document is generated with the desired data, it can be saved to a specific location on the file system or streamed directly to the user for download.

The following C# source code shows how to retrieve the data from database and write to the pdf file.

Full Source C#
using System; using System.Windows.Forms; using System.Diagnostics; using PdfSharp; using PdfSharp.Drawing; using PdfSharp.Pdf; using System.Data.SqlClient; using System.Data; namespace WindowsFormsApplication1 { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void button1_Click(object sender, EventArgs e) { try { string connetionString = null; SqlConnection connection ; SqlCommand command ; SqlDataAdapter adapter = new SqlDataAdapter(); DataSet ds = new DataSet(); int i = 0; string sql = null; int yPoint = 0; string pubname = null; string city = null; string state = null; connetionString = "Data Source=YourServerName;Initial Catalog=pubs;User ID=sa;Password=zen412"; sql = "select pub_name,city,country from publishers"; connection = new SqlConnection(connetionString); connection.Open(); command = new SqlCommand(sql, connection); adapter.SelectCommand = command; adapter.Fill(ds); connection.Close(); PdfDocument pdf = new PdfDocument(); pdf.Info.Title = "Database to PDF"; PdfPage pdfPage = pdf.AddPage(); XGraphics graph = XGraphics.FromPdfPage(pdfPage); XFont font = new XFont("Verdana", 20, XFontStyle.Regular ); yPoint = yPoint + 100; for (i = 0; i < = ds.Tables[0].Rows.Count - 1; i++) { pubname = ds.Tables[0].Rows[i].ItemArray[0].ToString (); city = ds.Tables[0].Rows[i].ItemArray[1].ToString(); state = ds.Tables[0].Rows[i].ItemArray[2].ToString(); graph.DrawString(pubname, font, XBrushes.Black, new XRect(40, yPoint, pdfPage.Width.Point, pdfPage.Height.Point), XStringFormats.TopLeft); graph.DrawString(city, font, XBrushes.Black, new XRect(280, yPoint, pdfPage.Width.Point, pdfPage.Height.Point), XStringFormats.TopLeft); graph.DrawString(state, font, XBrushes.Black, new XRect(420, yPoint, pdfPage.Width.Point, pdfPage.Height.Point), XStringFormats.TopLeft); yPoint = yPoint + 40; } string pdfFilename = "dbtopdf.pdf"; pdf.Save(pdfFilename); Process.Start(pdfFilename); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } } }

Conclusion

Reading data from a database using SQL statements and writing that data to a PDF document entails establishing a database connection, retrieving the data using SQL queries, selecting a suitable PDF generation library, and using it to create the PDF document with the extracted data. This process enables the transformation of database information into a convenient and widely accessible PDF format.