Developing an Alerts Web Service that Uses Outlook 2003 Alerts

Angela Wong
Microsoft Corporation

April 2004

Applies to:
    Microsoft® Office Outlook® 2003
    Microsoft Office SharePoint™ Portal Server 2003
    Microsoft Windows® SharePoint™ Services

Summary: As a third-party application developer, have you wanted to use e-mail to notify your users of an event, like an updated shared document or the price of a stock reaching a certain level? Learn how to use e-mail to notify your users of an event. Microsoft Office Outlook 2003 lets third-party applications use Outlook alerts to notify users of specified events. (32 printed pages)

Contents

Introduction
Scenario: User Sets up an Alert in an Application
Scenario: User Creates, Edits, or Deletes an Alert in Outlook
Alert Web Service: Support Outlook in Removing an Alert
Sample: The RSS Alerts Web Service
Scenario: User Deletes Alerts for RSS-Enabled Web Sites
Conclusion

Introduction

Microsoft® Office Outlook® 2003 lets third-party applications use Outlook alerts to notify users of specific events. Alerts can be very useful to many types of applications. For example, in team collaboration applications such as Microsoft Office SharePoint™ Portal Server 2003 and Microsoft Windows® SharePoint™ Services, information is gathered and shared in central repositories, and sifting through these repositories may require much effort. Team members can reduce this type of overhead by setting up alerts for items in which they are interested. When the content of an item changes, the site where the item resides, or the alert source site, sends an e-mail to the user. The user can apply Outlook rules on e-mails received from these alert source sites. Furthermore, Outlook lets users set up alerts on different Web servers, but manage their alerts from one central location in Outlook.

A developer can easily incorporate Outlook alerts into an application through an alerts Web service that collaborates with Outlook. The user can then set up an alert in that application or in Outlook. For example, the user can set up an alert for a Windows SharePoint Services site from Windows SharePoint Services or from Outlook. The user can then manage his or her alerts (edit or delete alerts) in Outlook 2003.

The Web service maintains a database for user alert data. To collaborate with Outlook, the alerts Web service notifies Outlook when the user sets up an alert, the Web service sends appropriate SOAP messages to Outlook when the user views, edits, or deletes his alerts in Outlook. The next two sections of this article describe how an alerts Web service can perform these tasks. The last section describes a sample alerts Web service, the RSS Alerts Web service.

Scenario: User Sets up an Alert in an Application

In an application that uses Outlook alerts, a user can specify conditions for an alert. For example, a user can set up an alert for a document repository in a team collaboration application so that the user receives a weekly summary of the documents that were changed in the repository that week.

To set up alerts, Outlook needs information about each alert that a user sets up. Outlook requires the alerts Web service to do one or both of the following tasks when a user sets up an alert:

• Send an alert setup e-mail to the user's Outlook account
• Send a cookie to the user's computer

Send an Alert Setup E-Mail

There are two purposes to this initial alert setup e-mail: to confirm to the user that the alert has been set up properly, and to provide Outlook information about the alert. This information is contained as X-headers as shown in Table 1. X-AlertWebSoap and Message-ID are mandatory headers.

Table 1. X-headers in an alert setup e-mail

When Outlook processes an incoming alert setup e-mail, it extracts and processes the X-headers, and stores the URL specified by X-AlertWebSoap in an alert sources list. The alert source list contains unique URLs for Web servers on which the user has set up alerts. Outlook uses the alert source list when the user subsequently needs to create, modify, or delete alerts.

Figure 1 shows how Outlook uses the alert source list to track new alerts being set up on multiple Web servers.

Figure 1. Outlook tracking user's alerts on multiple servers

Send a Cookie to the User's Computer

The cookie contains the fields shown in Table 2. The AlertWebSoap field is mandatory.

Table 2. Fields in an alert cookie

Scenario: User Creates, Edits, or Deletes an Alert in Outlook

Outlook provides the Manage Alerts tab of the Rules and Alerts dialog box as a central location for a user to manage all his alerts. On this tab, the user can create a new alert, modify an alert, navigate to the alert source site, manage all alerts on that alert source site, view the alerted item, create rules for alert e-mails, and delete an alert. When Outlook opens the Manage Alerts tab, Outlook populates the tab with all the alerts that the user has set up on different alert source sites.

