Your Data Everywhere, Part 2: Exposing LightSwitch Data as OData Services to Other Clients
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
In part 1 of this series, I explained how to consume Open Data Protocol (OData) services in your LightSwitch applications. Not only can LightSwitch in Visual Studio 2012 connect to existing OData services; it also can expose data sources as OData services to potentially an infinite number of clients. In this article, I'll show how LightSwitch exposes a data source as an OData service that can be consumed by other applications, using an example based on Microsoft PowerPivot for Excel. Part 3 will conclude this series by examining how to create a Windows Phone app that is capable of working with OData feeds produced by LightSwitch.
Introduction
In part 1 of this series, you learned that the next version of Visual Studio LightSwitch, which you can currently test as part of the Visual Studio 2012 Release Candidate, allows for consuming existing OData services. Actually, the OData support isn't limited to connecting to external data sources, since the LightSwitch infrastructure also automatically exposes all the data sources in the application as OData feeds that can be consumed by other clients—not only LightSwitch clients, but any client that supports OData, such as mobile applications. The really good news is that you don't have to do anything to create OData services from LightSwitch data sources; they're automatically created and exposed by the middle tier.
As you might know, an OData service is represented by a WCF service reachable by pointing to its .Svc file. What LightSwitch basically does is create a public WCF service for each data source, exposing the endpoint of each .Svc file. The intrinsic database, external databases (including SQL Azure), and RIA Services are all data sources that are exposed automatically in the form of OData services. (SharePoint 2010 already comes with an OData endpoint.) Then, when you deploy the application to a three-tier infrastructure such as Internet Information Services or Windows Azure, clients will reference the OData services pointing to the .Svc endpoint.
For example, the OData representation of the intrinsic database can be reached with something like this:
http://myserver/myapplication/ApplicationData.svc
Figure 1 shows how LightSwitch generates OData services from the various data sources, and how they can be consumed from both LightSwitch and other clients. It's an incredible benefit that developers can have OData services from LightSwitch data sources with absolutely no effort.
)
Figure 1 The middle tier exposes OData services endpoints.
Creating a Sample Project
As we go along in this article, you'll see how both the intrinsic database and an external database are exposed as OData services. The external data source I used for this example is the Northwind sample database for SQL Server, but the same concepts apply to SQL Azure databases and WCF RIA Services.
I assume that you already have some experience with creating a LightSwitch project and some entities and tables. To get started with this example, create a new LightSwitch application in Visual Studio 2012. Regardless of the programming language you use, call your project ExposingOData. When ready, create two new entities called Customer (Figure 2 shows the design you should use) and Order (as shown in Figure 3).
)
Figure 2 Design of the Customer entity.
)
Figure 3 Design of the Order entity.
Both entities offer a simplified representation of business data. We don't need complex entities and tables for this example, since our focus is on how the data source is exposed as an OData service. After creating both entities, establish a one-to-many relationship between the Customers and Orders tables that LightSwitch generated based on the new entities, as shown in Figure 4.
)
Figure 4 Specifying a one-to-many relationship between Customers and Orders.
The next part of the process is connecting to the Northwind database. Follow these steps:
- In Solution Explorer, right-click Data Sources and select Add Data Source.
- When the Add Data Source Wizard starts, select Database.
- Select the Northwind database via the Connection Properties dialog.
- Select the Shippers table and complete the wizard.
Visual Studio 2012 gives LightSwitch developers the opportunity of publishing either the data with a client application, or only the services that it generates. If you're using LightSwitch just to create and expose data sources as OData, you're not obliged to add the user interface. In this particular example, it's a good idea to add some screens to provide a clearer demonstration of how multiple clients (LightSwitch client and other clients) can consume OData feeds. At this point, you have two data sources in your application. Feel free to add screens to the application as you like; I suggest adding just one screen so that it's easier to demonstrate how the LightSwitch client is actually connecting to data.
Before testing how OData services have been implemented, you need to switch to a web application type in the project's properties. This is required so that you'll have the opportunity to type the service URL in the browser's address bar, but you can certainly create a desktop client that's capable of exposing OData services, if you prefer. If you run the application from Visual Studio 2012, notice that the URL contains the server name followed by the port number and then the application's main page, plus a value indicating that the application has been launched from Visual Studio. The URL takes the following form:
http://localhost:PortNumber/default.htm?IsLaunchedByVS=True&AuthenticationType=None
where PortNumber represents the port on the development web server used for debugging. Notice that the AuthenticationType value varies depending on whether you chose Windows or Forms authentication, rather than the anonymous None. Now, replace the URL with the following:
http://localhost:PortNumber/ApplicationData.svc
Of course the port number must be left unchanged. At this point, your web browser should show the OData representation of the intrinsic database, as demonstrated in Figure 5.
)
Figure 5 Testing the OData service representation of the intrinsic database.
You now have a fully-featured OData service that supports all the REST-ful APIs that you can use for querying and updating data. For example, you can query the full list of items from a given table by adding the name of the table to the URL like this:
http://localhost:PortNumber/ApplicationData.svc/Customers
Figure 6 shows a sample query result.
)
Figure 6 Querying a table from the OData service.
By now, you're probably beginning to understand how powerful this feature is. Any external client can consume your LightSwitch data—desktop clients, web clients, and even mobile clients. You can also test the OData service for the Northwind database, reachable via the following URL:
http://localhost:PortNumber/NorthwindData.svc
Keep these facts in mind:
- [lb] LightSwitch generates the name of the service based on the name of the data source, which is visible in Solution Explorer, followed by the .Svc extension. For the intrinsic database, the name of the service is always ApplicationData.svc.
- [lb] The service name is case sensitive, so you pay attention to typing the service URL correctly. ApplicationData.svc will work, Applicationdata.svc won't.
- [lb] Case sensitivity also applies to table names, so CUSTOMERS is not the same as Customers.
So far, we've seen LightSwitch generate one OData service for each data source, both intrinsic and external. However, we've only looked at how to reach services at debugging time, using the local development server. This is certainly important, but more likely you'll want to expose such services through the network from a web server. To do this, you need to deploy the LightSwitch application to a three-tier environment. Let's look at that option next.
Deploying to a Three-Tier Web Server
Deploying LightSwitch OData services is incredibly easy, since you simply have to publish the application in the usual way, and the setup procedure automatically deploys services and exposes endpoints for you. Also, Visual Studio 2012 provides an option that allows you to publish the application as if it were a web service, with no client. This is useful if you only want to expose data sources without the need for a LightSwitch client.
We've looked at how to deploy LightSwitch services to the local web server on the development machine. This is a good simulation, since you know that there's basically no difference between that and publishing to a production web server, except that such a server must be configured with the LightSwitch server prerequisites and must be capable of accepting incoming connections from Visual Studio. Before publishing, change the configuration from Debug to Release, and then select Build > Publish. When the LightSwitch Publish Application Wizard starts, follow these steps:
- On the Client Configuration screen, select the Web option. As I mentioned earlier, we want to publish a LightSwitch client as well in this example, so this is the appropriate option. Select Service Only if you want to deploy only the OData services, without a LightSwitch client application.
- On the Application Server Configuration screen, make sure that IIS is selected.
- On the Publish Output screen, select the option "Remotely publish to a server now" and specify the server's URL. Make sure that the name of the application has been entered correctly in the Site/Application text box. Figure 7 shows an example of how the screen should look.
- On the Database Connections screen, in the first text box specify the SQL credentials that will be used for reading and writing data. In the second text box, specify any administrator credentials that are required for publishing the database schema and generating the database on the server. Figure 8 shows an example.
- Make sure that appropriate credentials are specified in the Other Connections screen for the Northwind database.
- When you're ready, click the Publish button.
)
Figure 7 Setting the publish output options.
)
Figure 8 Setting SQL Server credentials for creating and accessing the database.
To make sure that both the application and the OData services have been published correctly, type this to launch the application (or specify the server name instead of localhost):
http://localhost/ExposingOData
Then type this:
http://localhost/ExposingOData/ApplicationData.svc
At this point, we can test services with a different client: Microsoft PowerPivot for Excel.
Consuming LightSwitch OData Services from Different Clients
A good example for understanding the benefits of exposing OData services from LightSwitch is using PowerPivot, a famous add-in from Microsoft for Excel 2010. PowerPivot is basically an add-in that allows for consuming data sources that Excel doesn't support out of the box, including OData feeds, to create reports and perform data analysis inside Excel.
- Start Excel 2010 and select the PowerPivot tab.
- Click the PowerPivot Window button to launch the add-in.
- When the window is active, click the From Data Feeds button. This feature allows you to specify an OData service as the data source for the new report.
- Specify the service URL and a friendly name for the connection in the Table Import Wizard (see Figure 9). Click Next.
- In the next dialog, specify the table(s) to be imported from the data service. Figure 10 shows the selection of both tables from the OData service that exposes the LightSwitch intrinsic database.
- Once the import is completed, the PowerPivot window shows a sheet for each table, and lists data if the table contains any. You can use the PivotTable drop-down on the Ribbon to generate charts that will be sent to Excel and proceed to data analysis. You can also manage relationships between tables that PowerPivot can discover from the OData feed. (This is why we added a relationship between Customers and Orders earlier in this article.) Figure 11 shows some imported tables and the options available on the PivotTable drop-down.
)
Figure 9 Specifying the connection information for the OData service.
)
Figure 10 Selecting tables from the data service.
)
Figure 11 Tables have been imported, and users can now interact with Excel.
Working with PowerPivot is just one example of how an external client can interact with LightSwitch data through OData services, but it should open up your mind to other exciting possibilities. In part 3 of this series, we'll consider another interesting (and more specific) example based on a mobile client, which is a very important scenario these days.
Authentication Hints
OData services support authentication, so that only authenticated users will be able to access data feeds. In LightSwitch, you can secure your application by specifying Windows authentication and Forms authentication, in addition to using anonymous authentication (as in the previous example). When you publish a LightSwitch application, the selection that you made in the LightSwitch Access Control will also affect the generated data services; for example, if you decide to publish a LightSwitch application requiring Forms authentication, accessing the generated OData services will also require supplying a username and password. You can test this scenario by deploying the previous example with Forms authentication enabled and then by attempting to view the ApplicationData.svc file in the web browser; at this point, you'll be prompted to enter your credentials. Any client you use to access OData feeds exposed by LightSwitch under Access Control will need to enter the required credentials.
Conclusion
Full support for OData in the latest version of LightSwitch offers an incredible number of new business opportunities, because external applications different from one another can all take advantage of what you've developed inside LightSwitch. Consuming existing OData feeds and exporting LightSwitch data sources as OData services is available out of the box, and no effort is needed from the developer. Even still in beta, the Visual Studio LightSwitch 2012 looks very promising for making it easier to interact with other applications and data sets.