Chapter 4: Integrating Web Services into Your Office Business Applications

Article
05/05/2014

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

This article is an excerpt from Programming Microsoft Office Business Applications by Steve Fox, Rob Barker, Paul Stubbs, Joanna Bichsel, and Erika Ehrli Cabral, from Microsoft Press(ISBN 9780735625365, copyright Microsoft Press 2008, all rights reserved). No part of these chapters may be reproduced, stored in a retrieval system, or transmitted in any form or by any means—electronic, electrostatic, mechanical, photocopying, recording, or otherwise—without the prior written permission of the publisher, except in the case of brief quotations embodied in critical articles or reviews.

Contents

The Need for S+S in Today's Enterprise Solutions
S+S and the 2007 Microsoft Office System
Job Candidate Recruiting Interview Feedback Solution
Summary
Further Reading and Resources

The Need for S+S in Today's Enterprise Solutions

The demand on enterprises to be leaner, to have greater agility, to be more efficient, and at the same time to surpass customer expectations, is growing continuously with the competitive market. Enterprises have been increasing their spending steadily in IT. In fact, Forrester Research says that in 2007, “enterprises expect to increase their IT budgets by 3.8 percent, up from a 3.2 percent planned increase at this time last year.” This huge amount of money that goes into IT results in tools that are often disparate and not familiar to the end users who need to learn to use these complicated line-of-business (LOB) applications. More often than not, end users are most comfortable working in Microsoft Office environments and interfaces than in these older, traditional LOB systems that have a very steep learning curve and require a lot of content switching, like copying and pasting, which result in work inefficiencies and general loss of productivity. One example of this happens when a salesperson needs to go into several different systems to update his customer’s information after the sale has been made. Mistakes are made, and often the task is forgotten. This is one reason why 42 percent of customer relationship management (CRM) licenses that are purchased are never actually deployed (Gartner Group) and why 57 percent of SAP customers don’t believe they’ve achieved a positive ROI from their implementations (Nucleus Research). This is something that we call the “results gap”—the disconnect between IT solutions and the real workflow of the information worker.

While LOB systems are foundational to enterprise productivity, it’s important to step back and ask what the source of the competitive factor and profit driver for the company is. Is it the LOB systems, or is it the people—the individuals completing the sales, the employees meeting with customers, and those creating the products? I believe it is the people who drive businesses because it is the employees who create the customer leads, who raise the bottom line, who innovate, who develop strong customer relationships, and who improve operations. Once we’ve identified the source of the competitive factor and profit driver, it would be wise to start building the processes and applications around the source and make the technology support the people rather than make the people conform to the software and infrastructure. There is no need to throw out any existing investments; it would be best to leverage our current investments and bridge the gap between the back-end and the front-end applications.

Across organizations, and for all information workers, there is the problem of too much data in too many disparate places. Enterprises have data stored in their LOB systems, in their corporate portals, on employees’ hard drives, in e-mail, in records repositories; we are in the era of information overload. One of the big challenges that IT faces today is giving end users the ability to work productively by not requiring them to go to all of the systems in which the enterprise has been invested. Creating solutions based on Software + Services (S+S) results in systems that bring data from all these systems together into one application, so that while the reality is that all this data still exists, managing the data becomes easier.

IT departments have long suffered with the problem of deploying fixes and updates to each employee in the organization. Luckily, this is a challenge that is easily solved by creating solutions based on S+S as a result of the natural code reuse inherent in the architecture. The code is centralized so if a fix needs to be made to the service, this automatically gets picked up by the clients with no interruption to the application.

With the challenges that enterprises face today, like getting a positive ROI from their existing LOB systems, challenging IT to create solutions that mimic the real information worker processes, not just those of the LOB system, and unifying all of our disparate systems to combat information overload, it is easy to see why enterprises are starting to turn to a more agile, service-oriented approach that works around the information worker. So how do we get from disparate, hard-to-use systems to integrated and interoperable applications? That’s where S+S and OBAs enter the picture.

S+S and the 2007 Microsoft Office System

The 2007 Microsoft Office system is a natural fit for Software + Services for two main reasons:

Office has exposed a rich set of services that include features such as security, calculation, Enterprise Search, workflow, and single sign-on.
Office provides the presentation layer for displaying the results of this rich data retrieval into the client applications with which end users are most often comfortable.

The goal of the Office platform here is to achieve integration between software and services and to simplify the consumption of these services for the developer. In addition, Microsoft Visual Studio 2008, which contains Microsoft Visual Studio Tools for the Microsoft Office system, provides RAD development tools so that teams involving anyone with the skill set of the designer to the professional developer can work collaboratively to create rich, service-oriented Office Business Applications. Let’s take a closer look at the services available in Office today.

Services Available in the 2007 Office System

Windows SharePoint Services 3.0 sits at the base of the SharePoint Products and Technologies stacks and offers a lot in terms of administrative and management services, as shown in Figure 4-1.

Figure 4-1. Web services exposed off of Windows SharePoint Services 3.0

Web service exposed by Windows SharePoint Services

Microsoft Office SharePoint Server, which sits on top of Windows SharePoint Services architecturally, contains the bulk of the services. Office SharePoint Server contains many powerful new features, such as Excel Services (the Excel worksheet publishing tool, which we will talk more about later on), Forms Services (a thin client of Microsoft Office InfoPath), the Business Data Catalog, which enables you to connect to any LOB system and surface that data into your portal without writing any code, Enterprise Search, workflow, and more. Here is a reference of all the Web services available in Office SharePoint Server.

