WEB API 2 와 MVC 그리고 AngularJS 로 CRUD 구현하기

http://www.codeproject.com/Articles/1046214/MVC-AngularJS-Master-Detail-CRUD-Filter-And-Sortin

 

MVC AngularJS Master/Detail CRUD, Filter And Sorting

Using WEB API 2 With Stored Procedure

 

Introduction

 

how to create a Master/Detail HTML GRID 여기 들어가시면 강좌가 존재하는데, 여기 코드에 MVC 와 AngularJs 를 적용해 보는 시간을 가져보도록 하겠습니다.

강좌를 기재하기 앞서, 아래와 같은 목차를 두고 설명하겠습니다.

This article will explain:

• How to Create Order Master and Order Detail table with sample records inserted.
  (주문 master 와 주문 상세 테이블을 생성하는 방법)
• Create Stored Procedure to perform Insert/Update/Select and Delete both Order Master and Order Detail table.
  (CRUD 를  저장프로시저로 구현하며, Master 와 Detail 테이블에 적용하기)
• Create Entity Framework and add all the Stored Procedures.
  (모든 저장프로시저를 entity framework 를 활용해서 사용하기)
• Create a separate WEB API for both Order Master and Order Detail to execute all our Stored Procedures from AngularJS Controller.
  (분리된 web api 를 통해 Master 와 Detail 을 생성하며, AngularJS Controller 를 통해 저장프로시저를 실행할 겁니다.)
• Create AngularJS Controller to perform all business logic part to display our Master/Detail HTML grid.
  (AngularJS Controller 를 통해 모든 business logic 을 실행 합니다. 역할은 Master 와 Detail 를 위한 Html 그리드를 보여주기 위한 용도입니다.)
• Add Sorting /Filtering features for both Master and Detail HTML grid.
  (Master 와 Html Grid 둘다 정렬/검색 기능을 추가합니다.)
• Display Total Row for each Child Detail Grid.
  (하위 Detail 그리드 를 위한 통합 row 를 보여주기)
• Add/Edit/ and Delete each Order Master and Order Detail from grid.
  (Master 와 Detail  그리드 행에 각각의 row 별로 추가,수정,삭제 기능을 제공)
• Search Order Master Details.
  (Master 상세 주문에 검색기능 넣기)

Building the Sample (빌드하기)

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 Serice.

• https://code.msdn.microsoft.com/Dynamic-scheduling-using-35328360
• https://code.msdn.microsoft.com/MVC-Angular-JS-CRUD-using-b4845edc
• https://code.msdn.microsoft.com/AngularJS-Shopping-Cart-8d4dde90
• https://code.msdn.microsoft.com/MVC-AngularJS-and-WCF-Rest-27d239b4
• https://code.msdn.microsoft.com/MVC-Web-API-and-Angular-JS-36302919
• https://code.msdn.microsoft.com/MVC-Web-API-and-Angular-JS-36302919
• https://code.msdn.microsoft.com/Image-Preview-using-MVC-792d881c

Description

1. Create Database and Table (테이블 생성)

SQL Server 2014 에 아래와 같은  테이블을 생성합니다.

 

SQL

use master
--create DataBase
-- 1) Check for the Database Exists .If the database is exist then drop and create new DB
IF EXISTS (SELECT [name] FROM sys.databases WHERE [name] = 'OrderManagement' )
DROP DATABASE OrderManagement
GO
 
CREATE DATABASE OrderManagement
GO
 
USE OrderManagement
GO
 
-- Create OrderMasters Table
 
CREATE TABLE [dbo].[OrderMasters](
[Order_No] INT IDENTITY PRIMARY KEY,
[Table_ID] [varchar](20) NOT NULL,
[Description] [varchar](200) NOT NULL,
[Order_DATE] [datetime] NOT NULL,
[Waiter_Name] [varchar](20) NOT NULL
)
 
-- Insert OrderMasters sample data
 
INSERT INTO [OrderMasters]
          ([Table_ID] ,[Description],[Order_DATE],[Waiter_Name])
    VALUES
          ('T1','Order for Table T1',GETDATE(),'SHANU' )   
 
