A Simple CRUD application in AngularJS using $http and Web API

Here in this post I’ll show how to create a simple CRUD application in AngularJS using Web API (for data manipulation) and $http. We’ll do simple operations like insert, update and delete in AngularJS, with data in an SQL Server database.

Perform a Simple CRUD Operation in AngularJS with Web API

Are you new to AngularJS? This 5 minutes AngularJS tutorial will help create your first AngularJS Application.

Note: This article has code in both C# and Vb.

Create a Table in SQL Server

You will first create a table that will hold the data and will help us do the CRUD operation. The table is dbo.Books.

CREATE TABLE [dbo].[Books](
    [BookID] [int] IDENTITY(1,1) NOT NULL,
    [BookName] [varchar](50) NULL,
    [Category] [varchar](50) NULL,
    [Price] [numeric](18, 2) NULL
    PRIMARY KEY CLUSTERED ( [BookID] ASC )
) ON [PRIMARY]

The table has just four columns. The first (the BookID) is of type IDENTITY, that is, it will create a unique id for each book when you insert a new data.

The Web API

I am using Asp.Net MVC 4 to create my Web API that is the Models and Controllers. I’ll create a Books model, along with a controller.

Model “Books.cs”

using System;

namespace BooksApp.Models
{
    public class Books
    {
        public int BookID { get; set; }
        public string BookName { get; set; }
        public string Category { get; set; }
        public decimal Price { get; set; }
        public string Operation { get; set; }
    }
}

Model “Books.vb”

Imports System.Web

Namespace BooksApp.Models
    Public Class Books
        Public Property BookID() As Integer
            Get
                Return m_BookID
            End Get
            Set(value As Integer)
                m_BookID = value
            End Set
        End Property
        Private m_BookID As Integer

        Public Property BookName() As String
            Get
                Return m_BookName
            End Get
            Set(value As String)
                m_BookName = value
            End Set
        End Property
        Private m_BookName As String

        Public Property Category() As String
            Get
                Return m_Category
            End Get
            Set(value As String)
                m_Category = value
            End Set
        End Property
        Private m_Category As String

        Public Property Price() As Decimal
            Get
                Return m_Price
            End Get
            Set(value As Decimal)
                m_Price = value
            End Set
        End Property
        Private m_Price As Decimal

        Public Property Operation() As String
            Get
                Return m_Ops
            End Get
            Set(value As String)
                m_Ops = value
            End Set
        End Property
        Private m_Ops As String
    End Class
End Namespace

After you have successfully created the model in Web API MVC 4, create the controller now.

Note: If you are new to Web API, I would suggest you to click the below link to learn the basic procedure of creating a Web API from scratch.

👉 A basic Asp.Net Web API Example – AutoComplete Textbox using jQuery and Web API