Table 1. Web services available in Office SharePoint Server

Microsoft.Office.DocumentManagement.Server	Microsoft.Office.Workflow
Microsoft.Office.DocumentManagement.WebControls	Microsoft.Office.Workflow.Utility
Microsoft.Office.Excel.Server.Udf	Microsoft.SharePoint.Portal
Microsoft.Office.Excel.Server.WebServices	Microsoft.SharePoint.Portal.Administration
Microsoft.Office.Excel.WebUI	Microsoft.SharePoint.Portal.Analytics
Microsoft.Office.RecordsManagement.Holds	Microsoft.SharePoint.Portal.Analytics.Processing
Microsoft.Office.RecordsManagement.InformationPolicy	Microsoft.SharePoint.Portal.Audience
Microsoft.Office.RecordsManagement.PolicyFeatures	Microsoft.SharePoint.Portal.Search.Admin.WebControls
Microsoft.Office.RecordsManagement.RecordsRepository	Microsoft.SharePoint.Portal.Search.PortalCrawl
Microsoft.Office.RecordsManagement.Reporting	Microsoft.SharePoint.Portal.Security
Microsoft.Office.RecordsManagement.SearchAndProcess	Microsoft.SharePoint.Portal.ServerAdmin
Microsoft.Office.Server	Microsoft.SharePoint.Portal.SingleSignon
Microsoft.Office.Server.Administration	Microsoft.SharePoint.Portal.SingleSignon.Security
Microsoft.Office.Server.ApplicationRegistry.Administration	Microsoft.SharePoint.Portal.SingleSignonAdministration
Microsoft.Office.Server.ApplicationRegistry.Infrastructure	Microsoft.SharePoint.Portal.SiteAdmin
Microsoft.Office.Server.ApplicationRegistry.MetadataModel	Microsoft.SharePoint.Portal.SiteAdmin.General
Microsoft.Office.Server.ApplicationRegistry.Runtime	Microsoft.SharePoint.Portal.SiteData
Microsoft.Office.Server.ApplicationRegistry.Search	Microsoft.SharePoint.Portal.SiteDirectory
Microsoft.Office.Server.ApplicationRegistry.SystemSpecific.Db	Microsoft.SharePoint.Portal.Topology
Microsoft.Office.Server.ApplicationRegistry.SystemSpecific.WebService	Microsoft.SharePoint.Portal.UserProfiles
Microsoft.Office.Server.ApplicationRegistry.WebService	Microsoft.SharePoint.Portal.UserProfiles.AdminUI
Microsoft.Office.Server.Audience	Microsoft.SharePoint.Portal.WebControls
Microsoft.Office.Server.Auditing	Microsoft.SharePoint.Portal.WebControls.Alerts
Microsoft.Office.Server.EvaluatorModeProvisioning	Microsoft.SharePoint.Portal.WebControls.WSRPWebService
Microsoft.Office.Server.Infrastructure	Microsoft.SharePoint.Publishing
Microsoft.Office.Server.Search.Administration	Microsoft.SharePoint.Publishing.Administration
Microsoft.Office.Server.Search.Administration.Security	Microsoft.SharePoint.Publishing.Administration.WebServices
Microsoft.Office.Server.Search.Query	Microsoft.SharePoint.Publishing.Design.WebControls
Microsoft.Office.Server.Search.WebControls Microsoft.Office.Server.Security	Microsoft.SharePoint.Publishing.WebControls .EditingMenuActions
Microsoft.Office.Server.UserProfiles	Area Web Service
Microsoft.Office.Server.Utilities	Official File Web Service
Microsoft.Office.Server.WebControls	Published Links Web Service
Microsoft.Office.Server.WebControls.FieldTypes	Search Web Service
Microsoft.SharePoint.Publishing.Fields	User Profile Change Web Service
Microsoft.SharePoint.Publishing.Navigation	User Profile Web Service
Microsoft.SharePoint.Publishing.WebControls	Workflow Web Service

For more information about these namespaces and references, refer to the Class Library and Web Service Reference.

By exposing these services, the Office server platform empowers developers to start building their own custom solutions with rich Office features and functionality. For example, let’s drill down into Excel Services found in Office SharePoint Server. In Figure 4-2, you can see the ExcelService class found in the Microsoft.Office.Excel.Server.WebServices namespace and all of its public methods.

Figure 4-2. Public methods of the ExcelService class found in the Microsoft.Office.Excel.Server.WebServices namespace; Excel Services is a new feature in Office SharePoint Server 2007

ExcelService class public methods

Excel Services has full calculation fidelity with the client, so if you consume its Web services, you are getting the full power of Excel right within your custom application. Excel Services has two front ends: the Web services and also the Excel Web Access, which is really the Web part feature in Windows SharePoint Services. The back end is the Excel Calculation Services on the Application Server where any user-defined functions can be deployed. More information about Excel Services is available further on in the chapter.

Presentation Layer for Services in the 2007 Office System Client

Whether you want to use 2007 Office system Web services, or you want to consume custom or third-party services, the 2007 Office release can become the host application for bringing in the data from all your services. Between the flexible development tools and the new user interface it offers, developers can customize the look and feel of the application-use services in ways that appear natural, seamless, and integrated.

