MVC AngularJS Master/Detail CRUD, Filter And Sorting Using WEB API 2 With Stored Procedure

 
 
In one of my article I explained about how to create a Master/Detail HTML GRID using MVC and AngularJS. Few members requested me to write an article for Master/Detail html grid with CRUD (Insert, Update, Select and Delete) for both Master and Detail grid. As a result here I have created a simple demo program with the following features.

This article will explain:

  • How to Create Order Master and Order Detail table with sample records inserted.
  • Create Stored Procedure to perform Insert/Update/Select and Delete both Order Master and Order Detail table.
  • Create Entity Framework and add all the Stored Procedures.
  • Create a separate WEB API for both Order Master and Order Detail to execute all our Stored Procedures from AngularJS Controller.
  • Create AngularJS Controller to perform all business logic part to display our Master/Detail HTML grid.
  • Add Sorting /Filtering features for both Master and Detail HTML grid.
  • Display Total Row for each Child Detail Grid.
  • Add/Edit/ and Delete each Order Master and Order Detail from grid.
  • Search Order Master Details. 

Prerequisites

Visual Studio 2015 - You can download it from here.

You can also view my previous articles related to AngularJS using MVC and the WCF Rest Service.

Code part

1. Create Database and Table

We will create an Order Master and Order Detail table to be used for the Master and Detail Grid data binding. The following is the script to create a database, table and sample insert query. Run this script in your SQL Server. I have used SQL Server 2014. 

  1. use master  
  2. --create DataBase  
  3. -- 1) Check for the Database Exists .If the database is exist then drop and create new DB  
  4. IF EXISTS (SELECT [nameFROM sys.databases WHERE [name] = 'OrderManagement' )  
  5. DROP DATABASE OrderManagement  
  6. GO  
  7.   
  8. CREATE DATABASE OrderManagement  
  9. GO  
  10.   
  11. USE OrderManagement  
  12. GO  
  13.   
  14.   
  15.   
  16. -- Create OrderMasters Table  
  17.   
  18. CREATE TABLE [dbo].[OrderMasters](   
  19. [Order_No] INT IDENTITY PRIMARY KEY,   
  20. [Table_ID] [varchar](20) NOT NULL,   
  21. [Description] [varchar](200) NOT NULL,   
  22. [Order_DATE] [datetime] NOT NULL,   
  23. [Waiter_Name] [varchar](20) NOT NULL  
  24. )  
  25.   
  26.   
  27. -- Insert OrderMasters sample data  
  28.   
  29. INSERT INTO [OrderMasters]   
  30.           ([Table_ID] ,[Description],[Order_DATE],[Waiter_Name])   
  31.     VALUES   
  32.           ('T1','Order for Table T1',GETDATE(),'SHANU' )   
  33.       
  34. INSERT INTO [OrderMasters]   
  35.           ([Table_ID] ,[Description],[Order_DATE],[Waiter_Name])   
  36.     VALUES   
  37.            ('T2','Order for Table T2',GETDATE(),'Afraz' )   
  38.            
  39. INSERT INTO [OrderMasters]   
  40.           ([Table_ID] ,[Description],[Order_DATE],[Waiter_Name])   
  41.      VALUES   
  42.              ('T3','Order for Table T3',GETDATE(),'Afreen')   
  43.               
  44.   
  45.               
  46. CREATE TABLE [dbo].[OrderDetails](   
  47.   [Order_Detail_No] INT IDENTITY PRIMARY KEY,  
  48.  [Order_No] INT,  
  49.  [Item_Name] [varchar](20) NOT NULL,    
  50.  [Notes] [varchar](200) NOT NULL,   
  51. [QTY]  INT NOT NULL,   
  52.  [Price] INT NOT NULL   
  53.  )  
  54.   
  55. --Now let’s insert the 3 items for the above Order No 'Ord_001'.   
  56.   
  57. INSERT INTO [OrderDetails]   
  58.           ( [Order_No],[Item_Name],[Notes],[QTY] ,[Price])   
  59.    VALUES   
  60.           (1,'Ice Cream','Need very Cold',2 ,160)   
  61.   
  62. INSERT INTO [OrderDetails]   
  63.           ([Order_No],[Item_Name],[Notes],[QTY] ,[Price])   
  64.    VALUES   
  65.           (1,'Coffee','Hot and more Suger',1 ,80)   
  66.            
  67.   
  68.           INSERT INTO [OrderDetails]   
  69.           ([Order_No],[Item_Name],[Notes],[QTY] ,[Price])   
  70.    VALUES   
  71.           (1,'Burger','Spicy',3 ,140)   
  72.            
  73.           INSERT INTO [OrderDetails]   
  74.           ([Order_No],[Item_Name],[Notes],[QTY] ,[Price])   
  75.    VALUES   
  76.           (2,'Pizza','More Chees and Large',1 ,350)   
  77.   
  78.            
  79.           INSERT INTO [OrderDetails]   
  80.           ([Order_No],[Item_Name],[Notes],[QTY] ,[Price])   
  81.    VALUES   
  82.           (2,'Cola','Need very Cold',3 ,50)   
  83.            
  84.           INSERT INTO [OrderDetails]   
  85.           ([Order_No],[Item_Name],[Notes],[QTY] ,[Price])   
  86.    VALUES   
  87.           (3,'IDLY','Hot',3 ,40)   
  88.   
  89.           INSERT INTO [OrderDetails]   
  90.           ([Order_No],[Item_Name],[Notes],[QTY] ,[Price])   
  91.    VALUES   
  92.           (3,'Thosa','Hot',3 ,50)   
  93.   
  94. -- To Select and test Order Master and Details  
  95.   
  96. Select * FROM OrderMasters  
  97.   
  98. Select * From OrderDetails  
After creating our Table we will create a Stored Procedure for our CRUD Operations. Firstly, we will create a stored procedure for Order Master Table to perform CRUD. 
  1. -- 1) Stored procedure to Select OrderMaster  
  2.   
  3. -- Author      : Shanu                                                                  
  4. -- Create date : 2015-10-26                                                                 
  5. -- Description : Order Master                                                
  6. -- Tables used :  OrderMaster                                                                 
  7. -- Modifier    : Shanu                                                                  
  8. -- Modify date : 2015-10-26                                                                 
  9. -- =============================================     
  10. -- exec USP_OrderMaster_Select '',''  
  11. -- =============================================                                                             
  12. Create PROCEDURE [dbo].[USP_OrderMaster_Select]                                                
  13.    (                              
  14.      @OrderNo           VARCHAR(100)     = '',  
  15.      @Table_ID               VARCHAR(100)     = ''      
  16.       )                                                          
  17. AS                                                                  
  18. BEGIN          
  19.          Select [Order_No],  
  20.                 [Table_ID],  
  21.                 [Description],  
  22.                 [Order_DATE],  
  23.                 [Waiter_Name]  
  24.             FROM   
  25.                 OrderMasters   
  26.             WHERE  
  27.                 Order_No like  @OrderNo +'%'  
  28.                 AND Table_ID like @Table_ID +'%'  
  29.             ORDER BY  
  30.                 Table_ID      
  31. END  
  32.   
  33. -- 2) Stored procedure to insert OrderMaster  
  34.   
  35. -- Author      : Shanu                                                                  
  36. -- Create date : 2015-10-26                                                                 
  37. -- Description : Order Master                                                
  38. -- Tables used :  OrderMaster                                                                 
  39. -- Modifier    : Shanu                                                                  
  40. -- Modify date : 2015-10-26                                                                    
  41. -- =============================================      
  42. -- exec USP_OrderMaster_Insert 'T4','Table 4','SHANU'  
  43. -- =============================================                                                            
  44. Create PROCEDURE [dbo].[USP_OrderMaster_Insert]                                                
  45.    (                         
  46.      @Table_ID           VARCHAR(100)     = '',  
  47.      @Description               VARCHAR(100)     = '',  
  48.      @Waiter_Name               VARCHAR(20)     = ''  
  49.       )                                                          
  50. AS                                                                  
  51. BEGIN          
  52.         IF NOT EXISTS (SELECT Table_ID FROM OrderMasters WHERE Table_ID=@Table_ID)  
  53.             BEGIN  
  54.   
  55.                   INSERT INTO [OrderMasters]   
  56.           ([Table_ID] ,[Description],[Order_DATE],[Waiter_Name])   
  57.     VALUES   
  58.           (@Table_ID,@Description,GETDATE(),@Waiter_Name )   
  59.                                  
  60.                     Select 'Inserted' as results  
  61.                           
  62.             END  
  63.          ELSE  
  64.              BEGIN  
  65.                      Select 'Exists' as results  
  66.               END  
  67.   
  68. END  
  69.   
  70. -- 3) Stored procedure to Update OrderMaster  
  71.       
  72. -- Author      : Shanu                                                                  
  73. -- Create date : 2015-10-26                                                                 
  74. -- Description : Order Master                                                
  75. -- Tables used :  OrderMaster                                                                 
  76. -- Modifier    : Shanu                                                                  
  77. -- Modify date : 2015-10-26                                                                  
  78. -- =============================================        
  79. -- exec USP_OrderMaster_Update 4,'T4','Table 4 wer','SHANU'  
  80. -- =============================================                                                             
  81. CREATE PROCEDURE [dbo].[USP_OrderMaster_Update]                                                
  82.    (  @OrderNo               Int=0,                             
  83.       @Table_ID           VARCHAR(100)     = '',  
  84.       @Description               VARCHAR(100)     = '',  
  85.       @Waiter_Name               VARCHAR(20)     = ''  
  86.       )                                                          
  87. AS                                                                  
  88. BEGIN          
  89.         IF NOT EXISTS (SELECT Table_ID FROM OrderMasters WHERE Order_No!=@OrderNo AND Table_ID=@Table_ID)  
  90.             BEGIN  
  91.                     UPDATE OrderMasters  
  92.                     SET    [Table_ID]=@Table_ID ,  
  93.                     [Description]=@Description,  
  94.                     [Order_DATE]=GETDATE(),  
  95.                     [Waiter_Name]=@Waiter_Name  
  96.                     WHERE  
  97.                         Order_No=@OrderNo  
  98.                                  
  99.                     Select 'updated' as results                          
  100.             END  
  101.          ELSE  
  102.              BEGIN  
  103.                      Select 'Exists' as results  
  104.               END  
  105. END  
  106.   
  107. -- 4) Stored procedure to Delete OrderMaster  
  108.       
  109. -- Author      : Shanu                                                                  
  110. -- Create date : 2015-10-26                                                                 
  111. -- Description : Order Master                                                
  112. -- Tables used :  OrderMaster                                                                 
  113. -- Modifier    : Shanu                                                                  
  114. -- Modify date : 2015-10-26                                                                   
  115. -- =============================================    
  116. -- exec USP_OrderMaster_Delete '3'  
  117. -- =============================================                                                             
  118. CREATE PROCEDURE [dbo].[USP_OrderMaster_Delete]                                                
  119.    (  @OrderNo               Int=0 )                                                          
  120. AS                                                                  
  121. BEGIN          
  122.         DELETE FROM OrderMasters WHERE  Order_No=@OrderNo               
  123.   
  124.         DELETE from OrderDetails WHERE  Order_No=@OrderNo      
  125.   
  126.          Select 'Deleted' as results  
  127.               
  128. END  
