Skip to main content

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

Back Office Real Time Integration is need for any Enterprise Applications residing on salesforce platform .One of Key challenges of Salesforce Enterprise customers have been how to show data in salesforce 1 platform from back office or third party systems  without actually storing or consuming the storage space .

Salesforce External Object and Lightning Connect solves this .If you are not so familiar with External Objects ,I would recommend below links for further understanding

All of the above blog links are really good and they explain Odata in particular .Lets take some time now in exploring whats Odata


The Open Data Protocol (OData) is a data access protocol built on core protocols like HTTP and commonly accepted methodologies like REST for the web.

Standardization of OData has been going on for years (they are working on version  4), but it has suffered from a bit of a chicken-egg problem. Applications haven’t put a large priority on supporting the consumption of OData because there haven’t been enough OData providers, and data providers haven’t prioritized making their data available through OData because there haven't been enough consumers.

With the above theory and concepts now we are set to use windows azure cloud services to set up a SQL server and expose data and metadata in SQL as Odata service .we will use salesforce lightning connect to connect to this web service and see the data in SFDC .We will create Tab in SFDC and view the data .Sounds Interesting and exciting as well .Lets carry on !!!

Creating a SQL table and adding data using windows azure cloud services

I started my career with salesforce and cloud so I love to be working with only cloud platforms .windows azure is a microsoft cloud platform.Azure is the only major cloud platform ranked by Gartner as an industry leader for both infrastructure-as-a-service (IaaS) and platform-as-a-service (PaaS). This powerful combination of managed and unmanaged services lets you build, deploy, and manage applications any way you like for unmatched productivity.

I signed up for free trial with the help of below link

If you don't have windows account ,you will need to create one to complete the sign up.Please note it does ask for credit card info but you are not charged any amount from that.

Once you sign up the windows azure you will be able to explore and create various services .Below picture shows management portal of azure platform

The New button exists throughout the Windows Azure Management Portal and will enable you to provision new services within your Windows Azure subscription. This button is also context aware, so if your focus is currently within the SQL Database service, clicking this button will default the selections for creating a new Windows Azure SQL Database.
Click on the New button. You should now be presented with a menu to create a new Windows Azure SQL Database. Notice that the menu has a list of the Windows Azure service categories on the left, followed by the services within the selected category, and options for creating your new service in the third column. ClickCustom Create to begin setting up your Windows Azure SQL Database.
Click Custom Create
You should now see the New SQL Database – Custom Create wizard.
New SQL Database – Custom Create wizard
In the Name text box enter Websites.

In the Subscription drop down, select the Windows Azure subscription where you would like to create your SQL Database.

1)In the Server drop down, select the Windows Azure SQL Database server that you would like to use for your new database. If you have created a Windows Azure SQL Database server previously, you can select that server, otherwise, select New SQL database server.
2)The remaining fields you can leave set to their defaults.
3)Click the checkmark in the lower right corner to advance to the next step in the wizard.
4)If you chose to create a New SQL database server, you should now see the SQL Database server settings screen.
5)In the Login Name text box, enter the user name you would like to use to login to your SQL Database.
6)In the Login Password and Confirm Password text boxes, enter the password you would like to use to login to your SQL Database.
7)In the Region drop down, select the Windows Azure data center where you would like your new SQL Database server provisioned. For optimal latency and cost of data transfer between data centers, it is recommended that you provision all of the services for an application that are dependent on one another in the same data center.
8)Lastly, leave the Allow Windows Azure Services to Access The Server check box checked. This will allow other services in Windows Azure, like the Windows Azure Web Site that we will create in future steps to access your SQL Database.
leave the Allow Windows Azure Services to Access The Server check box checked
After you have provided all of the details for your new SQL Database server, click the checkmark in the lower right hand corner to finish the wizard and create your database.
 You will see progress in the toolbar at the bottom, and once your database has been created, you should see a success message like the following. 
Click the OK button in the notification bar to dismiss the message.
Click the OK button in the notification bar
Once your database has been successfully created, go ahead and click on the Name in the list of databases, which will display information about your database and additional options and configuration.

The next thing we need to do is configure access to your SQL Database. By default, Windows Azure SQL Database firewalls all IP address from connecting to your server. As a result, you need to grant access to connect and manage your server. To grant access to your IP, click on Set up Windows Azure firewall rules for this IP address.
Set up Windows Azure firewall rules for this IP address
Your database is now ready! So now you are ready to add some tables and data.

