In this article I will explain how to generate PDF reports from database in ASP.Net using iTextSharp Free PDF Generator Library
More articles on PDF using iTextSharp
 
How to create and download PDF Report from database in ASP.Net using iTextSharp C# and VB.Net


Database
For this article I am making use of the Employees table of the Microsoft’s Northwind sample database which can be downloaded from here
Download Northwind Database
 
HTML Markup
Below is the HTML markup where I have an ASP.Net DropDownList which I’ll fill with the records of the employees from the Employees table and a Button which when clicked will generate the PDF report for the selected Employee.
<asp:DropDownList ID="ddlEmployees" runat="server">
</asp:DropDownList>
<asp:Button ID="btnReport" runat="server" Text="Generate Report" OnClick = "GenerateReport" />
 
Namespaces
You will need to import the following namespaces
C#
using iTextSharp.text;
using iTextSharp.text.pdf;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
 
VB.Net
Imports iTextSharp.text
Imports iTextSharp.text.pdf
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
 
Database Connection String
I have set the following connection string in the Connection Strings section of the Web.Config file
<connectionStrings>
 <addname="constr"connectionString="Data Source=.\SQL2005;Initial Catalog=northwind;User id = sa;password=pass@123"/>
</connectionStrings>
 
 
Populating the Employees DropDownList
Below is the code to bind the Employees DropDownList in the Page Load event of the ASP.Net Web Page.
Note:GetData is a Generic function to execute SELECT queries and returns DataTable

C#
protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        BindEmployeesDropDown();
    }
}
private void BindEmployeesDropDown()
{
    ddlEmployees.DataSource = GetData("SELECT EmployeeId, (FirstName + ' ' + LastName) Name FROM Employees");
    ddlEmployees.DataTextField = "Name";
    ddlEmployees.DataValueField = "EmployeeId";
    ddlEmployees.DataBind();
}
private DataTable GetData(string query)
{
    string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    SqlCommand cmd = new SqlCommand(query);
    using (SqlConnection con = new SqlConnection(conString))
    {
        using (SqlDataAdapter sda = new SqlDataAdapter())
        {
            cmd.Connection = con;
 
            sda.SelectCommand = cmd;
            using (DataTable dt = new DataTable())
            {
                sda.Fill(dt);
                return dt;
            }
        }
    }
}
 
VB.Net
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
    If Not IsPostBack Then
        BindEmployeesDropDown()
    End If
End Sub
Private Sub BindEmployeesDropDown()
    ddlEmployees.DataSource = GetData("SELECT EmployeeId, (FirstName + ' ' + LastName) Name FROM Employees")
    ddlEmployees.DataTextField = "Name"
    ddlEmployees.DataValueField = "EmployeeId"
    ddlEmployees.DataBind()
End Sub
Private Function GetData(query As String) As DataTable
    Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Dim cmd As New SqlCommand(query)
    Using con As New SqlConnection(conString)
        Using sda As New SqlDataAdapter()
            cmd.Connection = con
 
            sda.SelectCommand = cmd
            Using dt As New DataTable()
                sda.Fill(dt)
                Return dt
            End Using
        End Using
    End Using
