Learn MVC Using Angular Datatable

Introduction

In this article, we will learn how to use MVC, using Angular’s data binding functionality to create a datatable from the server side with the stored procedures.

Why Use Angular Datatable?

Datatable is great for loading a large amount of data. The table is designed to be extremely flexible and lightweight.

Some main features are given below:

Column Reordering.
Column Resizing.
Handle Large Data.
Client Side and Server Side Pagination/Sorting.
Fixed and Fluid Height.

In this article, we are going to:

Create a database.
Create a stored procedure.
Create an MVC Application.
Use Angular Datatable.

Create a Database

Open SQL Server 2016. Click New Query Window and run the query given below.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
USE [master]
 
GO
 
/****** Object:  Database [test]    Script Date: 5/7/2017 8:09:18 AM ******/
 
CREATE DATABASE [test]
 
 CONTAINMENT = NONE
 
 ON  PRIMARY
 
( NAME = N'test', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\test.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB ),
 
 FILEGROUP [DocFiles] CONTAINS FILESTREAM  DEFAULT
 
( NAME = N'FileStream', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\FileStream' , MAXSIZE = UNLIMITED)
 
 LOG ON
 
( NAME = N'test_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\test_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
 
GO
 
ALTER DATABASE [test] SET COMPATIBILITY_LEVEL = 130
 
GO
 
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
 
begin
 
EXEC [test].[dbo].[sp_fulltext_database] @action = 'enable'
 
end
 
GO
 
ALTER DATABASE [test] SET ANSI_NULL_DEFAULT OFF
 
GO
 
ALTER DATABASE [test] SET ANSI_NULLS OFF
 
GO
 
ALTER DATABASE [test] SET ANSI_PADDING OFF
 
GO
 
ALTER DATABASE [test] SET ANSI_WARNINGS OFF
 
GO
 
ALTER DATABASE [test] SET ARITHABORT OFF
 
GO
 
ALTER DATABASE [test] SET AUTO_CLOSE OFF
 
GO
 
ALTER DATABASE [test] SET AUTO_SHRINK OFF
 
GO
 
ALTER DATABASE [test] SET AUTO_UPDATE_STATISTICS ON
 
GO
 
ALTER DATABASE [test] SET CURSOR_CLOSE_ON_COMMIT OFF
 
GO
 
ALTER DATABASE [test] SET CURSOR_DEFAULT  GLOBAL
 
GO
 
ALTER DATABASE [test] SET CONCAT_NULL_YIELDS_NULL OFF
 
GO
 
ALTER DATABASE [test] SET NUMERIC_ROUNDABORT OFF
 
GO
 
ALTER DATABASE [test] SET QUOTED_IDENTIFIER OFF
 
GO
 
ALTER DATABASE [test] SET RECURSIVE_TRIGGERS OFF
 
GO
 
ALTER DATABASE [test] SET  DISABLE_BROKER
 
GO
 
ALTER DATABASE [test] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
 
GO
 
ALTER DATABASE [test] SET DATE_CORRELATION_OPTIMIZATION OFF
 
GO
 
ALTER DATABASE [test] SET TRUSTWORTHY OFF
 
GO
 
ALTER DATABASE [test] SET ALLOW_SNAPSHOT_ISOLATION OFF
 
GO
 
ALTER DATABASE [test] SET PARAMETERIZATION SIMPLE
 
GO
 
ALTER DATABASE [test] SET READ_COMMITTED_SNAPSHOT OFF
 
GO
 
ALTER DATABASE [test] SET HONOR_BROKER_PRIORITY OFF
 
GO
 
ALTER DATABASE [test] SET RECOVERY FULL
 
GO
 
ALTER DATABASE [test] SET  MULTI_USER
 
GO
 
ALTER DATABASE [test] SET PAGE_VERIFY CHECKSUM
 
GO
 
ALTER DATABASE [test] SET DB_CHAINING OFF
 
GO
 
ALTER DATABASE [test] SET FILESTREAM( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'DocFileDirctory' )
 
GO
 
ALTER DATABASE [test] SET TARGET_RECOVERY_TIME = 60 SECONDS
 
GO
 
ALTER DATABASE [test] SET DELAYED_DURABILITY = DISABLED
 
GO
 
EXEC sys.sp_db_vardecimal_storage_format N'test', N'ON'
GO
ALTER DATABASE [test] SET QUERY_STORE = OFF
GO
Create a Table
I will create a new table, with the information given below:
 
CREATE TABLE [dbo].[BookMaster](
 
    [BookCode] [varchar](10) NULL,
 
    [BookName] [varchar](50) NULL,
 
    [BookDesc] [varchar](500) NULL,
 
    [BookAuthor] [varchar](50) NULL
 
) ON [PRIMARY]
 
GO

After creating the table, add some data, as shown below:

MVC

Create a Stored Procedure

I have written the stored procedure for my data operations, so run SP as given below. In this procedure, I have created Done Select, Insert, Update, and Delete functionalities.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
Create PROCEDURE [dbo].[BookMaster_SP]
@BookCode   VARCHAR(10)=NULL,
@BookName   VARCHAR(50)=NULL,
@BookDesc   VARCHAR(500)=NUL,
@BookAutor  VARCHAR(500)=NULL,
@Mode       VARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;
    IF (@Mode='ADD')
    BEGIN
        INSERT INTO BookMaster(BookCode,BookName,BookDesc,BookAuthor)
 
           VALUES(@BookCode,@BookName,@BookDesc,@BookAutor)
    END
    IF (@Mode='EDIT')
    BEGIN
        UPDATE BookMaster SET BookName=@BookName,BookDesc=@BookDesc,BookAuthor=@BookAutor WHERE BookCode=@BookCode
    END
    IF (@Mode='DELETE')
    BEGIN
        DELETE FROM BookMaster WHERE BookCode=@BookCode
    END
    IF (@Mode='GET')
    BEGIN
        SELECT BookCode,BookName,BookDesc,BookAuthor FROM BookMaster
 
    END
    IF (@Mode='GETID')
    BEGIN
        SELECT BookCode,BookName,BookDesc,BookAuthor FROM BookMaster WHERE BookCode=@BookCode
 
    END
    SET NOCOUNT OFF;
END

Create an MVC Application

Open Visual Studio 2015, go to Menu New->New project ->select Visual C# under templates-> Choose ASP.NET Web Application.

MVC

Once you click the OK button, one more Window opens. Select MVC.

MVC

After creating the project, click the link given below and download the plugin files.

Subsequently, inject the datatable keyword in the Angular module.

angular.module('uiroute',['ui.router', 'datatables']);

Create and design an HTML page with the table. Mention datatable=ng. Now, bind the Server data.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
<table datatable="ng" class="table-responsive table-bordered table-striped ">
            <thead style="background-color:#428bca;color:white" >
                <tr>
                    <td >
                        Book Code
                    </td>
                    <td >
                        Book Name
                    </td>
                    <td >
                        Book Description
                    </td>
                    <td>
                        Book Author Name
                    </td>
                    <td>
                        <span class="glyphicon glyphicon-edit"></span>
                    </td>
                    <td>
                        <span class="glyphicon glyphicon-trash"></span>
                    </td>
                </tr>
            </thead>
            <tbody>
                <tr ng-repeat="model in LoadData">
                    <td >{{ model.BookCode }}</td>
                    <td >{{ model.BookName }}</td>
                    <td>{{ model.BookDesc }}</td>
                    <td>{{ model.BookAuthor }}</td>
                    <td><span class="glyphicon glyphicon-edit" ng-click="LoadById(model)"></span></td>
                    <td><span class="glyphicon glyphicon-trash" ng-click="DaleteById(model)"></span></td>
                </tr>
            </tbody>
        </table>

Using Angular Datatable

Create a Model folder in Solution Explorer and create a new class in the Model folder.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
public class BookModel
 
    {
 
        public string BookCode { get; set; }
 
        public string BookName { get; set; }
 
        public string BookDesc{ get; set; }
 
        public string BookAuthor { get; set; }
 
        public string Mode { get; set; }
 
    }

Write the method given below in your home controller. LoadData displays the data in the Datatable, and another one is using data manipulation.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
[HttpPost]
 
        #region LoadData
 
        public JsonResult LoadData(BookModel Param)
 
        {
 
            List<BookModel> BookList = new List<BookModel>();
 
            using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["DbSqlCon"].ConnectionString))
 
            {
 
                var cmd = new SqlCommand("BookMaster_SP", con);
 
                cmd.CommandType = CommandType.StoredProcedure;
 
                cmd.Parameters.Add(new SqlParameter("@Mode", SqlDbType.VarChar)).Value = Param.Mode;
 
                cmd.Parameters.Add(new SqlParameter("@BookCode", SqlDbType.VarChar)).Value = Param.BookCode;
 
                try
 
                {
 
                   con.Open();
 
                    using (SqlDataReader DbReader = cmd.ExecuteReader())
 
                        if (DbReader.HasRows)
 
                        {
 
                            while (DbReader.Read())
 
                            {
 
                                BookModel Books = new BookModel();
 
                                Books.BookCode = DbReader.GetString(DbReader.GetOrdinal("BookCode"));
 
                                Books.BookName = DbReader.GetString(DbReader.GetOrdinal("BookName"));
 
                                Books.BookDesc = DbReader.GetString(DbReader.GetOrdinal("BookDesc"));
 
                                Books.BookAuthor = DbReader.GetString(DbReader.GetOrdinal("BookAuthor"));
 
                                BookList.Add(Books);
 
                            }
 
                        }
 
                    return Json(BookList, JsonRequestBehavior.AllowGet);
 
                }
 
                finally
 
                {
 
                        con.Close();
 
                }
 
            }
 
        }
 
        #endregion
 
 
        [HttpPost]
 
        #region EditData
 
        public string EditData(BookModel Param)
 
        {
 
            if (Param != null)
 
            {
 
                using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["DbSqlCon"].ConnectionString))
 
                {
 
                    var cmd = new SqlCommand("BookMaster_SP", con);
 
                    cmd.CommandType = CommandType.StoredProcedure;
 
                    cmd.Parameters.Add(new SqlParameter("@Mode", SqlDbType.VarChar)).Value = Param.Mode;
 
                    cmd.Parameters.Add(new SqlParameter("@BookCode", SqlDbType.VarChar)).Value = Param.BookCode;
 
                    cmd.Parameters.Add(new SqlParameter("@BookName", SqlDbType.VarChar)).Value = Param.BookName;
 
                    cmd.Parameters.Add(new SqlParameter("@BookDesc", SqlDbType.VarChar)).Value = Param.BookDesc;
 
                    cmd.Parameters.Add(new SqlParameter("@BookAutor", SqlDbType.VarChar)).Value = Param.BookAuthor;
 
 
                    try
 
                    {
 
                        con.Open();
 
                        cmd.ExecuteNonQuery();
 
                        return "Success";
 
                    }
 
                    catch (Exception ex)
 
                    {
 
                        return ex.ToString();
 
                    }
 
                    finally
 
                    {
 
                        if (con.State != ConnectionState.Closed)
 
                            con.Close();
 
 
                    }
 
 
                }
 
            }
 
 
            else
 
            {
                return "Model Error";
            }
        }
        #endregion

