AngularJS CRUD Operations With Web API Using SQL Database

In this article, we will learn so many things like how to achieve CRUD operations in AngularJS with Web API using SQL Server database. This article will explain basic architecture when we work on real time project. This can be very useful for developers who are working on projects with MVC.

Getting Started

Create a new MVC project in Visual Studio 2015.

First of all, let me share my database diagram.

Now add a new Class Library using right click on solutions and select Add new project and Add New Class Library.

Select class library and give appropriate name and browse saved location.

Add connection string in App.Config

<connectionStrings>
    <add name="CricketerDBEntities" connectionString="metadata=res://*/Models.CricketerModel.csdl|res://*/Models.CricketerModel.ssdl|res://*/Models.CricketerModel.msl;provider=System.Data.SqlClient;provider connection string="data source=.;initial catalog=CricketerDB;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework"" providerName="System.Data.EntityClient" /> </connectionStrings>
<entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
    <providers>
        <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" /> </providers>
</entityFramework>

Now add a new model and context class, 

public class ConnCricketerContext: DbContext
{
    public ConnCricketerContext(): base("name=CricketerDBEntities") {}
    public virtual DbSet < Cricketer_Details > Cricketer_Details {
        get;
        set;
    }
    public virtual DbSet < Cricketer_ODI_Statistics > Cricketer_ODI_Statistics {
        get;
        set;
    }
    public virtual DbSet < Cricketer_Test_Statistics > Cricketer_Test_Statistics {
        get;
        set;
    }
    public virtual DbSet < Cricketer > Cricketers {
        get;
        set;
    }
}

These are my model classes,

public partial class Cricketer {
    [Key]
    public int ID {
        get;
        set;
    }
    public string Name {
        get;
        set;
    }
    public Nullable < int > ODI {
        get;
        set;
    }
    public Nullable < int > Test {
        get;
        set;
    }
}
public partial class Cricketer_Details {
    [Key]
    public int Details_ID {
        get;
        set;
    }
    public Nullable < int > Cricketer_ID {
        get;
        set;
    }
    public string Team {
        get;
        set;
    }
    public Nullable < int > ODI_Runs {
        get;
        set;
    }
    public Nullable < int > Test_Runs {
        get;
        set;
    }
    public Nullable < int > Wickets {
        get;
        set;
    }
    public virtual Cricketer Cricketer {
        get;
        set;
    }
}
public partial class Cricketer_ODI_Statistics {
    [Key]
    public int ODI_ID {
        get;
        set;
    }
    public Nullable < int > Cricketer_ID {
        get;
        set;
    }
    public string Name {
        get;
        set;
    }
    public Nullable < int > Half_Century {
        get;
        set;
    }
    public Nullable < int > Century {
        get;
        set;
    }
    public virtual Cricketer Cricketer {
        get;
        set;
    }
}
public partial class Cricketer_Test_Statistics {
    [Key]
    public int Test_ID {
        get;
        set;
    }
    public Nullable < int > Cricketer_ID {
        get;
        set;
    }
    public string Name {
        get;
        set;
    }
    public Nullable < int > Half_Century {
        get;
        set;
    }
    public Nullable < int > Century {
        get;
        set;
    }
    public virtual Cricketer Cricketer {
        get;
        set;
    }
}

I think let’s use View Model also J. Create a new view model class, 

public class CricketerViewModel {
    public int ? Id {
        get;
        set;
    }
    public string Name {
        get;
        set;
    }
    public int ? ODI {
        get;
        set;
    }
    public int ? Test {
        get;
        set;
    }
    public Boolean Update {
        get;
        set;
    }
}
public class CricketerDetailViewModel {
    public int Id {
        get;
        set;
    }
    public int Cricketer_Id {
        get;
        set;
    }
    public string Team {
        get;
        set;
    }
    public int ODI_Runs {
        get;
        set;
    }
    public int Test_Runs {
        get;
        set;
    }
    public int Wickets {
        get;
        set;
    }
}
public class CricketerODIStatsViewModel {
    public int Id {
        get;
        set;
    }
    public int Cricketer_Id {
        get;
        set;
    }
    public string Name {
        get;
        set;
    }
    public int Half_Century {
        get;
        set;
    }
    public int Century {
        get;
        set;
    }
}
public class CricketerTestStatsViewModel {
    public int Id {
        get;
        set;
    }
    public int Cricketer_Id {
        get;
        set;
    }
    public string Name {
        get;
        set;
    }
    public int Half_Century {
        get;
        set;
    }
    public int Century {
        get;
        set;
    }
}

