With LightSwitch, there are many ways for you to connect to external data. You can do this through the Add Data Source dialog, and for non-standard data sources, you can create your own RIA service. OData (Open Data Protocol) provides yet another choice. While other data-access methods allow you to connect to data sources, the added benefit of OData is that you can use it to view and update LightSwitch data from applications outside of LightSwitch. In this chapter, I’ll show you how to do the following:

• connect to third-party data by using OData
• share your LightSwitch data with other applications
• update LightSwitch data from outside of LightSwitch

This chapter will extend the Help Desk application by combining office location details with travel-advice data that comes from an Internet data source. I’ll show you how to connect to LightSwitch data from an Excel spreadsheet, and also how to build an external ASP.NET website that users can utilize to update issue records.

Introduction

OData is an open standard that provides a common way to retrieve and update data via the web. A common standard makes it easier for you to connect to external data because it saves you from having to learn a separate data-access API for each data type you want to access. For example, SAP (the enterprise resource-planning software product) publishes its data via OData. The great thing is that you can display SAP data inside LightSwitch without your having to learn a complicated new API.

If you want to connect to LightSwitch data through an external application, there are many languages and platforms that you can use, including Java, PHP, Ruby, JavaScript, and many more.

In the first section, you’ll learn to attach to an OData data source. Later in the book, you’ll find out how to attach to LightSwitch data from Excel and .NET.

Using External Data Sources

By building a LightSwitch application that connects to external data sources with OData, you can consolidate the vast amounts of disparate data that organizations own. Microsoft products that support OData include SharePoint 2010, Microsoft Dynamics CRM 2011, and SQL Server Reporting Services 2012. If your company uses any of these products, you can use LightSwitch to connect to the data that these applications hold.

In addition to accessing OData data sources from within your organization, there are plenty of third-party and external data sources that you can use. I’ll now show you how to find public data sources that you can incorporate into your LightSwitch applications.

Finding Third-Party Data Sources to Use

