Connect Office Apps to .NET with "Lite" Web Services

Many of us would like to be able to take advantage of web services and access .NET data from Microsoft Office applications such as Excel using VBA. Imagine your users opening an Excel spreadsheet and connecting it to your ASP.NET application in order to view data in a rich environment with equations and charts, and even being able to upload data, as in Figure 1. You could create web services using the Office XP Web Services Toolkit 2.0, but then you will need a deployment package that includes Microsoft XML (MSXML) and Microsoft SOAP (MSSOAP). At times we can't require client installations because, for one reason or another, the workstations and laptops that will be running office applications are out of our control. Although Office 2003 will provide even greater support for interacting with .NET and web services, it may be a while before many of our clients are upgraded. Luckily, it is possible to use existing technology to communicate between .NET and versions of Office products from the 2000, XP, and 2003 family. It might even work with Office 97, although I haven't tried it.

Before I get into the details, I should mention Visual Studio Tools for Office (VSTO). If you perform a complete install of Office 2003 on a machine where the .NET Framework 1.1 is installed, then the Office 2003 Interop assemblies will also be installed into the Global Assembly Cache. You may then download and install VSTO. The tools will allow you to use Visual Studio .NET 2003 along with C# or VB.NET to write code behind Word 2003 and Excel 2003 documents. You create, run, and debug these projects from VS.NET as you would any other .NET project, and you have the Excel and Word object models available as you would in VBA. Your code behind compiles into a DLL that can either reside locally with the Excel or Word document, or be placed in a location such as a network share or web site where the document will look for it (as well as updates to it). VSTO gives you all of the benefits of using .NET — the security of managed code, ADO.NET, XML, no-touch deployment, a feature-rich IDE, etc. — and allows you to use it to program Word and Excel documents, which also has new built-in functionality for working with XML data. Read more at http://msdn.microsoft.com/vstudio /office/officetools.aspx.

With VSTO, you can easily call web services just as you would from any other C# or VB.NET application, and you can write your entire Word or Excel (2003) application without using VBA at all. You don't need to do the extra plumbing in .NET and VBA that I will show you how to do in this article — so if your user base will soon have Office 2003, then you should be looking at VSTO. But if you still need to support several versions of Office, this technique is for you.

In this article, I will show you step-by-step how to create a "lite" web service by using ASP.NET, and how to call it from VBA. I will take advantage of a class in MSXML called "XMLHTTPRequest." Aptly named, this class handles all of the plumbing to make an HTTP request and receive an XML response. Fortunately, MSXML began shipping with Internet Explorer version 4.01 so, unlike MSSOAP, we can assume our clients already have MSXML installed. To package the data up into XML format, I will create a disconnected ADO Recordset on the fly. The Recordset's Save method has an option to persist the Recordset in its own XML format. ADO can be used from both VBA and .NET (via COM Interop), but since many of us are already working with ADO.NET, I will show you how to convert DataSets to and from Recordsets. This article assumes that you have access to Visual Studio .NET 2002 or 2003, a SQL Server with the pubs database, and Excel. The listings are written in C# and VBA, and are available for download.

Convert a DataTable to a Recordset

Let's assume that you already have some code that uses ADO.NET and returns important data in the format of a DataSet. It could simply be querying data from a database or an XML file, or it could be the result of some complex business logic. But you can't access ADO.NET objects in VBA, so we need to convert the data into a format that we can easily work with in VBA — Recordsets. A DataSet may actually contain several DataTables, so we will convert a DataSet to a collection of Recordsets. To get started, launch Visual Studio .NET and create a new C# ASP.NET web project called "LiteWebServicesCS." Remove WebForm1.aspx — you won't need it. Then add a new code file to the project called "ADOUtility.cs." You will also need to add a reference to adodb (under the .NET tab) to the project's references. This is a .NET COM-Interop assembly that lets us work with ADO. It is not installed with the .NET Framework, but it comes with Visual Studio .NET, so you'll need to deploy it when you deploy your ASP.NET application.

As you can see in Listing 1, ConvertDataTableToRecordset is a static method that takes a DataTable as a parameter and returns a Recordset. This is done by creating a new instance of a Recordset. You may only be familiar with receiving Recordsets as output from ADO. In this case, I am making use of an ADO feature that allows the creation of disconnected Recordsets from scratch. The next step is to loop through the columns in the DataTable and create corresponding fields in the Recordset. This is done by looking up the .NET data type and retrieving an equivalent ADO data type and maximum size. Finally, I loop through the Rows collection in the DataTable, retrieve the values for each row, and add them to the Recordset. You might have noticed that I do some special processing for decimal data types when I create the Recordset's fields — you will need this code if you use the decimal data type, otherwise you might lose precision. I also do special processing for globally unique identifiers (GUIDs); otherwise ADO will not recognize them as such.