INSERT INTO [OrderMasters]
          ([Table_ID] ,[Description],[Order_DATE],[Waiter_Name])
    VALUES
           ('T2','Order for Table T2',GETDATE(),'Afraz' )        
 
INSERT INTO [OrderMasters]
          ([Table_ID] ,[Description],[Order_DATE],[Waiter_Name])
     VALUES
             ('T3','Order for Table T3',GETDATE(),'Afreen')       
         
 
CREATE TABLE [dbo].[OrderDetails](
  [Order_Detail_No] INT IDENTITY PRIMARY KEY,
[Order_No] INT,
[Item_Name] [varchar](20) NOT NULL, 
[Notes] [varchar](200) NOT NULL,
[QTY]  INT NOT NULL,
[Price] INT NOT NULL
)
 
--Now let’s insert the 3 items for the above Order No 'Ord_001'.
 
INSERT INTO [OrderDetails]
          ( [Order_No],[Item_Name],[Notes],[QTY] ,[Price])
   VALUES
          (1,'Ice Cream','Need very Cold',2 ,160)
 
INSERT INTO [OrderDetails]
          ([Order_No],[Item_Name],[Notes],[QTY] ,[Price])
   VALUES
          (1,'Coffee','Hot and more Suger',1 ,80)
        
          INSERT INTO [OrderDetails]
          ([Order_No],[Item_Name],[Notes],[QTY] ,[Price])
   VALUES
          (1,'Burger','Spicy',3 ,140)
        
          INSERT INTO [OrderDetails]
          ([Order_No],[Item_Name],[Notes],[QTY] ,[Price])
   VALUES
          (2,'Pizza','More Chees and Large',1 ,350)        
 
          INSERT INTO [OrderDetails]
          ([Order_No],[Item_Name],[Notes],[QTY] ,[Price])
   VALUES
          (2,'Cola','Need very Cold',3 ,50)
        
          INSERT INTO [OrderDetails]
          ([Order_No],[Item_Name],[Notes],[QTY] ,[Price])
   VALUES
          (3,'IDLY','Hot',3 ,40)
 
          INSERT INTO [OrderDetails]
          ([Order_No],[Item_Name],[Notes],[QTY] ,[Price])
   VALUES
          (3,'Thosa','Hot',3 ,50)
 
-- To Select and test Order Master and Details
 
Select * FROM OrderMasters
Select * From OrderDetails

 

테이블을 생성한 후, Master 를 위한 저장프로시저를 생성하는 쿼리 실행 합니다.

 

-- 1) Stored procedure to Select OrderMaster
-- Author      : Shanu                                                             
-- Create date : 2015-10-26                                                              
-- Description : Order Master                                              
-- Tables used :  OrderMaster                                                              
-- Modifier    : Shanu                                                               
-- Modify date : 2015-10-26                                                               
-- =============================================  
-- exec USP_OrderMaster_Select '',''
-- =============================================                                                          
Create PROCEDURE [dbo].[USP_OrderMaster_Select]                                            
   (                          
     @OrderNo           VARCHAR(100)     = '',
     @Table_ID               VARCHAR(100)     = ''  
      )                                                      
AS                                                              
BEGIN      
         Select [Order_No],
                [Table_ID],
                [Description],
                [Order_DATE],
                [Waiter_Name]
            FROM
                OrderMasters
            WHERE
                Order_No like  @OrderNo +'%'
                AND Table_ID like @Table_ID +'%'
            ORDER BY
                Table_ID  
END
 
-- 2) Stored procedure to insert OrderMaster
-- Author      : Shanu                                                              
-- Create date : 2015-10-26                                                             
-- Description : Order Master                                             
-- Tables used :  OrderMaster                                                              
-- Modifier    : Shanu                                                               
-- Modify date : 2015-10-26                                                                 
-- =============================================   
-- exec USP_OrderMaster_Insert 'T4','Table 4','SHANU'
-- =============================================                                                          
Create PROCEDURE [dbo].[USP_OrderMaster_Insert]                                             
   (                      
     @Table_ID           VARCHAR(100)     = '',
     @Description               VARCHAR(100)     = '',
     @Waiter_Name               VARCHAR(20)     = ''
      )                                                       