There is a whole chapter in this book on customizing the UI (see Chapter 2, “Creating a Smart Client for Your Office Business Application by Using VSTO”), so we will not go into detail on this. Keep in mind that customizing the UI means both client and server, and that the server can also be quite richly customized. This is true especially with technologies such as AJAX (Asynchronous Javascript And XML) and Microsoft Silverlight, which can be applied to SharePoint Products and Technologies, and with tools such as Microsoft Office SharePoint Designer for the design of master pages. Silverlight is a cross-browser, cross-platform plug-in for delivering next-generation media experiences and rich interactive applications for the Web. It has a flexible programming model that supports JavaScript, Microsoft Visual C#, Microsoft Visual Basic, and other languages.

Job Candidate Recruiting Interview Feedback Solution

In this human resources solution, Christoph is a recruiter whose main job function is to fill open positions in the company by searching for candidates in the software industry, recruiting them, interviewing them, negotiating with them, and then finally either offering them a position or giving them the bad news. This solution focuses on gathering feedback during an interview but also incorporates many other aspects of the recruiter’s job.

Scenario Overview

The recruiter, Christoph, has opened the Interview Feedback document template that the Wide World Importers human resources department provides for its recruiters to take notes in when interviewing job candidates. As shown in Figure 4-3, the template has a header with information such as the recruiter name, candidate name, interview date, hiring manager, and a link to the résumé stored in a SharePoint list in the Recruiting site.

The Office Fluent Ribbon is customized so that the Home tab has a button on it in the Recruiting Tools group to show or hide the task pane, depending on whether the task pane is toggled to be visible or not. The custom task pane is actually a Document Actions Pane, which means that it is a document-level task pane and shows up only for this template and not for all instances of a Microsoft Office Word document, and it allows Christoph to find everything he needs in the context of the application, even though the information he needs may exist in SharePoint or other applications. The first tab in the multi-tab control allows Christoph to search for Interview Questions in the repository of best-practice interview questions stored in the Recruiting SharePoint site. This makes use of the SharePoint Lists Web service, which allows users to retrieve items in custom applications. When Christoph selects one he likes, he can insert it into the document with the click of a button, without any copying and pasting.

Figure 4-3. The Interview Feedback Document solution contains the Word Add-In, which consists of a customized Office Fluent Ribbon and task pane. The Home tab contains a button that toggles the task pane, and the latter is the heart of the solution

Customized Ribbon and task pane in solution

The second tab allows Christoph to negotiate the salary with the candidate by entering in information such as the candidate’s job category, title, location, and education. Clicking Calculate makes use of the Excel Services Web service to use a calculation model stored in a spreadsheet uploaded in a document library in the Recruiting site. This returns a salary range that Christoph can refer to when negotiating with the candidate. The third tab allows Christoph to search for candidates and to see the list of candidates in the LOB system that is being surfaced through SharePoint Server. As a result, Christoph is able to do all of his research efficiently right within Word while interviewing the candidate.

A Possible End-To-End Solution

This solution starts in the Word document where it focuses on the actual interviewing process. An interesting way to have this scenario extend into an end-to-end scenario would be to have it start off when the recruiter schedules a meeting request in Microsoft Office Outlook. Since not all meetings are interviews, the Office Fluent Ribbon could be customized to include an option in which the recruiter could select a check box if the meeting request is an interview. When it is time for the meeting, another button launches Word and opens the Interview Feedback Document, where the recruiter can see the custom Office Fluent Ribbon and task pane. This document can be hooked up to SharePoint Server as a Content Type and therefore be tied to a workflow. When it is time for the interview, the recruiter can seamlessly enter into the document from the meeting request and automatically save the document to the appropriate SharePoint document library, which kicks off a workflow immediately routing the document to the next interviewer or to the hiring manager, depending on the process of the organization and the step that the interviewee is in.

Retrieving Best-Practice Interview Questions

Windows SharePoint Services 3.0 provides Web services so that you can manipulate the items in your lists by doing things such as retrieving and deleting items or attachments. In this scenario, we are using the Lists Web service to retrieve interview questions stored in a Windows SharePoint Services list when a user types a keyword into a search box. Figure 4-4 shows the experience presented to the user.

Figure 4-4. When the recruiter searches for best-practice interview questions in the custom task pane, the code uses the SharePoint Lists Web service to retrieve list items from a SharePoint list, which acts as a repository containing best-practice interview questions. The user can then insert these questions into the document

Retrieving questions from a SharePoint list

You can see that the recruiter can then also select an interview question and insert it into the document, a functionality that we’ll go over later.

Using the SharePoint List Web Service to Retrieve List Items

To hook up the Windows SharePoint Services list Web service to the custom task pane in Word, we need to add the Web reference to our add-in project. To do this, in Solution Explorer, you right-click References, click Add Web Reference, type http://<servername>/_vti_bin/lists.asmx, and click Go. At this point, you will see all of the available Web methods in this service, such as AddAttachment, AddList, and GetListItemChanges. You can change the Web Reference name to whatever you like, and then click Add Reference.

Now we are ready to start instantiating the service object and calling the methods. Behind the Search button, we need to declare and initialize a variable for the Lists Web service and then authenticate the current user. We do this by passing the default credentials from the system credential cache to the Web service. We then set the URL property of the service for the path to the subsite that we are targeting. Here you can use either the GUID or the URL.

ListService.Lists listService = new ListService.Lists();
listService.Credentials = System.Net.CredentialCache.DefaultCredentials;

listService.Url =
“http://moss/SiteDirectory/Human%20Resources/Recruiting/_vti_bin/lists.asmx”;

We need to formulate a CAML query in order to filter our results, which we will pass in as a parameter of type XML string into the GetListItems method.

