How do I access data in SQL via Sharepoint 2013?

Question

I'm not a SharePoint developer, but I have developed some small ASP/VB.net web pages.

Basically, prior to using SharePoint, I have developed a basic portal that pulls data (VB.Net) and sends it to the ASP page which I'm using that data in JavaScript functions/API.

We are being forced to use SharePoint now and I have figured out how to run my JavaScript code within a SharePoint page, but I'm not sure how to get data from SQL Server into my JavaScript functions without pulling data via JavaScript which is a security concern.

So my question is, what is the best way to get data from SQL server or any other data source and use that data via JavaScript client side code on a SharePoint page to run a JavaScript API like HighCharts? Also is there any way to run VB.Net on SharePoint as I did with a standalone vb.net/asp.net solution?

Let me know if I can provide any more detail.

Answer 1

If you didn't had a page already existing with name = "WelcomeNew", then people will try to access site and get Page Not Found error.

An Ideal SharePoint approach is a follows

1. Create an External Content Type (ECT) and use this to connect to you SQL Server

2. In ECT you can define methods which you want to act on the backend (Ex: SQL Table)

   • Read All Items
   • Read a single Item
   • Update an Item
   • Delete an Item

3. Once this done you need to create and External List

The above steps you can do it using SharePoint Designer with ZERO lines of code. Now you have exposed the data over SharePoint.

You can easily consume this data using you JavaScript API. For that you can leverage SharePoint REST API.

For example, assume you create External List with name Employee which exposes SQL table Employees. Now to query this data, you can use REST URL

http://siteurl/_api/web/lists/getByTitle('Employee')/items

You can use this in a normal jQuery ajax call using

jQuery.ajax({
    url: "http://siteurl/_api/web/lists/getByTitle('Employee')/items",
    method: "GET",
    headers: { "Accept": "application/json; odata=verbose" },
    success: function (data) {
        // You can manipulate data here         
    },
    error: function (data) {
        alert('call failed');
    }
});

This approach eliminates any server side coding.

Answer 2

The best practice will be creating a webservice layer to pull the SQL data based and expose via REST API or JSON formats that are compatable with javascript.

Then consume or manipulate the data via javascript. In this way you can handle all your security related aspects and SQL related queries with in the web service layer.