Declare the connection string in your Web.config file.

1
2
3
<span class="cm-tag bracket">&lt;</span><span class="cm-tag">connectionStrings</span><span class="cm-tag bracket">&gt;
</span><span class="cm-tag bracket">&lt;</span><span class="cm-tag">add</span> <span class="cm-attribute">name</span>=<span class="cm-string">"DbSqlCon"</span> <span class="cm-attribute">connectionString</span>=<span class="cm-string">"Data Source=xxxx; Initial Catalog=test; User I
</span><span class="cm-tag bracket">&lt;/</span><span class="cm-tag">connectionStrings</span><span class="cm-tag bracket">&gt;</span>

Create an Angular Controller and Service to get the data from the Server side.

Controller

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
$scope.LoadById = function (model)
    {
        $scope.Ed = model;
        $scope.Datamode = 'Update';
        $scope.Enable = true;
    }
 
    $scope.Save = function ()
    {
        if ($scope.Datamode === 'Update')
        {
            var Param = {
                Mode: 'EDIT',
                BookCode: $scope.Ed.BookCode,
                BookName: $scope.Ed.BookName,
                BookDesc: $scope.Ed.BookDesc,
                BookAuthor: $scope.Ed.BookAuthor
            }
        }
        
        else
        {
            var Param = {
                Mode: 'ADD',
                BookCode: $scope.Ed.BookCode,
                BookName: $scope.Ed.BookName,
                BookDesc: $scope.Ed.BookDesc,
                BookAuthor: $scope.Ed.BookAuthor
            }
        }
        var ServiceData = BookService.EditData(Param);
        ServiceData.then(function (result) {
            $scope.loadTable()
            $scope.Ed = '';         
            $scope.message = "Data Save Successfully";
        }, function () {
 
        });
    }
 
    $scope.DaleteById = function (model) {
        var Param = {
            Mode: 'DELETE',
            BookCode: model.BookCode,
        }
        var ServiceData = BookService.EditData(Param);
        ServiceData.then(function (result) {
            $scope.loadTable()
            $scope.Ed = '';
            $scope.message = "Data Delete Successfully";
        }, function () {
 
        });

Service

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
this.loadGrid = function (Param) {
        var response = $http({
            method: "post",
            url: "Home/LoadData",
            data: JSON.stringify(Param),
            dataType: "json"
        });
        return response;
    }
 
    this.EditData = function (Param) {
        var response = $http({
            method: "post",
            url: "Home/EditData",
            data: JSON.stringify(Param),
            dataType: "json"
        });
        return response;
    }

Do not forget to refer the plugin files and JS file also.

Plugin

1
2
3
<link href="~/Plugin/datatables/media/css/jquery.dataTables.min.css" rel="stylesheet" />
<script src="~/Plugin/datatables/media/js/jquery.dataTables.js"></script>
<script src="~/Plugin/angular-datatables/dist/angular-datatables.js"></script>

My Files

1
2
3
<script src="~/App/App.module.js"></script>
<script src="~/App/App.config.js"></script>
<script src="~/App/BookController.js"></script>

Once you are done with the process given above, your datatable is ready to load. Thus, run the application.

Output

MVC

Conclusion

In this article, we have learned some MVC, using Angular Datatable. If you have any queries, please tell me through the comments section.

Happy Coding!

Author Profile

Thiruppathi Rengasamy

I 'm a .Net Developer & Blogger. And also I'm working as senior software engineer in Microsoft Technologies for 5+ Years.I would like to spend my precious time in writing articles, research in .Net technologies and to learn new technologies.

Latest entries

Angular2018.08.19Angular Console UI For Developer
Azure2018.08.19Learn About Azure SQL Server Using VS Code
ASP.NET MVC2018.07.17Azure Web App Deployment Using Visual Studio
Azure2018.07.12Creating & managing Azure App Service Plans

Dotnet Spices

Azure Web App Deployment Using Visual Studio

Learn ASP.NET MVC Using Angular 5

Learn MVC Using Angular Dynamic Localization

Learn MVC Using Angular Dynamic Control In DataTables

Learn About Azure SQL Server Using VS Code

Azure Web App Deployment Using Visual Studio

Creating & managing Azure App Service Plans

Learn About Azure Resource Group

Simple Cheque Maintenance Application

Microsoft – SQL Operations Studio

Learn AWS With MS-SQL(DB) Using SQL Client

Convert Table Column Into C# Model Class

Learn WPF Using Google Place API

Learn WPF CRUD Operations Using VB.NET

Learn Web API Using WPF, WebForms And Xamarin

Apply ComboBox Selected Color To The Form’s Background Color In WPF

Learn ASP.NET Core WEB API With Angular 5 Using ngFor

Learn MVC Using Angular and Web API and Deploy to IIS

How to Use the SQL Helper Class to Create Web APIs