Using ADO 2.5 and OLEDB 2.5 with Exchange Server 2000

[Previous] [Next]

While Windows 2000 ships with OLEDB 2.5 and ADO 2.5, Exchange Server 2000 includes the OLEDB 2.5 provider for Exchange Server. If you've done any development with Microsoft SQL Server, you know how easy to use and powerful the ADO object model is. The OLEDB provider in Exchange Server 2000, called EXOLEDB, capitalizes on this ease and power. In this section, we'll take a quick look at some of the new features in ADO 2.5. Then, using the Training application, we'll examine how to build ADO applications with Exchange Server.

Note that the OLEDB provider should be used only server-side with Exchange Server 2000. Do not attempt to use the provider remotely from client-side applications, including using the new version of CDO on the client-side. For those applications that require client-side interaction, you can leverage WebDAV or previous versions of CDO or the Outlook Object Model.

New ADO 2.5 Features

Two major enhancements of ADO 2.5 should be of interest to Exchange Server developers: URL binding support, and Record and Stream support. These enhancements are why ADO 2.5 works so well with the type of data stored in an Exchange Server application. There are two reasons these enhancements make using ADO with Exchange Server easy:

• Exchange Server is best at storing semistructured data rather than structured, relational data.
• Exchange Server supports URL addressing for every record in the Exchange Server database.

URL Binding Support

In my opinion, one of the most annoying things about the earlier versions of ADO is all the setup work required to open a data source connection. You have to create a Connection object, set the properties on that object, and then issue a query and retrieve your Recordset from the data source. However, ADO 2.5 lets you bypass all the setup work. You simply provide a URL for the data you want to retrieve, and OLEDB sets up all the connections and manages all the security for you. Following is an example of binding directly to an Exchange Server resource with this new URL support:

 'You can achieve the binding like this: Set oRec = CreateObject("ADODB.Record") oRec.Open "http://server/public/myfolder/myitem", , _ adReadWrite, adOpenIfExists 'Or with one statement, like this: oRec.Open "file://./backofficestorage/domain/" & _ "public folders/myfolder/myitem", , adReadWrite, _ adOpenIfExists 

Notice how I don't have to create a Connection object. OLE DB does this automatically. The technology that makes this URL binding work is the new Root Binder in OLE DB 2.5. The Exchange Server OLE DB provider and other OLE DB providers register themselves with the Root Binder, specifying which types of URLs they support. For example, the Exchange Server OLE DB provider registers for the URL "file://./backofficestorage". Using the file protocol, you can access mailboxes and Public Folders via "file://./backofficestorage/domain/public" folders/myfolder.

Be aware that EXOLEDB does not register for the URLs that begin with http://. However, the Microsoft OLE DB Provider for Internet Publishing does. Therefore, if you want to use http:// in the URLs to connect to Exchange Server data, you must explicitly specify that you want to use the EXOLEDB provider. To do so, you might want to create or grab an explicit Connection object that sets the provider to EXOLEDB and then use that object in your ADO methods. In order to work with Exchange Server using the EXOLEDB provider and http:// URLs, the code similar to the code just shown now looks like this:

 Set oConnection = CreateObject("ADODB.Connection") 'Open a connection to the Exchange server by using a throwaway 'Record Set oThrowAwayRec = CreateObject("ADODB.Record") oThrowAwayRec.Open "http://server/public/" set oConnection = oThrowAwayRec.ActiveConnection oConnection.Provider = "EXOLEDB.DataSource" Set oRec = CreateObject("ADODB.Record") oRec.Open "http://server/public/myfolder/myitem", _ oConnection, adReadWrite, adOpenIfExists 

NOTE

---

There are other ways to create the Connection object. Keep in mind that if you want to use http:// URLs, you have to make sure the provider is EXOLEDB. Also, notice that to get at Public Folders, you do not use Public Folders in the http:// URL like you do the File:// URL. Instead, Exchange automatically creates a public virtual directory that points at your Public Folders. If you create other top-level hierarchies, and you are going to access folders using the http:// URL, you will want to create virtual directories that point at the hierarchy that contains those folders.

If you plan to use OLE DB transactions in your code, you might want to continue to explicitly create Connection objects. In ADO, you accomplish this by using the BeginTrans, CommitTrans, and RollbackTrans methods.

Because you'll be using URLs extensively with Exchange Server 2000, there is one property that you should commit to memory quickly: the DAV:href property, which is available on every item in Exchange Server. This property contains the URL to the associated item, making it easy for you to retrieve the URL and pass it to procedures or functions in your application.

Record and Stream Support