End Function
 
 
Generation of PDF Report using iTextSharp
Below is the code to generate the PDF with Employee details from database.
First I have fetched the details of the Employee based on the Employee ID from the DropDownList. Then I have created the Header of the report with Logo and name of the company. Below that a Horizontal line is drawn which separates out the Header with the Body.
The Body of the report contains the Employee Details like his Photo, Name, Designation and other details.
To reduce the size of code I have created three generic methods.
C#
protected void GenerateReport(object sender, EventArgs e)
{
    DataRow dr = GetData("SELECT * FROM Employees where EmployeeId = " + ddlEmployees.SelectedItem.Value).Rows[0]; ;
    Document document = new Document(PageSize.A4, 88f, 88f, 10f, 10f);
    Font NormalFont = FontFactory.GetFont("Arial", 12, Font.NORMAL, Color.BLACK);
    using (System.IO.MemoryStream memoryStream = new System.IO.MemoryStream())
    {
        PdfWriter writer = PdfWriter.GetInstance(document, memoryStream);
        Phrase phrase = null;
        PdfPCell cell = null;
        PdfPTable table = null;
        Color color = null;
 
        document.Open();
 
        //Header Table
        table = new PdfPTable(2);
        table.TotalWidth = 500f;
        table.LockedWidth = true;
        table.SetWidths(new float[] { 0.3f, 0.7f });
 
        //Company Logo
        cell = ImageCell("~/images/northwindlogo.gif", 30f, PdfPCell.ALIGN_CENTER);
        table.AddCell(cell);
 
        //Company Name and Address
        phrase = new Phrase();
        phrase.Add(new Chunk("Microsoft Northwind Traders Company\n\n", FontFactory.GetFont("Arial", 16, Font.BOLD, Color.RED)));
        phrase.Add(new Chunk("107, Park site,\n", FontFactory.GetFont("Arial", 8, Font.NORMAL, Color.BLACK)));
        phrase.Add(new Chunk("Salt Lake Road,\n", FontFactory.GetFont("Arial", 8, Font.NORMAL, Color.BLACK)));
        phrase.Add(new Chunk("Seattle, USA", FontFactory.GetFont("Arial", 8, Font.NORMAL, Color.BLACK)));
        cell = PhraseCell(phrase, PdfPCell.ALIGN_LEFT);
        cell.VerticalAlignment = PdfCell.ALIGN_TOP;
        table.AddCell(cell);
 
        //Separater Line
        color = new Color(System.Drawing.ColorTranslator.FromHtml("#A9A9A9"));
        DrawLine(writer, 25f, document.Top - 79f, document.PageSize.Width - 25f, document.Top - 79f, color);
        DrawLine(writer, 25f, document.Top - 80f, document.PageSize.Width - 25f, document.Top - 80f, color);
        document.Add(table);
 
        table = new PdfPTable(2);
        table.HorizontalAlignment = Element.ALIGN_LEFT;
        table.SetWidths(new float[] { 0.3f, 1f });
        table.SpacingBefore = 20f;
 
        //Employee Details
        cell = PhraseCell(new Phrase("Employee Record", FontFactory.GetFont("Arial", 12, Font.UNDERLINE, Color.BLACK)), PdfPCell.ALIGN_CENTER);
        cell.Colspan = 2;
        table.AddCell(cell);
        cell = PhraseCell(new Phrase(), PdfPCell.ALIGN_CENTER);
        cell.Colspan = 2;
        cell.PaddingBottom = 30f;
        table.AddCell(cell);
 
        //Photo
        cell = ImageCell(string.Format("~/photos/{0}.jpg", dr["EmployeeId"]), 25f, PdfPCell.ALIGN_CENTER);
        table.AddCell(cell);
 
        //Name
        phrase = new Phrase();
        phrase.Add(new Chunk(dr["TitleOfCourtesy"] + " " + dr["FirstName"] + " " + dr["LastName"] + "\n", FontFactory.GetFont("Arial", 10, Font.BOLD, Color.BLACK)));
        phrase.Add(new Chunk("(" + dr["Title"].ToString() + ")", FontFactory.GetFont("Arial", 8, Font.BOLD, Color.BLACK)));
        cell = PhraseCell(phrase, PdfPCell.ALIGN_LEFT);
        cell.VerticalAlignment = PdfPCell.ALIGN_MIDDLE;
        table.AddCell(cell);
        document.Add(table);
 
        DrawLine(writer, 160f, 80f, 160f, 690f, Color.BLACK);
        DrawLine(writer, 115f, document.Top - 200f, document.PageSize.Width - 100f, document.Top - 200f, Color.BLACK);
 
        table = new PdfPTable(2);
        table.SetWidths(new float[] { 0.5f, 2f });
        table.TotalWidth = 340f;
        table.LockedWidth = true;
        table.SpacingBefore = 20f;
        table.HorizontalAlignment = Element.ALIGN_RIGHT;
 
        //Employee Id
        table.AddCell(PhraseCell(new Phrase("Employee code:", FontFactory.GetFont("Arial", 8, Font.BOLD, Color.BLACK)), PdfPCell.ALIGN_LEFT));
        table.AddCell(PhraseCell(new Phrase("000" + dr["EmployeeId"], FontFactory.GetFont("Arial", 8, Font.NORMAL, Color.BLACK)), PdfPCell.ALIGN_LEFT));
        cell = PhraseCell(new Phrase(), PdfPCell.ALIGN_CENTER);
        cell.Colspan = 2;
        cell.PaddingBottom = 10f;
        table.AddCell(cell);
 
 
        //Address
        table.AddCell(PhraseCell(new Phrase("Address:", FontFactory.GetFont("Arial", 8, Font.BOLD, Color.BLACK)), PdfPCell.ALIGN_LEFT));
        phrase = new Phrase(new Chunk(dr["Address"] + "\n", FontFactory.GetFont("Arial", 8, Font.NORMAL, Color.BLACK)));
        phrase.Add(new Chunk(dr["City"] + "\n", FontFactory.GetFont("Arial", 8, Font.NORMAL, Color.BLACK)));
        phrase.Add(new Chunk(dr["Region"] + " " + dr["Country"] + " " + dr["PostalCode"], FontFactory.GetFont("Arial", 8, Font.NORMAL, Color.BLACK)));
        table.AddCell(PhraseCell(phrase, PdfPCell.ALIGN_LEFT));
        cell = PhraseCell(new Phrase(), PdfPCell.ALIGN_CENTER);
        cell.Colspan = 2;
        cell.PaddingBottom = 10f;
        table.AddCell(cell);
 
        //Date of Birth
        table.AddCell(PhraseCell(new Phrase("Date of Birth:", FontFactory.GetFont("Arial", 8, Font.BOLD, Color.BLACK)), PdfPCell.ALIGN_LEFT));
        table.AddCell(PhraseCell(new Phrase(Convert.ToDateTime(dr["BirthDate"]).ToString("dd MMMM, yyyy"), FontFactory.GetFont("Arial", 8, Font.NORMAL, Color.BLACK)), PdfPCell.ALIGN_LEFT));
        cell = PhraseCell(new Phrase(), PdfPCell.ALIGN_CENTER);
        cell.Colspan = 2;
        cell.PaddingBottom = 10f;
        table.AddCell(cell);
 
        //Phone
        table.AddCell(PhraseCell(new Phrase("Phone Number:", FontFactory.GetFont("Arial", 8, Font.BOLD, Color.BLACK)), PdfPCell.ALIGN_LEFT));
        table.AddCell(PhraseCell(new Phrase(dr["HomePhone"] + " Ext: " + dr["Extension"], FontFactory.GetFont("Arial", 8, Font.NORMAL, Color.BLACK)), PdfPCell.ALIGN_LEFT));
        cell = PhraseCell(new Phrase(), PdfPCell.ALIGN_CENTER);
        cell.Colspan = 2;
        cell.PaddingBottom = 10f;
        table.AddCell(cell);
 
        //Addtional Information
        table.AddCell(PhraseCell(new Phrase("Addtional Information:", FontFactory.GetFont("Arial", 8, Font.BOLD, Color.BLACK)), PdfPCell.ALIGN_LEFT));
        table.AddCell(PhraseCell(new Phrase(dr["Notes"].ToString(), FontFactory.GetFont("Arial", 8, Font.NORMAL, Color.BLACK)), PdfPCell.ALIGN_JUSTIFIED));
        document.Add(table);
        document.Close();
        byte[] bytes = memoryStream.ToArray();
        memoryStream.Close();
        Response.Clear();
        Response.ContentType = "application/pdf";
        Response.AddHeader("Content-Disposition", "attachment; filename=Employee.pdf");
        Response.ContentType = "application/pdf";
        Response.Buffer = true;
        Response.Cache.SetCacheability(HttpCacheability.NoCache);
        Response.BinaryWrite(bytes);
        Response.End();
        Response.Close();
    }
}
 