AS                                                               
BEGIN       
        IF NOT EXISTS (SELECT Table_ID FROM OrderMasters WHERE Table_ID=@Table_ID)
            BEGIN
                  INSERT INTO [OrderMasters]
          ([Table_ID] ,[Description],[Order_DATE],[Waiter_Name])
    VALUES
          (@Table_ID,@Description,GETDATE(),@Waiter_Name )                           
 
                  Select 'Inserted' as results                     
         END
         ELSE
             BEGIN
                     Select 'Exists' as results
              END
END
 
-- 3) Stored procedure to Update OrderMaster
   
-- Author      : Shanu                                                               
-- Create date : 2015-10-26                                                              
-- Description : Order Master                                             
-- Tables used :  OrderMaster                                                              
-- Modifier    : Shanu                                                               
-- Modify date : 2015-10-26                                                               
-- =============================================     
-- exec USP_OrderMaster_Update 4,'T4','Table 4 wer','SHANU'
-- =============================================                                                          
CREATE PROCEDURE [dbo].[USP_OrderMaster_Update]                                             
   (  @OrderNo               Int=0,                          
      @Table_ID           VARCHAR(100)     = '',
      @Description               VARCHAR(100)     = '',
      @Waiter_Name               VARCHAR(20)     = ''
      )                                                       
AS                                                               
BEGIN      
        IF NOT EXISTS (SELECT Table_ID FROM OrderMasters WHERE Order_No!=@OrderNo AND Table_ID=@Table_ID)
            BEGIN
                    UPDATE OrderMasters
                    SET    [Table_ID]=@Table_ID ,
                                                                  [Description]=@Description,
                                                                   [Order_DATE]=GETDATE(),
                                                                   [Waiter_Name]=@Waiter_Name
 
                    WHERE
                        Order_No=@OrderNo                             
                    Select 'updated' as results                       
            END
         ELSE
             BEGIN
                     Select 'Exists' as results
              END
END
 
-- 4) Stored procedure to Delete OrderMaster 
 
-- Author      : Shanu                                                               
-- Create date : 2015-10-26                                                              
-- Description : Order Master                                             
-- Tables used :  OrderMaster                                                               
-- Modifier    : Shanu                                                               
-- Modify date : 2015-10-26                                                                
-- ============================================= 
-- exec USP_OrderMaster_Delete '3'
-- =============================================                                                          
CREATE PROCEDURE [dbo].[USP_OrderMaster_Delete]                                             
 
   (  @OrderNo               Int=0 )                                                        
AS                                                               
BEGIN       
        DELETE FROM OrderMasters WHERE           Order_No=@OrderNo            
                           DELETE from OrderDetails WHERE  Order_No=@OrderNo    
 
                            Select 'Deleted' as results
 
END

 

테이블을 생성한 후,Detail 를 위한 저장프로시저를 생성하는 쿼리 실행 합니다.

 

USE OrderManagement
GO
 
-- 1) Stored procedure to Select OrderDetails
-- Author      : Shanu                                                               
-- Create date : 2015-10-26                                                              
-- Description : OrderDetails                                           
-- Tables used :  OrderDetails                                                              
-- Modifier    : Shanu                                                               
-- Modify date : 2015-10-26                                                               
-- =============================================  
-- exec USP_OrderDetail_Select '1'
-- =============================================                                                          
Create PROCEDURE [dbo].[USP_OrderDetail_Select]                                              
   (                           
     @OrderNo           VARCHAR(100)     = '' 
      )                                                       
AS                                                               
BEGIN       
         Select Order_Detail_No,
                 [Order_No],
                [Item_Name],
                [Notes],
                [QTY],
                [Price]
            FROM
                OrderDetails
            WHERE
                Order_No like  @OrderNo +'%'            
            ORDER BY
                Item_Name   
END
 
-- 2) Stored procedure to insert OrderDetail
-- Author      : Shanu                                                               
-- Create date : 2015-10-26                                                               
-- Description : Order Master                                             
-- Tables used :  OrderDetail                                                              
-- Modifier    : Shanu                                                                
-- Modify date : 2015-10-26                                                                 
-- =============================================   
-- exec USP_OrderDetail_Insert 4,'cadburys','cadburys Chocolate','50',50
-- =============================================                                                         
Create PROCEDURE [dbo].[USP_OrderDetail_Insert]                                             
   ( 
     @Order_No                                          VARCHAR(10),                    
     @Item_Name           VARCHAR(100)     = '',
     @Notes               VARCHAR(100)     = '',
     @QTY                 VARCHAR(20)     = '',
     @Price               VARCHAR(20)     = ''
      )                                                       
