Using XML Web Services in SharePoint Designer

I noticed that I had promised better details on a previous post, so I have decided to include step by step instructions on building a solution that queries an XML web service for data and displays that information in SharePoint 2007. I apologize that it has taken me two years to get back to this, and I hope you will still find this information valuable.

In the post, I described how I used SharePoint Designer to query an XML web service to build a course roster system for our professors. This is a very simple solution that allows a faculty member to go to SharePoint and display their courses and view the students enrolled in that course from data in our ERP system.

Starting in SharePoint designer, I open the site where this solution would be placed. The site I’m using has a Pages document library, but any document library should work.

I need two pages: one to display the available courses and another to display the students enrolled in the course. A page can be created by right clicking on the window and choosing new aspx page. Following this process, I create these two pages: CourseRoster.aspx and StudentsInCourse.aspx.

Now, the web service data sources need to be created. I begin by opening Manage Data Sources from the Data View menu. This opens the Data Source Library task pane. I locate the XML Web Services node and expand it. Next, I click on Connect to a web service…

The data source properties window will open and show the Source Tab. To help identify this data source, I begin by going to the General Tab and providing a name that is more descriptive than the default one that will be created.

On the Source tab, I provide the details on how to contact the web service, connect to the web service by clicking on Connect Now and select the appropriate method. (Notice that you do have the ability to use web services to update data as well, but this is beyond the scope of this post.). Any parameters that are needed for the web service will be displayed in the parameters box at the bottom of the dialog.

So that I can customize the parameters sent to the web service, I need to make modifications to the parameters. I click on the parameters that will need to allow to be set dynamically and then click on Modify. In this case, the Login will need to be provided dynamically; However, for simplicity, I will only be using the default value for this data source.

When I click on Modify, the parameter properties dialog will open. I insure the checkbox for The value of this parameter can be set via a Web Part connection is checked and provide a default value to use for the initial query of the data source. When the values are set correctly, I click OK to close the dialog.

If the web service required authentication, I would change properties on the Login tab. Fortunately, the web service that I am using does not require authentication, so I click OK to create the new data source.

This data source will be used to get the list of courses for a specific professor. Since the same web service also has a method to get the students currently enrolled in the course, I can simply make a copy of this web service and modify the settings appropriately.

To do this, I right click on the web service I just created and choose Copy and Modify. Again, I change the name on the general tab to provide a better name for the data source. In my case, this one is called StudentsInCourseWebService. On the source tab, I click reconnect and choose the new method to use. Again, I need to modify the parameters. This web service method expects a parameter named SYN. This is the unique identifier of a course in our organization, so it will need to be provided dynamically.

Now I have both of my web service data sources ready, it’s time to use the data! First, I open the CourseRoster.aspx page in SharePoint designer. A blank ASP.NET page is displayed. With the page active, I open the data source library task pane and locate the data source that queries the courses for the current professor. I right click on it and choose Show Data.

This queries the web service using the default value and returns the results in the Data Source Details task pane. Now, I CTRL-Click on all the fields that will need to be displayed on the data source and then click on Insert Selected Fields as… > Multiple Item View

The CourseRoster.aspx page now displays a list of available courses using the default value provided to the web service.

I want the professor to be able to click on the course number to open the course and view currently enrolled students. To do this, I move the mouse cursor over one of the items in the CourseNumber row and look for the task widget (I’m not sure what the actual name of it is, but it’s a little gray arrow).

I click on the widget and the properties of this field are be displayed. I need this to be a hyperlink, so I choose Hyperlink in the Format As dropdown.

A message warning me hyperlinks should only be used in a trusted environment appears. I click Yes to confirm the creation of a hyperlink and the hyperlink properties window is displayed. I will leave the text to display as {CourseNumber}, but I need to change the Address to StudentsInCourse.aspx?SYN={SYN}. I confirm this by clicking on OK.

I now move to the StudentsInCourse.aspx page and make sure it is the active page in SharePoint designer. I reopen the Data Source Library task pane to locate the data source that gets the students in a specific course. Right clicking on it, as before, I choose to Show Data. Again, I choose the columns to display and click on Insert Selected Items As Multiple Item View. There is now a list of students for the default value provided in the data source. This is not the desired result, so I need to specify a source for the parameter. I locate the widget for the data source and click on Parameters.

