Tutorial_SPA_CSharp
September 19th, 2016
The objective is to learn the development of Single Page Application with Knockout and Web API using Open Touryo framework, by developing sample programs according to the exercises in this tutorial. This tutorial explains the generation of scripts and class files by using the tool, flow and functionality of single page application with an example. For details of single page application, refer MSDN.
SE/Developers should have knowledge on ASP.NET Single Page Application development. Scope of this tutorial is learning how to use Open Touryo framework in single page application.
This tutorial describes the development flow for Single Page Application by using Open Touryo framework. The sample program attached to Open Touryo Visual Studio 2015 template base is used as the material of this tutorial.
As we are pressed for time, some images are only displayed in Japanese.
The company names and product names used in this document are the trademarks or registered trademarks of the respective companies.
This document can use Creative commons CC BY 2.1 JP license.
1. Overview of Open Touryo framework
Open Touryo framework is an application framework for .NET. Open Touryo framework targets .NET Framework 4.6 and above and can be used in various applications like C/S (Windows Forms, WPF), Web (ASP.NET) and RIA (Silverlight). ASP.NET Single Page Application (SPA) is an ASP.NET web application developed using Model-View-Controller (MVC) and Model-View-ViewModel (MVVM) patterns.
Figure 1-1 shows the class configuration of Open Touryo framework. Compared to traditional ASP.NET, configuration of B layer (business logic layer) and D layer (data access layer) part of existing Open Touryo framework does not change even in ASP.NET SPA. Thus developers, who are experienced in using Open Touryo framework in ASP.NET, can use know-how development of B layer and D layer part.
Figure 1-1 Class diagram of Single Page Application using Open Touryo framework
This tutorial explains how to create Screen / Business logic class / Data access class to be implemented by developer according to Class configuration diagram - Figure 1-1. Further, this tutorial uses Razor syntax to create view.
The followings are the prerequisites for this tutorial.
- Development environment
- IDE
- Visual Studio 2015 (Express Edition is also available)
- Application framework
- Open Touryo Template Base for Visual Studio 2015
- IDE
- Runtime environment
- Runtime
- .NET Framework 4.6
- DB
- SQL Server
- Runtime
- Others
- OS
- Windows 7
- Programming language
- C#
- OS
Install Visual Studio referring to Microsoft homepage beforehand.
Next, set up Open Touryo Template Base and database.
-
Click [Download ZIP] button on GitHub and obtain OpenTouryoTemplates.zip. Unzip this zip file and obtain Open Touryo Template Base for Visual Studio 2015.
-
Set up Open Touryo Template Base and database according to Readme.md in root_VS2015 folder.
Users of this tutorial can practice by adding screen or logic to the sample program that bundled with Open Touryo template base. Figure 3-1 shows the configuration of sample program and Figure 3-2 shows the screen transition diagram of the tutorial.
Figure 3-1 Configuration of sample program
When the exercise of this tutorial is completed, three types of class, indicated in the following table, are created for data access class. For details on these classes, refer User guide of Open Touryo (Better use and FAQ Edition).
| Data Access class | Explanation |
|---|---|
| Auto generation DAO | DAO class that is generated by D layer auto generation tool bundled with Open Touryo template base. This class is used while performing simple CRUD process in table/view. |
| Common DAO | DAO class that is provided by Open Touryo framework. This class is used when D layer auto generation tool can not be used, such as the case of obtaining the result based on joined tables. |
| DAO summary class | Facade class in D layer. This class is used for summarizing requests from business logic class when using multiple DAO classes in one transaction. |
Figure 3-2 Screen transition diagram of tutorial
The following section describes development flow of single page application using Open Touryo framework.
-
Open
C:\root\programs\C#\Samples\WebApp_sample\SPA_Sample\SPA_Sample.sln. -
Confirm that Visual Studio is launched and the sample program that bundled with Open Touryo template base is opened.
Open Touryo stores the input data sended from View in Parameter class. And Open Touryo sends the Parameter class to business logic class and data access class. On the other hand, Open Touryo stores the result data of data access or business logic in Result class. And Open Touryo sends the Result data to View.
When using SPA, as shown in Figure 1-1, Web API receives the input data (JSON format) sent from the View. Open Touryo stores the data, that Web API receives, in Parameter class, and sends the data to business logic class and data access class. On the other hand, Open Touryo stores the result data of data access or business logic in Result class and returns the data to Web API. Furthermore, the data are converted to JSON format and is sent to the View.
Create the Parameter class and the Return class in this section.
-
Select
Codes\Commonfolder in the Solution Explorer. And click Project -> Add Class in toolbar.
-
Add new class named
OrderParameterValue.cs. -
Add the following namespaces in
OrderParameterValue.csto import the classes provided by Open Touryo.using Touryo.Infrastructure.Business.Util; using Touryo.Infrastructure.Business.Common;
-
Inherit Parameter Value Parent Class of Open Touryo to
OrderParameterValueclass as shown below.public class OrderParameterValue : MyParameterValue
-
Create
OrderParameterValueconstructor with the following code.public OrderParameterValue(string screenId, string controlId, string methodName, string actionType, MyUserInfo user) : base(screenId, controlId, methodName, actionType, user) { }
-
Create the following properties in
OrderParameterValueclass to transfer data from form to data access class./// <summary>Order ID</summary> public string OrderId; /// <summary>Order information (Summary)</summary> public System.Data.DataTable Orders; /// <summary>Order information (Details)</summary> public System.Data.DataTable OrderDetails;
-
In the same way to section 4.2.1, create new class named
OrderReturnValue.csinCodes\Commonfolder. -
Add the following namespace in
OrderReturnValue.csto import the classes provided by Open Touryo.using Touryo.Infrastructure.Business.Common;
-
Inherit the Return Value Parent Class of Open Touryo to
OrderReturnValueclass as shown below.public class OrderReturnValue : MyReturnValue
-
Create the following properties in
OrderReturnValueclass to transfer the resultant data from data access class to form./// <summary>Order information (Summary)</summary> public System.Data.DataTable Orders; /// <summary>Order information (Details)</summary> public System.Data.DataTable OrderDetails;
Perform the following steps to generate data access class and SQL files that implement simple CRUD operation for table or view, using the D layer auto generation tool (DaoGen_Tool) bundled with Open Touryo template base.
-
Open
C:\root\programs\C#\Frameworks\DaoGen_Tool\bin\Debug\DaoGen_Tool.exe. -
In the STEP1 screen, set database information as follows and click acquisition.
- Data provider: SQL Server Client
- Connection string: Data Source=localhost\SQLExpress;Initial Catalog=Northwind;Integrated Security=true;
- Schema Info: Summary Information
-
If database information, such as connection string, are correct, Display (Summary Information) dialog of schema information of DBMS screen is shown.
Click Close to close the dialog box.
-
Click Get Table List.
In the Note? (prerequisites) dialog box, click OK.
-
Tables and views in Northwind database are shown in list box. Since Orders table and Order Details table will be used in this tutorial, select all tables and views except Order table and Order Details table, and click Delete.
-
Confirm whether Table List contains only Order table and Order Details table, and click Load.
-
The Generate D layer definition file button is activated. Select utf-8 as file encoding and click Generate D layer definition file.
Save as
C:\root\Info.csv. -
Click OK in the dialog box displaying the message Completion of generation of the D-layer definition information!.
-
Click Go to STEP 2.
-
In the STEP2 screen, enter input / output settings as follows:
- D layer definition file: C:\root\Info.csv
- Source Template Folder: C:\root\files\tools\DGenTemplates
- Output File: C:\root
- Leave the other fields as default.
Click Generate Program.
-
Click OK in the dialog box displaying the message Automatic Generation Completed!.
-
Confirm that data access classes and SQL files are generated in
C:\rootfolder.
Note:
In the Open Touryo framework, the files with extensions.sqland.xmlare SQL files. (For more details, refer to the Open Touryo framework user guide) -
To add generated data access class to sample program, select
Daofolder in Solution Explorer, and click Project -> Add Existing File in toolbar.
-
In the Add Existing Item screen, select
DaoOrders.csandDaoOrder_Details.csinC:\rootfolder. Click Add. -
Copy the generated SQL and XML files in
C:\roottoC:\root\files\resource\Sqlfolder. -
Close D layer auto generation tool.
D layer auto generation tool can generate data access class and SQL file for simple CRUD processing. However, the developer should create sql file individually for complicated processing, such as join multiple tables.
-
Create
SelectOrders.sqlfile inC:\root\files\resource\Sqlfolder. -
Add the following SQL scripts in SelectOrders.sql to get the list of orders.
SELECT Orders.OrderID, Customers.CompanyName, Customers.ContactName, Employees.LastName As EmployeeLastName, Employees.FirstName As EmployeeFirstName, Orders.OrderDate FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
In this tutorial, the following data access classes are used.
- DaoOrders
- For access to Orders table
- DaoOrder_Details
- For access to Order Details table
- CmnDao (Common data access class with Open Touryo)
- For access with SQL file created in section 4.5.2.
In this section describes how to create Dao Summary Class. Dao summary Class is Facade for business logic class, and controls the call the above data access classes.
-
Select
Daofolder in the Solution Explorer. And click Project -> Add Class in toolbar.
-
Add class named
ConsolidatedLayerD.cs. -
Add the following namespaces to
ConsolidatedLayerD.csto import the classes provided by Open Touryo, and parameter and return value class created in section 4.3.// Open Touryo using Touryo.Infrastructure.Business.Dao; using Touryo.Infrastructure.Public.Db; // Parameter and return value class using SPA_sample.Common;
-
Inherit Data Access Parent Class of Open Touryo framework to
ConsolidatedLayerDclass as shown below.public class ConsolidatedLayerD : BaseConsolidateDao
-
Create ConsolidatedLayerD constructor with the following code.
public ConsolidatedLayerD(BaseDam dam) : base(dam) { }
-
Create
GetOrdersmethod to get the list of orders with the following code.public OrderReturnValue GetOrders(OrderParameterValue orderParameter) { // Create an object of Return Value class OrderReturnValue returnValue = new OrderReturnValue(); // Create an object of common DAO and assign SQL file CmnDao dao = new CmnDao(this.Dam); dao.SQLFileName = "SelectOrders.sql"; // Create an object of DataTable System.Data.DataTable table = new System.Data.DataTable(); // Get the list of orders information from the database and stored in a DataTable dao.ExecSelectFill_DT(table); // Store the orders information to the Return Value class and return to the B layer returnValue.Orders = table; return returnValue; }
-
Create
GetOrderByIdmethod to get the details of specific order summary and order details information based on the Order ID with the following code.public OrderReturnValue GetOrderById(OrderParameterValue orderParameter) { // Create an object of Return Value class OrderReturnValue returnValue = new OrderReturnValue(); // Create an object of auto generated DAO classes DaoOrders orderDao = new DaoOrders(this.Dam); DaoOrder_Details orderDetailsDao = new DaoOrder_Details(this.Dam); // Create objects of DataTable to store the details of specified order information System.Data.DataTable orderTable = new System.Data.DataTable(); System.Data.DataTable orderDetailsTable = new System.Data.DataTable(); // Set the required parameters orderDao.PK_OrderID = orderParameter.OrderId; orderDetailsDao.PK_OrderID = orderParameter.OrderId; // Get the details of specific order information from the database and stored in the DataTable orderDao.D2_Select(orderTable); orderDetailsDao.D2_Select(orderDetailsTable); // Store the details of specific order information to the Return Value class and return to the B layer returnValue.Orders = orderTable; returnValue.OrderDetails = orderDetailsTable; return returnValue; }
-
Create
UpdateOrdermethod to update the details of specified order summary and order details information with the following code.public OrderReturnValue UpdateOrder(OrderParameterValue orderParameter) { // Create an object of Return Value class OrderReturnValue returnValue = new OrderReturnValue(); // Create an object of auto generated DAO classes DaoOrders orderDao = new DaoOrders(this.Dam); DaoOrder_Details orderDetailsDao = new DaoOrder_Details(this.Dam); // Create an object of DataTable to store order information and order details information System.Data.DataTable orderTable = orderParameter.Orders; System.Data.DataTable orderDetailsTable = orderParameter.OrderDetails; // Judge the state of DataRow, and update database if DataRow is modified if (orderTable.Rows[0].RowState == System.Data.DataRowState.Modified) { // Set the parameters of the order information orderDao.PK_OrderID = orderTable.Rows[0]["OrderId"]; orderDao.Set_OrderDate_forUPD = orderTable.Rows[0]["OrderDate"]; orderDao.Set_RequiredDate_forUPD = orderTable.Rows[0]["RequiredDate"]; orderDao.Set_ShippedDate_forUPD = orderTable.Rows[0]["ShippedDate"]; orderDao.Set_ShipVia_forUPD = orderTable.Rows[0]["ShipVia"]; orderDao.Set_Freight_forUPD = orderTable.Rows[0]["Freight"]; orderDao.Set_ShipName_forUPD = orderTable.Rows[0]["ShipName"]; orderDao.Set_ShipAddress_forUPD = orderTable.Rows[0]["ShipAddress"]; orderDao.Set_ShipCity_forUPD = orderTable.Rows[0]["ShipCity"]; orderDao.Set_ShipRegion_forUPD = orderTable.Rows[0]["ShipRegion"]; orderDao.Set_ShipPostalCode_forUPD = orderTable.Rows[0]["ShipPostalCode"]; orderDao.Set_ShipCountry_forUPD = orderTable.Rows[0]["ShipCountry"]; // Update the order information to the database orderDao.D3_Update(); } foreach (System.Data.DataRow row in orderDetailsTable.Rows) { // Judge the state of DataRow, and update database if DataRow is modified if (row.RowState == System.Data.DataRowState.Modified) { // Set the parameters of the order details information orderDetailsDao.PK_OrderID = row["OrderId"]; orderDetailsDao.PK_ProductID = row["ProductId"]; orderDetailsDao.Set_UnitPrice_forUPD = row["UnitPrice"]; orderDetailsDao.Set_Quantity_forUPD = row["Quantity"]; orderDetailsDao.Set_Discount_forUPD = row["Discount"]; // Update the order details information to the database orderDetailsDao.D3_Update(); } } // Return result value return returnValue; }
-
Select
Codes\Businessfolder in the Solution Explorer. And click Project -> Add Class in toolbar.
-
Add class named
OrdersLogic.cs. -
Add the following namespaces to
OrdersLogic.csto import the classes provided by Open Touryo, and parameter and return value class, and data access class.// Open Touryo // Business using Touryo.Infrastructure.Business.Business; using Touryo.Infrastructure.Business.Common; using Touryo.Infrastructure.Business.Dao; using Touryo.Infrastructure.Business.Exceptions; using Touryo.Infrastructure.Business.Presentation; using Touryo.Infrastructure.Business.Util; // Framework using Touryo.Infrastructure.Framework.Business; using Touryo.Infrastructure.Framework.Common; using Touryo.Infrastructure.Framework.Dao; using Touryo.Infrastructure.Framework.Exceptions; using Touryo.Infrastructure.Framework.Presentation; using Touryo.Infrastructure.Framework.Util; using Touryo.Infrastructure.Framework.Transmission; using Touryo.Infrastructure.Framework.RichClient.Presentation; // Public using Touryo.Infrastructure.Public.Db; using Touryo.Infrastructure.Public.IO; using Touryo.Infrastructure.Public.Log; using Touryo.Infrastructure.Public.Str; using Touryo.Infrastructure.Public.Util; // Parameter and Return value class using SPA_Sample.Codes.Common; // Data access class using SPA_Sample.Codes.Dao;
- Inherit Business Parent Class of Open Touryo framework to
OrdersLogicclass as shown below.
public class OrdersLogic : MyFcBaseLogic
- Inherit Business Parent Class of Open Touryo framework to
-
Create
UOC_GetOrdersmethod to get the list of orders with the following code.private void UOC_GetOrders(OrderParameterValue orderParameter) { // Create an object DAO Summary class ConsolidatedLayerD facade = new ConsolidatedLayerD(this.GetDam()); // Get the list of orders information OrderReturnValue returnValue = facade.GetOrders(orderParameter); // Return the object of Return Value class this.ReturnValue = returnValue; }
Note:
The method to be called from the forms is required to be created asUOC_xx(xx is arbitrary string). When the forms send xx as parameter, Open Touryo framework allocates the processing toUOC_xxmethod in business logic class. -
Create
UOC_GetOrderByIdmethod to get the details of specific order summary and order details information based on the Order ID with the following code.private void UOC_GetOrderById(OrderParameterValue orderParameter) { // Create an object DAO Summary class ConsolidatedLayerD facade = new ConsolidatedLayerD(this.GetDam()); // Get the details of specific order information and order details information based on Order ID OrderReturnValue returnValue = facade.GetOrderById(orderParameter); // Return the object of Return Value class this.ReturnValue = returnValue; }
-
Create
UOC_UpdateOrdermethod to update the details of specified order summary and order details information with the following code.private void UOC_UpdateOrder(OrderParameterValue orderParameter) { // Create an object DAO Summary class ConsolidatedLayerD facade = new ConsolidatedLayerD(this.GetDam()); // Update the specified order information and order details information OrderReturnValue returnValue = facade.UpdateOrder(orderParameter); // Return the object of Return Value class this.ReturnValue = returnValue; }
This section describes how to create Controller that receives the request from View, ViewModel that defines the view display data and command, and View.
And, this tutorial uses Knockout, that is, JavaScript framework bundled in the Visual Studio SPA tempate by default. So, ViewModel and View should be implemented in the notation of Knockout framework. If you want to use a JavaScript framework except Knockout, like Angular, Angular 2, React, the notation in section 4.5.2 and 4.5.3 should be replaced with the notation of the framework. (The overall class configuration shown in Figure 1-1 is independent of the JavaScript framework.)
Perform the following steps to create POCO (Plain Old CLR Object) to store JSON data sent from ViewModel.
-
Define the JSON format transferred between View and Web API. Here, decide the name of JSON data and the data type, like
{ "name": "value" }. In this tutorial, define the JSON format as follows:-
Get order list
-
View -> Web API
{} (No transfer data) -
Web API -> View
[{"OrderID": "Order Id", "CustomerID": "Customer Id", "EmployeeID": "Emplayee Id", "OrderDate": "Order Date", "RequiredDate": "Required Date", "ShippedDate": "Shipped Date", "ShipVia": "Ship Via", "Freight": "Freight", "ShipName": "Ship Name", "ShipAddress": "Ship Address", "ShipCity": "Ship City", "ShipRegion": "Ship Region", "ShipPostalCode": "Ship Postal Code", "ShipCountry": "Ship Country", "ProductID": "Product Id", "UnitPrice": "Unit Price", "Quantity": "Quautity", "Discount": "Discount"}, …] (The order list)
-
-
Get order information by Order Id
-
View -> Web API
{"OrderID": "Order Id"} -
Web API -> View
{"OrderID": "Order Id", "CustomerID": "Customer Id", "EmployeeID": "Emplayee Id", "OrderDate": "Order Date", "RequiredDate": "Required Date", "ShippedDate": "Shipped Date", "ShipVia": "Ship Via", "Freight": "Freight", "ShipName": "Ship Name", "ShipAddress": "Ship Address", "ShipCity": "Ship City", "ShipRegion": "Ship Region", "ShipPostalCode": "Ship Postal Code", "ShipCountry": "Ship Country", "ProductID": "Product Id", "UnitPrice": "Unit Price", "Quantity": "Quautity", "Discount": "Discount"}
-
-
Update order information
-
View -> Web API
{"OrderID": "Order Id", "CustomerID": "Customer Id", "EmployeeID": "Emplayee Id", "OrderDate": "Order Date", "RequiredDate": "Required Date", "ShippedDate": "Shipped Date", "ShipVia": "Ship Via", "Freight": "Freight", "ShipName": "Ship Name", "ShipAddress": "Ship Address", "ShipCity": "Ship City", "ShipRegion": "Ship Region", "ShipPostalCode": "Ship Postal Code", "ShipCountry": "Ship Country", "ProductID": "Product Id", "UnitPrice": "Unit Price", "Quantity": "Quautity", "Discount": "Discount"} -
Web API -> View
{Updated Record Count}
-
-
-
Create an object to store JSON data defined in previous step. Here, if the name of property is reconciled with the name of JSON data, transferred JSON data is stored in this object automatically. Select
Modelsfolder in the Solution Explorer. And click Project -> Add Class in toolbar.
-
Add new class named
WebApiOrderParams.cs. -
Add the properties in
WebApiOrderParams.cs. (As mentioned above, reconcile the name of property with the name of JSON data.)/// <summary>Order ID</summary> public string OrderId { get; set; } /// <summary>Product ID</summary> public string ProductId { get; set; } /// <summary>Unit Price</summary> public string UnitPrice { get; set; } /// <summary>Quantity</summary> public string Quantity { get; set; } /// <summary>Discount</summary> public string Discount { get; set; } /// <summary>Customer ID</summary> public string CustomerID { get; set; } /// <summary>Employee ID</summary> public string EmployeeID { get; set; } /// <summary>Order Date</summary> public string OrderDate { get; set; } /// <summary>Required Date</summary> public string RequiredDate { get; set; } /// <summary>Shipped Date</summary> public string ShippedDate { get; set; } /// <summary>Ship Via</summary> public string ShipVia { get; set; } /// <summary>Freight</summary> public string Freight { get; set; } /// <summary>Ship Name</summary> public string ShipName { get; set; } /// <summary>Ship Address</summary> public string ShipAddress { get; set; } /// <summary>Ship City</summary> public string ShipCity { get; set; } /// <summary>Ship Region</summary> public string ShipRegion { get; set; } /// <summary>Ship Postal Code</summary> public string ShipPostalCode { get; set; } /// <summary>Ship Country</summary> public string ShipCountry { get; set; }
The following steps describe how to create the controller class to display order information view.
-
Right-click
Controllersfolder and click Add -> Controller option.
-
In the Add Controller dialog, set the properties as follows and click Add.
- Controller name: OrderController
- Template: Empty MVC controller
Next, create Web API controller class to receive the request from ViewModel and call business logic class.
-
Add the following namespace in OrderController.cs to use the classes provided by Open Touryo framework.
// Open Touryo framework // Business using Touryo.Infrastructure.Business.Business; using Touryo.Infrastructure.Business.Common; using Touryo.Infrastructure.Business.Dao; using Touryo.Infrastructure.Business.Exceptions; using Touryo.Infrastructure.Business.Presentation; using Touryo.Infrastructure.Business.Util; // Framework using Touryo.Infrastructure.Framework.Business; using Touryo.Infrastructure.Framework.Common; using Touryo.Infrastructure.Framework.Dao; using Touryo.Infrastructure.Framework.Exceptions; using Touryo.Infrastructure.Framework.Presentation; using Touryo.Infrastructure.Framework.Util; using Touryo.Infrastructure.Framework.Transmission; // Public using Touryo.Infrastructure.Public.Db; using Touryo.Infrastructure.Public.IO; using Touryo.Infrastructure.Public.Log; using Touryo.Infrastructure.Public.Str; using Touryo.Infrastructure.Public.Util; // Logic inside application using SPA_Sample.Codes.Business; using SPA_Sample.Codes.Common; using SPA_Sample.Models; // System using System.Net; using System.Net.Http; using System.Web.Http; using System.Data;
-
Create GetOrders Web API controller class in
SPA_Sample.Controllersnamespace, to get the list of orders information with the following code.public class GetOrdersController : ApiController { public HttpResponseMessage Post(WebApiOrderParams param) { // Create an object of Parameter Value class OrderParameterValue orderParameterValue = new OrderParameterValue("OrdersList", string.Empty, "GetOrders", "SQL", new MyUserInfo("user01", "192.168.1.1")); // Create objects of Return Value class and Business logic class OrderReturnValue orderReturnValue; OrdersLogic logic = new OrdersLogic(); // Get the list of orders information and store in the return value object orderReturnValue = (OrderReturnValue)logic.DoBusinessLogic(orderParameterValue); if (orderReturnValue.ErrorFlag == true) { // If the Return Value object has error, display the possible error message string message = string.Empty; message = "ErrorMessageID:" + orderReturnValue.ErrorMessageID + ";"; message += "ErrorMessage:" + orderReturnValue.ErrorMessage + ";"; message += "ErrorInfo:" + orderReturnValue.ErrorInfo; Dictionary<string, string> dic = new Dictionary<string, string>(); dic.Add("Error", message); return Request.CreateResponse(HttpStatusCode.OK, dic); } else { // Display the list of orders information DataTable dt = orderReturnValue.Orders; List<Dictionary<string, string>> list = new List<Dictionary<string, string>>(); foreach (DataRow row in dt.Rows) { Dictionary<string, string> dic = new Dictionary<string, string>(); for (int index = 0; index < dt.Columns.Count; index++) { dic.Add(dt.Columns[index].ColumnName, row[index].ToString()); } list.Add(dic); } return Request.CreateResponse(HttpStatusCode.OK, list); } } }
-
Create GetOrderById Web API controller class in SPA_Sample.Controllers namespace, to get the order summary and order details information with the following code.
public class GetOrderByIdController : ApiController { public HttpResponseMessage Post(WebApiOrderParams param) { // Create an object of Parameter Value class OrderParameterValue orderParameterValue = new OrderParameterValue("OrderInformation", string.Empty, "GetOrderById", "SQL" new MyUserInfo("user01", "192.168.1.1")); // Send Order ID as search condition orderParameterValue.OrderId = param.OrderId; // Create objects of Return Value class and Business logic class OrderReturnValue orderReturnValue; OrdersLogic logic = new OrdersLogic(); // Get the order summary and order details information and store in the return value object orderReturnValue = (OrderReturnValue)logic.DoBusinessLogic(orderParameterValue); if (orderReturnValue.ErrorFlag == true) { // If the Return Value object has error, display the possible error message string message = string.Empty; message = "ErrorMessageID:" + orderReturnValue.ErrorMessageID + ";"; message += "ErrorMessage:" + orderReturnValue.ErrorMessage + ";"; message += "ErrorInfo:" + orderReturnValue.ErrorInfo; Dictionary<string, string> dic = new Dictionary<string, string>(); dic.Add("Error", message); return Request.CreateResponse(HttpStatusCode.OK, dic); } else { // Display the order information (summary) and order information (details) DataTable dtOrders = orderReturnValue.Orders; List<Dictionary<string, string>>[] list = new List<Dictionary<string, string>>[2]; list[0] = new List<Dictionary<string, string>>(); list[1] = new List<Dictionary<string, string>>(); foreach (DataRow row in dtOrders.Rows) { Dictionary<string, string> dic = new Dictionary<string, string>(); for (int index = 0; index < dtOrders.Columns.Count; index++) { dic.Add(dtOrders.Columns[index].ColumnName, row[index].ToString()); } list[0].Add(dic); } DataTable dtOrderDetails = orderReturnValue.OrderDetails; foreach (DataRow row in dtOrderDetails.Rows) { Dictionary<string, string> dic = new Dictionary<string, string>(); for (int index = 0; index < dtOrderDetails.Columns.Count; index++) { dic.Add(dtOrderDetails.Columns[index].ColumnName, row[index].ToString()); } list[1].Add(dic); } return Request.CreateResponse(HttpStatusCode.OK, list); } } }
-
Create UpdateOrder Web API controller class in SPA_Sample.Controllers namespace, to update the order summary and order details information with the following code.
public class UpdateOrderController : ApiController { public HttpResponseMessage Post(WebApiOrderParams param) { // Create an object of Parameter Value class OrderParameterValue orderParameterValue = new OrderParameterValue("OrderInformation", string.Empty, "UpdateOrder", "SQL", new MyUserInfo("user01", "192.168.1.1")); // Set the parameters of order summary information orderParameterValue.OrderId = param.OrderId; orderParameterValue.CustomerID = param.CustomerID; orderParameterValue.EmployeeID = param.EmployeeID; orderParameterValue.OrderDate = param.OrderDate; orderParameterValue.RequiredDate = param.RequiredDate; orderParameterValue.ShippedDate = param.ShippedDate; orderParameterValue.ShipName = param.ShipName; orderParameterValue.ShipVia = param.ShipVia; orderParameterValue.ShipAddress = param.ShipAddress; orderParameterValue.ShipCountry = param.ShipCountry; orderParameterValue.ShipCity = param.ShipCity; orderParameterValue.ShipRegion = param.ShipRegion; orderParameterValue.ShipPostalCode = param.ShipPostalCode; orderParameterValue.Freight = param.Freight; // Set the parameters of order details information orderParameterValue.ProductId = param.ProductId; orderParameterValue.UnitPrice = param.UnitPrice; orderParameterValue.Quantity = param.Quantity; orderParameterValue.Discount = param.Discount; // Create objects of Return Value class and Business logic class OrderReturnValue orderReturnValue; OrdersLogic logic = new OrdersLogic(); // Update the order information (summary) and order information (details) orderReturnValue = (OrderReturnValue)logic.DoBusinessLogic(orderParameterValue); // Create an object of string to display message string message = string.Empty; Dictionary<string, string> dic = new Dictionary<string, string>(); if (orderReturnValue.ErrorFlag == true) { // If the Return Value object has error, display the possible error message message = "ErrorMessageID:" + orderReturnValue.ErrorMessageID + ";"; message += "ErrorMessage:" + orderReturnValue.ErrorMessage + ";"; message += "ErrorInfo:" + orderReturnValue.ErrorInfo; dic.Add("Error", message); return Request.CreateResponse(HttpStatusCode.OK, dic); } else { // Display the success message message = "Successfully updated order summary and order details information."; dic.Add("Message", message); return Request.CreateResponse(HttpStatusCode.OK, dic); } } }
Perform the following steps to create ViewModel that defines properties and commands for binding to View.
-
Select
Scripts\appfolder in the Solution Explorer. And click Project -> Add New Item in toolbar.
-
Add new JavaScript file named
Order.viewmodel.js. -
Create OrderViewModel method in
Order.viewmodel.jsto define properties and commands for binding to View with the following code.function OrderViewModel() { // Orders list, order summary and order details information (JSON format) this.dataOrders = ko.observableArray(); this.dataOrder = ko.observableArray(); this.dataOrderDetails = ko.observableArray(); // Success message this.Result = ko.observable(""); // Error message this.ErrorMessage = ko.observable(""); // Handles visibility Orders list, order summary and order details information this.CanDisplayOrders = ko.observable(true); this.CanDisplayOrderInfo = ko.observable(false); this.CanEditOrderInfo = ko.observable(false); this.DisplayOrders = function () { var self = this; self.CanDisplayOrders(true); self.CanDisplayOrderInfo(false); self.CanEditOrderInfo(false); }; this.DisplayOrderInfo = function () { var self = this; self.CanDisplayOrders(false); self.CanDisplayOrderInfo(true); self.CanEditOrderInfo(false); }; this.EditOrderInfo = function () { var self = this; self.CanDisplayOrders(false); self.CanDisplayOrderInfo(false); self.CanEditOrderInfo(true); }; // Gets the list of Orders this.GetOrders = function () { var self = this; // Reset the error message this.ErrorMessage(""); // Set the parameters in JSON format var param = {} // Ajax request $.ajax({ type: 'POST', url: '/api/GetOrders', data: param, dataType: 'json', success: function (data, dataType) { if (data.error) { // Set the error message self.ErrorMessage(data.error); } else { // Reset the Orders information self.ClearList(); // Enable visibility of Orders self.DisplayOrders(); // Set the list of Orders self.dataOrders(data); } }, error: function (XMLHttpRequest, textStatus, errorThrown) { // Set the error message self.ErrorMessage(XMLHttpRequest.responseText); } }); }; // Get the order summary and order details information this.GetOrderById = function (orderID) { var self = this; // Reset the error message this.ErrorMessage(""); // Set the parameters in JSON format var param = { OrderId: orderID } // Ajax request $.ajax({ type: 'POST', url: '/api/GetOrderById', data: param, dataType: 'json', success: function (data, dataType) { if (data.error) { // Set the error message self.ErrorMessage(data.error); } else { // Reset the orders information self.ClearList(); // Enable visibility of order information self.DisplayOrderInfo(); // Set the order information self.dataOrder(data[0]); self.dataOrderDetails(data[1]); } }, error: function (XMLHttpRequest, textStatus, errorThrown) { // Set the error message self.ErrorMessage(XMLHttpRequest.responseText); } }); }; // Enable the order summary and order details information to edit this.EditOrder = function (data) { var self = this; this.Result(""); self.EditOrderInfo(); self.dataOrderDetails(data); }; // Updates the selected order summary and order details information this.UpdateOrder = function () { var self = this; // Reset the error message this.ErrorMessage(""); // Set the parameters in JSON format var param = { OrderID: self.dataOrder()[0].orderID, CustomerID: self.dataOrder()[0].customerID, EmployeeID: self.dataOrder()[0].employeeID, OrderDate: self.dataOrder()[0].orderDate, RequiredDate: self.dataOrder()[0].requiredDate, ShippedDate: self.dataOrder()[0].shippedDate, ShipVia: self.dataOrder()[0].shipVia, Freight: self.dataOrder()[0].freight, ShipName: self.dataOrder()[0].shipName, ShipAddress: self.dataOrder()[0].shipAddress, ShipCity: self.dataOrder()[0].shipCity, ShipRegion: self.dataOrder()[0].shipRegion, ShipPostalCode: self.dataOrder()[0].shipPostalCode, ShipCountry: self.dataOrder()[0].shipCountry, ProductID: self.dataOrderDetails().productID, UnitPrice: self.dataOrderDetails().unitPrice, Quantity: self.dataOrderDetails().quantity, Discount: self.dataOrderDetails().discount } // Ajax request $.ajax({ type: 'POST', url: '/api/UpdateOrder', data: param, dataType: 'json', success: function (data, dataType) { if (data.error) { // Set the error message self.ErrorMessage(data.error); } else { // Set the success message self.Result(data.message); } }, error: function (XMLHttpRequest, textStatus, errorThrown) { // Set the error message self.ErrorMessage(XMLHttpRequest.responseText); } }); }; // Resets the orders list, order summary and order details information this.ClearList = function () { this.dataOrders([]); this.dataOrder([]); this.dataOrderDetails([]); }; } // Creates Order ViewModel var model = new OrderViewModel(); // Creates dialog to display error message model.ErrorMessage.subscribe(function (newValue) { if (newValue != '') { $('<div>' + newValue + '</div>').dialog({ title: 'Error!', modal: true, resizable: false, height: 600, width: 800, buttons: { 'OK': function (event) { $(this).dialog('close'); } } }); } }); // Activates knockout.js ko.applyBindings(model);
-
Open
App_Start\BundleConfig.csfile with Visual Studio and add the reference ofOrder.viewmodel.jsin RegisterBundles method with the following code.bundles.Add(new ScriptBundle("~/bundles/Order").Include("~/Scripts/app/Order.viewmodel.js"));
-
In code view of Visual Studio, right-click somewhere of Index method of
OrderControllerclass and select Add View.
-
In the Add View dialog, set the properties as follows and click Add.
- View name: Index
- View engine: Razor (CSHTML)
- Create a strongly-typed view: Uncheck the box
- Create as a partial view: Uncheck the box
- Use a layout or master page: Check the box
-
Add the following code in
Index.cshtmlto display list of orders, order summary and order details information and to update order summary and order details information.<!--Title and header information--> @{ ViewBag.Title = "Open Touryo SPA Sample"; Layout = "~/Views/Shared/_Layout.cshtml"; } <div style="width:100%;padding:0.5em 0em;background:#f0f0f0;"> <b style="margin-left:0.4em;font-size:1.5em;">Open Touryo SPA Sample</b> </div> <!--Displays List of Orders--> <div style="width:100%;height:460px;margin-top:0.5em;overflow:scroll;display:none;" data-bind="visible: GetOrders(), style: { display: $root.CanDisplayOrders() === true ? 'block' : 'none' }"> <b style="margin-left:1em;">List of Orders:</b> <table style="width:100%;margin-top:0.5em;font-size:medium;"> <thead> <tr> <th style="width:8%;">Order ID</th> <th style="width:18%">Company Name</th> <th style="width:18%">Contact Name</th> <th style="width:18%">Employee Last Name</th> <th style="width:18%">Employee First Name</th> <th style="width:15%">Order Date</th> </tr> </thead> <tbody data-bind="foreach: dataOrders"> <tr> <td> <a data-bind="text: orderID, click: function(){ $root.GetOrderById($data.orderID) }" href="javascript:void(0)" /> </td> <td data-bind="text: companyName"></td> <td data-bind="text: contactName"></td> <td data-bind="text: employeeLastName"></td> <td data-bind="text: employeeFirstName"></td> <td data-bind="text: orderDate"></td> </tr> </tbody> </table> </div> <!--Displays the order summary and order details information--> <div style="width:100%;display:none;" data-bind="style: { display: CanDisplayOrderInfo() === true ? 'block' : 'none' }"> <!--Displays the Order information (Summary)--> <div style="width: 100%;margin-top:0.5em;"> <b style="margin-left:1em;">Order information (Summary):</b> <table style="width:100%;margin-top:0.5em;font-size:small;"> <thead> <tr> <th>Order ID</th> <th>Customer ID</th> <th>Employee ID</th> <th>Order Date</th> <th>Required Date</th> <th>Shipped Date</th> <th>Ship Via</th> <th>Freight</th> <th>Ship Name</th> <th>Ship Address</th> <th>ShipCity</th> <th>Ship Region</th> <th>Ship PostalCode</th> <th>Ship Country</th> </tr> </thead> <tbody data-bind="foreach: dataOrder"> <tr> <td data-bind="text: orderID"></td> <td data-bind="text: customerID"></td> <td data-bind="text: employeeID"></td> <td data-bind="text: orderDate"></td> <td data-bind="text: requiredDate"></td> <td data-bind="text: shippedDate"></td> <td data-bind="text: shipVia"></td> <td data-bind="text: freight"></td> <td data-bind="text: shipName"></td> <td data-bind="text: shipAddress"></td> <td data-bind="text: shipCity"></td> <td data-bind="text: shipRegion"></td> <td data-bind="text: shipPostalCode"></td> <td data-bind="text: shipCountry"></td> </tr> </tbody> </table> </div> <!--Displays the Order information (Details)--> <div style="width:100%; margin-top:0.5em;"> <b style="margin-left:1em;">Order information (Details):</b> <table style="width:100%;margin-top:0.5em; font-size:small;"> <thead> <tr> <th></th> <th>Order ID</th> <th>Product ID</th> <th>Unit Price</th> <th>Quantity</th> <th>Discount</th> </tr> </thead> <tbody data-bind="foreach: dataOrderDetails"> <tr> <td> <a data-bind="click: function () { $root.EditOrder($data) }" href="javascript:void(0)">Edit</a> </td> <td data-bind="text: orderID"></td> <td data-bind="text: productID"></td> <td data-bind="text: unitPrice"></td> <td data-bind="text: quantity"></td> <td data-bind="text: discount"></td> </tr> </tbody> </table> </div> <!--Action controls--> <div style="width:100%;margin:0.5em 0;"> <div style="float:right;"> <input type="button" id="button1" value="<< Back" data-bind="click: function () { $root.GetOrders() }" /> </div> </div> </div> <!--Displays the order summary and order details information to update--> <div style="width:100%;display:none;" data-bind="style: { display: CanEditOrderInfo() === true ? 'block' : 'none' }"> <!--Update the Order information (Summary)--> <fieldset style="border:1px solid #ddd;width:96%;padding-bottom:1em;"> <legend style="font-weight:bold;display:block;margin-left:3px;"> Update Order information (Summary) </legend> <table style="width:100%;font-size:small;" data-bind="foreach: dataOrder"> <tr> <td style="width:16%;"><b>Order ID</b></td> <td style="width:16%;"> <label style="padding:0.3em 0;background:#f0f0f0;" data-bind="text: orderID" /> </td> <td style="width:16%;"><b>Customer ID</b></td> <td style="width:16%"> <input type="text" data-bind="value: customerID" /> </td> <td style="width:16%;"><b>Employee ID</b></td> <td style="width:16%;"> <input type="text" data-bind="value: employeeID" /> </td> </tr> <tr> <td><b>Order Date</b></td> <td> <input type="text" data-bind="value: orderDate" /> </td> <td><b>Required Date</b></td> <td> <input type="text" data-bind="value: requiredDate" /> </td> <td><b>Shipped Date</b></td> <td> <input type="text" data-bind="value: shippedDate" /> </td> </tr> <tr> <td><b>Ship Via</b></td> <td> <input type="text" data-bind="value: shipVia" /> </td> <td><b>Freight</b></td> <td> <input type="text" data-bind="value: freight" /> </td> <td><b>Ship Name</b></td> <td> <input type="text" data-bind="value: shipName" /> </td> </tr> <tr> <td><b>Ship Address</b></td> <td> <input type="text" data-bind="value: shipAddress" /> </td> <td><b>Ship City</b></td> <td> <input type="text" data-bind="value: shipCity" /> </td> <td><b>Ship Region</b></td> <td> <input type="text" data-bind="value: shipRegion" /> </td> </tr> <tr> <td><b>Ship Postal Code</b></td> <td> <input type="text" data-bind="value: shipPostalCode" /> </td> <td><b>Ship Country</b></td> <td> <input type="text" data-bind="value: shipCountry" /> </td> </tr> </table> </fieldset> <!--Update the Order information (Details)--> <fieldset style="border:1px solid #ddd;width:96%;padding-bottom:1em;"> <legend style="font-weight:bold;display:block;margin-left:3px;"> Update Order information (Details) </legend> <table style="width:100%;font-size:small;" data-bind="foreach: dataOrderDetails"> <tr> <td style="width:16%;"><b>Order ID</b></td> <td style="width:16%;"> <label style="padding:0.3em 0;background:#f0f0f0;" data-bind="text: orderID" /> </td> <td style="width:16%;"><b>Product ID</b></td> <td style="width:16%;"> <label style="padding:0.3em 0;background:#f0f0f0;" data-bind="text: productID" /> </td> <td style="width:16%;"><b>Unit Price</b></td> <td style="width:16%;"> <input type="text" data-bind="value: unitPrice" /> </td> </tr> <tr> <td><b>Quantity</b></td> <td> <input type="text" data-bind="value: quantity" /> </td> <td><b>Discount</b></td> <td> <input type="text" data-bind="value: discount" /> </td> </tr> </table> </fieldset> <!--Action controls--> <div style="width:100%;margin:0.5em 0px;"> <div style="float:left;"> <input type="button" id="button2" value="Update Order Information" data-bind="click: UpdateOrder" /> </div> <div style="float:right;" data-bind="foreach: dataOrder"> <input type="button" id="button3" value="<< Back" data-bind="click: function () {$root.GetOrderById($data.orderID)}" /> </div> </div> <div style="width:100%;margin:0.5em 0;display:inline-block;"> <label id="labelMessage" style="color:#088608;font-weight:bold;" data-bind="text: Result" /> </div> </div> @section scripts{ @Scripts.Render("~/bundles/knockout") @Scripts.Render("~/bundles/jqueryval") @Scripts.Render("~/bundles/Order") }
-
Run
services.mscin the Search programs and files box on the start menu.
-
In Services window, right-click ASP.NET state service and select Start.
-
Confirm that the status of ASP.NET state services has been changed to Started.
-
In Solution Explorer, right-click
SPA_Sampleproject and click Properties. -
In
SPA_Sampleproperty window, select Web and set start action as follows:- Start Action: Spacific Page
- Page: Order
-
Debug the sample application with Visual Studio.
-
Confirm that the List of orders is displayed.
-
Click the particular Order ID in the List of Orders and confirm that the Order Summary and Order Details information are displayed for selected Order ID.
-
Click the Edit link to update the Order Summary and particular Order Details information.
*Note: *
If the value of Ship Region is null, Open Touryo error occurs during the update record. So, select the order information that the value of Ship Region is not null. To enable update record in any cases, modify sql file generated in section 4.3.1. For details, refer Open Touryo user guide (Dynamic Parameterized Query Analytical Tool version). -
Confirm that the update screen of Order Summary and Order Details information are displayed. Edit the fields of Order Summary and Order Details information and click Update Order Information.
-
Confirm that the Order Summary and Order Details information are updated with the succes message.
-
Open log file -
C:\root\files\resource\Log\ACCESS.yyyy-mm-dd.log(where, yyyy-mm-dd is executed date) in Notepad. -
Confirm that the access to business logic class is logged.
[2016/08/22 00:11:07,450],[INFO ],[9],,user01,192.168.1.1,----->>,OrderDetails,,GetOrders, [2016/08/22 00:11:08,350],[INFO ],[9],,user01,192.168.1.1,<<-----,OrderDetails,,GetOrders,,890,94 [2016/08/22 00:11:18,092],[INFO ],[8],,user01,192.168.1.1,----->>,OrderDetails,,GetOrderById, [2016/08/22 00:11:18,574],[INFO ],[8],,user01,192.168.1.1,<<-----,OrderDetails,,GetOrderById,,481,47 [2016/08/22 00:11:24,337],[INFO ],[7],,user01,192.168.1.1,----->>,OrderDetails,,UpdateOrder, [2016/08/22 00:11:24,700],[INFO ],[7],,user01,192.168.1.1,<<-----,OrderDetails,,UpdateOrder,,362,16
-
Open log file -
C:\root\files\resource\Log\SQLTRACE.yyyy-mm-dd.log(where, yyyy-mm-dd is executed date) in Notepad. -
Confirm the executed SQL text for Orders and OrderDetails table with the following SQL trace log.
[2016/08/22 00:11:08,349],[INFO ],[9],238,31,[commandText]:SELECT Orders.OrderID, Customers.CompanyName, Customers.ContactName, Employees.LastName As EmployeeLastName, Employees.FirstName As EmployeeFirstName, Orders.OrderDate FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID [commandParameter]: [2016/08/22 00:11:18,373],[INFO ],[8],114,16,[commandText]: -- DaoOrders_D2_Select -- 2016/8/3 日立 太郎 SELECT [OrderID], [CustomerID], [EmployeeID], [OrderDate], [RequiredDate], [ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode], [ShipCountry] FROM [Orders] WHERE [OrderID] = @OrderID [commandParameter]:OrderID=10250, [2016/08/22 00:11:18,573],[INFO ],[8],71,0,[commandText]: -- DaoOrder_Details_D2_Select -- 2016/8/3 日立 太郎 SELECT [OrderID], [ProductID], [UnitPrice], [Quantity], [Discount] FROM [Order Details] WHERE [OrderID] = @OrderID [commandParameter]:OrderID=10250, [2016/08/22 00:11:24,530],[INFO ],[7],122,0,[commandText]: -- DaoOrders_D3_Update -- 2016/8/3 日立 太郎 UPDATE [Orders] SET [CustomerID] = @Set_CustomerID_forUPD, [EmployeeID] = @Set_EmployeeID_forUPD, [OrderDate] = @Set_OrderDate_forUPD, [RequiredDate] = @Set_RequiredDate_forUPD, [ShippedDate] = @Set_ShippedDate_forUPD, [ShipVia] = @Set_ShipVia_forUPD, [Freight] = @Set_Freight_forUPD, [ShipName] = @Set_ShipName_forUPD, [ShipAddress] = @Set_ShipAddress_forUPD, [ShipCity] = @Set_ShipCity_forUPD, [ShipRegion] = @Set_ShipRegion_forUPD, [ShipPostalCode] = @Set_ShipPostalCode_forUPD, [ShipCountry] = @Set_ShipCountry_forUPD WHERE [OrderID] = @OrderID [commandParameter]:Set_ShipCity_forUPD=Rio de Janeiro,Set_CustomerID_forUPD=HANAR,OrderID=10250,Set_EmployeeID_forUPD=4,Set_ShippedDate_forUPD=7/12/1996 12:00:00 AM,Set_RequiredDate_forUPD=8/5/1996 12:00:00 AM,Set_ShipName_forUPD=Hanari Carnes,Set_ShipPostalCode_forUPD=05454-876,Set_ShipVia_forUPD=2,Set_ShipAddress_forUPD=Rua do Paco, 67,Set_ShipRegion_forUPD=RJ,Set_OrderDate_forUPD=7/8/1996 12:00:00 AM,Set_ShipCountry_forUPD=Brazil,Set_Freight_forUPD=65.8300, [2016/08/22 00:11:24,699],[INFO ],[7],39,0,[commandText]: -- DaoOrder_Details_D3_Update -- 2016/8/3 日立 太郎 UPDATE [Order Details] SET [UnitPrice] = @Set_UnitPrice_forUPD, [Quantity] = @Set_Quantity_forUPD, [Discount] = @Set_Discount_forUPD WHERE [OrderID] = @OrderID AND [ProductID] = @ProductID [commandParameter]:OrderID=10250,Set_Quantity_forUPD=35,Set_Discount_forUPD=0.15,Set_UnitPrice_forUPD=42.4000,ProductID=51,
-