Create a Web Application for Performing CRUD Operations Using JavaScript and Web API

April 19 2017

This post was originally published on June 6, 2016 for Wijmo Japan

This blog introduces you to performing CRUD operations on a database using JavaScript and Web API.

Here, we create the view using JavaScript and HTML. We also use Wijmo’s FlexGrid (a datagrid control) and CollectionView (a utility class for managing changed data) for better data handling and productivity.

The server is developed using the following frameworks and products.

ASP.NET Web API
SQL Server
Entity Framework
Visual Studio 2015

The Web API provides CRUD operations and processes the database by sending HTTP requests from JavaScript.

Here, CRUD refers to the four major database operations i.e., Create, Read, Update and Delete.

We are using JSON data, which can be easily handled with JavaScript, for transferring data between the view and the server.

1. Create Web API application

First, create a Web API application that provides CRUD operations for the database. Create a new ASP.NET Web Application project in Visual Studio.

Use an "Empty" template when setting up the project.

Add the App_Data folder to the project.

Add a SQL Server database file in the App_Data folder. Here, we are using the NORTHWND.MDF file which is provided in the trial version of ComponentOne Studio.

Add an ADO.NET Entity Data Model to the project.

On the "Choose Model Contents" page of Entity Data Model Wizard, select "EF Designer from database".

On the "Choose Your Data Connection" page, confirm that the MDF file is automatically selected.

Then, on the "Choose Your Version" page, select version of Entity Framework that you want to use.

On the "Choose Your Database Objects and Settings" page, select the database. Here, we have only selected the Products table.

This creates a model and the designer gets displayed.

In order to reflect the model settings, close the designer and build the project.

Add scaffolded items to the project.

In the "Add Scaffold" dialog, select the "Web API 2 Controller with actions, using Entity Framework" option.

In the "Add Controller" dialog, select the Model class and Data context class from the dropdown.

With this, we have a Web API that provides CRUD operations.

Details on how to create a Web application using ASP.NET Web API and the Entity Framework are also introduced in the tutorial provided on the official ASP.NET website.
Using Web API 2 with Entity Framework 6 | The ASP.NET Site

2. Create a View

Next, create a view in which the end user can perform database operations.

Here, we are creating a view on the index page of the Web API project. Add an HTML page to the Web API project and change the file name to "index.html".

Add Wijmo references in the section of index.html.

<!-- Wijmo -->
<link rel="stylesheet" href="http://cdn.wijmo.com/5.latest/styles/wijmo.min.css" />
<script src="http://cdn.wijmo.com/5.latest/controls/wijmo.min.js"></script>
<script src="http://cdn.wijmo.com/5.latest/controls/wijmo.grid.min.js"></script>

In the section of index.html, add an element to host the update button and the FlexGrid control.

<button onclick="update()">Update</button>
<div id="flexGrid" style="height: 300px;"></div>

At the end of the section of index.html, add the following JavaScript code to generate CollectionView and the FlexGrid control.

<script>
var cv = new wijmo.collections.CollectionView();
var flexGrid = new wijmo.grid.FlexGrid('#flexGrid');
flexGrid.itemsSource = cv;
</script>

When you run the project, an empty FlexGrid control and the "Update" button will appear on the index page.

3. Populate data

Next, load data from the database and populate it in the FlexGrid.

There are different methods used to call the Web API (to send an HTTP request) such as jQuery's ajax function or XMLHttpRequest, but here, we are using Wijmo's wijmo.httpRequest method. This method allows you to send HTTP requests by using much simpler code than XMLHttpRequest, even if you are not using jQuery.

Add the following JavaScript code to send a GET request to the Web API. When the request is successful, it converts the loaded JSON data to a JavaScript array object and sets it to the CollectionView.

wijmo.httpRequest('/api/Products', {
  success: function (xhr) {
    cv.sourceCollection = JSON.parse(xhr.response);
  }
});

The URL and HTTP methods of the Web API are mentioned in the comments of the automatically generated controller (ProductsController.cs).

Run the project to populate the data in the FlexGrid control.

4. UPDATE

Next, update the database with the data edited in FlexGrid.

Here, we are using a batch update (where multiple edits are updated in the database in one go) instead of the normal update mode (where the database is updated every time a data record is edited).

In order to perform a batch update, it is necessary to keep track of data that is changed and manage a list of changes. Since the CollectionView automatically handles such operations, there is no need to write complex code to perform the batch update.

The following JavaScript code enables tracking of the changed data in CollectionView. It gets a list of updated data (CollectionView.itemsEdited property) and sends a PUT request to the Web API. In the PUT request, specify the ID of the updated data in the URL and data to be sent in the data.

cv.trackChanges = true;

function update() {
  for (var i = 0; i < cv.itemsEdited.length; i++) {
    wijmo.httpRequest('/api/Products/' + cv.itemsEdited[i].ProductID, {
      method: 'PUT',
      data: cv.itemsEdited[i]
    });
  }
}

As only one data set can be updated at a time in the Web API created by this method, the Web API is called as many times as the number of updated data items. If we compare it with the normal update, though the number of calls to the Web API remains the same, the update can be performed at any point of time which is an advantage over the batch update.

Run the project, edit multiple data records, and press the Update button. When you reload the page, you can see the updated data in the database.

5. CREATE

Next, reflect the data added to the FlexGrid to the database.

The following JavaScript code enables rows to be added to FlexGrid. Then, it gets a list of added data (CollectionView.itemsAdded property) and sends a POST request to the Web API. In the POST request, specify the added data in the data parameter.

flexGrid.allowAddNew = true;

function update() 
  ：
  for (var i = 0; i < cv.itemsAdded.length; i++) {
    wijmo.httpRequest('/api/Products/', {
      method: 'POST',
      data: cv.itemsAdded[i]
    });
  }
}

A new row appears at the bottom of FlexGrid, and when you input in a cell, a new data record is added.

Run the project, add multiple data records, and press the Update button. When you reload the page, you can see the added data records in the database.

6. DELETE

Next, reflect the data deleted from FlexGrid to the database.

The following JavaScript code enables rows to be deleted from FlexGrid. Then, it gets a list of the deleted data (CollectionView.itemsRemoved property), and sends a DELETE request to the Web API. In the DELETE request, specify the ID of the deleted data in the URL.

flexGrid.allowDelete = true;

function update() 
  ：
  for (var i = 0; i < cv.itemsRemoved.length; i++) {
    wijmo.httpRequest('/api/Products/' + cv.itemsRemoved[i].ProductID, {
      method: 'DELETE'
    });
  }
}

In FlexGrid, you can easily delete a row by selecting it and pressing the Delete key. It is also possible to select multiple rows and delete them at once.

Run the project, delete the data added in Step #5, and press the Update button. When you reload the page, you can see that those data records have been deleted from the database.

Please note that it is not possible to delete the existing data in NORTHWND.MDF database because it has a relationship with other tables as well.

With the above implementation, we executed the database CRUD operations in a Web application that uses JavaScript and Web API.

Download Now!

MESCIUS inc.