Angular Manage MSSQL Table CRUD With MVC Web API for Beginners

Introduction

The article is related to managing data table CRUD operations in AngularJS using a SQL database table and ASP.NET MVC web API. This tutorial is for beginners or students.

We created a CRUD functionality in AngularJS. Managing the datatable is easy. Also, we will learn how to publish it on a server to access the web API. Furthermore, we will learn how to test the web API with the Postman client. Finally, we explained all data operations in one page of coding. I hope that it will be helpful for you to manage data tables in your program with advanced mode.

Angular JS code point

ng-app - This directive is used to bootstrap the application. Normally, it is put in top-level elements like HTML or body tag.
ng-controller - This is the main component of AngularJS, which contains the state and logic both. It acts as a bridge between services and views.
$scope - Provides a link between the data and views. It holds all the required data for the views and used within the HTML template.
{{expression}} - It is called expressions and JavaScript-like code can be written inside. It should be used for mainly small operations in an HTML page.
ng-init - This directive provides us a way to initialize a variable on the page. ng-repeat-It actually repeats the element on which it is used based on the provided list of data. In our example, persons is a JSON object which contains a list of people.
$http-It enables you to initiate the AJAX request to the server and get the response. This is a core angular service.

Example ScreenShot

Section 1

Section 1 shows a short view of the code part. We create ng-app myApp and in the body part we create ng-controller myTableCtrl. Both ng-app and ng-controller are the two main parts of AngularJS. In the header tag, we include angular.min.js and link bootstrap.min.css, font-awesome.css to run angular js functionality and design css. Also, we added a custom design class to create a good-looking output. In HTML, we add code to get an Angular app by using angular.module() function, so that we can access the controller by using "appName".controller() function. Next, we created an empty json object array named persons to fill the table. Also, add three functions named Save(),RecEdit() and RecDel() to manage the table record.

<html ng-app="myApp">
<head>
<link href="bootstrap.min.css" rel="stylesheet" />
<link href="fontawesome/css/font-awesome.css" rel="stylesheet"/>
<script src="angular.min.js"></script>
<title>My Data Table</title>
<style>
.container {text-align: center;background-color: rgb(255, 244, 244);}
.container h2{margin:0px;background-color: rgb(224, 255, 224);color:darkgreen;}
.row {text-align: center;}
.row h4 {color:rgb(67, 21, 192);}
.row table{margin: auto;}
.row table tr th{color:red;}
.row table #row_header th{color:white;background-color:red;}
.row table #row_header #th1,#th5,#th6{text-align: center;}
.row table tr #td1,#td5,#td6{text-align: center;}
.row table tr #td1,#td2,#td3,#td4 {font-size:large;}
</style>
<script>
var myApp = angular.module('myApp', []);
myApp.controller('myTableCtrl',function($scope,$http){
var mykey=0;
// create empty json object array.
$scope.persons=[];
// get all records, local function not related to scope
GetRecords = function (sqry) {/*code below*/ }
// save or update record, local function not related to scope
SaveRecords=function(sqry){/*code below*/ }
// get record data to update by key.
$scope.RecEdit = function (per) {/*code below*/}
// delete record by key
$scope.RecDel = function (PersonId) {/*code below*/}
// save or update record
$scope.Save = function () {/*code below*/ }
// default load record if available.
GetRecords("{'StrQry':'SELECT * FROM [TblPerson]'}");
});
</script>
</head>
<body ng-controller="myTableCtrl">
<div class="container"><h2><u>Angular Js Table CRUD MSSQL with MVC API for Beginner</u></h2>
<div class="container" ng-init="per={}"></div>
<div class="container"></div>
</body>
</html>

Here, we create an empty JSON object array named persons=[]. Also, we added two new functions to call the API, which is named GetRecords() and SaveRecords() which receives a parameter JSON object. Both functions have $http request with post method and call the web API URL for reading or saving table data. In our case, we used iis server localhost. The application's name is mywapi.

// get all records, local function not related to scope
GetRecords = function (sqry) {
$http({
method: 'POST',
url: 'http://localhost/mywapi/api/tcrud/recselect',
data: sqry
}).then(function mySuccess(response) {
if (response.status == 200) {
var data = response.data;
data = JSON.parse(data);
if (data.length > 0)
$scope.persons = data;
else
alert("No record found.");
} else
alert("Server error...");
})
}
// save or update record, local function not related to scope
SaveRecords=function(sqry){
$http({
method: 'POST',
url: 'http://localhost/mywapi/api/tcrud/recsave',
data: sqry
}).then(function mySuccess(response) {
if (response.status == 200)
GetRecords("{'StrQry':'SELECT * FROM [TblPerson]'}");
else
alert("Server error...");
})
}

