Write a Custom Outlook Add-In with Visual Studio Tools for Office : Page 2

Get Data from a Web Service
In the Add-in project, you need to add a Web reference to a Web service that will provide the stock quotes to the application.

To do this, right click on the 'References' node in the solution and select 'Add Web Reference'. In the ensuing dialog, use the following WSDL declaration:

  http://services.xmethods.net/soap/
      urn:xmethods-delayed-quotes.wsdl

Name the service quoteservice. Visual Studio will generate a proxy to this Web service for you.

Next you'll need a database that contains your portfolio of stocks that the Web service will use to check closing prices. If you are using SQL Server here's a generate script that you can use to create an appropriate database:

   USE [Portfolio]
   GO
   /****** Object:  Table [dbo].[stocktriggers]    Script Date: 06/19/2006 06:51:34 ******/
   SET ANSI_NULLS ON
   GO
   SET QUOTED_IDENTIFIER ON
   GO
   CREATE TABLE [dbo].[stocktriggers](
      [id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
      [stockticker] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [hightrigger] [decimal](18, 0) NULL,
      [lowtrigger] [decimal](18, 0) NULL
   ) ON [PRIMARY]

The preceding script creates a simple database table containing three fields where you can enter a stock ticker and your high and low triggers. So, for example, MSFT at time of writing was quoted at 22.20, so you might set a low trigger at 15 and a high trigger at 30. For this application, if MSFT falls below 15 or rises above 30, Outlook will set an appointment alert in your calendar to call your broker, because you may want to buy low or sell high.

When you've set up the database and entered some stock ticker symbols and triggers, you're ready to start programming.

Building the Application
The first step is to add a new DataSet to your application and connect this to the table you created in the previous section. Of course, you don't have to use SQL Server; you could, for example, use an XML file. However, the code in this section is based on using a database, and using a Dataset object to connect to it.

Your new Outlook add-in project will contain a class called "ThisApplication" by default, and it will have two event handlers set up by default: ThisApplication_Startup and ThisApplication_Shutdown. You'll be coding in the ThisApplication_Startup event handler.

First, you'll need to connect to the data set using the TableAdapter that Visual Studio created when you added the DataSet to the designer:

   folioTableAdapters.stocktriggersTableAdapter da = new
      folioTableAdapters.stocktriggersTableAdapter();
   folio.stocktriggersDataTable dt = da.GetData();

At this point, you have a DataTable object that you can iterate through to get your data. I'll get back to that in a moment. Before you get to that point, you'll need to create an instance of the Web service proxy:

   quoteservice.netxmethodsservicesstockquoteStockQuoteService 
      quote = new quoteservice.
      netxmethodsservicesstockquoteStockQuoteService();

Next, you need to iterate through the records in the DataTable, getting the current quote for each stock symbol. If the current quote is above or below the trigger criteria, you will add text to a StringBuilder that will form the body of your calendar entry.

   foreach(folio.stocktriggersRow theRow in dt.Rows)
   {
     float q = quote.getQuote(theRow.stockticker.Trim());
     Decimal d = (Decimal)q;
     if (d > theRow.hightrigger)
     {
       strMessage.Append(theRow.stockticker.Trim());
       strMessage.Append(" has crossed over your high " + 
          "trigger for it.");
       ...
     }
     if (d < theRow.lowtrigger)
     {
       strMessage.Append(theRow.stockticker.Trim());
       strMessage.Append(" has crossed beneath your " + 
          "low trigger for it.");
       ...
     }
   }

Author's Note: The string aggregation has been considerably abridged in the preceding code, but you can see the full listing in the downloadable code for this article.

A DataTable object contains a collection of rows. The preceding code iterates through the Rows collection, assigning a folio.stocktriggersRow object to the current row. That object has public members corresponding to the columns in the database. So, to get the stock ticker for the current row you just use:

   theRow.stockticker

The string you get will be padded with spaces, so before you call the Web service you'll need to remove this using the Trim() method. You retrieve the hightrigger and lowtrigger values for the current row in a similar manner. If the current quote falls outside either of these values, the code appends text to the strMessage StringBuilder instance.

Previous Page

Next Page