Figure 2. Manage Alerts tab in Outlook provides a central location to manage all alerts (Click picture to view larger image)

Alert Web Service: Support Outlook to List all Alerts on the Web Server

To populate the Manage Alerts tab, Outlook checks two places:

• Outlook goes through the alert source list and extracts the Simple Object Access Protocol (SOAP) address of each Web server in that list. It then sends a GetAlerts() Web method call to each of the SOAP addresses.
• Outlook searches the client computer for alert cookies. For each cookie, Outlook extracts the AlertWebSoap field and sends a GetAlerts() Web method call to that SOAP address.

The alert Web service of each Web server should implement the GetAlerts() Web method and return a SOAP message in the following format:

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd=
"http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <soap:Body>
    <GetAlertsResponse 
         xmlns="http://schemas.microsoft.com/
           sharepoint/soap/2002/1/alerts/">
      <GetAlertsResult>
        <CurrentUser>string</CurrentUser>
        <AlertServerName>string</AlertServerName>
        <AlertServerUrl>string</AlertServerUrl>
        <AlertServerType>string</AlertServerType>
        <AlertsManagementUrl>string</AlertsManagementUrl>
        <AlertWebTitle>string</AlertWebTitle>
        <NewAlertUrl>string</NewAlertUrl>
        <AlertWebId>string</AlertWebId>
        <Alerts>
          <Alert>
            <Id>string</Id>
            <Title>string</Title>
            <Active>boolean</Active>
            <EventType>string</EventType>
            <AlertForTitle>string</ AlertForTitle>
            <AlertForUrl>string</AlertForUrl>
            <EditAlertURL>string</EditAlertURL>
            <DeliveryChannels>
              <DeliveryChannel xsi:type="string">
                <Frequency>string</Frequency>
                <Address>string</Address>
            </DeliveryChannels>
          </Alert>
        </Alerts>
      </GetAlertsResult>
    </GetAlertsResponse>
  </soap:Body>
</soap:Envelope>

The XML tags are defined in Table 3. The tags in bold are mandatory.

Table 3. XML tags in the GetAlerts() schema

Figure 3. Opening Alert Properties allows further management and viewing of alerted item

By conforming to the preceding format when responding to a GetAlerts() Web method call, an alerts Web service provides the necessary information for Outlook to complete the user tasks in Table 4.

Table 4. XML tags that support user tasks

Alert Web Service: Support Outlook in Removing an Alert

When the user deletes one or more alerts on the same Web server and clicks OK in the Manage Alerts tab (see Figure 2), Outlook sends a DeleteAlerts() Web method call to the SOAP address for the corresponding Web server. Here is the format of a DeleteAlerts() SOAP message:

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/
    XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"
      xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <soap:Body>
    <DeleteAlerts 
      xmlns="http://schemas.microsoft.com/sharepoint/soap/2002/
          1/alerts/">
      <IDs>
        <string>string</string>
        <string>string</string>
      </IDs>
    </DeleteAlerts>
  </soap:Body>
</soap:Envelope>

The alert Web service for that Web server should implement the DeleteAlerts() Web method and return a SOAP response indicating the result of each of the delete requests. The SOAP message should be in the following format:

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-
    instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
      xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <soap:Body>
    <DeleteAlertsResponse 
       xmlns="http://schemas.microsoft.com/sharepoint/soap/2002/
           1/alerts/">
      <DeleteAlertsResult>
        <DeleteFailure>
          <Id>string</Id>
          <Error>None or AccessDenied or ServerError or 
            TooManyErrors</Error>
        </DeleteFailure>
        <DeleteFailure>
          <Id>string</Id>
          <Error>None or AccessDenied or ServerError or 
           TooManyErrors</Error>
        </DeleteFailure>
      </DeleteAlertsResult>
    </DeleteAlertsResponse>
  </soap:Body>
</soap:Envelope>