Next we create stored procedure for Order Detail Table to perform CRUD. 
  1. USE OrderManagement  
  2. GO  
  3.   
  4. -- 1) Stored procedure to Select OrderDetails  
  5.   
  6. -- Author      : Shanu                                                                  
  7. -- Create date : 2015-10-26                                                                 
  8. -- Description : OrderDetails                                              
  9. -- Tables used :  OrderDetails                                                                 
  10. -- Modifier    : Shanu                                                                  
  11. -- Modify date : 2015-10-26                                                                 
  12. -- =============================================     
  13. -- exec USP_OrderDetail_Select '1'  
  14. -- =============================================                                                             
  15. Create PROCEDURE [dbo].[USP_OrderDetail_Select]                                                
  16.    (                              
  17.      @OrderNo           VARCHAR(100)     = ''    
  18.       )                                                          
  19. AS                                                                  
  20. BEGIN          
  21.          Select Order_Detail_No,  
  22.                 [Order_No],  
  23.                 [Item_Name],  
  24.                 [Notes],  
  25.                 [QTY],  
  26.                 [Price]  
  27.             FROM   
  28.                 OrderDetails   
  29.             WHERE  
  30.                 Order_No like  @OrderNo +'%'               
  31.             ORDER BY  
  32.                 Item_Name      
  33. END  
  34.   
  35. -- 2) Stored procedure to insert OrderDetail  
  36.   
  37. -- Author      : Shanu                                                                  
  38. -- Create date : 2015-10-26                                                                 
  39. -- Description : Order Master                                                
  40. -- Tables used :  OrderDetail                                                                 
  41. -- Modifier    : Shanu                                                                  
  42. -- Modify date : 2015-10-26                                                                    
  43. -- =============================================      
  44. -- exec USP_OrderDetail_Insert 4,'cadburys','cadburys Chocolate','50',50  
  45. -- =============================================                                                            
  46. Create PROCEDURE [dbo].[USP_OrderDetail_Insert]                                                
  47.    (    
  48.      @Order_No            VARCHAR(10),                       
  49.      @Item_Name           VARCHAR(100)     = '',  
  50.      @Notes               VARCHAR(100)     = '',  
  51.      @QTY                 VARCHAR(20)     = '',  
  52.      @Price               VARCHAR(20)     = ''  
  53.       )                                                          
  54. AS                                                                  
  55. BEGIN          
  56.         IF NOT EXISTS (SELECT Item_Name FROM OrderDetails WHERE Order_No=@Order_No AND Item_Name=@Item_Name)  
  57.             BEGIN  
  58.   
  59.                   INSERT INTO [OrderDetails]   
  60.           ( [Order_No],[Item_Name],[Notes],[QTY] ,[Price])   
  61.     VALUES   
  62.           ( @Order_No,@Item_Name,@Notes,@QTY ,@Price )   
  63.                                  
  64.                     Select 'Inserted' as results  
  65.                           
  66.             END  
  67.          ELSE  
  68.              BEGIN  
  69.                      Select 'Exists' as results  
  70.               END  
  71.   
  72. END  
  73.   
  74. -- 3) Stored procedure to Update OrderDetail  
  75.       
  76. -- Author      : Shanu                                                                  
  77. -- Create date : 2015-10-26                                                                 
  78. -- Description : Order Master                                                
  79. -- Tables used :  OrderDetail                                                                 
  80. -- Modifier    : Shanu                                                                  
  81. -- Modify date : 2015-10-26                                                                  
  82. -- =============================================        
  83. -- exec USP_OrderDetail_Update 8,4,'Cadburys','cadburys Chocolate','50',50  
  84. -- =============================================                                                             
  85. ALTER PROCEDURE [dbo].[USP_OrderDetail_Update]                                                
  86.    (  @Order_Detail_No   Int=0,                             
  87.       @Order_No           VARCHAR(10),                       
  88.       @Item_Name           VARCHAR(100)     = '',  
  89.       @Notes               VARCHAR(100)     = '',  
  90.       @QTY                 VARCHAR(20)     = '',  
  91.       @Price               VARCHAR(20)     = ''  
  92.       )                                                          
  93. AS                                                                  
  94. BEGIN          
  95.         IF NOT EXISTS (SELECT Item_Name FROM OrderDetails WHERE Order_Detail_No!=@Order_Detail_No AND Item_Name=@Item_Name)  
  96.             BEGIN  
  97.                     UPDATE OrderDetails  
  98.                     SET   [Item_Name]=@Item_Name,  
  99.                     [Notes]=@Notes,  
  100.                     [QTY] =@QTY,  
  101.                     [Price]=@Price  
  102.                     WHERE  
  103.                        Order_Detail_No=@Order_Detail_No  
  104.                        AND  Order_No=@Order_No  
  105.                                  
  106.                     Select 'updated' as results                          
  107.             END  
  108.          ELSE  
  109.              BEGIN  
  110.                      Select 'Exists' as results  
  111.               END  
  112. END  
  113.   
  114. -- 4) Stored procedure to Delete OrderDetail  
  115.       
  116. -- Author      : Shanu                                                                  
  117. -- Create date : 2015-10-26                                                                 
  118. -- Description : Order Master                                                
  119. -- Tables used :  OrderDetail                                                                 
  120. -- Modifier    : Shanu                                                                  
  121. -- Modify date : 2015-10-26                                                                   
  122. -- =============================================    
  123. -- exec USP_OrderDetail_Delete '8'  
  124. -- =============================================                                                             
  125. CREATE PROCEDURE [dbo].[USP_OrderDetail_Delete]                                                
  126.    (  @Order_Detail_No               Int=0 )                                                          
  127. AS                                                                  
  128. BEGIN          
  129.        
  130.         DELETE from OrderDetails WHERE   Order_Detail_No=@Order_Detail_No  
  131.   
  132.          Select 'Deleted' as results  
  133.               
  134. END  
