Finding Third-Party Data Sources to Use

A great place to find public data sources is the Ecosystem page on the official OData web site (http://www.odata.org/ecosystem). Here, you’ll find a list of third-party OData providers that includes eBay, Windows Live, and various Government and public bodies.

Another great place is the Azure Data Market (http://datamarket.azure.com/). Here, you’ll find many data sources, some of which are free.

To demonstrate how to consume an OData data source, I’ll show you how to connect to a travel advisory service that’s provided by the UK Foreign and Commonwealth Office. This enables the HelpDesk 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 to a Marketplace service, you can use the web site’s Explore This Dataset option to preview the data that’s offered by the service.

Determining Connection Settings

To connect to an OData data source, you’ll need to know the web address that identifies the data source and any authentication details that the service requires. The Travel Advisory Service displays these details on the Details tab of its web page. The OData address you need is labeled Service Root URL, as shown in Figure 10-1.

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 note the Customer ID and Primary Account Key settings. (See Figure 10-2.) 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, open the Attach Data Source Wizard, and choose the OData option. When the OData settings page appears, enter your OData URL in the Specify The OData Service Endpoint text box. (See Figure 10-3.) In the travel advisory example, the Customer ID is your user name and the Primary Account Key is your password.

By default, LightSwitch checks the ‘Attach To This Data Source As Read-Only’ check box. 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.

Next, the wizard prompts you to select the entities you want to use in your application (as shown in Figure 10-4).

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 10-5 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. We used a data source that comes from the Foreign and Commonwealth Office (FCO). But the FCO might not have implemented all of 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 isn’t supported by your data source, 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 datathat you’ve added 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 slow down your application.

To further help performance, LightSwitch disables eager loading if your screen contains multiple related tables that come from an OData data source. Finally, LightSwitch also turns off 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.

Finding Your Application’s End Point URL

When you deploy your application, LightSwitch creates service end points for each data source in your application. You can access these end points in a web browser by entering the root URL for your application followed by the data source name with an .SVC extension, followed by the name of your entity set. Figure 10-6 shows some example URLs.

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

If you can’t see the raw data as shown in Figure 10-7, you need to adjust your browser settings. In Internet Explorer, you do this by switching off the Turn On Feed Reading View option under Tools Internet Options Content Feeds and Web Sites (Settings).

Querying OData

OData allows you to perform queries by appending query operators to your URL. To return records for the first engineer, you’d use this URL:

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

The OData protocol allows you access related records by using similar syntax. So to return all issues that are assigned to the first engineer, you’d use the 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

Following the previous example, seeing all your data exposed through OData might leave you feeling vulnerable. For instance, any user could easily view all your engineer records by typing this address into their browser:

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

If this concerns you, there sadly isn’t a way to switch off OData end points. However, you can secure your data by using the security features that are built into LightSwitch. The first thing that you should do is to turn on authentication. This prevents someone from accessing the end point without a password or 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 securing your data, refer to Chapters 16 and 17.

Connecting to LightSwitch Data from Excel

Excel 2013 allows you to connect to your LightSwitch data via OData. The big advantage of using Excel is that it allows users to analyze their data by using custom formulas, charts, or PivotTables. To create a connection, select the OData option that you’ll find in the Get External Data group in the ribbon’s Data section. (See Figure 10-8.)

Connecting to LightSwitch Data from .NET

Any application you create that uses .NET 3.5 or above can access your LightSwitch data through WCF (Windows Communication Foundation) Data Services. In this section, you’ll find out how to extend the HelpDesk system by creating an ASP.NET application that allows users to create and view issues.

To create this application, start Visual Studio, create a new ASP.NET Web Forms application, and call 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 10-9.

Enter the URL of your OData end point in the Address field (for example, 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. You can use the service reference (also known as the proxy or service client) to access your LightSwitch entities in .NET code.

File: HelpDeskPortalCreateIssue.aspx
  
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title />
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Label runat="server" Text="Issue Subject"/>
        <asp:TextBox ID="IssueSubject" runat="server"/>                         
        <asp:Label runat="server" Text="Description"/>
        <asp:TextBox ID="IssueDescription" runat="server"/>                     
        <asp:Button Text="Add Issue" runat="server" OnClick="AddIssue_Click" /> 
        <asp:Label ID="ConfirmLabel" runat="server" />
    </div>
    </form>
</body>
</html>

VB:
File: HelpDeskPortalVBCreateIssue.aspx.vb
  
Imports HelpDeskPortalVB.HelpDeskServiceReference
  
Protected Sub AddIssue_Click(sender As Object, e As EventArgs) Handles Button1.Click
  
    Dim srvRef As ApplicationData =
       New ApplicationData(
          New Uri("http://localhost/HelpDesk/ApplicationData.svc/"))            
    'srvRef.Credentials = New Net.NetworkCredential("username", "password")
  
    Dim issue As HelpDeskServiceReference.Issue =
        New HelpDeskServiceReference.Issue()                                    
  
    issue.Subject = IssueSubject.Text                                           
    issue.CreateDateTime = DateTime.Now
    issue.TargetEndDateTime = DateTime.Now.AddDays(3)
    issue.ProblemDescription = IssueDescription.Text
  
    Try
        srvRef.AddToIssues(issue)                                               
        srvRef.SaveChanges()
        ConfirmLabel.Text = "Issue Created"
  
    Catch ex As Exception
        ConfirmLabel.Text = ex.Message                                          
    End Try
  
End Sub
  
C#:
File: HelpDeskPortalCSCreateIssue.aspx.cs
  
using HelpDeskPortalCS.HelpdeskServiceReference;
  
protected void AddIssue_Click(object sender, EventArgs e)
{
  
    ApplicationData srvRef =
       new ApplicationData(
          new Uri("http://localhost/HelpDesk/ApplicationData.svc/"));            
    //srvRef.Credentials =
    //     new System.Net.NetworkCredential("username", "password");
  
    HelpdeskServiceReference.Issue issue =
        new HelpdeskServiceReference.Issue();                                    
  
    issue.Subject = IssueSubject.Text;                                           
    issue.CreateDateTime = DateTime.Now;
    issue.TargetEndDateTime = DateTime.Now.AddDays(3);
    issue.ProblemDescription = IssueDescription.Text ;
  
    try
    {
        srvRef.AddToIssues(issue);                                               
        srvRef.SaveChanges();
        ConfirmLabel.Text = "Issue Created";
    }
    catch (Exception ex)
    {
        ConfirmLabel.Text = ex.Message;                                          
    }
}

File: HelpDeskPortalViewIssues.aspx
  
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title />
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="IssuesGrid" runat="server"/>
    </div>
    </form>
</body>
</html>

VB:
File: HelpDeskPortalVBViewIssues.aspx.vb
  
Imports HelpDeskPortalVB.HelpDeskServiceReference
  
Protected Sub Page_Load
   (ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
  
    Dim srvRef As ApplicationData =
       New ApplicationData(
          New Uri("http://localhost/HelpDesk/ApplicationData.svc/"))          
  
    Dim issues = srvRef.Issues.Where(Function(i) i.User.Username = "timl")    
  
    IssuesGrid.DataSource = issues                                            
    IssuesGrid.DataBind()
  
End Sub
  
C#:
File: HelpDeskPortalCSViewIssues.aspx.cs
  
using HelpDeskPortalCS.HelpDeskServiceReference;
  
protected void Page_Load(object sender, EventArgs e)
{
    ApplicationData srvRef =
        new ApplicationData(
           new Uri("http://localhost/HelpDesk/ApplicationData.svc/"));        
  
    var issues = srvRef.Issues.Where(i => i.User.Username == "timl");         
  
    IssuesGrid.DataSource = issues;                                           
    IssuesGrid.DataBind();
}

Connecting to Use LightSwitch from Other Platforms

So far, you’ve seen how to consume LightSwitch data from a .NET /ASP.NET application. Fortunately, connecting to your LightSwitch data from other platforms or devices isn’t very difficult. The main thing that you’ll need to do is to find an appropriate OData client.

Table 10-1 shows a list of common platforms and links to suitable OData clients for those platforms. When you connect to a LightSwitch application from a different platform or language, the principles that you’ve learned will still remain the same. For example, you’d still use the same OData methods to access entities and to save your changes.