A great place to find public data sources is the EcoSystem page on the official OData website (http://www.odata.org/ecosystem). Here, you’ll find a list of third-party OData providers that includes EBay, Netflix, and Windows Live.

Another great place is the Data Services section of the Azure Marketplace (https://datamarket.azure.com/browse/data). Here, you’ll find many data sources, some of which are free, and others that must be paid for.

To demonstrate how to consume an OData data source, I’ll now show you how to connect to a travel-advisory service that the UK Foreign and Commonwealth Service provides. This enables the Help Desk application to associate travel-advice information with the office locations that are stored in your application. You can find this service by visiting http://datamarket.azure.com/dataset/uk.gov/traveladvisoryservice.

To use this service, you’ll need to create an account on the Windows Azure Marketplace. Once you do this, the next step is to click on the Sign Up button that you’ll find on the Travel Advisory Service’s web page (at the time of writing, it’s free to sign up). Once you sign up for a Marketplace service, you can use the website’s “Explore this Dataset” option to preview the data that the service offers (see Figure 15-1).

Determining Connection Settings

To connect to an OData data source, you need to know the web address that identifies the data source as well as any authentication details that the service requires.

The Travel Advisory Service displays these details in the Details tab of its web page. The OData address that you’ll need is labeled “Service root URL,” as shown in Figure 15-2.

All the services from the Azure Marketplace require authentication. To find out your credentials, go to the My Account section of the Windows Azure Marketplace and make a note of the Customer ID and Account Key settings (Figure 15-3). You’ll need these details to authenticate to this service from LightSwitch.

Connecting to an OData Data Source

To connect to an OData data source from LightSwitch, right-click Solution Explorer and select the “Add Data Source” menu item to open the Attach Data Source wizard. When the wizard opens, select the OData option. In the Enter Connection Information page, enter your OData URL into the “Specify the OData service endpoint” text box (Figure 15-4). In the travel-advisory example, the Customer ID is your username and the Primary Account Key is your password.

By default, LightSwitch checks the “Attach to this data source as read-only” checkbox. This is because OData data sources are more likely to be non-updatable. This option disables the insert, update, and delete commands on screens and also prevents you from updating the data source through code.

The next step in the wizard prompts you to select the entities that you want to use in your application (Figure 15-5). Once you complete the wizard, you can use the data source just as you would any other data source. Remember that you can also add relationships between entities in different data sources. Figure 15-6 shows the appearance of this data when you include it on a screen that includes department data.

Understanding Default Data-Source Options

OData feeds from different sources behave differently. In the Foreign and Commonwealth Office (FCO) example, the FCO might not implement all the underlying OData query operators that LightSwitch supports. For example, some OData data sources don’t support paging, sorting, or certain kinds of query filters.

To stop your application from breaking when LightSwitch tries to do something that your data source doesn’t support, LightSwitch applies application options that are more restrictive than normal. First, it switches off searching by setting the Is Searchable option to false at the entity level. Any collections of data that you add to a screen will also have their Support Search settings set to false. If you later discover that your OData data source supports searching, you can manually enable the setting in the table designer’s property sheet. However, you should be aware that this can have a negative impact on performance.

To further help performance, LightSwitch disables eager loading if your screen contains multiple related tables from an OData data source. Finally, LightSwitch also disables pagination on data grids and data lists if it isn’t able to request an individual page of data or count how many pages of data there are.

Exposing Your LightSwitch Data Via OData

With OData, you can very easily attach to LightSwitch data from external applications. LightSwitch publishes your data through OData feeds, and in this section I’ll show you how it works.

Finding Your Application’s Endpoint URL

LightSwitch creates service endpoints for each data source in your application. You can access these endpoints in a web browser by entering the root URL for your application followed by the data-source name with a .SVC extension, followed by the name of your entity set. Figure 15-7 shows some example URLs.

To see the data that an OData endpoint returns, start up a LightSwitch web application and make note of the root URL—it’ll look something like this: http://localhost:41155/. Now, open a new browser tab and enter http://localhost:41155/ApplicationData.svc/Engineers into the address bar. This returns the raw data, as shown in Figure 15-8.

Note that by default, Internet Explorer applies an RSS Feed View to this data. This view hides much of the content, so if you want to see just the raw XML, you can switch off the “Turn on feed reading view” option in Internet Explorer. You can find this option by clicking Tools Internet Options Content Feeds and Web Sites (Settings).

Querying OData

With OData, you can query data by appending query operators to the end of your URL. To return the first engineer, you would use this URL:

http://localhost:41155/ApplicationData.svc/Engineers(1)

You can use the OData protocol to access related records by using similar syntax. To return all issues that are assigned to the first engineer, you’d use the following URL:

http://localhost:41155/ApplicationData.svc/Engineers(1)/Issues.

If you want to find all engineers with a surname of Smith, you’d use

http://localhost:41155/ApplicationData.svc/Engineers?$filter=Surname eq 'Smith'

Securing OData Feeds

The ability for a user to see all your data exposed through an OData endpoint can make you feel vulnerable. For instance, any user could easily view all of your engineer records by typing this address into their browser:

http://localhost:41155/ApplicationData.svc/Engineers

If this worries you, there sadly isn’t a way to switch off this behavior. However, you can secure your data by using the security features that LightSwitch provides. The first thing that you should do is to enable authentication. This prevents someone from accessing the endpoint without a password, or without being Windows authenticated. Next, you can limit access to your data by writing code in your entity’s CanRead, CanUpdate, CanDelete, and CanInsert methods. If you want to apply row-level access control, you can write code in your entity’s Entity_Filter method. To find out more about how to secure your data, refer to Chapters 21 and 22.

Consuming LightSwitch Data

Now that you understand how LightSwitch exposes your data using OData, I’ll show you how to access your data from outside of LightSwitch. In this section, I’ll show you how to connect to your data from both Microsoft Excel and .NET.

Connecting to LightSwitch Data from Excel

With Excel 2013 and above, you can connect to LightSwitch data through OData. The benefit of Excel is that it allows users to analyze their data by using custom formulas, charts, or PivotTables. To create a connection, open Excel and select the OData option in the Get External Data group within the ribbon bar’s Data tab (Figure 15-9).

Connecting to LightSwitch Data from .NET

Any application that you build with .NET 3.5 or above can access LightSwitch data through WCF (Windows Communication Foundation) Data Services. The primary purpose of this section is to show you how to connect to LightSwitch data from an external .NET application. To do this, I’ll show you how to build an ASP.NET Web Forms application to allow users to create and view issue records. An ASP.NET Web Forms application is the easiest way to demonstrate the coding technique, but in practice you can apply the same technique on all types of .NET applications (for example, MVC, WPF, or Windows Forms applications).

To create this application, start Visual Studio, create a new ASP.NET Web Forms application, and name it HelpDeskPortal. Right-click your project and select the “Add Service Reference” option from Solution Explorer. This opens the dialog that’s shown in Figure 15-10.

Enter the URL of your OData endpoint into the “Address” text box (e.g., http://WebServer/YourAppName/ApplicationData.svc) and click the Go button to populate the Services list box. Click the OK button to complete the addition of the service reference. The service reference (also known as proxy or service client) enables you to access your LightSwitch entities in .NET code.

Adding Data from ASP.NET

To demonstrate how to create records outside of LightSwitch, let’s see how to build a web page that users can use to create new issue records. To create this page, add a new Web Form to your ASP.NET project and name it CreateIssue.aspx. Next, add the code that’s shown in Listing 15-1. This code contains the markup for a page that includes data-entry controls and a button that performs the save operation.

The IssueSubject and IssueDescription text boxes provide controls to enable the user to enter a subject and description. The Add Issue button calls the code that creates a new issue record in your LightSwitch database (Listing 15-2).

The first part of the code creates the service context that enables you to access your LightSwitch data. The constructor requires you to supply a URL for your OData endpoint. This example hard codes the URL, but in practice it’s a good idea to store the value in your application’s web.config file. This would allow you to change the endpoint address after you deploy your application. If you’ve sensibly secured your application by enabling authentication (see Chapter 21), the commented-out line of code shows how you would supply a username and password.

The next part of the code creates a new instance of an issue entity and sets the value of properties that include the subject, create date, target end date, and description. Visual Studio provides you with full IntelliSense suggestions in the code editor. This makes it easy for you to set your entity’s property values, because there’s no need for you to remember the exact names.

LightSwitch provides an AddTo method for each entity in your data source. To add a new record, you would call the AddTo method that corresponds with your entity and then supply the entity object that you want to add. In this case, the method is called AddToIssues . Finally, you need to call the SaveChanges method to commit your changes to the database. If the data that your user enters fails validation, the SaveChanges method raises an exception, and you can handle this as you wish in the exception handler .

With some extra styling, Figure 15-11 shows how this web page could look. Hopefully, this example shows how simple it is to work with LightSwitch data through the intuitive API.

Reading Data from ASP.NET

You can very easily read and query LightSwitch data from .NET code. To demonstrate this, let’s create a page that displays the issues that are assigned to an engineer. To do so, add a new Web Form to your ASP.NET project and name it ViewIssues.aspx. From the toolbox, drag a grid view control onto your web form. Your markup should look the code that’s shown in Listing 15-3.

Now add the .NET code to the load method of your page, as shown in Listing 15-4.

Just like in the previous example, the first part of this code creates the service reference context. The next line of code selects the issues that are assigned to the user timl . In practice, you would replace the hard-coded timl value with logic that determines the currently logged in user. But the important point of this code is that you can query your data by using the same LINQ query syntax that I showed you in Chapter 10. So, if you want to return just a single issue, you can write code that calls the FirstOrDefault method.

The final piece of code sets the data source of your grid view control and data binds the issues data to the grid. Figure 15-12 shows what your final screen might look like with some extra formatting.

Connecting to LightSwitch from Other Platforms

So far, I’ve showed you how to consume LightSwitch data from a .NET/ASP.NET application. Fortunately, it’s not difficult to connect to LightSwitch data from other platforms or devices. The most important step is to find a compatible OData client.

Table 15-1 shows a list of common platforms and corresponding links to suitable OData clients for those platforms. When you connect to LightSwitch data from a different platform or language, the principles I showed you remain the same. For example, you would still call the same OData methods to access entities and to save your changes.

Summary

LightSwitch supports OData—a data-access protocol you can use to connect to external data sources and to share your LightSwitch data with other applications.

Within your organization, you can use OData to connect to data in recent versions of SharePoint, Microsoft Dynamics CRM, and SQL Server Reporting Services. Externally, there are many other third-party data sources that you can use. In this chapter, I showed you how to connect to a travel-advice service. However, you can find many other data sources you can connect to through the official OData website and the Windows Azure Marketplace.

To connect to an OData data source, you would use the Add Data Source wizard, just as you would for any other external data source. The connection process requires you to enter an endpoint. This is the web address that identifies the OData data source that you want to use. Depending on your data source, you might also need to supply a username and password.

LightSwitch automatically creates service endpoints for each data source in your application. These endpoint addresses are made up of the root URL for your application, followed by the data-source name with a .SVC extension and the name of your entity set. For example, http://WebSite/HelpDesk/ApplicationData.svc/Engineers is the address that returns all engineer records. Because users can access your raw data through a web browser, it’s a good idea to apply access control to your application. Chapters 21 and 22 will show you how to do this.

You can use OData to attach to LightSwitch data from other applications. I showed you how to connect to LightSwitch from within Microsoft Excel and ASP.NET. In Excel, you can connect to OData data sources by using the import feature in the data section of the ribbon bar. In .NET, you can connect to LightSwitch data by adding a WCF Service Reference to an OData endpoint. This provides you with strongly typed access to entities and enables you to query, add, and update records using syntax that is simple and intuitive.