2. Create your MVC Web Application in Visual Studio 2015

After installing our Visual Studio 2015 click Start, then Programs and select Visual Studio 2015 - Click Visual Studio 2015.

Click New, then Project, select Web and click ASP.NET Web Application. Select your project location and enter your web application name.

 

Select MVC and in Add Folders and Core reference for select the Web API and click OK

 
Add Database using ADO.NET Entity Data Model

Right click our project and click
Add, then New Item.

 

Select Data, then ADO.NET Entity Data Model and give the name for our EF and click Add.

 

Select EF Designer from the database and click Next >.

 

Here click New Connection and provide your SQL Server - Server Name and connect to your database.



Here you can see I have given my SQL server name, Id and PWD and after it connected I selected the database as OrderManagement since we have created the Database using my SQL Script.

 

Click Next and select the tables and all the Stored Procedures need to be used and click Finish.



Here we can see now we have created our OrderDetailModel.



Once the Entity has been created the next step is to add a Web API to our controller and write the function to Select/Insert/Update and Delete.

Procedure to add our Web API Controller

Right-click the Controllers folder, click Add and then click Controller.



Select Controller and add an Empty Web API 2 Controller. Provide your name to the Web API controller and click OK. Here for my Web API Controller I have given the name “OrderAPIController". In this demo project I have created 2 different controller for Order master and order Detail.