The Id tag identifies an alert in the list of all alerts on that Web server. The alert Web service should return None with the Error tag if the delete request is successful, and specify the cause of failure otherwise. Upon receiving a success for the delete request, Outlook updates the Manage Alerts tab to remove the deleted alerts.

Sample: The RSS Alerts Web Service

This sample applies to the situation where a user has subscribed to an RSS-enabled Web site (such as CNET News.com). RSS-enabled Web sites allow headlines and summaries from the site to be aggregated on another Web site such as a weblog (blog). This sample allows the user to be informed when news feeds are posted to the site. The RSS Alerts Web service allows a user to receive Outlook alerts at specified times, daily for example, for new news feeds.

The following sections outline the major design aspects of the RSS Alerts Web service, and include sample code for these major areas.

Data Storage

The RSS Alerts Web service stores data in a SQL database to facilitate quick and efficient storage and retrieval of data. Figure 4 shows the data structure.

Figure 4. The RSS Alerts Web service stores data in this structure

The UserSubscriptions table lists all subscriptions on the Web server, with each subscription associated with an individual user defined in the UserList table. The UserList table stores the unique user ID and e-mail address for each user subscribing to an RSS-enabled Web site using the RSS Alert Web service. The FeedEntries table lists the news feeds for subscriptions in the UserSubscriptions table. The fields are defined in Table 5.****

Table 5. Description of data fields for the UserList table

Table 6. Description of data fields for the UserSubscriptions table

Table 7. Description of data fields for the FeedEntries table

Generating Outlook Alerts

The user can specify the frequency that Outlook alerts should be sent. Currently, the RSS Alert Web service supports daily alerts.

Each day starting at 4:00 P.M. Pacific Standard Time, the RSS Alerts Web service goes through the UserSubscriptions table, creates a summary for each user, and sends the summary as an alert e-mail to the user that created the subscription.

After the alert e-mail is sent, the Web service deletes all rows in the FeedEntries table that belong to that subscription (with the same usIndex) with a DateEntered timestamp older than the time the summary was created, This keeps the table small.

Once the summary is sent, the Web service updates the LastSent field in the UserSubscriptions table with the current date and time.

An alert e-mail appears as follows:

Subject

<UserSubscriptions.Name> subscription for <CurrentDate>

Body

This is a summary of your <UserSubscriptions.Name> subscription for <CurrentDate>:

<FeedEntries.Body>

Scenario: User Creates an Alert for an RSS-Enabled Site

When a user creates an alert for an item in an RSS-enabled Web site, the RSS Alerts Web service sends a setup e-mail to Outlook with X-header values as shown in Table 8.

Table 8. X-header values in an alert setup e-mail sent by the RSS Alerts Web service

The RSS Alerts Web service also sends a cookie to the client computer. The cookie contains values as shown in Table 9.

Table 9. Cookie values in a cookie sent by the RSS Alerts Web service

Scenario: User Opens Manage Alerts Tab in Outlook

When a user opens the Manage Alerts tab in Outlook to view, edit or delete an alert, Outlook makes a GetAlerts() call to each of the Web servers in its alerts source list. Figure 5 shows an alert entry for those RSS-enabled Web sites that the user has subscribed to.

Figure 5. User opens the Manage Alerts tab in Outlook

GetAlerts() as Implemented by the RSS Alerts Web service

The RSS Alerts Web service implements GetAlerts() that is shown in the Sample Code section below. It also returns a SOAP message to Outlook that contains the values in Table 10.

Table 10. Values in SOAP response message for GetAlerts() as implemented by the RSS Alerts Web service

Scenario: User Deletes Alerts for RSS-Enabled Web Sites

The user can delete multiple items simultaneously through the Delete Alerts dialog box in Outlook, as shown in Figure 6.

Figure 6.User deletes alerts for RSS-enabled Web sites

DeleteAlerts() as Implemented by the RSS Alerts Web service

When the user clicks Delete in the Manage Alerts tab, Outlook calls DeleteAlerts() that is implemented by the RSS Alerts Web service, as shown in the Sample Code section below. The RSS Alerts Web service also returns a SOAP message to Outlook that contains the values shown in Table 12.

