VSM Cover Story
Databind to Office Apps With Web Services
Create powerful Office applications using Visual Studio 2005 and Web services.
Technology Toolbox: C#, SQL Server 2000, Visual Studio 2005 beta 2, Visual Studio Tools for Office 2005 beta 2
longstanding desire since the release of Visual Studio .NET is to be able to write custom solutions for Office in managed code without exiting the Visual Studio IDE.
Visual Studio Tools for Office 2005 (VSTO) finally lets you do this, enabling you to write managed code that runs inside Word and Excel 2003. Taken together, the combination of VSTO 2005 and Visual Studio 2005 brings Office development to the forefront by giving developers a much richer set of tools to access and manipulate Office documents programmatically.
I'll show you how to take advantage of these tools to create a custom Excel application. On top of that, you'll learn how to take advantage of Web services and the new databinding mechanism in VSTO. Note that this article relies on the beta 2 of VSTO and Visual Studio 2005, so specific aspects are subject to change before the final release. Regardless, the principles exhibited by developing this kind of app will serve as a good model for future Office application development when using VSTO.
Taking advantage of VSTO requires some setup. Begin by installing Visual Studio 2005 beta 2, VSTO 2005 beta 2, and Microsoft Office 2003 Professional. The example in this article also relies on a SQL Server database. This specific example uses SQL Server 2000, but MSDE or SQL Server 2005 should work equally well. Note that VSTO 2005 requires Office Professionalit won't function correctly if you use any other version. As always, I highly recommend having a clean image to work with because the install can get messy, and as usual when working with beta software, you should install this suite of applications on a PC that isn't mission-critical.
Once you install everything, open Visual Studio 2005, and create a new project, you should see an option under Office named Excel Workbook. (If not, you probably suffered an installation issue.) Select Excel Workbook and name your new workbook ExpenseReport. Visual Studio then asks you whether you want to create a new document or select an existing one. Create a new "smart" document by selecting "Create a new document"; name the document ExpenseReport.
At this point you should see something new to Visual Studio .NET: The Excel designer is fully integrated into the IDE. This new feature is one of many improvements Microsoft has made in Visual Studio 2005 for developing Office-based applications. You no longer must toggle between applications or use VBA to work with Excel or Word; simply use the Excel integration as you would the Windows Forms designer.
Next, drag six labels onto the worksheet and label them like this: User Id, Name, Manager, Email, Department, and Phone. Now drag a textbox (txtUserID), five of the new Named Range host controls (nrName, nrManager, nrEmail, nrDept, and nrPhone), and the List Object host control (lstExpense) onto the ExpenseReport sheet (see Figure 1). Complete your UI by dragging the btnUpdate and btnGet buttons onto the sheet. This article's sample code also adds some styles to the sheet by creating a heading and adding some borders with a bit of color, but the application is functional without these additions (download the sample code here).
Implement VSTO Databinding
So far, you've dragged some "new" controlsdubbed host controlsonto the Excel worksheet. These new controls are extensions of native Word 2003 and Excel 2003 objects and comprise two of the more interesting new features in VSTO 2005. When you add host controls to an Office document, they act and behave like an Office object. This means you get access to the core object model in addition to these controls' new functionality, including databinding and events. Excel gives you access to three new controlsNamed Range, XMLMappedRange, and ListObjectwhile Word gives you access to Bookmark, XMLNode, and XMLNodes.
Your UI is now complete, so you can jump into the main functionality of VSTO and the sample application. One significant new aspect of VSTO is its built-in databinding functionality. Databinding in VSTO projects works identically to databinding in Windows Forms projects in Visual Studio .NET: You use the Data Sources window in the IDE to connect to your data source, which gives you the option of dragging and dropping it onto your document. In addition to the standard direct database binding, Visual Studio 2005 also lets you bind to an object, a Web service, or any other managed provider (SQL Server, Oracle, and so on).
The sample code implements a combination of Web services and object binding. Before drilling down on the finer points of databinding, you need to create your database, Web services, and the data object. Start by creating two tables in SQL Server. First, create a Person table with these columns: User_ID (primary key), Name, Manager, Department, Phone, and Email (all VARCHARS for simplicity's sake). Next, create an Expense table with these columns: ID (an INT identity field), User_ID, Type (both VARCHARS), Total (INT), CurrentDate (smalldatetime), and Description (text). Finally, add a foreign key relationship between the User_ID fields in the two tables. For you DBA types, remember the focus of this article is VSTO and Web services databinding, not data normalization.
Next, add a new Web site to your Expense Report solution by clicking on File | Add | New Web Site. A template window will open with many options; select ASP.NET Web Service and name it ExpenseReportWS. After you add the new project to your solution, rename the autogenerated ASMX file DataBroker. Open the code-behind file for the DataBroker.asmx file and add a new Person class. This new class acts as the serializable data object used in the GetPerson Web service you'll create. The Person class is an object that contains public properties for each column in the Person table (see Listing 1). The GetPerson Web service accepts a userID string parameter to query the database using a SqlConnection, a SqlCommand, and a SqlReader. The second Web service is an UpdateExpense service, which accepts an untyped DataSet as a parameter and then uses a SqlConnection, a SqlCommand, and a SqlDataAdapter to insert the data into the Expense table.
I know some of you just read "untyped" DataSet and your hearts skipped a beat. Most of us know from reading various Web services pundits that the metadata and the contract are the most important aspects of Web services design. Specifically, you lose that wonderful metadata when you use a polymorphic container such as a DataSet without strongly typing it. But there remain good reasons to adopt such an approach. First, using an untyped DataSet lets me demonstrate the parallel between the ListObject and the DataGrid control. Also, it distracts from the sample to detour into a topic such as Web services methodologies, which is the subject of entire books.
After you create the backend Web services, switch over to the Excel GUI and open the Data Sources window to add a new reference to the DataBroker Web services. Use the Data Sources wizard and select Add New Data Source. This prompts you to select the type of data source (Database, Object, or Web Service); select Web Service, then select Web services in this solution. You should see the DataBroker Web services, including GetPerson and UpdateExpenseDatathe two methods you created. Select Add Reference and the Data Sources window populates with a reference to the Person object, which VS 2005 enumerates completely (see Figure 2).
Enable the Bound Fields
The new databinding functionality included in the host controls means you can use the Properties window and bind each of your NamedRange fields (nrName, nrManager, and so on) to the corresponding property hanging off the Person object. As you navigate to the DataBinding field in the Properties window and select the Data Source, a new reference (PersonBindingSource) is placed in the designer tray. Once the NamedRange fields are bound, all you must do is write two lines of code to enable the bound fields (which are invoked in the btnGet click event):
localhost.DataBroker db =
The second requirement for enabling your Excel GUI is to give the ListObject (lstExpense) some brains. I mentioned previously that using an untyped DataSet lets me demonstrate the parallel between the ListObject and the DataGrid control. The ListObject behaves just like a DataGrid, so you can use a container, such as a DataTable, for databinding. Create a DataTable with a structure identical to the Expense table, but set a default value for the User_ID field that equals the client's input in the txtUserID field. After instantiating the DataTable and setting the column names, bind to the ListObject and set the fields you want to display:
DataTable expenseTable =
DefaultValue = txtUserID.Text;
In addition to binding the ListObject, you must add some code to invoke the UpdateExpenseData Web service. Call the UpdateExpenseData Web service by creating a reference to the DataBroker, retrieve the DataTable from the DataSource of the ListObject, and add it to a new DataSet, passing it as a parameter to UpdateExpenseData. The UpdateExpenseData transaction is a fire-and-forget Web service, so you only get a result back if an error occurs. Be sure to add a Try/Catch block to trap errors coming back from the Web service so you can handle them gracefully (see Figure 3).
Finally, you must take care of validation in the ListObject. Take advantage of the event notification system built into the ListObject control to ensure the data being entered is in the correct format. You use the BeforeAddDataboundRow event to validate data. After you add the event handler, it is simply a matter of getting a reference to the Row using the DataRowView object and validating the input data (see Listing 2).
You have three distinct deployment models to choose from when deploying a VSTO application. First, you can migrate the document and code to the client machine. The advantage of this option is that everything is in one place and you don't need a network connection. The disadvantage is that because everything is in one place, automatic updates are out of the question. The second option is to store the document on the client machine and store the code on a network share. This option works well in corporate situations where users need to create and modify documents, but remember: If the base template changes, you must still push it to the client machines. The third option is to store both the document and the code on a network share. This enables maximum automation from an administration standpoint, but it also prevents you from using a disconnected model.
At this point, you can build a basic solution in Excel. Ways to extend this application might include using typed datasets, adding more validation and event handlers for a better user experience, and modifying the data model so it's closer to what you would see in a real-world expense-reporting application. In addition to extending the example, you might want to explore VSTO data caching, server-side development, InfoPath, and other means of extending your application through the use of Web services.