Using ADO and OLE DB with Exchange Server

Windows ships with OLE DB and ADO, and Exchange Server includes the OLE DB 2.5 and later 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 OLE DB provider in Exchange Server, called EXOLEDB, capitalizes on this ease and power. In this section, we'll take a quick look at some of the features in ADO. Then, using the Training application, we'll examine how to build ADO applications with Exchange Server.

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

ADO Features

Two major enhancements of ADO in version 2.5 and beyond should be of interest to Exchange Server developers: URL binding support, and Record and Stream support. These enhancements enable ADO to work so easily with the type of data stored in an Exchange Server application, for two reasons:

• 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

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. Recent versions of ADO let you bypass all that setup work. You simply provide a URL for the data you want to retrieve, and OLE DB sets up all the connections and manages all the security for you. Here 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 that 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. 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/defaultSMTPdomain/public folders/myfolder" .

Note  

Notice that the file:// URL format requires passing a domain as part of the path , as in file://./backofficestorage/microsoft.com/public . This domain is usually the same as your Windows domain name . However, the real value for the domain is pulled from the default SMTP policy that you set in Exchange. If this policy uses a different addressing scheme than your Windows domain, the two will not match up. If you want to get a domain programmatically, the address you'll want will be stored in the following Active Directory object as the gatewayProxy property. You can use ADSI to read this property.

 LDAP://server/CN=Default Policy,CN=Recipient Policies,      CN=First Organization,CN=Microsoft Exchange,CN=Services,      CN=Configuration,DC=domain,DC=com 

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 can create or grab an explicit Connection object that sets the provider to EXOLEDB and then use that object in your ADO methods . To work with Exchange Server using the EXOLEDB provider and http:// URLs, the code will look like this:

 Set oConnection = CreateObject("ADODB.Connection") 'Open a connection to the Exchange server by using a throw-away 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  

You can create the Connection object in other ways as well. Keep in mind that if you want to use http:// URLs, you have to be sure the provider is EXOLEDB. Also, to get at Public Folders, you do not use Public Folders in the http:// URL as you do with 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 should 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, you should commit one property to memory quickly: DAV:href , 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 enables you to create records and streams. Creating recordsets is useful for relational data sources because recordsets are very "rectangular," which means 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 fewer columns than the next row because Exchange Server provides flexible schema support even within a table (or folder, as it's called in Exchange Server). This is why the ability to create records and streams of information is a welcome addition to ADO.

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 its record support, recent 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. In Chapter 17, 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 to Work with Exchange Server

You'll mainly use ADO 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 on issuing SQL queries against Exchange Server: Exchange Server does not provide JOIN support, so you cannot join two Exchange Server folders into a single recordset.

SELECT Statement

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

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

You should avoid using SELECT * if possible. 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 must 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. A deep traversal of the URL means that Exchange Server searches not only the specified folder but also all its subfolders . As you'd expect, a shallow traversal means that Exchange Server searches only the specified folder.

Note  

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

Using 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 will 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. Note, however, 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" 

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 as simple as you want. The previous examples 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 complex WHERE clauses in its searches. For example, the following SQL statement is from the application's showcourses.asp file, which shows the available courses 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 also need to use the CAST clause to set the correct data types for multivalue properties. The format for a multivalue string is CAST("Properties" as 'mv.string') . Use the CAST clause for your custom properties that are not strings, including custom Boolean properties. Otherwise, Exchange will evaluate your custom properties as strings.

Note  

Missing CAST clauses are one of the biggest gotchas when you're working with custom properties. When you try to select your property or query on the property, things might not work as expected. Be sure to CAST all your custom properties except string properties.

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) format and expects your date queries to use the ISO8601 format. Therefore, when you work with ADO, all dates are returned as UTC dates rather than in the client's local time zone. If you want time-zone conversion performed for you automatically, you must use CDO instead.

Performance Considerations with Query Scope

When shallow traversal queries are scoped to a single folder, Exchange Server automatically caches results of that shallow traversal query. These cached results are saved in a search folder that is kept up-to-date with changes in the folder to which the query is scoped. As a result of this cache, if the query is repeated, the results are available again without requiring a repeat of the entire query. By default, Exchange limits the number of search folders that it keeps per folder to 11, and it limits the time it keeps a search folder since it was last used to 8 days. It does this because there is an additional cost to maintain the search folder when changes occur to the scoped folder. If you want to increase the number of cached views beyond 11, you should modify the property http://schemas.microsoft.com/mapi/proptag/xe680003 on the desired folder. Just set this property to the number of cached views you want for the folder. Remember, though, that each cached view takes resources, so be careful not to set this number too high for multiple folders.