AS                                                              
BEGIN       
        IF NOT EXISTS (SELECT Item_Name FROM OrderDetails WHERE Order_No=@Order_No AND Item_Name=@Item_Name)
            BEGIN
                  INSERT INTO [OrderDetails]
          ( [Order_No],[Item_Name],[Notes],[QTY] ,[Price])
    VALUES
          ( @Order_No,@Item_Name,@Notes,@QTY ,@Price )
                    Select 'Inserted' as results                       
            END
         ELSE
             BEGIN
                     Select 'Exists' as results
              END
END
 
-- 3) Stored procedure to Update OrderDetail
  
-- Author      : Shanu                                                               
-- Create date : 2015-10-26                                                              
-- Description : Order Master                                             
-- Tables used :  OrderDetail                                                              
-- Modifier    : Shanu                                                               
-- Modify date : 2015-10-26                                                                
-- =============================================     
-- exec USP_OrderDetail_Update 8,4,'Cadburys','cadburys Chocolate','50',50
-- =============================================                                                          
ALTER PROCEDURE [dbo].[USP_OrderDetail_Update]                                             
   (  @Order_Detail_No   Int=0,                          
      @Order_No                                        VARCHAR(10),                    
      @Item_Name           VARCHAR(100)     = '',
      @Notes               VARCHAR(100)     = '',
      @QTY                 VARCHAR(20)     = '',
      @Price               VARCHAR(20)     = ''
      )                                                      
AS                                                                
BEGIN       
        IF NOT EXISTS (SELECT Item_Name FROM OrderDetails WHERE Order_Detail_No!=@Order_Detail_No AND Item_Name=@Item_Name)
            BEGIN
                    UPDATE OrderDetails
                    SET   [Item_Name]=@Item_Name,
                                                          [Notes]=@Notes,
                                                                   [QTY] =@QTY,
                                                                  [Price]=@Price
                    WHERE
                       Order_Detail_No=@Order_Detail_No
                            AND  Order_No=@Order_No
                    Select 'updated' as results                      
            END
         ELSE
             BEGIN
                     Select 'Exists' as results
              END
END
-- 4) Stored procedure to Delete OrderDetail
   
-- Author      : Shanu                                                               
-- Create date : 2015-10-26                                                              
-- Description : Order Master                                             
-- Tables used :  OrderDetail                                                              
-- Modifier    : Shanu                                                               
-- Modify date : 2015-10-26                                                                 
-- ============================================= 
-- exec USP_OrderDetail_Delete '8'
-- ============================================                                                          
CREATE PROCEDURE [dbo].[USP_OrderDetail_Delete]                                             
   (  @Order_Detail_No               Int=0 )                                                       
AS                                                               
BEGIN       
                             DELETE from OrderDetails WHERE  Order_Detail_No=@Order_Detail_No
                            Select 'Deleted' as results          
 
END

 

 

2. Create your MVC Web Application in Visual Studio 2015

( vs 2015 로 mvc web application 생성하기)

 

Visual Studio 2015  설치한 후, web 에서 asp.net web application 을 선택하고 name 을 입력합니다.

 

 

asp.net 4.6 templates 에서 mvc 를 선택하고, 하단에 add folders and core reference for : 부분에 web api 도 체크해 줍니다.

 

ADO.NET ENTITY DATA MODEL 를 사용하기 위해 테이블을 추가합니다.

 

우클릭 해서 ADD > NEW ITEM 을 선택합니다.

 

여기서 ADO.NET ENTITY DATA MODEL 을 선택하고 NAME 을 기재후, ADD 합니다.

 

EF Designer 에서 database 를 선택한 다음 , next 합니다.

 

database 의 서버 이름을 설정하고, user name, password 를 기재한 후, database name 을 선택합니다. ok 버튼을 선택합니다.

 