As we have created Web API controller, we can see our controller has been inherited with ApiController. 

As we all know Web API is a simple and easy way to build HTTP Services for Browsers and Mobiles.

Web API has the following four methods as
Get/Post/Put and Delete where:
  • Get is to request for the data. (Select)
  • Post is to create a data. (Insert)
  • Put is to update the data.
  • Delete is to delete data.

Get Method

In our example I have used only a Get method since I am using only a Stored Procedure. We need to create an object for our Entity and write our Get Method to do Select/Insert/Update and Delete operations.

Select Operation

We use a get method to get all the details of the OrderMasters table using an entity object and we return the result as IEnumerable. We use this method in our AngularJS and display the result in an MVC page from the AngularJS controller. Using Ng-Repeat we can bind the details.

Here we can see in the get method I have passed the search parameter to the
USP_OrderMaster_Select Stored Procedure. In the Stored Procedure I used like "%" to return all the records if the search parameter is empty. 

  1. OrderManagementEntities objapi = new OrderManagementEntities();  
  2.   
  3.         // to Search Student Details and display the result  
  4.         [HttpGet]  
  5.         public IEnumerable<USP_OrderMaster_Select_Result> Get(string OrderNO, string TableID)  
  6.         {  
  7.             if (OrderNO == null)  
  8.                 OrderNO = "";  
  9.             if (TableID == null)  
  10.                 TableID = "";  
  11.   
  12.             return objapi.USP_OrderMaster_Select(OrderNO, TableID).AsEnumerable();  
  13.   
  14.         }  