private static void DrawLine(PdfWriter writer, float x1, float y1, float x2, float y2, Color color)
{
    PdfContentByte contentByte = writer.DirectContent;
    contentByte.SetColorStroke(color);
    contentByte.MoveTo(x1, y1);
    contentByte.LineTo(x2, y2);
    contentByte.Stroke();
}
private static PdfPCell PhraseCell(Phrase phrase, int align)
{
    PdfPCell cell = new PdfPCell(phrase);
    cell.BorderColor = Color.WHITE;
    cell.VerticalAlignment = PdfCell.ALIGN_TOP;
    cell.HorizontalAlignment = align;
    cell.PaddingBottom = 2f;
    cell.PaddingTop = 0f;
    return cell;
}
private static PdfPCell ImageCell(string path, float scale, int align)
{
    iTextSharp.text.Image image = iTextSharp.text.Image.GetInstance(HttpContext.Current.Server.MapPath(path));
    image.ScalePercent(scale);
    PdfPCell cell = new PdfPCell(image);
    cell.BorderColor = Color.WHITE;
    cell.VerticalAlignment = PdfCell.ALIGN_TOP;
    cell.HorizontalAlignment = align;
    cell.PaddingBottom = 0f;
    cell.PaddingTop = 0f;
    return cell;
}
 