You can also change the default value of 11 views per folder on a databasewide basis by setting the MsExchMaxCachedViews parameter in Microsoft Active Directory. You can find this property on the AD object for the database by using ADSI Edit.

In the case of searches, to take advantage of a cached view, the search must exactly match the search on which the cached view was created. The searches must match each other, down to the order in which the expressions are listed in the WHERE clause. If the expressions are not listed in exactly the same way, Exchange will create a new search rather than search the cached results.

Increasing the number of cached views on a given folder also results in a small additional performance cost of about 5 percent for each additional cached view when you add, update, or remove items from the folder if the item in question satisfies the restriction on the view. The cost is much less if the changed item does not satisfy the restriction.

Hierarchical Traversal

When you do a deep traversal search in which you only want to search against folder (such as nonmessage and nondocument) resources, an application can improve the performance of the search by specifying that the search uses hierarchical traversal rather than deep traversal. For example, both of the following searches return the same results, but the first returns much faster and uses fewer server resources than the second because the first uses the hierarchical traversal rather than a deep traversal, which looks at every item in the database:

 "SELECT "DAV:displayname" from scope('HIERARCHICAL TRAVERSAL OF "http://myserver/public"')      "SELECT "DAV:displayname" from scope('DEEP TRAVERSAL OF "http://myserver/public"') WHERE "DAV:iscollection" = true 

ORDER BY Clause

Exchange Server supports the ORDER BY clause. The next code sample, taken from the Training application, shows how to use ORDER BY . Exchange Server lets you 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 supports the LIKE predicate, which allows you to perform queries using pattern matching of wildcard characters . The format of 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 15-2 shows the wildcard characters you can use.

Table 15-2: Wildcard Characters You Can Use with the LIKE Predicate

Wildcard Name	Character	Description
Percent	%	Matches one or more characters
Underscore	_	Matches exactly one character
Square brackets	[ ]	Matches any single character in the range or set that 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 supports the GROUP BY predicate, allowing you to group all rows that have the same value into a single row. This is useful when you want to obtain distinct counts based on a specific value. For example, you can 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 all the 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 set the isindexed property to True . One way is to specify a SELECT * statement on the folder. The preferred way is to use the CREATE INDEX statement. Here is the format for this statement:

 CREATE INDEX * ON scopeURL (*) 

A command that uses this statement looks 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 should run the statement multiple times with the different scopes. One issue to note is that the index created by Exchange is kept 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.

Property-level indexes help the performance only of searches that use the indexed property in the WHERE clause of the search. For example, if an index has been created on myFolder for the property myProp1 , the following search will yield a performance gain:

 SELECT DAV:displayname from scope('DEEP TRAVERSAL OF "http://myserver/public/myFolder"') WHERE "myProp1" = 'foo' 

If the restriction is more complicated, however, the addition of a single index might not be sufficient, depending on how efficiently the query can be evaluated. For example, the following restriction benefits from the index on myProp1 because Exchange can evaluate the first clause of the restriction by using the index and then apply the second part of the restriction against those results.

 WHERE "myProp1" = 'foo' and "myProp2" = 'bar' 

You will not see improved performance for the following restriction because while the first half of the restriction can be evaluated efficiently, the second half must be evaluated by looking at every item because there is no index on myProp2 . To evaluate this efficiently, indexes on both properties are required.

 WHERE "myProp1" = 'foo' or "myProp2" = 'bar' 

With the use of property-level indexes, applications experience a performance decrease of about 1 percent per index when you insert or update items in the folders that have had indexes created or remove items from those folders. This decrease is due to the cost of updating the index information for that folder to reflect the update operation. To maximize application performance you should create indexes only on properties that are frequently searched against.

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, aliasing is supported when you use the Fields collection. This makes it easy to create short, memorable aliases for the schema names in your applications.

Note  

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

Note  

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

Content Indexing and Using CI from ADO