Controller “BooksController.cs” (For C#)

using System;
using System.Collections.Generic;
using System.Net;
using System.Net.http;
using System.Web.http;

using BooksApp.Models;
using System.Data.SqlClient;

namespace BooksApp.Controllers
{
    public class BooksController : ApiController
    {
        const string sConnString = "Data Source=DNA;Persist Security Info=False;" +
            "Initial Catalog=DNA_Classified;User Id=sa;Password=;Connect Timeout=30;";

        // LIST OBJECT WILL HOLD AND RETURN A LIST OF BOOKS.
        List<Books> MyBooks = new List<Books>();

        [HttpPost()]
        public IEnumerable<Books> Perform_CRUD(Books list) 
        {
            bool bDone = false;

            using (SqlConnection con = new SqlConnection(sConnString))
            {
                using (SqlCommand cmd = new SqlCommand("SELECT *FROM dbo.Books"))
                {
                    cmd.Connection = con;
                    con.Open();

                    switch (list.Operation)
                    {
                        case "SAVE":
                            if (list.BookName != "" & list.Category != "" & list.Price > 0)
                            {
                                cmd.CommandText = "INSERT INTO dbo.Books (BookName, Category, Price) " +
                                    "VALUES (@BookName, @Category, @Price)";

                                cmd.Parameters.AddWithValue("@BookName", list.BookName.Trim());
                                cmd.Parameters.AddWithValue("@Category", list.Category.Trim());
                                cmd.Parameters.AddWithValue("@Price", list.Price);

                                bDone = true;
                            }
                            break;

                        case "UPDATE":
                            if (list.BookName != "" & list.Category != "" & list.Price > 0)
                            {
                                cmd.CommandText = "UPDATE dbo.Books SET BookName = @BookName, Category = @Category, " + 
                                    "Price = @Price WHERE BookID = @BookID";

                                cmd.Parameters.AddWithValue("@BookName", list.BookName.Trim());
                                cmd.Parameters.AddWithValue("@Category", list.Category.Trim());
                                cmd.Parameters.AddWithValue("@Price", list.Price);
                                cmd.Parameters.AddWithValue("@BookID", list.BookID);

                                bDone = true;
                            }
                            break;

                        case "DELETE":
                            cmd.CommandText = "DELETE FROM dbo.Books WHERE BookID = @BookID";
                            cmd.Parameters.AddWithValue("@BookID", list.BookID);

                            bDone = true;
                            break;
                    }

                    if (bDone)
                    {
                        cmd.ExecuteNonQuery();
                    }

                    con.Close();
                }
            }

            if (bDone)
            {
                GetData();
            }
            return MyBooks;
        }

        // EXTRACT ALL TABLE ROWS AND RETURN DATA TO THE CLIENT APP.
        private void GetData()
        {
            using (SqlConnection con = new SqlConnection(sConnString))
            {
                SqlCommand objComm = new SqlCommand("SELECT *FROM dbo.Books ORDER BY BookID DESC", con);
                con.Open();

                SqlDataReader reader = objComm.ExecuteReader();

                // POPULATE THE LIST WITH DATA.
                while (reader.Read())
                {
                    MyBooks.Add(new Books
                    {
                        BookID = Convert.ToInt32(reader["BookID"]),
                        BookName = reader["BookName"].ToString(),
                        Category = reader["Category"].ToString(),
                        Price = Convert.ToDecimal(reader["Price"])
                    });
                }

                con.Close();
            }
        }
    }
}

What this Controller do?

The controller is very basic. I’ve got two methods, one public method, which is called from a client using http POST. The second is a private procedure, which returns all the rows in the books table. The first method is common for all operations, that is insert, update and delete.

Controller “BooksController.vb” (For Visual Basic)

Option Explicit On

Imports System.Net.http
Imports System.Web.http

Imports System.Data.SqlClient
Imports BooksApp.BooksApp.Models

Namespace BooksApp
    Public Class BooksController
        Inherits ApiController

        Const sConnString As String = "Data Source=DNA;Persist Security Info=False;" & _
            "Initial Catalog=DNA_Classified;User Id=sa;Password=;Connect Timeout=30;"

        ' LIST OBJECT WILL HOLD AND RETURN A LIST OF BOOKS.
        Dim MyBooks As New List(Of Books)()

        <HttpPost()> _
        Public Function Perform_CRUD(list As Books) As IEnumerable(Of Books)

            Dim bDone As Boolean = False

            Using con As SqlConnection = New SqlConnection(sConnString)
                Using cmd As SqlCommand = New SqlCommand("SELECT *FROM dbo.Books")
                    With cmd
                        .Connection = con
                        con.Open()

                        Select Case list.Operation
                            Case "SAVE"
                                If Trim(list.BookName) <> "" And Trim(list.Category) <> "" And
                                    Val(list.Price) > 0 Then

                                    .CommandText = "INSERT INTO dbo.Books (BookName, Category, Price) " & _
                                        "VALUES (@BookName, @Category, @Price)"

                                    .Parameters.AddWithValue("@BookName", Trim(list.BookName))
                                    .Parameters.AddWithValue("@Category", Trim(list.Category))
                                    .Parameters.AddWithValue("@Price", list.Price)

                                    bDone = True
                                End If

                            Case "UPDATE"
                                If Trim(list.BookName) <> "" And Trim(list.Category) <> "" And
                                    Val(list.Price) > 0 Then

                                    .CommandText = "UPDATE dbo.Books SET BookName = @BookName, Category = @Category, " & _
                                        "Price = @Price WHERE BookID = @BookID"

                                    .Parameters.AddWithValue("@BookName", Trim(list.BookName))
                                    .Parameters.AddWithValue("@Category", Trim(list.Category))
                                    .Parameters.AddWithValue("@Price", Val(list.Price))
                                    .Parameters.AddWithValue("@BookID", Val(list.BookID))

                                    bDone = True
                                End If

                            Case "DELETE"
                                .CommandText = "DELETE FROM dbo.Books WHERE BookID = @BookID"
                                .Parameters.AddWithValue("@BookID", Val(list.BookID))

                                bDone = True
                        End Select

                        If bDone Then
                            .ExecuteNonQuery()
                        End If

                        con.Close()
                    End With
                End Using
            End Using

            If bDone Then
                GetData()
                Return MyBooks
            End If
        End Function

        ' EXTRACT ALL TABLE ROWS AND RETURN DATA TO THE CLIENT APP.
        Private Sub GetData()
            Using con As SqlConnection = New SqlConnection(sConnString)
                Dim objComm As New SqlCommand("SELECT *FROM dbo.Books ORDER BY BookID DESC", con)
                con.Open()

                Dim reader As SqlDataReader = objComm.ExecuteReader()

                ' POPULATE THE LIST WITH DATA.
                While reader.Read()
                    MyBooks.Add(New Books() With { _
                        .BookID = CInt(reader("BookID")), _
                        .BookName = reader("BookName").ToString(), _
                        .Category = reader("Category").ToString(), _
                        .Price = CDbl(reader("Price")) _
                     })
                End While

                con.Close()
            End Using
        End Sub
    End Class
End Namespace

We are now done with our backend. Let’s now create a small AngularJS frontend Application.

The CSS

I have added some basic styles to the elements in the Markup section. This will keep the controls aligned and organized.

<!DOCTYPE html>
<html>
<head>
    <title>CRUD Operation in AngularJS</title>
    <script src="https://ajax.googleapis.com/ajax/libs/angularjs/1.4.5/angular.min.js"></script>
    <style>
        div {
            width:600px;
            text-align:center;
        }
        ul {
            list-style:none;
            margin:5px; padding:0;
            display:inline-block;
        }
        li {
            border:none;
            background:#FFF;
        }
        input, select {
            font:15px Calibri;
            width:170px;
            padding:3px;
        }
        button {
            margin:5px;
        }
        table, th, td {
            font:15px Calibri;
            border:solid 1px #CCC;
            width:600px;
        }
        th {font-weight:bold;}
    </style>
</head>

The Body (Model)

<body>
    <div ng-app="myApp" 
        ng-controller="myController"
        ng-init="myList=[
            { category:'Business' },
            { category:'Computers' },
            { category:'Programming' },
            { category:'Science' }]">

        <div>
            <!--BASIC FORM CONTROLS-->
            <label>{{bookid}}</label>
            <ul>
                <li><input name="bookname" type="text" 
                    ng-model="bookname" placeholder="Enter the Book Name" /></li>
            </ul>
            <ul>
                <li>
                    <select name="category" ng-model="category" 
                        ng-options="c.category as c.category for c in myList">
                        <option value="">-- Select a Category --</option>
                    </select>
                </li>
            </ul>
            <ul>
                <li><input name="price" type="number" 
                    ng-model="price" placeholder="Enter the Price" /></li>
            </ul>
 
            <!--TWO BUTTON CONTROLS-->
            <p>
                <button id="save" ng-disabled="isDisabled" ng-click="save()">Save My Book</button>
                <button id="update" ng-click="update()">Update My Book</button>
            </p>

            <hr />

            <!-- A TABLE ELEMENT TO SHOW ALL THE ENTRIES. -->
            <table>
                <tr>
                    <th>ID</th>
                    <th>Book Name</th>
                    <th>Category</th>
                    <th>Price</th>
                </tr>
                <tr ng-repeat="myBooks in arrBooks" ng-click="edit(myBooks)">
                    <td>{{ myBooks.BookID }}</td>
                    <td>{{ myBooks.BookName }}</td>
                    <td>{{ myBooks.Category }}</td>
                    <td>{{ myBooks.Price }}</td>

                    <td><button id="delete" ng-click="del(myBooks)">Delete</button></td>
                </tr>
            </table>
        </div>
    </div>
