Guest Post: Sara Morgan Nettles is an independent Software Developer and long-time .NET programmer who has recently been wooed into the world of Force.com development. She is a certified Force.com Advanced Developer and recently filmed her first introductory course covering Visualforce for lynda.com. Her blog is www.saramorgan.net.
Lightning Connect truly is a game changer when it comes to easily integrating external data with Salesforce. In this article I am going to show you how simple it is to access real-time data from a SQL Server Azure database and display it seamlessly in Salesforce using OData and Lightning Connect. This will involve creating a WCF Data Service that exposes data from a SQL Server Azure database as OData. This OData service will be published to the Windows Azure platform where we can access it using Lightning Connect and display a real-time feed of data through a custom tab in Salesforce. To demonstrate this, I will walk you through the following steps:
- Create a Proof of Concept SQL Server Azure database named PetSupplies on the Windows Azure platform.
- Create an ASP.NET Web Application that renders the SQL Azure data as read-only OData using a WCF data service and the Entity Framework Data Model.
- Publish the ASP.NET Web Application to the Windows Azure platform.
- Use Lightning Connect to create an external data source that accesses the published OData service.
- Create a Custom Object Tab in Salesforce that displays the PetSupplies data from the SQL Server Azure database just like data from any other custom object.
Are you excited? You should be. This really is some cool stuff. And the best part is that most of the work is point and click. There is an amazingly small amount of code changes involved.
Create a SQL Server Azure Database
In order to follow along with this tutorial and create your own SQL Server Azure database, you’ll need access to both Salesforce and an active Microsoft Azure account. If you do not already have one, follow these instructions. You can also get a free Force.com Developer Edition account by going to https://developer.salesforce.com/signup.
For this tutorial, I suggest you select a Basic service tier and leave the other settings as their default. You will also need to select New SQL database server from the server drop down box.Once your account has been created, you will need to login to the Portal and create a new database. You do this by going to the SQL Databases tab and clicking the big plus sign next to the word New in the bottom right-hand corner of the Portal. From there click Custom Create and enter a Database Name (see Figure 1), along with some other database settings.
The next step will be to specify the SQL database server settings (see Figure 2). Enter a login name and password which meet the requirements and be sure to pick a region that is geographically as close to you as possible.
It will take a few minutes for the server and database to be created. When it is complete, you should see a status of online next to the SQL database. Click on the database name and notice the unique server name and port assignment listed at the bottom of the quick start page (see Figure 3). You will need this server name later on in the tutorial, so for now write it down or copy the value to the clipboard.
At this point, you have a SQL Server Azure database named PetSupplies, but it has no schema and no data. You can use Visual Studio 2013 to add the schema and data, but to do that you must first add your local IP Address to the Windows Azure firewall rules. This can be done by clicking the Set up Windows Azure firewall rules for this IP address link on the quick start page (Figure 3). Keep in mind that if you have a network that assigns your IP address dynamically, this IP address may change. This means that the IP address you add to the firewall rules today might not be the same one assigned to you tomorrow and you will have to go back and click this link in order to access the database locally on another day.
Although you can use SQL Server Management Studio to access your SQL Azure database, I am going to walk you through accessing your database using the Microsoft SQL Server Data Tools available with Visual Studio 2013. You will first need to have Visual Studio 2013 with Update 4 installed locally on your machine. If you do not already have a copy of Visual Studio, download and install a free version of the Community version from the following link.
Once Visual Studio is installed, access your SQL Server Azure database by clicking the Open in Visual Studio link in the bottom menu bar of the quick start page (Figure 3). This will launch Visual Studio and prompt you to connect to your SQL Server Azure database using the login name you specified earlier (see Figure 4).
For this tutorial, I will use a very simple database structure that consists of a single table named Supplies. The easiest way to create the table and populate it with data is to use a SQL Server script. You can do this by expanding the server node in SQL Server Object Explorer, right-click on the PetSupplies database and select New Query. This will open a window where you can paste in the following script and click SQL | Execute to create the Supplies table and populate it with 5 rows of sample data.
Create an ASP.NET Web Application
OData or Open Data Protocol is a web-based standard protocol that was initially defined by Microsoft, but is now actively developed by OASIS. It applies RESTful principles that enable the consumption of data across the web using simple HTTP messages. Exposing the SQL Server Azure data involves creating an ASP.NET web application that uses a WCF (Windows Communication Foundation) data service and an Entity Framework Data Model to render the SQL Server data as OData.
The Entity Framework is an ORM (Object Relational Mapper) that essentially just makes the task of how to get data in and out of a database much easier. It is Microsoft’s recommended data access technology for new applications and in this tutorial, I will have you utilize the latest version, 6.1.3.
To get started, open Visual Studio 2013 and click File | New | Project. Select a Visual C# ASP.NET Web Application as the template and specify a name for your project. Click OK to continue. On the next page (see Figure 5), select Empty as the template and OK to create your web application. You do not need to select the Host in the Cloud checkbox, as we will be moving this project to the cloud in a later step.
Add the Entity Data Model
The first thing to do is add the Entity Data Model to our project. Do this by right-clicking the PetSuppliesService project in Solution Explorer and selecting Add | New Item. Select Data and ADO.NET Entity Data Model as the project item and name it PetSuppliesModel (see Figure 6).
The Entity Data Model Wizard will ask you what the model should contain. Select EF Designer from Database since we are building our model from an existing database. You will then be asked to choose your data connection. Click New Connection to bring up the Connection Properties dialog (see Figure 7).
The server name should be the string value you copied earlier from the quick start page on the Windows Azure portal. Select Use SQL Server Authentication and enter the secure login name and password that you specified when you created the SQL Server Azure database. You should also select PetSupplies as the database and click OK to continue.
When you return to the Choose your data connection page, select the “Yes, include the sensitive data in the connection string” radio button (see Figure 8). You should also enter PetSuppliesEntities as the model name and click Next to continue.
From the Choose Your Version page, leave the default selection of Entity Framework 6.x and click Next to continue. In the final page of the Entity Data Model Wizard, select the checkbox next to Tables and specify PetSuppliesModel as the Model Namespace (see Figure 9). Click Finish to complete the wizard and generate the Entity Model code. If you receive a Security Warning dialog, click OK to continue.
Because we are referencing the latest version of the Entity Framework, along with a WCF Data Service, we will have to install the WCF Data Services Entity Framework Provider. But before this can be done, you will need to update the Visual Studio project to use the latest version of the Entity Framework. This can be done by clicking Tools | NuGet Package Manager | Manage NuGet Packages for Solution. From the Manage NuGet Packages dialog, click Updates and you should see an entry for EntityFramework (see Figure 10).
Click the Update button to start the update. In order to uninstall the prior version of Entity Framework, it will be necessary to restart Visual Studio and save the current solution. You should be promoted to do so at the bottom of the dialog when the update is complete. Go ahead and click the Restart Now button to initiate the restart and click OK if promoted to save the solution.
Once the Entity Framework has been updated, you can return to the NuGet Package manager and expand the online tab. Make sure “Include Prerelease” is selected in the top dropdown box (see Figure 11). Search for “WCF Data Services Entity” and click the Install button next to the entry for WCF Data Services EntityFramework Provider. Click OK to install the package into the PetSuppliesService. You should also be prompted to accept the license agreement. When you are done, click Close to return to your Visual Studio project.
Add the WCF Data Service
The Entity Framework model provides a wrapper for the data, but you need to add a WCF data service to expose that data as OData across the web. This is done by right-clicking the project file in Solution Explorer and clicking Add | New Item. Expand the Web node and select WCF Data Service 5.6 as the item template to add (see Figure 12).
Name the service PetSuppliesData.svc and click Add to add it to your project. Doing this will generate template code and the comments will indicate where to replace code for TODO items (see Figure 13).
The first thing to do is add your data source class name, which if you followed my instructions earlier when naming the service will be PetSuppliesEntities. You will also need to change the type declaration for the class from DataService to EntityFrameworkDataService and then add a using reference to the System.Data.Services.Providers namespace.
The next to do item involves uncommenting the line that sets the SetEntitySetAccessRule method on the DataServiceConfiguration object. You will need to replace the word “MyEntityset” with a “*” to indicate that it should grant read permissions to all entities in your OData service.
And finally, you should add a line of code before the class definition that modifies the service behavior and enables server logs to be generated. This will help you quickly diagnose any problems that may arise by giving you back exception details in any error messages you may receive. When all the code changes are complete, your code should look like the following:
To test that everything is working as it should, save all the changes by going to File |Save All. You will then right-click the PetSuppliesData.svc file in Solution Explorer and select View in Browser (see Figure 14).
A browser window should open up and call up an instance of the data service on your localhost machine. If successful, a small XML file should appear which looks like the following:
If this is what you see, you are now ready to publish your OData data service to the Windows Azure cloud.
Publish the ASP.NET Web Application to Windows Azure
For this next step, you will need to go back to the Windows Azure Management Portal and login. Click on the Websites icon in the left menu bar and then click the New button in the toolbar at the bottom of the page. From the New menu, select Quick Create (see Figure 15) and enter a unique name for the URL that will be used for your particular hosted website.
It may take a few minutes for the website to be created, but when it is done, click on the site to bring up the Quick Start page (see Figure 16). From there, click the Download the publish profile link and once the file is downloaded, return to Visual Studio and the PetSuppliesService project.
In Visual Studio 2013, right-click the project and select Publish to start the Publish Web Wizard. Make sure that a web deploy profile is selected in the drop down box (see Figure 17) and click Import.
Browse to the location on your local machine where you downloaded the publish profile and click Open and OK to begin the Import. If prompted to save your changes, click Yes.
On the connection page (see Figure 18) click Validate Connection and make sure it comes back with a green checkmark indicating that the test was successful. Click Publish to begin the process.
It should take several minutes for the publish to complete. Open a web browser and browse to the following location to verify that the website was published successfully.
http://<your website name>.azurewebsites.net/PetSuppliesData.svc
If successful, you should see the same XML that was displayed when you tested the data service locally.
Create a Lightning Connect external data source in Salesforce
Now, comes the really easy part – creating the external data source in Salesforce. Go to Setup | Develop | External Data Sources. Click New External Data Source and enter PetSupplies as the label and name (see Figure 19).
Select Lightning Connect: OData 2.0 as the type and enter the publish URL from the last step. Click Save and on the next page click Validate and Sync. If successful, the page should come back with a status of success. It should also display the single table name Supplies with a checkbox next to it. Select the checkbox and click the Sync button to start the Sync (see Figure 20).
Create a custom object tab to access the published data
In this step, we will create a custom tab that is linked to the new external data object. This tab will display the data from the SQL Server Azure database as if it were any other custom object in Salesforce. In your Salesforce development org, click Setup | Create | Tabs and click New Custom Object Tab.
Because we already validated and synced the external object, you should see the Supplies table appear in the list of objects. Select Supplies and a tab Style (See Figure 21) and click Next to continue. On the next page, click Next to accept the default profile assignments. Finally, click Save to create your new custom tab. If you click on the Supplies custom tab and click Go!, you will see a list of what appears to be 5 records (see Figure 22).
And there you have it – real-time access to external data, hosted on a SQL Server Azure server, seamlessly available within Salesforce. The data in the SQL Server Azure database can change and those changes will be immediately reflected in the new custom tab. That is the magic of Lightning!