Interacting with database in MVC

When using MVC 4 Razor (I am new to web development), I get concerned about my understanding of interactions between the database and the client. Much like all appilications these days, there is a lot of data that is presented to the user and in different fashions. I have looked around, and I can't find a concrete C# example, as they're mostly PHP.

Below is an example of loading data into a jQuery DataTables plugin.

• Is this the correct way of formatting data to JSON and loading it into a jQuery function?
• If I wanted to update/delete/insert to the database from the client, is there a way to do that within JavaScript, or should I be doing it within C# somehow? (I have looked around and really, there isn't a ton of documentation)

Note: If it is easier to answer my question without concerning the use of jQuery DataTables, that's completely fine. I'm looking for a broader spectrum of information.

HTML

<table id="grid" class="row-border hover order-column " width="100%">

<tr>
<th>Sequence</th>
<th>ID</th>
<th>Name</th>
<th>Ticker #</th>
<th>Salary</th>
<th>Bonus</th>
<th>Allocation</th>
<th>Begin Date</th>
<th>End Date</th>
</tr>
</table>

<div id="pager"></div>


Home Controller - C#

    public string GetAssociateFromDb()
{
var dt = new DataTable();
string connString = ConfigurationManager.ConnectionStrings["DEFCOMP"].ConnectionString;
using (var connection = new SqlConnection())
{
connection.ConnectionString = connString;
using (var cmd = new SqlCommand("SELECT * FROM FUND", connection))
{
connection.Open();
return JsonConvert.SerializeObject(dt);
}
}
}


In the _Layout

<head>
<script src= "http://cdn.datatables.net/1.10.0/js/jquery.dataTables.js" type="text/javascript"></script>

• You're forgetting about the M in MVC. Put your data into a model, and pass that to a view. You don't have to use JSON and jQuery to put your data into the table. – krillgar Jul 25 '14 at 14:14
• »You don't have to use JSON and jQuery to put your data into the table« When you to the rendering via ASP.Net MVC you are right, But when you are using datatables.net jQuery is a requirement. – Thomas Junk Jul 25 '14 at 14:57

It's been a while, since I've been on the ASP-Road, but I hope to provide you some help:

1) Read the Documentation of DataTables, especially the paragraph about Ajax source data. There you learn how to use Datatables with server generated data. Serverside processing might be of interest too.

2) You are free to use a html-skeleton (as you did), but you could use Custom Column Definitions

3) In order to get the Datatables Plugin to work you need a) an Element in your DOM, where the Datatable can hook on: $('#example').dataTable() does that; and b) you need data to display. There are two basic formats: I) You have loose data, which is represented as an Array of Arrays. Each Array representing a Row, which consists of an Array of columns as in this or this example. II) You have structured data, which comes as JSON as in this example So when I get your example right: return JsonConvert.SerializeObject(dt);  does exactly that. What your code is lacking is the initialization of the datatable (see (3) ). And then you are done. P.S.: Perhaps this is interesting for you. Edit: I forgot to address this point If I wanted to update/delete/insert to the database from the client, is there a way to do that within JavaScript, or should I be doing it within C# somehow? (I have looked around and really, there isn't a ton of documentation) No. Within Javascript there is no way. But it is possible to send a message to the server, which lets the server know, that you wanted to delete one entry. It is possible to intercept the rendering process of datatables.net, so that you generate attributes, which you could use to identify the objects displayed in the column (e.g. an ID or the like). And you could then initiate a DELETE request to the server $.ajax({
url: '/endpoint'+idOfSelectedElement,
type: 'DELETE',
success: function(result) {
// Do something with the result
}
});


Look here for the documentation.

Edit2: A question to elaborate a bit more on the topic

When dealing with insert, update, delete, select etc from the front end to server, is this always done with ajax? Is it safe? Is it fast? How are the requests usually formatted to delete specific rows, or update specific rows? (your answer doesn't have to be jquery datatables specific) Thanks!

a) CRUD and ajax

There is no clear answer to that: it depends. If you are working with browsers as clients of your interface/API, there are only two of the many HTTP-Methods implemented, namely GET and POST. This is, what we all know from our standard <form> specified under method. These were the genuine methods available (rooted in historical decisions, but I see no reason to stick with that; browser developer should change this constraint).

In order to use a restish API, which has e.g. also support of PUT, DELETE, PATCH there is a way to circumvent this constraint. Many frameworks provide parsing a hidden <input>-field where the "rest-method" is defined and map to the according controller.

As you can see, it is not always necessary to do it asynchronous or with javascript. So ajax is only one - but a nice one - option to play with. Unless not otherwise restricted (managers, users etc.) I would always go for ajax.

b) Is ajax safe

It is as safe as you make it. There are possibilities for securing your endpoints. But as I said, I am not quite up to date, what Microsoft offers, there are possibilities for sure.

c) Is it fast

It is as fast as your hardware (connection, server etc.) allow. There is no difference in terms of performance doing something like a PostBack or a \$.post(). The main difference is, that a traditional POST is a) not asynchronous and b) you are loosing the current context.

