Tuesday, January 18, 2011

Generating PDF from DataTable using iTextSharp

iTextSharp is a wonderful open-source tool for generating PDF in C#.

Here is a simple generic helper class for Exporting data from a DataTable to a PDF file.

using System;
using System.Web;
using System.Data;

using iTextSharp.text;
using iTextSharp.text.pdf;

namespace yetanothercoder
    /// Summary description for CreatePdf
    public class PDFExporter

        private readonly DataTable dataTable;
        private readonly string fileName;
        private readonly bool timeStamp;

        public PDFExporter(DataTable dataTable, string fileName, bool timeStamp)
            this.dataTable = dataTable;
            this.fileName = timeStamp ? String.Format("{0}-{1}", fileName, GetTimeStamp(DateTime.Now)) : fileName;
            this.timeStamp = timeStamp;

        public void ExportPDF()
            HttpResponse Response = HttpContext.Current.Response;
            Response.ContentType = "application/octet-stream";
            Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName + ".pdf");

            // step 1: creation of a document-object
            Document document = new Document(PageSize.A4, 10, 10, 90, 10);

            // step 2: we create a writer that listens to the document
            PdfWriter writer = PdfWriter.GetInstance(document, Response.OutputStream);

            //set some header stuff
            document.AddSubject(String.Format("Table of {0}", fileName));

            // step 3: we open the document

            // step 4: we add content to the document

            // step 5: we close the document

        private void CreatePages(Document document)
            PdfPTable pdfTable = new PdfPTable(dataTable.Columns.Count);
            pdfTable.DefaultCell.Padding = 3;
            pdfTable.WidthPercentage = 100; // percentage
            pdfTable.DefaultCell.BorderWidth = 2;
            pdfTable.DefaultCell.HorizontalAlignment = Element.ALIGN_CENTER;

            foreach (DataColumn column in dataTable.Columns)
            pdfTable.HeaderRows = 1;  // this is the end of the table header
            pdfTable.DefaultCell.BorderWidth = 1;

            foreach (DataRow row in dataTable.Rows)
                foreach (object cell in row.ItemArray)
                    //assume toString produces valid output


        private static Phrase FormatPageHeaderPhrase(string value)
            return new Phrase(value, FontFactory.GetFont(FontFactory.TIMES, 10, Font.BOLD, new BaseColor(255, 0, 0)));

        private static Phrase FormatHeaderPhrase(string value)
            return new Phrase(value, FontFactory.GetFont(FontFactory.TIMES, 8, Font.UNDERLINE, new BaseColor(0, 0, 255)));

        private Phrase FormatPhrase(string value)
            return new Phrase(value, FontFactory.GetFont(FontFactory.TIMES, 8));

        private string GetTimeStamp(DateTime value)
            return value.ToString("yyyyMMddHHmmssffff");

Now if you have an Export to Excel Button, you can generate pdf easily like this

protected void ExportToPDFLink_Click(object sender, EventArgs e)
    PDFExporter pdf = new PDFExporter(GetCostumers(), "customer", true);


SQLite in ASP.NET Web Forms

What is SQLite?

SQLite is a in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine.

SQLite is a must have tool for manipulating database for small to mid size projects, there by cutting the cost of maintaining an extra database server. Its very fast and has less percentage of crashing also.
Next, download an SQLite Manager from which we can create and manipulate a sample database and its objects. For that I used the SQLite Manager add on for Firefox. Feel free to use any other tools.
Now we are all set to use the SQLite database in our ASP.NET application.

Steps to follow

1. Create a Sample Database called Northwind.sqlite using the firefix addon.
2. Add a table named Customers to it with some fields and some dummy data to it.
3. Create an empty website in .NET 3.5 using Visual Studio
4. Create an App_Data folder in it and place the Northwind.sqlite to it
5. Add to the empty website as reference, the dll
using System.Data.SQLite
6. Create a default page in your application and place a gridview in that

<asp:GridView ID="CustomersGrid" runat="server">

7. At code behind, add reference to System.Data.SQLite;
8. Write the code to get data from Customers Table here.

private void BindCustomerGrid()
    string commandText = "SELECT * FROM Customers";
    string path_to_db = System.IO.Path.Combine(Server.MapPath("."), "App_Data", "Northwind.sqlite3");
    string connectionString = String.Format("Data Source={0};Version=3;", path_to_db);
    DataTable dataTable = new DataTable();
    using (SQLiteConnection connection = new SQLiteConnection(connectionString))
        using (SQLiteCommand cmd = new SQLiteCommand(commandText, connection))
            cmd.CommandType = CommandType.Text;
            using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd))
    CustomersGrid.DataSource = dataTable;

Thats all to it. Happy Coding!