The SYN parameter is available, I just need to specify the source. This is done be choosing Query String in the parameter source drop down and setting the Query String Variable to SYN.

Now, it’s time for testing. To insure that everything is working, I save everything and go back to the CourseRoster.aspx page and click on the preview button on the toolbar.

This opens Internet Explorer to the CourseRoster.aspx page with a list of courses. I can click on a specific course to view the students enrolled in the course.

Finally, I need to apply the SharePoint master pages to these pages. To do this I go back to SharePoint Designer and click on Format > Master Page > Attach Master Page. I choose to attach the Default Master and match any content regions that need to be setup.

When completed, the page is now wrapped in SharePoint. I do the same thing for the StudentsInCourse.aspx page and the solution is now complete.

Resources: LINQ to XML for SharePoint Lists Web Service

As I have been continuing the development of the end-to-end solution, I have been doing a lot of work with the SharePoint web services -- specifically the lists.asmx web service used for working with lists in SharePoint.

Anyone who has dealt with this web service knows that SharePoint is happy to provide you with a lot of XML to parse. In an effort to make these easier, I began exploring the use of LINQ to XML since I'm not the biggest fan of writing my own XPath queries. In this quest, I came across two articles that were very useful and I hope you will find these useful as well.

Using LINQ to XML in combination with the Lists.asmx
This article describes how to use LINQ to XML in conjunction with the  GetListCollection() method of the lists.asmx web service. I made use of this in my client application to populate a combo-box.

SharePoint to Linq
Using this site, I was able to learn how to use LINQ to XML to build queries and anonymous types for data-binding list data to my client application.

I'm in the process of refactoring some of the end-to-end solution I've been working on, so that has slowed me down a bit in getting the next posts up. I hope to get the refactoring completed soon so I can share the steps I've taken to create an end-to-end solution in SharePoint 2007.

Querying a Web Service with InfoPath 2007

One of the things that I have found very useful with InfoPath 2007 is the ability to query web services for data. In one of my recent projects I was asked to have an employee enter their ID and return some specific information about them to be used in an InfoPath form. The group involved wanted to make sure that the name of the employee was the official name found in the HR database.

So, my first thought was, great, I'm going to have to create a custom web part or field type for SharePoint -- then I discovered that InfoPath has the capability of querying web services. This changed my game-plan significantly. So, without further explanation of the project, here are the steps taken to query a web service using InfoPath.

Step One - Create The InfoPath Form

I begin my process by laying out the required data-source elements. The group wants to return the official name of the employee based on their employee ID. So, I create my data-source with the following nodes:

I now add my fields to the InfoPath form using the Layout task-pane. For the purposes of this demonstration, I have removed the additional data elements.

Because I don’t want the employee changing the first name and last name fields, I am going to set these both to read-only. This is accomplished by right clicking on the field and choosing Text Box Properties. One the display tab, you’ll see the read-only checkbox.

Now that these items are ready only, I’m ready to move on to step two.

Step 2 – Create the data connections

From the menu, I choose Tools -> Data Connections.

I currently don’t have any data connections defined, so I will need to add my data connection. In this situation, I already have a web-service that connects to my HR database. I just need to connect InfoPath to the web service, so I click on Add.

In the dialog that appears, I choose to create a new connection to receive data.

In the list of options presented, I select from a web service. I am prompted to enter the URI for my web service or to use UDDI to locate a web service. After you have selected the web service, you will be presented with all of the available methods. For my example, I will be choosing the method to return an employee by id as XML data.

The next two screens present the available parameters and ask me to fill them out. The first screen is used to gather the schema of the returned data. The second is to create a default value. This method expects a single employee id formatted as an integer. In my situation, I set both of these to my employee ID (mainly because it’s the only one I know from memory).

Now I’m asked by InfoPath if I want to store a copy of the data in the template. This will be based upon your situation, so I have no recommendation. Since I’m returning a single record, it almost seems pointless to store the record with this template, so I have chosen not to store any data.

