Post
Ask Question

Learn MVC Using Angular Dynamic Control In DataTable

MVC

Introduction

In this article, we will learn MVC using Angular data binding for dynamic control in datatable from server side Web API using visual studio 2017

In this article we are going to

  • Create Database
  • Create Store procedure
  • Create MVC Application
  • Using Angular Datatable Dynamic Control

Create Database

Open SQL Server 2016, Then Click “New Query” window & Run the below query. 

  1. USE [master]  
  2. GO  
  3. CREATE DATABASE [test]  
  4.  CONTAINMENT = NONE  
  5.  ON  PRIMARY   
  6. ( NAME = N'test', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\test.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB ),   
  7.  FILEGROUP [DocFiles] CONTAINS FILESTREAM  DEFAULT  
  8. ( NAME = N'FileStream', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\FileStream' , MAXSIZE = UNLIMITED)  
  9.  LOG ON   
  10. ( 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 )  
  11. GO  
  12. ALTER DATABASE [test] SET COMPATIBILITY_LEVEL = 130  
  13. GO  
  14. IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))  
  15. begin  
  16. EXEC [test].[dbo].[sp_fulltext_database] @action = 'enable'  
  17. end  
  18. GO  
  19. ALTER DATABASE [test] SET ANSI_NULL_DEFAULT OFF   
  20. GO  
  21. ALTER DATABASE [test] SET ANSI_NULLS OFF   
  22. GO  
  23. ALTER DATABASE [test] SET ANSI_PADDING OFF   
  24. GO  
  25. ALTER DATABASE [test] SET ANSI_WARNINGS OFF   
  26. GO  
  27. ALTER DATABASE [test] SET ARITHABORT OFF   
  28. GO  
  29. ALTER DATABASE [test] SET AUTO_CLOSE OFF   
  30. GO  
  31. ALTER DATABASE [test] SET AUTO_SHRINK OFF   
  32. GO  
  33. ALTER DATABASE [test] SET AUTO_UPDATE_STATISTICS ON   
  34. GO  
  35. ALTER DATABASE [test] SET CURSOR_CLOSE_ON_COMMIT OFF   
  36. GO  
  37. ALTER DATABASE [test] SET CURSOR_DEFAULT  GLOBAL   
  38. GO  
  39. ALTER DATABASE [test] SET CONCAT_NULL_YIELDS_NULL OFF   
  40. GO  
  41. ALTER DATABASE [test] SET NUMERIC_ROUNDABORT OFF   
  42. GO  
  43. ALTER DATABASE [test] SET QUOTED_IDENTIFIER OFF   
  44. GO  
  45. ALTER DATABASE [test] SET RECURSIVE_TRIGGERS OFF   
  46. GO  
  47. ALTER DATABASE [test] SET  DISABLE_BROKER   
  48. GO  
  49. ALTER DATABASE [test] SET AUTO_UPDATE_STATISTICS_ASYNC OFF   
  50. GO  
  51. ALTER DATABASE [test] SET DATE_CORRELATION_OPTIMIZATION OFF   
  52. GO  
  53. ALTER DATABASE [test] SET TRUSTWORTHY OFF   
  54. GO  
  55. ALTER DATABASE [test] SET ALLOW_SNAPSHOT_ISOLATION OFF   
  56. GO  
  57. ALTER DATABASE [test] SET PARAMETERIZATION SIMPLE   
  58. GO  
  59. ALTER DATABASE [test] SET READ_COMMITTED_SNAPSHOT OFF   
  60. GO  
  61. ALTER DATABASE [test] SET HONOR_BROKER_PRIORITY OFF   
  62. GO  
  63. ALTER DATABASE [test] SET RECOVERY FULL   
  64. GO  
  65. ALTER DATABASE [test] SET  MULTI_USER   
  66. GO  
  67. ALTER DATABASE [test] SET PAGE_VERIFY CHECKSUM    
  68. GO  
  69. ALTER DATABASE [test] SET DB_CHAINING OFF   
  70. GO  
  71. ALTER DATABASE [test] SET FILESTREAM( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'DocFileDirctory' )   
  72. GO  
  73. ALTER DATABASE [test] SET TARGET_RECOVERY_TIME = 60 SECONDS   
  74. GO  
  75. ALTER DATABASE [test] SET DELAYED_DURABILITY = DISABLED   
  76. GO  
  77. EXEC sys.sp_db_vardecimal_storage_format N'test', N'ON'  
  78. GO  
  79. ALTER DATABASE [test] SET QUERY_STORE = OFF  
  80. GO   