Now let’s add data in view model. Let’s add one more class and add LINQ queries.

using System.Collections.Generic;
using System.Linq;
using MVCUsingWebAPIWithAngularJS.BR.Models;
using MVCUsingWebAPIWithAngularJS.DataLayer.Models;
namespace MVCUsingWebAPIWithAngularJS.DataLayer {
    public class Cricketer {
        private ConnCricketerContext _dbCricketer;
        public Cricketer() {
                //
                _dbCricketer = new ConnCricketerContext();
            }
            /// <summary>
            /// Get Cricketers
            /// </summary>
            /// <returns></returns>
        public List < CricketerViewModel > GetCricketers() {
                var cricketer = new List < CricketerViewModel > ();
                cricketer = (from x in _dbCricketer.Cricketers select new CricketerViewModel() {
                    Id = x.ID,
                        Name = x.Name,
                        ODI = x.ODI,
                        Test = x.Test,
                        Update = true
                }).ToList();
                return cricketer;
            }
            /// <summary>
            /// Delete cricketer
            /// </summary>
            /// <param name="cricketerId"></param>
        public void DeleteCricketer(int Id) {
                _dbCricketer.Cricketers.RemoveRange(_dbCricketer.Cricketers.Where(x => x.ID == Id));
                _dbCricketer.SaveChanges();
            }
            /// <summary>
            /// Update cricketer
            /// </summary>
            /// <param name="assesmentViewModel"></param>
            /// <returns></returns>
        public int UpdateCricketer(CricketerViewModel cricketerViewModel) {
                MVCUsingWebAPIWithAngularJS.BR.Models.Cricketer cricketer =
                    default (MVCUsingWebAPIWithAngularJS.BR.Models.Cricketer);
                if (cricketerViewModel.Update) {
                    cricketer = _dbCricketer.Cricketers.Find(cricketerViewModel.Id);
                } else {
                    cricketer = new MVCUsingWebAPIWithAngularJS.BR.Models.Cricketer();
                }
                //add cricketer value
                cricketer.Name = cricketerViewModel.Name;
                cricketer.ODI = cricketerViewModel.ODI;
                cricketer.Test = cricketerViewModel.Test;
                if (!cricketerViewModel.Update) {
                    _dbCricketer.Cricketers.Add(cricketer);
                }
                _dbCricketer.SaveChanges();
                return cricketer.ID;
            }
            /// <summary>
            /// Detail
            /// </summary>
            /// <param name="cricketerId"></param>
            /// <returns></returns>
        public CricketerDetailViewModel Detail(int cricketerId) {
                CricketerDetailViewModel cricketer = new CricketerDetailViewModel();
                cricketer = (from p in _dbCricketer.Cricketer_Details.Where(x => x.Cricketer_ID == cricketerId).DefaultIfEmpty() select new CricketerDetailViewModel {
                    Cricketer_Id = (int) p.Cricketer_ID,
                        Team = p.Team,
                        ODI_Runs = (int) p.ODI_Runs,
                        Test_Runs = (int) p.Test_Runs,
                        Wickets = (int) p.Wickets
                }).FirstOrDefault();
                return cricketer;
            }
            /// <summary>
            /// ODI Stats
            /// </summary>
            /// <param name="cricketerId"></param>
            /// <returns></returns>
        public CricketerODIStatsViewModel ODIStats(int cricketerId) {
                CricketerODIStatsViewModel cricketer = new CricketerODIStatsViewModel();
                cricketer = (from p in _dbCricketer.Cricketer_ODI_Statistics.Where(x => x.Cricketer_ID == cricketerId).DefaultIfEmpty() select new CricketerODIStatsViewModel {
                    Cricketer_Id = (int) p.Cricketer_ID,
                        Name = p.Name,
                        Half_Century = (int) p.Half_Century,
                        Century = (int) p.Century
                }).FirstOrDefault();
                return cricketer;
            }
            /// <summary>
            /// Test Stats
            /// </summary>
            /// <param name="cricketerId"></param>
            /// <returns></returns>
        public CricketerTestStatsViewModel TestStats(int cricketerId) {
            CricketerTestStatsViewModel cricketer = new CricketerTestStatsViewModel();
            cricketer = (from p in _dbCricketer.Cricketer_Test_Statistics.Where(x => x.Cricketer_ID == cricketerId).DefaultIfEmpty() select new CricketerTestStatsViewModel {
                Cricketer_Id = (int) p.Cricketer_ID,
                    Name = p.Name,
                    Half_Century = (int) p.Half_Century,
                    Century = (int) p.Century
            }).FirstOrDefault();
            return cricketer;
        }
    }
}

