Save an XML File to Database and Send an Email using XSLT

• Download source files - 2.36 KB

Introduction

It is a common requirement in a website to have the ability to send an email to the organisation via a contact form. Depending on the organisation, they may need this information saved to a database or sent directly to a pre defined email account where this information will be processed. This article will illustrate the basics of retrieving the information from the contact page to an XML file, using the XML file to perform an insert into a database table, and then transform the XML to the required format for sending to a specified account.

Background

By performing XSL transformations on XML documents, one can transform the document to any format one would require the information in. There are many excellent resources to assist you in learning XSLT, therefore I will not be going into much depth on that in this article. I have also only included rudimentary basics in the source of this application. The included XSL file is not a perfect example of XSL template, just barely enough to give an idea of how to use it. Using the information contained in this article, one can easily build upon it.

Using the Code

In the attached source code, I have included a very simple database, SQL 2000, consisting of one table tblContact, and one Stored Procedure spAddContact. The application is a very simple application consisting of just two pages built in ASP.NET 2.0. I make use of several .NET namespaces. The most notable for the purpose of this article are:

• System.Data.SqlClient
• System.IO
• System.Xml
• System.Xml.Xsl
• System.Net.Mail
• System.Text
• System.IO

Step 1

When the send button is clicked, we create an XML file. In the example, I achieve this by creating an in memory XML file, then iterate through the Pages control collection and then depending on the controls type, I append the data to the XML File.

Note

As force of habit, when I add controls to a page I carried over the habit by adding the three letters of the controls type, i.e. DropDownList = ddl.

When iterating through the control collection and creating the XML File I take off the first three letters of the control type by performing a substring(3) on the control ID and then use the remaining portion of the name to create the name of the XML tag, and then place whatever value of the control into the tag.

private XmlDocument GetEmailDocument()
   {
       // We create an xmlDocument with a root node
       XmlDocument xmlDoc = new XmlDocument();
       XmlNode xmlnode;
       xmlnode = xmlDoc.CreateElement("Root");
       xmlDoc.AppendChild(xmlnode);
       GetFormData(pnlEmail, xmlDoc);
       return xmlDoc;
   }

A very basic XML file is created. The GetFormData method is a method that iterates through the page control collection. The GetFormData method checks to see if a control is able to contain more controls, if this is possible then we recursively iterate through that control. This is an example of how to use recursion, a programming concept that is often neglected.

protected void GetFormData(Control Parent, XmlDocument xmlDoc)
    {
        XmlNode xmlnode;
        foreach (Control wc in Parent.Controls)
        {
        /* Depending on which version of the Framework you are using
       One can choose which version of this line of code you want to use
        for 2.0 You can use if(wc.HasControls())GetFormData(wc, xmlDoc);
        Or alternatively for 1.1 you can count the controls as shown below */

    if (wc.Controls.Count > 0) GetFormData(wc, xmlDoc);
            switch (wc.GetType().FullName.ToString())
            {
                case "System.Web.UI.WebControls.TextBox":
                    TextBox txt = (TextBox)wc;
                    xmlnode = xmlDoc.CreateElement(txt.ID.Substring(3).ToString());
                    xmlnode.InnerText = Server.HtmlEncode(txt.Text);
                    xmlDoc.DocumentElement.AppendChild(xmlnode);
                    break;
                case "System.Web.UI.WebControls.DropDownList":
                    DropDownList ddl = (DropDownList)wc;
                    xmlnode = xmlDoc.CreateElement(ddl.ID.Substring(3).ToString());
                    xmlnode.InnerText = ddl.SelectedValue.ToString();
                    xmlDoc.DocumentElement.AppendChild(xmlnode);
                    break;
            }
        }
    }

Step 2

Once the data has been collected to the XML file, we will save the data to the database. I demonstrate how to pass an XML file to SQL Server 2000. The C# code to perform this is nothing out of the ordinary here other than I pass the XML document through to SQL as SQL Text. You could pass this parameter through as either Char, Varchar, nVarchar, or nText. It makes no difference other than limitation on the size of the document you wish to pass through I have chosen to pass it through as Text purely out of habit (It could be a bad one :-))