Listing 2 contains the lookup method to determine the proper ADO data type and size for a given .NET type. The ADO.NET Column object's DataType property returns a System.Type object, which is passed into GetADOType. I chose to create a two-dimensional object array to hold the lookup data. I use the C# typeof keyword to get an instance of the correct object; typeof(System.Boolean) creates a System.Type object corresponding to System.Boolean, for example. I loop through the array looking for the specified type, and then return the equivalent ADO data type and maximum length as output parameters. I went through the available ADO data types and included each one that could easily be converted, and this will cover all but the rarest situations. There is an exhaustive list in the ADO API Reference at http://msdn.microsoft.com/library/ en-us/ado270/htm/mdaenumdm.asp.

Persist Recordsets as XML

The Recordset's Save method can be used to persist the Recordset in its very own XML format. Likewise, the Open method of the Recordset will recreate the Recordset from that persisted XML. The XML itself is not very useful to us, but just by being XML, it can easily be manipulated. At this point, my goal is to create a method to return the Recordset's XML as a string, but the Save method saves it to a file, or, starting with ADO 2.5, to an ADO Stream object. A few short lines of code perform this task easily in the ConvertRecordsetToXmlText method (see Listing 3).

Recalling that a DataSet contains a collection of DataTables, we need a way to turn several Recordsets into one XML document that can be returned to the client. The System.Xml namespace allows us to manipulate XML in a variety of ways, and now we can reap the benefits of having our Recordset in XML format. Since XML is hierarchical, I create a new XML document with an element called "results" to represent multiple Recordsets. I then loop through the tables in the DataSet and add each one's Recordset XML as a child of the results element. Since the Recordset's XML is an XML document in its own right, I use the ImportNode method to bring a Recordset as a child element into the new document. The ConvertDataSetToXmlDocument method appears in Listing 4.

Creating a Lite Web Service with IHttpHandler

Now that we have a way to represent our data in XML format, we need a way to make it available to our client applications. We can build our service as an Http Handler and let ASP.NET take care of the rest. I call this a lite web service because it only has a few of the characteristics of a real web service: It uses XML and HTTP, but does not use SOAP or conform to any other standards. An ASPX page, or web form, is really just a specialized Http Handler. To build an Http Handler, start out by opening the Class View window, right-clicking on the project, and selecting Add Class. In the wizard, set the class name to GetAuthors and then select the Inheritance tab. Set the current namespace to System.Web and then add IHttpHandler, followed by Finish. In the class view, find GetAuthors in the class hierarchy, open Bases and Interfaces, right-click IHttpHandler, and select Add / Implement Interface. This will automatically create the properties and methods of the interface, so all you will need to do is fill in the code. Of course, you could have done all of this from scratch, without using the tools in the Class View, but it is useful to know about the Implement Interface command.

The ProcessRequest method is all that we really need to deal with. I deleted the constructor that the wizard created, and I didn't touch the code that returns True for IsReusable, because I will implement the class such that a given instance could be invoked multiple times. The only parameter to ProcessRequest is an HttpContext object. The HttpContext object exposes everything we will need including the Request and Response objects. This means that we can look at query parameters, get and set cookies, and so on, just as with a Web Form. In fact, all we really need to do is write our XML to the response stream.

I added a method to the GetAuthors class called GetAuthorsData, which returns a DataSet containing the results of "select * from authors" from the database. The data access code looks for the connection string to be defined in the Web.Config file, so add it now just below the <configuration> tag. It should look something like Listing 5. The ProcessRequest method simply retrieves the data, calls ConvertDataSetToXmlDocument, and then writes the documents XML as the response. I also set the response's Content-Type header to "text/xml," and I set the CacheControl property to "no-cache" since I don't want clients to cache the results. The complete GetAuthors class is in Listing 6.

There is one last step before our service can be accessed. We need to map a specific URL to our Http Handler. This is done in the Web.Config file by adding an entry to the <httpHandlers> section, which you may need to add under <system.web>. We need to tell ASP.NET what type of HTTP "verbs" we will handle (GET, POST, etc.), what "path" to look for in the URL, and the Http Handler "type" (class) that will handle the request. See Listing 7 for my mapping of GetAuthors. Note that I used "asmx" as the extension in the path. I also could have used "aspx," since both are mapped to ASP.NET in IIS when .NET is installed. Choosing another extension may require additional configuration in IIS in order to get things working. The verb of "*" means that it will handle all types of requests. See "Registering Http Handlers" in the .NET Framework documentation for more information.

