By Jim Kleewein and Ray Dixon
The SQL Server Tables and Exchange Web Services sample demonstrates a powerful integration of Microsoft® Exchange Server 2007 and Microsoft SQL Server™ 2005 features. This integration enables you to provide data from both Microsoft Exchange and SQL Server to client applications so that the data appears as if it were stored in SQL Server. As you will see, this creates some exciting development scenarios.
The SQL Server Tables and Exchange Web Services sample takes advantage of two powerful new features in SQL Server and Microsoft Exchange: table-valued user-defined functions, and Exchange Web Services. Table-valued user-defined functions in SQL Server return a table, much like a SQL Server view, but provide for much more powerful logic. Exchange Web Services in Exchange 2007 provides access to Exchange store items and information, such as message, contact, task, and calendar items and availability information. When you combine these technologies, you have a formidable new way to integrate, search, and analyze your Microsoft Exchange messaging data by using the full power of SQL Server, and to combine that messaging data with virtually any other data managed by, or available from, SQL Server. For example, by combining these technologies, you can:
This list provides some ideas, but is not all-inclusive. I'm sure that you will find more ideas for your environment as you explore the sample.
Note This sample is intended for instructional purposes only and is not meant to be used in a production environment.
The following are the prerequisites for the SQL Server Tables and Exchange Web Services sample:
The following figure shows the process that the SQL Server Tables and Exchange Web Services sample uses.
SQL Server Tables and Exchange Web Services process flow
The process flow for the sample involves the following steps:
The SQL Server Tables and Exchange Web Services sample includes common files and Inbox folder–related, calendar-related, and contact-related files.
The following table lists the common files that are included in the sample.
Contains basic information about how to set up the sample.
Contains SQL Server statements that will register the functions on the computer that is running SQL Server and create common views.
You must edit this file to substitute the correct path of the ExchangeUDFs.dll file and the correct database name.
Contains SQL Server statements that will clean up all the SQL Server artifacts that are registered by the setup.sql file.
You must edit this file to substitute the correct database name.
Contains SQL Server statements that invoke functions and views that are created by the setup.sql file.
You must edit this file to remove the SQL Server comments around the SQL Server statements that you want to execute.
Contains information that the nmake tool uses to compile the sample.
Contains the four common classes:
The following table lists the Inbox folder–related files that are included in the sample.
Contains the classes and methods that are used to access basic information about messages in the Inbox folder of the mailbox.
The following table lists the calendar-related files that are included in the sample.
Contains classes and methods that are used to access basic availability (that is, free/busy) information.
Contains classes and methods that are used to access basic information about calendar items.
Contains classes and methods that are used to access calendar item attachments.
Contains classes and methods that are used to access information about calendar attendees.
Contains classes and methods that are used to access information about calendar items that are next to other calendar items, such as an appointment that immediately comes before or follows a meeting.
Contains classes and methods that are used to access information about calendar items that conflict with other calendar items, such as two appointments that start at the same time.
The following table lists the contact-related files that are included in the sample.
Contains classes and methods that are used to access basic information about contact items.
Contains classes and methods that are used to access the postal addresses of contact items.
Contains classes and methods that are used to access the e-mail addresses of contact items.
The following table lists and describes the classes that are used in the sample. Each class is defined in the common.cs file.
An abstract class that implements the IEnumerable interface. This class builds the Web service request and submits the request to the Microsoft Exchange server. This class also determines whether the Web service response is an error. If the response is not an error, the class parses the response to find nodes that represent rows in the resulting table and returns an enumerator that allows you to iterate through those rows.
Implements the IEnumerable interface and is used to return the rows from the Web service response.
Contains a reference to an instance of the ExchangeWebServiceHelper and a reference to the node that represents the row in the Web service response.
SQL Server invokes instances of classes derived from this abstract class to produce sets of values to be treated as SQL Server tables.
The SQL Server Tables and Exchange Web Services sample provides some table-valued functions that enable you to access some key Microsoft Exchange messaging objects (Inbox, calendar, and contacts). Some sample functions are very simple (Inbox), while others are more complex (calendar attachments). The sample functions fall into two categories based on how they invoke Exchange Web Services: either by performing a single Web service invocation, or by performing multiple Web service invocations. Sample functions, such as Inbox, that perform a single Web service invocation produce all the necessary results by invoking a single Web service. Sample functions that perform multiple Web service invocations first produce a summary set of information with a single Web service invocation, and then get detailed information by performing a Web service invocation to get details for each member of the set. The CalAdjacent sample performs multiple Web service invocations.
It is important to be aware of the following limitations to the sample:
This sample uses the following authentication models:
When the SQL Server authentication model is used, the sample authenticates to the Microsoft Exchange server by using the credentials of the SQL Server process. With this authentication model, the computer that is running SQL Server must run under an account that has permission to access the Microsoft Exchange mailbox that you want to work with. The SQL Server authentication model works best if you only want to work with a single mailbox and you can run your instance of SQL Server by using the credentials necessary to access that mailbox.
When the SQL Server user authentication model is used, the sample authenticates to the Microsoft Exchange server by impersonating the credentials of the SQL Server user. With this authentication model, the computer that is running SQL Server must run under an account that is trusted for Kerberos delegation, and the SQL Server user must have access to the mailbox that you want to work with.
When the user ID with password authentication model is used, the sample authenticates to the Microsoft Exchange server by using an explicit user name and password that allows the sample to access the mailbox that you want to work with.
The following figure shows the basic logic flow for the sample.
SQL Server Tables and Exchange Web Services logic flow
The logic flow for the sample involves the following steps:
If the example requires more detail than is provided by the initial InitMethod invocation, when the computer that is running SQL Server iterates through the enumerationNodes, it will call into the helper to make another Web service call. For example, calling the CalAdjacentInitMethod method will get a list of calendar entries that are next to the entry specified. For each entry in that list, the method may then call another method to get and return the details for that calendar entry.
The sample also includes an example that requires three Web service calls: an outer, an inner, and a detail. For example, to get information about calendar attachments, you must:
This sample is intended to educate and inspire you in your Microsoft Exchange development. In this section, I'll introduce some ideas that you might consider for extending the sample.
Exchange Web Services enables you not only to read data, but also add and update data in the Exchange store. SQL Server 2005 includes INSTEAD OF triggers that enable you to change read-only views (such as those over table-valued user-defined functions) into read/write views by supplying logic to execute instead of a typical SQL Server insert/update/delete operation. The combination of these technologies enables you to update Microsoft Exchange data by using SQL Server.
The authentication models used in this sample enable a mailbox owner to access a single mailbox. To access other users’ mailboxes, you must change the sample to accommodate either delegate access or Exchange impersonation. These mechanisms supported by Exchange Web Services allow trusted accounts to impersonate or act on behalf of users without using Kerberos impersonation. You can extend the sample to use these mechanisms to enable complex and powerful operations to access more than one mailbox.
You may find it useful to access message item attachments in the same way that the sample accesses calendar item attachments. You can also implement the ability to access the attachments for any item that you can access via Exchange Web Services.
The sample shows you how to access information about the Inbox folder. You can adapt this code to implement classes that access any available folders.