Creating Tables in Your SQL Database

While on the home screen of your SQL Database, click on the Manage button in the toolbar at the bottom.
click on the Manage button
This will display the SQL Database Management Portal login screen. Enter the username and password that you used when creating your SQL Database and click the Log on button.
SQL Database Management Portal login screen
Select your newly created Table1 database and click on the Design button in the menu on the left.

You should now see options to design the schema of your database including Tables, Views and Stored Procedures.
Click on the New table button in the middle of the screen.
Click on the New table button
1)Enter Table1 in the Table Name text box.
2)On the ID column that was added by default, check the Is Identity? check box.
3)On the Column1 column that was added by default, enter FirstName under Column, leave the defaults forType set to nvarchar and the Length to 50.
4)On the Column2 column that was added by default, enter LastName under Column, leave the default for Typeset to nvarchar and set the Length to 50.
Click the Save button in the toolbar at the top to save the Person table.
Click the Save button

Adding Data to Your Database

Now that you have the structure of our database setup, it is time to enter data into your tables. The same web-based SQL Database Management Portal that you used to setup the structure and schema of your database, you can use to add data to your database.
To begin, you will add data as shown

Now that your table is selected, click on the Data tab at the top and a list of rows from the Table1 table will be displayed. At this point, the list of rows should be empty, because you should have an empty table.
Click the Add row button to add a new row to the Person table.
Click the Add row button
The ID column is setup as an identity column and it will get automatically incremented and assigned when a record is added, which means you should leave this column blank.
Enter some text in the FirstName column.
Enter some text in the LastName column.
Repeat these steps two more times to add fields to the  table. 
Once finished adding rows to the table, click the Save button in the toolbar at the top to save your new rows to the table.
Now that you have fields  in your table named websites, you need to add Items to their Shopping Lists.
Click on the Item table in the menu on the left.

Click on the Data tab at the top and an empty list of rows from the Item table will be displayed.
Click the Add row button to add a new row to the Item table.

Once finished adding rows to the Item table, click the Save button in the toolbar at the top to save your new rows to the table.
Congratulations! You have now built a database and stored data in the cloud!
Now that you have people in your Person table and shopping list items in your Item table, you are done setting up your database and can now close the SQL Database Management Portal and move on to building your web service and application.


If you followed along with this article, you just:
1. Created a database in the Cloud using Windows Azure SQL Database
2. Set Up a table and defined your database’s schema
3. Added data to your database using the web-based SQL Database Management Portal
Next blog will demonstrate on how to expose this SQL table as an ATOM feed and then use lightning connect to display data .

Popular posts from this blog

Invoking Apex Callout From Process Builder

Process builder is GA in Spring 15 and one of the queries I came across was around how to invoke apex callouts from Process builder .

Before process builder came we had two common approaches of calling webservice 

1)We have outbound messages as one of the Actions for workflows.This works if other party implements the WSDL that is generated once Outbound messaging is defined with appropriate end point .

2)Most of times future method invoked through triggers allowed to do apex callouts and invoke external web service provided the future method is annotated with @future(callout=true).This provides lot of flexibility and one of the best approaches .

3)Flow triggers was in BETA and this was also one of the ways we could invoke callouts provided the Flow implements process plugin .To understand in detail how to implement process plugin refer to the below example

The purpose of this blogpost is to demonstrate the new possibility of invoking apex callout through Process Builder 

I tried initi…

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

Writing Test Classes For Apex Rest Service

I came across a question in a developer community on how to write Unit Test Classes for the Rest API service for POST HTTP calls

Unit Test Classes for REST API following link is very useful and inspired by this jeff had an article on his blog on how to write the test method for same .

Here is the Jeff Blog Link Test Class for REST API(Good reference for GET Rest Services)

In one of my previous blog post i demonstrated how to use native parsing technique for Rest api for User Defined Type
I wrote the Test class for the same and this post is helpful for all those searching for how to write test classes for apex rest service annotated with POST Call
Here is the Rest Service Class for which i attempted the test class
@RestResource(urlMapping='/DemoUrl/*') global with sharing class MyRestResourcedemo { global class RequestWrapper{ public Account acct; public Contact[] cons; } global class ResponseWrapper { public String StatusCode; …