You have seen that in AngularJS, to access any function or variable we can use $scope. But the functions GetRecords() and SaveRecords() are not related to $scope, because it is local function used to call the web API. The function parameter in JSON is {"StrQry":"Query String"}. Example: "{'StrQry':'SELECT * FROM [TblPerson]'}". We are going to explain the functions Save(), RecEdit() and RecDel(). The function Save() checks the key of row. If bigger than 0, then it updates the record. Otherwise, it adds a new record in the data table using web API functionality. Here, $scope.per is a single JSON object. This empty JSON object is part of ng-controller, which is available in person form ng-init='per={}' shows in the red-colored rectangle.

// get record data to update by key.
$scope.RecEdit = function (per) {
$scope.per.Name = per.Name;
$scope.per.Age = per.Age;
$scope.per.City = per.City;
mykey = per.Id;
}
// delete record by key
$scope.RecDel = function (PersonId) {
var sqry = { "StrQry": "DELETE FROM [TblPerson] WHERE [Id]='" + PersonId + "'" };
SaveRecords(sqry);
}
// save or update record
$scope.Save = function () {
//default new record save query
var sqry = { "StrQry": "INSERT INTO [TblPerson] ([Name],[Age],[City]) VALUES('" + $scope.per.Name + "','" + $scope.per.Age + "','" + $scope.per.City + "')" }
if(mykey>0){
sqry = { "StrQry": "UPDATE [TblPerson] SET [Name] = '" + $scope.per.Name + "',[Age] = '" + $scope.per.Age + "',[City] = '" + $scope.per.City + "' WHERE [Id]='" + mykey + "'" }
}
SaveRecords(sqry);
mykey = 0;
$scope.per.Name = ''; $scope.per.Age = ''; $scope.per.City = '';
}

For the HTML code, three div are available in this section. The first div tag is to show page header title, the second div tag show form of person, and the third div tag shows the table part. In the second div tag, we initialize the empty json object by using the AngularJS directive ng-init='per={}', which shows in the red-colored rectangular. Next, in for we use ng-model directive in input control in the form, and ng-click to get button event in angular js. Now finally, we are going to talk about the main data table in AngularJS. ng-repeat directive is used to show items in json object array. ng-repeat directive is like a loop. Using ng-repeat displays all items in an array in tabular format.

<div class="container" ng-init="per={}">
<div class="row">
<h4><u>*** Person Data Management ***</u></h4>
<table>
<tr><th>Name</th><th>Age</th><th>City</th></tr>
<tr>
<td> <input ng-model="per.Name" /></td>
<td> <input ng-model="per.Age" /> </td>
<td> <input ng-model="per.City" /> </td>
<td>
<i class= "fa fa-save fa-2x" style="color:green;margin-left: 5px;" data-ng-click="Save()"></i></td>
</tr>
</table>
</div>
</div>
<div class="container">
<div class="row">
<table class="table table-condensed table-hover" style="width:97%;margin-left:24px;margin-top: 5px;">
<tr id="row_header">
<th id="th1">#</th><th>Name</th><th>Age</th><th>City</th><th id="th5">Edit</th><th id="th6">Delete</th>
</tr>
<tr id="rdata" ng-repeat="person in persons">
<td id="td1">{{$index+1}}</td>
<td id="td2">{{person.Name}}</td>
<td id="td3">{{person.Age}}</td>
<td id="td4">{{person.City}}</td>
<td id="td5"><i class= "fa fa-edit fa-2x" style="color:blue;" data-ng-click="RecEdit(person)"></i></td>
<td id="td6"><i class= "fa fa-trash fa-2x" style="color:red;" data-ng-click="RecDel(person.Id)"></i></td>
</tr>
</table>
</div>
</div>

Section 2 (ASP.NET MVC WEB API and DataTable)

Now, we create an ASP.NET MVC API application. The ASP.NET Web API solution structure is below. To add App_Code folder, right-click on the project ---> Add ---> Add ASP.NET folder --> Select App_Code. We created two CS classes in the App_Code folder, the name is TblCUD.cs and TestDataLayer.cs. TblCUD.cs has only one get, set property which is named StrQry to receive a JSON string. Next, the TestDataLayer.cs has three functions, the first is related to the get connection string from web.config. You need to change as per your local environment. The other two functions are related to accessing the SQL client and connection for the table data record operation.

