How to connect javascript code to database in ASP.NET

Question

I have an ASP.NET application which has been working by creating a context to connect to a SQL Server 2016 database. So far it's been working great, but I've been tasked with adding javascript into the code to retrieve data from the database and display it to the user. I've heard connecting directly to the database through Javascript has security risks due to users being able to see the username and password, so I wanted to know if there's a safe alternative to direct connection that would have the Javascript code call server-side code and retrieve the data (stored in the database as JSON). I know AJAX is how to retrieve the code and have learned a decent amount about it, but I don't know how to get the connection string that AJAX needs to function. I've heard Node.js could work, but we already have a server stood up so I'm pretty confused. Any advice would be much appreciated!

Side note: I know there's already a good explanation of why a direct connection is bad here, but while the top answer says "A better way to connect to a sql server would be to use some server side language like PHP, Java, .NET, among others." they didn't explain how to connect through the server side with .NET

Answer 1

When tasked with this sort of thing, the general idea is to defer loading of the data so that the page can load faster. You do this by loading the UI first, then fetching the data (via an AJAX call done with JavaScript).

With this in mind and generally speaking, you would do something like this:

1. Code a method in your web form or controller, depending on the technology you are using, that loads the page without making a call to load the data. It simply loads the UI with placeholders.
2. On the UI side, have a JavaScript function fire once the DOM has loaded. This function will make a call to another method on the backend that will provide the data (or even the HTML if you so desire) needed to populate the UI. this call will use a callback function that will be triggered when the data is received back from the server. This function will then populate the UI with the data.
3. Back on your web form, controller, web service, web api or wherever you decide to host your data retrieving logic, you will code a method that will service the JS AJAX call. This backend method will then make the call (server side) to the database using the connection string safely stored in your web application and retrieve the data, then pass it back to the calling JS function, which will then fire the callback mentioned before and populate the UI.

There are plenty of examples out there as this is a common design pattern nowadays. You can find one here.

Answer 2

I actually found the solution I was looking for here (suggested by a coworker):

https://learn.microsoft.com/en-us/aspnet/web-api/overview/getting-started-with-aspnet-web-api/

In case the link becomes invalid, the solution is to use Microsoft's Web API 2 with ASP.NET and EntityFramework. By creating Models and controllers in Visual Studio, you can create a context that can be reached with AJAX using routes defined in the RouteConfig access in the JavaScript like so:

var ViewModel = function () {
var self = this;
self.books = ko.observableArray();
self.error = ko.observable();

var booksUri = '/api/books/';

function ajaxHelper(uri, method, data) {
    self.error(''); // Clear error message
    return $.ajax({
        type: method,
        url: uri,
        dataType: 'json',
        contentType: 'application/json',
        data: data ? JSON.stringify(data) : null
    }).fail(function (jqXHR, textStatus, errorThrown) {
        self.error(errorThrown);
    });
}

function getAllBooks() {
    ajaxHelper(booksUri, 'GET').done(function (data) {
        self.books(data);
    });
}

// Fetch the initial data.
getAllBooks();

};