여기서 yes , 를 선택하고 나서 connectionstring 을 민감한 data 로 포함합니다.

 

이제 테이블을 선택한 다음, 생성한 저장프로시저를 전부 선택하고 finish 를 선택합니다.

 

그럼, OrderDetailModel.edmx 라는 파일이 생성 되어 집니다.

 

entity 를 생성하고 나서, 이제 web api 를 controller 에서 추가합니다. 그리고 그 함수에 select , insert, update , delete 를 기술합니다.

web api controller 에 저장프로시저를 추가합니다. controller 폴더에 우클릭해서 controller 를 클릭합니다.

 

controller 를 선택하고, 여기서 empty web api 2 controller 를 추가합니다. web api controller 이름을 적고, ok 를 클릭합니다. 여기서 web api controller 는 “OrderAPIController” 라고 기재합니다.
데모 프로젝트에서 2개의 다른 controller 를 생성하는데, 하나는 order master , 또 하나는 order detail 입니다.
알다시피 web api 는 브라우저와 모바일에 대한 http 서비스를 간단하고 쉽게 사용할 수 있도록 설계되어 있습니다.
web api 는 메소드가 4가지 제공되며,  Get / Post / Put / Delete 가 있습니다.

 

• 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

예제에서는 저장프로시저를 사용해서 get 메소드만 사용됩니다.  그래서 entity 에 대한 object 를 생성하고,
Select/Insert/Update and Delete 의 operation 를 하기 위해, Get 메소드를 기재합니다.

Select Operation

entity object 를 사용하기 위해 OrderMasters 의 모든 detail 를 가져오는 메소드로 사용할겁니다. 그리고 이는 IEnumerable 로 결과값을 반환할 것이고요.
이제 AngularJs 안에 해당 메소드를 사용할거고 AngularJs controller 를 통해 mvc 페이지에 결과를 보여줄겁니다. Ng-Repeat 를 사용해서 detail 에 바인딩을 할 수 있습니다.
여기에 검색 파리미터를 전송해서 USP_OrderMaster_Select 저장프로시저를 실행하는 구문은 아래와 같습니다. 저장프로시저에 검색 파라미터값이 없다면 모든 레코드를
반환할 수 있도록 % 를 사용했습니다.

WHERE                 
Order_No like  @OrderNo +'%'                 
AND Table_ID like @Table_ID +'%' 

 

      OrderManagementEntities objapi = new OrderManagementEntities();          // to Search Student Details and display the result  [HttpGet]   public IEnumerable<USP_OrderMaster_Select_Result> Get(string OrderNO, string TableID)        {              if (OrderNO == null)                     OrderNO = "";               if (TableID == null)                     TableID = "";             return objapi.USP_OrderMaster_Select(OrderNO, TableID).AsEnumerable();  }

예제에서 Select/Insert/Update and Delete operations 를 위해 get 메소드를 사용했고, insert/update and delete 를 저장프로시저로 호출해서 database 에 전달하여 결과값을 반환 받았습니다.

 

Insert Operation

동일하게 insert 저장프로시저에 모든 파라미터를 전송하여 처리했습니다. 그리고 결과값을 얻고 AngularJs Controller  에서 mvc application 를 통해 보여줄겁니다.

 

   // To Insert new Student Details  [HttpGet]     public IEnumerable<string> insertOrderMaster(string Table_ID,string Description,string Waiter_Name)     {             return objapi.USP_OrderMaster_Insert( Table_ID, Description, Waiter_Name).AsEnumerable();     }

 

Update Operation

insert 저장프로시저에 모든 파라미터를 전송 처리 하는 동일한 방법으로 update 메소드도 아래 처럼 구현했습니다.
나머지 설명은 insert 와 동일합니다.

 

//to Update Student Details
[HttpGet]
public IEnumerable<string> updateOrderMaster(int OrderNo, string Table_ID, string Description, string Waiter_Name)
{
    return objapi.USP_OrderMaster_Update(OrderNo, Table_ID, Description, Waiter_Name).AsEnumerable();
}

 

Delete Operation

삭제 부분입니다.

