PB to C#: Migrating Re-factored Business Logics (NVOs)
Last Updated: July 2021
Introduction
You may want to implement the business logic in a PowerBuilder application using Web API services, so that various clients can reuse the business logic through consuming these services.
A normal migration process has these steps:
Step 1: Define your Web API migration objective.
Define the scope of business logics that you want to migrate from your PowerBuilder application to C#.
Step 2: Prepare the target application.
You must well-refactor the business logic and encapsulate the business logic into corresponding NVOs. Note that UI logic cannot be migrated, so it shall not be encapsulated in NVOs.
Step 3: Create the Web API and its components.
This is a standard web application creation step.
Step 4: Migrate the refactored PowerBuilder business logic.
You will migrate DataWindows to .NET DataStores, port PowerScript, and also port embedded SQLs.
Step 5: Call the Web API from a client application.
The last step is to enable a client application to call the migrated the business logic.
This tutorial will teach you, through a demo application that has already been refactored, the strategies and detailed steps (i.e. the aforementioned step 3 to 5) of migrating refactored PowerBuilder business logics to C# .NET Core Web APIs and enabling the PowerBuilder application to call the Web APIs.
The step 1 to 2 are very important steps but are not discussed in this tutorial: They require an in-depth understanding of the whole business logics of the original application, and would vary greatly depending on the scope and strategy you set up. If you need to gain an idea on how to start with the least refactoring hassle, you may refer to the other tutorial: PB to C#: Porting Business Logics with Miminum Refactoring Hassle.
Prerequisites
- PowerBuilder 2021 and SnapDevelop 2021
- Sample database setup
- Download the database backup file from here.
- Install SQL Server Express or SQL Server if it is not installed.
- Restore database using the downloaded database backup file.
- PB OrderDemo application downloadable from here. (Note that this demo app has already been refactored to use Web APIs).
Create a Web API project
Create a Web API project
Start SnapDevelop and select Create New Project from the Start page. Or, from the File menu, select New and then Project....
In the New Project dialog box, select .NET Core, and in the list of project templates, select ASP.NET Core Web API. Name the project "OrderDemo", name the solution "Appeon.OrderDemo" and click OK.
Test the Web API
The project template creates a sample Web API. Call the GET HTTP verb from a browser to test the app.
Press Ctrl+F5 to run the app. SnapDevelop launches a browser and navigates to http://localhost:5000/api/sample/load.
If it's the first time the Web API runs, you may need to wait several seconds for initiating .NET runtime after the browser is launched.
The following JSON is returned:
["value1","value2"]
Add a database context
The database context is the class that manages database connections and transactions. This class is created by deriving from the SnapObjects.Data.DataContext class.
Right-click the OrderDemo project and select Add > New Item.... In the item template list select DataContext and name the class AppeonDataContext.cs and click OK.
In the Database Connection dialog box, click New.
In the dialog box that opens, fill in the database connection information and click OK.
Then go back to the Database Connection dialog box, and notice that the Connection String shows in the dialog box, click OK.
The Connection String is saved in appsettings.json and the AppeonDataContext class is created.
Register the database context
In ASP.NET Core, services such as the DataContext must be registered with the dependency injection (DI) container. The container provides the service to controllers.
Update Startup.cs according to the comments:
// Add the following using statements
using SnapObjects.Data;
using SnapObjects.Data.SqlServer;
and modify the ConfigureServices() method in Startup.cs according to the comments:
// Uncomment the following line to connect to the SQL server database.
// Note: Replace "ContextName" with the configured context name; replace "key" with the database connection name that exists in appsettings.json. The sample code is as follows:
services.AddDataContext<AppeonDataContext>(m => m.UseSqlServer(this.Configuration, "AppeonSample"));
Add a Service
In this section, you create three Service classes (corresponding to the three NVOs), and you only initialize these Service classes with interface and initial code. In the next section Migrate the PowerBuilder business logic, you will update the implementation of these Service classes with the corresponding code from the ported business logic.
Why adding the Service class
A service is a component that's intended for common consumption in an app and commonly implements an interface. This is where you develop the business logic of your Web API.
The interface contains only the declaration of the methods, properties and events, but not the implementation.
To add the Interface class and the Service class, you
Right-click on the Web API Project. Then click Add > New Folder. Name it Services. Now add a sub-folder to it and name it Impl. Next, right-click on the Services folder and click on Add > Interface.
Name it ICustomerService.cs and click OK.
Then add the IOrderService.cs and IProductService.cs interfaces in the same way.
Now the ICustomerService, IOrderService, and IProductService interfaces are created accordingly.
In the ICustomerService interface, add the declaration of the methods that will be implemented on the service. (Notice how the reserved word public has been added to the declaration of the Interface)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using SnapObjects.Data;
using DWNet.Data;
namespace OrderDemo.Services
{
public interface ICustomerService
{
IDataStore RetrieveCustomerSelect();
IDataStore RetrieveCustList();
int CheckCustNo(string custNo);
}
}
Add the declaration of the following methods to the IOrderService interface:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using SnapObjects.Data;
using DWNet.Data;
namespace OrderDemo.Services
{
public interface IOrderService
{
IDataStore RetrieveOrderList(string custNo);
IDataStore RetrieveOrderModify(string orderNo);
IDataStore RetrieveOrderItemEdit(string custNo, string orderNo);
IDataStore RetrieveOrderItemList(string custNo, string orderNo);
bool Add(IDataStore order, IDataStore orderItems);
bool Modify(IDataStore order, IDataStore orderItems);
void Delete(string orderNo);
short GetLineIdMax(string orderNo);
string GetOrderNo(string custNo);
}
}
Add the declaration of the following methods to the IProductService interface. Currently OrderDemo.Models namespace and the D_Product class do not exist. The D_Product class will be generated in the next step.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using SnapObjects.Data;
using DWNet.Data;
using OrderDemo.Models;
namespace OrderDemo.Services
{
public interface IProductService
{
IDataStore RetrieveDddwProducts();
IDataStore RetrieveDddwCategory();
D_Product SelectBySku(string sku);
D_Product SelectByName(string name);
}
}
After adding the interface, you add the service by right-clicking on the Service > Impl sub-folder and clicking on Add > Class. Name it CustomerService.cs and click OK. (Notice the difference between the name of the interface "ICustomerService.cs" and the name of the service "CustomerService.cs").
Then add OrderService.cs and ProductService.cs in the same way.
Now the CustomerService, OrderService, and ProductService classes are created accordingly.
Add the initial code to the CustomerService class:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using SnapObjects.Data;
using DWNet.Data;
namespace OrderDemo.Services.Impl
{
public class CustomerService : ICustomerService
{
/// <summary>
/// The DataContext local variable. (Similar to PB's SQLCA)
/// </summary>
private readonly AppeonDataContext _context;
/// <summary>
/// Initializes a new instance of the CustomerService class.
/// </summary>
/// <param name="Context">Context.</param>
public CustomerService(AppeonDataContext Context)
{
// Initialize the DataContext variable
_context = Context;
}
public IDataStore RetrieveCustomerSelect()
{
// To be updated with the code from ported business logic
throw new NotImplementedException();
}
public IDataStore RetrieveCustList()
{
// To be updated with the code from ported business logic
throw new NotImplementedException();
}
public int CheckCustNo(string custNo)
{
// To be updated with the code from ported business logic
throw new NotImplementedException();
}
}
}
Add the initial code to the OrderService class:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using SnapObjects.Data;
using DWNet.Data;
namespace OrderDemo.Services.Impl
{
public class OrderService : IOrderService
{
/// <summary>
/// The DataContext local variable. (Similar to PB's SQLCA)
/// </summary>
private readonly AppeonDataContext _context;
/// <summary>
/// Initializes a new instance of the OrderService class.
/// </summary>
/// <param name="Context">Context.</param>
public OrderService(AppeonDataContext Context)
{
// Initialize the DataContext variable
_context = Context;
}
public IDataStore RetrieveOrderList(string custNo)
{
// To be updated with the code from ported business logic
throw new NotImplementedException();
}
public IDataStore RetrieveOrderModify(string orderNo)
{
// To be updated with the code from ported business logic
throw new NotImplementedException();
}
public IDataStore RetrieveOrderItemEdit(string custNo, string orderNo)
{
// To be updated with the code from ported business logic
throw new NotImplementedException();
}
public IDataStore RetrieveOrderItemList(string custNo, string orderNo)
{
// To be updated with the code from ported business logic
throw new NotImplementedException();
}
public bool Add(IDataStore order, IDataStore orderItems)
{
// To be updated with the code from ported business logic
throw new NotImplementedException();
}
public bool Modify(IDataStore order, IDataStore orderItems)
{
// To be updated with the code from ported business logic
throw new NotImplementedException();
}
public void Delete(string orderNo)
{
// To be updated with the code from ported business logic
throw new NotImplementedException();
}
public short GetLineIdMax(string orderNo)
{
// To be updated with the code from ported business logic
throw new NotImplementedException();
}
public string GetOrderNo(string custNo)
{
// To be updated with the code from ported business logic
throw new NotImplementedException();
}
}
}
Add the initial code to the ProductService class:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using SnapObjects.Data;
using DWNet.Data;
using OrderDemo.Models;
namespace OrderDemo.Services.Impl
{
public class ProductService : IProductService
{
/// <summary>
/// The DataContext local variable. (Similar to PB's SQLCA)
/// </summary>
private readonly AppeonDataContext _context;
/// <summary>
/// Initializes a new instance of the ProductService class.
/// </summary>
/// <param name="Context">Context.</param>
public ProductService(AppeonDataContext Context)
{
// Initialize the DataContext variable
_context = Context;
}
public IDataStore RetrieveDddwProducts()
{
// To be updated with the code from ported business logic
throw new NotImplementedException();
}
public IDataStore RetrieveDddwCategory()
{
// To be updated with the code from ported business logic
throw new NotImplementedException();
}
public D_Product SelectBySku(string sku)
{
// To be updated with the code from ported business logic
throw new NotImplementedException();
}
public D_Product SelectByName(string name)
{
// To be updated with the code from ported business logic
throw new NotImplementedException();
}
}
}
In ASP.NET Core, services such as the CustomerService must be registered with the dependency injection (DI) container. The container provides the service to controllers.
Update Startup.cs. Add the following using statements:
// Add the following using statements
using OrderDemo.Services;
using OrderDemo.Services.Impl;
and modify according to the comments:
// The service needs to be registered in the ConfigureServices method of the Startup class. Sample code as follows:
// services.AddScoped<ServiceInterfaceName, ServiceClassName>();
services.AddScoped<ICustomerService, CustomerService>();
services.AddScoped<IOrderService, OrderService>();
services.AddScoped<IProductService, ProductService>();
Add a Controller
In this section, you create three Controller classes (corresponding to the three Service classes and NVOs), and you only initialize these Controller classes with initial code. In the next section Migrate the PowerBuilder business logic, you will update the implementation of these Controller classes with the corresponding code from the ported business logic.
Why adding the Controller class
Since the .NET DataStore and PowerBuilder's DataStore are essentially the same, you perform CRUD operations virtually the same in the Web API.
The Web API requires one or more components called Controller. A Controller determines what response to send back to a user when a client makes an HTTP request.
A default Controller was created when you created the Web API project; but you can also create a new one by right-clicking on the Controllers folder, then clicking on Add > New Item > API Controller Class.
Add three new controllers and name them CustomerController, OrderController, and ProductController accordingly.
Add the initial code to the CustomerController class:
using System;
using System.Text;
using System.Collections;
using System.Collections.Generic;
using Microsoft.AspNetCore.Mvc;
using SnapObjects.Data;
using DWNet.Data;
using OrderDemo.Services;
using System.Net;
using Microsoft.AspNetCore.Http;
namespace OrderDemo.Controllers
{
[Route("api/[controller]/[action]")]
[ApiController]
public class CustomerController : ControllerBase
{
// Declare the local variable for the Service.
private readonly ICustomerService _service;
// Constructor.
public CustomerController(ICustomerService service)
{
// Initialize the Service variable
_service = service;
}
/// Update this method with the corresponding code to perform the action
[HttpGet]
public ActionResult<IDataStore> RetrieveCustomerSelect()
{
return null;
}
/// Update this method with the corresponding code to perform the action
[HttpGet]
public ActionResult<IDataStore> RetrieveCustList()
{
return null;
}
/// Update this method with the corresponding code to perform the action
[HttpGet]
public ActionResult<int> CheckCustNo(string custNo)
{
return 1;
}
}
}
Add the initial code to the OrderController class:
using System;
using System.Text;
using System.Collections;
using System.Collections.Generic;
using Microsoft.AspNetCore.Mvc;
using SnapObjects.Data;
using DWNet.Data;
using System.Net;
using Microsoft.AspNetCore.Http;
using OrderDemo.Services;
namespace OrderDemo.Controllers
{
[Route("api/[controller]/[action]")]
[ApiController]
public class OrderController : ControllerBase
{
// Declare the local variable for the Service.
private readonly IOrderService _service;
// The DataContext local variable. (Similar to PB's SQLCA)
private readonly AppeonDataContext _context;
// Constructor.
public OrderController(IOrderService service, AppeonDataContext context)
{
// Initialize the Service variable
_service = service;
// Initialize the DataContext variable
_context = context;
}
/// Update this method with the corresponding code to perform the action
[HttpGet]
public ActionResult<IDataStore> RetrieveOrderList(string custNo)
{
return null;
}
/// Update this method with the corresponding code to perform the action
[HttpGet]
public ActionResult<IDataStore> RetrieveOrderItemList(string custNo, string orderNo)
{
return null;
}
/// Update this method with the corresponding code to perform the action
[HttpGet]
public ActionResult<IDataStore> RetrieveOrderModify(string custNo)
{
return null;
}
/// Update this method with the corresponding code to perform the action
[HttpGet]
public ActionResult<IDataStore> RetrieveOrderItemEdit(string custNo, string orderNo)
{
return null;
}
/// Update this method with the corresponding code to perform the action
[HttpPost]
public ActionResult Add(IDataUnpacker dataUnpacker)
{
return Ok();
}
/// Update this method with the corresponding code to perform the action
[HttpPost]
public ActionResult Modify(IDataUnpacker dataUnpacker)
{
return Ok();
}
/// Update this method with the corresponding code to perform the action
[HttpDelete]
public ActionResult Delete(string orderNo)
{
return Ok();
}
/// Update this method with the corresponding code to perform the action
[HttpGet]
public ActionResult<short> GetLineIdMax(string orderNo)
{
return 1;
}
/// Update this method with the corresponding code to perform the action
[HttpGet]
public ActionResult<string> GetOrderNo(string custNo)
{
return null;
}
}
}
Add the initial code to the ProductController class:
using System;
using System.Text;
using System.Collections;
using System.Collections.Generic;
using Microsoft.AspNetCore.Mvc;
using SnapObjects.Data;
using DWNet.Data;
using OrderDemo.Services;
using System.Net;
using Microsoft.AspNetCore.Http;
using OrderDemo.Models;
namespace OrderDemo.Controllers
{
[Route("api/[controller]/[action]")]
[ApiController]
public class ProductController : ControllerBase
{
// Declare the local variable for the Service.
private readonly IProductService _service;
// Constructor.
public ProductController(IProductService service)
{
// Initializes the Service variable
_service = service;
}
// Replace this method with the corresponding code to perform the action
[HttpGet]
public ActionResult<string> RetrieveDddwCategory()
{
return Ok();
}
// Replace this method with the corresponding code to perform the action
[HttpGet]
public ActionResult<string> RetrieveDddwProducts()
{
return Ok();
}
// Replace this method with the corresponding code to perform the action
[HttpGet]
public ActionResult<D_Product> SelectBySku(string sku)
{
return Ok();
}
// Replace this method with the corresponding code to perform the action
[HttpGet]
public ActionResult<D_Product> SelectByName(string name)
{
return Ok();
}
}
}
Migrate the PowerBuilder business logic
In the previous sections, you have initialized the Interfaces, Service and Controller classes. In this section, you will learn how to migrate the existing business logic from PB data objects into the corresponding classes.
SnapDevelop provides DataWindow Conveter plug-in and PowerScript Migrator plug-in to make migration of DataWindows and PowerScript NVO's to C# assemblies or Web API's as easy and fast as possible. In this tutorial we will divide them in 3 major steps:
Step 1: Using the DataWindow Converter plug-in
Step 2: Porting PowerScript functions to Services and Controllers using the PowerScript Migrator plug-in
Step 3: Reusing Embedded SQL in Services and Controllers
Using the DataWindow Converter plug-in
You can automatically migrate the PowerScript data objects to C# data models using the DataWindow Converter plug-in. The DataWindow conversion utility also supports batch conversion, automatically migrating hundreds or even thousands of DataWindows and DataStores.
To launch the DataWindow Converter,
Open the PowerBuilder application which contains the DataWindow object in SnapDevelop IDE (right click in the Solution Explorer, select Open PB Workspace).
Right click the workspace, target, library file, or the SRD file of the DataWindow object, and then choose Convert DataWindow to C# Model.
If you right click the workspace, target, or library file, all of the DataWindow objects contained in it will be displayed in the DataWindow Converter.
If you right click the SRD file of DataWindow, only that DataWindow will be displayed in the DataWindow Converter.
Now, on the DataWindow window you will see the selected DataObject(s). Click on the Export button.
Then on the popup Database Connection window, click New.
Configure the AppeonSample connection with the same settings as Add a database context, and click OK.
Then Connection String is generated, click OK.
Then, on the Model Export window, browse the file system to your Web API project folder and select the OrderDemo.csproj file. Then click on Export.
In Solution Explorer, you will now find the data models (.cs files) exported into your Web API project!
Porting PowerScript functions to Services and Controllers Using the PowerScript Migrator Plug-in
The best approach to porting PowerScript is to first refactor your application in order to manage the Web API operations. Then porting the PowerScript code is easier for larger applications.
In this tutorial, the demo application has already been refactored. So all the Web API operations related to Customers have been placed, in PowerBuilder, in a Non-Visual Object named nvo_customer. The same refactoring has been applied to the Orders (nvo_order) and Products (nvo_product).
Now that we have the Interfaces, Services and Controllers, the next step is to migrate the PowerScript from the NVOs into the Services and Controllers.
You can automatically migrate PowerScript code to C# using the PowerScript Migrator plug-in.
For this tutorial we will use .NET DataStore because it is the best migration solution for existing DataWindows. Since you don't need to re-code each individual DataWindow, and .NET DataStore has virtually the same properties, events and functions as the DataWindow. Thus making the migration easier, faster and even can use less code.
Migrating NVO_Customer
Start by porting the PowerScript from the customer NVO (NVO_Customer) to the corresponding service (CustomerService class) and controller (CustomerController class).
The following table provides an overview of the functions in NVO_Customer, and the corresponding method in the service and controller each function will be ported to.
| PowerScript Function | Functionality and migration strategy | To be ported to Service | To be ported to Controller |
|---|---|---|---|
| of_Retrieve_Customer_Select() | Retrieve the customer list for selecting the customer when adding a new order. The .NET DataStore's Retrieve() method is used to migrate the PowerScript. |
RetrieveCustomerSelect() | RetrieveCustomerSelect() |
| of_Retrieve_Cust_list() | Retrieve the customer list used to view orders. The .NET DataStore's Retrieve() method is used to migrate the PowerScript. |
RetrieveCustList() | RetrieveCustList() |
Porting the function of_Retrieve_Customer_Select()
Original PowerScript code
Int li_return
// Set the transaction object
adw_customers.SetTransObject(SQLCA)
// Retrieve the DW
li_return = adw_customers.Retrieve()
// Return the number of rows
Return li_return
Ported code in the Service class
If you have opened the nvo_customer.sru in SnapDevelop IDE, you can click in the of_Retrieve_Customer_Select function and select Translate and Open in Editor to automatically translate the PowerScript code into C#. You may review and adjust the translated code according to the in-line warning/error/information messages in the code, and then update the RetrieveCustomerSelect() method in the CustomerService class. In this example, the C# code to be added to the method is as follows:
// Create the .NET DataStore; set its Dataobject; and set its DataContext
IDataStore ds_Customer_select = new DataStore("d_customer_select", _context);
// Retrieve the .NET DataStore
ds_Customer_select.Retrieve();
// Return the .NET DataStore
return ds_Customer_select;
Notice how creating the .NET DataStore object, assigning the DataObject and the DataContext was done in one single line of code.
Ported code in the Controller class
Update the RetrieveCustomerSelect() method in CustomerController class with the following code:
/// <summary>
/// Retrieves the d_customer_select DataStore.
/// </summary>
/// <returns>The d_customer_select DataStore in Plain JSON.</returns>
/// GET api/customers/RetrieveCustomerSelect
[HttpGet]
[ProducesResponseType(StatusCodes.Status200OK)]
[ProducesResponseType(StatusCodes.Status404NotFound)]
public ActionResult<string> RetrieveCustomerSelect()
{
// Declare and initialize a local variable by calling the service
var ds = _service.RetrieveCustomerSelect();
// Check if any rows have been returned
if (ds.RowCount == 0)
{
// Return a Status Code 404 (NotFound)
return NotFound("No rows retrieved.");
}
else
{
// Return Status Code 200 with the Plain JSON
return Ok(ds);
}
}
Porting the function of_Retrieve_cust_list()
Original PowerScript code
Int li_return
// Set the Transaction Object
adw_customers.SetTransObject(SQLCA)
// Retrieve the DW
li_return = adw_customers.Retrieve()
// Return the number of rows
Return li_return
Ported code in the Service class
If you have opened the nvo_customer.sru in SnapDevelop IDE, you can click in the of_Retrieve_cust_list function and select Translate and Open in Editor to automatically translate the PowerScript code into C#. You may review and adjust the translated code according to the in-line warning/error/information messages in the code, and then update the RetrieveCustList() method in the CustomerService class. In this example, the C# code to be added to the method is as follows:
// Create the .NET DataStore; set its Dataobject; and set its DataContext
IDataStore ds_Customer_list = new DataStore("d_customer_list", _context);
// Retrieve the .NET DataStore
ds_Customer_list.Retrieve();
// Return the .NET DataStore
return ds_Customer_list;
Ported code in the Controller class
Update the RetrieveCustList() method in the CustomerController class with the following code:
/// <summary>
/// Retrieves the d_customer_list DataStore.
/// </summary>
/// <returns>The d_customer_list DataStore in Plain JSON format.</returns>
/// GET api/customers/RetrieveCustList
[HttpGet]
[ProducesResponseType(StatusCodes.Status200OK)]
[ProducesResponseType(StatusCodes.Status404NotFound)]
public ActionResult<string> RetrieveCustList()
{
// Declare and initialize a local variable by calling the service
var ds_Cust_list_all = _service.RetrieveCustList();
// Check if any rows have been returned
if (ds_Cust_list_all.RowCount > 0)
{
// Return Status Code 200 with the Plain JSON
return Ok(ds_Cust_list_all);
}
else
{
// Return a Status Code 404 (NotFound)
return NotFound("No rows retrieved.");
}
}
Note: It is a good approach to return plain JSON on the Retrieve methods because this data state is not always required after the retrieval of data. This boosts performance and reduces the amount of data that needs to be sent back to the client.
Migrating NVO_Order
Let's now continue porting the PowerScript from the order NVO (NVO_Order) to the corresponding service (OrderService class) and controller (OrderController class).
The following table provides an overview of the functions in NVO_Order, and the corresponding method in the service and controller each function will be ported to.
| PowerScript Function | Functionality and migration strategy | To be ported to Service | To be ported to Controller |
|---|---|---|---|
| of_Retrieve_Order_list() | Retrieve the order list by customer number. The .NET DataStore's Retrieve() method is used to migrate the PowerScript. |
RetrieveOrderList() | RetrieveOrderList() |
| of_Retrieve_Order_modify() | Retrieve the order by order number which is used to modify a order. The .NET DataStore's Retrieve() method is used to migrate the PowerScript. |
RetrieveOrderModify() | RetrieveOrderModify() |
| of_Retrieve_OrderItem_edit() | Retrieve the order item that is used to add and modify the order item in an order. The .NET DataStore's Retrieve() method is used to migrate the PowerScript. The IDataStore.ExportJson() method is used to export data from the Primary buffer and DDDWs in a DataWindow JSON. |
RetrieveOrderItemEdit() | RetrieveOrderItemEdit() |
| of_Retrieve_OrderItem_list() | Retrieve the order item that is used to view order item in an order. The .NET DataStore's Retrieve() method is used to migrate the PowerScript. The IDataStore.ExportJson() method is used to export data from the Primary buffer and DDDWs in a DataWindow JSON. |
RetrieveOrderItemList() | RetrieveOrderItemList() |
| of_Add() | Create the order and its order items. The .NET DataStore's Update() method is used to migrate the PowerScript. Notice that it will use DataContext to manage the transaction. It has the same code with of_Modify below. The function uses IDataUnpacker type parameter to receive multiple data elements from the JSON generated by JSONPackage object from the native PowerBuilder client. |
Add() | Add() |
| of_Modify() | Update the order and its order items. The .NET DataStore's Update() method is used to migrate the PowerScript. Notice that it will use DataContext to manage the transaction. The function uses IDataUnpacker type parameter to receive multiple data elements from the JSON generated by JSONPackage object from the native PowerBuilder client. |
Modify() | Modify() |
Porting the function of_Retrieve_Order_list()
Original PowerScript code
Int li_return
// Set the Transaction Object
adw_orders.SetTransObject(SQLCA);
// Retrieve the DW
li_return = adw_orders.Retrieve(as_custno)
// Return the number of rows
Return li_return
Ported code in the Service class
If you have opened the nvo_order.sru in SnapDevelop IDE, you can click in the of_Retrieve_Order_list function and select Translate and Open in Editor to automatically translate the PowerScript code into C#. You may review and adjust the translated code according to the in-line warning/error/information messages in the code, and then update the RetrieveOrderList(string custNo) method in the OrderService class. In this example, the C# code to be added to the method is as follows:
// Create the .NET DataStore; set its Dataobject; and set its DataContext
IDataStore ds_Order_list = new DataStore("d_order_list", _context);
// Retrieve the .NET DataStore
ds_Order_list.Retrieve(custNo);
// Return the .NET DataStore
return ds_Order_list;
Ported code in the Controller class
Update the RetrieveOrderList(string custNo) method in the OrderController class with the following code:
/// <summary>
/// Retrieves the order list.
/// </summary>
/// <returns>The order list.</returns>
/// <param name="custNo">Cust no.</param>
/// GET api/order/RetrieveOrderList
[HttpGet]
[ProducesResponseType(StatusCodes.Status200OK)]
[ProducesResponseType(StatusCodes.Status404NotFound)]
public ActionResult<IDataStore> RetrieveOrderList(string custNo)
{
// Declare and initialize a local variable by calling the service
var ds = _service.RetrieveOrderList(custNo);
// Check if any rows have been returned
if (ds.RowCount > 0)
{
// Return Status Code 200 with the Plain JSON
return Ok(ds);
}
else
{
// Return a Status Code 404 (NotFound)
return NotFound("No rows retrieved.");
}
}
Porting the function of_Retrieve_Order_modify()
Original PowerScript code
Int li_return
// Set the Transaction Object
adw_order.SetTransObject(SQLCA);
// Retrieve the DW
li_return = adw_order.Retrieve(as_orderno)
// Return the number of rows
Return li_return
Ported code in the Service class
If you have opened the nvo_order.sru in SnapDevelop IDE, you can click in the of_Retrieve_Order_modify function and select Translate and Open in Editor to automatically translate the PowerScript code into C#. You may review and adjust the translated code according to the in-line warning/error/information messages in the code, and then update the RetrieveOrderModify(string custNo) method in the OrderService class. In this example, the C# code to be added to the method is as follows:
// Create the .NET DataStore; set its Dataobject; and set its DataContext
IDataStore ds_Order_modify = new DataStore("d_order_modify", _context);
// Retrieve the .NET DataStore
ds_Order_modify.Retrieve(orderNo);
// Return the .NET DataStore
return ds_Order_modify;
Ported code in the Controller class
Update the RetrieveOrderModify(string orderNo) method in the OrderController class with the following code:
/// <summary>
/// Retrieves the order record to be modified.
/// </summary>
/// <returns>The order record to be modified.</returns>
/// <param name="orderNo">Order no.</param>
// GET api/order/RetrieveOrderCustModify
[HttpGet]
[ProducesResponseType(StatusCodes.Status200OK)]
[ProducesResponseType(StatusCodes.Status404NotFound)]
public ActionResult<IDataStore> RetrieveOrderModify(string orderNo)
{
// Declare and initialize a local variable by calling the service
var ds = _service.RetrieveOrderModify(orderNo);
// Check if any rows have been returned
if (ds.RowCount > 0)
{
// Return Status Code 200 with the Plain JSON
return Ok(ds);
}
else
{
// Return a Status Code 404 (NotFound)
return NotFound("No rows retrieved.");
}
}
Porting the function of_Retrieve_OrderItem_edit()
Original PowerScript code
Int li_return
// Set the Transaction Object
adw_orderItems.SetTransObject(SQLCA);
// Retrieve the DW
li_return = adw_orderItems.Retrieve(as_custno,as_orderno)
// Return the number of rows
Return li_return
Ported code in the Service class
If you have opened the nvo_order.sru in SnapDevelop IDE, you can click in the of_Retrieve_OrderItem_edit function and select Translate and Open in Editor to automatically translate the PowerScript code into C#. You may review and adjust the translated code according to the in-line warning/error/information messages in the code, and then update the RetrieveOrderItemEdit(string custNo, string orderNo) method in the OrderService class. In this example, the C# code to be added to the method is as follows:
// Create the .NET DataStore; set its Dataobject; and set its DataContext
IDataStore ds_OrderItem_edit = new DataStore("d_orderitem_edit", _context);
// Retrieve the .NET DataStore
ds_OrderItem_edit.Retrieve(custNo, orderNo);
// Return the .NET DataStore
return ds_OrderItem_edit;
Ported code in the Controller class
Update the RetrieveOrderItemEdit(string custNo, string orderNo) method in the OrderController class with the following code:
/// <summary>
/// Retrieves the order item edit list.
/// </summary>
/// <returns>The order item edit list.</returns>
/// <param name="custNo">Cust no.</param>
/// <param name="orderNo">Order no.</param>
// GET api/order/RetrieveOrderItemEdit
[HttpGet]
[ProducesResponseType(StatusCodes.Status200OK)]
[ProducesResponseType(StatusCodes.Status404NotFound)]
public ActionResult<string> RetrieveOrderItemEdit(string custNo, string orderNo)
{
var ds = _service.RetrieveOrderItemEdit(custNo, orderNo);
if (ds.RowCount > 0)
{
// Export data from the Primary buffer and DDDWs.
return Ok(ds.ExportJson(true, false, false, true, MappingMethod.Index));
}
else
{
return NotFound("No rows retrieved.");
}
}
Note: It uses the IDataStore.ExportJson() method in this example to export data from the Primary buffer and DDDWs in a DataWindow JSON. To ensure better performance of the Web API, you can consider the logic of independently wrapping the cached DDDW data according to the business logic, instead of using plain JSON or DataWindow JSON. You can also consider returning an IDataPacker object to export data from both the Primary buffer and DDDWs.
Porting the function of_Retrieve_OrderItem_list()
Original PowerScript code
Int li_return
// Set the Transaction Object
adw_orderitems.SetTransObject(SQLCA);
// Retrieve the DW
li_return = adw_orderitems.Retrieve(as_custno, as_orderno)
// Return the number of rows
Return li_return
Ported code in the Service class
If you have opened the nvo_order.sru in SnapDevelop IDE, you can click in the of_Retrieve_OrderItem_list function and select Translate and Open in Editor to automatically translate the PowerScript code into C#. You may review and adjust the translated code according to the in-line warning/error/information messages in the code, and then update the RetrieveOrderItemList(string custNo, string orderNo) method in the OrderService class. In this example, the C# code to be added to the method is as follows:
// Create the .NET DataStore; set its Dataobject; and set its DataContext
IDataStore ds_OrderItem_list = new DataStore("d_orderitem_list", _context);
// Retrieve the .NET DataStore
ds_OrderItem_list.Retrieve(custNo, orderNo);
// Return the .NET DataStore
return ds_OrderItem_list;
Ported code in the Controller class
Update the RetrieveOrderItemList(string custNo, string orderNo) method in the OrderController class with the following code:
/// <summary>
/// Retrieves the order item list.
/// </summary>
/// <returns>The order item list.</returns>
/// <param name="custNo">Cust no.</param>
/// <param name="orderNo">Order no.</param>
// GET api/order/RetrieveOrderItemEdit
[HttpGet]
[ProducesResponseType(StatusCodes.Status200OK)]
[ProducesResponseType(StatusCodes.Status404NotFound)]
public ActionResult<string> RetrieveOrderItemList(string custNo, string orderNo)
{
// Declare and initialize a local variable by calling the service
var ds = _service.RetrieveOrderItemList(custNo, orderNo);
// Check if any rows have been returned
if (ds.RowCount > 0)
{
// Export data from the Primary buffer and DDDWs.
// Return Status Code 200
return Ok(ds.ExportJson(true, false, false, true, MappingMethod.Index));
}
else
{
// Return a Status Code 404 (NotFound)
return NotFound("No rows retrieved.");
}
}
Porting the function of_Add()
Original PowerScript Code
adw_order.SetTransObject(SQLCA)
adw_orderitems.SetTransObject(SQLCA)
If adw_order.UPDATE() = 1 Then
If adw_orderitems.UPDATE() = 1 Then
COMMIT;
Return 1
Else
ROLLBACK;
MessageBox("Warning","Failed to save the changes.",exclamation!)
Return -1
End If
Else
ROLLBACK;
MessageBox("Warning","Failed to save the changes.",exclamation!)
Return -1
End If
Ported code in the Service class
If you have opened the nvo_order.sru in SnapDevelop IDE, you can click in the of_Add function and select Translate and Open in Editor to automatically translate the PowerScript code into C#. You may review and adjust the translated code according to the in-line warning/error/information messages in the code, and then update the Add(IDataStore order, IDataStore orderItems) method in the OrderService class. In this example, the C# code to be added to the method is as follows:
// Return the value from the Modify Method
return Modify(order, orderItems);
Note: it reuses Modify() method here because the Modify() method and the Add() method have exactly the same code.
Ported code in the Controller class
Update the Add(IDataUnpacker dataUnpacker) method in the OrderController class with the following code:
/// <summary>
/// Add the specified Order and OrderItem.
/// </summary>
/// <returns>The Action Result.</returns>
/// <param name="dataUnpacker">Data unpacker.</param>
/// POST api/orders/add
[HttpPost]
[ProducesResponseType(StatusCodes.Status200OK)]
[ProducesResponseType(StatusCodes.Status500InternalServerError)]
public ActionResult Add(IDataUnpacker dataUnpacker)
{
// Declare and initialize local .NET DataStore variables
// By getting the Packaged DataStores
IDataStore order = dataUnpacker.GetDataStore("order", _context);
IDataStore orderItems = dataUnpacker.GetDataStore("orderItems", _context);
// Check if the addition was successful
if (_service.Add(order, orderItems))
{
// Return Status Code 200
return Ok();
}
else
{
// Return a Status Code 500 for Internal Server Error
return StatusCode(StatusCodes.Status500InternalServerError);
}
}
Note: you can use an IDataUnpacker type parameter to receive multiple data elements from the JSON generated by JSONPackage object from native PowerBuilder client.
Porting the function of_Modify()
Original PowerScript Code
adw_order.SetTransObject(SQLCA)
adw_orderitems.SetTransObject(SQLCA)
If adw_order.UPDATE() = 1 Then
If adw_orderitems.UPDATE() = 1 Then
COMMIT;
Return 1
Else
ROLLBACK;
MessageBox("Warning","Failed to save the changes.", exclamation!)
Return -1
End If
Else
ROLLBACK;
MessageBox("Warning","Failed to save the changes.", exclamation!)
Return -1
End If
Ported code in the Service class
If you have opened the nvo_order.sru in SnapDevelop IDE, you can click in the of_Modify function and select Translate and Open in Editor to automatically translate the PowerScript code into C#. You may review and adjust the translated code according to the in-line warning/error/information messages in the code, and then update the Modify(IDataStore order, IDataStore products) method in the OrderService class. In this example, the C# code to be added to the method is as follows:
// Begin the Transaction
_context.BeginTransaction();
// Do a try/catch to catch errors
try
{
// Update the Order DataStore
order.Update();
// Update the Products DataStore
orderItems.Update();
// Commit the transaction
_context.Commit();
// Return TRUE for success
return true;
}
catch
{
// Rollback the transaction
_context.Rollback();
// Return FALSE for failure
return false;
}
Ported code in the Controller class
Update the Modify(IDataUnpacker dataUnpacker) method in the OrderController class with the following code:
/// <summary>
/// Modify the specified Order and OrderItems.
/// </summary>
/// <returns>The Action Result.</returns>
/// <param name="dataUnpacker">Data unpacker.</param>
/// POST api/orders/modify
[HttpPost]
[ProducesResponseType(StatusCodes.Status200OK)]
[ProducesResponseType(StatusCodes.Status500InternalServerError)]
public ActionResult Modify(IDataUnpacker dataUnpacker)
{
// Declare and initialize local .NET DataStore variables
// By getting the Packaged DataStores
IDataStore order = dataUnpacker.GetDataStore("order", _context);
IDataStore orderItems = dataUnpacker.GetDataStore("orderitems", _context);
// Check if the modification was successful
if (_service.Modify(order, orderItems))
{
// Return Status Code 200
return Ok();
}
else
{
// Return a Status Code 500 for Internal Server Error
return StatusCode(StatusCodes.Status500InternalServerError);
}
}
Migrating NVO_Product
Let's continue porting the PowerScript from the product NVO (NVO_Product) to the corresponding service (ProductService class) and controller (ProductController class).
The following table provides an overview of the functions in NVO_Product, and the corresponding method in the service and controller each function will be ported to.
| PowerScript Function | Functionality and migration strategy | To be ported to Service | To be ported to Controller |
|---|---|---|---|
| of_Retrieve_dddw_Category() | Retrieve the category of products that is used in DDDW. The .NET DataStore's Retrieve() method is used to migrate the PowerScript. It reuses the model D_Product here to return a row of product info. |
RetrieveDddwCategory() | RetrieveDddwCategory() |
| of_Retrieve_DDDW_Products() | Retrieve the products, used in DDDW. The .NET DataStore's Retrieve() method is used to migrate the PowerScript. It reuses the model D_Product here to return a row of product info. |
RetrieveDddwProducts() | RetrieveDddwProducts() |
Porting the function of_Retrieve_DDDW_Products()
Original PowerScript code
Int li_return
// Set the Transaction Object
adwc_products.SetTransObject(SQLCA)
// Retrieve the DWC
li_return = adwc_products.Retrieve()
// Return the retrieve count
Return li_return
Ported code in the Service class
If you have opened the nvo_product.sru in SnapDevelop IDE, you can click in the of_Retrieve_DDDW_Products function and select Translate and Open in Editor to automatically translate the PowerScript code into C#. You may review and adjust the translated code according to the in-line warning/error/information messages in the code, and then update the RetrieveDddwProducts() method in the ProductService class. In this example, the C# code to be added to the method is as follows:
// Create the .NET DataStore; set its Dataobject; and set its DataContext
IDataStore ds_DDDW_products = new DataStore("d_dddw_products", _context);
// Retrieve the .NET DataStore
ds_DDDW_products.Retrieve();
// Return the .NET DataStore
return ds_DDDW_products;
Ported code in the Controller class
Update the RetrieveDddwProducts() method in the ProductController class with the following code:
/// <summary>
/// Retrieves the d_dddw_products DataStore.
/// </summary>
/// <returns>The retrieved d_dddw_products DataStore.</returns>
/// GET api/product/RetrieveDddwProducts
[HttpGet]
[ProducesResponseType(StatusCodes.Status200OK)]
[ProducesResponseType(StatusCodes.Status404NotFound)]
public ActionResult<string> RetrieveDddwProducts()
{
// Declare and initialize a local variable by calling the service
var ds = _service.RetrieveDddwProducts();
// Check if any rows have been returned
if (ds.RowCount > 0)
{
// Return Status Code 200 with the Plain JSON
return Ok(ds);
}
else
{
// Return a Status Code 404 (NotFound)
return NotFound("No rows retrieved.");
}
}
Porting the function of_Retrieve_dddw_Category()
Original PowerScript code
Int li_return
// Set the Transaction Object
adwc_categories.SetTransObject(SQLCA)
// Retrieve the DW
li_return = adwc_categories.Retrieve()
// Return the number of rows
Return li_return
Ported code in the Service class
If you have opened the nvo_product.sru in SnapDevelop IDE, you can click in the of_Retrieve_dddw_Category function and select Translate and Open in Editor to automatically translate the PowerScript code into C#. You may review and adjust the translated code according to the in-line warning/error/information messages in the code, and then update the RetrieveDddwCategory() method in the ProductService class. In this example, the C# code to be added to the method is as follows:
// Create the .NET DataStore; set its Dataobject; and set its DataContext
IDataStore ds_Dddw_prod_category = new DataStore("d_dddw_prod_category", _context);
// Retrieve the .NET DataStore
ds_Dddw_prod_category.Retrieve();
// Return the .NET DataStore
return ds_Dddw_prod_category;
Ported code in the Controller class
Update the RetrieveDddwCategory() method in the ProductController class with the following code:
/// <summary>
/// Retrieves the d_dddw_category DataStore.
/// </summary>
/// <returns>The d_dddw_category DataStore.</returns>
/// GET api/product/RetrieveDddwCategory
[HttpGet]
[ProducesResponseType(StatusCodes.Status200OK)]
[ProducesResponseType(StatusCodes.Status404NotFound)]
public ActionResult<string> RetrieveDddwCategory()
{
// Declare and initialize a local variable by calling the service
var ds = _service.RetrieveDddwCategory();
// Check if any rows have been returned
if (ds.RowCount == 0)
{
// Return a Status Code 404 (NotFound)
return NotFound("No rows retrieved.");
}
else
{
// Return Status Code 200 with the Plain JSON
return Ok(ds);
}
}
Reusing Embedded SQL in Services and Controllers
In this section, you will learn how the embedded SQL can be reused when migrating in the Service and Controller.
Reusing Embedded SQL from your PB application is as easy as Copy/Paste. The object that manages the embedded SQL is named SqlExecutor. This object enables us to reuse the embedded SQL from PowerBuilder and it also empowers our code with the methods it provides.
For example: let's say that I only need to obtain the first value from the first column from the result set of a SQL query. So instead of using a combination of several methods, I could only use the Scalar method from the SqlExecutor object which does that for me in one step.
If you'd like to know more about the SqlExecutor object you can refer to the online documentation.
Migrating NVO_Customer
Let's now port the function with embedded SQL queries from the customer NVO (NVO_Customer) to the corresponding service (CustomerService class) and controller (CustomerController class).
The following table provides an overview of the function with embedded SQL queries in NVO_Customer, and the corresponding method in the service and controller the function will be ported to.
| PowerScript Function | Functionality and migration strategy | To be ported to Service | To be ported to Controller |
|---|---|---|---|
| of_Check_CustNo() | Check whether a customer number exists. The SqlExecutor's Scalar() method is used to migrate this embedded SQL. |
CheckCustNo() | CheckCustNo() |
Porting the function of_Check_CustNo()
Original PowerScript code with embedded SQL
This is the original code with embedded SQL in of_Check_CustNo():
SELECT Count(*) Into :li_Count From t_customers Where fcustno = :as_custno;
Ported code in the Service class
If you have opened the nvo_customer.sru in SnapDevelop IDE, you can click in the of_Check_CustNo function and select Translate and Open in Editor to automatically translate the PowerScript code into C#. You may review and adjust the translated code according to the in-line warning/error/information messages in the code, and then update the CheckCustNo(string custNo) method in the CustomerService class. In this example, the C# code to be added to the method is as follows:
// Paste the Embedded SQL into this function
int count = _context.SqlExecutor.Scalar<int>(
"SELECT Count(*) FROM t_customers WHERE fcustno = @custNo",
custNo);
// Return the total count
return count;
Ported code in the Controller class
Update the CheckCustNo(string custNo) method in the CustomerController class with the following code:
/// <summary>
/// Checks the cust no.
/// </summary>
/// <returns>1 if custNo is valid and -1 if not.</returns>
/// <param name="custNo">Cust no.</param>
/// GET api/customers/CheckCustNo
[HttpGet]
[ProducesResponseType(StatusCodes.Status200OK)]
public ActionResult<int> CheckCustNo(string custNo)
{
// Return the value recieved from calling the service
return _service.CheckCustNo(custNo);
}
Migrating NVO_Order
Next, let's port the function with embedded SQL queries from the order NVO (NVO_Order) to the corresponding service (OrderService class) and controller (OrderController class).
The following table provides an overview of the function with embedded SQL queries in NVO_Order, and the corresponding method in the service and controller the function will be ported to.
| PowerScript Function | Functionality and migration strategy | To be ported to Service | To be ported to Controller |
|---|---|---|---|
| of_Delete() | Delete the order and its order items. The SqlExecutor's Execute() method is used to migrate the embedded SQLs. |
Delete() | Delete() |
| of_Get_LineId_Max() | Get the maximum line ID of the order items in an order. The SqlExecutor's Scalar() method is used to migrate the embedded SQLs. |
GetLineIdMax() | GetLineIdMax() |
| of_Get_OrderNo() | Get a new order number for a customer. The SqlExecutor's Scalar() method is used to migrate Embedded SQLs. |
GetOrderNo() | GetOrderNo() |
Porting the function of_Delete()
Original PowerScript code with embedded SQL
This is the original code with embedded SQL in of_Delete():
//firstly delete order item detail
DELETE FROM t_orders_items WHERE forderno = :as_orderno;
//delete order information
DELETE FROM t_orders WHERE forderno = :as_orderno;
Ported code in the Service class
If you have opened the nvo_order.sru in SnapDevelop IDE, you can click in the of_Delete function and select Translate and Open in Editor to automatically translate the PowerScript code into C#. You may review and adjust the translated code according to the in-line warning/error/information messages in the code, and then update the Delete(string orderNo) method in the OrderService class. In this example, the C# code to be added to the method is as follows:
// Delete Order Items from the database
_context.SqlExecutor.Execute("DELETE FROM t_orders_items WHERE forderno = @orderNo", orderNo);
// Delete the Order from the database
_context.SqlExecutor.Execute("DELETE FROM t_orders WHERE forderno = @orderNo", orderNo);
Ported code in the Controller class
Update the Delete(string orderNo) method in the OrderController class with the following code:
/// <summary>
/// Delete the specified Order and Order Items.
/// </summary>
/// <returns>The Action Result.</returns>
/// <param name="orderNo">Order no.</param>
/// DELETE api/order/delete/{orderNo}
[HttpDelete("{orderNo}")]
[ProducesResponseType(StatusCodes.Status200OK)]
[ProducesResponseType(StatusCodes.Status500InternalServerError)]
public ActionResult Delete(string orderNo)
{
// Do a Try/Catch for Exception handling
try
{
// Delete the Order and Order Items by calling the service
_service.Delete(orderNo);
// Return a Status Code of 200
return Ok();
}
catch (Exception e)
{
// Return a Status Code of 500 with the Exception Message
return StatusCode(StatusCodes.Status500InternalServerError, e.Message);
}
}
Porting the function of_Get_LineId_Max()
Original PowerScript code with embedded SQL
This is the original code with embedded SQL in of_Get_LineId_Max():
Int li_LineID
SELECT isnull(max(flineid),0)
INTO :li_LineID
FROM t_orders_items
WHERE forderNo = :as_orderno;
If IsNull(li_LineID) Or li_LineID < 1 Then
li_LineID = 0
End If
Return li_LineID
Ported code in the Service class
If you have opened the nvo_order.sru in SnapDevelop IDE, you can click in the of_Get_LineId_Max function and select Translate and Open in Editor to automatically translate the PowerScript code into C#. You may review and adjust the translated code according to the in-line warning/error/information messages in the code, and then update the GetLineIdMax(string orderNo) method in the OrderService class. In this example, the C# code to be added to the method is as follows:
// Paste the Embedded SQL query in this function
short maxLineId = _context.SqlExecutor.Scalar<short>(
@"SELECT isnull(max(flineid),0)
FROM t_orders_items
WHERE forderNo = @orderNo",
orderNo);
// Return the Max Line Id
return maxLineId;
Ported code in the Controller class
Update the GetLineIdMax(string orderNo) method in the OrderController class with the following code:
/// <summary>
/// Gets the line identifier max.
/// </summary>
/// <returns>The line identifier max.</returns>
/// <param name="orderNo">Order no.</param>
/// GET api/order/GetLineIdMax
[HttpGet]
[ProducesResponseType(StatusCodes.Status200OK)]
public ActionResult<short> GetLineIdMax(string orderNo)
{
// Return the value from calling the service
return _service.GetLineIdMax(orderNo);
}
Porting the function of_Get_OrderNo()
Original PowerScript code with embedded SQL
This is the original code with embedded SQL in of_Get_OrderNo():
String ls_OrderNo
Int li_Max
SELECT max(convert(integer,right(forderNo,2)))+1
INTO :li_Max
FROM t_orders
WHERE fcustNo = :as_custNo;
If isnull(li_Max) Or li_Max <=0 Then li_Max = 1
ls_OrderNo = string(li_Max,'00')
ls_orderNo = as_custNo + ls_orderNo
Return ls_OrderNo
Ported code in the Service class
If you have opened the nvo_order.sru in SnapDevelop IDE, you can click in the of_Get_OrderNo function and select Translate and Open in Editor to automatically translate the PowerScript code into C#. You may review and adjust the translated code according to the in-line warning/error/information messages in the code, and then update the GetOrderNo(string custNo) method in the OrderService class. In this example, the C# code to be added to the method is as follows:
// Paste the Embedded SQL in this function
int? maxOrderNo = _context.SqlExecutor.Scalar<int?>(
@"SELECT max(convert(integer,right(forderNo,2)))+1
FROM t_orders WHERE fcustNo = @as_custNo",
custNo);
// Check if the Order Number exists
if (maxOrderNo == null || maxOrderNo <= 0)
{
// Set the local variable to 1 for NOT VALID
maxOrderNo = 1;
}
// Set the formatted Value of the Order Number
string orderNo = maxOrderNo.Value.ToString("00");
orderNo = custNo + orderNo;
// Return the Formatted Order Number
return orderNo;
Ported code in the Controller class
Update the GetOrderNo(string custNo) method in the OrderController class with the following code:
/// <summary>
/// Gets the formatted Order Number.
/// </summary>
/// <returns>The formatted Order Number.</returns>
/// <param name="custNo">Cust no.</param>
/// GET api/order/GetOrderNo
[HttpGet]
[ProducesResponseType(StatusCodes.Status200OK)]
public ActionResult<string> GetOrderNo(string custNo)
{
// Return the Formatted Order Number by calling the service
return _service.GetOrderNo(custNo);
}
Migrating NVO_Product
Let's port the function with embedded SQL queries from the product NVO (NVO_Product) to the corresponding service (ProductService class) and controller (ProductController class).
The following table provides an overview of the function with embedded SQL queries in NVO_Product, and the corresponding method in the service and controller the function will be ported to.
| PowerScript Function | Functionality and migration strategy | To be ported to Service | To be ported to Controller |
|---|---|---|---|
| of_Select_By_Sku() | Retrieve a product by its SKU (Stock Keeping Unit) that is used to get product info when adding or modifying an order item. The .NET DataStore's Retrieve() method is used to migrate the PowerScript. |
SelectBySku() | SelectBySku() |
| of_Select_By_Name() | Retrieve a product by its product name that is used to get product info when adding or modifying an order item. The .NET DataStore's Retrieve() method is used to migrate the PowerScript. |
SelectByName() | SelectByName() |
Porting the function of_Select_By_Sku()
Original PowerScript code with embedded SQL
This is the original code with embedded SQL in of_Select_By_Sku():
String ls_category
String ls_ProdName
String ls_description
Dec ldec_unitprice
SELECT Top 1 fcategory, fproname, funit_price, fdescription
INTO :ls_category, :ls_ProdName, :ldec_unitprice, :ls_description
FROM t_products
WHERE fsku = :sku;
If SQLCA.SQLCode <> 0 Then
MessageBox("Alert","The SKU does not exist.")
Return 1
End If
adw_orderitems.Object.fcategory[row] = ls_category
adw_orderitems.Object.fproname[row] = ls_ProdName
adw_orderitems.Object.funit_price[row] = ldec_unitprice
adw_orderitems.Object.fdescription[row] = ls_description
adw_orderitems.Object.famount[row] = ldec_unitprice*adw_orderitems.Object.fquantity[row]
Ported code in the Service class
If you have opened the nvo_product.sru in SnapDevelop IDE, you can click in the of_Select_By_Sku function and select Translate and Open in Editor to automatically translate the PowerScript code into C#. You may review and adjust the translated code according to the in-line warning/error/information messages in the code, and then update the SelectBySku(string sku) method in the ProductService class. In this example, the C# code to be added to the method is as follows:
// Paste the Embedded SQL into this function
string sql = @"SELECT TOP 1 fcategory,fproname,funit_price,fdescription
FROM t_products
WHERE fsku = @sku";
// Returns the retrieved product using the embedded sql
return _context.SqlExecutor.SelectOne<D_Product>(sql, sku);
Ported code in the Controller class
Update the SelectBySku(string sku) method in the ProductController class with the following code:
/// <summary>
/// Selects the product by sku.
/// </summary>
/// <returns>The requested product by sku.</returns>
/// <param name="sku">Sku.</param>
/// GET api/product/SelectBySku
[HttpGet]
[ProducesResponseType(StatusCodes.Status200OK)]
[ProducesResponseType(StatusCodes.Status404NotFound)]
public ActionResult<D_Product> SelectBySku(string sku)
{
// Declare a local variable and initialize it by calling the service
var product = _service.SelectBySku(sku);
// Check if anything has been returned
if (product == null)
{
// Return a Status Code 404 (Not Found)
return NotFound("No rows retrieved.");
}
else
{
// Return Status Code 200 with the Product data
return Ok(product);
}
}
Porting the function of_Select_By_Name()
Original PowerScript code with embedded SQL
This is the original code with embedded SQL in of_Select_By_Name():
String ls_category
String ls_Sku
String ls_description
Dec ldec_unitprice
SELECT Top 1 fcategory, funit_price, fsku, fdescription
INTO :ls_category, :ldec_unitprice, :ls_Sku, :ls_description
FROM t_products
WHERE fproname = :name;
If SQLCA.SQLCode <> 0 Then
MessageBox("Alert","The Product Name does not exist!")
Return 1
End If
adw_orderitems.Object.fcategory[row] = ls_category
adw_orderitems.Object.funit_price[row] = ldec_unitprice
adw_orderitems.Object.fdescription[row] = ls_description
adw_orderitems.Object.fsku[row] = ls_sku
adw_orderitems.Object.famount[row] = ldec_unitprice*adw_orderitems.Object.fquantity[row]
Ported code in the Service class
If you have opened the nvo_product.sru in SnapDevelop IDE, you can click in the of_Select_By_Name function and select Translate and Open in Editor to automatically translate the PowerScript code into C#. You may review and adjust the translated code according to the in-line warning/error/information messages in the code, and then update the SelectByName(string name) method in the ProductService class. In this example, the C# code to be added to the method is as follows:
// Paste the Embedded SQL into this function
string sql = @"SELECT TOP 1 fcategory,funit_price,fsku,fdescription
FROM t_products
WHERE fproname = @name";
// Returns the retrieved product using the embedded sql
return _context.SqlExecutor.SelectOne<D_Product>(sql, name);
Note: it reuses the model D_Product here to return a row of product info.
Ported code in the Controller class
Update the SelectByName(string name) method in the ProductController class with the following code:
/// <summary>
/// Retrieves the Product by Name.
/// </summary>
/// <returns>The requested Product.</returns>
/// <param name="name">Name.</param>
/// GET api/product/SelectByName
[HttpGet]
[ProducesResponseType(StatusCodes.Status200OK)]
[ProducesResponseType(StatusCodes.Status404NotFound)]
public ActionResult<D_Product> SelectByName(string name)
{
// Declare a local variable and initialize it by calling the service
var product = _service.SelectByName(name);
// Check if anything has been returned
if (product == null)
{
// Return a Status Code 404 (Not Found)
return NotFound("No rows retrieved.");
}
else
{
// Return Status Code 200 with the Product data
return Ok(product);
}
}
Call the Web API from PowerBuilder
Now that the code has been migrated to the Web API, the final step to complete the migration is to call this RESTful Web APIs from PowerBuilder.
The demo application used in this tutorial includes a Non-Visual Object named n_RESTClient. This object has been developed with some sample functions to facilitate and standardize the RESTful Web APIs calls.
Convert your DataWindows into JSON DataWindows
Since the application will no longer use a database connection to perform the CRUD functionalities, all the related DW functions have been commented and a call to the RESTClient functions will be used instead.
It is recommended to use the RESTClient and the JSONPackage objects as much as possible. The RESTClient object is optimized for RESTful Web API calls and the JSONPackage object enables you to package several DataWindows into one object. You can then un-package this object in your Web API and boost performance.
The following example demonstrates how this has been accomplished:
Call the Web API in NVO_Customer
Change the PowerScript code to use the RESTClient object to call the RESTful Web API in NVO_Customer.
Modify of_Retrieve_Customer_Select()
Original PowerScript code:
Int li_return
adw_customers.SetTransObject(SQLCA)
li_return = adw_customers.Retrieve()
Return li_return
Modified code:
String ls_url
Int li_return
ls_url = gn_RESTClient.of_get_url("customer", "RetrieveCustomerSelect")
li_return = gn_RESTClient.Retrieve(adw_customers, ls_url)
// The logic to validate if the data retrieval was successful, is not completely provided.
// Add you own validation code if needed.
// ...
Return li_return
Modify of_Retrieve_Cust_List()
Original PowerScript Code:
Int li_return
adw_customers.SetTransObject(SQLCA)
li_return = adw_customers.Retrieve()
Return li_return
Modified code:
String ls_url
Int li_return
ls_url = gn_RESTClient.of_get_url("customer", "RetrieveCustList")
li_return = gn_RESTClient.Retrieve(adw_customers, ls_url)
// The logic to validate if the data retrieval was successful, is not completely provided.
// Add you own validation code if needed.
// ...
Return li_return
Modify of_Check_CustNo()
Original PowerScript code:
Int li_count
SELECT Count(*) Into :li_Count From t_customers Where fcustno = :as_custno;
If li_Count = 1 Then
Return True
Else
Return False
End If
Modified code:
String ls_url
String ls_response
Int i
ls_url = gn_RESTClient.of_get_url("customer", "CheckCustNo") + "?custNo=" + as_custno
i = gn_RESTClient.SendGetRequest(ls_url, ls_response)
If i = 1 Then
// CustNo exists
If ls_response = '1' Then
Return True
End If
Else
// Process errors
End If
Return False
Call the Web API in NVO_Order
Change the PowerScript code to use the RESTClient object to call the RESTful Web API in NVO_Order.
Modify of_Retrieve_Order_List()
Original PowerScript code:
Int li_return
adw_orders.SetTransObject(SQLCA);
li_return = adw_orders.Retrieve(as_custno)
Return li_return
Modified code:
String ls_url
Int li_return
ls_url = gn_RESTClient.of_get_url("order", "RetrieveOrderList") + "?custNo=" + as_custno
li_return = gn_RESTClient.Retrieve(adw_orders, ls_url)
// The logic to validate if the data retrieval was successful, is not completely provided.
// Add you own validation code if needed.
// ...
Return li_return
Modify of_Retrieve_Order_Modify()
Original PowerScript code:
Int li_return
adw_order.SetTransObject(SQLCA);
li_return = adw_order.Retrieve(as_orderno)
Return li_return
Modified code:
String ls_url
Int li_return
ls_url = gn_RESTClient.of_get_url("order", "RetrieveOrderModify") + "?orderNo=" + as_orderno
li_return = gn_RESTClient.Retrieve(adw_order, ls_url)
// The logic to validate if the data retrieval was successful, is not completely provided.
// Add you own validation code if needed.
// ...
Return li_return
Modify of_Retrieve_OrderItem_Edit()
Original PowerScript code:
Int li_return
adw_orderItems.SetTransObject(SQLCA);
li_return = adw_orderItems.Retrieve(as_custno,as_orderno)
Return li_return
Modified code:
String ls_url
Int li_return
ls_url = gn_RESTClient.of_get_url("order", "RetrieveOrderItemEdit") + "?custNo=" + as_custno + "&orderNo=" + as_orderno
// Send request, then import response data (DataWindow JSON format) into a DataWindow and its child DataWindows.
li_return = gn_RESTClient.of_retrieve_standard(adw_orderItems, ls_url)
// The logic to validate if the data retrieval was successful, is not completely provided.
// Add you own validation code if needed.
// ...
Return li_return
Modify of_Retrieve_OrderItem_List()
Original PowerScrip code:
Int li_return
adw_orderitems.SetTransObject(SQLCA);
li_return = adw_orderitems.Retrieve(as_custno, as_orderno)
Return li_return
Modified code:
String ls_url
Int li_return
ls_url = gn_RESTClient.of_get_url("order", "RetrieveOrderItemList") + "?custNo=" + as_custno + "&orderNo=" + as_orderno
// Send request, then import response data (DataWindow JSON format) into a DataWindow and its child DataWindows.
li_return = gn_RESTClient.of_retrieve_standard(adw_orderitems, ls_url)
// The logic to validate if the data retrieval was successful, is not completely provided.
// Add you own validation code if needed.
// ...
Return li_return
Modify of_Add()
Original PowerScript code:
adw_order.SetTransObject(SQLCA)
adw_orderitems.SetTransObject(SQLCA)
If adw_order.UPDATE() = 1 Then
If adw_orderitems.UPDATE() = 1 Then
Return 1
Else
ROLLBACK;
MessageBox("Warning","Failed to save the changes.", exclamation!)
Return -1
End If
Else
ROLLBACK;
MessageBox("Warning","Failed to save the changes.", exclamation!)
Return -1
End If
Modified code:
String ls_url
String ls_response
JsonPackage lnv_package
Int li_return
lnv_package = Create JsonPackage
lnv_package.SetValueByDataWindow("order", adw_order, true)
lnv_package.SetValueByDataWindow("orderitems", adw_orderitems, true)
ls_url = gn_RESTClient.of_get_url("order", "Add")
li_return = gn_RESTClient.SendPostRequest(ls_url, lnv_package.GetJsonString(), ls_response)
// Success
If li_return = 1 Then
// OK 200
If gn_RESTClient.GetResponseStatusCode() = 200 Then
adw_order.ResetUpdate()
adw_orderitems.ResetUpdate()
Return 1
Else
MessageBox("Warning","Failed to save the changes.", exclamation!)
Return -1
End If
Else
// Process errors
// ...
End If
Return -1
Modify of_Delete()
Original PowerScript code:
// Delete order item details
DELETE FROM t_orders_items WHERE forderno = :as_orderno;
// Delete order
DELETE FROM t_orders WHERE forderno = :as_orderno;
Return 1
Modified code:
String ls_url
String ls_response
Int li_return
ls_url = gn_RESTClient.of_get_url("order", "Delete") + as_orderno
li_return = gn_RESTClient.SendDeleteRequest(ls_url, ls_response)
// Success
If li_return = 1 Then
// OK 200
If gn_RESTClient.GetResponseStatusCode() = 200 Then
Return 1
Else
Return -1
End If
Else
// Add your own logic to validate if the deletion was successful
// ...
End If
Return -1
Modify of_Get_LineId_Max()
Original PowerScript code:
Int li_LineID
SELECT isnull(max(flineid),0)
INTO :li_LineID
FROM t_orders_items
WHERE forderNo = :is_OrderNo;
If IsNull(li_LineID) Or li_LineID < 1 Then
li_LineID = 0
End If
Return li_LineID
Modified code:
String ls_url
String ls_response
Int li_LineID
Int li_return
ls_url = gn_RESTClient.of_get_url("order", "GetLineIdMax") + "?orderNo=" + as_orderno
li_return = gn_RESTClient.SendGetRequest(ls_url, ls_response)
If li_return = 1 and gn_RESTClient.GetResponseStatusCode() = 200 Then
li_LineID = Integer(ls_response)
Return li_LineID
Else
// Process errors
Return -1
End If
Modify of_Get_OrderNo()
Original PowerScript code:
String ls_OrderNo
Int li_Max
SELECT max(convert(integer,right(forderNo,2)))+1
INTO :li_Max
FROM t_orders
WHERE fcustNo = :as_custNo;
If isnull(li_Max) Or li_Max <=0 Then li_Max = 1
ls_OrderNo = string(li_Max,'00')
ls_orderNo = as_custNo + ls_orderNo
Return ls_OrderNo
Modified code:
String ls_url
String ls_response
String ls_OrderNo
Int li_return
ls_url = gn_RESTClient.of_get_url("order", "GetOrderNo") + "?custNo=" + as_custno
li_return = gn_RESTClient.SendGetRequest(ls_url, ls_response)
If li_return = 1 Then
ls_OrderNo = ls_response
Return ls_OrderNo
Else
// Process errors
// ...
Return "ERROR"
End If
Modify of_Modify()
Original PowerScript code:
adw_order.SetTransObject(SQLCA)
adw_orderitems.SetTransObject(SQLCA)
If adw_order.UPDATE() = 1 Then
If adw_orderitems.UPDATE() = 1 Then
COMMIT;
Return 1
Else
ROLLBACK;
MessageBox("Warning","Failed to save the changes.", exclamation!)
Return -1
End If
Else
ROLLBACK;
MessageBox("Warning","Failed to save the changes.", exclamation!)
Return -1
End If
Modified code:
String ls_url
String ls_response
JsonPackage lnv_package
Int li_return
lnv_package = Create JsonPackage
lnv_package.SetValueByDataWindow("order", adw_order, true)
lnv_package.SetValueByDataWindow("orderitems", adw_orderitems, true)
ls_url = gn_RESTClient.of_get_url("order", "Modify")
li_return = gn_RESTClient.SendPostRequest(ls_url, lnv_package.GetJsonString(), ls_response)
If li_return = 1 Then
If gn_RESTClient.GetResponseStatusCode() = 200 Then
adw_order.ResetUpdate()
adw_orderitems.ResetUpdate()
Return 1
Else
MessageBox("Warning","Failed to save the changes.",exclamation!)
Return -1
End If
Else
// Process errors
End If
Return -1
Call the Web API in NVO_Product
Change the PowerScript code to use the RESTClient object to call the RESTful Web API in NVO_Product.
Modify of_Retrieve_DDDW_Category()
Original PowerScript code:
Int li_return
adwc_categories.SetTransObject(SQLCA)
li_return = adwc_categories.Retrieve()
Return li_return
Modified code:
String ls_url
Int li_return
ls_url = gn_RESTClient.of_get_url("product", "RetrieveDddwCategory")
li_return = gn_RESTClient.Retrieve(adwc_categories, ls_url)
// Add your logic to validate if the data retrieval was successful
// ...
Return li_return
Modify of_Retrieve_DDDW_Products()
Original PowerScript code:
Int li_return
adwc_products.SetTransObject(SQLCA)
li_return = adwc_products.Retrieve()
Return li_return
Modified code:
String ls_url
Int li_return
ls_url = gn_RESTClient.of_get_url("product", "RetrieveDddwProducts")
li_return = gn_RESTClient.Retrieve(adwc_products, ls_url)
// Add your logic to validate if the data retrieval was successful
// ...
Return li_return
Modify of_Select_By_Name()
Original PowerScript code:
String ls_category
String ls_Sku
String ls_description
Dec ldec_unitprice
SELECT Top 1 fcategory, funit_price, fsku, fdescription
INTO :ls_category, :ldec_unitprice, :ls_Sku, :ls_description
FROM t_products
WHERE fproname = :name;
If SQLCA.SQLCode <> 0 Then
MessageBox("Alert","The Product Name does not exist!")
Return 1
End If
adw_orderitems.Object.fcategory[row] = ls_category
adw_orderitems.Object.funit_price[row] = ldec_unitprice
adw_orderitems.Object.fdescription[row] = ls_description
adw_orderitems.Object.fsku[row] = ls_sku
adw_orderitems.Object.famount[row] = ldec_unitprice*adw_orderitems.Object.fquantity[row]
Modified code:
String ls_url
String ls_response
Int li_return
Long ll_RootObject
Dec ldec_unitprice
JsonParser lnv_JsonParser
ls_url = gn_RESTClient.of_get_url("product", "SelectByName") + "?name=" + name
li_return = gn_RESTClient.SendGetRequest(ls_url, ls_response)
If li_return = 1 Then
If gn_RESTClient.GetResponseStatusCode() = 200 Then
// Parse Json
lnv_JsonParser = Create JsonParser
lnv_JsonParser.LoadString(ls_response)
ll_RootObject = lnv_JsonParser.GetRootItem()
adw_orderitems.Object.fcategory[row] = lnv_JsonParser.GetItemString(ll_RootObject, "fcategory")
ldec_unitprice = lnv_JsonParser.GetItemNumber(ll_RootObject, "funit_price")
adw_orderitems.Object.funit_price[row] = ldec_unitprice
adw_orderitems.Object.fdescription[row] = lnv_JsonParser.GetItemString(ll_RootObject, "fdescription")
adw_orderitems.Object.fsku[row] = lnv_JsonParser.GetItemString(ll_RootObject, "fsku")
adw_orderitems.Object.famount[row] = ldec_unitprice * adw_orderitems.Object.fquantity[row]
Destroy lnv_JsonParser
Else
Return -1
End If
Else
// Add your logic to validate if the deletion was successful
// ...
End If
Return li_return
Modify of_Select_By_Sku()
Original PowerScript code:
String ls_category
String ls_ProdName
String ls_description
Dec ldec_unitprice
SELECT Top 1 fcategory, fproname, funit_price, fdescription
INTO :ls_category, :ls_ProdName, :ldec_unitprice, :ls_description
FROM t_products
WHERE fsku = :sku;
If SQLCA.SQLCode <> 0 Then
MessageBox("Alert","The SKU does not exist.")
Return 1
End If
adw_orderitems.Object.fcategory[row] = ls_category
adw_orderitems.Object.fproname[row] = ls_ProdName
adw_orderitems.Object.funit_price[row] = ldec_unitprice
adw_orderitems.Object.fdescription[row] = ls_description
adw_orderitems.Object.famount[row] = ldec_unitprice*adw_orderitems.Object.fquantity[row]
Modified code:
String ls_url
String ls_response
Int li_return
Long ll_RootObject
Dec ldec_unitprice
JsonParser lnv_JsonParser
ls_url = gn_RESTClient.of_get_url("product", "SelectBySku") + "?sku=" + sku
li_return = gn_RESTClient.SendGetRequest(ls_url, ls_response)
If li_return = 1 Then
If gn_RESTClient.GetResponseStatusCode() = 200 Then
// Parse Json
lnv_JsonParser = Create JsonParser
lnv_JsonParser.LoadString(ls_response)
ll_RootObject = lnv_JsonParser.GetRootItem()
adw_orderitems.Object.fcategory[row] = lnv_JsonParser.GetItemString(ll_RootObject, "fcategory")
adw_orderitems.Object.fproname[row] = lnv_JsonParser.GetItemString(ll_RootObject, "fproname")
ldec_unitprice = lnv_JsonParser.GetItemNumber(ll_RootObject, "funit_price")
adw_orderitems.Object.funit_price[row] = ldec_unitprice
adw_orderitems.Object.fdescription[row] = lnv_JsonParser.GetItemString(ll_RootObject, "fdescription")
adw_orderitems.Object.famount[row] = ldec_unitprice * adw_orderitems.Object.fquantity[row]
Destroy lnv_JsonParser
Else
Return -1
End If
Else
// Add your logic to validate if the deletion was successful
// ...
End If
Return li_return
Test the Migrated PB application and RESTful Web API
Run the RESTful Web API
Go to SnapDevelop and press Ctrl+F5 to run the Web API. SnapDevelop launches a browser and navigates to http://localhost:5000/api/sample.
If it's the first time to run, you may need to wait several seconds for initiating .NET runtime after the browser is launched.
Run the PB application
Go to PowerBuilder and press Ctrl+R to run the app. PowerBuilder will now start the application and all the calls to the RESTful Web API will be performed.