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.


AngularWebAPI1A

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.


AngularWebAPI1A

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.


AngularWebAPI2

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


AngularWebAPI3

Add the App_Data folder to the project.


AngularWebAPI4

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.


AngularWebAPI5

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


AngularWebAPI6

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


AngularWebAPI7

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


AngularWebAPI8

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


AngularWebAPI9

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


AngularWebAPI10

This creates a model and the designer gets displayed.


AngularWebAPI11

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


Add scaffolded items to the project.


AngularWebAPI12

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


AngularWebAPI13

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


AngularWebAPI14

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".


AngularWebAPI15

Add Wijmo references in the <head> 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 <body> 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 <body> 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.


AngularWebAPI16

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.


AngularWebAPI17

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.


AngularWebAPI18

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.


AngularWebAPI19

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.


AngularWebAPI20

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.


AngularWebAPI21

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


Download Wijmo 5