Consuming Data within SharePoint On-premises

By now you should have realized that SharePoint is a fantastic presentation layer for many other applications. In fact, lots of vendors have written add-ins or components that allow you to surface that line of business application into SharePoint. However, the only real component within SharePoint On-premises for consuming data is Business Data Connectivity services, which works but does not always meet the needs. Outside of that you have the Business Intelligence components within SharePoint such as PerformancePoint that allows you to surface data. In reality there is not any fantastic way to connect your line of business data to SharePoint easily, without the need for customization such as using SharePoint Designer or even writing managed code.

So, let’s say that we needed to connect a SQL database to SharePoint and display some data. Firstly, lets understand the business need and what we need to allow, as that will determine the approach we take. The following table shows the options available depending on the requirement:

As you can see, in reality without going to full custom code, you are very limited on what can be used. If you had however created a Restful API over the top of your database then your choices are endless. Of course, there are always 3rd Party options when working with data.

If we are using SharePoint Designer, then simply connect to the SharePoint site and then select the “Data Sources” menu item on the left.

Once the ribbon bar has loaded select “Database Connection”, then select the “Configure Database Connection” button from the wizard.

Populate the SQL database server and credentials for your environment.

Once you save the details and press “Next” it should connect and list you the databases and tables. Select the database and tables, views or store procedures you wish to use and save. When you get back to the main SharePoint designer console your database connection should then be listed.

Now we have this listed we can create a site page and use this connection. Select the “Site Pages” link on the left and then select the “Web Part Page” button and create a new page using the template of your choice. Once saved it will be listed in the window.

Click on the page and then select the “Edit File”, which will open the page in edit mode. Next click on the code options on the ribbon bar and choose the “Parse HTML” button.

Then scroll through the code and set your cursor within the “” code block.

On the ribbon bar, you should then see the “Data View” button become active. Click the option and choose “Empty data view”, this will then insert a data view control into the page and the various options will then become available within the ribbon bar.

Next select within the web part zone again and choose the same option as before, “Data View” but this time you should see more options, select the “All Data Sources” link and choose “Database Connections”.

When the popup window loads, you can now select the database connection you added earlier. 

This will then render the database values in a standard row layout, where you can modify it to look as needed. If you want to edit the XSLT of this web part you can navigate to the “Design” tab and click on “Customize Entire View”.

Next make the changes you need and you can then save the page and view or even choose the “Preview” link on the menu to make sure it works as expected.

This process works great and is very easy to create, however if you were creating something like this within Office 365 or even SharePoint 2016, then I would recommend creating a RESTful API for the data connection and using client side code, which would prepare you for building solutions in the new SharePoint Framework (SPFx) way.