Using SQL Server Table-Valued User-defined Functions with Exchange Web Services
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.
Overview of the Sample
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:
- Correlate information that is stored in Microsoft Exchange (messages, contacts, calendar items, tasks, and so on) with customer relationship management (CRM) data that is stored in SQL Server.
- Join Microsoft Exchange contacts data with sales data.
- Combine messaging data with any SQL Server data; for example, combine e-mail with call center data for customer support.
- Correlate information from different calendar resources.
- Add a subset of messaging data to a SQL Server data repository.
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.
Prerequisites
The following are the prerequisites for the SQL Server Tables and Exchange Web Services sample:
- A computer that is running Exchange 2007 that has the Client Access server role enabled
- SQL Server 2005
- Microsoft Visual Studio 2005 (to build or modify the sample)
Process Flow
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
.gif)
The process flow for the sample involves the following steps:
- The user requests information from the computer that is running Microsoft SQL Server.
- The computer that is running SQL Server acts as a Microsoft Exchange client by requesting data from the Client Access server by using Exchange Web Services.
- The Client Access server communicates with the Microsoft Exchange computer that is running the Unified Messaging server role or the user to request the specified data from the mailbox for the user.
- The Unified Messaging server responds to the request.
- The Client Access server returns the response to the computer that is running SQL Server by using Exchange Web Services.
- The computer that is running SQL Server combines the requested Microsoft Exchange data with the requested SQL Server data and returns the single set of data to the user.
Files
The SQL Server Tables and Exchange Web Services sample includes common files and Inbox folder–related, calendar-related, and contact-related files.
Common Files
The following table lists the common files that are included in the sample.
File |
Description |
readme.txt |
Contains basic information about how to set up the sample. |
setup.sql |
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. |
cleanup.sql |
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. |
run.sql |
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. |
makefile |
Contains information that the nmake tool uses to compile the sample. |
common.cs |
Contains the four common classes:
|
Inbox Folder–Related Files
The following table lists the Inbox folder–related files that are included in the sample.
File |
Description |
inboxBase.cs |
Contains the classes and methods that are used to access basic information about messages in the Inbox folder of the mailbox. |
Calendar–Related Files
The following table lists the calendar-related files that are included in the sample.
File |
Description |
availabilityBase.cs |
Contains classes and methods that are used to access basic availability (that is, free/busy) information. |
calBase.cs |
Contains classes and methods that are used to access basic information about calendar items. |
calAttachment.cs |
Contains classes and methods that are used to access calendar item attachments. |
calAttendee.cs |
Contains classes and methods that are used to access information about calendar attendees. |
calAdjacent.cs |
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. |
calConflicting.cs |
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. |
Contact-Related Files
The following table lists the contact-related files that are included in the sample.
File |
Description |
contactBase.cs |
Contains classes and methods that are used to access basic information about contact items. |
contactAddress.cs |
Contains classes and methods that are used to access the postal addresses of contact items. |
contactEmail.cs |
Contains classes and methods that are used to access the e-mail addresses of contact items. |
Classes Used
The following table lists and describes the classes that are used in the sample. Each class is defined in the common.cs file.
Class |
Description |
ExchangeWebServiceHelper |
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. |
ExchangeEnumerator |
Implements the IEnumerable interface and is used to return the rows from the Web service response. |
enumerationNode |
Contains a reference to an instance of the ExchangeWebServiceHelper and a reference to the node that represents the row in the Web service response. |
ExchangeService |
SQL Server invokes instances of classes derived from this abstract class to produce sets of values to be treated as SQL Server tables. |
Types of Sample Functions
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.
Limitations
It is important to be aware of the following limitations to the sample:
- Microsoft Exchange allows properties to have varying sizes. In this sample, the default field sizes, especially for SQL Server varchar and varbinary data types, may be smaller than the data retrieved from the Microsoft Exchange server. If you experience truncation errors, you may have to increase the size of the class property.
- This sample does not show you how to access message folders other than the default Inbox folder. You must modify the sample in order to access mailbox folders other than the Inbox folder.
Authentication Models
This sample uses the following authentication models:
- SQL Server authentication
- SQL Server User authentication
- User ID with Password authentication
SQL Server Authentication
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.
SQL Server User Authentication
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.
User ID with Password Authentication
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.
Sample Logic Flow
The following figure shows the basic logic flow for the sample.
SQL Server Tables and Exchange Web Services logic flow
.gif)
The logic flow for the sample involves the following steps:
- SQL Server invokes the InitMethod static method for the type of item being requested. For example, the InitMethod used for the Inbox examples is named InboxInitMethod.
- The instance of the helper class builds the Web service request, submits the request to the Microsoft Exchange server, gets and verifies the Web service response, and remembers the set of XML nodes that contain the rows to be returned.
- The InitMethod returns a new instance of the helper class for the item being requested. For example, invoking the InboxInitMethod returns a new instance of the ExchangeInboxHelper class.
- SQL Server iterates through the results in the returned helper class instance, and returns an enumerationNode for each iteration until it comes to the end of the nodes.
- For each enumerationNode instance, SQL Server invokes the FillMethod for the item being requested, passing the enumerationNode.
- The FillMethod uses the passed enumerationNode to retrieve the subnodes, each of which represents a column, from the XML node that represents the row. The method then puts the subnodes in a form that SQL Server can understand and returns them to SQL Server.
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:
- Get a list of calendar items.
- Get the list of attachments for each calendar item.
- Get the details of each attachment.
Extending the Sample
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.
Updating Exchange Data
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.
Accessing Multiple Mailboxes
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.
Accessing Message Attachments
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.
Accessing Any Folder
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.
See Also
Microsoft Exchange Server Developer Center