public class TestDataLayer
{
private string GetConnectionString()
{
return ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;
}
public bool ExecuteCRUDByQuery(string SQLstring)
{
SqlConnection sqlCon = null;SqlCommand sqlCmd = null;
bool retVal = false;
try
{
//Setup command object
sqlCmd = new SqlCommand(SQLstring);
sqlCmd.CommandType = CommandType.Text;
sqlCmd.CommandTimeout = 100000;
sqlCon = new SqlConnection(GetConnectionString());
sqlCmd.Connection = sqlCon;
sqlCon.Open();
//Execute the command
int iR = sqlCmd.ExecuteNonQuery();
retVal = iR > 0 ? true : false;
}
catch { retVal = false;throw; }
finally
{
if (sqlCmd != null) sqlCmd.Dispose();
if (sqlCon.State == ConnectionState.Open) sqlCon.Close();
}
return retVal;
}
public DataTable ExecuteQuery(string SQLstring)
{
SqlConnection sqlCon = null; SqlCommand sqlCmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter();DataTable dt = new DataTable();
try
{
//Setup command object
sqlCmd = new SqlCommand(SQLstring);
sqlCmd.CommandType = CommandType.Text;
sqlCmd.CommandTimeout = 10000000;
da.SelectCommand = (SqlCommand)sqlCmd;
sqlCon = new SqlConnection(GetConnectionString());
sqlCmd.Connection = sqlCon;
sqlCon.Open();
//Fill the dataset
da.Fill(dt);
}
catch {throw;}
finally
{
if (da != null) da.Dispose();
if (sqlCmd != null) sqlCmd.Dispose();
if (sqlCon.State == ConnectionState.Open) sqlCon.Close();
}
return dt;
}
}

Next, add a new web API controller, in our case, its name is DefaultController. It has four API functions. The first one is for test purposes with a get request. The other two functions have a post request to receive operations related to your data table. The last function is DTToJson to convert a datatable to JSON output. The function is using MSSQL data table to hold the record. Also, you need to create a data table in your SQL server database. DefaultController is derived from ApiController and also we have set attribute [RoutePrefix("api/tcrud")] on top of the controller. We created two API functions ExecuteCRUDByQuery and ExecuteQuery. Both functions use HttpPost, also set attribute Route for both which is [Route("recselect")] and [Route("recsave")] to call an API with a new name in the URL. If you publish on localhost iis server, at the time of testing, the URL will look like "localhost/mywapi/api/tcrud/recselect". Here, localhost is your machine. In my case, mywapi is the application name, api/tcrud is the routeprefix name of the API controller, and finally recselect,recsave is the API function route name.

[RoutePrefix("api/tcrud")]
public class DefaultController : ApiController
{
// test api function
[HttpGet]
[Route("myw")]
public string MyWelcome()
{
return "Welcome Api world!";
}
// to add , update or delete record
[HttpPost]
[Route("recsave")]
public bool ExecuteCRUDByQuery(TblCUD obj)
{
TestDataLayer objMdl = new TestDataLayer();
bool bResult = objMdl.ExecuteCRUDByQuery(obj.StrQry);
return bResult;
}
// to get data table record
[HttpPost]
[Route("recselect")]
public string ExecuteQuery(TblCUD obj)
{
string sJson = "";
TestDataLayer objMdl = new TestDataLayer();
DataTable dt = null;
dt = objMdl.ExecuteQuery(obj.StrQry);
if (dt != null)
sJson = DTToJson(dt);
return sJson;
}
// convert datatable to json
// code available in source code
private string DTToJson(DataTable dtT){ }
}

In our case, the table name is TblPerson. The structure is shown below:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TblPerson](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](20) NOT NULL,
[Age] [int] NOT NULL,
[City] [varchar](20) NOT NULL,
CONSTRAINT [PK_TblPerson] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

Section 3(Published and Test API on Postman)

To save your time, I recommend downloading our application source code and publishing it on your local IIS server. Open the Web API application, right-click on the project, then click on publishing action and follow the instructions. Just open the IIS server, then next right-click on the default website icon --> add application ---> Give the application an Alias Name--> select your published path location --> OK.

Repeat the same process to publish the AnguarJS app on the local IIS server.

Key Point

Also, check your DefaultAppPool advance setting, if the application has any issue after publishing, then enable enable 32-bit Application mode set to true. Also, if your database server has not connected after publishing, then add a new login and user by using SQL server management studio.

Postman Client

Our next step is to test our API via Postman. You need to download your postman client from the web and install it in Google Chrome. Then, you have to add a request GET or POST and API URL. In our case, first, we check the GET request method of our published API. The Get request method is shows a welcome message. So in the Postman client, you need to follow some steps below

Ppen postman --> select method get --> then add API URL --> click on send

Next, we check the post method to get a record in the data table. Open postman --> select method post --> then add API URL --> select Body --> radio button raw --> input type JSON (application/JSON) --> write JSON object data --> click on send

Note

In this article, we have not discussed any type of web API security. This article's purpose is for publishing a web API, using the web API, postman testing the API, and creating an API for learning purposes.

Uploaded source code of the web API, TestApiWeb_SourceCode.zip
Uploaded source code of AngularJS, mydatatableserver_angularjs.zip
Uploaded the web API published code, PublishedWebApiTest.zip