string qry = 
“<Query><Where><Contains><FieldRef Name=‘Title’/><Value Type=‘Text’>” + 
                txtKeyword.Text + “</Value></Contains></Where></Query>”;
string flds = “<ViewFields><FieldRef Name=‘Title’><FieldRef Name=‘Body’>” +
                “</FieldRef></FieldRef></ViewFields>”; 

XmlDocument doc1 = new XmlDocument();
doc1.LoadXml(qry);

XmlDocument doc2 = new XmlDocument();
doc2.LoadXml(flds);

From the GetListItems method, we will receive an XML string with all of our information, which we will then need to parse. An interesting thing is when you retrieve the list of titles from SharePoint lists, they are all preceded with the string ows_ so you need to strip that out.

try
{
    // Bind to the gridview.                            
    System.Xml.XmlNode oNode = listService.GetListItems(listName.ToString(),           
        null, doc1.DocumentElement, doc2.DocumentElement, null, null, null);
    XmlNodeReader rd = new XmlNodeReader(oNode);
    DataSet ds = new DataSet();
    ds.ReadXml(rd);
    rd.Close();
    ResultsGridView.DataSource = ds.Tables[1];

    // Add only the first column and remove the “ows_” that is at the     
    // beginning of the string.
    ResultsGridView.Columns.Clear();
    ResultsGridView.Columns.Add(“ows_Title”, “Interview Questions”);
    ResultsGridView.Columns[“ows_Title”].DataPropertyName = “ows_Title”;
                  
ResultsGridView.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells);                
}
catch (SoapException ex)
{
    MessageBox.Show(“Message:\n” + ex.Message + “\nDetail:\n” +    
       ex.Detail.InnerText + “\nStackTrace:\n” + ex.StackTrace);
}                        
}

Inserting an Interview Question

This part is simple and just deals with working with the Word object model. The trick is to Activate the document. Because the gridView always has at least one item selected, we don’t need to verify that there is an item selected in the control. This code only inserts at the end of the document for simplicity.

private void btnInsert_Click(object sender, EventArgs e)
{            
         string txtQuestion = ResultsGridView.CurrentCell.Value.ToString();                
         Globals.ThisDocument.Activate();
    Globals.ThisDocument.Paragraphs[Globals.ThisDocument.Paragraphs.Count].Range.Text 
             = txtQuestion;                
}

Verifying a Candidate’s Salary Using the Recruiting Salary Mode

As we briefly described earlier, Office SharePoint Server has a new feature called Excel Services, which allows developers to use the Excel calculation engine as a service. In this scenario, as shown in Figure 4-5, the recruiter is able to get a quick salary estimate by entering the candidate’s information in the Salary tab in the Word task pane. A general salary range is then calculated and retrieved based on the company’s salary model.

Overview of Excel Services

Excel Services is a Office SharePoint Server Enterprise edition offering that publishes Excel Workbooks to the server and exposes Web service interfaces that enable developers to access the Excel Calculation engine so that they can create custom applications.

Excel Services is built on the SharePoint Products and Technologies platform. As shown in Figure 4-6, at a high level, it is made up of three core components: Excel Web Access (EWA), Excel Web Services (EWS), and Excel Calculation Services (ECS).

Figure 4-5. The Salary tab allows the recruiter to bring up a quick salary range as specified by the company’s salary model. This is calculated server-side using Excel Services Web Services after the recruiter enters in the interviewee’s pertinent information

Salary tab in the task pane

Figure 4-6. High-level architecture of Excel Services

High-level architecture of Excel Services

On the front end, based on the Windows SharePoint Services user interface, we have the EWA, which is a Web part that enables a thin browsing experience. This is where users can interact with Excel spreadsheets that have been published to the server, sort and filter tables, and drill up and drill down on pivotTables without requiring end users to have Excel on their computers. In fact, there is zero footprint here because this is just HTML and JavaScript, and the end user doesn’t need to install any ActiveX controls in order to use the EWA.

Also on the front end is the EWS, which is the programmatic interface to server calculations. It is a Web service hosted in SharePoint. Developers can build custom applications—for example, those reliant on calculation-intensive financial models—by consuming these Web services with methods that perform actions like setting cells, calculating ranges in the workbook, and refreshing the workbook.

On the back-end or application server, we have the ECS, which loads the published spreadsheet, calculates it, refreshes the external data, and maintains the session state for interactivity. It is the engine of Excel Services. The ECS also maintains the caching of the file itself, the results within the file, and the location of the file. All the cache settings are controllable by the administrator. If any user-defined functions are used, these are also deployed to the application server.

Performing Calculations Server-Side in Word Using the Excel Calculation Engine

In this Recruiting Scenario, as shown in Figure 4-7, the simple business logic of the salary calculator is stored in an Excel spreadsheet, which is uploaded to a SharePoint document library.

Each “field” is a Named Range in the spreadsheet. This is important because we need to know the cell range to perform the calculation via the Web services. If we pass in a literal range and at some point we change this spreadsheet around, the formulas will break. On the other hand, if we use Named Ranges, the formulas will remain tied to our new ranges.

So the spreadsheet is uploaded in a document library in SharePoint Server, and the services in Office SharePoint Server, specifically Excel Services, allow us to set the fields in the salary calculator, which causes the Min, Mid, and Max Salary values to calculate, which we can then access using another Excel Service method.