//to Delete Student Details
[HttpGet]
public IEnumerable<string> deleteOrderMaster(int OrderNo)
{
    return objapi.USP_OrderMaster_Delete(OrderNo).AsEnumerable();
}

 

OrderMasterController  도 동일하게 DetailAPI 처럼 또 다른 controller 를 생성합니다. 이는 detail table 의 CRUD Operations 를 기재하기 위함입니다.
아래는 detailController 를 위한 WEB API 코드 입니다.

 

OrderMasterController

public class DetailAPIController : ApiController
{
  OrderManagementEntities objapi = new OrderManagementEntities();
  // to Search Student Details and display the result
  [HttpGet]
  public IEnumerable<USP_OrderDetail_Select_Result> Get(string OrderNO)
  {  
    if (OrderNO == null)
      OrderNO = "0";
    return objapi.USP_OrderDetail_Select(OrderNO).AsEnumerable();
  }
 
  // To Insert new Student Details
  [HttpGet]
  public IEnumerable<string> insertOrderDetail(string Order_No, string Item_Name, string Notes, string QTY, string Price)
  {
    return objapi.USP_OrderDetail_Insert(Order_No, Item_Name, Notes, QTY, Price).AsEnumerable();
  }
 
  //to Update Student Details
  [HttpGet]
  public IEnumerable<string> updateOrderDetail(int Order_Detail_No, string Order_No, string Item_Name, string Notes, string QTY, string Price)
  {
    return objapi.USP_OrderDetail_Update(Order_Detail_No, Order_No, Item_Name, Notes, QTY, Price).AsEnumerable();
  }
 
  //to Delete Student Details
  [HttpGet]
  public IEnumerable<string> deleteOrderDetail(int Order_Detail_No)
  {
    return objapi.USP_OrderDetail_Delete(Order_Detail_No).AsEnumerable();
  }
 
}

자 이제, WEB API 컨트롤러 CLASS 를 생성하고, 그다음 AngularJs 의 Module 과 Controller 를 생성하는 겁니다.
AngularJs Controller 를 생성하는 방법은 Visual Studio 2015 에서 쉽게 생성할 수 있습니다.
이제 따라해 보죠.

Creating AngularJs Controller

 

Script 폴더에 MyAngular 라는 폴더를 만듭니다.

 

 

그리고 web 에서 AngularJS Controller 의 template 를 선택하고 Controller.js 라고 이름 기재하고 add 합니다.

AngularJs Controller 를 생성하자마자, 기본값이 아래와 같이 나옵니다.

 

AngularJs package 가 없을 경우, package 에 AngularJs 를 추가하면 됩니다.
이는 mvc project 에서 우클릭하면 Manage NuGet Packages 가 존재합니다. 이를 클릭해서 “AngularJs “ 를 검색하고 설치하면 됩니다.

 

Procedure to Create AngularJs Script Files

 

Module.js : AngularJs JavaScript 를 참조할 때 사용되며, “RESTClientModule” 이라는 Angular Module 을 생성할겁니다.

 

// <reference path="../angular.js" /> 
/// <reference path="../angular.min.js" />  
/// <reference path="../angular-animate.js" />  
/// <reference path="../angular-animate.min.js" /> 
var app;
(function () {
    app = angular.module("RESTClientModule", ['ngAnimate']);
})();

Controllers : AngularJs Controller 에서 모든 business logic 를 구현하고, web api 에서 mvc html page 에 데이터를 반환하는 
부분을 구현하는 곳입니다.

 

 

1. Variable declarations : 먼저 사용하는데 필요한 로컬 변수는 여기에 전부 기술합니다.
First I declared all the local variables that need to be used.

 