At this point, you should be able to build your solution and do a quick test. Don't try to run it from Visual Studio because there is no startup page in your project. However, after you have built it, open up Internet Explorer and enter "http://localhost/LiteWebServicesCS/ GetAuthors.asmx" into the address bar (change the "LiteWebServicesCS" part if your web project is configured differently). You should see the raw XML returned by the GetAuthors Http Handler. If you do want to run your service from Visual Studio — and you will want to in order to set breakpoints and debug it — then you can simply add a blank HTML page to the project and set it as the startup page.

Creating the VBA Client

Now that the lite web service is in place, the next step is to write the client code in VBA to access it. I will use Excel as a client, so open Excel and draw a new Command Button somewhere on the sheet. You might need to select the Control Toolbox by right-clicking the toolbar first. Double-click the Command Button to create the CommandButton1_Click event code in VBA, but don't add any code to it yet. Instead, add references to the VBA project for MSXML and ADO, since we will be using those objects. Go to Tools/References and check both "Microsoft ActiveX Data Objects 2.5 Library" (or later) and "Microsoft XML, version 2.0" (or later).

Next, create a function called InvokeLiteWebService, as in Listing 8. This method takes three parameters: the URL of the lite web service, an optional request, and an optional verb. The request is a variant containing data that should be sent to the service being invoked. So far, I have only discussed receiving data from the service — we can send data, too, as I will show you later in this article. The third parameter is the verb to be used for the HTTP request. For retrieving data, "GET" is fine. If we are going to send request data as well, then "POST" should be used.

To invoke the service, I create an instance of MSXML.XMLHTTPRequest. I use the Open method of this object, passing in the verb and the URL. I also specify that I want the call to be synchronous. In the Open method you may optionally specify a username and password, which could be used in conjunction with IIS and/or ASP.NET security. Next, I check to see if request data has been passed into InvokeLiteWebService that I need to send to the service. If not, I invoke the Send method of the XMLHTTPRequest without any parameters. If there is a request, then I first set the Content-Type header for the request, and then I call the Send method, passing the data to be posted.

The Send method invokes the service and receives the response. I'm expecting an XML response, but trial and error has taught me that if something goes wrong on the server side (such as an exception being thrown), then I might end up with an HTML response with an error message, so I check the response text to see if it starts with an <html> tag, and I raise an error if it does. If not, then everything went well and I retrieve the response as an XML document and return it. XMLHTTPRequest takes care of converting the XML text to an XML document for us. Note that either ASP.NET or IIS might return an HTML page if something goes wrong. You could write code to handle ASP.NET errors and package the error into the XML response with the recordsets, and look for it in the VBA code. You could also try and parse the text of the HTML response, using MSHTML for example, to show a text message to the user. I'll leave this as an exercise for the reader.

Converting the XML Back Into Recordsets

The ConvertXmlDocumentToRecordsetsCollection function in Listing 9 will reverse engineer the XML document. On the server, I added each Recordset's XML as a child node of the document I created. Now, I loop through the child nodes and call a function to convert the XML from each node into a single Recordset, and add each Recordset to a Collection. The code to convert a single Recordset's XML back to a Recordset, in Listing 10, writes the XML text to an ADO Stream object and then uses the Recordset's Open method to load the data from the stream — the opposite of how the XML was first created on the server.

Now we can write the code for the click event of the Command Button. I call the InvokeLiteWebService function and pass the results to ConvertXmlDocumentToRecordsetsCollection. The GetAuthors service only returns one result set, so the collection will only contain one Recordset. I use the CopyFromRecordset method of the Excel Range object to copy the data into Sheet1; see Listing 11. Go back into Sheet1 in Excel, and click the command button (make sure you are no longer in design mode by toggling the toolbar button, if necessary). The data from the pubs table is loaded into Sheet1.

Sending Data to the Server

At this point, we have a back-to-front solution for exposing the data from a .NET application as a lite web service that can be called from a VBA application. Now, I'll show you two ways to send data from a VBA application to a lite web service. The first is by simply adding a query string to the service's URL. The second is by sending an XML document representing multiple Recordsets — the same format that we are receiving data in.

I may want to create a lite web service to return titles for a given author, for example. This is a good case for using a query string to send data to the server. By simply putting the author's ID into the query string, the service can return the titles that correspond to that author. Since some characters in a query string need to be URL-encoded, I created a simple function in VBA to do URL encoding (see Listing 12). The basic idea is to append something like "?au_id=486-29-1786" to the URL. Test this out by adding another command button to the Excel sheet and adding the same code to the event as with the first command button, but this time append the query parameter to specify an author, as in Listing 13. Copy the GetAuthors handler in the ASP.NET application to create GetTitles. Don't forget to add another entry to the Web.Config file for GetTitles. Add some code to the GetTitles handler to retrieve the author ID and then use it in the SQL query, as in Listing 14.