VB.Net
Protected Sub GenerateReport(sender As Object, e As EventArgs)
    Dim dr As DataRow = GetData("SELECT * FROM Employees where EmployeeId = " + ddlEmployees.SelectedItem.Value).Rows(0)
 
    Dim document As New Document(PageSize.A4, 88.0F, 88.0F, 10.0F, 10.0F)
    Dim NormalFont As Font = FontFactory.GetFont("Arial", 12, Font.NORMAL, Color.BLACK)
    Using memoryStream As New System.IO.MemoryStream()
        Dim writer As PdfWriter = PdfWriter.GetInstance(document, memoryStream)
        Dim phrase As Phrase = Nothing
        Dim cell As PdfPCell = Nothing
        Dim table As PdfPTable = Nothing
        Dim color__1 As Color = Nothing
 
        document.Open()
 
        'Header Table
        table = New PdfPTable(2)
        table.TotalWidth = 500.0F
        table.LockedWidth = True
        table.SetWidths(New Single() {0.3F, 0.7F})
 
        'Company Logo
        cell = ImageCell("~/images/northwindlogo.gif", 30.0F, PdfPCell.ALIGN_CENTER)
        table.AddCell(cell)
 
        'Company Name and Address
        phrase = New Phrase()
        phrase.Add(New Chunk("Microsoft Northwind Traders Company" & vbLf & vbLf, FontFactory.GetFont("Arial", 16, Font.BOLD, Color.RED)))
        phrase.Add(New Chunk("107, Park site," & vbLf, FontFactory.GetFont("Arial", 8, Font.NORMAL, Color.BLACK)))
        phrase.Add(New Chunk("Salt Lake Road," & vbLf, FontFactory.GetFont("Arial", 8, Font.NORMAL, Color.BLACK)))
        phrase.Add(New Chunk("Seattle, USA", FontFactory.GetFont("Arial", 8, Font.NORMAL, Color.BLACK)))
        cell = PhraseCell(phrase, PdfPCell.ALIGN_LEFT)
        cell.VerticalAlignment = PdfCell.ALIGN_TOP
        table.AddCell(cell)
 
        'Separater Line
        color__1 = New Color(System.Drawing.ColorTranslator.FromHtml("#A9A9A9"))
        DrawLine(writer, 25.0F, document.Top - 79.0F, document.PageSize.Width - 25.0F, document.Top - 79.0F, color__1)
        DrawLine(writer, 25.0F, document.Top - 80.0F, document.PageSize.Width - 25.0F, document.Top - 80.0F, color__1)
        document.Add(table)
 
        table = New PdfPTable(2)
        table.HorizontalAlignment = Element.ALIGN_LEFT
        table.SetWidths(New Single() {0.3F, 1.0F})
        table.SpacingBefore = 20.0F
 
        'Employee Details
        cell = PhraseCell(New Phrase("Employee Record", FontFactory.GetFont("Arial", 12, Font.UNDERLINE, Color.BLACK)), PdfPCell.ALIGN_CENTER)
        cell.Colspan = 2
        table.AddCell(cell)
        cell = PhraseCell(New Phrase(), PdfPCell.ALIGN_CENTER)
        cell.Colspan = 2
        cell.PaddingBottom = 30.0F
        table.AddCell(cell)
 
        'Photo
        cell = ImageCell(String.Format("~/photos/{0}.jpg", dr("EmployeeId")), 25.0F, PdfPCell.ALIGN_CENTER)
        table.AddCell(cell)
 
        'Name
        phrase = New Phrase()
        phrase.Add(New Chunk(dr("TitleOfCourtesy").ToString & " " + dr("FirstName").ToString & " " + dr("LastName").ToString, FontFactory.GetFont("Arial", 10, Font.BOLD, Color.BLACK)))
        phrase.Add(New Chunk("(" + dr("Title").ToString() + ")", FontFactory.GetFont("Arial", 8, Font.BOLD, Color.BLACK)))
        cell = PhraseCell(phrase, PdfPCell.ALIGN_LEFT)
        cell.VerticalAlignment = PdfPCell.ALIGN_MIDDLE
        table.AddCell(cell)
        document.Add(table)
 
        DrawLine(writer, 160.0F, 80.0F, 160.0F, 690.0F, Color.BLACK)
        DrawLine(writer, 115.0F, document.Top - 200.0F, document.PageSize.Width - 100.0F, document.Top - 200.0F, Color.BLACK)
 
        table = New PdfPTable(2)
        table.SetWidths(New Single() {0.5F, 2.0F})
        table.TotalWidth = 340.0F
        table.LockedWidth = True
        table.SpacingBefore = 20.0F
        table.HorizontalAlignment = Element.ALIGN_RIGHT
 
        'Employee Id
        table.AddCell(PhraseCell(New Phrase("Employee code:", FontFactory.GetFont("Arial", 8, Font.BOLD, Color.BLACK)), PdfPCell.ALIGN_LEFT))
        table.AddCell(PhraseCell(New Phrase("000" + dr("EmployeeId"), FontFactory.GetFont("Arial", 8, Font.NORMAL, Color.BLACK)), PdfPCell.ALIGN_LEFT))
        cell = PhraseCell(New Phrase(), PdfPCell.ALIGN_CENTER)
        cell.Colspan = 2
        cell.PaddingBottom = 10.0F
        table.AddCell(cell)
 
 
        'Address
        table.AddCell(PhraseCell(New Phrase("Address:", FontFactory.GetFont("Arial", 8, Font.BOLD, Color.BLACK)), PdfPCell.ALIGN_LEFT))
        phrase = New Phrase(New Chunk(dr("Address").ToString, FontFactory.GetFont("Arial", 8, Font.NORMAL, Color.BLACK)))
        phrase.Add(New Chunk(dr("City").ToString + vbLf, FontFactory.GetFont("Arial", 8, Font.NORMAL, Color.BLACK)))
        phrase.Add(New Chunk(dr("Region").ToString + " " + dr("Country").ToString + " " + dr("PostalCode").ToString, FontFactory.GetFont("Arial", 8, Font.NORMAL, Color.BLACK)))
        table.AddCell(PhraseCell(phrase, PdfPCell.ALIGN_LEFT))
        cell = PhraseCell(New Phrase(), PdfPCell.ALIGN_CENTER)
        cell.Colspan = 2
        cell.PaddingBottom = 10.0F
        table.AddCell(cell)
 
        'Date of Birth
        table.AddCell(PhraseCell(New Phrase("Date of Birth:", FontFactory.GetFont("Arial", 8, Font.BOLD, Color.BLACK)), PdfPCell.ALIGN_LEFT))
        table.AddCell(PhraseCell(New Phrase(Convert.ToDateTime(dr("BirthDate")).ToString("dd MMMM, yyyy"), FontFactory.GetFont("Arial", 8, Font.NORMAL, Color.BLACK)), PdfPCell.ALIGN_LEFT))
        cell = PhraseCell(New Phrase(), PdfPCell.ALIGN_CENTER)
        cell.Colspan = 2
        cell.PaddingBottom = 10.0F
        table.AddCell(cell)
 
        'Phone
        table.AddCell(PhraseCell(New Phrase("Phone Number:", FontFactory.GetFont("Arial", 8, Font.BOLD, Color.BLACK)), PdfPCell.ALIGN_LEFT))
        table.AddCell(PhraseCell(New Phrase(dr("HomePhone") + " Ext: " + dr("Extension"), FontFactory.GetFont("Arial", 8, Font.NORMAL, Color.BLACK)), PdfPCell.ALIGN_LEFT))
        cell = PhraseCell(New Phrase(), PdfPCell.ALIGN_CENTER)
        cell.Colspan = 2
        cell.PaddingBottom = 10.0F
        table.AddCell(cell)
 
       'Addtional Information
        table.AddCell(PhraseCell(New Phrase("Addtional Information:", FontFactory.GetFont("Arial", 8, Font.BOLD, Color.BLACK)), PdfPCell.ALIGN_LEFT))
        table.AddCell(PhraseCell(New Phrase(dr("Notes").ToString(), FontFactory.GetFont("Arial", 8, Font.NORMAL, Color.BLACK)), PdfPCell.ALIGN_JUSTIFIED))
        document.Add(table)
        document.Close()
        Dim bytes As Byte() = memoryStream.ToArray()
        memoryStream.Close()
        Response.Clear()
        Response.ContentType = "application/pdf"
        Response.AddHeader("Content-Disposition", "attachment; filename=Employee.pdf")
        Response.ContentType = "application/pdf"
        Response.Buffer = True
        Response.Cache.SetCacheability(HttpCacheability.NoCache)
        Response.BinaryWrite(bytes)
        Response.[End]()
        Response.Close()
    End Using
 