</body>

The Model comprises of few elements, such as, textboxes, buttons and an HTML5 <select> element with a dropdown list. Along with it, I have a table that will show the newly added rows in the SQL Server data.

The Controller (Script)

<script>
    var myApp = angular.module('myApp', []);

    myApp.controller('myController',
        function ($scope, $http) {

            $scope.save = function () {
                Perform_CRUD('SAVE', 0)
                ClearInput();
            };

            $scope.edit = function (myBook) {
                $scope.bookid = myBook.BookID;
                $scope.bookname = myBook.BookName;
                $scope.category = myBook.Category;
                $scope.price = myBook.Price;

                $scope.isDisabled = true;
            };

            $scope.update = function () {
                Perform_CRUD('UPDATE', $scope.bookid);
                $scope.isDisabled = false;

                ClearInput();
            };

            $scope.del = function (myBook) {
                Perform_CRUD('DELETE', myBook.BookID)
            };

            function Perform_CRUD(ops, id) {
                var request = {
                    method: 'post',
                    url: '/api/books/',
                    data: {
                        BookID: id,
                        BookName: $scope.bookname,
                        Category: $scope.category,
                        Price: $scope.price,
                        Operation: ops
                    },
                    dataType: 'json',
                    contentType: "application/json"
                };

                // POST DATA WITH $http.
                $http(request)
                    .success(function (data) {

                        var i = 0;      // JUST A COUNTER.
                        $scope.arrBooks = new Array;

                        // LOOP THROUGH EACH DATA.
                        angular.forEach(data, function () {
                            var b = {
                                BookID: data[i].BookID,
                                BookName: data[i].BookName,
                                Category: data[i].Category,
                                Price: data[i].Price
                            };

                            $scope.arrBooks.push(b);    // ADD DATA TO THE ARRAY.
                            i += 1;
                        });
                    })
                    .error(function () {...});
            };

            // CLEAR ALL INPUT FIELDS AFTER EVERY OPERATION.
            function ClearInput() {
                $scope.bookid = '';
                $scope.bookname = '';
                $scope.category = '';
                $scope.price = '';
            }
        });
</script>
</html>

I am posting the data using AngularJS $http service. The data is serialized using JSON and posted to the Web API controller. The method I have used is post and I have mentioned the dataType too. Once, the API receives and processes the data successfully it will return the entire SQL Server table’s row to display.

👉 Here's another post you may like: How to create a simple CRUD application in Angular 6 using Web API

I also have four $scope functions inside my controller that will perform insert (or save), edit, update and delete functions. Each method will extract data for an operation and call the Perform_CRUD() function, which will POST the data to the API.

That’s it. If you like this tuturial and its example useful, then please share it with your friends. In-addition, if you have any queries, leave a message below.

← PreviousNext →