We are done here with data model and business relation model. 

Now let’s add a new Web API in Controller folder in MVC project and add references of other created projects.

This is my Web API code.

using System.Collections.Generic;
using System.Web.Http;
using MVCUsingWebAPIWithAngularJS.DataLayer;
using MVCUsingWebAPIWithAngularJS.DataLayer.Models;
//[Authorize]
[RoutePrefix("api/cricketer")]
public class CricketerAPIController: ApiController {
    // GET api/CricketerAPI/
    [HttpGet]
    [Route("getcricketers/")]
    public List < CricketerViewModel > GetCricketers() {
            Cricketer cricketerDataLayer = new Cricketer();
            return cricketerDataLayer.GetCricketers();
        }
        // GET api/CricketerAPI/Detail/1111
        [HttpGet()]
        [Route("cricketerdetail/{cricketerId}")]
    public CricketerDetailViewModel GetDetail(int cricketerId) {
            Cricketer cricketerDataLayer = new Cricketer();
            return cricketerDataLayer.Detail(cricketerId);
        }
        [HttpPost()]
        [Route("updatecricketer")]
    public int UpdateCricketer([FromBody()] CricketerViewModel cricketer) {
            Cricketer cricketerDataLayer = new Cricketer();
            return cricketerDataLayer.UpdateCricketer(cricketer);
        }
        [HttpGet()]
        [Route("deletecricketer/{cricketerId}")]
    public void DeleteCricketer(int cricketerId) {
            Cricketer cricketerDataLayer = new Cricketer();
            cricketerDataLayer.DeleteCricketer(cricketerId);
        }
        // GET api/CricketerAPI/Detail/1111
        [HttpGet()]
        [Route("cricketerdetail/ODIStats/{cricketerId}")]
    public CricketerODIStatsViewModel GetODIStats(int cricketerId) {
            Cricketer cricketerDataLayer = new Cricketer();
            return cricketerDataLayer.ODIStats(cricketerId);
        }
        // GET api/CricketerAPI/Detail/1111
        [HttpGet()]
        [Route("cricketerdetail/TestStats/{cricketerId}")]
    public CricketerTestStatsViewModel GetTestStats(int cricketerId) {
        Cricketer cricketerDataLayer = new Cricketer();
        return cricketerDataLayer.TestStats(cricketerId);
    }
}

Now let’s work on Angular part like service, controller, module. 

Angular Service

app.service("CricketerService", function($http) {
    this.GetCricketers = function() {
        var req = $http.get('api/cricketer/getcricketers/');
        return req;
    }
    this.CricketerDetail = function(cricketerId) {
        var req = $http.get('api/cricketer/cricketerdetail/' + cricketerId);
        return req;
    }
    this.UpdateCricketer = function(cricketer) {
        var req = $http.post('api/cricketer/updatecricketer', cricketer);
        return req;
    }
    this.DeleteCricketer = function(Id) {
        var req = $http.get('api/cricketer/deletecricketer/' + Id + '');
        return req;
    }
    this.CricketerODIStats = function(cricketerId) {
        var req = $http.get('api/cricketer/cricketerdetail/ODIStats/' + cricketerId);
        return req;
    }
    this.CricketerTestStats = function(cricketerId) {
        var req = $http.get('api/cricketer/cricketerdetail/TestStats/' + cricketerId);
        return req;
    }
});