End Sub
 
Private Shared Sub DrawLine(writer As PdfWriter, x1 As Single, y1 As Single, x2 As Single, y2 As Single, color As Color)
    Dim contentByte As PdfContentByte = writer.DirectContent
    contentByte.SetColorStroke(color)
    contentByte.MoveTo(x1, y1)
    contentByte.LineTo(x2, y2)
    contentByte.Stroke()
End Sub
Private Shared Function PhraseCell(phrase As Phrase, align As Integer) As PdfPCell
    Dim cell As New PdfPCell(phrase)
    cell.BorderColor = Color.WHITE
    cell.VerticalAlignment = PdfCell.ALIGN_TOP
    cell.HorizontalAlignment = align
    cell.PaddingBottom = 2.0F
    cell.PaddingTop = 0.0F
    Return cell
End Function
Private Shared Function ImageCell(path As String, scale As Single, align As Integer) As PdfPCell
    Dim image As iTextSharp.text.Image = iTextSharp.text.Image.GetInstance(HttpContext.Current.Server.MapPath(path))
    image.ScalePercent(scale)
    Dim cell As New PdfPCell(image)
    cell.BorderColor = Color.WHITE
    cell.VerticalAlignment = PdfCell.ALIGN_TOP
    cell.HorizontalAlignment = align
    cell.PaddingBottom = 0.0F
    cell.PaddingTop = 0.0F
    Return cell
End Function
 
Demo
View Demo
 
Downloads
The download sample contains the iTextSharp DLL.