Exchange Server provides built-in support for content indexing. If you plan to use Exchange Server as a repository for a large amount of information, or if you think that your users will require advanced search features, you should consider using content indexing. While content indexing can index the standard properties such as the From, Subject, and Message text fields, you can also use content indexing to search for text in attachments on items. Content indexing supports indexing attachments in different formats, such as Office and HTML documents, as well as standard text attachments. The only caveat for content indexing is the requirement for higher processor and disk resources due to the indexing overhead. To keep this overhead within reasonable limits, you can schedule incremental crawls of the data sources for content indexing through the Exchange System Manager, shown in Figure 15-18.

Figure 15-18: Using the Exchange System Manager to schedule Content Indexing.

If you have SharePoint Portal Server in your environment, you will want to leverage that technology to search Exchange and other data sources in a single search, because Exchange can only index and search Exchange information, while SharePoint can search many data sources, including Exchange, Lotus Notes, Web sites, and file shares. The one advantage Exchange content indexing has over SharePoint is that SharePoint can index only Exchange public folders, while Exchange can index and search user's mailboxes as well. For more information on SharePoint and programming search in SharePoint, please refer to the online chapters for this book on the Microsoft Press Web site.

With content indexing, you can perform quick queries against a full-text index inside your Exchange Server applications. Using the full-text index is as easy as generating a SQL query that uses the CONTAINS or FREETEXT predicate. You cannot use these predicates, however, unless you turn on content indexing for your Exchange server. Be aware that content indexing operates on a per-store basis, which means there is no top-level index that allows you to search across stores. Let's see how a SQL query containing the CONTAINS or FREETEXT predicate looks.

CONTAINS Predicate

The CONTAINS predicate allows you to perform text-matching operations against the full-text index. With CONTAINS , you can perform simple queries, such as "Show me all items that contain the word Bob in the subject," as well as complex queries with weighting on the terms they contain. (You can use a weighted query to indicate relative importance of the terms you search for.) The following code snippet shows several ways you can use the CONTAINS predicate. The code includes a simple version of CONTAINS; it also shows how to use the NEAR keyword, prefix matching, linguistic matching (such as drive, driving, and so on), and weighted queries. For more information on this predicate, please refer to the Microsoft Developer Network (MSDN) at http://msdn.microsoft.com .

 'Contains Bob strSQL = "SELECT ""urn:schemas:httpmail:subject"" FROM " _        & "SCOPE('SHALLOW TRAVERSAL OF """ & strURL _        & """') WHERE CONTAINS(""urn:schemas:httpmail:subject"",' ""Bob"" ')"      'Contains Bob AND Cool, could also be OR strSQL = "SELECT ""urn:schemas:httpmail:subject"" FROM " _        & "SCOPE('SHALLOW TRAVERSAL OF """ & strURL _        & """') WHERE CONTAINS(""urn:schemas:httpmail:subject"",' ""Bob"" " _        & "AND ""Cool""')"      'Word prefix match strSQL = "SELECT ""urn:schemas:httpmail:subject"" FROM " _        & "SCOPE('SHALLOW TRAVERSAL OF """ & strURL _        & """') WHERE CONTAINS(""urn:schemas:httpmail:subject"",' ""*Bob*"" ')"      'Linguistic matching strSQL = "SELECT ""urn:schemas:httpmail:subject"" FROM " _        & "SCOPE('SHALLOW TRAVERSAL OF """ & strURL _        & """') WHERE CONTAINS('FORMSOF(INFLECTIONAL,""drive"") ')"      'Bob NEAR cool, where ~ is same as NEAR (within 50 words) strSQL = "SELECT ""urn:schemas:httpmail:subject"" FROM " _        & "SCOPE('SHALLOW TRAVERSAL OF """ & strURL _        & """') WHERE CONTAINS(""urn:schemas:httpmail:subject""," _        & "'""Bob"" ~ ""cool""')"      'Weighted Match strSQL = "SELECT ""urn:schemas:httpmail:subject"" FROM " _        & "SCOPE('SHALLOW TRAVERSAL OF """ & strURL _        & """') WHERE CONTAINS(""urn:schemas:httpmail:subject""," _        & "'ISABOUT (""Bob"" WEIGHT(0.9), ""Cool"" WEIGHT(0.1))')" 

FREETEXT Predicate

You can use the FREETEXT predicate to search columns based on the meaning of the search words rather than the exact wording. When you use FREETEXT , the query engine breaks the string you specify into a number of search terms, assigns weights to the terms, and then attempts to find a match. The following code performs a search for the meaning "best server on the planet." I have in my Inbox a message with the subject "Exchange Server is the best server in the world," and the search finds that message even though the wording is slightly different.

Note  

You can use the AND , OR , and FREETEXT predicates together.

 'FREETEXT strSQL = "SELECT ""urn:schemas:httpmail:subject"" FROM " _        & "SCOPE('SHALLOW TRAVERSAL OF """ & strURL _        & """') WHERE FREETEXT(""urn:schemas:httpmail:subject""," _        & "'best server on the planet')" 

Working with Ranking

When you use content indexing with a query, you might want to retrieve the rank value of a document compared to the search terms in your query. The search engine will assign a rank of 0 to 1000 to your items based on how well they match the query. In your SQL SELECT statement, you can request the rank property by adding the urn:schemas.microsoft.com:fulltextqueryinfo:rank property. You can use the ORDER BY predicate with this property to sort the items returned by the query based on their relevance to the search terms. Also, you can force Exchange to coerce the values for the rank by using clause weighting or rank coercion.

The idea of clause weighting is similar to that of weighted columns, which you saw in an earlier example. The difference is that instead of applying the weight to only the column, you apply the weight to the entire search term using the RANK BY predicate. This predicate takes a number of options, such as WEIGHT and COERCION .

The next example demonstrates clause ranking, so it uses the WEIGHT option. This option takes a decimal value from 0 through 1 plus up to three digits past the decimal, as in 0.832. Using the technique of clause weighting, you can assign certain search terms a fraction of the weight that other search terms have. The following example searches all properties on the items for the term transportation and the terms heavy and trains . As you can see by the ranking, if the search engine finds only transportation , it should rank that item at one-quarter the value of an item containing heavy trains . When you use weighting in this manner, the search engine applies weighting to the terms in the preprocessing stage.

 StrSQL = "Select ""urn:schemas:httpmail:subject"", " _        & """urn:schemas.microsoft.com:fulltextqueryinfo:rank"" FROM " _        & "scope('shallow traversal of ""file://./backofficestorage/" _        & "thomriznt5dom.extest.microsoft.com/apps/items/""') WHERE " _        & "CONTAINS(*,'""transportation""') RANK BY WEIGHT(0.25) OR " _        & "CONTAINS(*,'""heavy trains""') RANK BY WEIGHT(1.0)" 