Table 12. Values in SOAP Response Message for DeleteAlerts() as Implemented by the RSS Alerts Web service

To delete an alert entry, the RSS Alerts Web service deletes the relevant row (as indicated by the Id tag) in the UserSubscriptions table, as well as all other related rows in the FeedEntries table as identified by the usIndex key. This is done to ensure that:

• The subscription is no longer fetched.
• There won't be data left over from deleted subscriptions.

Sample Code

This section lists sample code showing how the RSS Alerts Web service implements GetAlerts() and DeleteAlerts(). Note that Outlook 2003 only calls these two Web methods. The other Web methods are used for server-side data management.

using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Web;
using System.Web.Services;
using System.Web.Mail;
using System.Data.SqlClient;
using System.Configuration;
using System.Text.RegularExpressions;
using System.Security;

namespace AlertsSample
{
   using System;
   using System.Xml;

   // Outlook Alerts Schema
   //
   // The AlertsNamespace and following structures are the schema expected by Outlook 11
   // and should not be changed.
   public class AlertsConst
   {
      public const string AlertsNamespace  = "http://schemas.microsoft.com/sharepoint/soap/2002/1/alerts/";
   }

   [System.Xml.Serialization.XmlTypeAttribute(Namespace=AlertsConst.AlertsNamespace)]
   [System.Xml.Serialization.XmlIncludeAttribute(typeof(DeliveryChannel))]
   [System.Xml.Serialization.XmlIncludeAttribute(typeof(EmailChannel))]
   [System.Xml.Serialization.XmlIncludeAttribute(typeof(Alert))]
   [System.Xml.Serialization.XmlIncludeAttribute(typeof(AlertInfo))]
   [System.Xml.Serialization.XmlIncludeAttribute(typeof(DeleteFailure))]
   public abstract class DeliveryChannel
   {
   }

   [System.Xml.Serialization.XmlTypeAttribute(Namespace=AlertsConst.AlertsNamespace)]
   public class EmailChannel : DeliveryChannel
   {
      public string Frequency;
      public string Address;
   }

   [System.Xml.Serialization.XmlTypeAttribute(Namespace=AlertsConst.AlertsNamespace)]
   public struct Alert
   {
      public string      Id;
      public string   Title;
      public bool     Active;
      public string   EventType;
      public string   AlertForTitle;
      public string   AlertForUrl;
      public string   EditAlertUrl;
      public DeliveryChannel[] DeliveryChannels;
   }

   [System.Xml.Serialization.XmlTypeAttribute(Namespace=AlertsConst.AlertsNamespace)]
   public struct AlertInfo
   {
      public string   CurrentUser;
      public string   AlertServerName;
      public string   AlertServerUrl;
      public string   AlertServerType;
      public string   AlertsManagementUrl;
      public string   AlertWebTitle;
      public string   NewAlertUrl;
      public string   AlertWebId;
      public Alert[]  Alerts;
   }

   [System.Xml.Serialization.XmlTypeAttribute(Namespace=AlertsConst.AlertsNamespace)]
   public enum ErrorType
   {
      None = 0,
      AccessDenied,
      ServerError,
      TooManyErrors      // Operation aborted in unknown state -- client should re-query
   }

   [System.Xml.Serialization.XmlTypeAttribute(Namespace=AlertsConst.AlertsNamespace)]
   public struct DeleteFailure
   {
      public string      ID;      // Alert id that failed
      public ErrorType   Error;
   }
      
   // Alerts Sample Web Service
   //
   // Note that GetAlerts() and DeleteAlerts() are the only methods called by
   // Outlook 11 -- the other methods here are used for server-side data management.
   //
   [WebService(Namespace=AlertsConst.AlertsNamespace)]
   public class Alerts : System.Web.Services.WebService
   {
      public enum AddAlertErrorType
      {
         None = 0,
         AlreadyExists,
         DatabaseUpdateError,
         EmailMismatch,
         HttpException,
         SMTPSendError
      }

      public Alerts()
      {
         //CODEGEN: This call is required by the ASP.NET Web Services Designer
         InitializeComponent();
      }