Let’s start to build this. First we need to add the Web reference to the project. In Solution Explorer, right-click References, click Add Web Reference, enter http://<servername>/_vti_bin/ExcelService.asmx, and then click Go. At this point, you will see all of the available Web methods in this service, such as OpenWorkbook, SetRange, and CalculateWorkbook. You can change the Web Reference name to whatever you like, and then click Add Reference.

Behind the Calculate Salary Range button, we first initialize the Web service, set variables, and pass in the default credentials in the system credential cache.

Figure 4-7. The salary model, which calculates server-side, is stored in a workbook in a SharePoint document library. The custom task pane in the Word Add-In accesses the named ranges in the workbook, using the Excel Services Web services to write the text from the selected fields in the task pane to the cells in the workbook and returning the calculated results from the named ranges in the workbook to display in the task pane

Salary calculator stored in workbook

ExcelService es = new ExcelService();
Status[] outStatus;
string sheetName = “Salary”;
string targetWorkbookPath = “http://moss/HR/Recruiting/Recruiting%20Tools/Salary%20
Calculation%20Model.xlsx”;
es.Credentials = System.Net.CredentialCache.DefaultCredentials;

Next we call the OpenWorkbook method from the Excel Services. This loads our Salary Calculation Workbook that was published to SharePoint Server and returns a sessionID that can be used to perform further operations.

try
{
    string sessionId = es.OpenWorkbook(targetWorkbookPath, “en-us”, “en-us”,
                        out outStatus);

Now we link what the user selected in the drop-down box to the values in the cells in the spreadsheet so that we can calculate the salary range. After that, we retrieve the calculated values where Min_Salary, Mid_Salary, and Max_Salary are the Named Ranges containing the formulas.

es.SetCellA1(sessionId, sheetName, “Job_Category”, comboJobCategory.Text);
es.SetCellA1(sessionId, sheetName, “Title”, comboJobTitle.Text);
es.SetCellA1(sessionId, sheetName, “State”, comboState.Text);
es.SetCellA1(sessionId, sheetName, “City”, comboCity.Text);
es.SetCellA1(sessionId, sheetName, “Education”, comboEducation.Text);

object oMin = es.GetCellA1(sessionId, sheetName, “Min_Salary”, true, out outStatus);
object oMid = es.GetCellA1(sessionId, sheetName, “Mid_Salary”, true, out outStatus);
object oMax = es.GetCellA1(sessionId, sheetName, “Max_Salary”, true, out outStatus);

if (oMin != null)
{
    txtMinSalary.Text = Convert.ToString(oMin);
    txtMidSalary.Text = Convert.ToString(oMid);
    txtMaxSalary.Text = Convert.ToString(oMax);
 }
 else
 {
 MessageBox.Show(“Error retrieving salary values”);
 return;
 }
    es.CloseWorkbook(sessionId);
 }

catch (SoapException ex)
{
    MessageBox.Show(“SOAP Exception Message: {0}”, ex.Message);
}

Retrieving Candidate Data from the LOB System from the Custom Task Pane

In this section, we’ll learn how to create and consume custom Web services, which wrap the Business Data Catalog functionality in Office SharePoint Server, that define the LOB entity. In the Candidate tab, the recruiter is able to search for candidates and to see the list of candidates in the LOB system that is being surfaced through SharePoint Server. The recruiter can also query based on a specific candidate ID. This data is pulled in through the use of custom Web services, which access the Business Data Catalog in Office SharePoint Server through which we surface our LOB entities. Since these entities are surfaced through Office SharePoint Server, the recruiter is able to click the link in the Profile column in the task pane and view the metadata about the candidate in SharePoint Server, a feature that comes out of the box with the BDC. Figure 4-8 shows what the user experience looks like for the Candidate tab.

Since this LOB data is surfaced through the Business Data Catalog in Office SharePoint Server, the recruiter is able to take advantage of the SharePoint integration and click the link in the Profile column in the gridView control. This launches the browser showing the candidate’s profile in SharePoint, as displayed in a dynamic Web part page (see Figure 4-9).

Figure 4-8. In the Candidate tab, the recruiter can pull up lists of candidates from the LOB system

Candidate tab lets you retrieve candidate list

Overview of the Business Data Catalog

The Business Data Catalog (BDC) is a new feature in Office SharePoint Server 2007; it surfaces data from any line-of-business application, such as SAP or Siebel, into SharePoint Server without writing any code. As a result, this rich data is available through Web Parts, is indexed as part of the Enterprise Search in your portal, and is available through lists and user profiles. Furthermore, developers can use it to create custom applications.

For information workers who often need to access LOB data, this means that they no longer need to go into separate applications to retrieve data from applications that are not intuitive or often require a lot of training, and as a result are usually limited to specific roles. Office SharePoint Server, with the addition of the BDC, allows all users to view data from traditional LOB systems easily from a familiar interface.

For developers, the great advantage is that you can surface your LOB data into SharePoint Server without writing any code. The BDC makes use of standard Microsoft .NET Framework classes to interface with LOB systems, connecting with OLEDB and ODBC data sources using an ADO.NET interface, or to any other data source using a SOAP interface. In this recruiting example, the BDC is connecting to the AdventureWorks sample database through its ADO.NET interface. Figure 4-10 gives a high-level architecture view of the BDC. For more detailed information on the architecture of the BDC, go to Business Data Catalog: Architecture.

Figure 4-9. The SharePoint Profile page of the candidate that the recruiter sees when he or she clicks the Profile link in the custom task pane of the solution

SharePoint Profile page of the candidate