Angular Controller 

app.controller('CricketerController', function($scope, $uibModal, CricketerService, $window) {
    var serv = CricketerService.GetCricketers();
    serv.success(function(cricketers) {
        $scope.heading = "Cricketers List";
        $scope.displayMessage = false;
        $scope.cricketers = cricketers
    }).error(function(data, status, headers, config) {
        $scope.message = "Oops... something went wrong";
    });
    $scope.addCricketer = function() {
        var cricketer = {
            ID: 0,
            Name: "",
            ODI: "",
            Test: "",
            Update: false
        };
        $scope.editCricketer(cricketer);
    }
    $scope.editCricketer = function(cricketer) {
        var modalInstance = $uibModal.open({
            animation: $scope.animationsEnabled,
            templateUrl: 'cricketerModal.html',
            controller: 'CricController',
            resolve: {
                cricketer: function() {
                    return cricketer;
                }
            }
        });
        modalInstance.result.then(function(cricketer) {
            //alert(cricketer.Update);
            //If it was deleted
            if (!cricketer.Update) {
                $scope.cricketers.push(cricketer);
            }
        }, function() {
            //$log.info('Modal dismissed at: ' + new Date());
        });
    }
    $scope.deleteCricketer = function(Id, index) {
        if ($window.confirm("Are you sure you want to Delete this player?")) {
            var serv = CricketerService.DeleteCricketer(Id);
            serv.success(function() {
                $scope.cricketers.splice(index, 1);
            }).error(function(data, status, headers, config) {
                $scope.displayMessage = true;
                $scope.message = "Oops... something went wrong";
            });
        } else {}
    };
});
app.controller('CricController', function($scope, $uibModalInstance, $filter, CricketerService, cricketer) {
    //alert(cricketer.Update);
    $scope.displayMessage = false;
    $scope.cricketer = cricketer;
    // debugger;
    if (cricketer.Update) {
        $scope.updateButtonText = "Update";
        $scope.headingtext = "Edit";
    } else {
        $scope.updateButtonText = "Add";
        $scope.headingtext = "Add";
    }
    $scope.update = function() {
        if ($scope.validate()) {
            $scope.cricketer.Name = $scope.cricketer.Name;
            $scope.cricketer.ODI = $scope.cricketer.ODI;
            $scope.cricketer.Test = $scope.cricketer.Test;
            var serv = CricketerService.UpdateCricketer($scope.cricketer);
            serv.success(function(ID) {
                $scope.cricketer.Id = ID;
                //$scope.$parent.assessments.push($scope.assessment);
                $uibModalInstance.close($scope.cricketer);
            }).error(function(data, status, headers, config) {
                $scope.displayMessage = true;
                $scope.message = "Oops... something went wrong";
            });
        }
    }
    $scope.validate = function() {
        var valid = true;
        var errorMessage = "";
        if ($scope.cricketer.Name == "") {
            errorMessage += "-Name\n";
        }
        if ($scope.cricketer.ODI == "") {
            errorMessage += "-ODI\n";
        }
        if ($scope.cricketer.Test == "") {
            errorMessage += "-Test\n";
        }
        if (errorMessage != "") {
            errorMessage = "Please enter the following.\n" + errorMessage
                //alert(errorMessage);
            $scope.displayMessage = true;
            $scope.message = errorMessage;
            valid = false;
        }
        return valid;
    }
    $scope.cancel = function() {
        $uibModalInstance.dismiss('cancel');
    };
});
app.controller('DetailController', function($scope, CricketerService, $routeParams) {
    var serv = CricketerService.CricketerDetail($routeParams.id);
    serv.success(function(details) {
        $scope.displayMessage = false;
        $scope.detail = details;
    }).error(function(data, status, headers, config) {
        $scope.displayMessage = true;
        $scope.message = "Oops... something went wrong";
    });
    var odistats = CricketerService.CricketerODIStats($routeParams.id);
    odistats.success(function(stats) {
        $scope.displayMessage = false;
        $scope.ODIStats = stats;
    }).error(function(data, status, headers, config) {
        $scope.displayMessage = true;
        $scope.message = "Oops... something went wrong";
    });
    var teststats = CricketerService.CricketerTestStats($routeParams.id);
    teststats.success(function(stats) {
        $scope.displayMessage = false;
        $scope.TestStats = stats;
    }).error(function(data, status, headers, config) {
        $scope.displayMessage = true;
        $scope.message = "Oops... something went wrong";
    });
});
Angular App
var app = angular.module('cricketerapp', ['ngRoute', 'ngAnimate', 'ui.bootstrap'])
    //// configure routes