      #region Component Designer generated code
      
      //Required by the Web Services Designer 
      private IContainer components = null;
            


      /// <summary>
      /// Required method for Designer support - do not modify
      /// the contents of this method with the code editor.
      /// </summary>
      private void InitializeComponent()
      {
      }

      /// <summary>
      /// Clean up any resources being used.
      /// </summary>
      protected override void Dispose( bool disposing )
      {
         if(disposing && components != null)
         {
            components.Dispose();
         }
         base.Dispose(disposing);      
      }
      
      #endregion

      /* ValidUserName
       * ============
       * NOTE: To prevent SQL Injection attacks, we're being fairly
       * restrictive & requiring user names to contain only alpha
       * characters (or a '\' delimiter) before being used in a SQL command.
       * */
      private bool ValidUserName(string userName)
      {
         // for our example, force alpha or backslash
         // (which has to be escaped for both C# and Regex)
         Regex r = new Regex("^[a-zA-Z\\\\]+$");

         return r.IsMatch(userName);
      }

      /* ValidAlertId
       * ============
       * NOTE: AlertId string is passed in via SOAP and
       * should not be trusted. To prevent SQL Injection
       * attacks, the string must be verified to be a
       * known valid format before being used in a SQL command.
       * */
      private bool ValidAlertId(string alertId)
      {
         // for our example, force numeric Id of no more than 8 digits
         Regex r = new Regex("^[0-9]{1,8}$");

         return r.IsMatch(alertId);
      }

      /* IdentifyUser
       * ============
       * Helper function for authentication */
      private string IdentifyUser()
      {
         if (!User.Identity.IsAuthenticated) // true on first try
         {
            HttpContext context = HttpContext.Current;
            HttpResponse resp = context.Response;
            resp.StatusCode = 401;
            resp.Clear();
            resp.Write("401 UNAUTHORIZED");
            resp.Flush();
            resp.End();
            return null; 
         }
         else 
         {
            // prevent SQL injection via user name
            if (!ValidUserName(User.Identity.Name))
               throw new SecurityException();

            return User.Identity.Name;
         }
      }      

      SqlConnection connection = new SqlConnection(ConfigurationSettings.AppSettings["DBConnectionString"]);

      /* GetAlerts
       * =========
       * Webmethod called by Outlook to retrieve all alerts for
       * a particular user. NTLM authentication required to identify
       * user. Outlook will attempt an anonymous first try,
       * to which we must throw an exception before receiving the
       * second, authenticated request. */
      [WebMethod]
      public AlertInfo GetAlerts()
      {
         // First, find out who the user is
         AlertInfo si = new AlertInfo();
         si.CurrentUser = IdentifyUser(); 
         si.AlertServerName = "RSS Alerts Sample";

         si.AlertServerUrl = ConfigurationSettings.AppSettings["webserviceurl"];
         si.AlertServerType = "Third Party";
         si.AlertWebTitle = "RSS Alerts Web Service";
         si.AlertWebId = "{1FFAB040-A674-4eaa-96A8-9442B79E1E00}";

         // Point to management page for this Alert
         si.AlertsManagementUrl = ConfigurationSettings.AppSettings["webserviceurl"];

         // Point to new Alert page for this site
         si.NewAlertUrl = ConfigurationSettings.AppSettings["webserviceurl"];

         try 
         {
            string commandString = "SELECT ulIndex FROM UserList "
               + "WHERE UserID ='" + si.CurrentUser + "'";
            SqlCommand command = connection.CreateCommand();
            command.CommandText = commandString;
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = command;
            DataSet user = new DataSet();
            DataSet usersubs = new DataSet();
            DataSet title = new DataSet();
            connection.Open();
            string userIndex = "";
            string subIndex = "";
            int numSubs;
            if (adapter.Fill(user, "UserList") == 0) 
            {
               numSubs = 0;
            }
            else 
            {
               DataTable usertable = user.Tables["UserList"];
               foreach (DataRow row in usertable.Rows) userIndex = row[0].ToString();
               commandString = "SELECT usIndex,Location,Title,MailFrequency FROM UserSubscriptions " 
                  + "WHERE ulIndex ='" + userIndex + "'";
               command.CommandText = commandString;
               adapter.SelectCommand = command;
               numSubs = adapter.Fill(usersubs, "UserSubscriptions");
            }

            si.Alerts = new Alert[numSubs];
            DataTable substable = usersubs.Tables["UserSubscriptions"];
            int iSub = 0;
            // Get each subscription
            foreach (DataRow row in substable.Rows) 
            {
               subIndex = row["usIndex"].ToString();
               si.Alerts[iSub].Active = true;   
               si.Alerts[iSub].EventType = "All";
               si.Alerts[iSub].Id = subIndex;             
               si.Alerts[iSub].AlertForUrl = row["Location"].ToString();

               // Point to Edit page for this subscription
               si.Alerts[iSub].EditAlertUrl = ConfigurationSettings.AppSettings["webserviceurl"];

               EmailChannel emc = new EmailChannel();
               emc.Frequency = row["MailFrequency"].ToString();
               emc.Address = ConfigurationSettings.AppSettings["senderEmailAddr"]; 
               si.Alerts[iSub].DeliveryChannels = new DeliveryChannel[1];
               si.Alerts[iSub].DeliveryChannels[0] = emc;

               si.Alerts[iSub].Title = row["Title"].ToString();
               ++iSub;
            }
         } 
         catch (Exception ex) 
         { 
            connection.Close();
            return si; 
         }
         connection.Close();
         return si;
      }

