Objectives ®.com
Hands-On LabBuilding Applications and Services Using Open Data ProtocolLab version: 1.0.0Last updated: DATE \@ "M/d/yyyy" 5/4/2010Contents TOC \h \z \t "Heading 3,2,pp Topic,1,PP Procedure start,3" Overview PAGEREF _Toc255484132 \h 3Starting Materials PAGEREF _Toc255484133 \h 6Exercise 1: Introduction to Open Data Protocol PAGEREF _Toc255484134 \h 6Task 1 – Exploring an OData Feed PAGEREF _Toc255484135 \h 7Exercise 2: Creating an OData Feed over SQL Server PAGEREF _Toc255484136 \h 8Task 1 – Creating the Data Service Web Application Project PAGEREF _Toc255484137 \h 9Task 2 – Creating the Entity Data Model PAGEREF _Toc255484138 \h 11Task 3 – Creating the WCF Data Service PAGEREF _Toc255484139 \h 14Exercise 2 Verification: Exploring the Data Services Feed PAGEREF _Toc255484140 \h 17Exercise 3: Exploring OData Feed from Windows Azure PAGEREF _Toc255484141 \h 19Task 1 – Examining the OGDI Site PAGEREF _Toc255484142 \h 20Task 2 – Creating a Service Reference in a Client Application PAGEREF _Toc255484143 \h 21Exercise 4: Building a Silverlight Client with OData Feeds: PAGEREF _Toc255484144 \h 24Task 1 – Creating Silverlight Application PAGEREF _Toc255484145 \h 24Task 2 – Adding Service References PAGEREF _Toc255484146 \h 29Task 3 – Configuring the AdventureWorks Shopping Project PAGEREF _Toc255484147 \h 31Task 4 – Persisting Changes to a Database PAGEREF _Toc255484148 \h 34Exercise 4 Verification PAGEREF _Toc255484149 \h 37Exercise 5: Importing OData Feeds using PowerPivot for Excel 2010: PAGEREF _Toc255484150 \h 39Task 1 – Importing Data from a Data Feed PAGEREF _Toc255484151 \h 40Task 2 – Using Excel PowerPivot PAGEREF _Toc255484152 \h 44Exercise 5 Verification PAGEREF _Toc255484153 \h 49Exercise 6: Building a PHP Application using Data from OData Feeds PAGEREF _Toc255484154 \h 50Task 1 – Exploring and Configuring the PHP Data Services Toolkit PAGEREF _Toc255484155 \h 50Task 2 – Creating a Simple PHP Application PAGEREF _Toc255484156 \h 52Task 3 – Configuring a PHP Application in IIS PAGEREF _Toc255484157 \h 56Exercise 6 Verification PAGEREF _Toc255484158 \h 57Summary PAGEREF _Toc255484159 \h 59OverviewThe Open Data Protocol (OData) is a web protocol for querying and updating data. Its specification is available under Microsoft's Open Specification Promise (OSP), therefore third parties, including open source projects, can build Open Data Protocol clients and services.OData is defined as a set of open extensions/conventions to AtomPub. It adds the following features to AtomPub.A convention for representing structured dataA resource addressing scheme and URL syntaxA set of common query options (filter, sort, etc.)Schema describing resource structure, links and metadataPayload formats and semantics for batch and “unit of work” requestsAlternate representations of resource content (JSON)In this lab, you will learn about the Open Data Protocol, the ecosystem where it works, and why developers should write applications and services using the Open Data Protocol as their Data Access protocol. You will also learn how to create simple applications in various technologies that manage the OData feeds.ObjectivesIn this Hands-On Lab, you will learn how to:Use Open Data Protocol in different environments. Create an OData feed over SQL Server 2008Explore OData feeds from Windows Azure Import OData feeds using the PowerPivot for Microsoft Excel 2010Create a PHP application reading data from OData feedsPrerequisitesThe following is required to complete this hands-on lab:Microsoft Visual Studio 2010Microsoft .NET Framework 4SQL Server 2008 Express Edition (or above)Silverlight 4 Tools for Microsoft Visual Studio 2010SetupFor convenience, much of the code used in this hands-on lab is available as Visual Studio code snippets. To check the prerequisites of the lab and install the code snippets:Run the Setup.cmd script located in the lab's Source\Setup folder to check dependencies and install any missing prerequisites.Once you have verified every prerequisite, follow the instructions to install the code snippets.Note: If the computer you are using has been setup especially to run this lab, the two steps above are unnecessary. Using the Code SnippetsWith code snippets, you have all the code you need at your fingertips. The lab document will tell you exactly when you can use them. For example,?To add this code snippet in Visual Studio, you simply place the cursor where you would like the code to be inserted, start typing the snippet name (without spaces or hyphens), in this case LabNameEx01RunmethodCS, watch as Intellisense picks up the snippet name, and then hit the TAB key twice once the snippet you want is selected. The code will be inserted at the cursor location. Figure 1Hit TAB to select the highlighted snippet.Figure 2Hit TAB again and the snippet will expandTo insert a code snippet using the mouse rather than the keyboard, right-click where you want the code snippet to be inserted, select Insert Snippet followed by My Code Snippets and then pick the relevant snippet from the list.To learn more about Visual Studio IntelliSense Code Snippets, including how to create your own, please see Creating and Using IntelliSense Code Snippets.ExercisesThis Hands-On Lab comprises the following exercises:Introduction to Open Data ProtocolCreating an OData Feed over SQL ServerExploring OData Feed from Windows AzureBuilding a Silverlight Client with OData FeedsImporting OData Feeds using PowerPivot for Excel 2010Building a PHP Application using Data from OData FeedsEstimated time to complete this lab: 60 minutes.Starting MaterialsThis Hands-On Lab includes the following starting materials.Visual Studio solutions. The lab provides the following Visual Studio solutions that you can use as starting point for the exercises.Source\Ex2\Begin\{C#|VB}\OpenDataServices.slnSource\Ex3\Begin\{C#|VB}\ OpenDataServices.slnSource\Ex4\Begin\{C#|VB}\ OpenDataServices.slnSource\Ex6\Begin\C#\ OpenDataServices.slnNote: Inside each exercise folder, you will find an end folder containing a solution with the completed lab exercise.Exercise 1: Introduction to Open Data ProtocolIn this exercise, you will learn the advantages provided by Open Data Protocol, the ecosystem; where it works, and why developers should write applications and services using the Open Data Protocol as their Data Access protocol. Today, one important value in a connected world is the ability to reuse and share data, by creating a system that can support multiples data sources. The Open Data Protocol, referred to as OData, is a web protocol for querying and updating data and it was born of the need to break down data silos and increase their shared value. This allows data silos to interoperate between producers such as SQL Server, SharePoint servers, Cloud Storage Services, and consumers, for example Java, PHP, Silverlight, IIS, , AJAX. OData uses web technologies, such as HTTP, Atom Publishing (AtomPub), and JSON to provide access to information from a range of applications, services, and stores like relational databases, file systems, content management systems, and traditional web sites. OData is also defined as a set of open extensions / conventions to AtomPub that are documented and released under the principle of OSP (Open Specification Promise) to allow everyone to freely interoperate with OData implementations.?Currently, the following Microsoft products support OData:Microsoft Visual Studio 2008 SP1Microsoft Visual Studio 2010Microsoft Office Excel 2010 (Through PowerPivot)SharePoint 2010SQL Server 2008 R2Windows Azure Table StorageUsing OData as the default data access protocol, allows your application to work and interact with other systems; without any implementation restriction because it supports HTTP, XML and JSON. Note: The data in OData is represented as rows using Atom feeds, where each entry has the usual metadata and is structured as name/value pairs inside content. Task 1 – Exploring an OData FeedIn this task, you will browse an OData feed and perform a simple query to filter out the data.Open an Internet Explorer windows and browse to the following URL. data shown contains general locations and amenity information about recreation parks of the Department of Columbia and it is formatted as an OData feed.Note: You might need to turn off Feed Reading View in Internet Explorer to visualize the Atom content. Go to the Tools menu, Internet Options, then click on the Content tab and select the Feeds and Web Slices Settings button. Make sure that the “Turn on feed reading view” option is unchecked. Add a filter to return only the data about MEDIAN parks. To do this, add the following query string to the URL and press the ENTER key to see the filtered results.?$filter=use_type eq 'MEDIAN'The resulting URL is the following.URL?$filter=use_type eq 'MEDIAN'You can perform a more complex query on the data, for example adding an additional filter.Replace, the previous query string with the following one, which shows MEDIAN parks that have seven or more wardens. The following is the resulting URL.URL?$filter=(use_type eq 'MEDIAN') and (ward ge '7')Figure 1Results feed of the OData queryNote: This data used in this exercise, is real data taken from the Open Government Data Initiative (OGDI). The OGDI project contains data gathered from government agencies. For more information about this, see Exercise 3For more information about OData, see .Exercise 2: Creating an OData Feed over SQL ServerIn this exercise, you will learn how to create a data service that exposes a SQL Server database, and consume it using a web browser.Task 1 – Creating the Data Service Web Application ProjectIn this task, you will create an Web application project that will host and expose the data service.Open Microsoft Visual Studio 2010 from Start | All Programs | Microsoft Visual Studio 2010 | Microsoft Visual Studio 2010.Open the OpenDataServices.sln solution file, located under the Source\Ex2\Begin folder of this lab (choosing the folder that matches the language of your preference). This is an empty solution for this exercise.In Solution Explorer, right-click the OpenDataServices solution, point to Add and select New Project.In the Add New Project dialog, select the language of your preference (Visual C# or Visual Basic), and then click the Web project type. Make sure that .NET Framework 4.0 is selected, and then select the Empty Web Application template. You may set the location to the Source\Ex2\Begin folder of this lab (and choosing the folder that matches the language of your preference).Set the Name to WebSite, and click OK.Figure SEQ Figure \* ARABIC 2Creating a new Website (C#)Figure SEQ Figure \* ARABIC 3Creating a new Website (Visual Basic)Configure the web site to use port 50000.To do this, in Solution Explorer, right-click the WebSite project and in the context menu select Properties. In the Property page, open the Web tab.In the Servers section, select Specific Port.Set the port number to 50000.Press Ctrl + S to save changes.Figure SEQ Figure \* ARABIC 4Specifying a port numberTask 2 – Creating the Entity Data ModelIn this task, you will create the mapping specification that connects programmable classes to storage structures.The Entity Data Model (EDM) is a specification for defining the data used by applications built on the Entity Framework. Applications using the EDM define a design schema, which includes entities and relationships in the domain of the application. Create the AdventureWorks Entity Data Model. To do this, in Solution Explorer, right-click the WebSite project, point to Add, and click New Item. In the Add New Item dialog box, select Entity Data Model. Specify a Name value of AdventureWorks.edmx, and then click Add.Figure SEQ Figure \* ARABIC 5Adding the Entity Data Model (C#)Figure SEQ Figure \* ARABIC 6Adding the Entity Data Model (Visual Basic)In the Entity Data Model Wizard, select Generate from Database and click Next.Specify the Database connection. To do this, follow the following steps:Click New Connection.In the Choose Data Source dialog, select Microsoft SQL Server Database File (SqlClient) as Data Source and click Continue.In the Connection Properties dialog, browse to Source\Assets\AdventureWorksShopping.mdf in the Database file name field, and then click OK.Figure SEQ Figure \* ARABIC 7Specify the database connectionBack on the Entity Data Model Wizard click Next, and then Yes when the Wizard asks to copy the database file to the application data folder.Include only the Tables objects from all the proposed database objects, leave the Model Namespace and the check boxes with its default values, and click Finish.Figure SEQ Figure \* ARABIC 8Choose database objects to include in the modelNote: For more information, see Entity Framework.Task 3 – Creating the WCF Data ServiceIn this task, you will create the WCF Data Service that exposes data by using the Entity Data Model as specified by the Entity Framework.Add the Data Service. To do this, in Solution Explorer, right-click the WebSite project, point to Add, and click New Item.In the Add New Item dialog box, select the WCF Data Service template. Specify a Name value of AdventureWorks.svc, and then click Add.Figure SEQ Figure \* ARABIC 9Add the WCF Data Service item (C#)Figure SEQ Figure \* ARABIC 10Add the WCF Data Service item (Visual Basic)Locate the class definition and replace it with the following code. This indicates that the service uses the Entity Framework Data Model as data source.Note: Visual Studio will open the code file for the new service by default. You can also find the file in the Solution Explorer by right-clicking the AdventureWorks.svc file.(Code Snippet – OData Lab – Ex 2.3.3 - AdventureWorks Data Service Definition CSharp)C#public class AdventureWorks : DataService<AdventureWorksShoppingEntities>(Code Snippet – OData Lab – Ex 2.3.3 - AdventureWorks Data Service Definition VB)Visual BasicPublic Class AdventureWorks Inherits DataService(Of AdventureWorksShoppingEntities)Grant read and write access to all resources of the Entity Data Model associated with the service, by replacing the InitializeService method with the following code:(Code Snippet – OData Lab – Ex 2.3.4 - AdventureWorks.svc InitializeService CSharp)C#public static void InitializeService(DataServiceConfiguration config){ config.SetEntitySetAccessRule("*", EntitySetRights.AllRead); config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V2;}(Code Snippet – OData Lab – Ex 2.3.4 - AdventureWorks.svc InitializeService VB)Visual BasicPublic Shared Sub InitializeService(ByVal config As DataServiceConfiguration) config.SetEntitySetAccessRule("*", EntitySetRights.AllRead) config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V2End SubNote: By default, a data service, does not expose any resources. Access to resources need to be explicitly enabled before any resources or associations are accessible. For more information, see IDataServiceConfiguration.SetEntitySetAccessRule Method (System.Data.Services).Exercise 2 Verification: Exploring the Data Services FeedIn this verification, you will use a web browser to consume the Data Service and to check its XML response.Note: The easiest way to try a data service is to simply access it through a web browser. While this is probably not the way you will ultimately use the data service (it is more likely that a program will interact with it), it is an easy way to understand how requests work, how results look like, and other details surrounding the implementation of the service.Navigate to the AdventureWorks data service. To do this, right-click the AdventureWorks.svc file and select View in Browser. You will notice that the XML response of the data service is a list of entity-sets. The entity-sets in the Entity Data Model (EDM) style represent the database tables that are exposed by the data service. The output will be similar to the following figure.Note: The XML document returned by default is an Atom service document because the default serialization used by the data service is Atom.Note: If the browser displays a message saying it cannot display the feed, turn off the feed reading view. In Internet Explorer, clear the Turn on feed reading view check box, located under Tools | Internet Options | Content Tab | Feeds Section | Settings,.Figure SEQ Figure \* ARABIC 11Data Service XML responseTo browse a specific entity, add its name to the address in the web browser. For instance, if the web server port is 50000 you should browse . The following output should appear.Figure SEQ Figure \* ARABIC 12Data Service XML response for the product entity setNote: The WCF Data Services framework implements a data addressing scheme that uses URIs. It is based on the Entity Data Model (EDM) specification for data types and data sets. For building more complex requests using URIs check Simple Addressing Scheme for Data with Uniform URLs.Exercise 3: Exploring OData Feed from Windows AzureIn this exercise, you will learn how to explore and perform queries on a OData repository that is hosted on Windows Azure.This exercise uses the Open Government Data Initiative (OGDI) databases. OGDI uses the Windows Azure Platform to make it easier to publish and use a wide variety of public data from government agencies. OGDI is also a free, open source starter kit, which includes code that can be used to publish data on the Internet in a Web-friendly format with easy-to-use, open API's. OGDI-based web API’s can be accessed from a variety of client technologies such as Silverlight, Flash, JavaScript, PHP, Python, Ruby, mapping web sites, etc.Task 1 – Examining the OGDI SiteIn this task, you will browse to the OGDI site to examine the data sets provided by the site.Open an Internet Explorer window, and browse to the site, you will find information about the initiative, including a webcast that shows how to use the OGDI site and some of its features. Click the Browse the Data link to start browsing the datasets included in the OGDI initiative.Select the Bank Locations data set from the Available Data Sets table. In this table, you can see more information from the data set; that is the source of the data and a brief description of the data set.Examine the data returned in the data set.Using the Filter Expression textbox, enter the following filter to return the banks that are located in the area with zip code 20006. Finally, click Run.zipcode eq 20006Notice that the Full Query Link is provided in the Results area, to view the result data in XAML/ATOM format.Figure 13Filtering the OGDI dataNote: For more information about filter expressions, click the question mark next to the Filter Expression textbox.Task 2 – Creating a Service Reference in a Client ApplicationIn this task, you will create a client application and add service reference to an OData service. Note: To perform this task you must have Visual Studio 2010, or Visual Studio 2008 SP1 with the Data Services.Open Microsoft Visual Studio 2010 from Start | All Programs | Microsoft Visual Studio 2010 | Microsoft Visual Studio 2010.Create a new Empty Web Application project. To do this go to the File menu, click New, and select Project.In the Add New Project dialog, select the language of your preference (Visual C# or Visual Basic) and then the Web project type. Make sure that .NET Framework 4.0 is selected, and then select the Empty Web Application template. You may set the location to the provided Source\Ex3\Begin folder of this lab (and choosing the folder that matches the language of your preference).Set the Name to WebSite and click OK.Figure 14Creating a new Website (C#)In Solution Explorer, right-click the WebSite project, and click Add Service Reference.Enter as the Address, and click Go to search for services. Visual Studio will find and show the available services.Set the Namespace to DistrictOfColumbia and click OK to add the service reference.Figure 15Adding the Service ReferenceIn Solution Explorer, right-click the DistrictOfColumbia service reference, and click View in Object Browser.Expand the WebSite.DistrictOfColumbia namespace by double-clicking it.Look for the dcDataService class, select it. In the right pane, you can observe all the methods and properties that were automatically generated when adding the service reference.Note: You can use the OpenData Protocol Visualizer, which creates a read-only graphical view of the types and relationships provided by a WCF Data Service. Once installed, right click the service reference and click the View Diagram option to generate the graphic.Exercise 4: Building a Silverlight Client with OData Feeds: In this exercise, you will create a Silverlight Application that consumes a local OData service and an OData service on the clouds. Additionally, this exercise demonstrates how to perform simple CRUD operations using LinQ.Task 1 – Creating Silverlight ApplicationIn this task, you will create the Silverlight client application, which exposes products for the categories available in each shopping center. Open Microsoft Visual Studio 2010, from Start | All Programs | Microsoft Visual Studio 2010, If the User Account Control dialog appears, click Continue.In the File menu, choose Open and then Project/Solution. In the Open Project dialog, browse to Ex4\Begin in the Source folder of the lab, select OpenDataService.sln in the folder of the language of your preference (Visual C# or Visual Basic), and click Open.Note: If you performed the previous exercise, you can use the same ending solution. The solution contains the following project:Figure 16Solution Explorer showing the Open Data solutionWebSiteA standard web site project that hosts the AdventureWorks service and expose its entities.In the Solution Explorer, right-click over the solution, select Add | New Project. In the New Project dialog, expand the language of your preference (Visual C# or Visual Basic) in the Project types list select Silverlight. In the Templates list, select Silverlight Application. Enter the name AdventureWorksShopping, and click OK to create the project.Figure 17Creating a new Silverlight Application project (C#)Figure 18Creating a new Silverlight Application project (VB)In the New Silverlight Application dialog, make sure that the WebSite project is selected to host the Silverlight application, and that the Silverlight 4 version of the framework is targeted. Make sure that the Add a test page references the application checkbox, and click OK.Figure 19New Silverlight Application DialogThe following figure shows the newly Silverlight Application in the Solution Explorer.Figure 20Solution Explorer Showing the Adventure Works Shopping project (C#)Figure 21Solution Explorer Showing the Adventure Works Shopping project (VB)Task 2 – Adding Service References In this task, you will create a reference to the Open Government Data Initiative (OGDI) and a reference to the Adventure Works service. Add the OGDI reference to the AdventureWorksShopping project. To do this, in Solution Explorer, right-click the AdventureWorksShopping project, and select Add Service Reference.Enter as the Address, and click Go to search for services. Visual Studio will find and show the available services.Set the Namespace to DistrictOfColumbia, and click OK to add the service reference.Figure 22Adding the Service ReferenceTo add the Adventure Work reference into the project, run the applications locally in the Development Server. To do this, in the Solution Explorer, right-click the AdventureWorks.svc file of the WebSite project and click View in Browser. Note: Notice that the Adventure Work service is configured to use the port number 50000. Make sure there is no other application that uses this port. If you wish, you can change the Web properties of the project. Figure Development Server Showing the Adventure Work host serviceAdd the AdventureWorks reference to the project. To do this, in the Solution Explorer, right-click the AdventureWorksShopping project, and click Add Service Reference.Enter as the Address, and click Go to search for services. Visual Studio will find and show the available services.Set the Namespace to AdventureWorks, and click OK to add the service reference.Figure 24Solution Explorer showing the Service Reference into the AdventureWorksShopping project.Task 3 – Configuring the AdventureWorks Shopping ProjectIn this task, you will complete the methods that query the database to retrieve the application data. All the queries use LinQ.Delete the MainPage.xaml file from the AdventureWorks. To do this, in the Solution Explorer, right-click the file and click Delete.Right-click the AdventureWorksShopping project, point to Add, and click Existing Item. Browse to the Source\Assets\Ex4\ folder, go to the subfolder that corresponds to the language of your preference, select the MainPage.xaml file, and click Add.Add the file that contains the styles of the application’s UI. To do this, right-click the AdventureWorksShopping project, point to Add, click New Folder and name it Styles. Right-click this folder, point to Add and click Existing Item. Browse to the Source\Assets\Ex4\ folder, select the Styles.xaml file, and click Add.Add a reference to the styles file in App.xaml. To do this, open App.xaml and add the following code, between the Application.Resources tags.XAML<Application.Resources> <ResourceDictionary> <ResourceDictionary.MergedDictionaries> <ResourceDictionary Source="Styles/styles.xaml"></ResourceDictionary> </ResourceDictionary.MergedDictionaries> </ResourceDictionary></Application.Resources>Add the following references to the AdventureWorksShopping by right-clicking the References folder in Solution Explorer, and clicking Add Reference.System.Windows.Controls.DataSystem.Windows.Controls.Data.InputSelect the MainPage.xaml file in Solution Explorer, and press F7 to open its code file.Check the AdventureWorks and DistrictOfColumbia properties; both initialize their respective data services in their getters.Locate the UserControl_Loaded method and add the following code.(Code Snippet - OData Lab - Ex04.3.8 UserControl_Loaded Method - CS)C#private void UserControl_Loaded(object sender, RoutedEventArgs e){ var categoryQuery = (from category in this.AdventureWorks.ProductCategory.Expand("ProductCategoryShopping") where category.ParentProductCategoryID != null select category) as DataServiceQuery<ProductCategory>; this.AsyncQueryOnDataContext(categoryQuery, this.CategoriesListbox); var shoppingCartQuery = this.AdventureWorks.ShoppingCart.Expand("Product"); this.AsyncQueryOnDataContext(shoppingCartQuery, this.ShoppingCartListbox);}(Code Snippet - OData Lab - Ex04.3.8 UserControl_Loaded Method - VB)Visual BasicPrivate Sub UserControl_Loaded(ByVal sender As Object, ByVal e As RoutedEventArgs) Dim categoryQuery = TryCast((From category In Me.AdventureWorks.ProductCategory.Expand("ProductCategoryShopping") _ Where category.ParentProductCategoryID IsNot Nothing _ Select category), DataServiceQuery(Of ProductCategory)) Me.AsyncQueryOnDataContext(categoryQuery, Me.CategoriesListbox) Dim shoppingCartQuery = Me.AdventureWorks.ShoppingCart.Expand("Product") Me.AsyncQueryOnDataContext(shoppingCartQuery, Me.ShoppingCartListbox)End SubThe preceding method queries the AdventureWorks service and retrieves the data necessary to populate the list boxes of the application when it is loaded.Locate the CategoriesListBox_SelectionChanged method, which updates the list of products when a category is selected, and add the following code.(Code Snippet - OData Lab - Ex04.3.9 CategoriesListBox_SelectionChanged Method - CS)C#private void CategoriesListbox_SelectionChanged(object sender, SelectionChangedEventArgs e){ var category = this.CategoriesListbox.SelectedItem as ProductCategory; PopulateFilteredShoppingCenters(category); this.AdventureWorks.BeginLoadProperty( category, "Product", result => this.Dispatcher.BeginInvoke(() => { this.AdventureWorks.EndLoadProperty(result); this.ProductsListbox.DataContext = category.Product; }), null);}(Code Snippet - OData Lab - Ex04.3.9 CategoriesListBox_SelectionChanged Method - VB)Visual BasicPrivate Sub CategoriesListbox_SelectionChanged(ByVal sender As Object, ByVal e As SelectionChangedEventArgs) Dim category = TryCast(Me.CategoriesListbox.SelectedItem, ProductCategory) PopulateFilteredShoppingCenters(category) Me.AdventureWorks.BeginLoadProperty(category, "Product", Sub(result) Me.Dispatcher.BeginInvoke(Sub() Me.AdventureWorks.EndLoadProperty(result) Me.ProductsListbox.DataContext = category.Product End Sub) End Sub, Nothing)End SubLocate the PopulateFilteredShoppingCenters method and add the following code. This method creates a filter to return only the shopping centers that sell the specified product category.(Code Snippet - OData Lab - Ex04.3.10 PopulateFilteredShoppingCenters Method - CS)C#private void PopulateFilteredShoppingCenters(ProductCategory category){ if (category != null && category.ProductCategoryShopping != null && category.ProductCategoryShopping.Count > 0) { var shoppingQuery = (from shoppingCenter in this.DistrictOfColumbia.ShoppingCenters.AddQueryOption("$filter", this.CreateShoppingsFilter(category)) select shoppingCenter) as DataServiceQuery<ShoppingCentersItem>; this.AsyncQueryOnDataContext(shoppingQuery, this.ShoppingsListbox); } else { this.ShoppingsListbox.DataContext = null; }}(Code Snippet - OData Lab - Ex04.3.10 PopulateFilteredShoppingCenters Method - VB)Visual BasicPrivate Sub PopulateFilteredShoppingCenters(ByVal category As ProductCategory) If category IsNot Nothing AndAlso category.ProductCategoryShopping IsNot Nothing AndAlso category.ProductCategoryShopping.Count > 0 Then Dim shoppingQuery = TryCast((From shoppingCenter In Me.DistrictOfColumbia.ShoppingCenters.AddQueryOption("$filter", Me.CreateShoppingsFilter(category)) _ Select shoppingCenter), DataServiceQuery(Of ShoppingCentersItem)) Me.AsyncQueryOnDataContext(shoppingQuery, Me.ShoppingsListbox) Else Me.ShoppingsListbox.DataContext = Nothing End IfEnd SubPress Ctrl+B to build the solution.Task 4 – Persisting Changes to a DatabaseIn this task, you will complete the methods that manage the shopping cart and save its content to the database.In the MainPage.xaml.cs file, locate the AddItemButton_Click method and add the following code. This method adds one unit of the corresponding product to the shopping cart.(Code Snippet - OData Lab - Ex04.4.1 AddItemButton_Click Method - CS)C#private void AddItemButton_Click(object sender, RoutedEventArgs e){ var product = ((Button)sender).DataContext as Product; if (product != null) { var shoppingCart = this.ShoppingCartListbox.DataContext as ObservableCollection<ShoppingCart>; var item = shoppingCart.SingleOrDefault(i => i.ProductID == product.ProductID); if (item == null) { var newShoppingCart = new ShoppingCart { ProductID = product.ProductID, Product = product, ItemQty = 1 }; shoppingCart.Add(newShoppingCart); this.AdventureWorks.AddToShoppingCart(newShoppingCart); } else { item.ItemQty++; this.AdventureWorks.UpdateObject(item); } }}(Code Snippet - OData Lab - Ex04.4.1 AddItemButton_Click Method - VB)Visual BasicPrivate Sub AddItemButton_Click(ByVal sender As Object, ByVal e As RoutedEventArgs) Dim product = TryCast(DirectCast(sender, Button).DataContext, Product) If product IsNot Nothing Then Dim shoppingCart = TryCast(Me.ShoppingCartListbox.DataContext, ObservableCollection(Of ShoppingCart)) Dim item = shoppingCart.SingleOrDefault(Function(i) i.ProductID = product.ProductID) If item Is Nothing Then Dim newShoppingCart = New ShoppingCart() shoppingCart.Add(newShoppingCart) Me.AdventureWorks.AddToShoppingCart(newShoppingCart) Else item.ItemQty += 1 Me.AdventureWorks.UpdateObject(item) End If End IfEnd SubLocate the DeleteItemButton_Click method and add the following code.(Code Snippet - OData Lab - Ex04.4.2 DeleteItemButton_Click Method - CS)C#private void DeleteItemButton_Click(object sender, RoutedEventArgs e){ var item = ((Button)sender).DataContext as ShoppingCart; if (item != null) { var shoppingCart = this.ShoppingCartListbox.DataContext as ObservableCollection<ShoppingCart>; shoppingCart.Remove(item); this.AdventureWorks.DeleteObject(item); }}(Code Snippet - OData Lab - Ex04.4.2 DeleteItemButton_Click Method - VB)Visual BasicPrivate Sub DeleteItemButton_Click(ByVal sender As Object, ByVal e As RoutedEventArgs) Dim item = TryCast(DirectCast(sender, Button).DataContext, ShoppingCart) If item IsNot Nothing Then Dim shoppingCart = TryCast(Me.ShoppingCartListbox.DataContext, ObservableCollection(Of ShoppingCart)) shoppingCart.Remove(item) Me.AdventureWorks.DeleteObject(item) End IfEnd SubIn the preceding code, the selected item is removed from the Shopping cart.Locate the CheckoutButton_Click method and add the following code to save the shopping cart in the database.(Code Snippet - OData Lab - Ex04.4.3 CheckoutButton_Click Method - CS)C#private void CheckoutButton_Click(object sender, RoutedEventArgs e){ this.AdventureWorks.BeginSaveChanges(result => { var response = this.AdventureWorks.EndSaveChanges(result); MessageBox.Show("Changes saved successfully!"); }, null);}(Code Snippet - OData Lab - Ex04.4.3 CheckoutButton_Click Method - VB)Visual BasicPrivate Sub CheckoutButton_Click(ByVal sender As Object, ByVal e As RoutedEventArgs) Me.AdventureWorks.BeginSaveChanges(Sub(result) Dim response = Me.AdventureWorks.EndSaveChanges(result) MessageBox.Show("Changes saved successfully!") End Sub, Nothing)End SubNote: This exercise uses Entity Framework to manage the data access of the application.Press Ctrl+B to build the solution.Exercise 4 VerificationIn this verification, you will explore the Silverlight application; add some products to your shopping cart, and save it to the database.Set the Website project as the startup project. To do this, right-click the Website project in Solution Explorer, and click Set as StartUp Project.Set the AdventureWorksShoppingTestPage.html as the startup page, by right-clicking it in Solution Explorer, and then clicking Set As Start Page.Figure 25Setting the Start PagePress F5 to run the solution.In the application, select the Mountain Bike category to display the shopping centers that sell products from this category. Figure 26The AdventureWorks Shopping applicationClick the + button to add some products to the shopping cart. You can also add products from other categories.Try deleting some products from the shopping cart by clicking in the X button next to the desired product.Note: The shopping cart is implemented using an Observable Collection instance, therefore when you add or remove products from it, the Datagrid is notified, so it is refreshed to reflect the changes.When you are ready, click the Checkout button to save the changes to the database. Notice that this is the point where the changes are impacted in the database, when items are added or removed; these operations are kept in-memory.Figure 27Saving the Shopping CartClose Internet Explorer to finish the application.Exercise 5: Importing OData Feeds using PowerPivot for Excel 2010: The SQL Server PowerPivot for Excel component is used for loading and organizing BI data, and to build an analysis on it using pivot tables. The result is called a “model”. A wide range of data sources can be loaded. The overall experience is similar to how Excel pivot tables have traditionally been used for years, except that with PowerPivot, far more data can be manipulated (100 million rows has been demonstrated in 64 bit mode with sufficient memory). The data also can be analyzed with much faster performance than before. PowerPivot for Excel is an overlay on Excel but is designed to look and act like Excel as much as possible so that this greatly enhanced capability still feels familiar to both casual and power Excel users. Power users can also use a new expression language called Data Analysis Expressions (DAX) to exploit a host of multidimensional capabilities from within Excel that are difficult or impossible otherwise.Workbooks containing PowerPivot data and pivot tables are XLSX files and can be shared, copied, emailed, uploaded to a SharePoint document library and otherwise treated just like non-PowerPivot workbooks. They can be opened in a browse-only mode by versions of Excel lacking PowerPivot for Excel (such as pre-Office 2010 versions). There may be limitations to treating PowerPivot workbooks in these traditional ways, however:The file size may preclude emailing if a large amount of data was loaded, in spite of the compression.A client must have PowerPivot for Excel to fully leverage such workbooks.Such use does not avoid the proliferation of “spreadmarts” which are the bane of many IT departments.Note: PowerPivot for SharePoint addresses all of these issues.Note: For more information, you can download the PowerPivot for Excel help file from 1 – Importing Data from a Data FeedIn this exercise, you will become familiar with loading data into Microsoft SQL Server PowerPivot for Excel. As in any version of Excel, this is the first step in preparing for data analysis.PowerPivot for Excel acts as an overlay to Excel, with which it is tightly integrated but distinct. When importing data it presents a worksheet UI similar to Excel – but each is running as a separate application (as can be seen in Task Manager), and at first it can be easy to confuse which you are in or how to switch between them. As different actions require one or the other environment, being able to make the distinction is important. As in native Excel, PowerPivot for Excel supports a variety of data sources, including typical relational database sources, Access, linked Excel tables and copy/paste. It also supports new sources unique to this add-in, namely Atom 1.0-based feeds from Reporting Services reports and SharePoint lists (these also require Reporting Services 2008 R2 and SharePoint 2010), Analysis Services databases, text files and regular Excel documents.Open Microsoft Excel 2010 from All Programs | Microsoft Office | Microsoft Excel 2010. Click the PowerPivot tab to show that ribbon, and then click the PowerPivot window button to launch the PowerPivot window.In the Get External Data group, click the From Data Feeds button and select From Other Feeds to open the Table Import Wizard.Figure 28PowerPivot Table Showing How to Get Data From Other FeedsEnter in the Data Feed Url field, and click Test Connection to verify that the connection to the OGDI feed is successful. Click Next to continue.Figure 29Entering The Data Feed Url Into The Table Import Wizard DialogClick the friendly name of the table and rename it to ShoppingCenter, and then click Finish to continue.Figure 30Selecting The Data Feed Content The wizard will start importing the table and showing the progress. When it finishes successfully, it will show how many rows were imported. Click Close to close the wizard.Figure 31Showing The Importing Process Task 2 – Using Excel PowerPivotIn this task, you will learn how to import data from differences feeds using PowerPivot; how to create relationships, as well as how to create a pivot table to show the associated categories to each shopping.Open Microsoft Visual Studio 2010 from Start | All Programs | Microsoft Visual Studio 2010 | Microsoft Visual Studio 2010. Open the end solution of exercise 2. To do this, in click the File menu, point to Open, and select Project/Solution. Browse to \Labs\NETFX4\BuildingAppAndServicesUsingOData\Source\Ex5\End and open the OpenDataServices.sln file.Press F5 to run the solution.Switch to the PowerPivot window. In the Get External Data group, click the From Data Feeds button and select From Other Feeds to open the Table Import Wizard.Enter in the Data Feed Url field, and click Test Connection to verify that the connection to the local feed is successful. Click Next to continue.Select the ProductCategory and ProductCategoryShopping tables, and then click Finish to continue.The wizard will start importing the tables and showing the progress. When it finishes successfully, it will show how many rows were imported for each table. Click Close to close the wizard.Click the Table tab to expand the ribbon bar, and click the Create Relationship button.Create the relationship between the product and product categories tables. To do this, in the Create Relationship window, select the Product table in the Table combo box and the ProductCategoryID field as the Column. In the Related Lookup Table combo box, select the ProductCategory table. Finally, click Create.Figure 32Creating Relationship To Product and ProductCategory TablesRepeat the previous steps and create the following relationships.ProductCategoryShopping and ProductCategory tables using the ProductCategoryID columnProductCategoryShopping and ShoppingCenter tables using the gis_id columnClick the Manage Relationship button, to see the relationships you have just created. You should see a screen similar to the following figure.Figure 33Showing The Created RelationshipsCreate the pivot table. To do this, on the Home ribbon, click PivotTable button and select Single PivotTable.Figure 34Creating a Single PivotTableIn the popup window, click Ok to insert the pivot in a new worksheet.In the Gemini Task Pane select the following fields to include them in the report.Name from the ProductCategory tableGis_id from the ProductCategoryShopping tableName from the ShoppingCenter tableFigure 35Adding Fields to The Report Rename the name fields by right-clicking them, and then selecting Field Settings. Change the Custom Name property using the following.Rename the Name field from the ProductCategory table to CategoryRename the name field from the ShoppingCenter table to ShoppingDrag and drop the Category item to the Column Labels box.Drag and drop the gis_id item to the Values box. Right-click the item, point to Summarize by, and click Sum.From the fields’ list box, drag and drop a copy of the name field from the ShoppingCenter table, to the Slicers Horizontal box. Rename this item to Shopping Name. The following figure shows the final configuration of the pivot table.Figure 36Configuring The Pivot TableExercise 5 VerificationTo verify that you have correctly performed all steps of exercise 5, proceed as follows:Verification 1In this verification, you will use the pivot table to show the associated categories to each shopping.In the Shopping Name slicer, select the first shopping center. Notice how the product categories for each shopping are updated when other shopping centers are selected. The following figure reflects this.Figure 10Showing The Report ResultsExercise 6: Building a PHP Application using Data from OData FeedsIn this exercise, you will learn how to create a PHP application using data from Open Data feeds; how to create proxy classes using the Toolkit for PHP with Data Services as well as how to manipulate data using the generated proxy file. Finally, you will configure the Internet Information Services (IIS) Manager in order to verify the PHP application.Task 1 – Exploring and Configuring the PHP Data Services ToolkitIn this task, you will become familiar with the PHP Toolkit for Data Services, and you will create proxy classes to the Open Government Data Initiative (OGDI) database and the AdventureWorks service. The PHP Toolkit for Data Services enables PHP developers to connect to data services and take advantage of services built using ADO .NET Data Services; providing similar functionality as the .NET library to access and modify data, and its relationships using URIs, which point to pieces of data integrated with the web. The logical architecture of the Toolkit is based on a utility (PHPDataSvcUtil) that is used for generating a proxy class from an ADO .NET Data Service; in this task you will create proxy classes for the OGDI and the AdventureWorks services. Figure 1The Toolkit for PHP with Data Services ArchitectureNote: The PHPDataSvcUtil tool generated a single proxy PHP file that contains class definitions. In addition, notice that for each EntityType it generates one container class and one class.Note: The assumption is that PHP is already installed and configured on the machine where the Data Services toolkit is installed. If not, you can easily install it through the Windows Platform Installer. To do this, go to and follow the provided instructions.Configure the Toolkit for PHP with Data Service. To do this, download the Toolkit for PHP with Data Services from the files contained in the OData_PHP_SDK.zip package and follow the user guide to install and enable the toolkit. Create the proxy class to the OGDI Data Service. To do this, open a command prompt, go to the path where the client library files of the toolkit are installed, and execute the following command. Replace the <PATH_SOURCE_EX6_BEGIN> placeholder with the path where the begin folder of the exercise is located. Command Promptphp PHPDataSvcUtil.php /uri= /out=<PATH_SOURCE_EX6_BEGIN>OpenGovermentDataInitiativeEntities.phpFigure 38Generating the OGDI proxyNote: The PHPDataSvcUtil.php utility will read the Data Service metadata and create the PHP proxy classes into the OpenGovertmentDataInitiativeEntities.php file. Notice that <c:\PHPLib\ODataPHP> is the path by default.Create the proxy class to the AdventureWorks Service Reference, to do this open a command prompt, go to the path where the client library files are installed, and execute the following command.C#php PHPDataSvcUtil.php /uri= /out=<PATH_SOURCE_EX6_BEGIN>\AdventureWorksShoppingEntities.phpFigure 39Generating the AdventureWorks proxyNote: If you have any trouble generating these proxy files, you can find them in Source\Assets\Ex6\PHPAdventureWorksShopping folder.Task 2 – Creating a Simple PHP ApplicationIn this task, you will create a simple PHP Application to consume data from the two data sources created in the previous task. You will provide functionality to display three lists of data. Create a new folder named PHPAdventureWorksShopping under Source\Ex6\Begin.Browse to the Source\Assets\Ex6\PHPAdventureWorksShopping folder.Copy the index.php and site.css files, and the Images folder into the PHPAdventureWorksShopping folder that you created in the previous step.Copy the files of the generated proxy class to access the open data exposed by the Adventure Works service (AdventureWorksShoppingEntities.php) and the Open Government Data Initiative service (OpenGovermentDataInitiativeEntities.php) that you created in exercise 1. To do this, browse to the generated files and copy them into the PHPAdventureWorksShopping folder. In addition, you can locate the pre-built files into the Assets folder at Sources\Assets\Ex6\PHPAdventureWorksShopping.Now, you will modify the main application to include the proxy classes. To do this, open the index.php file and add the following code below the corresponding line.PHP//Include Data Services Classrequire_once 'OpenGovermentDataInitiativeEntities.php';require_once 'AdventureWorksShoppingEntities.php';Next, you will define PHP named constants with both the OGDI and Adventure Works service URL. To do this, add the following code below the corresponding line.PHP// Define Data Service Endpointsdefine("OGDIDB_SERVICE_URL", "");define("ADVENTUREWORKS_SERVICE_URL", "");Note: Notice the local port is set to 50000 make sure there is no other application running that is using the same port number.Before you can retrieve any data from the services, you will create the service proxies. To do this, add the following code below the corresponding line.PHP//Adventure Work Proxy$adventureWorkProxy = new AdventureWorksShoppingEntities(ADVENTUREWORKS_SERVICE_URL); //Open Government Data Initiative (OGDI) Proxy.$ogdiProxy = new dcDataService(OGDIDB_SERVICE_URL); You will add PHP code to query the Adventure Works service and retrieve a filtered set of categories to populate the HTML list box with <option> tags. To do this, add the following code for querying the ProductCategory inside the Category <fieldset> and populate the List box.XML<div class="fieldsA"> <h2> Category: </h2> <fieldset> <select name="categories" id="categoryID" onchange="this.form.submit();" multiple="false"> <?php try { $categoryQuery = $adventureWorkProxy->ProductCategory()->filter("ParentProductCategoryID ne null")->Execute()->Result; foreach ($categoryQuery as $category) { ?> <option value="<?php echo $category->ProductCategoryID;?>" <?php if (substr_compare($category->ProductCategoryID, $categoryFilterID, 0) == 0) { $selectedCategory = $category; ?> selected <?php } ?> ><?php echo $category->Name;?></option> <?php } } catch (Exception $e) { echo "Error:" . $e->getError() . "<br>" . "Detailed Error:" . $e->getDetailedError(); } ?> </select> </fieldset></div>Now, you will query the OGDI service to retrieve a list of Shopping Centers and populate the HTML list box. You should take into account that the list will be filtered by the available shopping centers for the selected category. To do this, add the following code for querying the ProductCategoryShopping set and then the ShoppingCenters set inside the Shopping Centers <fieldset> and populate the List box.XML<div class="fieldsB"> <h2> Shopping Centers:</h2> <fieldset> <select name="shoppingCenters" id="shoppingCentersID" multiple="true"> <?php try { $categoriesShopping = $adventureWorkProxy->ProductCategoryShopping()->filter("ProductCategoryID eq " . $categoryFilterID)->Execute()->Result; $shoppingFilter = CreateShoppingsFilter($categoriesShopping); $shoppingQuery = $ogdiProxy->ShoppingCenters()->filter($shoppingFilter)->Execute()->Result; foreach ($shoppingQuery as $shopping) { ?> <option value="<?php echo $shopping->name;?>"><?php echo $shopping->name;?></option> <?php } } catch (Exception $e) { echo "Error:" . $e->getError() . "<br>" . "Detailed Error:" . $e->getDetailedError(); } ?> </select> </fieldset></div>Now you will query the Adventure Works service again, and this time you will retrieve the list of Products for the selected Category and populate the HTML list box. To do this, add the following code for querying the Product set inside the Products <fieldset> and populate the List box.PHP<div class="fieldsB"> <h2> Products:</h2> <fieldset> <select name="products" id="productsID" multiple="true"> <?php try { $productQuery = $adventureWorkProxy->Product()->filter("ProductCategoryID eq " . $categoryFilterID)->Execute()->Result; foreach ($productQuery as $product) { ?> <option value="<?php echo $product->Name;?>"><?php echo $product->Name;?></option> <?php } } catch (Exception $e) { echo "Error:" . $e->getError() . "<br>" . "Detailed Error:" . $e->getDetailedError(); } ?> </select> </fieldset></div>Task 3 – Configuring a PHP Application in IISIn this task, you will create a new Web Application to host the PHP content.Open Internet Information Services (IIS) Manager (inetmgr.exe).Expand the server node and then expand Sites.Right-click the Default Web Site in the left-hand tree view.Click Add Application.Enter PHPAdventureWorksShopping as the Alias.Enter the folder that you created in the previous task including all your php files. In this case Source\Ex6\begin\PHPAdventureWorksShopping for Physical Path.Figure 2Adding new Web ApplicationYour new PHP application should be available in the Default Web Site node.Figure 3New PHP Adventure Works Shopping applicationExercise 6 VerificationTo verify that you have correctly performed all steps of exercise 6, proceed as follows:Verification 1In this verification, you will use the pivot table to show the categories associated to each shopping.Open Microsoft Visual Studio 2010, from Start | All Programs | Microsoft Visual Studio 2010. If the User Account Control dialog appears, click Continue.In the File menu, choose Open and then Project/Solution. In the Open Project dialog, browse to Ex6\Begin in the Source folder of the lab, select OpenDataService.sln, and click Open.Note: If you performed the previous exercise, you can use the same ending solution. Navigate to the AdventureWorks data service. To do this, right-click the AdventureWorks.svc file and select View in Browser. You should be able to see the XML response of the data service as a list of entity-sets.Open Internet Explorer and browse to the recently created Web application. In this case, navigate to . You should see the categories, shopping centers, and products list boxes.Figure 4PHP AdventureWorks Shopping ApplicationClick the Mountain Bikes Category. The application will process your request and the Shopping Centers list box will show the shopping centers where that category is available. The Products list box will be populated with mountain bike category products.Figure 5Selected Mountain Bikes CategoryClose Internet Explorer to finish the application.?SummaryIn this Lab, you have learnt what Open Data is and how to consume it from .NET applications. You also learnt what the Open Data Government Initiative is. It was demonstrated how to build applications that consume Open Data from different sources, for example a local database and a database on the clouds. Then, you have seen how to use the PowerPivot add-in for Microsoft Excel 2010 that reads and organize the OData data to perform analysis on it. And finally, you used the PHP Toolkit to create a proxy class for an OData service and use it in a seamless way. ................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.