Here in my example I have used the get method for Select/Insert/Update and Delete operations, since in my Stored Procedure after insert/update and delete I have returned the message from the database.

Insert Operation

The same as select I passed all the parameters to the insert procedure. This insert method will return the result from the database as a record is inserted or maybe not. I will get the result and display it from the AngularJS Controller to MVC application.
 
  1. // To Insert new Student Details  
  2. [HttpGet]  
  3. public IEnumerable<string> insertOrderMaster(string Table_ID,string Description,string Waiter_Name)  
  4. {  
  5.    return objapi.USP_OrderMaster_Insert( Table_ID, Description, Waiter_Name).AsEnumerable();  
  6. }  
Update Operation

The same as Insert I have passed all the parameters to the insert procedure. This Update method will return the result from the database as a record is updated or maybe not. I will pass the
>OrderNo to the update procedure to update the record for the >OrderNo. I will get the result and display it from the AngularJS Controller to the MVC application. 
  1. //to Update Student Details  
  2. [HttpGet]  
  3. public IEnumerable<string> updateOrderMaster(int OrderNo, string Table_ID, string Description, string Waiter_Name)  
  4. {  
  5.     return objapi.USP_OrderMaster_Update(OrderNo, Table_ID, Description, Waiter_Name).AsEnumerable();  
  6. }  
Delete Operation

The same as update I have passed the
>OrderNo to the procedure to delete the record. 
  1. //to Delete Student Details  
  2. [HttpGet]  
  3. public IEnumerable<string> deleteOrderMaster(int OrderNo)  
  4. {  
  5.      return objapi.USP_OrderMaster_Delete(OrderNo).AsEnumerable();  
  6. }  
Same like OrderMasterController I have created another controller as “DetailAPI” for Detail table CRUD Operations. Here is the complete code for DetailController. 
  1. public class DetailAPIController: ApiController  
  2. {  
  3.     OrderManagementEntities objapi = new OrderManagementEntities();  
  4.     // to Search Student Details and display the result    
  5.     [HttpGet]  
  6.     public IEnumerable < USP_OrderDetail_Select_Result > Get(string OrderNO)  
  7.         {  
  8.             if(OrderNO == null) OrderNO = "0";  
  9.             return objapi.USP_OrderDetail_Select(OrderNO)  
  10.                 .AsEnumerable();  
  11.         }  
  12.         // To Insert new Student Details    
  13.         [HttpGet]  
  14.     public IEnumerable < string > insertOrderDetail(string Order_No, string Item_Name, string Notes, string QTY, string Price)  
  15.         {  
  16.             return objapi.USP_OrderDetail_Insert(Order_No, Item_Name, Notes, QTY, Price)  
  17.                 .AsEnumerable();  
  18.         }  
  19.         //to Update Student Details    
  20.         [HttpGet]  
  21.     public IEnumerable < string > updateOrderDetail(int Order_Detail_No, string Order_No, string Item_Name, string Notes, string QTY, string Price)  
  22.         {  
  23.             return objapi.USP_OrderDetail_Update(Order_Detail_No, Order_No, Item_Name, Notes, QTY, Price)  
  24.                 .AsEnumerable();  
  25.         }  
  26.         //to Delete Student Details    
  27.         [HttpGet]  
  28.     public IEnumerable < string > deleteOrderDetail(int Order_Detail_No)  
  29.     {  
  30.         return objapi.USP_OrderDetail_Delete(Order_Detail_No)  
  31.             .AsEnumerable();  
  32.     }  
  33. }  