d) Form of CRUD requests

You form your actions via URL and HTTP-Verbs:

1) Get a list of all Users: GET api/users

2) Get information about user 1: GET api/users/1

3) Get all users, which are from Seattle GET api/users?location=Seattle

4) Get all male users, which are form Seattle GET api/users?location=Seatte&gender=male

5) Create a user POST api/users/ sending a JSON representation of the created user {"name":"Doe", "firstname":"John","city":"Seattle" ...}

6) Update user 1 PATCH/PUT api/users/1 sending JSON of the changes {"city":"Miami"} (if he moved to Miami}

7) Delete user 1 DELETE api/users/1

If you want to specify the output data from the server, you could set the ACCEPT field in the header e.g. to application/json or application/xml.

As you see, there is a big advantage in using a RESTish API: you have a set of universal verbs, which each service speaks, which is easy to learn and to adapt.

These are the basics of the REST-philosophy. There is more to say, but not in this post ;)

• Thank you for answering my question, Thomas. This is the kind of answer I was hoping to receive. I did look at the documentation - at least 25 times. I think my lack of extensive JS knowledge is a contributing factor to my confusion. Can I ask you a question that extends from the 2nd half of your answer? When dealing with insert, update, delete, select etc from the front end to server, is this always done with ajax? Is it safe? Is it fast? How are the requests usually formatted to delete specific rows, or update specific rows? (your answer doesn't have to be jquery datatables specific) Thanks! – Mark Jul 25 '14 at 12:14
• You are welcome. I elaborated a bit further on the topic: Hope this helps you a bit, getting grips on JSON, REST and the like. Feel free to ask more questions (in other posts): I am glad, if I can help you out. – Thomas Junk Jul 25 '14 at 14:02
• To be honest, all of my confusion originated when I started looking at AngularJS and it's MVC-oriented nature. The CRUD requests are definitely something I need to learn and become very familiar with. They also contribute to my confusion. Let me ask 1 more thing => in your examples: api/users is this using EF/ORM? I don't use EF/ORM currently. Am I making a call to a method that has a SQL statement? (as above?) – Mark Jul 25 '14 at 15:18
• »in your examples: api/users is this using EF/ORM? I don't use EF/ORM currently. Am I making a call to a method that has a SQL statement? (as above?) « No. These are the API calls, or so to say: This is, what the server/Framework gets. These are the routes you have to define somewhere. This is independent from EntityFramework. You only neet something, that binds incoming JSON to C# objects - which IMHO does ASP.Net MVC for you. – Thomas Junk Jul 25 '14 at 15:25
• Sounds like something I'll need to take a closer look at and keep practicing. Thank you for the knowledge! I'm sure I'll be back with more. – Mark Jul 25 '14 at 16:28

I really feel bad about recommending this, but based on what you're doing you should investigate the use of Webforms in ASP.NET.

Webforms are the most straightforward way to provide easy CRUD operations on a database in C# while exposing the operations safely to the user. You also will work entirely with C# and the ASP.NET elements in html, so there's no need to manually use javascript with most basic tasks.

If you want to get more advanced then you can go with MVC5 in ASP.NET but that doesn't have the same level of builtin controls, data table displays, and similar that webforms has baked into itself. MVC5 does give you more power and flexibility in return, but if you need to get something done in a hurry or in a very basic form then you'll probably be fine with Webforms.

The problem with webforms, and why I feel bad recommending it, is that it tends to not encourage good N-Tier design because it both allows you to do things like embed SQL queries in your views (which is blasphemy) and a lot of the examples display exactly that behavior.

It is possible to setup a good distribution of responsibilities into multiple tiers with webforms, which is what I'm doing at my current job, but it doesn't do a great job of encouraging good design. That being said, it does encourage getting things done in a hurry, so that can sometimes outweigh the drawbacks.

• I see no sense in recommending webforms. This has nothing to do with webforms. And webforms are no advantage to his approach. So why recommending it? And disencouraging of Javascript is ridiculous. – Thomas Junk Jul 24 '14 at 23:32
• "Note: If it is easier to answer my question without concerning the use of jQuery DataTables, that's completely fine." << OP requested other alternatives to jQuery which is why I provided one. – Ken Jul 24 '14 at 23:33
• How are webforms easier than datatables.net? – Thomas Junk Jul 24 '14 at 23:55
• You don't have to go through an intermediate JSON layer and you can bind the results of a SQL query directly to a gridview or template or whatever you need to display the data. Removing the JSON layer removes complexity since you're going to have to implement the data loading either way, whether it's for binding to the gridview or serializing to JSON for dumping out of a webservice to use in datatables. Even if you ignore the reduced complexity, webforms are easier for a beginner because the documentation and abundance of examples is superior to his current less-documented approach. – Ken Jul 24 '14 at 23:58
• @KennethPosey Awesome. Thank you for the insight and for your answer. I really do appreciate it. – Mark Jul 25 '14 at 16:36