NOTE: This information applies to LightSwitch in Visual Studio 2012.
In the next version of LightSwitch, we’ve added support for OData services, both consuming external services as well as producing services from the LightSwitch middle-tier. The Open Data Protocol (OData) standardizes the way we communicate with data services over the web. Many enterprises today use OData as a means to exchange data between systems, partners, as well as provide an easy access into their data stores. So it makes perfect sense that LightSwitch, which centers around data, also should work with data services via OData. Since OData is a standard protocol, it also means that other clients can access the data you create through LightSwitch.
In my last post on OData in LightSwitch I showed you how we could use external OData services to enhance our LightSwitch applications. In this post I’ll show you how to consume OData services that LightSwitch exposes.
Creating OData Services with LightSwitch
Creating OData services in LightSwitch doesn’t take any additional skills than you had before. In fact, you don’t have to do anything special to create these services. They are automatically created when you define your data and compile your application. Each data source you use in your LightSwitch application becomes a data service endpoint. Within those endpoints, each entity you define in LightSwitch is exposed automatically. Instead of the “black box” middle-tier we had in the first version of LightSwitch, we now have an open middle-tier that can be used to interface with other systems and clients.
What’s really compelling here is not only can you model data services easily with LightSwitch, but any business logic (and user permissions) that you have written on your entities will execute as well, no matter what client is accessing the services. And since OData is an open protocol there are a multitude of client libraries available for creating all sorts of applications on a variety of platforms, from web, phone, etc.
Let’s dig into the service endpoints a little more and see what they look like. For this example I’m going to use the Contso Construction sample.
Getting to Your LightSwitch OData Services
When you deploy your LightSwitch application in a three-tier configuration (either hosting the middle-tier in IIS or Windows Azure) then the service endpoints are exposed. The name of the services correspond to the name of your data sources. In the Contoso Construction sample, we have two service endpoints because we have two data sources. Within each of the services, we can navigate to all the entity sets we model in the data designer.
There are a couple ways you can get to the services when debugging. The easiest thing to do is to change the client application type in the project properties to “Web”. When you debug the app (F5) you will see the port number Visual Studio has assigned you in the address bar of your browser.
While debugging, open another tab in the browser and navigate to your OData service using the port number. You will see the list of entity sets available to query through the service.
The Open Data Protocol is a REST-ful protocol based on AtomPub and defines a set of query operations that can be performed on the data using a set of URI conventions. You query the service with an HTTP-GET request and the service will return to you a feed with the results in the response. To see the raw feed in IE, go to Tools –> Internet Options, Content tab. Under Feeds & Web Slices click “Settings” then uncheck “Turn on feed reading view”.
If we want to see all the customers in the system we can simply type the URL http://localhost:41155/ApplicationData.svc/Customers and you will get the following response from the service. Each entry in the feed is a customer entity which corresponds to a row in the database.
Keep in mind that the queries are case sensitive. Notice it’s Customers not customers in the URL. If you want to return a customer who’s ID = 1 then use:
which would return only the first customer shown above. Similarly, the OData protocol defines a standard way of navigating relationships via navigation properties. If you want to get all the construction projects for a particular customer use:
which would return just that customer’s projects. If you want to return only Customers who’s last name is “Massi” then use:
http://localhost:41155/ApplicationData.svc/Customers?$filter=LastName eq ‘Massi’
Of course there are a whole slew of other query operations supported like OrderBy, Top, Skip, Sort, etc. Take a look at the Open Data Protocol URI conventions for a full list. There are also operations defined in the protocol for performing updates, inserts and deletes using standard HTTP verbs. The LightSwitch client communicates with the middle-tier data services this way.
Another way to inspect the service requests and responses is to install a tool like Fiddler. Fiddler is a Web Debugging Proxy which logs all http(s) traffic between your computer and the Internet or localhost. This way you don’t have to change the client application type, you can leave it as a desktop application and still see the traffic. If you’re trying to build your own clients against OData services this is a must-have tool.
Now that you understand how OData services are exposed from the LightSwitch middle-tier, let’s move on and consume some of our data from another client outside the LightSwitch client. One of my favorite tools for analyzing data is Excel.
Consuming LightSwitch Services in Excel using PowerPivot
You don’t actually need to know anything about OData to consume these services in Excel. Excel has a free add-in aimed at power users called PowerPivot that you can install into Excel 2010 to get access to all sorts of data sources that Excel doesn’t support out of the box, including OData feeds. Download it at www.powerpivot.com. You can arm your power users with this add-in and point them to your LightSwitch data services to perform complex data analysis and create reports all in a familiar tool.
To connect Excel to an OData service, click on the PowerPivot tab and then click the “PowerPivot Window” button to launch PowerPivot. Click the “From Data Feeds” button and supply the Data Feed URL, then click next.
At this point you can select from a list of data sets.
Select the data you want and then click finish. PowerPivot will import the data you select into a set of spreadsheets. You can keep importing additional feeds or data sources into PowerPivot and then relate them together to create powerful data mashups. To create relationships between your data sets select the Design tab on the PowerPivot window and click Manage Relationships.
Once you set up the relationships you need, you can create pivot tables and charts by selecting the Home tab and dropping down the PivotTable button.
You will then see the PowerPivot Field List window that you can use to create charts like normal. Keep in mind that if you are not working against LightSwitch data services that are deployed but are instead trying this while debugging, you may need to update the port number to your data service. You can do this in the PowerPivot window by selecting the Design tab and then clicking “Existing Connections”. Then you can update the port number and refresh the data back on the Home tab.
The updated Contoso Construction sample contains a spreadsheet in the root folder called ContsoAnalysisPowerPivot.xlsx that has a variety of charts you can play with.
I hope you are now starting to realize how powerful LightSwitch can be not only to create business applications, but also data services. Being able to consume as well as expose LightSwitch data sources as OData services opens the door for a wide variety of client applications accessing your data through your business rules. In the next few weeks the team and I will take you on some more adventures with OData and show you some of the numerous possibilities you now have. Of course, we’ll also drill into other new features as well. Stay tuned!