Create Table

I will create a new table based on employee info. 

  1. CREATE TABLE [dbo].[EmpMaster](  
  2.     [Row_id] [numeric](18, 0) IDENTITY(1,1) NOT NULL,  
  3.     [Emp_Code] [varchar](10) NULL,  
  4.     [Emp_FName] [varchar](50) NULL,  
  5.     [Emp_LName] [varchar](50) NULL,  
  6.     [Emp_Status] [bit] NULL,  
  7.     [Emp_DOB] [datetime] NULL,  
  8.     [Emp_Maritalstatus] [varchar](10) NULL,  
  9.     [Emp_Role] [varchar](50) NULL,  
  10.     [Emp_Department] [varchar](50) NULL,  
  11.     [Emp_Address] [varchar](500) NULL,  
  12.     [Emp_Profilestatus] [int] NULL,  
  13.     [Emp_Expriance] [int] NULL,  
  14.     [Create_By] [varchar](50) NULL,  
  15.     [Create_Date] [datetime] NULL  
  16. ) ON [PRIMARY]   

After creating the table add some data 

  1. SET IDENTITY_INSERT [dbo].[EmpMaster] ON   
  2. GO  
  3. INSERT [dbo].[EmpMaster] ([Row_id], [Emp_Code], [Emp_FName], [Emp_LName], [Emp_Status], [Emp_DOB], [Emp_Maritalstatus], [Emp_Role], [Emp_Department], [Emp_Address], [Emp_Profilestatus], [Emp_Expriance], [Create_By], [Create_Date]) VALUES (CAST(1 AS Numeric(18, 0)), N'1000', N'Amit ', N'Sharma', 1, CAST(N'1958-04-20T00:00:00.000' AS DateTime), N'Married', N'Admin', N'Dev', N'California', 100, 20, N'Thiru', CAST(N'2017-07-24T00:00:00.000' AS DateTime))  
  4. GO  
  5. INSERT [dbo].[EmpMaster] ([Row_id], [Emp_Code], [Emp_FName], [Emp_LName], [Emp_Status], [Emp_DOB], [Emp_Maritalstatus], [Emp_Role], [Emp_Department], [Emp_Address], [Emp_Profilestatus], [Emp_Expriance], [Create_By], [Create_Date]) VALUES (CAST(2 AS Numeric(18, 0)), N'2000', N'Erik ', N'Dietrich', 0, CAST(N'1988-05-10T00:00:00.000' AS DateTime), N'Married', N'Employee', N'Dev', N'Washington', 50, 10, N'Thiru', CAST(N'2017-07-24T00:00:00.000' AS DateTime))  
  6. GO  
  7. INSERT [dbo].[EmpMaster] ([Row_id], [Emp_Code], [Emp_FName], [Emp_LName], [Emp_Status], [Emp_DOB], [Emp_Maritalstatus], [Emp_Role], [Emp_Department], [Emp_Address], [Emp_Profilestatus], [Emp_Expriance], [Create_By], [Create_Date]) VALUES (CAST(3 AS Numeric(18, 0)), N'3000', N'Abdul ', N'Azeez', 1, CAST(N'1990-02-14T00:00:00.000' AS DateTime), N'UnMarried', N'Employee', N'Dev', N'Michigan', 80, 8, N'Thiru', CAST(N'2017-07-24T00:00:00.000' AS DateTime))  
  8. GO  
  9. INSERT [dbo].[EmpMaster] ([Row_id], [Emp_Code], [Emp_FName], [Emp_LName], [Emp_Status], [Emp_DOB], [Emp_Maritalstatus], [Emp_Role], [Emp_Department], [Emp_Address], [Emp_Profilestatus], [Emp_Expriance], [Create_By], [Create_Date]) VALUES (CAST(4 AS Numeric(18, 0)), N'4000', N'Dizzy', N'Dee', 1, CAST(N'1995-01-10T00:00:00.000' AS DateTime), N'UnMarried', N'Employee', N'Test', N'Kentucky', 90, 5, N'Thiru', CAST(N'2017-07-24T00:00:00.000' AS DateTime))  
  10. GO  
  11. INSERT [dbo].[EmpMaster] ([Row_id], [Emp_Code], [Emp_FName], [Emp_LName], [Emp_Status], [Emp_DOB], [Emp_Maritalstatus], [Emp_Role], [Emp_Department], [Emp_Address], [Emp_Profilestatus], [Emp_Expriance], [Create_By], [Create_Date]) VALUES (CAST(5 AS Numeric(18, 0)), N'5000', N'John  ', N'Sonmez ', 1, CAST(N'1989-05-10T00:00:00.000' AS DateTime), N'Married', N'Employee', N'Test', N'North Carolina', 0, 2, N'Thiru', CAST(N'2017-07-24T00:00:00.000' AS DateTime))  
  12. GO   

