Export Dataset/DataReader .net using 4.0, 4.5 To PDF

When having problems to export to PDF I have recently used a dll called iTextSharp which can be downloaded here and SQL Helper can be found here and once installed the location of the .cs file is \Microsoft Application Blocks for .NET\Data Access v2\Code\CS\Microsoft.ApplicationBlocks.Data
The main problem I had is that I as trying to make a control render using the HtmlTextWriter this result in and error saying that the control should be within the form tags. I think this was due to the way I was rendering the gridviews with sqldatasource controls.
Anwyay this seemed to be a silly way of doing things in the end as I had the datasets returning in the correct format that I wanted already in place, so I decided to use the SQLDataReader. After using this it was a lot more straight forward as I could create loops to display my table in the PDF how I wanted.
You will need to setup the reference to the DLL which will then be accessible.

Your code behind using block should look something like this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;

using System.IO;
using System.Data;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;
using iTextSharp.text;
using iTextSharp.text.pdf;
using iTextSharp.text.html.simpleparser;


This is the following function I have created which can be called multiple times. The natural progression from this would be to pass sqlparameters into the function and then to the reader. The filename is the output filename that will popup on the browser, so it's good to have the ability to change this on the fly.

protected void ExportToPDF(string storedproc, string filename) {

            string attachment = "attachment; filename=" + filename;

            Response.AddHeader("content-disposition", attachment);
            Response.ContentType = "application/pdf";
            Response.Cache.SetCacheability(HttpCacheability.NoCache);
            Response.ClearContent();

            StringWriter sw = new StringWriter();
            HtmlTextWriter htw = new HtmlTextWriter(sw);


            SqlDataReader dr = SqlHelper.ExecuteReader(cn, CommandType.StoredProcedure, storedproc);


            htw.Write("<table width='100%'>");

            int t;

            htw.Write("<tr>");

            for (t = 0; t < dr.FieldCount; t++)
            {
                htw.Write("<td>" + dr.GetName(t) + "</td>");
            }

            htw.Write("</tr>");

            while (dr.Read())
            {
                htw.Write("<tr>");

                for (t = 0; t < dr.FieldCount; t++)
                {
                    htw.Write("<td>" + dr[t].ToString() + "</td>");
                }

                htw.Write("</tr>");
            }

            htw.Write("</tr></table>");


                        StringReader sr = new StringReader(sw.ToString());

            Document pdfDoc = new Document(PageSize.A4, 10f, 10f, 10f, 0f);
          
            HTMLWorker htmlparser = new HTMLWorker(pdfDoc);

            PdfWriter.GetInstance(pdfDoc, Response.OutputStream);

            pdfDoc.Open();
            htmlparser.Parse(sr);
            pdfDoc.Close();

            Response.Write(pdfDoc);
            Response.End();    

      
        }

Example Usage:
ExportToPDF("usp_YourProcedure", "MyTestOutput.pdf");

Notes: If you try to render out the control you will probably get an error like this
"Control 'MainContent_GridView1' of type 'GridView' must be placed inside a form tag with runat=server".

This is the main reason I didnt render out the controls directly, but pulled the dataset in and then created a table.  I was also using SQL Datasources with the grids on my page, this could have been done differently, but I wanted the sorting and paging to be automatic also.

Hope this helps those who are struggling with this one. Netferret

SQLHelper - Microsoft Data Access Application Block

When starting to program in c# .net, it helps to use a class provided by Microsoft which allows you to code more quickly and effectively. Instead of creating several lines of code to perform a database task you can simply just use a couple of lines.

This is a really basic example of how the class can be used.

Add this namespace to your page

using Microsoft.ApplicationBlocks.Data;

// Setup Connection
SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["TestConnection"].ConnectionString);


protected void Page_Load(object sender, EventArgs e)
{

// Open datareader to read through our items
SqlDataReader dr = SqlHelper.ExecuteReader(cn, CommandType.Text, "SELECT * FROM tbl_blogs ORDER BY title");

// See if we have any data and if so then cycle through the datareader
while (dr.Read())
{
Response.Write(Convert.ToString(dr["title"]) + ""<br />");
}

// Close the datareader or we cant use the connection for other operations
dr.Close();

}

}


There is more information available on http://msdn.microsoft.com/en-us/library/ms954827.aspx