Programming Microsoft Outlook and Microsoft Exchange, Second Edition (DV-MPS Programming)

There is a wealth of other components you can use as the basis for your information nuggets. In this section, we'll examine using the Office 2000 Web Components, the Outlook View control, and the Outlook Databinding control to build information nuggets. We'll also look at using Microsoft ActiveX Data Objects (ADO) as components for accessing data in your nuggets.

Office Web Components

Office 2000 enables you to publish spreadsheets, charts, and databases to the Web via a set of COM controls called the Office Web Components. You can use these components in any container that supports the hosting of ActiveX controls. However, you'll typically use these controls inside Web applications as client components, or in ASP applications to generate charts and graphs for your Web applications. The Finance Digital Dashboard that you saw earlier in the chapter used the Office Web Components.

This set contains four controls, called components: Data Source, Spreadsheet, PivotTable, and Chart. These components have an object library that you can program with to create applications. We'll quickly look at each of these components in this section; for more information, see the Office 2000 documentation.

Data Source Component

The Data Source component allows you to connect to external data sources to retrieve information for the other components to display. Although this control is invisible, it can establish data bindings to external data sources such as SQL Server or OLAP cubes. Usually when you create interactive Web applications using Office 2000, this control is automatically added and set up for you.

Spreadsheet Component

This component is like a miniature version of an Excel spreadsheet. It has a similar user interface and can support many of the functions that Excel supports. The Spreadsheet control enables you to load data programmatically using the Data Source control or any URL that points to an Excel spreadsheet saved as HTML. Once you retrieve the data, you can sort or filter it according to your application needs.

The Spreadsheet control is useful in applications in which you want a grid-based data entry model and calculation functionality such as expense reports or budgets. This component even supports real-time updates that you could use to display real-time information such as stock prices that are being fed from the Internet.

PivotTable Component

The PivotTable component is a small version of the Excel PivotTable. This component can retrieve data from OLE DB data sources or even OLAP data sources. Users of your application can model their data by filtering, grouping, and slicing it in numerous ways. One of the nice features of the PivotTable component is that you can link it with the Chart component (discussed next) so that when a user slices the data, the Chart component automatically updates to reflect the filtering or grouping that the user has selected.

Chart Component

The Chart component is the most flexible of the Office Web Components. The Chart component provides charting functionality similar to that of Excel. It can retrieve its data either by being filled with literal data, or by connecting to an ADO recordset or to the Spreadsheet or PivotTable components. As implied earlier, once you bind the Chart control to a data source, it will change whenever that data source is updated.

In addition to using the Chart control as a client-side component, you can use it as a server-side component in ASP applications. Many Web applications strive to provide data charting capabilities. Before the Chart control was developed, you either had to buy a third-party tool to chart information or you had to try automating Excel on your Microsoft Internet Information Services server, which is not a good idea. The Chart control enables you to connect to a data source in your ASP, chart the information, and then export that chart as a GIF image that you can embed in your web page. The image won't be as interactive as if you'd run the Chart component client-side in your application, but this solution works for browsers that do not support running ActiveX controls.

WARNING

Outlook View Control

I won't cover the View control extensively here because we looked at it in great detail in Chapter 10. The View control is a great source of information for the nuggets you create for your Digital Dashboard. For more information on this control, refer back to Chapter 10.

Outlook Databinding Control

In Outlook Today, Outlook uses an ActiveX data source control similar to the Data Source control of the Office Web Components—the Databinding control. The difference is that the Outlook Databinding control works only in Outlook and can connect only to Outlook data. Furthermore, the Databinding control supports only a limited subset of fields from an Outlook data source. However, you can use the Databinding control within your Digital Dashboard to display nuggets of Outlook information. Figure 11-14 shows a not-so-pretty but functional example of hosting the Outlook Databinding control in a folder home page.

Figure 11-14. The Outlook Databinding control in a folder home page.

The number 4 appearing on the calendar is actually a placeholder for the arrow that normally points to the user's next appointment. You can use cascading style sheets (CSS) to format this data so that it appears as it does in the standard Outlook Today page. The code for this example page follows:

The code creates three Outlook Databinding objects of the same type that have different module parameters. One object points at the calendar, another at the tasks folder, and the third at the Inbox. The nice thing about using databinding objects is that if the underlying Outlook folder changes, you don't need to refresh the page—the databinding objects automatically refresh it for you.

These three databinding objects correspond to the three HTML tables in the code. Each of the HTML rows has a <DIV> tag with a DATAFLD element. This DATAFLD element tells the Databinding control which field to place in that row of the table. The data sources of the Outlook Databinding control are limited to the fields shown in this table:

NOTE

You can customize the data source objects for the Calendar and Task data sources to point to specific folders. Doing so allows you to quickly display public calendar or task information in your nuggets. To customize the data source objects, add the Path parameter to the object tag for the control. The Path parameter takes as its value the path to the folder to which you want the Databinding control to bind. The following example points the Databinding control at a calendar folder:

ActiveX Data Objects

ADO is a high-level interface to OLE DB data sources. You can use ADO to connect to a multitude of data sources that have OLE DB providers. In this section, we'll briefly look at using ADO to connect to SQL databases. This section covers ADO 2.1 only. When we discuss Exchange 2000 Server in Chapter 18, we'll examine ADO 2.5 and its new features, one of which is the ability to understand hierarchical data sources such as those used in Exchange 2000 Server. In Chapter 18, we'll also discuss using ADO 2.5 and the Exchange 2000 Server OLE DB provider to connect to Exchange 2000 Server data sources.

