Net-informations.com
SiteMap  | About    

Write database to PDF file

pdf - Portable Document Format

A file with .pdf file extension is called Portable Document Format(PDF) file. Nowadays businesses and government agencies around the world use Portable Document Format(PDF) as a standard format for exchanging documents more securely. PDF File format allows us a page-by-page view of documents, exactly as they appear in their printed form. There is a usual question that how to read data from a database using sql statements and write that data to a PDF document.

Database to PDF

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

pdf assembly files

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.

Sql to PDF

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




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






net-informations.com (C) 2017    Founded by raps mk
All Rights Reserved. All other trademarks are property of their respective owners.