Previous versions of ADO allowed you to create only Recordsets. Yet ADO 2.5 enables you to create Records and Streams. Creating Recordsets is useful for relational data sources because Recordsets are very "rectangular," meaning that the data in the data sources will be uniform. However, nonrelational data sources such as Exchange Server are not very rectangular in nature. In fact, one row in an Exchange Server database could have many more or many less columns than the next row, since Exchange Server provides very flexible schema support even within a table (or folder, as it's called in Exchange Server). This is why creating Records and Streams of information is a welcome addition to ADO 2.5.

Record object

In previous versions of ADO, if you wanted to open a single row, you had to create an entire Recordset to contain that single row. However, with Record support, ADO allows you to pass the URL to a unique item in Exchange Server, and without requiring you to create a Connection or Recordset object, ADO opens that item into your Record object. You can then use the properties and methods of the Record object to manipulate the data. You'll see the Record object used extensively throughout the code samples for the Training application shown later in the chapter.

Stream object

You can use the Stream object to manipulate streams of data, either text or binary. An Exchange Server message is a good example of a stream. When you retrieve the stream of a message, you're given the entire message, its headers, and its content as serialized data. You can then manipulate the data using the methods on the Stream object. Later in Chapter 19 when we look at how to set up the workflow process definition programmatically, you'll see an excellent example of working with streams.

Putting ADO 2.5 to Work with Exchange Server 2000

You'll predominantly use ADO 2.5 to access data in Exchange Server. Exchange Server supports the major features of ADO, including the ability to perform queries with clauses such as WHERE or ORDER BY. However, there is one major restriction for issuing SQL queries against Exchange Server: Exchange Server does not provide JOIN support, meaning you cannot join two Exchange Server folders into a single Recordset.

SELECT Statement

Exchange Server 2000 supports SQL SELECT statements. This is the basic format of a SELECT statement:

 SELECT * | select-list FROM SCOPE( resource-list) [WHERE search-condition] [order-by-clause] 

Remember, you should not use SELECT * if you can avoid it. Instead, pass in the property list that you want to retrieve from Exchange Server as part of your SELECT statement. An example of retrieving a specific set of properties from Exchange Server is shown here. Notice that you will need to place property names in quotes in order to retrieve them.

 'Build the SQL statement strURL = "file://./backofficestorage/domain/public folders/myfolder/" strSQL = "SELECT ""urn:schemas:httpmail:subject"" " & _ "FROM scope('shallow traversal of """ & strURL & _ """') WHERE ""DAV:iscollection"" = false AND ""DAV:ishidden"" = false" 'Create a new Recordset object Set rst = New Recordset With rst 'Open Recordset based on the SQL string .Open strSQL End With If rst.BOF And rst.EOF Then Debug.Print "No Records!" End End If rst.MoveFirst Do Until rst.EOF Debug.Print rst.Fields("urn:schemas:httpmail:subject").value rst.MoveNext Loop 

You'll notice that the SELECT statement contains a SCOPE clause and a WHERE clause. The SCOPE clause identifies the resource at which you want the search to begin, such as the URL of a folder. Exchange Server supports deep and shallow traversals of the scope. Requesting a deep traversal of the URL means that Exchange Server will search not only the specified folder, but also all subfolders it contains. As you'd expect, requesting a shallow traversal means Exchange Server will search only the specified folder.

NOTE

---

Deep traversals are not supported in the MAPI Public Folder top-level hierarchy, but shallow traversals are. Deep traversals are supported in other TLHs, or in Mailbox folders, however. If your application requires deep traversals, this is something you should take into consideration.

With the SCOPE clause, you can request multiple folders as long as the traversal is the same for all the folders. For example, you might want to perform deep traversals of multiple folders that do not have a parent-child relationship. This means that a single-level deep traversal would not search all the folders. Using the SCOPE clause, you can specify multiple URLs to search. The following example searches multiple folders using a shallow traversal. Please note, though, that your traversal must be the same for all URLs and that this technique will not work in the Public Folder hierarchy.

 'Build the SQL statement strURL = "file://./backofficestorage/domain/myTLH/myfolder/Events/" strURL2 = "file://./backofficestorage/domain/myTLH/myfolder/Students/" strSQL = "SELECT ""urn:schemas:httpmail:subject"" " & _ "FROM SCOPE('shallow traversal of """ & strURL & _ """', 'shallow traversal of """ & strURL2 & _ """') WHERE ""DAV:iscollection"" = false AND ""DAV:ishidden"" = false" 

Use of the SCOPE clause is optional. If you do not specify the clause, the shallow traversal is used. When you have a SCOPE clause without a traversal, the default traversal is the deep traversal. The following example illustrates this:

 'Build the SQL statement strURL = "file://./backofficestorage/domain/myTLH/myfolder/Events/" 'Shallow traversal is used strSQL = "SELECT ""urn:schemas:httpmail:subject"" " & _ "FROM """ & strURL & """" 'Deep traversal is used strSQL = "SELECT ""urn:schemas:httpmail:subject"" " & _ "FROM SCOPE('""" & strURL & """')" 

The WHERE clause in your Exchange Server queries can be as complex or simple as you want. The examples above simply check whether the items are collections, which is really a check to see whether the items are folders or whether they are hidden. If either of these checks is true, the item is not returned from the query.

The Training application uses some very complex WHERE clauses in its searches. For example, the following SQL statement is taken from this application's showcourses.asp file, which shows the available courses that a student can take:

 Function TurnintoISO(dDate,strType) 'Format should be "yyyy-mm-ddThh:mm:ssZ" strISO = Year(dDate) & "-" if Month(dDate)<10 then strISO = strISO & "0" & Month(dDate) & "-" else strISO = strISO & Month(dDate) & "-" end if if Day(dDate)<10 then strISO = strISO & "0" & Day(dDate) & "T" else strISO = strISO & Day(dDate) & "T" end if if strType = "End" then 'Make it 23:59:59 PM on the day strISO = strISO & "23:59:59Z" else 'Make it first thing in the morning 00:00:01 strISO = strISO & "00:00:01Z" end if TurnintoISO = strISO End Function 'Figure out the sort order from a QueryString variable if Request.QueryString("SortBy") = "" then strSortBy = """urn:schemas:mailheader:subject""" elseif Request.QueryString("SortBy") = "0" then strSortBy = """urn:schemas:mailheader:subject""" elseif Request.QueryString("SortBy") = "1" then strSortBy = """urn:schemas:calendar:dtstart""" elseif Request.QueryString("SortBy") = "2" then strSortBy = """" & strSchema & "category"", _ ""urn:schemas:mailheader:subject""" else strSortBy = """urn:schemas:mailheader:subject""" end if 'Figure out the date to show from QueryString if Request.QueryString("DateStart") = "" then dDateStart = Date else dDateStart = CDate(Request.QueryString("DateStart")) end if if Request.QueryString("DateEnd") = "" then dDateEnd = Date else dDateEnd = CDate(Request.QueryString("DateEnd")) end if 'Put this date into an ISO format dISODateEnd = TurnintoISO(dDateEnd,"End") dISODateStart = TurnintoISO(dDateStart,"Start") 'Build the SQL statement strSQL = "Select ""urn:schemas:mailheader:subject""," _ & """DAV:href"",""urn:schemas:calendar:dtstart""," _ & """urn:schemas:calendar:dtend"" FROM scope('shallow traversal of """ _ & strScheduleFolderPath & _ """') WHERE (""DAV:iscollection"" = false) AND " & _ "(""DAV:ishidden"" = false) " & _ "AND (""urn:schemas:calendar:dtstart"" >= " & _ "CAST(""" & dISODateStart & """ as 'dateTime'))" & _ "AND (""urn:schemas:calendar:dtstart"" <= " & _ "CAST(""" & dISODateEnd & """ as 'dateTime'))" & _ "ORDER BY " & strSortBy 

Notice that the SELECT statement uses the CAST clause to cast the date specified from the URL to the ASP page into a dateTime data type. This is necessary if you plan to compare dates in your SQL statements. You'll find that you also need to use the CAST clause to set the correct data types for multivalued properties. The format for a multivalued string would be CAST("Properties" as 'mv.string'). Use the CAST clause for your custom properties that are not strings, except for Booleans. Otherwise, your custom properties will be evaluated by Exchange as strings.

You'll also notice that the date data type must be formatted into a standard ISO8601 format, such as yyyy-mm-ddThh:mm:ssZ. Exchange Server stores all dates in Universal Time Coordinate (UTC) and expects your date queries to use the ISO8601 format. Therefore, when working with ADO, all dates will be returned as UTC dates rather than in the client's local time zone. If you want time-zone conversion performed for you automatically, you need to use CDO instead.

ORDER BY Clause

Exchange Server supports the ORDER BY clause. To see how to use ORDER BY, see the next code sample taken from the Training application. Exchange Server enables you to sort your records by multiple columns in both ascending and descending order. In the Training application, for example, the user can sort the list of courses by subject, by date, or by category and then by date. You can specify a list of properties in your ORDER BY clause. If you do not specify a sorting order for your property, the default sorting order is ascending. To specify ascending or descending order, use ASC or DESC, respectively.

 'Figure out the sort order from a querystring variable if Request.QueryString("SortBy") = "" then strSortBy = """urn:schemas:mailheader:subject""" elseif Request.QueryString("SortBy") = "0" then strSortBy = """urn:schemas:mailheader:subject""" elseif Request.QueryString("SortBy") = "1" then strSortBy = """urn:schemas:calendar:dtstart""" elseif Request.QueryString("SortBy") = "2" then strSortBy = """" & strSchema & "category""," & _ ""urn:schemas:mailheader:subject""" else strSortBy = """urn:schemas:mailheader:subject""" end if 'This will eventually become part of the overall SELECT 'statement, such as: 'SELECT prop FROM URL WHERE condition 'ORDER BY strSortBy 

LIKE Predicate

Exchange Server 2000 supports the LIKE predicate, which allows you to perform queries using pattern matching of wildcard characters. The format for the LIKE predicate follows:

 SELECT Select_List | * FROM_Clause [WHERE Column_Reference [NOT] LIKE 'String_Pattern'] [ORDER_BY_Clause] 

You can specify any column you want, as long as its data type is compatible with the string pattern specified. Table 18-1 shows the wildcard characters you can use.

Table 18-1. Wildcard characters you can use with the LIKE predicate.

Wildcard	Character Symbol	Description
Percent	%	Matches one or more characters
Underscore	_	Matches exactly one character
Square brackets	[ ]	Matches any single character in the range or set you specify in the brackets
Caret	^	Matches any single character not within the range

The following code is taken from the search page for the Training application. This code uses the % wildcard to perform a full-text search on certain properties to see whether they contain a substring of the search criteria specified in strText.

 strFullText = " AND (""urn:schemas:mailheader:subject"" LIKE '%" _ & strText & "%' OR " & _ " ""urn:schemas:httpmail:textdescription"" LIKE '%" & strText & _ "%' OR " & _ " ""urn:schemas:calendar:location"" LIKE '%" & strText & "%' OR " & _ " """ & strSchema & "instructoremail"" LIKE '%" & strText & "%' OR " & _ " """ & strSchema & "prereqs"" LIKE '%" & strText & "%')" 

Be aware that the LIKE predicate uses the Exchange Server query processor. This is different than the CONTAINS and FREETEXT statements, which we'll examine when we look at content indexing. Both CONTAINS and FREETEXT require you to turn on content indexing before you can use them in your SQL statement. The LIKE predicate is not as fast as CONTAINS or FREETEXT because the Exchange Server query processor must search each item to see whether one of its columns contains the value you specified.

GROUP BY Predicate

Exchange Server 2000 supports the GROUP BY predicate, allowing you to group all rows that have the same value into a single row. This is useful for cases in which you might want to obtain distinct counts based on a specific value. For example, you could use GROUP BY to count the number of people listed in your Inbox who sent you email. Instead of having to scroll through your Inbox and count the different From addresses, you can use the GROUP BY statement, as shown here:

 strURL = "file://./backofficestorage/domain/mbx/mailbox/inbox/" 'Build the SQL statement strSQL = "Select ""urn:schemas:mailheader:from"" " & _ "From scope('shallow traversal of """ & strURL & _ """') GROUP BY ""urn:schemas:mailheader:from""" Set conn = New ADODB.Connection With conn .Provider = "exoledb.datasource" .Open strURL End With 'Create a new Recordset object Set rst = New Recordset With rst 'Open Recordset based on the SQL string .Open strSQL, conn End With If rst.BOF And rst.EOF Then Msgbox "No values found!" End End If iCount = 0 rst.MoveFirst Do Until rst.EOF iCount = iCount + 1 strFrom = rst.Fields("urn:schemas:mailheader:from").Value Debug.Print "From: " & strFrom rst.MoveNext Loop Debug.Print "There are " & iCount & _ " distinct FROM addresses in your inbox." 

Here is the output from this code sample:

 From: "System Administrator" <[e-mail]> From: "Thomas Rizzo" <[e-mail]> From: <[e-mail]> There are 3 distinct FROM addresses in your Inbox. 

CREATE INDEX Predicate

You might be wondering how you actually force Exchange Server to create a database index on the properties for which you specified the isindexed property to be True. The current way in RC1 is to specify a SELECT * statement on the folder. The post-RC1 method and the preferred way to do this is to use the CREATE INDEX statement. Here is the format for this statement:

 CREATE INDEX * ON scopeURL (*) 

A command that uses this statement would look like this:

 CREATE INDEX * ON file://./backofficestorage/domain/public folders/my folder/ (*) 

Note that the scope of this statement is the folder, so if you have multiple application folders, you'll want to run the statement multiple times with the different scopes. One issue to note is that the index created by Exchange is kept around permanently, so you need to issue this statement only once per folder. You cannot explicitly name or delete the index. Creating the index should give your applications faster performance. This index is different from the full-text index, which we'll discuss in the next chapter.

Aliasing Column Names

To make it easier for you to work with the long schema names that Exchange Server provides, the Exchange Server OLE DB provider supports aliasing column names. This means you can give friendly names to the column names of the schema in your SELECT statements. The following is an example of such aliasing:

 strURL = "file://./backofficestorage/domain/mbx/thomriz/inbox/" 'Build the SQL statement strSQL = "Select ""urn:schemas:mailheader:from"" AS " & _ "EmailFrom From scope('shallow traversal of """ & strURL & _ """') ORDER BY EmailFrom" 'Create a new Recordset object Set rst = New Recordset With rst 'Open Recordset based on the SQL string .Open strSQL End With If rst.BOF And rst.EOF Then End End If rst.MoveFirst Do Until rst.EOF Debug.Print "From: " & rst.Fields("EmailFrom").Value rst.MoveNext Loop 

As you'll notice in the code, aliasing requires you to use the AS keyword and then the name of the alias you want for the column. You'll also notice that aliasing is supported in the ORDER BY clause. However, it is not supported in the WHERE or GROUP BY clauses. Also, you'll see that aliasing is supported when using the Fields collection. This makes it easy to create short, memorable aliases for the schema names in your applications.

NOTE

---

Aliasing will work only for a specific SELECT statement; it is not global in nature.

Which SQL Statements Are Not Supported?

Exchange Server 2000 does not support the following SQL statements: SET, DISTINCT, DELETE, INSERT, CONVERT, DATASOURCE, CREATE VIEW, COUNT, SUM, AVG, MIN, and MAX. Also, remember that Exchange Server does not support JOIN.

Common Tasks Performed with ADO

This section describes the eight most common tasks you'll perform when using ADO with Exchange Server: creating folders, creating items, deleting folders or items, copying folders or items, moving folders or items, working with the Fields collection, working with Recordsets, and handling errors.

Creating New Folders

To create a new folder using ADO, you need to construct the URL to the folder and then use the Open method on the ADO Record object with the parameter adCreateCollection. The following code, taken from the setup program of the Training application, creates the application folders by using ADO:

 Private Sub CreateFolder(strFolderPath, strFolderName, _ strExpectedContentClass, strMAPIFolderClass) Dim oFolderRecord As New ADODB.Record oFolderRecord.Open strFolderPath & strFolderName, _ oConnection, adModeReadWrite, adCreateCollection oFolderRecord.Fields("urn:schemas-microsoft-com:exch-data:" & _ "expected-content-class").Value = Array("urn:content-classes:" & _ strExpectedContentClass) 'Set the Schema collection reference to our schema folder 'even though it may not exist yet oFolderRecord.Fields("urn:schemas-microsoft-com:exch-data:" & _ "schema-collection-ref").Value = strFolderPath & "schema" oFolderRecord.Fields("DAV:contentclass").Value = _ "urn:content-classes:folder" 'Set the PR_CONTAINER_CLASS 'so that Outlook displays it correctly oFolderRecord.Fields("http://schemas.microsoft.com/mapi/proptag/" & _ PR_CONTAINER_CLASS).Value = strMAPIFolderClass oFolderRecord.Fields.Update oFolderRecord.Close End Sub Private Sub CreateFolders() On Error GoTo errHandler 'Create all the folders in a local OLE DB transaction 'so that all are created or none are created oConnection.BeginTrans 'Create the root folder first Dim oRecord As New ADODB.Record oRecord.Open strExchangeServerFilePath & txtFolderPath, _ oConnection, adModeReadWrite, adCreateCollection oRecord.Fields("DAV:contentclass").value = "urn:content-classes:folder" oRecord.Fields("http://schemas.microsoft.com/mapi/proptag/" & _ PR_CONTAINER_CLASS).Value = "IPF.Note" strPath = oRecord.Fields("DAV:href") oRecord.Close strPath = strPath & "/" CreateFolder strPath, "Categories", "message", "IPF.Note" CreateFolder strPath, "Configuration", "message", "IPF.Note" CreateFolder strPath, "Course Materials", "message", "IPF.Note" CreateFolder strPath, "Discussions", "message", "IPF.Note" CreateFolder strPath, "Emails", "message", "IPF.Note" CreateFolder strPath, "Instructors", "instructor", "IPF.Contact" CreateFolder strPath, "Pending", "message", "IPF.Note" CreateFolder strPath, "Schedule", "trainingevent", "IPF.Appointment" CreateFolder strPath, "schema", "message", "IPF.Note" CreateFolder strPath, "Students", "student", "IPF.Contact" CreateFolder strPath, "Surveys", "message", "IPF.Note" CreateFolder strPath, "Surveys\Courses", "survey", "IPF.Note" CreateFolder strPath, "Surveys\Instructors", "survey", "IPF.Note" If Err.Number = 0 Then oConnection.CommitTrans End If Exit Sub errHandler: MsgBox "Error in CreateFolders. Error " & Err.Number & " " & _ Err.Description oConnection.RollbackTrans End Sub 

Notice that the code wrapped in an OLE DB transaction. The CreateFolder subroutine takes the full URL to the new folder to be created, creates a new Record object, and calls the Open method on that object with the URL. The Open method takes several parameters. The syntax for the Open method is shown here:

 Open Source, ActiveConnection, Mode, CreateOptions, Options, _ UserName, Password 

To create the folder, you need to pass the Mode parameter the value adReadWrite, and the CreateOptions parameter the value adCreateCollection. Exchange 2000 does not support passing a username and password.

If you're using schema, you can set the expected-content-class and schema-collection-ref fields on the folder. You should set the content class of the folder to urn:content-classes:folder. Also, if you plan to display the folder in Outlook, you need to set the PR_CONTAINER_CLASS property in the MAPI name space, which is "http://schemas.microsoft.com/mapi/proptag/" . You should pass the unique hex identifier to the property you're interested in without the leading 0. For example, 0x00212 would be x00212. If you don't want to set the MAPI property yourself, you could instead set http://schemas.microsoft.com/exchange/outlookfolderclass to IPF.Note or another value.

If you're trying to access custom properties you created in Outlook via ADO, you need to use a slightly different format. The following code shows you how to get a custom property, called MyProp, that you set through MAPI. This property can be created using MAPI itself, CDO, or Outlook. The long GUID that you see in the code is the ID for the PS_PUBLIC_STRINGS property set, where public properties are created in MAPI.

 strSQL = "Select ""http://schemas.microsoft.com/mapi/string/" & _ "{00020329-0000-0000-c000-000000000046}/MyProp"" AS MAPIProp" & _ " From scope('shallow traversal of """ & strURL & """')" 'Create a new Recordset object Set rst = New Recordset With rst 'Open Recordset based on the SQL string .Open strSQL End With If rst.BOF And rst.EOF Then End End If rst.MoveFirst Do Until rst.EOF Debug.Print "MyProp: " & rst.Fields("MAPIProp").Value rst.MoveNext Loop 

Note that this format is not the only format you can use to query for MAPI properties. One format requires that you know the propset ID and the hexadecimal value for the property, and it is illustrated in the next bit of code. The other two formats are easier to use, but the one I just mentioned can be used to access properties in your own namespaces beyond the public strings namespace.

 http://schemas.microsoft.com/mapi/id/{propset GUID}/value Example: http://schemas.microsoft.com/mapi/id/{3f0a69e0-7f56-11d2-b536- 00aa00bbb6e6}/0xfeedface 

Creating New Items

Creating new items in Exchange Server using ADO is very similar to creating folders with ADO. The only difference is that you will pass as the CreateOptions parameter the value adCreateNonCollection. Other than that, it's pretty much the same code. The following code from the setup program of the Training application shows how to create a new item. The code creates e-mail messages for different notifications for the application, such as a new course or an authorization requirement. This makes the application fairly easy to configure, depending on which settings you specify. Note that I set the MAPI property tag for the message class. You could also set the property http://schemas.microsoft.com/exchange/outlookmessageclass to the correct message class for your item.

 Private Sub CreateEmailTemplates() On Error GoTo errHandler: oConnection.BeginTrans 'Open the Emails folder Dim oRec As New ADODB.Record oRec.Open strPath & "Emails", oConnection, adModeReadWrite, _ adFailIfNotExists 'Create a new post in the folder by using ADO Dim oEmail As New ADODB.Record oEmail.Open strPath & "Emails/New Discussion Group Email", _ oConnection, adModeReadWrite, adCreateNonCollection 'Open the text file on the hard drive 'and read the data Open App.Path & "\discussionemail.txt" For Input As #1 Dim strMessage Do While Not EOF(1) Input #1, strLine strMessage = strMessage & strLine Loop Close #1 oEmail.Fields("urn:schemas:httpmail:textdescription").Value = _ strMessage oEmail.Fields("DAV:contentclass") = "urn:content-classes:message" oEmail.Fields("http://schemas.microsoft.com/mapi/proptag/" & _ PR_MESSAGE_CLASS).Value = "IPM.Post" oEmail.Fields.Update oEmail.Close oEmail.Open strPath & "Emails/ " & _ Survey Email", oConnection, adModeReadWrite, adCreateNonCollection 'Open the text file on the hard drive 'and read the data Open App.Path & "\surveyemail.txt" For Input As #1 strMessage = "" Do While Not EOF(1) Input #1, strLine strMessage = strMessage & strLine Loop Close #1 oEmail.Fields("urn:schemas:httpmail:textdescription").Value = _ strMessage oEmail.Fields("DAV:contentclass") = "urn:content-classes:message" oEmail.Fields("http://schemas.microsoft.com/mapi/proptag/" & _ PR_MESSAGE_CLASS).Value = "IPM.Post" oEmail.Fields.Update oEmail.Close oEmail.Open strPath & "Emails\New Course Email", oConnection, _ adModeReadWrite, adCreateNonCollection 'Open the text file on the hard drive 'and read the data Open App.Path & "\courseemail.txt" For Input As #1 strMessage = "" Do While Not EOF(1) Input #1, strLine strMessage = strMessage & strLine Loop Close #1 oEmail.Fields("urn:schemas:httpmail:textdescription").Value = _ strMessage oEmail.Fields("DAV:contentclass") = "urn:content-classes:message" oEmail.Fields("http://schemas.microsoft.com/mapi/proptag/" & _ PR_MESSAGE_CLASS).Value = "IPM.Post" oEmail.Fields.Update oEmail.Close 'Create a new post in the folder by using ADO oEmail.Open strPath & "Emails/WorkflowMessage", oConnection, _ adModeReadWrite, adCreateNonCollection 'Open the text file on the hard drive 'and read the data Open App.Path & "\workflowmessage.txt" For Input As #1 strMessage = "" Do While Not EOF(1) Input #1, strLine strMessage = strMessage & strLine Loop Close #1 oEmail.Fields("urn:schemas:httpmail:textdescription").Value = _ strMessage oEmail.Fields("DAV:contentclass") = "urn:content-classes:message" oEmail.Fields("http://schemas.microsoft.com/mapi/proptag/" & _ PR_MESSAGE_CLASS).Value = "IPM.Post" oEmail.Fields.Update oEmail.Close oRec.Close If Err.Number = 0 Then oConnection.CommitTrans End If Exit Sub errHandler: MsgBox "Error in CreateEmailTemplates. Error " & Err.Number & _ " " & Err.Description oConnection.RollbackTrans End End Sub 

When creating items using ADO, you have to watch out for conflicting names. Check to see whether you receive an error when trying to create an item or folder. If so, pick a different name or add a random number to the end of the name. The following code shows you how to do this:

 On Error Resume Next Set rec = Server.CreateObject("ADODB.Record") With rec .Open strStudentsFolderPath & "/" & strName, , 3, 0 if err.number = &H80040e98 then 'Already exists; try to open with a random name! err.clear Randomize iRandom = Int((50000 * Rnd)+1) .Open strScheduleFolderPath & "/" & strName & iRandom,,3,0 end if End With 

Deleting Folders or Items

To delete a folder or an item, you can use the DeleteRecord method on an ADO Record object. If you delete a folder, all items in the folder also will be deleted. The following code sample shows you how to delete a folder called MyFolder:

 Set Rec = CreateObject("ADODB.Record") 'This URL is for a public folder strURL = "file://./backofficestorage/" & DomainName & "/" & strFolderPath Rec.Open strURL Rec.DeleteRecord 'Or you could do 'Rec.DeleteRecord strURL Rec.Close 

If you want to delete items using a Recordset instead of the Record object, you can use the Delete method of the Recordset object. The following example deletes all items in a folder:

 'Create a query with a WHERE clause to delete only items strQ = "SELECT * FROM scope('shallow traversal of " & Chr(34) & _ strURL & Chr(34) & "')" strQ = strQ & " WHERE ""DAV:isfolder"" = FALSE" 'Open the Recordset Rs.Open strQ Rs.MoveFirst Do Until Rs.EOF 'Delete current record (row); 1 is parameter for adAffectCurrent Rs.Delete 1 Rs.MoveNext Loop 

Copying Folders or Items

To copy folders or items, use the CopyRecord method on the Record object. If you copy a folder, all items and subfolders will be copied as well. Note that you cannot copy folders or items between Exchange Server databases. Therefore, you cannot copy between private or public databases because they reside in different Exchange Server databases. However, you could create a new item in the separate database, copy the properties from the original item to the new item, and save the new item. The creator, creation time, and other properties on the item will not be the same as those of the original item, however. The following code copies an item named MyItem from one folder to another:

 Set Rec = CreateObject("ADODB.Record") strURL = "file://./backofficestorage/" & DomainName & "/" & _ strPath & "/MyItem" Rec.Open strURL Rec.CopyRecord ,"file://./backofficestorage/" & DomainName & _ "/" strDestination 

Moving Folders or Items

To move items, you can call the MoveRecord method. Moving has the same restrictions as copying in that you cannot move between Exchange Server stores. Moving a folder moves all the items in the folder as well. The following code sample moves an item:

 Set Rec = CreateObject("ADODB.Record") Rec.Open URLFrom, , adModeReadWrite NewURL = Rec.MoveRecord URLFrom, URLTo, "","", adMoveOverWrite 

This is the syntax for the MoveRecord method:

 URL = MoveRecord (Source, Destination, UserName, Password, Options, Async) 

This method typically returns the Destination parameter, which is a string value of the destination the item was moved to. The Options parameter allows you to provide options for the move. The most common option you'll provide is the adMoveOverWrite option.

Using the Fields Collection

You've already seen how to get properties using the Fields collection in ADO. You might want to set properties on an item or folder using the Fields collection. If you do so, remember that the property is available only on the specified item rather than on every item in the folder. Adding new fields using the Fields collection is not like adding custom schema. When you add to the Fields collection, your properties are not included when a SELECT * statement is issued. Unlike schema, new properties added to the Fields collection cannot be shared among multiple items in a folder unless you explicitly create the property on every item. With schema, you can set the content class of your items to be of a particular type, and those items will implement the properties that you specified in your schema.

The Fields collection supports the Append method, which allows you to add custom fields to a Recordset or Record object. Here is the syntax for the Append method:

 fields.Append Name, Type, DefinedSize, Attrib, FieldValue 

The Type parameter specifies the data type of the field. This parameter can have one of many different values; however, you'll probably use one of the following: adWChar, adBStr, adDate, adInteger, or adBoolean.

The DefinedSize parameter specifies the size of the new field. Normally, you would leave this parameter blank because ADO will base the size of the new field on the data type you specify.

The Attrib parameter allows you specify attributes of the field—for example, whether the field is nullable or contains fixed-length data. Normally, you would leave this parameter blank too, unless your field really needs special handling.

The final parameter is a value for the field. Instead of having to create the field and then use another line of code to set the value, ADO allows you to simply assign the value to the field right in its parameter.

The following code shows you how to add new fields to a record:

 'Assumes oNewRecord is a new item in Exchange Server oNewRecord.Fields.Append "MyNewProp",adInteger,,,12 oNewRecord.Fields.Update 

Notice how the code calls the Update method on the Fields collection. If you do not call the Update method, your changes won't be saved to Exchange Server—meaning your new field won't be added to the item. Also, if you suspect that the values for your fields might have changed while you were working with your data, you can call the ReSync method to have ADO requery Exchange Server for the latest values for your data.

Working with Recordsets By Using ADO

One of the most common tasks you'll perform with ADO is working with Recordsets. To efficiently work with the Recordset object, you'll need to know about some of its methods. For example, you'll need to know how to scroll through records in the recordset as well as detect the end of the recordset.

The first thing that you will want to know about your Recordset after you have Exchange perform a query for you is the number of items returned in the Recordset. ADO provides a Recordcount property on the Recordset object so that you know how many records are contained in the Recordset. This number depends on the cursor type you use for your Recordset. Table 18-2 outlines the different cursor types and how they affect the Recordcount property.

Table 18-2. Cursor types and their affect on the Recordcount property.

Cursor Type	Description	Recordset Count Returned
Dynamic	Dynamic collection of records that is the most flexible of all cursors. Additions, changes, and deletions by you or other users are immediately visible. You can scroll forwards or backwards through the records.	-1
Keyset	Like a Dynamic cursor except that you cannot see the records added by other users. You cannot access the records that other users delete.	-1
Static	A copy of the records. Additions, changes, and deletions by other users are not reflected, but any type of movement is allowed.	Actual number
Forward Only	Very similar to static except that you can only scroll forward through the records. This is the default type of cursor created.	Actual number

Using the Recordcount property's value, you can decide whether you need to scroll through the records if any were returned. To make scrolling the records easy in a Recordset, ADO provides the BOF and EOF properties on a Recordset object. BOF is a Boolean that, if True, indicates that the current record position is before the first record—in effect, it indicates that you are at the beginning of the Recordset. EOF is a Boolean that, if True, indicates that the current record position is after the last record in the Recordset. To ensure you do not read beyond the end or the beginning of your Recordset, you can use these two properties while scrolling through the Recordset either forwards or backwards.

To actually scroll through the recordset, you'll need to use the MoveFirst, MoveNext, MoveLast, and MovePrevious methods of the Recordset object. Depending on the cursor type you specify, these methods will provide you with differing degrees of usability. The method names are pretty self-explanatory. MoveFirst moves you to the first record, MoveNext moves you to the next record if it exists (and returns an Error object if it doesn't exist), MoveLast moves you to the last record, and MovePrevious moves you to the previous record.

Handling Errors in ADO

You always have to be prepared to handle errors in your code. ADO provides built-in error-handling capabilities through its Errors collection on the Connection object. The Errors collection contains Error objects. These Error objects have three properties of interest to Exchange developers:

• Description. The default property for the Error object. This is a string representation of the error text.
• Number. A long integer that contains the number associated with the error. You can look at the ErrorValueEnum constants in ADO to see whether this number matches one of the ADO-defined errors.
• Source. A string that contains the name of the object that raised the error.

You can use the Errors collection in your error handling to scroll through all the Error objects contained in the collection. When you find the errors, you can then take action on them. For example, you can rollback a transaction, which we'll see shortly, or print an error message for the user.

Using OLE DB Transactions

The Exchange Server OLE DB provider supports local OLE DB transactions. This allows you to treat your ADO operations as tranasactions when working with Exchange Server. If for some reason an operation fails during the transaction, you can roll back the entire transaction without leaving your Exchange Server application in an inconsistent state. Since Exchange Server transactions are different than SQL Server ones, there are a few things you need to know.

First, as you would expect, for the transactions to work you must perform all your ADO operations on the same connection to the Exchange server. Therefore, when using the methods of the Record or Recordset object, always pass as a parameter the Connection object that you created at the beginning of the transaction.

Second, transactions in Exchange Server are not supported across Exchange Server stores. This means transactions will not work if performed on folders or items located in different Exchange Server stores. The most common examples of different Exchange Server stores are the private store and the public store.

Finally, Exchange Server does not support distributed transactions with SQL Server. You cannot wrap in a single transaction ADO commands between the two databases, nor can you use the distributed transaction coordinator with both databases. Therefore, you should use local transactions in each database and should perform all your SQL Server work before doing your Exchange Server work. That way, you guarantee that the SQL Server transactions go through, and you don't have to worry about rolling back your Exchange Server transactions.

You will work with a transaction by using one ADO Connection object. On this Connection object, you will first need to call the BeginTrans method, which starts a transaction. When your ADO methods are complete, you call the CommitTrans method, which attempts to commit the transaction to the Exchange database. If you find that an error occurred during your ADO calls, you can use the RollBackTrans method to roll back the entire transaction. The following example, taken from the Training application setup program, shows you how to use these methods:

 Private Sub CreateCategoryMessage() On Error GoTo errHandler: oConnection.BeginTrans 'Open the Categories folder Dim oRec As New ADODB.Record oRec.Open strPath & "Categories", oConnection, adModeReadWrite, _ adFailIfNotExists 'Create a new post in the folder by using ADO Dim oEmail As New ADODB.Record oEmail.Open strPath & "Categories\Categories", oConnection, _ adModeReadWrite, adCreateNonCollection oEmail.Fields("urn:schemas:httpmail:textdescription").Value = _ strCategories oEmail.Fields("http://schemas.microsoft.com/mapi/proptag/" & _ PR_MESSAGE_CLASS).Value = "IPM.Post" oEmail.Fields.Update oEmail.Close oRec.Close If Err.Number = 0 Then oConnection.CommitTrans End If Exit Sub errHandler: MsgBox "Error in CreateEmailTemplates. Error " & Err.Number & _ " " & Err.Description oConnection.RollbackTrans End End Sub 

Best Practices for Using ADO

There are three best practices for working with ADO and Exchange Server 2000. First, learn your Exchange Server properties well. When working with ADO, you'll need to remember all the different properties in all the different name spaces that Exchange Server provides. The most common ones that you will work with are listed here. For the full list of properties, refer to the Exchange Server SDK included on the companion CD.

• DAV:href
• DAV:contentclass
• DAV:displayname
• DAV:iscollection
• DAV:isfolder
• DAV:ishidden
• DAV:isreadonly
• urn:schemas-microsoft-com:office:office#Author
• urn:schemas-microsoft-com:office:office#Category
• urn:schemas-microsoft-com:office:office#Comments
• urn:schemas:httpmail:to
• urn:schemas:httpmail:subject
• urn:schemas:httpmail:cc
• urn:schemas:httpmail:bcc
• urn:schemas:httpmail:hasattachment
• urn:schemas:httpmail:from

The second best practice is to use transactions wherever appropriate. There are a number of reasons for this. First, it allows you to roll back the Exchange Server database to the original state in case of an error. Second, using transactions is key when working with events. If you create an item in the database using ADO, Exchange Server will fire an event. If you then update the fields after creating the item, Exchange Server will fire another event. You don't want this to happen; you want only one event to fire. (The problem of multiple events firing can be a tough one to debug.) When you use transactions, the information automatically is entered into the Exchange Server database, thereby causing only one event to fire.

The third best practice for working with ADO is to reuse a connection that already exists. Creating new connections to the Exchange Server database taxes your computer's resources because Exchange Server needs to keep open the connections you create. Therefore, you should reuse the same Connection object in a session variable—especially if you're writing ASP applications.