      // Returns "error" if there is an error, the attribute value otherwise
      [WebMethod]
      public string GetAlertInfo(string usIndex, string attrib)
      {
         if (!(attrib.Equals("Title") || attrib.Equals("Location") || attrib.Equals("MailFrequency"))) 
return "error";

         try 
         {
            string commandString = "SELECT " + attrib + " FROM UserSubscriptions "
               + "WHERE usIndex=@index";
            SqlCommand command = connection.CreateCommand();
            command.CommandText = commandString;

            command.Parameters.Add("@index", SqlDbType.Int).Value = usIndex;

            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = command;
            DataSet sub = new DataSet();
            connection.Open();
            string answer = "";
            if (adapter.Fill(sub, "UserSubscriptions") == 0) return "error";
            DataTable subs = sub.Tables["UserSubscriptions"];
            foreach (DataRow row in subs.Rows) answer = row[0].ToString();
            return answer;
         }
         catch (Exception e) 
         { 
            throw e; 
            return "error";
         }
      }

      // Returns 1 if there is an error, 0 otherwise
      [WebMethod]
      public int SetAlertInfo(string usIndex, string attrib, string val)
      {
         if (!(attrib.Equals("Title") || attrib.Equals("Location") || attrib.Equals("MailFrequency"))) return 1;

         try 
         {
            string commandString = "UPDATE UserSubscriptions SET " + attrib + "=@val "
               + "WHERE usIndex=@index";
            SqlCommand command = connection.CreateCommand();
            command.CommandText = commandString;

            command.Parameters.Add("@val", SqlDbType.VarChar, 256).Value = val;
            command.Parameters.Add("@index", SqlDbType.Int).Value = usIndex;

            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = command;
            DataSet sub = new DataSet();
            connection.Open();
            adapter.Fill(sub, "UserSubscriptions");
            return 0;
         }
         catch (Exception e) 
         { 
            throw e; 
            return 1;
         }
      }