Passing query parameters and accessing them from a lite web service is an easy way to send a couple of scalar parameters to the service. However, you may need to create a service to which you can upload more data. The GetAuthors and GetTitles examples that we have discussed already both return data. This isn't a requirement, though. You could create a service to accept nothing and return data (as in GetAuthors), to accept data and return nothing, or to accept data and return data. In any case, I use the same model to upload data from the VBA code as I do to return it from the .NET service. This is done by putting the data to be uploaded into a collection of one or more Recordsets, converting that into an XML document (this time with VBA code), and posting it to the service. The service then converts the XML document to a DataSet, making it ready for your .NET code to work with. The process is really the same as the code that we have already covered, except that this time the code to create the XML document is in VBA, and the code to convert from it is in .NET.

Creating a Recordset in VBA code is similar to the .NET code. Listing 15 shows how you might create a Recordset from some data in an Excel spreadsheet. Notice that I specify the data type for each field in the Recordset. In the .NET method ConvertDataTableToRecordset, I also find the corresponding data type for each Recordset field. In both cases, the Recordset is strongly typed — that is, you will only be able to put values into the records that match the defined types. There is a special data type in ADO called "adVariant." We could simply define each field as an adVariant data type, avoiding the additional code to set each field's type, or convert to or from .NET data types. This would allow us to create a Recordset easily from any range of data in Excel, as in Listing 16. Inspection of the Recordset's XML shows that ADO apparently persists variant data as strings. Listing 17 (available online) shows the code to convert a collection of Recordsets into an XML document. Listing 18 (available online) shows how this data could then be sent to the server. Notice that I pass the XML document object itself as the request. The Send method of XMLHTTPRequest will post the XML document as the HTTP request.

Receiving the Posted Data

The service needs to parse the HTTP request back into an XML document. Listing 19 shows how this is done by passing the HttpContext's Request object's InputStream into an XmlReader, which is used to load an XmlDocument. Listing 20 shows how to convert the XML back into a Recordset, and Listing 21 shows how to convert the Recordsets into a DataSet. The main point to note in Listing 21, ConvertXmlDocumentToDataSet, is that I take advantage of the OleDbDataAdapter's Fill method. An overload of the OleDbDataAdapter's Fill method actually takes an ADO Recordset and fills a DataTable from it, so I didn't need to write code to do a manual conversion. Once the data is in a DataSet, it is ready to be used by your .NET functionality. This completes the circle of communications between .NET and a VBA application — any data that you can express as a DataSet or Recordsets can be passed to the lite web services and/or received from them.

Security & Deployment

Because lite web services are hosted by IIS and ASP.NET, you can take advantage of your existing security infrastructure. You can set up IIS to use SSL and then make the request URL use HTTPS instead of HTTP. You can also use authentication — recall that the Open method of XMLHTTPRequest optionally takes a user and password. Even integrated windows authentication will work — if the IIS site and Web.Config files are configured for it, leave out the user and password in the Open method. The credentials of the user who is logged on to the workstation will be used for the call, or the user will be prompted by Internet Explorer to provide credentials. But in either case, you can make use of integrated windows authentication without any extra coding.

One note on deployment — there is an issue with deploying the ADO Interop assembly adodb.dll. The "Copy Local" option for deploying web server applications copies a particular assembly locally with the rest of the web application. The other option is to copy that assembly to the GAC (global assembly cache). If you have multiple ASP.NET web applications installed on the same machine, then adodb.dll must be installed in the GAC. You will get errors if more than one site tries to use local copies. See the KB article 321688 for more information (although it doesn't specifically address this issue, it discusses deploying adodb.dll).

Conclusion

In this article, I've shown how to convert data from ADO.NET into a form that can be used easily from VBA applications, and how to create lite web services to provide an interface between your Office VBA applications and your .NET functionality. You won't have to worry about providing the users with a potentially problematic setup program because this works with DLLs that are already installed on their machines. I successfully implemented this architecture in a hedge fund application that included a complex Excel spreadsheet for reporting purposes. The rest of the application was written in C# and ASP.NET, and I wanted the Excel component to access services in the .NET application, without any knowledge of the underlying database. I was able to use lite web services to easily create a secure way for the Excel application to communicate with the business layer in .NET. With lite web services, you now have the power to connect your Office applications to your .NET development efforts.