To make this connection, you need to describe the data source using an XML metadata file, called an application definition file (ADF), and define your business entities. The ADF contains all of the necessary information to connect to the LOB data and return the requested data. Here, you define your business entities, which are like business objects with properties, methods, and actions. Application definitions follow the schemas defined in the BDCMetaData.xsd file located under the Program Files\Microsoft Office Servers\12.0\Bin directory.

You do not need to author the ADF from scratch. If you download the SDK for Office SharePoint Server 2007, there is a tool called the Microsoft Business Data Catalog Definition Editor where you can create the XML using a user interface (UI).

Once you have created the ADF, you go to SharePoint 3.0 Central Administration, click Shared Services Administration on the left-hand navigation panel, and then pick your Shared Service Provider. As shown in Figure 4-10, you have a Business Data Catalog section where you can manage all the entities you have uploaded from the various LOB systems you have connected to SharePoint Server.

Figure 4-10. High-level architecture of the Business Data Catalog

High-level architecture of Business Data Catalog

To upload your ADF, just click Import Application Definition, which validates the file.

Creating the Application Definition File

In our example, we are using the AdventureWorks Microsoft SQL Server database as our LOB system, which therefore interfaces with an ADO.NET connection to the BDC. The first thing we need to do is create an ADF file, which defines all this connection information, plus all the entities we want to define, in this case, JobCandidates. Off of these entities, we’ll have properties, methods, and actions. Figure 4-11 shows what the ADF looked like for the Recruiting solution when loaded into the Business Data Catalog Definition Editor.

Figure 4-11. View of the Interview Feedback Document solution ADF as uploaded in the Business Data Catalog Definition Editor, which ships with the SharePoint Server SDK

Interview Feedback Document solution ADF

We have an instance of the AdventureWorks database called AdventureWorksRecruiting and one Entity called HumanResources.JobCandidate with several methods. These methods execute SQL queries against the LOB system since it is based on a SQL Server database. You won’t see the View Profile action in the BDC Definition Editor, but it gets created by default when you upload the ADF into SharePoint so you don’t need to create an action where you see all the metadata about your entity.

In our Recruiting scenario, we make use of the methods GetJobCandidate and GetJobCandidateSpecificFinder. Let’s dive deeper into how we built the method GetJobCandidate.

<Method Name=”GetJobCandidate”>
    <Properties>
      <Property Name=”RdbCommandType” Type=”System.Data.CommandType, System.Data,   
          Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089”>Text</Property>
      <Property Name=”RdbCommandText” Type=”System.String”>SELECT 
       [JobCandidateID],[EmployeeID],Resume.value(‘declare namespace 
       r=”http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume”;(r:Resume/r:Name/r:Name.First)[1]’, 
      ‘nvarchar(100)’) as [FirstName],Resume.value(‘declare namespace 
      r=”http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume”;(r:Resume/r:Name/r:Name.Last)[1]’, 
      ‘nvarchar(100)’) as [LastName],Resume.value(‘declare namespace 
      r=”http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume”;(r:Resume/r:Employment/r:Emp.JobTitle)[1]’, 
      ‘nvarchar(100)’) as [JobTitle],Resume.value(‘declare namespace 
      r=”http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume”;(r:Resume/r:Skills)[1]’, 
      ‘nvarchar(1000)’) as [Skills],Resume.value(‘declare namespace 
      r=”http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume”;(r:Resume/r:Education/r:Edu.Degree)[1]’, 
      ‘nvarchar(100)’) as [Degree],Resume.value(‘declare namespace 
      r=”http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume”;(r:Resume/r:Education/r:Edu.Major)[1]’, 
      ‘nvarchar(100)’) as [Major],Resume.value(‘declare namespace 
      r=”http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume”;(r:Resume/r:Employment/r:Emp.OrgName)[1]’, 
      ‘nvarchar(100)’) as [MostRecentEmployer],Resume.value(‘declare namespace 
      r=”http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume”;(r:Resume/r:Employment/r:Emp.StartDate)[1]’, 
      ‘nvarchar(10)’) as [StartDate],Resume.value(‘declare namespace 
      r=”http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume”;(r:Resume/r:Employment/r:Emp.EndDate)[1]’, 
      ‘nvarchar(10)’) as [EndDate],Resume.value(‘declare namespace 
      r=”http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume”;(r:Resume/r:Employment/r:Emp.Responsibility)[1]’, 
      ‘nvarchar(1500)’) as [Responsibility],[ModifiedDate]FROM [AdventureWorks].[HumanResources].[JobCandidate]</Property>
    </Properties>
    <Parameters>
      <Parameter Direction=”Return” Name=”HumanResources.[JobCandidate]”>
        <TypeDescriptor TypeName=”System.Data.IDataReader, System.Data, Version=2.0.3600.0,  
            Culture=neutral, PublicKeyToken=b77a5c561934e089” IsCollection=”true” 
            Name=”HumanResources.[JobCandidate]DataReader”>
          <TypeDescriptors>
            <TypeDescriptor TypeName=”System.Data.IDataRecord, System.Data, 
                Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089”  
                Name=”HumanResources.[JobCandidate]DataRecord”>
              <TypeDescriptors>
                <TypeDescriptor TypeName=”System.Int32” IdentifierName=”[JobCandidateID]” 
                  Name=”JobCandidateID” />
                <TypeDescriptor TypeName=”System.Int32” Name=”EmployeeID” />
                <TypeDescriptor TypeName=”System.String” Name=”FirstName” />
                <TypeDescriptor TypeName=”System.String” Name=”LastName” />
                <TypeDescriptor TypeName=”System.String” Name=”JobTitle” />
                <TypeDescriptor TypeName=”System.String” Name=”Skills” />
                <TypeDescriptor TypeName=”System.String” Name=”Degree” />
                <TypeDescriptor TypeName=”System.String” Name=”Major” />
                <TypeDescriptor TypeName=”System.String” Name=”MostRecentEmployer” />
                <TypeDescriptor TypeName=”System.String” Name=”StartDate” />
                <TypeDescriptor TypeName=”System.String” Name=”EndDate” />
                <TypeDescriptor TypeName=”System.String” Name=”Responsibility” />
                <TypeDescriptor TypeName=”System.String” Name=”ModifiedDate” />
              </TypeDescriptors>
            </TypeDescriptor>
          </TypeDescriptors>
        </TypeDescriptor>
      </Parameter>
    </Parameters>
    <MethodInstances>
      <MethodInstance Type=”Finder” ReturnParameterName=”HumanResources.[JobCandidate]”            
           ReturnTypeDescriptorName=”HumanResources.[JobCandidate]DataReader” 
           ReturnTypeDescriptorLevel=”0” Name=”HumanResources.[JobCandidate]Finder” />
    </MethodInstances>