      [WebMethod]
      public DeleteFailure[] DeleteAlerts(string [] IDs)
      {
         DeleteFailure[] dfReturn = new DeleteFailure[IDs.Length];
         string userID = IdentifyUser();
         bool allFailed = false;

         try 
         {
            // A match on both userID and usIndex must occur
            // before we proceed with the subscription removal
            string commandString = "SELECT ulIndex FROM UserList "
               + "WHERE UserID ='" + userID + "'";
            SqlCommand command = connection.CreateCommand();
            command.CommandText = commandString;
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = command;
            DataSet user = new DataSet();
            connection.Open();
            string userIndex = "";
            if (adapter.Fill(user, "UserList") == 0) allFailed = true;
            else 
            {
               DataSet usersubs = new DataSet();
               DataSet feeds = new DataSet();
               DataSet count = new DataSet();
               int i=0;
               DataTable usertable = user.Tables["UserList"];
               foreach (DataRow row in usertable.Rows) userIndex = row[0].ToString();
               foreach (string id in IDs) 
               {
                  // Make sure the id fits our format before using it
                  if (!ValidAlertId(id))
                     continue;

                  commandString = "DELETE FROM FeedEntries "
                     + "WHERE usIndex='" + id + "'";
                  command.CommandText = commandString;
                  adapter.SelectCommand = command;
                  dfReturn[i].ID = id;
                  adapter.Fill(feeds, "FeedEntries");
                  commandString = "DELETE FROM UserSubscriptions " 
                     + "WHERE ulIndex ='" + userIndex + "' "
                     + "AND usIndex='" + id + "'";
                  command.CommandText = commandString;
                  adapter.SelectCommand = command;
                  adapter.Fill(usersubs, "UserSubscriptions");
                  dfReturn[i].Error = ErrorType.None;
                  ++i;
               }
               commandString = "SELECT COUNT(*) FROM UserSubscriptions WHERE ulIndex='" + userIndex + "'"; 
               command.CommandText = commandString;
               adapter.SelectCommand = command;
               adapter.Fill(count, "UserSubscriptions");
               DataTable datatable = count.Tables["UserSubscriptions"];
               string numSubsLeft = "";
               foreach (DataRow row in datatable.Rows) numSubsLeft = row[0].ToString();
               if (numSubsLeft.Equals("0")) 
               {
                  commandString = "DELETE FROM UserList WHERE ulIndex='" + userIndex + "'";
                  command.CommandText = commandString;
                  adapter.SelectCommand = command;
                  adapter.Fill(user, "UserList");
               }
            }
         } 
         catch (Exception ex) 
         { 
            allFailed = true;
         }

         if (allFailed) 
         {
            int i=0;
            foreach (string id in IDs) 
            {
               dfReturn[i].Error = ErrorType.AccessDenied;
               dfReturn[i].ID = id;
               ++i;
            }
         }
         connection.Close();
         return dfReturn;
      }

      public const string AlertTitle = "RSS Feeds";
      public const string msgBody = "An alert has successfully been added on 'RSS News 
Aggregator' for ";
      public const string msgPrefix = "3BD50098E401463AA228377848493927";
      private static int msgIdCounter = 0;

