Chapter 11: Advanced Data Access: External Data and More

This article is an excerpt from Professional Microsoft Office SharePoint Designer 2007 by Woodrow W. Windischman, Brian Phillips, and Asif Rehmani from Wiley Publishing (ISBN 978-0-470-28761-3, Wiley Publishing, Inc., 2009, 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.

Now that you know how easy it is to modify SharePoint list and library views, and how to convert them to the much more flexible Data View Web Part, you’re ready to move ahead. This chapter builds on your Figure experience with the Data View and Data Source Library to reach beyond Windows SharePoint Services and build truly heterogeneous applications and mashups. You will learn about:

• Types of data sources.

• Displaying external data in Windows SharePoint Services.

• Using SharePoint data in multiple sites.

• Displaying images in XSL in Data Views.

• Exporting Data Views.

Contents

• The Data Source Library in Detail

• Working with Hierarchical Data

• More about the Business Data Catalog

• Data Views on the Move

• Summary

• Additional Resources

The Data Source Library in Detail

In the previous chapter, you saw how SharePoint lists and libraries are reflected in the Data Source Library task pane. You probably noticed several other sections. Microsoft Office SharePoint Designer 2007 enables you to connect to and manage many different types of information.

All of the formatting and display capabilities of Data Views described in chapter 10 can be used with any data source defined in the Data Source Library, including conditional formatting, Web Part connections, and custom XSL.

The Data Source Types

The Data Source Library task pane is divided into a number of sections that group similar types of data, helping you keep track of where the information displayed in your site is coming from. The following table describes those sections:

Section	Description
SharePoint Lists and Libraries	Lists and libraries are intrinsic data sources in Windows SharePoint Services. Whenever you create a list or library—whether through the web interface or SharePoint Designer—it automatically becomes available in the appropriate section.
Database Connections	Database connections enable you to define a source to connect to an arbitrary database on your network. SharePoint Designer supports creating data sources for SQL Server and Oracle, as well as virtually any database for which an OLE DB or ODBC connection is available, such as MySQL
XML Files	All data sources are converted to XML before a Data View Web Part can render them. It’s no surprise, then, that you can provide XML directly to the part as a file. SharePoint Designer automatically detects any .xml files that may be stored in your site and lists them in the XML Files section. In addition, you can define connections to XML files that reside in other locations on your network.
Server-Side Scripts	Server-side scripts are web pages that return (usually dynamically generated) XML data in response to a request. This differs from a web service in that the required form of the request, and the resultant XML, do not necessarily follow published standards for interprocess communication.
XML Web Services	XML Web Services typically follow a standardized set of rules—usually Simple Object Access Protocol (SOAP). This allows a client process to learn about the functions available, query the parameter formats required, and learn result formats provided by the service.
Business Data Catalog	If you open a Microsoft Office SharePoint Server 2007 Enterprise Edition site with SharePoint Designer, you will also see a Business Data Catalog section in the Data Source Library task pane. The Business Data Catalog enables the SharePoint administrator to define connections—not only to databases, but to enterprise applications such as SAP—and make their information available for use in various locations throughout SharePoint.
Linked Sources	Linked sources are made up of combinations of other sources in the Data Source Library.

Adding and Modifying Data Sources

Within each section of the Data Source Library, you will see a link to enable you to add a new data source of that type. After filling in the definition forms (except for lists and libraries), an XML file defining the data source will be saved in the /_catalogs/fpdatasources library.

XML definition files are not grouped within the fpdatasources library folder to match the grouping in the Data Source Library task pane. They are all simply stored at the root level.

Each type of data source has slightly different configuration requirements and therefore different forms for filling in the details. Nevertheless, there is certain information that is similar for any data source, and that information is entered on the General tab, as shown in Figure 11-1.

Figure 11-1

To enter or edit any of this information, switch to the General tab (when you create a new data source, the Source tab opens by default). You are not required to enter any information on the General tab; however, if you do not enter a friendly name, an arbitrary generic name such as NewDataSource1 is created when you save the data source for the first time.

Although the forms for adding and modifying a data source are the same, be aware that changes made to a data source after Data Views based on it have been created do not flow through to the existing Web Part. In essence, once created, each Data View retains its own independent instance of the data source definition.

Each data source in the task pane has a menu that provides you with several options. Figure 11-2 shows the menu for the Announcements list.

Figure 11-2

SharePoint Lists and Libraries

SharePoint lists and libraries are automatically added to the Data Source Library task pane when they are created. You do not need to manually create entries for them. In addition, unlike most data source definitions, these automatic entries have direct links to the underlying list or library, and therefore are not normally listed in the fpdatasources folder. You have the option to create new lists or libraries from the appropriate sections. Remember, however, that SharePoint Designer does not offer the capability to add or remove list or library fields directly.

Because the data source is tied directly to the list or library, unlike most data sources, lists and libraries do not show a query editor by default. The Data Source Library does give you a way to limit the available fields or rows when creating Data View Web Parts, though, and that is through the Copy and Modify option in the item's menu. This overrides the normal direct connection to the list, and opens the query editor shown in Figure 11-3.

Figure 11-3

The query editor is normally readily available when defining other types of data sources.

From here you can simplify the list's data display, prefilter results down to a subset, or create XPath calculated fields for direct selection when creating Data View Web Parts. When you click OK, SharePoint Designer saves a copy, and it appears as a new source in the list or library section of the Data Source Library task pane. In addition, unlike its parent list, this entry has an .xml definition file in the fpdatasources library, just like any other data source.

An HTMLEncoded version of the CAML query you define with the query editor is embedded in the XML definition file.

Database Connections

Creating a database connection is a two-stage process. First, clicking the “Connect to a database...” link produces a very simple dialog containing the General tab described previously and a Source tab with a single button (Configure Database Connection). Clicking this launches the second stage—the Database Connection Wizard.

There are several steps to creating a database connection. The first step (see Figure 11-4) defines the type and location of the database, and provides any needed access credentials.

Figure 11-4

If you wish to connect to a database other than Microsoft SQL Server (for example, Oracle or MySQL), you can check the Use custom connection string box and provide an appropriate connection string directly.

When you click Next, connection to the database is verified, and you are presented with a form to define the record set you want to use. You can select a table, view, or stored procedure, or indicate that you wish to enter a SQL query directly. If your SharePoint server administrator has enabled update query support, you may also define the statements required for writing information back to your data set. Figure 11-5 shows the Database and Table (etc.) selection page of the Database Connection Wizard.

Figure 11-5

If you select the option to specify custom queries, when you click Finish, this dialog will be followed by a form for building your queries.

After completing the wizard and saving your database connection, viewing the properties displays the Query options panel in the Source tab, allowing you to easily define or fine-tune subsets of your primary data source query.

XML Files

Creating a new XML reference is very straightforward. You can either import an XML file into your SharePoint site, or if the XML file is stored online, simply create a link to it. An online XML file does not need to be within a SharePoint site, but it does need to be accessible via either http or https.

If the server containing the file does not allow anonymous access, you can provide login information on the Login tab of the data source definition, shown in Figure 11-6.

Figure 11-6

The Login tab is the same for most data sources other than databases, the Business Data Catalog, and SharePoint lists and libraries.

Server-Side Scripts

Most server-side scripts, such as traditional .asp, .php, and Cold Fusion (.cfm), are designed to accept parameters from a form page. If the script can return results in XML format, you can create a connection to that script that provides the input parameters it expects, and returns the XML to Windows SharePoint Services. Figure 11-7 shows the Source tab of a server-side script data source.

Figure 11-7

At a minimum, you need to provide the URL of the script. If the script does not require any parameters, you do not need to enter any other information. If you need (or want) to provide extra information for the script, there is further work to do.

HTTP Method

Web forms can be submitted in two ways: HTTP Get and HTTP Post. The method you choose depends on the server-side script. Some scripts are independent of the method, but most require one or the other.

A script that uses the HTTP Get method is easy to recognize because parameters are passed as part of the URL. Such a URL might look like this:

http://www.example.com/inventory/products.asp?CompanyID=25&InStock=true

The parameters in this case are CompanyID and InStock.

When accessing a script that requires the HTTP Post method, the parameters are passed within the HTTP request header itself and are not normally visible to a user. To use this method, you need to find out from the application designer what parameters are expected by the script.

A few, very rare scripts depend on certain parameters to be submitted with each method. SharePoint Designer cannot configure connections for scripts that require parameters from both methods simultaneously.

Data Command

Most of the time, you only want to read and display (that is, Select) information from your external data source, so you simply leave the default Select option chosen. In addition to querying, however, some scripts allow you to insert, update, or delete the underlying data. To implement those functions in your Data View or forms, select the appropriate command from the Data Command dropdown. Each of the functions has its own set of parameters defined.

Parameters

Once you determine the HTTP method and Data Command, you can add any required parameters to the parameter table. In the parameter dialog, you can specify both the name and a default value for each parameter. In addition, you can designate a parameter as run time by checking the box for The value of this parameter can be set via a Web Part connection. Figure 11-8 shows the dialog to add or modify a parameter.

Figure 11-8

XML Web Services

XML Web Services are like an enhancement of server-side scripts, with a major exception—most server-side scripts are meant to be called from other pages within the same application. Web services, on the other hand, are designed specifically to be called by other applications, so there are some key differences between setting up a server-side script data source and setting up an XML Web Service data source. Figure 11-9 shows the Source configuration screen for an XML Web Service.

Figure 11-9

Service Description Location

As you did for the server-side script, you need to provide a URL to access a web service. For a web service, however, the URL you enter here typically will include the specific parameter ?WSDL, which means Web Services Description Language, or Web Service Definition Language. This parameter instructs the web service to return information about how it is used in a standardized XML form. SharePoint Designer then uses that information to provide the options for the remainder of the configuration.

Data Command

Again, as with the server-side script, you configure Select, Insert, Update, and Delete commands independently. Most of the time, you only configure a Select command. If the web service provides operations for the other commands, and you want your Data View Web Part to implement them, simply configure the remaining sections appropriately for each command.

Port

If a web service supports multiple interfaces, the Port option allows you to specify which interface to use. You typically use the first option provided, unless the web service provider gives specific instruction to the contrary.

Operation

Upon connecting to the WSDL file, SharePoint Designer populates the Operation dropdown with the supported functions of the web service. Select the operation you want to perform to implement the current command.

Parameters

After you select an operation, SharePoint Designer populates the Parameters table. This is much simpler than discovering the parameters for a server-side script because you do not have to guess or dig through possibly unobtainable documentation to determine what the functions expect.

Parameters to a web service may be optional or required. Required parameters are indicated by an asterisk (*). As with server-side script parameters, you may predefine a default value for a parameter and also make it configurable at run time (through a Web Part connection, for instance). Unlike server-side script parameters, these values can be complex entities that contain many elements, such as arrays, and have data types that can be enforced. For complex data types, each element can have a default value and/or runtime source specified.

You cannot add parameters to or remove parameters from a web service data source, because they are predefined in the WSDL. For optional parameters, just leave the configuration elements blank if you are not going to use them.

Business Data Catalog

Unlike other intrinsic SharePoint components, Business Data Catalog objects are not automatically included in the Data Source Library. When you open a SharePoint site, you need to add them individually to the available data sources. Figure 11-10 shows the Business Data Catalog data source configuration screen.

Figure 11-10

Each Business Data Catalog data source represents one entity type from one application. Business Data Catalog applications and entities are defined farm - wide, and invoked where needed. Most parameters of a Business Data Catalog list are set centrally, but you can apply basic filters to your data source to avoid returning the potentially millions of records in an enterprise application with a single query. In addition, when creating a Data View, you still have the option of selecting specific data columns and applying additional filters.

Connecting to Other Libraries

SharePoint Designer also allows you to connect to Data Source Libraries in other SharePoint sites and to leverage some or all of the data sources defined therein. The exact sources available through the connection will depend on the relationship (if any) between the current site and the site of the library to which you are connecting.

To connect to another site, open the Data Source Library task bar, and click the Connect To Another Library link at the bottom of that pane. This opens the Manage Library dialog, which lists the external libraries that are currently connected (see Figure 11-11).

Figure 11-11

Click the Add button to enter a descriptive name and the URL of the library you want to add. Click the Browse button to navigate to a particular SharePoint site, and click the Open button in the browse dialog to insert the selected site into the Location field. Alternatively, you can manually enter a URL in the field. In either case, once you have selected the site, click OK to establish the connection.

If the site you have selected is not available, or does not have a sharable Data Source Library, you will get an error message, and the library will not be added to the list.

Sites in the Same Site Collection

Connect to another site in the same site collection, and you have access to all of the data sources defined within the site, including the site's lists and libraries, and any external data sources. If you are using Office SharePoint Server, you also have access to the Business Data Catalog sources defined in the target site. Linked data sources are available, but may not function as expected if any of the constituent data sources require permissions not possessed by users of the connecting site.

Sites in a Different Site Collection

When you connect to sites in a different site collection—whether on the same server or on a different SharePoint server—you only get access to the user-defined data sources and XML files. You cannot access the target site’s lists and libraries. If the target site is a SharePoint Server site, you do not have direct access to any Business Data Catalog entries.

All linked data sources from the selected Data Source Library will be listed. Linked data sources made up of elements that can cross site collections will function normally. If a linked data source is composed with any elements that are not available as described above, however, that data source will not function.

Linking Data Sources

In the previous chapter, you saw how you could use Windows SharePoint Services and SharePoint Designer to relate Web Parts through Web Part connections. Another way to blend the display of heterogeneous data is through linked data sources. A linked data source enables you to define a relationship between two or more existing data sources and present them in a single Data View.

To select elements for a linked data source, click the Create A New Linked Source link, or select Edit from the data source context menu. The Data Source Properties dialog shown in Figure 11-12 appears. It is initially unpopulated.

Figure 11-12

Click the Configure Linked Source button to launch the Link Data Source Wizard. The first page, shown in Figure 11-13, lists all of the data sources in each of the Data Source Libraries you currently have connected.

Figure 11-13

Select and order the data sources you want, and then click Next. Select the type of link you want to create: merge or join.

A merged data source (see the left side of the Link Data Source Wizard example in Figure 11-14) is useful if the information in the component sources can be considered components in a single list. For example, you may want to roll up list information from multiple sites in your collection for a single view on the home page.

Figure 11-14

A joined data source (see the right side of the wizard example in Figure 11-14) is most useful when there is a hierarchical or one-to-many relationship between the elements in the component data sources. A SharePoint list of client contacts, joined to the Orders table of a financial system (connected through the Business Data Catalog), for instance, might make a very useful linked data source.

Working with Hierarchical Data

Until now, the discussion of data display has been about working with data in the relatively simple tabular form. Many kinds of information, however, do not naturally lend themselves to a simple tabular organization. The joined mode linked data source described previously is just one of many possible sources of hierarchical, or nested, information.

XML, with its capability to have nodes within nodes within nodes, is designed to easily represent such multidimensional data. Because the Data View Web Part is at its core an XML display tool, its usefulness would be quite limited if it were not capable of handling hierarchical data as well as tabular information. Fortunately, it handles it quite easily.

For this example, you will connect Windows SharePoint Services to Microsoft's Zune Social Web site. Zune Social is an online community closely associated with the Microsoft Zune digital media player. It allows Zune users to register and share information about their entertainment preferences with other users.

This sharing is done through what is known as a Zune Card. Zune Cards are used throughout the Zune Social site. They can also be shared and displayed on other web sites, usually rendered through an Adobe Flash applet. The information the Zune Card applet uses can be retrieved as hierarchical XML through a server-side script. You will use this as the data source in the following exercise.

The exercise walks you through creating a Data View Web Part that shows the playlists of a Zune Card, along with the album cover art. In it, you create a Web Part connection to allow you to choose the user whose playlist information you want to display. In the process, you will leverage many of the capabilities of the SharePoint Designer Data Views you have learned about in this and the preceding chapter, as well as several methods for selecting and inserting fields for a Data View.

This exercise assumes you have created a Web Part page to contain the Web Parts, and have it open in SharePoint Designer. (A Header, Left Column, Body page layout is suggested.) Save your page after the second and each subsequent module.

Create the Data Source

In this module, you create an external data source, and set up a parameter to be used later for a Web Part connection.

1. In the Data Source Library, open the Server-side scripts section.

2. Click the Connect To Script Or RSS Feed link.

3. Enter the following URL for the script:

   http://zcards.zune.net/zcard/usercardservice.ashx?src=external&zunetag=WoodyWindy

4. Click the Add button to register the existing parameters. (Cancel the Parameter pop-up.)

5. Select the src parameter and click Modify.

6. Uncheck the box for The Value Of This Parameter, and click OK.

   Optional: Change the default value of the zunetag parameter to your own Zune tag.

7. Click on the General tab and enter Zune Card Service into the Name field.

8. Click OK.

9. Select Show Data from the Zune Card Service context menu. The Data Source Details window shown in Figure 11-15 will appear. Notice that there are many nested folders, containing many kinds of data.

   Figure 11-15

   

Insert the Web Part

In this module, you create a Data View Web Part based on the data source created in the first module. Because the primary iterating level of this view is the playlist, that is the first data segment to be inserted. You will set the header of the view to include some of the Zune user’s general information.

1. If the page you want to create this Web Part on is not already open, open a Web Part Page.

2. Highlight the Web Part Zone in which you want to insert the Web Part (typically the Body).

3. Select Data View > Insert Data View.

4. Select Show Data from the Zune Card Service context menu.

5. Scroll down the list of fields, and select the object /zCard/user/manifest/playlists/playlist/label, opening any folders that may be closed. Figure 11-16 shows the expanded data source with the correct element highlighted.

   Figure 11-16

   

6. Click Insert Selected Fields as and select Multiple Item View. A basic list formatted Web Part similar to the one shown in the center of Figure 11-17 should be inserted.

   Figure 11-17

   

7. Select Common Data View Tasks > Change Layout.

8. Select the view described as The Two-Column Repeating Form.

9. Click the General tab, and check the Show View Header check box.

10. Click OK. The View should now look like Figure 11-18 (with callouts added to indicate some of the following steps).

    Figure 11-18

    

11. Click in the blank area above the playlist grid to set the insertion point.

12. From the user node in the data source, select the label and status fields.

13. Click Insert Selected Fields as, and select Item(s).

14. Click to set the text insertion point between the newly inserted fields, and type a colon (:) and a space, to separate the items.

15. In one of the playlist cells, highlight the two constituent cells.

16. Right-click the selected cells, and select Modify > Merge Cells.

17. Switch to Split view.

18. Click on one of the detail items. The Code pane should show a line that reads:

    <b>label:</b><xsl:value-of select="label"/></td>

19. Click in the Code pane, and edit that line so that it reads:

    <b><xsl:value-of select="label"/></b></td>

20. Click back in the Design pane, and the Web Part will now look like Figure 11-19. Notice that the user's Zune tag and status are displayed above the grid, and the word "label" is gone from the playlist cells.

    Figure 11-19

    

Insert and Customize the Subview

In this module, you add the playlist details and customize their display to show a small image of the album cover for each song.

1. Click in the Design pane to place the insertion point at the end of the Top 10 Songs label.

2. Right-click in the Data view, and select Data Source Details. (This may be required any time you restart SharePoint Designer or open the page, to ensure that the correct instance of the data source is displayed.)

3. In the Data Source Details task pane, scroll down to the track folder (/zCard/user/manifest/playlists/playlist/track).

4. Open the track folder (if required), and select the first label field.

5. Drag the label field and drop it when the insertion point is at the end of the Top 10 Songs label. The display should look like Figure 11-20.

   Figure 11-20

   

6. Highlight the text label: in any cell.

7. In the Data Source details pane, drill into the ./album/image folder, and select url.

8. Right-click url and select Insert As Picture. You may be warned that images from untrusted data sources can be dangerous. If so, click OK. You should now see album cover images in your Data View.

9. Right-click an album image, and select Picture Properties from the context menu.

10. In the Picture Properties dialog, check the Alternate Text, and enter the text {album/label}.This will show the title of the album when your user hovers over the image.

11. In the artist folder, drag the label field into the text area, beside the song title.

12. Place the text entry cursor between the title and artist name, and press Enter to put the artist's name on its own line.

13. Save your page, and click the Preview in the Browser icon. Your completed Zune Card Web Part should resemble Figure 11-21.

    Figure 11-21

    

Get Connected

The final module in the exercise shows how you can use the Data View you created to display any Zune Card you want. In the process, it introduces a very simple Form Web Part. (Forms will be treated in detail in the next chapter.)

1. Display the Web Parts task pane.

2. Drag a Form Web Part on the page. (If you're using the suggested Web Part page template, drag it into the LeftColumn Web Part Zone.) Notice that the form has a text box and a Go button.

3. Right-click the form, and select Web Part Connections from the context menu.

4. The default action is Provide Form Values To. That is correct, so click Next.

5. Ensure that Connect To A Web Part On This Page is selected, and click Next.

6. Select your Zune Card Web Part, and select Get Parameters From as the Target Action. Click Next.

7. Figure 11-22 shows the Parameter matching page of the Web Part Connections Wizard. In the Columns in Form Web Part column, select T1 (the default name for the text box) on the zunetag line.

   Figure 11-22

   

8. Click Next.

9. Click the Finish button.

10. Save your page, and view it in the web browser.

11. Enter various values in the Form Web Part, and click Go to see the associated Zune Cards.

Exploring Further

This exercise walked you through creating a powerful data query and display application without writing any code. There is almost no limit to the kinds of things you can do with the Data View. Here are some possible ways to extend this exercise:

• Add a zunetag field to a list on your site, and use a view of that list to feed the Web Part connection.

• Play around with the formatting and the many other fields available.

• Add the Zune user’ s picture to the view.

• Use the URL fields to create hyperlinks into the Zune Social site to see more information about the songs and albums in the playlists.

• Add another Web Part connection to filter the display to a single type of playlist.

• Create a data source based on a public RSS feed, and build your own targeted news page.

More about the Business Data Catalog

As previously mentioned, the Business Data Catalog allows Enterprise data to be surfaced in various places within Microsoft Office SharePoint Server, Enterprise Edition. Earlier in this chapter, you saw how the Business Data Catalog could be surfaced as a data source for Data View Web Parts. There is another kind of Web Part that is designed specifically for displaying information from Business Data Catalog: the Business Data Web Part.

Business Data Web Parts are available through the web interface, and allow your end users to easily display Business Data Catalog elements on their sites. There are limits to the flexibility of these parts, however. Specifically, when a user inserts a Web Part for a particular entity, the Web Part will display all of the fields for that entity. There is no easy way for the end user to select and display a subset of the fields, or select a layout other than the single record or basic table.

SharePoint Designer recognizes and supports Business Data Web Parts. While there are some extra controls specific to the Business Data Catalog, the primary display elements of these parts are treated just like any other Data View Web Part. You can modify fields, create conditional formatting, or apply any other XSL Data View formatting.

In this respect, SharePoint Designer can give you the best of both worlds—your users can select the Business Data Catalog elements they want to display through the web interface, and then you can use SharePoint Designer to customize them to your exact needs.

Data Views on the Move

Although you use SharePoint Designer to create Data View Web Parts, once they are placed on a page they can be exported to files through the web user interface. Exported Data Views retain not only the formatting XSL, but also the connection information required to access the data source. This can be very useful because it allows you to easily re-use a Data View in other environments.

An exported Data View Web Part is stored as an XML file with the extension .webpart. If you import it onto another SharePoint page and that server has access to the data source defined within the Web Part, the Web Part will work perfectly in its new home. The access rules for exported Data Views are similar to those for making connections to other Data Source Libraries in SharePoint Designer:

• Used on another page within a site collection, almost all Data Views function correctly. There is a limit to this: Data Views of a SharePoint list cannot be built from the default list connection in the site that hosts the list. You must build the Data View from a connected library.

• Used on another site collection, list-based Data Views do not work. Business Data Catalog views do not work on sites other than those on the SharePoint Server Enterprise farm containing the Business Data Catalog entities referenced.

Summary

This chapter explored the Data Source Library, and showed how to use the Data View Web Part to produce powerful displays of those data items. It explained:

• How to add data sources to SharePoint Designer.

• XML and Data Views can represent and display complex hierarchies of data.

• External data is just as easy to connect as internal SharePoint lists.

• There are many ways to insert specific fields or other elements into a Data View.

• The Business Data Catalog is surfaced in many different ways.

• Data View Web Parts can be exported for use on other pages, potentially even on other sites and servers.

The next chapter introduces you to the many different kinds of Forms you can work with in SharePoint Designer.

Additional Resources

For more information, see the following resources:

• SharePoint Designer 2007 Developer Reference

• SharePoint Designer Developer Center