Coercion, especially rank coercion, is a post-processing concept in which, after the search engine finds matches for the search terms, your application can tell the search to recalculate the rank according to your specifications. Coercion is best illustrated with an example. Suppose you are searching for a document that contains the word Exchange . If the word Exchange is in a particular property that you think will make the item containing the word particularly relevant, such as the subject property, you can coerce the search engine into giving the item you are searching for a very high ranking. If the search engine finds an item with the word Exchange in another property (that is, a property other than the subject property), you can have the search engine read just the ranking so the items containing Exchange found in the other properties are ranked lower than those found in the subject property.

You can perform coercion using one of two approaches. One approach is absolute coercion, in which you assign an absolute value, such as 500 , to the items that meet your criteria for coercion. But what if you have more complex scenarios and absolute coercion will not meet your needs? For example, say you want items with the word Exchange in the subject property to be ranked from 900 through 1000. (Remember that the rank can range from 0 through 1000.) Using a coercion formula ”the second approach ”you can tell the search engine to assign the coerced rank of these items according to the formula 900 + the uncoerced rank — 0.1. For the items containing the word Exchange in a property different from subject , you can coerce the rank to be in the range of 0 through 900 by making the coerced rank equal to the uncoerced rank multiplied by 0.9.

Using a coercion formula requires that your users know which columns they should have the search engine rank higher when their search criterion involves those columns. You can implement some logic in your application to take a shot at defining which columns should be coerced as ranking higher if search terms are found in those columns. The following code shows using both absolute coercion and a coercion formula for the example we just looked at:

 'Use absolute coercion '1000 - Exchange in Subject '500 - Exchange anywhere else StrSQL = "SELECT ""urn:schemas:httpmail:subject""," _        & """urn:schemas.microsoft.com:fulltextqueryinfo:rank"" FROM " _        & "SCOPE('SHALLOW TRAVERSAL OF ""file://./backofficestorage/" _        & "thomriznt5dom.extest.microsoft.com/apps/items/""') WHERE " _        & "CONTAINS(""urn:schemas:httpmail:subject"",'""Exchange""') " _        & "RANK BY COERCION(ABSOLUTE,1000) OR CONTAINS(*,'""Exchange""') " _        & "RANK BY COERCION(ABSOLUTE,500)"      'Use coercion formula '900 - 1000 - Exchange in Subject using MULTIPLY and ADD '0 - 900 - Exchange anywhere else using MULTIPLY 'MULTIPLY takes a decimal number from 0 to 1, with 3 digits after 'the decimal 'ADD takes an integer 'You cannot go above 1000 StrSQL = "SELECT ""urn:schemas:httpmail:subject""," _        & """urn:schemas.microsoft.com:fulltextqueryinfo:rank"" FROM " _        & "SCOPE('SHALLOW TRAVERSAL OF ""file://./backofficestorage/" _        & "thomriznt5dom.extest.microsoft.com/apps/items/""') WHERE " _        & "(CONTAINS(""urn:schemas:httpmail:subject"",'""Exchange""') " _        & "RANK BY COERCION(MULTIPLY,0.1)) RANK BY COERCION(ADD,900) OR " _        & "CONTAINS(*,'""Exchange""') RANK BY COERCION(MULTIPLY,0.9)" 

Indexing Default Properties

The content indexing engine by default indexes a certain set of built-in properties, which are listed in Table 15-3. Note that there is currently no simple way to tell the engine to index your custom properties, such as setting a fulltextindexed property in your schema. The only way to ensure that your custom properties are full-text indexed is to create a text file, such as http://thomriz.com/schema/myprop , that contains the fully qualified names for your properties on separate lines. You then need to locate the following registry key:

HKLM\Software\Microsoft\Search\1.0

If the registry key does not already exist, add a key under 1.0 with the name ExchangeParameters . If the ExchangeParameters key does not already exist, add a string value under ExchangeParameters with the name SchemaTextFilePathName . The SchemaTextFilePathName string should contain the path to the text file of properties, such as C:\Exchsrvr\fulltextprops.txt.

Table 15-3: Built-In Properties Indexed by the Content Indexing Engine by Default

MAPI Property	urn:schemas:httpmail Property
PR_SUBJECT, PR_SUBJECT_W	urn:schemas:httpmail:subject
PR_BODY, PR_BODY_W	urn:schemas:httpmail:textdescription
PR_SENDER_NAME, PR_SENDER_NAME_W	urn:schemas:httpmail:textdescription
PR_SENDER_NAME_W	urn:schemas:httpmail:sendername
PR_SENT_REPRESENTING_NAME, PR_SENT_REPRESENTING_NAME_W	urn:schemas:httpmail:fromname
PR_DISPLAY_TO, PR_DISPLAY_TO_W	urn:schemas:httpmail:displayto
PR_DISPLAY_CC, PR_DISPLAY_CC_W	urn:schemas:httpmail:displaycc
PR_DISPLAY_BCC, PR_DISPLAY_BCC_W	urn:schemas:httpmail:displaybcc
PR_SENDER_EMAIL_ADDRESS, PR_SENDER_EMAIL_ADDRESS_W	urn:schemas:httpmail:senderemail

You can use the following code, taken from the Training application, when you turn on content indexing for the application. In the Training application, I've commented out this code since you may not have content indexing enabled on the store where you install the application. To see this code in action, remove my comment characters. The following listing shows the code in activated state:

 'Uses CONTAINS instead of LIKE '***************************** BEGIN strCategoryText = "CONTAINS (""" & strSchema & "category"","      If strCategories = "all" Then          arrCategories = Session("arrCategories")          'Select the first one     'Generate the rest     strCategoriesSQL = strCategoryText     For i = LBound(arrCategories) To UBound(arrCategories)         If i = LBound(arrCategories) Then             'First one, start the '             strCategoriesSQL = strCategoryText & "'""" & arrCategories(i)             If LBound(arrCategories) = UBound(arrCategories) Then                 'Only one, end the statement                 strCategoriesSQL = strCategoriesSQL & """')"             Else                 strCategoriesSQL = strCategoriesSQL & """ OR "             End If         ElseIf (i < UBound(arrCategories) AND i > LBound(arrCategories)) Then                 strCategoriesSQL = strCategoriesSQL & """" _                                  & arrCategories(i) & """ OR "         Else             'It's the last one, drop the OR             If Right(arrCategories(i),1) = chr(10) Then                 'Must be a carriage return/linefeed                 arrCategories(i) = Mid(arrCategories(i), _                                        1,(len(arrCategories(i))-2))             End If             strCategoriesSQL = strCategoriesSQL & """" _                              & Trim(Cstr(arrCategories(i))) & """')"         End If     Next Else     'Need to create the category search string     'Grab the querystring value which should be separated by $     strCats = Request.QueryString("Categories")     arrCats = Split(strCats,"$")          'Always going to be at least one     For i = LBound(arrCats) to UBound(arrCats)         If i = LBound(arrCats) Then             'First one, start the '             strCategoriesSQL = strCategoryText & "'""" & arrCats(i)             If LBound(arrCats) = UBound(arrCats) Then                 'Only one, end the statement                 strCategoriesSQL = strCategoriesSQL & """')"             Else                 strCategoriesSQL = strCategoriesSQL & """ OR "             End If         ElseIf (i < UBound(arrCats) AND i > LBound(arrCats)) Then             strCategoriesSQL = strCategoriesSQL & """" & arrCats(i) _                              & """ OR "         Else             'It's the last one, drop the OR             If Right(arrCats(i),1) = chr(10) Then                 'Must be a carriage return/linefeed                 arrCats(i) = Mid(arrCats(i), 1, (len(arrCats(i))-2))             End If             strCategoriesSQL = strCategoriesSQL & """" _                              & Trim(Cstr(arrCats(i))) & """')"         End If     Next End If '************************************** END 

Common Tasks Performed Using 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 must 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 is 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 pass the Mode parameter the value adReadWrite , and you pass the CreateOptions parameter the value adCreateCollection . Exchange 2000 does not support passing a username and password.

If you're using schemas, 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 must set the PR_CONTAINER_CLASS property in the MAPI namespace, 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 can 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 must use a slightly different format. The following code shows 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 you can use the one I just described 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}/0x001E1232 

Note  

Sometimes you must preface your MAPI properties with a 0, as in 0x001E1232 . If you do not include the leading 0, the property might not return a value.

Creating New Items

Creating new items in Exchange Server using ADO is similar to creating folders using ADO. The only difference is that you 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 can 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 you create items using ADO, you must watch out for conflicting names. Check to see whether you receive an error when you try 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 will also be deleted. The following code 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, you 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 can 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 the 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 schemas. When you add to the Fields collection, your properties are not included when a SELECT * statement is issued. Unlike schemas, 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 schemas, you can set the content class of your items to be of a particular type, and those items will implement the properties you specified in your schemas.

The Fields collection supports the Append method, which allows you to add custom fields to a recordset or a 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 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. ADO allows you to simply assign the value to the field right in its parameter rather than having to create the field and then use another line of code to set the value.

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 ”which means 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 Using ADO

To efficiently work with the RecordSet object, you need to know about some of its methods. For example, you need to know how to scroll through records in the recordset as well as detect the end of the recordset.

The first thing 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 you can know how many records are contained in the recordset. This number depends on the cursor type you use for your recordset. Table 15-4 lists the cursor types and how they affect the RecordCount property.

Table 15-4: Cursor Types and How They Affect the RecordCount Property

Cursor Type	Description	Recordset Count Returned
Dynamic	A 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 forward or backward through the records.	“1
Keyset	Like a dynamic cursor except 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	Similar to a static cursor except 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 value 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 value that, if True , indicates that the current record position is after the last record in the recordset. To make sure you do not read beyond the end or the beginning of your recordset, you can use these two properties while scrolling forward or backward through the recordset.

To actually scroll through the recordset, you 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), MoveLast moves you to the last record, and MovePrevious moves you to the previous record.

Querying for MAPI Entry IDs

There is a special case you have to worry about when you query for MAPI entry IDs in your ADO code. First, the property name for the entryid property is http://schemas.microsoft.com/mapi/proptag/x0fff0102 . Second, once you try to retrieve this property, you will find that the property is a byte array. You must write some code that constructs the string-based entryid from this byte array. Once you create the string-based entryid , you can pass it to CDO 1.21 or the Outlook object model to have those APIs retrieve the item as well. The following example shows you how to perform the steps:

 Dim Rec As New ADODB.Record   Dim Rs As New ADODB.RecordSet   Dim strUrl As String   Dim strSQL As String        strUrl = "file://./backofficestorage/domain.com/mbx/thomriz/inbox/"        Rec.Open strUrl   Set Rs.ActiveConnection = Rec.ActiveConnection        strSQL = "select ""DAV:href"", " & _            """http://schemas.microsoft.com/mapi/proptag/x0fff0102"" " & _            "from scope('shallow traversal of """ & strUrl & """') "        Rs.Source = strSQL   Rs.Open        Dim arrEntryID As Variant   Dim i As Integer        arrEntryID = _     Rs.Fields("http://schemas.microsoft.com/mapi/proptag/x0fff0102").Value   For i = 0 To UBound(arrEntryID)       strEntryID = strEntryID & Right("0" & Hex(arrEntryID(i)), 2)   Next        Rs.Close   Rec.Close 

Working with Attachments in ADO

Exchange has some properties that make it easier to work with attachments in ADO, rather than CDO. They are listed in Table 15-5. Every item in Exchange exposes these properties.

Table 15-5: Properties for Attachments in ADO

Property	Description
http://schemas.microsoft.com/exchange/attachlist	An array that contains the URLs to all attachments in an item
http://schemas.microsoft.com/exchange/attachcontenttype	An array that contains all the content types, such as application/msword or application/msexcel for all attachments on an item
http://schemas.microsoft.com/exchange/attachname	An array that contains the friendly display name for all attachments on an item

The three properties work together. For example, if you want to know the name, type, and URL of the third attachment on your item, you can grab all three properties and go into the third entry in each array to get the values. Be aware, though, that these properties return only top-level attachments. If you have attachments embedded in attachments, you must open up the item using the correct CDO object for the type of item to get at the embedded attachments. Also, these properties are supported only on items, not folders (as you would expect) or freedocs (embedded items such as Word documents stored directly in Exchange that cannot have attachments). You must retrieve these properties from the Fields collection for the item. The following code shows how to use these properties with the ADO Record object:

 Dim oRecord as new ADODB.Record oRecord.Open "file://./backofficestorage/domain/ " _            & "Public Folders/myFolder/myworddoc.doc" arrAttachURLs = oRecord.Fields("http://schemas.microsoft.com/" & _                                "exchange/attachlist").value arrAttachNames = oRecord.Fields("http://schemas.microsoft.com/" & _                                "exchange/attachname").value arrAttachContentTypes = oRecord.Fields("http://schemas.microsoft.com/" & _                                "exchange/attachcontenttype").value For i=LBOUND(arrAttachURLs) To UBOUND(arrAttachURLs)     MsgBox "The name of the attachment is: " & arrAttachNames(i)     MsgBox "The content type of the attachment is: " _           & arrAttachContentTypes(i)     MsgBox "The URL to the attachment is: " & arrAttachURLs(i) Next 

Note  

These attachment properties are available only via EXOLEDB and ADO. You cannot retrieve them using WebDAV. With WebDAV, you should use the X-MS-ENUMATTS command to retrieve attachment information; we will discuss this in the next chapter.

Handling Errors in ADO

You must always 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, which 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 in the collection. When you find the errors, you can take action on them. For example, you can roll back 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 transactions when you work 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. Because Exchange Server transactions are different from 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 you use 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 example 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 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 work with a transaction by using one ADO Connection object. On this object, you must first 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 When Using ADO

I suggest three best practices for working with ADO and Exchange Server. First, learn your Exchange Server properties well. When you work with ADO, you need to remember all the different properties in all the different namespaces that Exchange Server provides. The most common ones you will work with are listed here. For the full list of properties, refer to the Exchange Server SDK.

• 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, using transactions allows you to roll back the Exchange Server database to the original state in case of an error. Second, using transactions is key when you work with events to eliminate erroneous event firing. 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 is entered into the Exchange Server database only when the transaction is committed, thereby causing only one event to fire.

The third best practice for working with ADO is to reuse existing connections. 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.