      /* AddAlert 
       * ========
       * Called by Web page UI. Sends email message 
       * with SOAP x-headers to user's Outlook client, 
       * telling it to add us to its subscriptions. 
       * For this sample service, note that once email
       * address is entered, it cannot be changed. */
      [WebMethod]
      public AddAlertErrorType AddAlert(string emailAddr, string username, string URL)
      {
         string userID = IdentifyUser(); // First, authenticate
         if (emailAddr.Equals("")) return AddAlertErrorType.EmailMismatch;

         // Then add alert and user info to our database
         try 
         {
            string commandString = "SELECT ulIndex,EmailAddress FROM UserList "
               + "WHERE UserID ='" + userID + "'";
            SqlCommand command = connection.CreateCommand();
            command.CommandText = commandString;
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = command;
            DataSet userlist = new DataSet();
            DataSet usersubs = new DataSet();
            DataSet feeds = new DataSet();
            connection.Open();
            string currIndex="";
            DataTable datatable;
            
            // if this user and email address does not yet exist 
            // in UserList table, add it
            if (adapter.Fill(userlist, "UserList") == 0) 
            {
               commandString = "INSERT INTO UserList (UserID, EmailAddress) " 
                  + "VALUES ('" + userID + "', @EmailAddress)";
               command.CommandText = commandString;

               command.Parameters.Add("@EmailAddress", SqlDbType.VarChar, 256).Value = emailAddr;

               adapter.SelectCommand = command;
               adapter.Fill(userlist, "UserList");
               commandString = "SELECT ulIndex FROM UserList WHERE EmailAddress =@EmailAddress "
                  + "AND UserID ='" + userID + "'";
               command.CommandText = commandString;

               // command.Parameters still includes emailAddr

               adapter.SelectCommand = command;
               adapter.Fill(userlist, "UserList");
               datatable = userlist.Tables["UserList"];
            }
            else 
            {
               string em ="";
               datatable = userlist.Tables["UserList"];                   
               foreach (DataRow row in datatable.Rows) em = row["EmailAddress"].ToString();
               if (!emailAddr.Equals(em)) return AddAlertErrorType.EmailMismatch;
            }
            foreach (DataRow row in datatable.Rows) currIndex = row["ulIndex"].ToString();
            commandString = "SELECT usIndex FROM UserSubscriptions WHERE Location =@location "
               + "AND ulIndex ='" + currIndex + "'";
            command.CommandText = commandString;

            command.Parameters.Clear();
            command.Parameters.Add("@location", SqlDbType.VarChar, 256).Value = URL;
            
            adapter.SelectCommand = command;
            int matchingSubs = adapter.Fill(usersubs, "UserSubscriptions");

            // Now check if user already has this subscription
            // and return error page if he or she does
            if (matchingSubs > 0) 
            {
               connection.Close();
               return AddAlertErrorType.AlreadyExists;
            }
            if (matchingSubs < 0) 
            {
               connection.Close();
               return AddAlertErrorType.DatabaseUpdateError;
            }
            else // add this subscription
            {
               // First add to UserSubscriptions table
               commandString = "INSERT INTO UserSubscriptions (ulIndex, Name, Location, Title, LastSent) " 
                  + "VALUES ('" + currIndex + "', @username, @location, @location, CURRENT_TIMESTAMP)";

               // Note we use CURRENT_TIMESTAMP because we want to start compiling from current time
               command.CommandText = commandString;

               // command.Parameters still includes URL
               command.Parameters.Add("@username", SqlDbType.VarChar, 256).Value = username;
               
               adapter.SelectCommand = command;
               adapter.Fill(usersubs, "UserSubscriptions");
            }

            connection.Close();
         } 
         catch (Exception ex) 
         { 
            connection.Close();
            return AddAlertErrorType.DatabaseUpdateError; 
         }
 
         // Send email confirmation regarding new subscription
         // if one was successfully created
         try
         {
            MailMessage Message = new MailMessage();
            Message.To = emailAddr;
            Message.From = ConfigurationSettings.AppSettings["senderEmailAddr"];
            Message.Subject = "You have successfully created an alert for " + URL + ".";
            Message.Headers.Add("X-AlertWebUrl", ConfigurationSettings.AppSettings["alertweburl"]);  
            Message.Headers.Add("X-AlertTitle",AlertTitle);
            string msgId = "<" + msgPrefix + (msgIdCounter++).ToString() + "@microsoft.com>";
            Message.Headers.Add("message-id",msgId);
            Message.Headers.Add("X-AlertId","{D32A01E6-8A46-41BF-8E0E-936F254ACA4A}");
            Message.Headers.Add("X-AlertServerType","Third Party");
            Message.Headers.Add("X-AlertWebSoap",ConfigurationSettings.AppSettings["webserviceurl"]);

            Message.Body = "Hello, " + username + ".\n\n" + msgBody + URL + ".\n\n- RSS Sample 
Web Service";
            try   
            {
               SmtpMail.SmtpServer = ConfigurationSettings.AppSettings["smtpserver"];
               SmtpMail.Send(Message);
            }
            catch(System.Web.HttpException ehttp)
            {
               return AddAlertErrorType.HttpException;
            }
         
         }
         catch(System.Exception e)
         {
            return AddAlertErrorType.SMTPSendError;
         }
         return AddAlertErrorType.None;

      }
   }
}

Conclusion

The Alerts Web Service provides a new level of integration between Microsoft Office Outlook 2003 and other collaborative applications. These applications can now send alerts to their users of events through e-mail. Users, in turn, can read and organize their alert e-mail messages in Outlook. This new functionality can greatly improve the efficiency of many collaborative processes.

© Microsoft Corporation. All rights reserved.