</Method>

You can see that we’ve specified that the SQL query return several values of data for the candidate from the table, such as JobCandidateID, EmployeeID, and tagged sections of the Resume field such as Skills. For the Resume field, the values are of type XML containing custom XML, which will display in the SharePoint profile page as one long XML string if we return the data as is. As a result, we need to create a query string, which navigates to the location in the XML string and parses out the data.

Next we define our parameters. Since this is just a Finder Method method, which does not take in a query parameter, we will only have parameters of the direction “return” defined and not of the direction “in,” as we would with the SpecificFinder method. Off of our return parameter, we create a Root TypeDescriptor, an object of the type IDataReader, which will basically read data from the returned recordset. From the Root TypeDescriptor you create a typeDescriptor, which is a Data Record, which holds the columns for all the parameters that you want to return. This is where we define all our out parameters as TypeDescriptors, JobCandidateID of type System.Int32, for example. Finally, we need to have at least one instance of our method to have a valid ADF.

Developing the Custom Web Service

While the BDC does have an object model for extensibility, it does not provide Web services if you would like to access the LOB data from a client application remotely. In the recruiting scenario, a custom Web service was built to access the LOB system through the BDC, access the HumanResources.JobCandidate entity, and then wrap the methods off of that entity.

Let’s take a look at code, specifically the GetJobCandidate method in the JobCandidate Web service. The first thing we need to do is set application information so that we can retrieve the data.

[WebMethod]
public XmlNode GetJobCandidate(string SSPName, string 
LobInstanceName, string EntityName, string MethodName, string SearchTerm, string ActionName)
{
   try
   {
         if (SSPName != null)
           SqlSessionProvider.Instance().SetSharedResourceProviderToUse(SSPName);
  
           NamedLobSystemInstanceDictionary lobInstances = 
                ApplicationRegistry.GetLobSystemInstances();
           LobSystemInstance lobInstance = 
         lobInstances[LobInstanceName];
           Entity entity = 
         lobInstance.GetEntities()[EntityName];
           MethodInstance methodInstance = 
             entity.GetMethodInstances()[MethodName];
           Microsoft.Office.Server.ApplicationRegistry.MetadataModel.Action action =
                 entity.GetActions()[ActionName];

We set up the filter for searching and then pass that filters collection and our lobInstance into the FindFiltered method of the entity object to return the data we want.

       FilterCollection filters = 
                 entity.GetFinderFilters();
          DbEntityInstanceEnumerator records = 
                 (DbEntityInstanceEnumerator)entity.FindFiltered(filters, lobInstance);

We load the results into a data table so that we can include the Action URL from the View Profile action defined in the ADF.

                DataTable entitiesTable = new DataTable();

                while (records.MoveNext())
                {
                    // Load each entity and include the Action URL.
                    DbEntityInstance record = 
                           (DbEntityInstance)records.Current;
                    DataTable entityTable = 
                           record.EntityAsDataTable;
                    entityTable.Columns.Add(“ActionURL”, 
                           typeof(string));
                    try { entityTable.Rows[0][“ActionURL”] = 
                           record.GetActionUrl(action); }
                    catch { entityTable.Rows[0][“ActionURL”] = “”; }
                    entityTable.AcceptChanges();
                    entitiesTable.Merge(entityTable);
                }

We return the results as the expected XML document.

                XmlElement entitiesElement = 
                   document.CreateElement(“bdc”, “Entities”, bdcSpace);
                document.AppendChild(entitiesElement);

                foreach (DataRow entityRow in entitiesTable.Rows)
                {
                    XmlElement entityElement = 
                             document.CreateElement(“bdc”,”Entity”,bdcSpace);
                    entitiesElement.AppendChild(entityElement);

                    foreach (DataColumn entityColumn in 
                      entitiesTable.Columns)
                    {
                        try
                        {
                            XmlAttribute valueAttribute = 
                            document.CreateAttribute(“bdc”, 
                            entityColumn.ColumnName, bdcSpace);
                            valueAttribute.Value = 
                            entityRow[entityColumn.ColumnName].ToString();
                            
                            entityElement.Attributes.Append(valueAttribute);
                        }
                        catch { }
                    }
                }

                return document;
            }
            catch (Exception x)
            {                
                XmlDocument document = new XmlDocument();
                XmlElement errorElement = 
                        document.CreateElement(“bdc”, “Error”, bdcSpace);
                document.AppendChild(errorElement);
                XmlNode messageNode = 
                        document.CreateNode(XmlNodeType.Text, “bdc”, 
                     “Message”, bdcSpace);
                messageNode.Value = x.Message;
                errorElement.AppendChild(messageNode);
                return null;
            }
        }