Finally, you’re asked for a name and if you want to automatically retrieve data when the form is opened. Set these values and click Finish. I have now created my data connection to retrieve employee information. You should be able to change your data source on the data source task pain to explore the returned schema.

Step Three – Linking Everything Together with Rules

Now that I have successfully connected to my web service, I need to create a rule that will populate the name based on the employee ID typed into the employee ID field. Begin by right clicking on the Employee ID field and choosing Rules.

The trick to getting this to work is to use the Employee ID field to pass the required parameter to the web service and re-querying the web-service with the new parameter. So, I need to create a new rule that will take care of this functionality for me. From the rules dialog, I click on Add. To help me clarify the rule later, I call this rule Set Employee Name.

I create a new action to set a field’s value. This is where some of the magic will happen. For the field, I click on the Browse Icon and select my web service from the data source drop down. You should see query fields and data fields as nodes in the tree. Open the query fields until you find your parameter. Click on the parameter and click OK to set this value.

For the value, I set this to the current node by selecting the function icon and choosing to insert a field or group. Make sure your main data source is selected and choose the appropriate field. In my case, this is the employee ID field. Once these bindings are done, I click OK.

I now need to add an action to query the web service, so I click on Add Action and choose Query using a data connection in the action drop down. Since I only have one data source, I choose the only available data source and click OK.

Now, the final step is to add an action to set the fields of my main data source to the retrieved values from the web service. I click on Add Action and choose the action to Set a field’s value.

This time, the field will be FirstName from the main data source and the value will be the associated value from the web service. I add one more action to set the last name. Now I’m ready to test it out to make sure it works as expected, so I click OK until I am back to the main form. I now click on preview and test the form to make sure it is able to retrieve the name from the web service and populate my read-only text boxes.

When I enter my employee ID, I find that my name is automatically populated back into the form.

Using Web Services in SharePoint Designer

I recently had a project in which I needed to create a course roster management system for our faculty to view a list of students in their class. We had already created a web service for the management of these courses for an older system, but since we have been moving towards SharePoint, when it was time to redesign this application we found SharePoint to be a strong candidate.

First, I created a new blank team site in SharePoint as my scratch pad. I connected to the site using SharePoint Designer and created two new ASPX pages. One would by my faculty course roster that included all of the courses the member was teachinng for the semester (facultycourses.aspx). The second one would have a list of students for a single course (courseroster.aspx).

Next, I opened up my data sources and added a new XML Web Service data source. I pointed the web service to my roster management course list by typing the URL in the top box. Since I'm only reading data for display, I will only need to configure the Select data command. I then selected the operation that returns a list of courses for a specified faculty member. I have one parameter that the webservice expects, Login. I specified a default value (for testing) and then checked the checkbox for The value of this parameter can be set via a Web Part connection. This allows me to use parameters in a data view to dynamically query the web service. Since this web service is used to both return courses and student rosters, I created a copy of the web service and pointed to the method that returns a list of students based on a specified course.

Next, from the Data Source that returns a list of courses, I right clicked and selected Show Data. This performed a query to the web service and returned the schema of the data. I select the fields I want to display and then click on Insert Selected Fields as > Multiple Item View. This inserts a data view with the selected items. I now have the data ready for some modifications. The first thing I need to do is add a link to the student roster page, so I selected the column for the name of my course. From the Insert Menu, I chose HTML > Hyperlink. When I am prompted for properties, I provided the url StudentRoster.aspx?SYN={SYN}.

Next, I selected the dataview and then clicked on Data View > Parameters. In the dialog I have a parameter named Login. I clicked on the parameter and set the source to a query string. (My eventual goal for the finished application is to have SharePoint pass this information to the data view parameter. I'll let you know when I figure out how to accomplish this). Now I'm ready to move on to the StudentRoster page to get it setup.

For the student roster, I follow the same instructions but set my data source to the data source that returns students in a course. I need to set the parameters for the data view to use a query string value that I set in the course link. This is the value SYN. After I save the page, I can now click on any course from the faculty courses page and display the students for that course.

Finally, to get these pages integrated into SharePoint, on each page I choose Format > Mater Page > Attach Master and use all of the defaults. This will wrap the sharepoint master page around the ASPX file and thus integrate this mini-application into sharepoint.

Here's my faculty page final result: