Skip to main content

Lightning Connect and SQL Integration Real Time Using Windows Azure Cloud Services-Part 2

This is a continuation from blog 1 where we had set up sample SQL server on the cloud using windows azure platform .

This article we will be doing below things 

1)Writing a Visual C# program to expose the SQL table and data as a Odata webservice(Using WCF template) 
2)Deploy the code on windows azure website
3)Accessing the SQL data using lightning connect using azure website URL

You will need below things as a prerequisite 

1)Install Visual studio from the here
2)Open online windows azure management portal that you had set up if you followed last article
3)Salesforce developer instance 


1)Create an OData Web Service

You now have a SQL Database in Windows Azure with tables and data, but now you need a simple and unified way to query and manage that data from all of the applications that you will build. Web services provide an abstraction over your data storage technology and with the broad adoption of web technologies and HTTP, RESTful web services are accessible from pretty much every platform and device. As a result, in this section you will create an OData web service using ASP.NET, an ADO.NET Entity Framework Data Model and a WCF Data Service.


To begin, open Visual Studio.
From the File menu, hover over New and select Project.
In the New Project dialog, browse to Installed > Templates > Visual C# and select the ASP.NET Web Application project template.
select the ASP.NET Web Application project template

Enter WebApplication5 for the Project Name.
Enter WebApplication5  for the Solution name.
Click the OK button to continue creating your new Visual Studio project and solution. You will now be presented with the New ASP.NET Project dialog. For this OData project select the Empty ASP.NET template and click OK to continue.

Click the OK button
Your new Visual Studio solution and project should now be displayed in the Visual Studio Solution Explorer.
In the Solution Explorer, right click on the WebApplication5 project and select Add > New Item…
Browse to Installed > Visual C# > Data and select ADO.NET Entity Data Model.
Enter ShoppingListModel in the name for ADO.NET Entity Data Model and click Add.
You will now be presented with the Entity Data Model Wizard. In the Entity Data Model Wizard, select Generate from database and click Next.
select Generate from database
On the Choose Your Data Connection screen of the Entity Data Model Wizard, click on the New Connection button to display the Connection Properties dialog and configure the connection to your Windows Azure SQL Database.
 click on the New Connection button

In your web browser, navigate to the Windows Azure Management Portal at http://manage.windowsazure.comand browse to your MyShoppingList SQL Database home screen.
On this screen you will see your SQL Database server name at the bottom. Select your server name and copy it to your clipboard. Your SQL Database server name will look like yourserver.database.windows.net,1433.

















Switch back to the Connection Properties dialog in the Visual Studio Entity Data Model Wizard and paste your SQL Database server name into the Server name text box.
Select Use SQL Server Authentication and enter your SQL Database User name and Password.
Expand the Select or enter a database name drop down and select your MyShoppingList database.
Click the OK button to finish configuring your SQL Database connection and return to the Entity Data Model Wizard.
Connection Properties
Select the Yes, include the sensitive data in the connection string option and name your entity connectionShoppingListEntities in the text box at the bottom.
Entity Data Model Wizard

Click the Next button to advance to the Choose Your Database Objects and Settings screen.
In the Choose Your Database Objects and Settings screen, check the Tables check box to include all of the tables in the data model.
Enter ShoppingListModel in the Model Namespace text box and click the Finish button.





Now that you have added an ADO.NET Entity Data Model to your Visual Studio project, you need to add a WCF Data Service.
Right click again on the WebApplication5  project in the Solution Explorer and select Add > New Item…
In the Add New Item dialog, browse to Installed > Visual C# > Web and select WCF Data Service 5.6. Name the new WCF Data Service ShoppingListService and click Add.

Add New Item
WCF Data Services make it easy to create an OData service using an ADO.NET Entity Data Model by providing a generic DataService class that is constructed using an ADO.NET Entity Data Model.
Open the ShoppingListService.svc and locate the first TODO comment in the ShoppingListService class definition.

 locate the first TODO comment in the ShoppingListService class definition
Change /* TODO: put your data source class name here */ to ShoppingListEntitiesThis change will setup your OData service. The only remaining thing to do is grant access to the entities exposed in the OData service.
Locate and uncomment the following line of code in the ShoppingListService.InitializeService method and change MyEntityset to *. This will grant read permissions to all entities in your OData service.
  1. // config.SetEntitySetAccessRule("MyEntityset", EntitySetRights.AllRead);
grant read permissions to all entities in your OData service
Now you can test your OData service. Right click on ShoppingListService.svc in the Visual Studio Solution Explorer and select View in Browser.
You should now see the following AtomPub feed in your browser. This is the metadata listing the entities that are available in your OData service using the AtomPub web standard.
AtomPub feed
Click for larger image
Your OData service is now ready to be published to Windows Azure!

2)Publishing Your OData Service to Windows Azure

In your browser, navigate to the Windows Azure Management Portal and click on Web Sites in the list of services on the left-hand side.
Click the New button in the toolbar at the bottom. In the New menu that is displayed, select Compute > Web Site > Quick Create.
Enter a URL for your OData service in the URL text box. You should choose a URL that is relevant and descriptive. Since you are creating a web site for your OData service, you might want to append Api or Serviceto the URL.
Click the Create Web Site checkmark to create your website.
Click the Create Web Site checkmark
Note: To avoid bandwidth charges and added latency for the transfer between data centers, it would be a good practice to create your web site in the same region as your SQL Database.
After your website has been successfully created, select your web site and download the publishing profile by clicking on the Download the publish profile link.
Download the publish profile link
After your publish profile is downloaded, switch back to Visual Studio and right click on yourCodeGuru.eSeminar.OData project and select Publish…
The Publish Web wizard will be displayed and you can begin publishing your OData service to Windows Azure.
On the Profile screen of the Publish Web wizard, click the Import… button to display the Import Publish Profiledialog.
On the Profile screen of the Publish Web wizard, click the Import… button
On the  Import Publish Profile dialog, select the Import from a publish profile file option and click the Browse button. Browse  to the Publish Profile you downloaded from your Web Site in the Windows Azure Management Portal, select it and click OK to return to the Publish Web wizard.
Import from a publish profile file
You will notice that the Publish Web wizard has populated all of the Web Deploy configuration using the Publish Profile file that you downloaded from the Windows Azure Management Portal. Click the Publish button and your OData service will be published to your Windows Azure Web Site.
Click the Publish button
When your Publish has successfully completed, browse to your OData service at http://YourODataSite.azurewebsites.net/ShoppingListService.svc to verify that your service is accessible and returning the AtomPub feed that with your entities.

Below is the code sample in C# that generated the Odata webservice 


3)Configuring Lightning Connect to pull data into salesforce

I wont be elaborating much as it is highlighted on how to configure in below blog
Let me put a simple Video demoing this real time data sync




Popular posts from this blog

TLS 1.0 has been disabled For Sandbox - Salesforce

Salesforce has finally disabled TLS 1.0 in sandboxes .This is in preparation for disablement later in 2017 for PRODUCTION instances .

Now this would have not impacted your integrations in PRODUCTION org but if your integration is broken in your sandbox ,this means you have only few months to sort this and fix before this affects your integration for PROD live users .
Before we deep drive on how to possibly fix this and work with your external systems to figure solution ,lets first understand what is TLS and why did SFDC moved to 1.x and had to disable TLS 1.0
TLS 1.0 Explained























TLS 1.1 Improvements 
Added protection against cipher-block chaining (CBC) attacks. Support for IANA registration of parameters.

Clearly TLS 1.1 is more secure(Compared to 1.0) and protects salesforce resources against CBC attacks .
Identify if this change broke anything .The things that can be affected in your instances are as below Web requests to Salesforce URLs that require authenticationWeb requests to the login pag…

Opening Modal Using Lightning Component Framework of SFDC

One of my friend from India threw a challenge .The challenge was to open a modal by using latest and greatest lightning components framework and modals design from SLDS .For the love of community I thought of sharing the entire code base that I did .

So here we start ..

Business Use Case - Need a handy SalesLeader board component that can be used to display the Sales revenue generated by each sales rep for current year in the order of decreasing total revenue .On click of the tile ,we will show detail opportunity list aggregating the revenue .

The component can be dragged in lightning design experience or in App builder lightning Page .

Video Demonstration-



SalesLeaderBoard from Mohith Kumar Shrivastava on Vimeo.








Frameworks Used -
Lightning Design Systems (SLDS) for CSSLightning Component Framework for client side logicApex aura enabled class for backend logicApproach
The component hierarchy is very important to imagine or mindmap before we dig deeper- SalesLeaderMain
    -SalesLeaderBoard
    …

Customizing Napili Template Of SFDC For Communities - Community Cloud

Recently I have been busy working with salesforce community cloud platform and I am impressed by the new added features of Spring 16 .

You can watch the Release Readiness webinar for more details on what are added features for community cloud 

Lets talk about something which most of client (Either SI or ISV ) would love to have with the Napili template .The first question that clients ask with Napili is its flexibility to customize and tailor to their specific needs .

The very straight forward questions that people familiar with Napili template ask are as follows 

Can I change the CSS and look and feel ?  

Well yes and No ....The new branding editor you can sparingly use CSS and change look and   feel   and community editor can also help you to some extent to change labels and the color and you can really get closer to what you want .Just note that efforts will be high if we want to change the entire template design.

Will i be able to populate data from custom objects and standard objects a…