oConn.Open();
          oComm.Connection = oConn;
          oComm.CommandType = CommandType.StoredProcedure;
          oComm.CommandText = "spAddEmailContact";
          oComm.Parameters.Add("@xmlDoc",SqlDbType.Text).Value =
                       xmlDoc.OuterXml.ToString();
         string sRes= oComm.ExecuteScalar().ToString();

Step 3

The real logic to saving this file is processed inside the SQL Stored Proc. There is a lot that is actually going on in this stored proc. First of all, we need to convert the text passed in the Incoming Parameter to an In Memory XML file. We achieve this by using the system stored Procedure sp_xml_preparedocument and pass it @xmlDoc as an input, and @DocHandle as an output which returns a reference to the XMLDocument. Then we start what looks like an ordinary insert statement. We may be accepting some empty strings in our XMl file. We don't want to save an empty string so we just want to save it as null. We now come to the more slightly complicated part of the stored proc, the OPENXML where we pass in the Dochandle we got earlier and then the start node we would like to start from. We then supply a value of 2 means to access the XPATH as elements.

ALTER   PROCEDURE dbo.spAddEmailContact

    (
        @xmlDoc text
    )
    
AS
    DECLARE @DocHandle int
    EXEC sp_xml_preparedocument @DocHandle OUTPUT, @xmlDoc

INSERT INTO tblContact
(FirstName, LastName , Email,Street, Town, Postcode,subject, Comment )
    SELECT 
   a.FirstName, 
   a.Lastname ,a.email,
   CASE LTRIM(RTRIM(a.Street))
      WHEN '' THEN NULL 
       ELSE a.Street
   END,
   CASE LTRIM(RTRIM(a.Town))
   WHEN '' THEN NULL
        ELSE a.town
    END ,
    CASE LTRIM(RTRIM(a.Postcode))
    WHEN '' THEN NULL
    ELSE a.Postcode
    END,
    a.subject, a.Comment
FROM OPENXML(@DocHandle, '/Root',2) WITH (FirstName varchar(50), _
	Lastname varchar(50) ,  Email varchar(50), Street varchar(50),
Town varchar(50), Postcode varchar(50),  Subject varchar(50), _
	Comment varchar(250) ) AS a

EXEC sp_xml_removedocument @DocHandle

 Select @@identity
 
    RETURN

Step 4

We now do XML/XSLT transformation and send the email. The XSLT file that we will be using in the transformation is kept in the App_Data folder of the application. We retrieve it and make use of the XSLCompiledTransform to transform the XML into an HTML file that we will send as the body of the email.

private void SendEmail(XmlDocument xmlDoc)
    {
        System.Text.StringBuilder sb = new System.Text.StringBuilder();
        XslCompiledTransform xslt = new XslCompiledTransform();
        xslt.Load(Server.MapPath("App_Data/email.xsl"));
        using (XmlTextWriter xtw = new XmlTextWriter(new StringWriter(sb)))
         {
            xslt.Transform(xmlDoc, xtw);
            xtw.Flush();
         }
           SmtpClient oMail = new SmtpClient();
           MailMessage msg = new MailMessage();
            try
            {
                MailAddress Madd = new MailAddress(txtEmail.Text, txtFirstName.Text);
                oMail.Host = "localhost";
                oMail.Port = 25;
                msg.From = Madd;
                msg.To.Add("someone@somewhere.com");
                msg.Subject = ddlSubject.SelectedValue.ToString();
                msg.IsBodyHtml = true;
                msg.Body = sb.ToString();
                oMail.Send(msg);
                Server.Transfer("sent.aspx");
            }
            catch (Exception ex)
            {
                //This step is not good practice but seen as though this is just a Demo
                //app I thought I would place this here so we can see if any errors occur
                lblFail.Visible = true;
                lblFail.Text = "An Error occurred:" + ex.Message;
            }   
    }

History

• 14-09-06 First upload with intention to further update the article later