Compile SQL Report in Console application

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)
       开发者_C百科 {
            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!

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.

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();