app.config(function($routeProvider) {
    debugger;
    $routeProvider.when('/', {
        templateUrl: 'Angular/Controllers/CricketerView.html',
        controller: 'CricketerController'
    }).when('/detail/:id', {
        templateUrl: 'Angular/Controller/DetailView.html',
        controller: 'DetailController'
    }).otherwise({
        redirectTo: '/'
    });
});

Let’s start work on view part now. Add a HTML page and provide the name of angular module and angular controller and add function of add, edit, detail and delete. When edit and add new item I am using bootstrap popup window. 

CricketerList.HTML

<!DOCTYPE html>
<html>

<head>
    <title></title>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0"> </head>

<body ng-app="cricketerapp" ng-controller="CricketerController">
    <div class="row" ng-show="displayMessage">
        <div class="panel panel-default">
            <div class="panel-heading"> {{message}} </div>
        </div>
    </div>
    <div>
        <h1>{{heading}}</h1>
        <div> <button type="submit" class="button" ng-click="addCricketer()">

<i class="fa fa-plus tc-btn"></i>    Add Cricketer

</button> </div>
    </div>
    <div><br /></div>
    <div>
        <div class="dataTable_wrapper">
            <table class="table table-striped table-bordered table-hover table-condensed table-responsive cell-border appointments" id="dataTables-Schedule">
                <tr>
                    <th ng-click="sort('Id')"> ID </th>
                    <th ng-click="sort('Name')"> Player Name </th>
                    <th ng-click="sort('ODI')"> Total ODI Played </th>
                    <th ng-click="sort('Test')"> Total Test Played </th>
                    <th></th>
                    <th></th>
                    <th></th>
                </tr>
                <tr ng-repeat="v in cricketers |orderBy:sortKey:reverse">
                    <td>{{v.Id}}</td>
                    <td> <a href="cricketer#/detail/{{v.Id}}">{{v.Name}}</a> </td>
                    <td>{{v.ODI}}</td>
                    <td>{{v.Test}}</td>
                    <td> <a href="#" ng-model="updateButtonText" ng-click="editCricketer(v)">Edit

</a> </td>
                    <td> <a href="cricketer#/detail/{{v.Id}}">

Detail

</a> </td>
                    <td> <a href="#" ng-click="deleteCricketer(v.Id,$index)" confirm="Are you sure you want to Delete this player?">Delete</a> </td>
                </tr>
            </table>
        </div>
    </div>
    <div ng-view></div>
    <script type="text/ng-template" id="cricketerModal.html">
        <h3>

{{headingtext}}

</h3>
        <div class="modal-body">
            <div class="row" ng-show="displayMessage">
                <div class="panel panel-default">
                    <div class="panel-heading"> {{message}} </div>
                </div>
            </div>
            <table>
                <tr>
                    <td class="bodyText10">Name</td>
                    <td class="bodyText10"> <input id="Text1" type="text" ng-model="cricketer.Name" /> </td>
                </tr>
                <tr>
                    <td class="bodyText10">ODI</td>
                    <td class="bodyText10"> <input type="text" class="input" ng-model="cricketer.ODI" /> </td>
                </tr>
                <tr>
                    <td class="bodyText10">Test</td>
                    <td class="bodyText10"> <input type="text" ng-model="cricketer.Test" /> </td>
                </tr>
            </table>
            <div class="modal-footer"> <button type="submit" class="btn-submit" ng-click="update()">

