Click here to Skip to main content
15,868,005 members
Articles / Programming Languages / C#
Article

SQL Query Results as Excel Spreadsheet Mailer Web Service

Rate me:
Please Sign up or sign in to vote.
4.67/5 (17 votes)
4 Oct 2003CPOL4 min read 174.7K   589   60   27
Web Service that e-mails results of a SQL Query as Excel Spreadsheet attachments

Introduction

This article describes a Web Service infrastructure that e-mails the results of a given SQL query on a given datasource with as an Excel Spreadsheet attachment. This service can then be used by a controller process that can be set up to run on a scheduled basis providing various reports that can be distributed via e-mail. A typical corporate scenario is when management requests the top N report for the previous day and they want the output to be formatted as an Excel Spreadsheet which they happen to be comfortable with and can also pass around to peers via e-mail. Or perhaps you would like to monitor the hits on your website and could use the numeric functions that Excel can provide to tabulate, group, arrange the data conveniently sent to your inbox on demand or on schedule.

Background

Automating Excel on the server-side is not for the faint hearted. Excel is designed for heavy end-user interaction and is full of modal dialog boxes that can be disastrous in a server side environment where unattended execution is a must. Luckily for us, Office 2000 and higher provides an assortment of components named Office Web Components (OWC for short) that are specifically designed for use with server-side applications. These are free-threaded components completely devoid of modal dialog boxes and various office assistants (remember Clippy ?) that appear to have a habit of popping up every now and then.

We will be using the OWC.SpreadSheet component in this article. The Office Web Components are installed by default with Office XP, however they can also be downloaded from here.

Using the code

The main entry point to the webservice is the SendQueryresultsAsSpreadSheet method which takes the connection string, the SQL query itself and the e-mail address to be notified of the results as parameters. Once the method is called it simply instantiates an ExcelQueryprocessor class and queues it by calling the Threadpool.QueueUserWorkItem method specifying a callback procedure ProcessRequest which the CLR will call with the persisted stateInfo when a thread is available. This implementation allows the Web Service call to return almost instantenously even under heavy load.

C#
[WebMethod]
public void SendQueryResultsAsSpreadSheet(string ConnectString, 
    string SQLQuery, string EmailAddress)
{
    // create our worker class
    ExcelQueryProcessor eqp = new ExcelQueryProcessor(
        ConnectString,SQLQuery,EmailAddress);
    // queue it 
    ThreadPool.QueueUserWorkItem(new WaitCallback(ProcessRequest),eqp);
}

private void ProcessRequest(Object stateInfo) {
    // cast stateInfo back to an ExcelQueryProcessor 
    ExcelQueryProcessor eqp  = (ExcelQueryProcessor)stateInfo;
    // off we go ...
    eqp.GenerateExcelSheet(); 
}        

We start by adding a reference to the Office Web Components.

Add reference to OWC

The main workhorse of the Web Service is the GenerateExcelSheet method of the ExcelQueryProcessor class. We being by checking whether the e-mail address passed to the service is valid by calling the ValidEmail method passing the e-mail adress as a parameter, which is a simple Regex check that filters out most malformed e-mail addresses. If the e-mail address is determined to be valid we continue by generating a SqlConnection with the connection string that is passed to the Web Service. We use a DataReader for performance reasons and start populating the OWC.Spreadsheet component with the data that returned from the query. This is done in two loops, one for the field names and the other for the data itself.

When the spreadsheet is generated we call the ActiveSheet.Export method of the Spreadheet object to persist it to a local directory. The actual path of this directory is stored in the Web.Config file allowing for flexibility of being able to change it. One point of caution : the ASP.Net Worker process account (MACHINE\ASPNET) must have write permissions to this directory. The filename to be used is generated by calling Guid.NewGuid() method. Other techniques that can assign more meaningful filenames can also be be used, such as counters, datetime stamps etc. After the e-mail is sucessfully sent the XLS file that is generated during the process is deleted to conserve diskspace. Also if an exception is caught such as a malformed query the description of the exception and the call stack is e-mailed to the recipient to help diagnose the error.

C#
using System;
using System.IO;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.Text.RegularExpressions;

namespace Hosca.SQLExcelMail {
public class ExcelQueryProcessor {
        string connectString;
        string sqlQuery;
        string emailAddress;

