Av rating:
Total votes: 2
Total comments: 0
Creating a Custom Content Formatter for SSNS 2005
24 April 2007
Microsoft SQL Server Notification Services 2005 (SSNS 2005) provides a foundation for developing and hosting notification applications. Once the technology's terminology, architecture, and core components are adequately understood, it's possible to build robust and scalable notification applications in relatively short order.
Often, these applications can be wholly created using only the components provided with the product. For example, a notification application designed to email a simple order acknowledgment to customers of an online store is a relatively straightforward task. The built-in SQL Server Event Provider may be employed to recognize new rows in the Orders table and the SMTP Delivery Channel is well suited for formatting notifications and handing them off to an email server for delivery.
In short, the built-in components for SSNS 2005 are ideal for use with notifications that are flat in structure. However, as is often the case in real-world applications, the requirements may be a bit more complex than the simple scenario just described. For example, our requirements might be to email to the customer a more complex, hierarchical order confirmation letter, complete with the line item order details.
Using SSNS 2005 to send notifications that are more hierarchical in structure, like the order detail confirmation letter, will necessitate the creation and use of a custom component.
Creating a custom component in Visual Studio .NET 2005
Rather than try to provide a built-in component for every occasion, Microsoft simply made available the SSNS 2005 API, which exposes the underlying SSNS classes that we, as database developers, can use to create our own custom components. These components can perform a variety of tasks, from collecting event data from proprietary data sources to sending notifications through most any means with which we can communicate (a fax server, a SMS gateway, or even an Instant Messenger service).
To examine the capabilities of the SSNS 2005 API, we'll continue with the order confirmation scenario. To satisfy the requirements, we will create a custom content formatter that will receive an order id from the Distributor, retrieve additional order and order detail information, and finally apply an XSLTransform to format the notification in HTML format.
Creating a Project
To create a custom component using Visual Studio .NET 2005, first create a new project (File | New | Project ). Select the Class Library template for your favorite programming language (in my case, C#) and provide the name and folder location.
Figure 1 – Creating an new project in Visual Studio .NET 2005
To make the SSNS classes available to the Custom_Formatter project, add a reference to the DLL that contains the classes. Navigate Project | Add Reference, select the Microsoft.SqlServer.NotificationServices reference and click OK
All code for the custom formatter will be added to the Custom_Formatter class in the Custom_Formatter.cs file.
For brevity in subsequent coding, add the following lines to the using section of the class.
using Microsoft.SqlServer.NotificationServices;
using System.Data.SqlClient;
using System.Data;
using System.Collections.Specialized;
using System.Collections;
using System.Xml;
using System.Xml.Xsl;
using System.IO;
Implementing the content formatter interface
The SSNS Distributor component expects that each custom formatter will provide the same consistent interface for it to use. To ensure that is the case, a custom formatter must implement the IContentFormatter interface. In C#, a class implements an interface using the following notation.
public class Custom_Formatter : IContentFormatter
The IContentFormatter interface defines three methods on which the Distributor relies for processing.
- Initialize – this method is called when the Distributor first loads the custom content formatter class.
- FormatContent – called for each notification to be formatted. Relevant information about each notification is passed to the method as parameters.
- Close – Once the customer formatter has completed its processing tasks, the Distributor will call the Close method to shutdown the custom component.
The next step is to stub in the public methods defined by the interface, as demonstrated in the following section of code.
namespace MyDemo
{
public class Custom_Formatter : IContentFormatter
{
//this method is called once when the distributor starts
public void Initialize(
StringDictionary arguments
, bool digest)
{
//initialize the arguments here
}
//the workhorse
//this method is called once for each notification
//or set of notifications in the case of digest delivery
public string FormatContent(
string subscriberLocale
, string deviceTypeName
, RecipientInfo recipientInfo
, Hashtable[] rawContent)
{
}
//this method is called only once,
//when the distributor is shutdown
public void Close()
{
//clean up any resources consumed during the run
}
}
}
The Initialize method
The Initialize method is called first by the Distributor, when the custom content formatter is to be used for a notification. It is only called once per run and it is guaranteed to be called before any other method is invoked. This method is typically used to define and set class variables required for processing notifications.
The Initialize method has two parameters, the arguments parameter and the digest parameter. The arguments parameter is a StringDictionary data type. It contains a series of argument names and values as defined in the Application Definition File. In our example, the Distributor will pass in three arguments: the XsltBaseDirectoryPath, the XsltFileName, and the ConnectionString.
- XsltBaseDirectoryPath specifies the location for the XSLTranform base directory. The exact XSLTranform file will reside in a sub-folder.
- XsltFileName provides the name of the XSLTransform to be used to transform the raw notification data into an HMTL formatted string that may be sent via email.
- ConnectionString specifies how the custom content formatter will make a connection to the production database to retrieve the additional order detail information named in our requirements.
The digest parameter is used by the customer content formatter to determine whether DigestDelivery is turned on for this SSNS application. For this example, we are not going use DigestDelivery so the code will largely ignore that argument.
The following code demonstrates how the Initialize method may be developed to process these parameters.
//class variables
private bool digest;
private SqlConnection cn;
private string XsltBaseDirectoryPath = "";
private string XsltFileName = "";
private string ConnectionString = "";
//this method is called once when the distributor starts
public void Initialize(
StringDictionary arguments
, bool digest)
{
//initialize the arguments here
this.XsltBaseDirectoryPath = arguments["XsltBaseDirectoryPath"];
this.XsltFileName = arguments["XsltFileName"];
this.ConnectionString = arguments["ConnectionString"];
//initialize the digest variable
this.digest = digest;
}
The FormatContent method
The FormatContent method is called by the Distributor for each notification (or set of notifications in the case of DigestDelivery) that needs to be formatted. In our example, this method will be charged with two tasks – gathering additional order detail information from the production database and formatting the notification using an XSLTransform. Although it is a best practice to develop custom content formatters to allow for multiple languages, this example, for brevity, assumes a United States English (en-US) language only.
The FormatContent method is listed below.
//the workhorse
//this method is called once for each notification
//or set of notifications in the case of digest delivery
public string FormatContent(
string subscriberLocale
, string deviceTypeName
, RecipientInfo recipientInfo
, Hashtable[] rawContent)
{
//declare some variables
string body = "";
int notificationCount = rawContent.Length;
bool success = true;
//get the list orders
ArrayList orders = new ArrayList();
foreach (Hashtable content in rawContent)
{
//get the order id
string orderId = Convert.ToString(content["OrderId"]);
//add to the orders collection
if (!orders.Contains(orderId))
{
orders.Add(orderId);
}
}
//create a dataset to store the orders
DataSet orderDataSet = new DataSet("Orders");
//loop through each order
for (int cnt = 0; cnt < orders.Count; cnt++)
{
string orderId = Convert.ToString(orders[cnt]);
//retrieve the order row
try
{
//get the order
//DataTable prov = getPhysicianInfo(Convert.ToInt32(providerId));
DataTable orderDataTable = getOrderInfo(Convert.ToInt32(orderId));
//make sure we found the order
if (orderDataTable.Rows.Count == 0)
{
//if we did not find it,
//there is a problem!
//exit the loop
success = false;
break;
}
//if the order table exists,
//merge the current order into the existing table
//if not add the order table to the dataset
if (orderDataSet.Tables.Contains("Order"))
{
if (orderDataSet.Tables["Order"].Rows.Contains(orderId) == false)
orderDataSet.Tables["Order"].Merge(orderDataTable);
}
else
{
//create a new datatable in the dataset
orderDataSet.Tables.Add(orderDataTable);
//create a primary key
DataColumn[] orderPk =
{ orderDataSet.Tables["Order"].Columns["OrderId"] };
orderDataSet.Tables["Order"].PrimaryKey = orderPk;
}
}
catch (Exception ex)
{
success = false;
}
//retrieve the order details
try
{
//get the order details
//DataTable pat =
getPatientInfo(patientId, Convert.ToInt32(providerId));
DataTable detailsDataTable = getOrderDetailInfo(orderId);
//make sure we found the details
if (detailsDataTable.Rows.Count == 0)
{
//if we cannot find the details
//so be it
continue;
}
//if the details table exists,
//merge the current details table
if (orderDataSet.Tables.Contains("OrderDetail"))
{
orderDataSet.Tables["OrderDetail"].Merge(detailsDataTable);
}
else
{
//add the table to the dataset
orderDataSet.Tables.Add(detailsDataTable);
//relate the order details to the order
DataRelation detailsRelation = new DataRelation(
"DetailsRelation"
, orderDataSet.Tables["Order"].Columns["OrderId"]
, orderDataSet.Tables["OrderDetail"].Columns["OrderId"]);
detailsRelation.Nested = true;
orderDataSet.Relations.Add(detailsRelation);
}
}
catch (Exception ex)
{
success = false;
}
}
if (success)
{
//extract the XML from the dataset
XmlDataDocument orderXML = new XmlDataDocument(orderDataSet);
//call the transform
string transformPath = @XsltBaseDirectoryPath + @"\en-US\"
+ deviceTypeName + @"\"
+ @XsltFileName;
body = Transform(orderXML, transformPath);
}
else
body = "";
//return the formatted body
return body;
}
Unformatted notifications are passed from the Distributor to the FormatContent method in a Hashtable array named rawContent. Since multiple notifications may be passed in the Hashtable array, our code iterates through each item of the array and adds its information to the orders collection.
Once the orders have been added to the collection, each order is processed in a foreach loop. Within the loop, two private helper methods are called to retrieve additional information from the database; the getOrderInfo method returns a single row of information about the order in a DataTable while the getOrderDetailInfo returns all detail rows for a specific order.
Each DataTable is added to (or merged with an existing DataTable in) the orderDataSet DataSet. The private helper methods are shown below.
private DataTable getOrderInfo(
int orderId)
{
DataTable dt = new DataTable("Order");
SqlDataAdapter da = new SqlDataAdapter();
//get the order from the database
if (openConnection())
{
//create the objects to get the data
SqlCommand cmd = new SqlCommand("usp_GetOrder", cn);
cmd.CommandType = CommandType.StoredProcedure;
//order parameter
SqlParameter parm = new SqlParameter("@OrderId", SqlDbType.Int, 4);
parm.Direction = ParameterDirection.Input;
parm.Value = orderId;
cmd.Parameters.Add(parm);
da.SelectCommand = cmd;
try
{
//fill the data table
int ret = da.Fill(dt);
}
catch (Exception ex)
{
//log the exception
}
}
return dt;
}
private DataTable getOrderDetailInfo(
string orderId)
{
DataTable dt = new DataTable("OrderDetail");
SqlDataAdapter da = new SqlDataAdapter();
//get the order from the database
if (openConnection())
{
//create the objects to get the data
SqlCommand cmd = new SqlCommand("usp_GetOrderDetail", cn);
cmd.CommandType = CommandType.StoredProcedure;
//order parameter
SqlParameter parm = new SqlParameter("@OrderId", SqlDbType.Int, 4);
parm.Direction = ParameterDirection.Input;
parm.Value = orderId;
cmd.Parameters.Add(parm);
da.SelectCommand = cmd;
try
{
//fill the data table
int ret = da.Fill(dt);
}
catch (Exception ex)
{
//log the exception
}
}
return dt;
}
private bool openConnection()
{
bool ret = true;
//is the connection already open?
if (cn.State == ConnectionState.Open)
return ret;
//get the connection string
cn.ConnectionString = this.ConnectionString;
//open the connection and return
try
{
cn.Open();
}
catch (Exception ex)
{
ret = false;
//log the exception
}
return ret;
}
Once all orders have been processed, the orderDataSet DataSet contains two DataTables, Order and OrderDetail.
Now that the order information has been collected from the production database, the notifications may be formatted using an XSLTransform. To do this, the code first creates an XmlDataDocument object from the orderDataSet. The XmlDataDocument, called orderXML, is passed as a parameter to another private helper method, named Transform. The Transform method applies the specified XSLTranform to the XmlDataDocument, resulting in a properly formatted notification in the body variable.
The Transform method is shown below.
public string Transform(XmlDataDocument orderXML, string xsltPath)
{
string returnString = "";
try
{
//create the transform object
XslCompiledTransform transform = new XslCompiledTransform();
//load the Xsltransform
transform.Load(xsltPath);
//create the output string
StringWriter outputString = new StringWriter();
//do the actual transform of Xml
transform.Transform(orderXML, null, outputString);
//capture the body
returnString = outputString.ToString();
//clean up
outputString.Close();
}
catch (Exception ex)
{
//log exception
}
return returnString;
}
The FormatContent method returns to the Distributor a properly-formatted notification.
The Close method
Like the Initialize method, the Close method is called only one per run. The Close method is called after all notifications have been processed and the Distributor is closing down the custom content formatter. The Close method is the appropriate place to release any resources consumed during processing.
//this method is called only once,
//when the distributor is shutdown
public void Close()
{
//clean up any resources consumed during the run
if (cn.State == ConnectionState.Open)
cn.Close();
}
After developing the three methods required by the IContentFormatter interface, and their supporting private helper methods, the project can be built and the used in the SSNS 2005 instance.
Configuring the SSNS instance to use a custom content formatter
Once the custom content formatter has been built, a SSNS 2005 application can be configured to use the custom component to format notifications before handing them off to a delivery service. As with all application-related items, the custom content formatter is declared in the Application Definition File (ADF).
Modifying the Application Definition File
Each notification class of an SSNS 2005 application contains a ContentFormatter node that defines the means by which notifications of the class are to be formatted.
Two elements are required for a custom content formatter, the ClassName and the AssemblyName. The ClassName element specifies the fully qualified name of the custom content formatter, including its namespace. The AssemblyName element defines the assembly in which the custom component may be found.
Additional arguments may be specified in the Arguments node. Three arguments are required in this example, XsltBaseDirectoryPath, XsltFileName, and ConnectionString, which correspond to the arguments found in the arguments parameter of the Initialize method.
The relevant section of the ADF is listed below.
<!-- custom content formatter -->
<ContentFormatter>
<ClassName>MyDemo.Custom_Formatter</ClassName>
<AssemblyName>E:\SSNS\Demo\bin\Custom_Formatter.dll</AssemblyName>
<Arguments>
<Argument>
<Name>XsltBaseDirectoryPath</Name>
<Value>E:\SSNS\Demo\bin\xslt</Value>
</Argument>
<Argument>
<Name>XsltFileName</Name>
<Value>MyTransform.xslt</Value>
</Argument>
<Argument>
<Name>ConnectionString</Name>
<Value>Data Source=(local);Initial Catalog=AdventureWorks;
Integrated Security=True;
Pooling=True
</Value>
</Argument>
</Arguments>
</ContentFormatter>
Once the ADF has been modified to use the custom content formatter, the SSNS instance should be updated using SQL Server Management Studio to apply the changes.
Conclusion
Although the built-in components provided with SSNS 2005 offer a healthy amount of capabilities, the SSNS architecture allows custom components to be created to satisfy the needs of most any requirement.
This article has been viewed 2900 times.