<i class="fa fa-pencil-square tc-btn"></i>    {{updateButtonText}}

</button> <button type="submit" class="btn-submit" ng-click="cancel()">

<i class="fa fa-remove tc-btn"></i>    Cancel

</button> </div>
        </div>
    </script>
</body>

</html>

Let’s see the output, 

When click on add button,

Click on Edit link,

When click on detail or name of player then we are loading overall stats and ODI stats and Text stats.

DetailView.html

<!DOCTYPE html>
<html>
<head>
    <title>Overall Stats</title><meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0"><meta charset="utf-8" />
</head>
<body>
    <div>
        <h4>Overall Stats</h4>
        <hr />
        <div ng-app="cricketerapp" ng-controller="CricketerController">
            <div class="row" ng-show="displayMessage">
                <div class="panel panel-default">
                    <div class="panel-heading">
                        {{message}}
                    </div>
                </div>
            </div>
            <div class="dataTable_wrapper">
                <table class="table table-striped table-bordered table-hover table-condensed table-responsive cell-border appointments" id="dataTables-Schedule">
                    <tr>
                        <th>
                            ID
                        </th>
                        <th>
                            Team
                        </th>
                        <th>
                            ODI Runs
                        </th>
                        <th>
                            Test Runs
                        </th>
                        <th>
                            Wickets
                        </th>
                    </tr>
                    <tr>
                        <td>{{detail.Cricketer_Id}}</td>
                        <td>{{detail.Team}}</td>
                        <td>{{detail.ODI_Runs}}</td>
                        <td>{{detail.Test_Runs}}</td>
                        <td>{{detail.Wickets}}</td>
                    </tr>
                </table>
            </div>
        </div>
    </div>

    <div>
        <h4>ODI Stats</h4>
        <hr />
        <div ng-app="cricketerapp" ng-controller="CricketerController">
            <div class="row" ng-show="displayMessage">
                <div class="panel panel-default">
                    <div class="panel-heading">
                        {{message}}
                    </div>
                </div>
            </div>
            <div class="dataTable_wrapper">
                <table class="table table-striped table-bordered table-hover table-condensed table-responsive cell-border appointments" id="dataTables-Schedule">
                    <tr>
                        <th>
                            ID
                        </th>
                        <th>
                            Name
                        </th>
                        <th>
                            Half Century
                        </th>
                        <th>
                            Century
                        </th>
                    </tr>
                    <tr>
                        <td>{{ODIStats.Cricketer_Id}}</td>
                        <td>{{ODIStats.Name}}</td>
                        <td>{{ODIStats.Half_Century}}</td>
                        <td>{{ODIStats.Century}}</td>
                    </tr>
                </table>
            </div>
        </div>
    </div>

    <div>
        <h4>Test Stats</h4>
        <hr />
        <div ng-app="cricketerapp" ng-controller="CricketerController">
            <div class="row" ng-show="displayMessage">
                <div class="panel panel-default">
                    <div class="panel-heading">
                        {{message}}
                    </div>
                </div>
            </div>
            <div class="dataTable_wrapper">
                <table class="table table-striped table-bordered table-hover table-condensed table-responsive cell-border appointments" id="dataTables-Schedule">
                    <tr>
                        <th>
                            ID
                        </th>
                        <th>
                            Name
                        </th>
                        <th>
                            Half Century
                        </th>
                        <th>
                            Century
                        </th>
                    </tr>
                    <tr>
                        <td>{{TestStats.Cricketer_Id}}</td>
                        <td>{{TestStats.Name}}</td>
                        <td>{{TestStats.Half_Century}}</td>
                        <td>{{TestStats.Century}}</td>
                    </tr>
                </table>
            </div>
        </div>
    </div>
</body>
</html>

Conclusion

In this article, we have learnt the crud operation using Web API and AngularJS in MVC with SQL Server database using entity framework. If you have any question or comments, you can download attached sample application or post me a comment in C# Corner comments section.