Learn MVC Using Angular Datatable

Introduction

In this article, we will learn MVC, using AngularJS data binding to the datatable from the Server side with the stored procedure, using Visual Studio 2015

Why use Angular Datatable?

Datatable is the most powerful table. It is best to load 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 & Server Side Pagination/Sorting.
Fixed And Fluid Height.

In this article, we are going to

Create a database.
Create a stored procedure.
Create MVC Application.
Using Angular Datatable.

Create a database

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

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, which is based on the book information.

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 given below.

Create a stored procedure

I have written the stored procedure for my data operations, so run SP given below. In this procedure, I have done Select, Insert, Update & Delete.

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 MVC Application

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

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

After creating the project, click the link given below and you can download the plug in files.

Subsequently, inject datatable key word in Angular modular.

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

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

<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 Model folder in Solution Explorer & create new class in Model folder.

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 home controller. LoadData displays the data in the Datatable & another one is using data manipulation.

[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 Web.config file.

<connectionStrings>
<add name="DbSqlCon" connectionString="Data Source=xxxx; Initial Catalog=test; User Id=sa; Password=XXX; connect timeout=0;" providerName="System.Data.SqlClient;" />
</connectionStrings>

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

Controller

$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

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 plug in files and JS file also.

Plug In

<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

<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

Here, I have done simple data manipulation method.

Kindly refer my AngularJS articles

Conclusion

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

19 Comments

Join the conversation! Your thoughts help the community grow.

Riddhi Valecha5y ago
Hello sir, I have implemented your code in my project, But I am getting error in angular - 1. Build:Namespace 'angular' has no exported member 'IServiceProvider'.2. Build:Namespace 'angular' has no exported member 'IPromise'. 3.File 'Plugin/angular-ui-router/angularjs/angular.d.ts' not found. Could you please help me out in these... It would be of great help.
Swapnil Awasthi8y ago
I have tried this code but its Not working.
Robert S.9y ago
Hello, I have a page in mvc with 2 dates and a button, in the first click the datatable works perfectly, but if I look for other dates, the datatable does not call the server. please help.
Sundaram Subramanian9y ago
Unable to launch the IIS Express Web server, this is the issue
Sundaram Subramanian9y ago
Bro I am facing an ISSUE, Unable to launch the IIS Express Web server. But I changed to local IIS, still facing the same issue. Please help.
- Thiruppathi R9y ago
  What is the issue? Send me a screenshot
Mukund Narayan Jha9y ago
Please upload demo with drop down list and radio button .
- Mukund Narayan Jha9y ago
  Save data of my form into database using angularjs in mvc with validation.if possible then please upload project for learning.
- Mukund Narayan Jha9y ago
  And with datepicker also.
- Mukund Narayan Jha9y ago
  No I want to create one form with Name,MobileNo,dropdownlist,radiobutton,fileuploader where dropdownlist bind data from database and
Prabu Elavarasan9y ago
Good One...Keep posting nice articles...
Anu V9y ago
Thanks for sharing.. nice article...
Shankar9y ago
Nice one! but seems conclusion is not relevant to the discussion.
- Thiruppathi R9y ago
  Thank you for your info.
Satyaprakash Samantaray9y ago
Nice one.........................
Manav Pandya9y ago
Nice one .............................