Create Store procedure

I have written the store procedure for my data operations so run the below SP. In this procedure, I have gathered different data for dynamic control 

  1. CREATE PROCEDURE [dbo].[PC_EmpMaster]  
  2.   
  3.     @Row_id             BIGINT=NULL,  
  4.     @MODE               VARCHAR(10)=NULL  
  5. AS   
  6.  BEGIN   
  7.     SET NOCOUNT ON;  
  8.         IF(@MODE ='GET')  
  9.         BEGIN SELECT Row_id,Emp_Code,Emp_FName,Emp_LName,Emp_Status,CONVERT(VARCHAR(10), CONVERT( DATE ,Emp_DOB)) AS Emp_DOB,Emp_Maritalstatus,Emp_Profilestatus,Emp_Expriance,Emp_Address,Create_By,Create_Date AS Create_Date FROM EmpMaster  
  10.         END  
  11.         ELSE IF(@MODE ='GETBYID')  
  12.         BEGIN  
  13. SELECT Emp_Code,Emp_FName,Emp_LName,Emp_Role,Emp_Department,Emp_Address FROM EmpMaster WHERE Row_id=@Row_id  
  14.         END  
  15.     SET NOCOUNT OFF;  
  16.   
  17. END   

Open Visual Studio 2017

MVC

Go to New menu >Click New & project. Now it will open New Project Window

MVC

You can select ASP.NET Web Application on Framework 4.5. Enter the name of project in “Solution name” textbox then click ok button.

MVC

One more Window should appear. Select MVC Template in this popup & Click ok button.

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

Then inject “datatable” key word in Angular modular

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

