JS/JQuery Connect to SQL Server

Question

Right now i am modeling a ClientWebPart outside of the SharePoint server. Although, i advised the client to let me on the server to do Code Behind connection, they said not right now. Server might not be ready for developer eyes. :)

That being said, i still need to connect to the Database (on the same Server/Instance) to make sure the Data to be queried will return the way i expect it to.

The only way i saw to do that was to spin up a JavaScript ADODB.Connection/Recordset set of object(s).

Here is what have so far but doesnt seem to be working:

try {
    var rs = new ActiveXObject("ADODB.Recordset");

//      var conn = new ActiveXObject("ADODB.Connection");
} catch (e) {
    alert("ADODB namespace not found.");
    return;
}
var sql = "Select id, value from tblTest";
rs.Open(sql, connStr, adOpenForwardOnly, adLockReadOnly, adCmdText);
//conn.Open(connStr);

lblData = $("#sqlData");
var val = "";

if (conn.State !== adStateOpen) {
    alert("Connection to Server Failed!");
    return;
}

//var rs = new ActiveXObject("ADODB.Recordset");
//rs.Open("Select id, value from tblTest");
rs.MoveFirst

rs.each(function (record, index) {
    val += "id: " + record["id"];
    val += "; value: " + record["value"];
    val += "\n";
});

lbldata.text(val);
//Clean Up
rs.close;
conn.close;

I am open to alternatives. Until i can get to the Server and develop on the SharePoint server, it will have to be client based solution.

Although i am not sure if its possible, i was thinking of doing Embedded coding since the WebParts are nothing but Pages, anyways. But could not find any credible or reliable documentation for SP2013 embedding code in the ASPX file.

Notes

• Do Not Reference External Content Types as they are resistant to implementation. They are fitting the bill for my time.
• I know i need to do a WebPart on the Server Side, but they havent given me access to the server so i can develop directly on it.

Answer 1

Personally, I'd host this out of SharePoint as an ASP.net Web API site and enable CORS to the SharePoint site's domain. I've done this sort of thing many times. The idea of embedding SQL commands into JavaScript as strings seems like just begging for some sort of SQL injection to occur.

You could also host the Web API solution in SharePoint. I have never done this, but I would imagine creating a Farm Solution and then adding ASP.net MVC/Web API via nuget would get you started. I might fiddle with this in my free time. If you do this and get it working, you need to write a blog post on it!

Answer 2

Simple take an Application Page in your solution and implement the Server side methods using System.Web.Services.WebMethod

Let's say you call your Application Page as "AjaxCallsProcessor" and it has a function say "GetSearchResults" than you can use it using JQuery Ajax call as:

$.ajax({
    type: "POST",
    url: "/_layouts/YourSolutionName/AjaxCallsProcessor.aspx/GetSearchResults",
    data: "{'pramater':'" + value + "'}",
    contentType: "application/json; charset=utf-8",
    dataType: "json",
    success: function (response) {
        var serachResults = response.d;
    }
});

You should write all your database connectivity logic on server-end, as the previous answer said there are high chances of SQL Injection otherwise..