Creating AngularJs Controller

Firstly, create a folder inside the Script Folder and I have given the folder name as “MyAngular”.




Now add your Angular Controller inside the folder.

Right click the MyAngular folder and click Add and New Item. Select Web and then AngularJS Controller and provide a name for the Controller. I have named my AngularJs Controller “Controller.js”.



Once the AngularJS Controller is created, we can see by default the controller will have the code with the default module definition and all. 



I have changed the preceding code like adding a Module and controller as in the following.

If the AngularJS package is missing, then add the package to your project.

Right click your MVC project and click Manage NuGet Packages. Search for AngularJS and click Install.



Procedure to Create AngularJS Script Files

Modules.js:
Here we will add the reference to the AngularJS JavaScript and create an Angular Module named “RESTClientModule”.  
  1. // <reference path="../angular.js" />    
  2. /// <reference path="../angular.min.js" />     
  3. /// <reference path="../angular-animate.js" />     
  4. /// <reference path="../angular-animate.min.js" />    
  5. var app;  
  6.   
  7. (function () {  
  8.     app = angular.module("RESTClientModule", ['ngAnimate']);  
  9. })();  
Controllers: In AngularJs Controller I have done all the business logic and returned the data from Web API to our MVC HTML page.

1. Variable declarations

Firstly, I declared all the local variables need to be used.

  1. app.controller("AngularJs_studentsController"function ($scope, $timeout, $rootScope, $window, $http) {  
  2.     $scope.date = new Date();  
  3.     $scope.MyName = "shanu";  
  4.   
  5.     //For Order Master Search   
  6.     $scope.OrderNos = "";  
  7.     $scope.Table_IDs = "";     
  8.   
  9.     //This variable will be used for Insert/Edit/Delete OrderMasters Table.  
  10.     $scope.OrderNo = 0;  
  11.     $scope.Table_ID = "";  
  12.     $scope.Description = "";  
  13.     $scope.Waiter_Name = "";  
  14.   
  15.     //Show Hide OrderMaster Table  
  16.     $scope.showOrderMasterAdd = true;  
  17.     $scope.addEditOrderMaster = false;  
  18.     $scope.OrderMasterList = true;  
  19.     $scope.showItem = true;  
  20.   
  21.     //This variable will be used for Insert/Edit/Delete OrderDetail Table.  
  22.     $scope.Order_Detail_No = 0;  
  23.     $scope.Item_Name ="";  
  24.     $scope.Notes = "";  
  25.     $scope.QTY = "1";  
  26.     $scope.Price = "0";  
  27.   
  28.      
  29.     $scope.addEditOrderDetail = false;  
  30.     $scope.expandImg = "expand.png";  

2. Methods

Select Method

In the select method I have used $http.get to get the details from Web API. In the get method I will provide our API Controller name and method to get the details. Here we can see I have passed the search parameter of
OrderNO and TableID using:

{ params: { OrderNO: OrderNos, TableID: Table_IDs }

The final result will be displayed to the MVC HTML page using data-ng-repeat.

  1. $http.get('/api/OrderAPI/',  
  2. {  
  3.     params:  
  4.     {  
  5.         OrderNO: OrderNos,  
  6.         TableID: Table_IDs  
  7.     }  
  8. })  
  9. .success(function (data)  
  10. {  
  11.     $scope.OrderMasters = data;  
  12.     $scope.showOrderMasterAdd = true;  
  13.     $scope.addEditOrderMaster = false;  
  14.     $scope.OrderMasterList = true;  
  15.     $scope.showItem = true;  
  16.     $scope.addEditOrderDetail = false;  
  17.     if($scope.OrderMasters.length > 0)  
  18.     {}  
  19. })  
  20. .error(function ()  
  21. {  
  22.     $scope.error = "An Error has occured while loading posts!";  
  23. });  

Search Button Click

In the search button click I will call the SearchMethod to bind the result. Here we can see in the search text box I have used ng-model="OrderNos". Using ng-model in the AngularJS Controller we can get the TextBox input value or we can set the value to the TextBox.

  1. <input type="text" name="txtOrderNos" ng-model="OrderNos" value="" />  
  2. <input type="text" name="txtTable_IDs" ng-model="Table_IDs" /><input type="submit" value="Search" style="background-color:#336699;color:#FFFFFF" ng-click="searchOrderMasters()" />  
  3. //Search  
  4. $scope.searchOrderMasters = function () 
  5. {  
  6.     selectOrderMasters($scope.OrderNos, $scope.Table_IDs);  
  7. }  


Insert new Order Master

In the ADD New Student Detail button click I will make visible the StudentAdd table details where the user can enter the new student information. For a new student I will make the Student ID as 0. In the New Student save button click I will call the save method.
  1. // New Student Add Details    
  2. $scope.showOrderMasters = function ()  
  3. {  
  4.     cleardetails();  
  5.     $scope.addEditOrderDetail = false;  
  6.     $scope.showOrderMasterAdd = true;  
  7.     $scope.addEditOrderMaster = true;  
  8.     $scope.OrderMasterList = true;  
  9.     $scope.showItem = true;  
  10. }  

In the Save method I will check for the OrderNo. If the OrderNo is “0” then it will insert the new Order Master details. Here I will call the Insert Web API method and if the OrderNo is > 0 then it means to update the Order record I will call the Update Web API method.



To Insert Web API Method I will pass all the Input parameters. In my Stored Procedure I will check whether the Table Name for the Order already exists. If the Table name does not exist in the database then I will insert the records and return the success message as “inserted” and if the Table name already exists then I will return the message as “Exists”.

  1. //Save OrderMaster    
  2. $scope.saveDetails = function ()  
  3. {  
  4.     $scope.IsFormSubmitted1 = true;  
  5.     if($scope.IsFormValid1)  
  6.     {  
  7.         if($scope.OrderNo == 0)  
  8.         {  
  9.             $http.get('/api/OrderAPI/insertOrderMaster/',  
  10.                 {  
  11.                     params:  
  12.                     {  
  13.                         Table_ID: $scope.Table_ID,  
  14.                         Description: $scope.Description,  
  15.                         Waiter_Name: $scope.Waiter_Name  
  16.                     }  
  17.                 })  
  18.                 .success(function (data)  
  19.                 {  
  20.                     $scope.orderMasterInserted = data;  
  21.                     alert($scope.orderMasterInserted);  
  22.                     cleardetails();  
  23.                     selectOrderMasters('''');  
  24.                 })  
  25.                 .error(function ()  
  26.                 {  
  27.                     $scope.error = "An Error has occured while loading posts!";  
  28.                 });  
  29.         }  
  30.         else  
  31.         { // to update to the student details    
  32.             $http.get('/api/OrderAPI/updateOrderMaster/',  
  33.                 {  
  34.                     params:  
  35.                     {  
  36.                         OrderNo: $scope.OrderNo,  
  37.                         Table_ID: $scope.Table_ID,  
  38.                         Description: $scope.Description,  
  39.                         Waiter_Name: $scope.Waiter_Name  
  40.                     }  
  41.                 })  
  42.                 .success(function (data)  
  43.                 {  
  44.                     $scope.orderMasterUpdated = data;  
  45.                     alert($scope.orderMasterUpdated);  
  46.                     cleardetails();  
  47.                     selectOrderMasters('''');  
  48.                 })  
  49.                 .error(function ()  
  50.                 {  
  51.                     $scope.error = "An Error has occured while loading posts!";  
  52.                 });  
  53.         }  
  54.     }  
  55.     else  
  56.     {  
  57.         $scope.Message1 = "All the fields are required.";  
  58.     }  
  59. }  

Update Order Master

 
 

The same as Insert I will display the update details for the user to edit the details and save it. In the Edit method I will get all the details for the row where the user clicks on the Edit Icon and sets all the results to the appropriate TextBox. In the Save button click I will call the save method to save all the changes to the database like Insert.

  1. //Edit Order Details    
  2. $scope.OrderMasterEdit = function OrderMasterEdit(OrderNoss, Table_IDss, Descriptionss, Waiter_Namess)  
  3. {  
  4.     cleardetails();  
  5.     $scope.OrderNo = OrderNoss;  
  6.     $scope.Table_ID = Table_IDss  
  7.     $scope.Description = Descriptionss;  
  8.     $scope.Waiter_Name = Waiter_Namess;  
  9.     $scope.addEditOrderDetail = false;  
  10.     $scope.showOrderMasterAdd = true;  
  11.     $scope.addEditOrderMaster = true;  
  12.     $scope.OrderMasterList = true;  
  13.     $scope.showItem = true;  
  14. }  

Delete Order Master Details



In the Delete button click, I will display the confirmation message to the user whether to delete the Order or not. If the user clicks the OK button I will pass the OrderNo to the delete method of the Web API to delete the record from the database.
  1. //Delete Order master Detail    
  2. $scope.OrderMasterDelete = function OrderMasterDelete(OrderNoss)  
  3. {  
  4.     cleardetails();  
  5.     $scope.OrderNo = OrderNoss;  
  6.     var delConfirm = confirm("Are you sure you want to delete the Order Master " + OrderNoss + " ?");  
  7.     if(delConfirm == true)  
  8.     {  
  9.         // alert($scope.OrderNo);    
  10.         $http.get('/api/OrderAPI/deleteOrderMaster/',  
  11.             {  
  12.                 params:  
  13.                 {  
  14.                     OrderNo: $scope.OrderNo  
  15.                 }  
  16.             })  
  17.             .success(function (data)  
  18.             {  
  19.                 // alert(data);    
  20.                 $scope.orderMasterDeleted = data;  
  21.                 alert($scope.orderMasterDeleted);  
  22.                 cleardetails();  
  23.                 selectOrderMasters('''');  
  24.             })  
  25.             .error(function ()  
  26.             {  
  27.                 $scope.error = "An Error has occured while loading posts!";  
  28.             });  
  29.     }  
  30. }  

Filter and Sorting Order Master 



Kindly refer my article that explains how to perform Filtering and Sorting for HTML generated grid.

The filters can be added with the ng-repeat using the pipe symbol.

Here we can see with ng-repeat we have added the filter and for the filter we have given the TextBox Model id. When the user presses the key on the TextBox the filter will be applied for the loop and display the appropriate value as in the following:

  1. </tr>  
  2. <tr style="height: 30px; background-color:#336699 ; color:#FFFFFF ;border: solid 1px #659EC7;">  
  3.     <td width="100" align="center" colspan="3"> <img src="~/Images/filter.png" /> Filter By </td>  
  4.     <td width="180" align="center" style="border: solid 1px #FFFFFF; padding: 5px;table-layout:fixed;">  
  5.         <input ng-model="search.Order_No" placeholder="Order..." width="90"> </td>  
  6.     <td width="180" align="center" style="border: solid 1px #FFFFFF; padding: 5px;table-layout:fixed;">  
  7.         <input ng-model="search.Table_ID" placeholder="Table..."> </td>  
  8.     <td width="200" align="center" style="border: solid 1px #FFFFFF; padding: 5px;table-layout:fixed;"> </td>  
  9.     <td width="200" align="center" style="border: solid 1px #FFFFFF; padding: 5px;table-layout:fixed;"> </td>  
  10.     <td width="200" align="center" style="border: solid 1px #FFFFFF; padding: 5px;table-layout:fixed;">  
  11.         <input ng-model="search.Waiter_Name" placeholder="Name..."> </td>  
  12. </tr>  

Sorting Order Master

The same as for a filter we add the orderBy with field and reverse value in ng-repat using the pipe symbol.

The OrderBy can be added with the ng-repeat using the pipe symbol. For example, let's consider the preceding example.

And in ng-repeat we will be giving the search by filter that will filter all the textbox values which we enter and produce the filtered result. 

  1. <tbody data-ng-repeat="stds in OrderMasters | filter:search | orderBy:predicate:reverse">  

Displaying Order Detail



Here we can see how I have displayed the Order Detail grid inside the Order Master by clicking the Detail button click.

In each Order Master Row click I will check for the active row. And then in the detail button click I called the showNewOrderDetails() method to display the details. 
  1. <tr ng-show="activeRow==stds.Order_No" >  

In detail button click,

  1. <input type="button" value="Add Detail" style="background-color:#439633;color:#FFFFFF;font-size:large;width:100px;  
  2. border-color:#a2aabe;border-style:dashed;border-width:2px;" ng-click="showNewOrderDetails()" />    
  3.   
  4. // New Detail Add  
  5. $scope.showNewOrderDetails = function () {  
  6.    clearOrderdetails();  
  7.    $scope.showOrderMasterAdd = false;  
  8.    $scope.addEditOrderMaster = false;  
  9.    $scope.OrderMasterList = true;  
  10.    $scope.showItem = true;  
  11.    $scope.addEditOrderDetail = true;  
  12. }  

For order Detail CRUD, Sorting and Filtering the same logic as we have seen for Order master has been used. Here we will see the following output:

Order Detail Add

 

Order Detail Edit
 
 
 
Order Detail Delete
 
 
 Order Detail Sorting and Filtering