The GetJobCandidateSpecificFinder method varies in that it calls the method FindSpecific off the Entity object rather than FindFiltered and passes in an identifier that is the JobCandidateID.

DbEntityInstance record = (DbEntityInstance)entity.FindSpecific(Identifier,lobInstance);

Consuming the Custom Web Service in the Client Application

Once the Web service is deployed, you can add it as a Web reference to your Word add-in project, instantiate, and use the methods.

private void btnSearchCandidates_Click(object sender, EventArgs e)
 {            
 RecruitingService.JobCandidate jobCandidateService = 
               new RecruitingService.JobCandidate();       
          jobCandidateService.Url = “http://localhost:2223/JobCandidate.asmx”;
          jobCandidateService.Credentials = 
               System.Net.CredentialCache.DefaultCredentials;

Recall the way the Candidate search tab worked. If the text box was empty, then all the candidates were returned. If a candidate ID was entered in the search box, then a query went out to search for that candidate. Technically, in the former case, the Finder method was called, and in the latter case, the SpecificFinder method was called. The difference in the parameters of the two methods is that for GetJobCandidate, which returns all the candidates, you pass in the value of the search text box, and for GetJobCandidateSpecificFinder, which returns only one candidate, you pass in the ID of the candidate.

        XmlNode jobCandidateNode; 
        if (txtCandidateSearch.Text == “”)
        {
            jobCandidateNode = 
jobCandidateService.GetJobCandidate(“SharedServices1”, 
            “AdventureWorksRecruiting”, “HumanResources.JobCandidate”, 
            “HumanResources.[JobCandidate]Finder”, txtCandidateSearch.Text, “View Profile”);
        }
        else
        {
            int jobCandidateID = Convert.ToInt32(txtCandidateSearch.Text);
            jobCandidateNode = jobCandidateService.GetJobCandidateSpecificFinder(“SharedServices1”, 
            “AdventureWorksRecruiting”, “HumanResources.JobCandidate”, 
            “HumanResources.[JobCandidate]SpecificFinder”, jobCandidateID, “View Profile”);
            }

Because these methods return XML strings, we need to parse them. Here, the results are bound to the gridview control in the CTP. The results show the candidate’s ID, last name, and first name.

            XmlNodeReader rd = new XmlNodeReader(jobCandidateNode);
            DataSet ds = new DataSet();
            ds.ReadXml(rd);
            rd.Close();
            gridViewRecruits.DataSource = ds.Tables[0];

            gridViewRecruits.Columns.Clear();
            gridViewRecruits.Columns.Add(“JobCandidateID”, “ID”);
            gridViewRecruits.Columns[“JobCandidateID”].DataPropertyName = 
               “JobCandidateID”;
            gridViewRecruits.Columns.Add(“LastName”, “Last Name”);
            gridViewRecruits.Columns[“LastName”].DataPropertyName = 
               “LastName”;
            gridViewRecruits.Columns.Add(“FirstName”, “First Name”);
            gridViewRecruits.Columns[“FirstName”].DataPropertyName = 
               “FirstName”;

           gridViewRecruits.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells);
        }

Summary

This Recruiting scenario shows how the services offered by the 2007 Office System platform enable developers to build SOA-rich OBAs. A custom task pane, built using Visual Studio Tools for Office in Visual Studio 2008, hosted our solution, enabling the recruiter to live in the Word application while accessing different systems and performing multiple tasks. From a development perspective, we were able to pull data from SharePoint lists using the lists Web service so that the end user could access best practices from the enterprise portal. We used Excel Services Web services to calculate a candidate’s salary range based on a model that was created in a spreadsheet, published, and calculated on the server. The recruiter was also able to retrieve specific candidates or a list of candidates from the LOB system through custom Web services that accessed the Business Data Catalog, which exposes LOB entities in SharePoint. The BDC allows you to surface any LOB system into Office SharePoint Server through lists, Web Parts, Enterprise Search, and custom applications. Using the richness of the Office Web services APIs coupled with the ability for rapid development enabled by tools such as Visual Studio Tools for Office in Visual Studio 2008, the 2007 Office system allows developers to build enterprise-ready solutions that are built around information worker processes and bring value to IT investments.

Chapter 4: Integrating Web Services into Your Office Business Applications

The Need for S+S in Today's Enterprise Solutions

S+S and the 2007 Microsoft Office System

Services Available in the 2007 Office System

Presentation Layer for Services in the 2007 Office System Client

Job Candidate Recruiting Interview Feedback Solution

Scenario Overview

A Possible End-To-End Solution

Retrieving Best-Practice Interview Questions

Inserting an Interview Question

Verifying a Candidate’s Salary Using the Recruiting Salary Mode

Retrieving Candidate Data from the LOB System from the Custom Task Pane

Summary

Further Reading and Resources

Office Business Applications

General Office Development

Office SharePoint Server

Windows SharePoint Services

Visual Studio Tools for Office

Blogs

Additional resources