        public ExcelQueryProcessor(string ConnectString, string SQLQuery, 
            string EmailAddress) {
            connectString = ConnectString;
            sqlQuery = SQLQuery;
            emailAddress = EmailAddress;
        }
        public void GenerateExcelSheet(){
            // is this a valid e-mail address ?
            if (ValidEmail(emailAddress)){
                try{
                    // grab a SqlConnection
                    using (SqlConnection sqlCon = GetSqlConnection(
                        connectString)) {
                        SqlCommand sqlCommand = new SqlCommand(sqlQuery, 
                           sqlCon);
                        // populate DataReader
                        SqlDataReader dr = sqlCommand.ExecuteReader(
                           CommandBehavior.CloseConnection);
                        // create an instance of SpreadSheet Web Control
                        OWC.Spreadsheet spread = new OWC.SpreadsheetClass();
                        // reset counters
                        int rowCount = 0;
                        rowCount++;
                        // write out field headers
                        for(int colCount = 0;colCount < dr.FieldCount;
                            colCount++){
                            spread.Cells[rowCount,colCount + 1] = 
                                  dr.GetName(colCount);
                        }
                        // write out the actual data by looping thru 
                        // the dataReader
                        while (dr.Read()) {
                            rowCount++;
                            for(int colCount = 0;colCount < 
                                dr.FieldCount;colCount++){
                                // filter out any embedded \r & \n in 
                                // the field data
                                spread.Cells[rowCount,colCount + 1] = 
                                     dr.GetValue(colCount).ToString().
                                        Replace("\r","").Replace("\n","");
                            }
                        }
                        // make sure the output directory path 
                        // terminates with a "\"
                        string outputFolder = 
                          ConfigurationSettings.AppSettings[
                          "SpreadSheetDirectory"];
                        if (!(outputFolder.Substring(outputFolder.Length - 1)
                              == @"\"))
                            outputFolder = string.Concat(outputFolder,@"\");
                        // create a unique filename
                        string fileName = outputFolder + 
                           Guid.NewGuid().ToString() +".XLS"; 
                        // generate the XLS file
                        spread.ActiveSheet.Export(fileName,0);
                        // de-reference the SpreadSheet Control 
                        spread = null;
                        // generate the e-mail body
                        string emailBody = string.Concat(
                          DateTime.Now.ToString(), 
                          " - Your query was :<hr>" , sqlQuery);
                        // send e-mail
                        SpreadSheetMailer sm = new SpreadSheetMailer(
                           emailAddress,emailBody,fileName);
                        sm.SendEMail();
                        // clean-up the file
                        File.Delete(fileName);
                    }
                }
                catch(Exception ex){
                    // notify recipient of caught exception
                    string message = string.Concat(ex.Message,
                          ex.StackTrace,DateTime.Now.ToString());
                    SpreadSheetMailer sm = new SpreadSheetMailer(
                          emailAddress,message,"");
                    sm.SendEMail();
                }
            }
        }
        private bool ValidEmail(string EmailAddress){
            // validate e-mail address
            // TODO: expand this to handle multiple e-mail 
            // addresses for group distribution
            Regex rx = new Regex(
               @"^[\w-]+(?:\.[\w-]+)*@(?:[\w-]+\.)+[a-zA-Z]{2,7}$");
            Match m = rx.Match(EmailAddress);
            if (m.Success)
                return true;
            else
                return false;
        }
        private SqlConnection GetSqlConnection(string ConnectString){
            SqlConnection sqlCon = new SqlConnection(ConnectString);
            try{
                // make sure the time-out value specified is acceptable
                if (!(sqlCon.ConnectionTimeout == 0)||
                          (sqlCon.ConnectionTimeout > 60)){
                    sqlCon.Open();
                    return sqlCon;
                }
                else{
                    throw new Exception(
                        "Invalid Connection Time Out Value Specified");
                }            
            }
            catch (Exception ex){
                throw ex;
            }
        }
    }
}

The SpreadSheetMailer object is responsible for sending out the actual e-mail with the Spreadsheet attached. A quick check is made to determine if there is a valid attachment file before the Send method is called. The address of the SMTP server used is also stored in the Web.Config file.

C#
using System;
using System.IO;
using System.Web.Mail; 
using System.Configuration;

namespace Hosca.SQLExcelMail
{
    public class SpreadSheetMailer
    {
        string mailRecipient = null;
        string mailBody = null;
        string mailAttachFile = null;

        public SpreadSheetMailer(string Recipient, string Body, 
                string AttachFile) {
            mailRecipient = Recipient;
            mailBody = Body;
            mailAttachFile = AttachFile;
        }
        public void SendEMail() {

            // get the MailFrom address from Web.Config
            string mailFrom = 
                 System.Configuration.ConfigurationSettings.AppSettings[
                 "MailFrom"];
            MailMessage msg = new MailMessage();
            // parse out username from e-mail address
            msg.From =  mailFrom.Substring(0,mailFrom.IndexOf("@"))
                    + "<" + mailFrom + ">";
            msg.To = mailRecipient + "<" + mailRecipient + ">";
            msg.Subject = "SQLExcelMail Query Results";
            msg.Priority = MailPriority.Normal;
            msg.BodyFormat = MailFormat.Html;
            msg.BodyEncoding = System.Text.Encoding.UTF8;
            msg.Body = mailBody;
            // do we have an attachment and is it valid ?
            if ((mailAttachFile.Length > 0) && 
              (File.Exists(mailAttachFile))){
                msg.Attachments.Add(new MailAttachment(mailAttachFile));
            }
            // get the SMTP server address from Web.Config
            SmtpMail.SmtpServer = ConfigurationSettings.AppSettings[
                  "SmtpServerName"];
            try {
                SmtpMail.Send(msg);
            }
            catch (Exception ex) {
                throw ex;
            }
        }
    }
}

Points of interest

This Web Service implementation is meant to be used as a part of a larger framework. This particular implementation allows the end user to specify the connection string to be used to allow for maximum flexibility. In a production environment however this should not be the case. Allowing the requester to specify the connection string opens up the possibility of the requester unknowingly (or willingly) specify the ConnectionTimeout value as zero, which would in theory be a significant drain on the available resources. Also any exceptions that are caught should really be e-mailed to a designated Administrator's inbox.

History

  • Oct 5 2003 Initial version (1.0.0)

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Web Developer JPMorgan Chase & Co.
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionAbout the Spreadsheet.Export method Pin
Daniel Mora2-Feb-07 11:45
Daniel Mora2-Feb-07 11:45 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.