The ADO object model consists of seven objects—Connection, Command, Recordset, Parameter, Field, Property, and Error—and four types of collections—Fields, Properties, Parameters, and Errors. We won't cover each of these objects and collections extensively in this chapter. Instead, I'll quickly walk you through the basics of connecting to OLE DB data sources from your Digital Dashboard. For more information on ActiveX Data Objects, refer to the ADO documentation.

Connection Object

Before you can manipulate data in a database, you need to connect to that database. The Connection object enables you to do this. You specify your connection criteria, such as the name of the database and the server it resides on, and then you attempt to connect. The following code sample shows how to create a connection object, set the connection properties, and then open the connection to the database:

NOTE

Recordset Object

Once you have a connection, you can create a Recordset object that will contain the records you want to retrieve from the data source. If the data source supports different cursor and lock types, you can specify them—for example, in the Open method. An example of a cursor type is a static cursor type, which allows you to see only a snapshot of the data; any additions, changes, or deletions made to the data source after you open the Recordset object won't be visible. Two lock type examples are optimistic and pessimistic locking. You can also pass a SQL query to the Open method on the Recordset object to filter or sort the Recordset before it returns to the server or client. For more information on this, take a look at the ADO documentation or Programming ADO (Microsoft Press, 2000).

The following code sample creates an ADO Recordset object, opens the user's table from the MyDB database used in the previous code, and closes both the Recordset and the Connection. You should close both when you complete your set of functions on each object.

Once you open your Recordset, you'll probably want to retrieve data from it. ADO provides a Fields collection that represents the columns in a row from the data source. Using the Fields collection, you can quickly and easily display data from a database in your Digital Dashboard. The following code uses the Fields collection to retrieve information from the database:

In addition to displaying information from the Recordset, you might want to move from one row to another in the Recordset object. ADO provides five methods that allow you to do this: Move, MoveFirst, MoveLast, MoveNext, and MovePrevious. The Move method moves the number of records that you specify from the current record. The MoveFirst and MoveLast methods move the cursor to the first or the last row of the Recordset, respectively. MoveNext and MovePrevious methods move the cursor to the next or the previous row, respectively.

For each of these methods, you need to use the BOF and EOF properties. The BOF property is a Boolean that specifies whether the current cursor position precedes the first record in the Recordset. The EOF property is a Boolean that specifies whether the current cursor position follows the last record in the Recordset. The following example shows how you can add navigation buttons to your application by using some of these methods and properties:

You can use the Filter property to filter the rows returned in the Recordset. You pass to the Filter property a criteria string that specifies the filter you want to impose on the Recordset. You'll probably use the Filter property with the RecordCount property, which specifies how many records the Recordset contains. The following example sets the Filter property to filter only users from the United States. It also displays a warning if the value of the RecordCount property is 0. You can disable the filter by setting the Filter property to the constant adFilterNone.

Command Object

You can use the Command object to build and execute commands on your data source. These commands can return Recordsets, execute bulk operations, or modify the structure of the data source. In your Digital Dashboard, your Command object will usually be returning Recordsets. The Command object has six properties—ActiveConnection, CommandText, CommandTimeOut, CommandType, Prepared, and State—and three methods—Cancel, CreateParameter, and Execute.

You'll probably use only the CommandText, CommandTimeOut, CommandType, and Prepared properties. CommandText contains a string that is a SQL statement or the name of a stored procedure you want to run on the data source. CommandTimeOut indicates how long, in seconds, you want the code to wait for the command to execute before terminating the attempt and displaying an error.

The CommandType property is a string that specifies the type of command contained in the CommandText property. CommandType is provided to optimize performance so that ADO doesn't have figure out the type of command you want to perform. This property can have the following values: adCmdText, indicating that CommandText contains a SQL statement; adCmdStoredProc, indicating that CommandText contains the name of a stored procedure; or adCmdUnknown, if CommandText is of unknown form.

The Prepared property is a Boolean value that specifies whether the provider should compile the command for reuse. By setting this property to True for commands you plan to reuse, you'll boost performance.

The Cancel method cancels the command that's currently executing. The CreateParameter method creates a Parameter object that you can use with stored procedures. The Execute method executes the command, whether it's a stored procedure or a SQL statement. Let's take a closer look at the capabilities of this method.

Execute Method

You can use the Execute method directly on the Connection object to add, delete, or update data in your data source or in methods on the Recordset object. We'll discuss both techniques so that you can decide which one works best for your applications.

Updating records using the Connection object requires you to write some SQL code. The following code snippet shows how to update the Users database to change the type of user:

You can achieve the same result with the Recordset object by scrolling through each record and updating the field manually, as shown here:

Inserting new records can again be achieved with a SQL statement on the Connection object. On the Recordset object, the object model supplies the AddNew method for inserting new records. The following code example shows how to use the Execute method on the Connection object to create a new record:

This code sample uses the Recordset object to achieve the same result:

Deleting a record yet again can be achieved by using a SQL statement for the Connection object, while the Recordset object provides the Delete method, which deletes the current record in the Recordset. The following code shows how to delete a record by using the Connection object:

Using the Delete method on the Recordset object will delete the current record. Make sure that you move the Recordset to a valid record after performing the deletion. The following code deletes the current record and then moves the Recordset to a valid record:

This has been a quick overview of using ADO to access and manipulate relational database records. We'll take a closer look at using ADO with Exchange 2000 Server in Chapter 18. For more information on using ADO with SQL Server databases, refer to the ADO documentation.