Create & design HTML page with Table, in there mention as “datatable="ng".Then Binding the Server data 

  1. <table datatable="ng" class="table-responsive table-bordered table-striped ">  
  2.   
  3.             <thead style="background :rgb(142, 28, 123); color: white;">  
  4.   
  5.                 <tr>  
  6.                     <th>  
  7.   
  8.                     </th>  
  9.                     <th >  
  10.                         Row ID #  
  11.                     </th>  
  12.                     <th >  
  13.                         Employee Code  
  14.                     </th>  
  15.                     <th >  
  16.                        Employee Name  
  17.                     </th>  
  18.                     <th >  
  19.                         Date of Birth  
  20.                     </th>  
  21.                     <th  >  
  22.                         Marital Status  
  23.                     </th>  
  24.                     <th >  
  25.                         Total Exprience  
  26.                     </th>  
  27.                     <th  >  
  28.                         Profile Status  
  29.                     </th>  
  30.                     <th >  
  31.                         Employee Status  
  32.                     </th>  
  33.                     <th >  
  34.                         Employee Address  
  35.                     </th>  
  36.                     <th>  
  37.                         Created By  
  38.                     </th>  
  39.                     <th>  
  40.                         Created Date  
  41.                     </th>  
  42.                 </tr>  
  43.             </thead>  
  44.             <tbody>  
  45.                 <tr ng-repeat-start="Grid in LoadData" style="cursor:pointer">  
  46.                     <td style="width:1% !important">  
  47.                          
  48.                             <input type="checkbox" ng-model="Grid.isChecked" id="chk1_{{$index}}">  
  49.                             <label for="chk1_{{$index}}"></label>  
  50.                             <div style="display:none"></div>  
  51.                           
  52.                     </td>  
  53.                     <td>  
  54.                         {{Grid.Row_id}}   
  55.                     </td>  
  56.                     <td class="SubGrid">  
  57.                         {{Grid.Emp_Code}} <span ng-click="SubGrid(Grid.Row_id,$index)" class="caret"></span>  
  58.                     </td>  
  59.                     <td>{{Grid.Emp_FName}} {{Grid.Emp_LName}} </td>  
  60.                     <td>{{Grid.Emp_DOB}}</td>  
  61.                     <td align="center">  
  62.                         
  63.                         <span class="label label-success" ng-show="Grid.Emp_Maritalstatus === 'UnMarried'">{{Grid.Emp_Maritalstatus}}</span>  
  64.                         <span class="label label-info" ng-show="Grid.Emp_Maritalstatus !== 'UnMarried'">{{Grid.Emp_Maritalstatus}}</span>  
  65.                     </td>  
  66.                     <td>  
  67.                         <span class="label label-danger">{{Grid.Emp_Expriance}}</span>  
  68.                     </td>  
  69.                     <td>{{Grid.Emp_Profilestatus}}  
  70.                         <div class="c100 p{{Grid.Emp_Profilestatus}} blue small">  
  71.                             <span>{{Grid.Emp_Profilestatus}} %</span>  
  72.                             <div class="slice">  
  73.                                 <div class="bar"></div>  
  74.                                 <div class="fill"></div>  
  75.                             </div>  
  76.                         </div>  
  77.                         <i class="glyphicon glyphicon-ok " style="color:green" ng-show="Grid.Emp_Profilestatus ===100"></i>  
  78.                         <i class="glyphicon glyphicon-remove" style="color:red" ng-show="Grid.Emp_Profilestatus < 100"></i>  
  79.                     </td>  
  80.                     <td>  
  81.                        
  82.                             <input type="checkbox" ng-model="Grid.Emp_Status" id="chk1_{{$index}}">  
  83.                             <label for="chk1_{{$index}}"></label>  
  84.                             <div style="display:none"></div>  
  85.                        </td>  
  86.                     <td>  
  87.                     <input type="text" class="control-label" ng-disabled="!Grid.Emp_Status" ng-model="Grid.Emp_Address"/>  
  88.                     </td>  
  89.                     <td>{{Grid.Create_By}}</td>  
  90.                     <td>{{Grid.Create_Date }}</td>  
  91.                 </tr>  
  92.                 <tr ng-show="ShowGrid==={{$index}}" ng-repeat-end>  
  93.                     <td></td>  
  94.                     <td colspan="6">  
  95.                         <div class="col-sm-1"></div>  
  96.                         <div class="col-lg-11" style=" border: 1px solid #e1e1e1;">  
  97.                             <table class="table table-hover" style="padding-top:5px">  
  98.                                 <thead style="background-color: #563cbc;color:white;">  
  99.                                     <tr>  
  100.                                         <th style="width: 150px;background-color: #563cbc;color:white; ">  
  101.                                             First Name  
  102.                                         </th>  
  103.                                         <th  style="width: 150px; background-color: #563cbc;color:white;">  
  104.                                             Last Name   
  105.                                         </th>  
  106.                                         <th  style="width: 150px; background-color: #563cbc;color:white;">  
  107.                                             Department  
  108.                                         </th>  
  109.                                         <th style="width: 150px;background-color: #563cbc;color:white; ">  
  110.                                            Role  
  111.                                         </th>  
  112.                                     </tr>  
  113.                                 </thead>  
  114.                                 <tbody style="height: 150px !important;">  
  115.                                     <tr ng-repeat="SG in SubGrid">  
  116.                                         <td style="width: 150px;">{{SG.Emp_FName}}</td>  
  117.                                         <td style="width: 150px;">{{SG.Emp_LName}}</td>  
  118.                                         <td style="width: 150px;">{{SG.Emp_Department}}</td>  
  119.                                         <td style="width: 150px;">{{SG.Emp_Role}}</td>  
  120.                                     </tr>  
  121.                                 </tbody>  
  122.                             </table>  
  123.                         </div>  
  124.                     </td>  
  125.                 </tr>  
  126.   
  127.             </tbody>  
  128.   
  129.         </table>   

Using Angular Datatable