app.controller("AngularJs_studentsController", function ($scope, $timeout, $rootScope, $window, $http) {
 
    $scope.date = new Date();
    $scope.MyName = "shanu";
 
    //For Order Master Search
    $scope.OrderNos = "";
    $scope.Table_IDs = "";  
 
    //This variable will be used for Insert/Edit/Delete OrderMasters Table.
    $scope.OrderNo = 0;
    $scope.Table_ID = "";
    $scope.Description = "";
    $scope.Waiter_Name = "";
    //Show Hide OrderMaster Table
 
    $scope.showOrderMasterAdd = true;
    $scope.addEditOrderMaster = false;
    $scope.OrderMasterList = true;
    $scope.showItem = true;
 
    //This variable will be used for Insert/Edit/Delete OrderDetail Table.
    $scope.Order_Detail_No = 0;
    $scope.Item_Name ="";
    $scope.Notes = "";
    $scope.QTY = "1";
    $scope.Price = "0";  
 
    $scope.addEditOrderDetail = false;
    $scope.expandImg = "expand.png";

2. Methods

 

Select Method

 

select 메소드에서 web api 를 통해 detail 을 얻기 위해선 $http.get 를 사용합니다.
get 메소드에서  API Controller name 을 기재하며, details 를 get 할 메소드를 기재합니다.
검색 파라미터에 OrderNo 와 TableID 를 전달하기 위한 부분을 기술하였습니다.

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

최종 결과는 data-ng-repeat 를 사용해서 mvc html 을 보여주는 것입니다.

 

$http.get('/api/OrderAPI/', { params: { OrderNO: OrderNos, TableID: Table_IDs } }).success(function (data) {
 
            $scope.OrderMasters = data;
            $scope.showOrderMasterAdd = true;
            $scope.addEditOrderMaster = false;
            $scope.OrderMasterList = true;
            $scope.showItem = true;
            $scope.addEditOrderDetail = false;
            if ($scope.OrderMasters.length > 0) {
            }
        })
   .error(function () {
       $scope.error = "An Error has occured while loading posts!";
   });
    }

 

Search Button Click

 

 

 

검색 버튼을 클릭하면, 결과 값을 얻기 위해 SearchMethod 를 호출합니다. 검색 text box 에는 ng-model=”OrderNos” 라고 사용된 부분이 있습니다.
AngularJs Controller  에서 ng-model 을 사용하면, textbox 의 입력값을 얻을 수 있습니다. 또는 값을 설정하기도 하고요.

 

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

Insert new Order Master

 

ADD New Student Detail 의 버튼을 클릭하면, StudentAdd 테이블이 보이며, 신규 학생 정보를 입력할 수 있도록 화면을 제공합니다.
신규 학생을 위해 학생 id 를 기본값으로 0 할당해서 생성하고, 저장 버튼 클릭하면 save method 를 호출합니다.

// New Student Add Details
    $scope.showOrderMasters = function () {
        cleardetails();
        $scope.addEditOrderDetail = false;
        $scope.showOrderMasterAdd = true;
        $scope.addEditOrderMaster = true;
        $scope.OrderMasterList = true;
        $scope.showItem = true;
    }

Save method 에서 OrderNo 를 선택해 봅니다. 만약에 OrderNo 가 0 이라고 하면, 신규로 Order Master detail 에 저장됩니다.
그럼, 저장하는 Web API method 이 호출되고 OrderNo 가 0 보다 크면 Order record 값을 읽어서 Update Web API method 에 호출합니다.

 

 

Insert Web API Method 에서 모든 입력 파라미터를 전송합니다. 저장프로시저에서 Order 테이블에 값이 존재하는지 체크합니다.
그래서 테이블이 데이터베이스에 존재하지 않으면, 레코드에 저장을 하고 inserted 라고 성공 메시지를 보여줍니다. 존재하면 Exits 라고 반환하고요.

 

//Save OrderMaster
    $scope.saveDetails = function () {
        $scope.IsFormSubmitted1 = true;
        if ($scope.IsFormValid1) {        
             if ($scope.OrderNo == 0) {
                $http.get('/api/OrderAPI/insertOrderMaster/',                    
                  { params: { Table_ID: $scope.Table_ID,                            
                      Description: $scope.Description,                            
                      Waiter_Name: $scope.Waiter_Name } }).success(function (data) {
                    $scope.orderMasterInserted = data;
                    alert($scope.orderMasterInserted);
                    cleardetails();
                    selectOrderMasters('', '');
                })
         .error(function () {
             $scope.error = "An Error has occured while loading posts!";
         });
            }
            else {  // to update to the student details
                $http.get('/api/OrderAPI/updateOrderMaster/',                   
                   { params: { OrderNo: $scope.OrderNo, Table_ID: $scope.Table_ID,                       
                         Description: $scope.Description,                       
                         Waiter_Name: $scope.Waiter_Name } }).success(function (data) {
                    $scope.orderMasterUpdated = data;
                    alert($scope.orderMasterUpdated);
                    cleardetails();
                    selectOrderMasters('', '');
                })
        .error(function () {
            $scope.error = "An Error has occured while loading posts!";
        });
            }
        }
        else {
            $scope.Message1 = "All the fields are required.";
        }   
}

Update Order Master

 

 

update 는 insert 와 동일하게 작동합니다.

 

//Edit Order Details
$scope.OrderMasterEdit = function OrderMasterEdit(OrderNoss, Table_IDss, Descriptionss, Waiter_Namess) {
        cleardetails();
        $scope.OrderNo = OrderNoss;
        $scope.Table_ID = Table_IDss
        $scope.Description = Descriptionss;
        $scope.Waiter_Name = Waiter_Namess;   
 
        $scope.addEditOrderDetail = false;
        $scope.showOrderMasterAdd = true;
        $scope.addEditOrderMaster = true;
        $scope.OrderMasterList = true;
        $scope.showItem = true;
    }

Delete Order Master Details

 

 

삭제 버튼을누르면, confirm 창이 뜨고, 삭제할건지 말건지 물어봅니다.
삭제 ok 하면 OrderNo 값이 web api 를 통해 database 의 데이터를 삭제합니다.

 

 

//Delete Order master Detail
    $scope.OrderMasterDelete = function OrderMasterDelete(OrderNoss) {
        cleardetails();
        $scope.OrderNo = OrderNoss;
    var delConfirm = confirm("Are you sure you want to delete the Order Master " + OrderNoss + " ?");
        if (delConfirm == true) {
         //   alert($scope.OrderNo);
            $http.get('/api/OrderAPI/deleteOrderMaster/',                { params: { OrderNo: $scope.OrderNo } }).success(function (data) {
               // alert(data);
                $scope.orderMasterDeleted= data;
                alert($scope.orderMasterDeleted);
                cleardetails();
                selectOrderMasters('', '');
            })
      .error(function () {
          $scope.error = "An Error has occured while loading posts!";
      });
        }
    }

 

Filter and Sorting Order Master

 

 

필터에는 pipe symbol 를 사용해서 ng-repeat 와 함께 추가할 수 있습니다.
ng-repeat 에서 볼 수 있듯이 filter 를 추가했고, filter 를 위해 textbox 의 Model id 를 설정했습니다.
사용자가 textbox 에 값을 입력하면 filter 가 loop 를 돌면서 적용해 주고, 아래와 같이 적절한 값을 보여줍니다.

 

 

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

Sorting Order Master

 

 

필터에 order by 로 동일한 기술로 구현하였습니다. pipe symbol 을 사용해서 ng-repeat 로 값을 reverse 합니다.
OrderBy 는 pipe symbol 를 사용해서 ng-repeat 를 가지고 추가될 수 있습니다.
ng-repeat 에서 filter 를 통해 search 를 줄 수 있고 , 이는 모든 textbox 의 값을 필터들을 제공할 수 있습니다.

 

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

Displaying Order Detail

 

여기서, Detail 버튼 클릭을 통해, Order Master 안에 Order Detail 그리드를 보여주는 방법입니다.
각 Order Master 의 행을 클릭할면, 행이 activeRow … 즉 활성화에 대해 체크 합니다. 그리고 나서 detail button 이 클릭되어 지고 이는 showNewOrderDetails()
함수를 통해 details 가 보여지게 됩니다.

 

<tr ng-show="activeRow==stds.Order_No" >

상세 버튼이 클릭되면 호출되는 함수임

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

Order Detail CRUD 에 대해, 동일한 로직으로 정렬, filter 도 구현하고 사용해 왔습니다. 이제 결과값을 볼까요?

 

Order Detail Add:

Order Detail Edit:

Order Detail Delete:

Order Detail Filtering and Sorting:

Source Code Files

• MasterDetailCRUD.zip

More Information

The main aim of this article is to create a simple MVC Web Based  Master/Detail CRUD,Filtering and Sorting Operation using Angular JS WEB API 2 with Stored Procedure.