Write database to PDF file

By: Rajesh P.S.

The Portable Document Format (PDF) is an extensively employed file format, denoted by the .pdf file extension, that has gained widespread recognition in contemporary times. Across the globe, businesses and government agencies have embraced PDF as a prevailing standard for secure document exchange. By using PDF, organizations can transmit sensitive information with enhanced security measures in place.

The inherent advantages of the PDF file format lie in its ability to present documents in a page-by-page view, faithfully reproducing the content in its original printed form. This characteristic ensures that recipients can access and review documents with the same level of accuracy and visual fidelity as the original hardcopy.

Database to PDF file

An often-posed query pertains to the process of extracting data from a database using SQL statements and subsequently transforming that data into a PDF document. This task requires a systematic approach, involving the retrieval of data from the database using SQL queries and the subsequent utilization of appropriate tools or libraries to convert the extracted information into a PDF format.

PDFsharp is an open source library that allows developers to create and manipulate PDF documents. PDFsharp enables developers looking to enhance their applications with dynamic PDF document creation and manipulation.

You can download .Net PDFsharp library from the following link: Download PDFsharp Assemblies

After download the zip file, extract it and add the reference to your vb.net project.

If you are not familiar with how to add reference and create pdf file, just follow the link : How to create PDF file programmatically

Write sql result to pdf file

Before creating PDF file we should read the data from database using sql query string. So we should create and initialize a connection string and sql statement.

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

Write data from Dataset to pdf file

After make the connection, select the data from database using sql query and store it in a Dataset.

adapter.Fill(ds)

Now we have data from the database, next step is to create and initialize Pdfsharp object to crate a new pdf file for writing the retrieved data.

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

Now you can write the data to the pdf file.

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

After write the data to the pdf file you should save the file with .pdf extension.

pdf.Save("yourpdffilename.pdf")

Now you can open the newly created pdf file, the output is look like the following picture.

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

Full Source VB.NET

Imports System.Data.SqlClient Imports PdfSharp Imports PdfSharp.Drawing Imports PdfSharp.Pdf Public Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Try Dim connetionString As String Dim connection As SqlConnection Dim command As SqlCommand Dim adapter As New SqlDataAdapter Dim ds As New DataSet Dim i As Integer Dim sql As String Dim yPoint As Integer Dim pubname As String Dim city As String Dim state As String 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() Dim pdf As PdfDocument = New PdfDocument pdf.Info.Title = "Database to PDF" Dim pdfPage As PdfPage = pdf.AddPage Dim graph As XGraphics = XGraphics.FromPdfPage(pdfPage) Dim font As XFont = New XFont("Verdana", 20, XFontStyle.Regular) yPoint = yPoint + 100 For i = 0 To ds.Tables(0).Rows.Count - 1 pubname = ds.Tables(0).Rows(i).Item(0) city = ds.Tables(0).Rows(i).Item(1) state = ds.Tables(0).Rows(i).Item(2) 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 Next Dim pdfFilename As String = "dbtopdf.pdf" pdf.Save(pdfFilename) Process.Start(pdfFilename) Catch ex As Exception MsgBox(ex.ToString) End Try End Sub End Class

Conclusion

The PDF file format has emerged as a widely embraced standard for secure document exchange, utilized by businesses and government agencies globally. Its unique ability to replicate printed content faithfully facilitates accurate document representation. When confronted with the task of retrieving data from a database via SQL statements and subsequently transforming it into a PDF document, a systematic approach involving SQL query execution and the utilization of appropriate tools or libraries enables developers to accomplish this objective successfully.

Next > VB.Net StreamReader

Related Topics

More Related Topics.....

Related Links