Create “Model” folder in solution explorer & create new class in model folder. 

  1. public class ParamModel  
  2.     {  
  3. public string Mode { get; set; }  
  4. public long Row_id { get; set; }      
  5. }   

Write below method in home controller. ”LoadData” displays the data in datatable.

  1. [HttpPost]  
  2.       #region LoadData  
  3.       public async Task<JsonResult> LoadData(BookModel Param)  
  4.       {  
  5.           var result = await Task.Run(() =>  
  6.           {  
  7.                
  8.               try  
  9.               {  
  10.                   HttpResponseMessage response = HttpClient.PostAsJsonAsync(apiUrl + "/GetEmployeeDetails", Param).Result;  
  11.                   if (response.IsSuccessStatusCode)  
  12.                   {  
  13.                       var responseData = response.Content.ReadAsStringAsync().Result;  
  14.                       return Json(responseData, JsonRequestBehavior.AllowGet);  
  15.                   }  
  16.                   else  
  17.                   {  
  18.                      return Json("Error", JsonRequestBehavior.AllowGet);  
  19.                   }  
  20.               }  
  21.               catch (Exception ex)  
  22.               {  
  23.                   return Json("Error" + ex.ToString(), JsonRequestBehavior.AllowGet);  
  24.               }  
  25.           });  
  26.           return result;  
  27.       }  
  28.       #endregion   

In this method, I have called Web API function with async Method. My previous articles “Learn Web API Using SQL Helper Class” will help you learn how to create & consume web APIs. So just, refer to the API URL like below in Web.config file

  1. <add key="APIUrl" value="http://localhost:53490/api" />  

Create an angular controller & service for getting data from server side.

Angular Controller 

  1. $scope.loadTable = function () {  
  2.         var Param={  
  3.             Mode:'GET'  
  4.         }  
  5.         var ServiceData = BookService.loadGrid(Param);  
  6.         ServiceData.then(function (response) {  
  7.             var result = JSON.parse(response.data);  
  8.             $scope.LoadData = result.loadEmployeeList;  
  9.         }, function () {  
  10.         });  
  11.     }  
  12.   
  13.     $scope.loadTable();  
  14.   
  15.     $scope.LoadById = function (Row_id)  
  16.     {  
  17.         var Param = {  
  18.             Row_id: Row_id,  
  19.             Mode: 'GETBYID'  
  20.         }  
  21.         var ServiceData = BookService.loadGrid(Param);  
  22.         ServiceData.then(function (response) {  
  23.             var result = JSON.parse(response.data);  
  24.             $scope.SubGrid = result.loadEmployeeList;  
  25.         }, function () {  
  26.         });  
  27.     }  
  28.   
  29.     $scope.SubGrid = function (Row_id,index)  
  30.     {  
  31.         if ($scope.ShowGrid == index) {  
  32.             $scope.ShowGrid = -1;  
  33.             $scope.SubGrid = {};  
  34.         } else {  
  35.             $scope.ShowGrid = index;  
  36.             $scope.LoadById(Row_id);  
  37.         }  
  38.     }   

Angular Service 

  1. this.loadGrid = function (Param) {  
  2.         var response = $http({  
  3.             method: "post",  
  4.             url: "Home/LoadData",  
  5.             data: JSON.stringify(Param),  
  6.             dataType: "json"  
  7.         });  
  8.         return response;  
  9.     }   

Do not forget to refer to the plug in files, in the same way as the JS file .

Plug In

  1. <script src="~/Plugin/jQuery/jquery-2.2.3.min.js"></script>  
  2.   <script src="~/Plugin/datatables/media/js/jquery.dataTables.js"></script>  
  3.   <script src="~/Plugin/angular/angular.min.js"></script>  
  4.   <script src="~/Plugin/angular-ui-router/release/angular-ui-router.min.js"></script>  
  5.   <script src="~/Plugin/angular-datatables/dist/angular-datatables.js"></script>    

My Files 

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

Once you have finished the above process now your datatable is ready to load, so run the application.

Output 1

MVC

If you click Employee Code near Arrow Button, It will open Sub table also.

Output 2

MVC

See my pervious Angular JS Articles

Conclusion

In this article, we have  learned about MVC using dynamic control datatable. If you have any queries, please tell me through the comments section because your comments are very valuable.

Happy Coding!...


Similar Articles