Compile SQL Report in Console application

Question

I have to write a console application that would run a sql query on a database. The application then has to take this information and compile it into a report, export this report to pdf and then e-mail the pdf report. (All this must happen automatically – I am going to use Windows Scheduler to run this application on a specific date and time.)

Here is what I have so far:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Sql;
using System.Data.SqlClient;
using System.IO;
using System.Net.Mail;

namespace SqlQueryReports
{
class Program
{
    static void Main(string[] args)
    {
        SqlConnection dataConnection = new SqlConnection();
        try
        {
            dataConnection.ConnectionString ="Data Source=MY-PC\\SQLEXPRESS;Initial Catalog=mydb;Integrated Security=True;Pooling=False";
            dataConnection.Open();

            SqlCommand dataCommand = new SqlCommand();
            dataCommand.Connection = dataConnection;

            dataCommand.CommandText = "SELECT Product_id,Product_name,Product_price FROM Product";
            Console.WriteLine("About to execute: {0}\n\n", dataCommand.CommandText);

            SqlDataReader dataReader = dataCommand.ExecuteReader();

            // Compile data into Report
            // Export Report to .pdf
            // Email .pdf report

            dataReader.Close();

            Console.WriteLine("DONE");
        }
        catch(SqlException e)
        {
            Console.WriteLine(e.Message);
        }

        finally
        {
            dataConnection.Close();
        }

    }       
}
}

I just need know how to:

1. Compile a report with this information.
2. Export this report to pdf
3. Email the pdf report.

Thanks in advance!

Answer 1

if you want to design the report nicely in a user friendly designer you could use XtraReports from DevExpress, or any other third party Report Engine, they usually allow you to bind a DataSource and to export as pdf ( or excel, html, png and so on... ).

If you want to do everything yourself you can format a kind of HTML document with a table ( for example ) where you literally compose the grid looping in the dataReader fields and columns, then you should use any component which allows you to create a pdf document and finally you could use the built in Smtp Mailing of the .NET Framework to send the pdf via email.

Answer 2

Really wish you had more input on this one, as I've just been given the exact same task basically. Here's what I've found thus far that might help; just a sample PDF export (source: ASP Snippets)...

protected void ExportToPDF(object sender, EventArgs e)
{
    //Get the data from database into datatable
    string strQuery = "select CustomerID, ContactName, City, PostalCode"     +
        " from customers";
    SqlCommand cmd = new SqlCommand(strQuery);
DataTable dt = GetData(cmd);

//Create a dummy GridView
GridView GridView1 = new GridView();
GridView1.AllowPaging = false;
GridView1.DataSource = dt;
GridView1.DataBind();

Response.ContentType = "application/pdf";
Response.AddHeader("content-disposition",
    "attachment;filename=DataTable.pdf");
Response.Cache.SetCacheability(HttpCacheability.NoCache);
StringWriter sw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(sw);
GridView1.RenderControl(hw);
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();