PB to C#: Porting Business Logics with Minimum Refactoring Hassle

Last Updated: July 2021

Introduction

A common way to start implementing business logics using Web API services is to refactor the PowerBuilder project and encapsulate the business logic into NVOs.

The daunting task to refactor a PowerBuilder project gives you these challenges:

  • The project developers need to have a good understanding of the business logic.
  • The project code shall stay unchanged during the refactoring process, otherwise, the refactoring result will be inconsistent with the original program logics.
  • The more complex your project, the more complex the refactoring is.

This article provides you a new porting solution that helps you minimize your refactoring efforts and risks. The solution guides you to create a PB NVO for every PB object (such as a window) that contains some business logic code to be ported, and define a function in the PB NVO for every event or function (of the PB object) that contains some business logic code to be ported. The benefits of this porting solution include:

  • It is easier to quantify the migration project.

  • It can reduce the difficulty for developers to analyze code during the migration process.

    Developers only need to pay attention to the code in one event or function at a time, and the possibility of error in porting process is also reduced.

  • Ensure that the separated business logic is reusable.

    In general, events and functions in PB have certain business logic independence. If the business logic is released to the Web API, even if the client is replaced later, the business logic of the server can be reused in a better way.

  • The Web API actions also correspond to the PB function/event one-to-one, which is relatively easy to maintain.

This tutorial will teach you, through a demo application, the procedure of porting PowerBuilder business logics to C# in 4 steps:

The Port PowerBuilder business logics section in this tutorial focuses on the business scenarios in the demo application, the most common ones. If you need guidance on handling more complete business scenarios, you may refer to the other tutorial: PB to C#: Migrating Re-factored Business Logics (NVOs).

Prerequisites

  • PowerBuilder 2021 and SnapDevelop 2021

  • Sample database setup

    1. Download the database backup file from here.
    2. Install SQL Server Express or SQL Server if it is not installed.
    3. Restore database using the downloaded database backup file.

  • The OrderDemo applications are downloadable from here. There are 3 subfolders in it:

    Subfolder Name Content
    Appeon.OrderDemo_1_Original (Optional) The starting PB OrderDemo application
    Appeon.OrderDemo_2_NVOsCreated The PB OrderDemo application with newly created NVOs (after separating the PB business logic from the UI)
    Appeon.OrderDemo_3_Finished The finished PB OrderDemo application and the finished C# Web API project

Separate PB business logics from UI

In many PB projects, the business logic code is inter-mixed with the code that deals with UI logic. Since we must keep the UI logic in the PB while migrating the business logic to the Web API, the step of separating the business logic from the UI logic is essential.

We recommend you take the following approach to separate the PB business logic from the UI logic:

  1. Create a PB NVO for every PB object (such as a window) that contains some business logic code to be ported;
  2. Define a function in the NVO for every event or function (of the PB object) that contains some business logic code to be ported;
  3. Port the business logic code from the event or function to the mapping NVO function.

Example

Let's analyze the code in w_order_main.ue_deleteorder() to see how to separate PB user logic and UI logic.

In this event both, the business-related code and the UI-related code, coexist. The two lines (between //BUSINESS LOGIC REGION START and //BUSINESS LOGIC REGION END) of embedded SQL that perform the delete order and order details are business logic.

Int	li_Ans
Long	ll_i,ll_row
String  ls_CustNo
String ls_OrderNo

ll_Row = dw_order.GetRow()
If ll_Row < 1  Then Return

ls_CustNo = dw_order.GetItemString(ll_Row,'FCustNo')
ls_OrderNo = dw_order.GetItemString(ll_Row,'FOrderNo')
If ls_CustNo = '' Or IsNull(ls_CustNo) Or ls_OrderNo = '' Or IsNull(ls_OrderNo) Then Return

li_Ans = MessageBox("Delete","Are you sure you want to delete the order?" ,&
							Question!,YesNo!)
If li_Ans <> 1 Then Return

//// BUSINESS LOGIC REGION START 
                    
// Delete order item detail
DELETE From t_orders_items Where forderno = :is_orderNo;                    
// Delete order information
DELETE From t_orders Where forderno = :is_orderNo;  
                    
//// BUSINESS LOGIC REGION END                     

// Reset
dw_orderitem.Reset()
dw_order.DeleteRow(0)
dw_order.ResetUpdate()

dw_order.Event RowFocusChanged(dw_order.GetRow())

We will create a new Custom Class (NVO) named nw_order_main for the object w_order_main, and use it to store the business logic for w_order_main (please check the NVO's AutoInstantiate property).

Then we will define a function called nw_order_main.of_ue_deleteorder() to hold the business logic from w_order_main.ue_deleteorder().

Now, move the business logic code from w_order_main.ue_deleteorder() to nw_order_main.of_ue_deleteorder().

public function integer of_ue_deleteorder (string as_orderno);

// Delete order item detail
DELETE From t_orders_items Where forderno = :as_orderNo;
// Delete order information
DELETE From t_orders Where forderno = :as_orderNo;

Return 1
end function

After that, add an instance variable: nw_order_main inv_order_main to the w_order_main window.

Modify the code in w_order_main.ue_deleteorder(), comment out the original business logic code block, and call the nw_order_main.of_ue_deleteorder() function. The other UI related code is not affected.

//// BUSINESS LOGIC REGION START 
                    
// Delete customer order

// Orignal Code
//// Delete order item detail
//DELETE From t_orders_items Where forderno = :is_orderNo;
//// Delete order information
//DELETE From t_orders Where forderno = :is_orderNo;

// Modified Code
inv_order_main.of_ue_deleteorder(is_orderNo)

//// BUSINESS LOGIC REGION END

Key Tips

  • Define how much business logic you want to migrate to the Web API.

    You'd better specify at the very beginning the scope of business logics you plan to migrate. Some objects and functions have in-depth combination of UI and business logics. Taking DataWindow as an example. The DataWindow related code usually combines UI and business logic. When you see a method like DWControl.SetItem(), it can be called for the UI display as well as for data storage.

    If you prefer to migrate less business logic to Web API, consider specifying the standard to try not to migrate code like DWControl.SetItem().

    If you prefer to migrate more complete business logic to the Web API, you can also migrate code like DWControl.SetItem to a Web API project. Often, the Web API will also need to return more data to the client.

  • Create PB NVOs that map to the original PB object with a one-to-one relationship, and define NVO functions that map to the events/methods in the original PB objects with a one-to-one relationship.

    There may be exceptions, though. For example, if there are many types of input and output for a method or event, you may flexibly define more functions in the NVO handling the situation, thus forming a one-to-many relationship.

    Also, remember to specify the NVO and NVO function naming rules at the beginning with easy readability and maintenance convenience in mind.

  • Pay attention to the scope of the transaction in the code. When several methods/events are in the same transaction scope, complete business logic and transaction management must be implemented in one NVO function. Because the NVO function will correspond to a stateless request with the Web API, that request is isolated from other requests.

    For example: The code in the A function calls the B function, and you need to ensure that A and B are executed within the same transaction scope to ensure transaction integrity. Then when splitting A's code into an NVO function, you must also implement a call to B in the NVO function to ensure transaction integrity.

  • Try to keep your business logic as lean and independent as possible. When splitting an event/method in a window, you can skip the line of other events/functions called in the code if the line is not under transaction management.

    For example, if some code in the A function calls the B function and has nothing to do with transactional integrity, when you are splitting the business logic related code of function A into an NVO function, you can skip the code that calls the B function.

Find More Examples in the Demo App

If you download and open the demo app Appeon.OrderDemo_2_NVOsCreated mentioned in the Prerequisites section, you will see more examples of porting the business logics from PB objects into the corresponding NVOs. The following sections are based on the demo app and assume you have already separated the business logics from the UI and ported the logic to NVOs.

Create a Web API project

Create an ASP.NET Core 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 from the list of project templates, select ASP.NET Core Web API. Name the project "OrderDemo", name the solution "Appeon.OrderDemo" and click OK.

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.

  • Fill in the database connection information and click OK.

  • In the Database Connection dialog box, the connection string is automatically generated. 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.

To register the database context, you

Add the following using statements to the Startup.cs file:

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 four Service classes (corresponding to the four NVOs), and initialize these Service classes with an interface and initial code. You will update the implementation of these Service classes with the corresponding code from the ported business logic in the next section Porting the PowerBuilder business logics.

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.

You will need to add the Interface classes first and then add the Service classes.

To add the Interface classes, 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 IWCustomerSelectService.cs and click OK.

  • Then add the IWOrderMainService.cs, IWOrderModifyService.cs and IWOrderNewService.cs interfaces in the same way.

Now the IWCustomerSelectService, IWOrderMainService, IWOrderModifyService, and IWOrderNewService interfaces are created.

These interfaces correspond to the PB NVOs one-to-one, and methods declared in the interface correspond to the PB NVO's functions one-to-one.

In the IWCustomerSelectService interface, add the declaration of the IDataStore Open() method 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 DWNet.Data;

namespace OrderDemo.Services
{
    public interface IWCustomerSelectService
    {
        IDataStore Open();
    }
}

Add the declaration of the following methods to the IWOrderMainService interface:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using DWNet.Data;

namespace OrderDemo.Services
{
    public interface IWOrderMainService
    {
        IDataStore DwOrderItemUeRetrieve(string custNo, string orderNo);

        IDataStore DwOrderUeRetrieve(string custNo);

        void UeDeleteOrder(string orderNo);

        IDataStore UePostOpen();
    }
}

Add the declaration of the following methods to the IWOrderModifyService 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 DWNet.Data;
using SnapObjects.Data;
using OrderDemo.Models;

namespace OrderDemo.Services
{
    public interface IWOrderModifyService
    {
        short DwOrderItemItemChanged(string columnName, string data, out bool hasProduct, out D_Product product);
        
        void Open(string custNo, string orderNo, out IDataStore order, out IDataStore orderItems);

        bool UeSave(IDataStore order, IDataStore orderItems);

        short WfGetMaxLineId(string orderNo);
    }
}

Add the declaration of the following methods to the IWOrderNewService interface.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using DWNet.Data;
using SnapObjects.Data;
using OrderDemo.Models;

namespace OrderDemo.Services
{
    public interface IWOrderNewService
    {
        short DwOrderItemChanged(string columnName, string data, out bool hasCustomer, out string orderNo);

        short DwOrderItemItemChanged(string columnName, string data, out bool hasProduct, out D_Product product);

        void Open(out IDataStore products, out IDataStore categories);

        bool UeSave(IDataStore order, IDataStore orderItems);

        string WfGetOrderNo(string custNo);
    }
}

To add the Service classes, you

  • Right-click the Service > Impl sub-folder and select Add > Class.

  • Name it WCustomerSelectService.cs and click OK.

    (Notice the difference between the name of the interface "IWCustomerSelectService.cs" and the name of the service "WCustomerSelectService.cs").

  • Then add WOrderMainService.cs, WOrderModifyService.cs and WOrderNewService.cs in the same way.

Now the WCustomerSelectService, WOrderMainService, WOrderModifyService, and WOrderNewService classes are created accordingly.

Add the initial code to the WCustomerSelectService class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using DWNet.Data;

namespace OrderDemo.Services.Impl
{
    public class WCustomerSelectService : IWCustomerSelectService
    {   
        // The DataContext local variable. (Similar to PB's SQLCA)
        private readonly AppeonDataContext _context;

        // Initializes a new instance of the WCustomerSelect class.
        public WCustomerSelectService(AppeonDataContext Context)
        {
            // Initialize the DataContext variable
            _context = Context;
        }
        
        public IDataStore Open()
        {
            // To be updated with the code from ported business logic
            throw new NotImplementedException();
        }
    }
}

Add the initial code to the WOrderMainService class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using DWNet.Data;

namespace OrderDemo.Services.Impl
{
    public class WOrderMainService : IWOrderMainService
    {
        // The DataContext local variable. (Similar to PB's SQLCA)
        private readonly AppeonDataContext _context;

        // Initializes a new instance of the WOrderMainService class.
        public WOrderMainService(AppeonDataContext Context)
        {
            // Initialize the DataContext variable
            _context = Context;
        }

        public IDataStore DwOrderItemUeRetrieve(string custNo, string orderNo)
        {
            throw new NotImplementedException();
        }

        public IDataStore DwOrderUeRetrieve(string custNo)
        {
            throw new NotImplementedException();
        }

        public void UeDeleteOrder(string orderNo)
        {
            throw new NotImplementedException();
        }

        public IDataStore UePostOpen()
        {
            throw new NotImplementedException();
        }
    }
}

Add the initial code to the WOrderModifyService class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using DWNet.Data;
using SnapObjects.Data;
using OrderDemo.Models;

namespace OrderDemo.Services.Impl
{
    public class WOrderModifyService : IWOrderModifyService
    {
        // The DataContext local variable. (Similar to PB's SQLCA)
        private readonly AppeonDataContext _context;


        // Initializes a new instance of the WOrderModifyService class.
        public WOrderModifyService(AppeonDataContext Context)
        {
            // Initialize the DataContext variable
            _context = Context;
        }

        public short DwOrderItemItemChanged(string columnName, string data, out bool hasProduct, out D_Product product)
        {
            throw new NotImplementedException();
        }

        public void Open(string custNo, string orderNo, out IDataStore order, out IDataStore orderItems)
        {
            throw new NotImplementedException();
        }

        public bool UeSave(IDataStore order, IDataStore orderItems)
        {
            throw new NotImplementedException();
        }

        public short WfGetMaxLineId(string orderNo)
        {
            throw new NotImplementedException();
        }
    }
}

Add the initial code to the WOrderNewService class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using DWNet.Data;
using SnapObjects.Data;
using OrderDemo.Models;

namespace OrderDemo.Services.Impl
{
    public class WOrderNewService : IWOrderNewService
    {
        // The DataContext local variable. (Similar to PB's SQLCA)
        private readonly AppeonDataContext _context;

        // Initializes a new instance of the WOrderNewService class.
        public WOrderNewService(AppeonDataContext Context)
        {
            // Initialize the DataContext variable
            _context = Context;
        }

        public short DwOrderItemChanged(string columnName, string data, out bool hasCustomer, out string orderNo)
        {
            throw new NotImplementedException();
        }

        public short DwOrderItemItemChanged(string columnName, string data, out bool hasProduct, out D_Product product)
        {
            
            throw new NotImplementedException();
        }

        public void Open(out IDataStore products, out IDataStore categories)
        {
            throw new NotImplementedException();
        }

        public bool UeSave(IDataStore order, IDataStore orderItems)
        {
            throw new NotImplementedException();
        }

        public string WfGetOrderNo(string custNo)
        {
            throw new NotImplementedException();
        }
    }
}

In ASP.NET Core, services such as the IWCustomerSelectService must be registered with the dependency injection (DI) container. The container provides the service to controllers.

To register the service, you

Add the following using statements to the Startup.cs file:

// Add the following using statements
using OrderDemo.Services;
using OrderDemo.Services.Impl;

and add the following code to register the service in the ConfigureServices method:

// The service needs to be registered in the ConfigureServices method of the Startup class. Sample code as follows:
// services.AddScoped<ServiceInterfaceName, ServiceClassName>();
services.AddScoped<IWCustomerSelectService, WCustomerSelectService>();
services.AddScoped<IWOrderMainService, WOrderMainService>();
services.AddScoped<IWOrderModifyService, WOrderModifyService>();
services.AddScoped<IWOrderNewService, WOrderNewService>();

Add a Controller

In this section, you create four Controller classes (corresponding to the four Service classes and NVOs), and initialize these Controller classes with the initial code. You will update the implementation of these Controller classes with the corresponding code from the ported business logic in the next section Porting the PowerBuilder business logics.

Why adding the Controller class

Since the .NET DataStore and PowerBuilder's DataStore are essentially the same, you perform CRUD operations virtually the same as in the Web API.

The Web API requires one or more component 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 the Controllers folder, then selecting Add -> New Item -> API Controller Class.

Add four new controllers and name them WCustomerSelectController.cs, WOrderMainController.cs, WOrderModifyController.cs and WOrderNewController.cs accordingly.

Add the initial code to the WCustomerSelectController class:

using System;
using System.Text;
using System.Collections;
using System.Collections.Generic;
using Microsoft.AspNetCore.Mvc;
using DWNet.Data;
using OrderDemo.Services;
using Microsoft.AspNetCore.Http;

namespace OrderDemo.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class WCustomerSelectController : ControllerBase
    {
        private readonly IWCustomerSelectService _service;

        public WCustomerSelectController(IWCustomerSelectService service)
        {
            _service = service;
        }

        // Update this method with the corresponding code to perform the action
        [HttpGet]
        public ActionResult<IDataStore> Open()
        {
            throw new NotImplementedException();
        }
    }
}

Add the initial code to the WOrderMainController class:

using System;
using System.Text;
using System.Collections;
using System.Collections.Generic;
using Microsoft.AspNetCore.Mvc;
using DWNet.Data;
using OrderDemo.Services;
using Microsoft.AspNetCore.Http;
using SnapObjects.Data;

namespace OrderDemo.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class WOrderMainController : ControllerBase
    {
        private readonly IWOrderMainService _service;

        public WOrderMainController(IWOrderMainService service)
        {
            _service = service;
        }

        // GET api/WOrderMain/DwOrderItemUeRetrieve
        [HttpGet]
        public ActionResult<string> DwOrderItemUeRetrieve(string custNo, string orderNo)
        {
            throw new NotImplementedException();
        }

        // GET api/WOrderMain/DwOrderUeRetrieve
        [HttpGet]
        public ActionResult<IDataStore> DwOrderUeRetrieve(string custNo)
        {
            throw new NotImplementedException();
        }

        /// DELETE api/WOrderMain/UeDeleteOrder/{orderNo}
        [HttpDelete("{orderNo}")]
        public ActionResult UeDeleteOrder(string orderNo)
        {
            throw new NotImplementedException();
        }

        // GET api/WOrderMain/UePostOpen
        [HttpGet]
        public ActionResult<IDataStore> UePostOpen()
        {
            throw new NotImplementedException();
        }
    }
}

Add the initial code to the WOrderModifyController class:

using System;
using System.Text;
using System.Collections;
using System.Collections.Generic;
using Microsoft.AspNetCore.Mvc;
using DWNet.Data;
using OrderDemo.Services;
using Microsoft.AspNetCore.Http;
using SnapObjects.Data;

namespace OrderDemo.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class WOrderModifyController : ControllerBase
    {
        // Declare the local variable for the Service.
        private readonly IWOrderModifyService _service;

        // Constructor.
        public WOrderModifyController(IWOrderModifyService service)
        {
            // Initialize the Service variable
            _service = service;
        }

        // POST api/WOrderNew/DwOrderItemItemChanged
        [HttpPost]
        public ActionResult<IDataPacker> DwOrderItemItemChanged(IDataUnpacker dataUnpacker)
        {
            throw new NotImplementedException();
        }
        
        // GET api/WOrderModify/Open
        [HttpGet]
        public ActionResult<IDataPacker> Open(string custNo, string orderNo)
        {
            throw new NotImplementedException();
        }

        // POST api/WOrderModify/UeSave
        [HttpPost]
        public ActionResult UeSave(IDataUnpacker dataUnpacker)
        {
            throw new NotImplementedException();
        }

        // GET api/WOrderModify/WfGetMaxLineIds
        [HttpGet]
        public ActionResult<short> WfGetMaxLineId(string orderNo)
        {
            throw new NotImplementedException();
        }
    }
}

Add the initial code to the WOrderNewController class:

using System;
using System.Text;
using System.Collections;
using System.Collections.Generic;
using Microsoft.AspNetCore.Mvc;
using DWNet.Data;
using OrderDemo.Services;
using Microsoft.AspNetCore.Http;
using SnapObjects.Data;

namespace OrderDemo.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class WOrderNewController : ControllerBase
    {
        // Declare the local variable for the Service.
        private readonly IWOrderNewService _service;

        // Constructor.
        public WOrderNewController(IWOrderNewService service)
        {
            // Initialize the Service variable
            _service = service;
        }

        // POST api/WOrderNew/DwOrderItemChanged
        [HttpPost]
        public ActionResult<IDataPacker> DwOrderItemChanged(IDataUnpacker dataUnpacker)
        {
            throw new NotImplementedException();
        }

        // POST api/WOrderNew/DwOrderItemItemChanged
        [HttpPost]
        public ActionResult<IDataPacker> DwOrderItemItemChanged(IDataUnpacker dataUnpacker)
        {
            throw new NotImplementedException();
        }

        // GET api/WOrderNew/Open
        [HttpGet]
        public ActionResult<IDataPacker> Open()
        {
            throw new NotImplementedException();
        }

        // POST api/WOrderNew/UeSave
        [HttpPost]
        public ActionResult UeSave(IDataUnpacker dataUnpacker)
        {
            throw new NotImplementedException();
        }

        /// GET api/WOrderNew/WfGetOrderNo
        [HttpGet]
        public ActionResult<string> WfGetOrderNo(string custNo)
        {
            throw new NotImplementedException();
        }
    }
}

Port PowerBuilder business logics

In this section, you will learn how to port/migrate PowerBuilder data objects and NVOs into the Web API project that you have created in the previous section.

Follow this 2-step process:

Step 1: Migrate PowerBuilder data objects to C# Data Models

Step 2: Port NVO functions to Services and Controllers

Migrate PowerBuilder data objects to C# Data Models

You can automatically migrate the PowerScript data objects to C# data models using the DataWindow Converter plug-in. This DataWindow conversion utility also supports batch conversion, automatically migrating hundreds or even thousands of DataWindows and DataStores.

To launch the DataWindow Converter,

  1. Open the PowerBuilder application which contains the DataWindow object in SnapDevelop IDE (right click the solution in the Solution Explorer, select Open PB Workspace).

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

DwExporter

Then, the Database Connection window displays. It automatically uses the database connection that you have created in Add a database context. Click OK.

Select Connection

Then, on the Model Export window, make sure the OrderDemo project is selected, and click Export.

DataWindow Export

When export is completed, a Models folder will be created under the OrderDemo project in Solution Explorer, and under the Models folder you will now find the data models (.cs files) exported!

DataWindows Exported

Porting NVO functions to Services and Controllers

Now that the Web API project contains the Interfaces, Services and Controllers, the next step is to port the PB NVO functions that you have created in Separate PowerBuilder business logics from UI into the Services and Controllers. You can automatically migrate the PB NVO functions to C# using the PowerScript Migrator plug-in.

In real project, you need to plan well and port all the NVO functions in certain good order. In this article we will only show you how to port the NVO functions in typical PB data processing scenarios.

Retrieve/Update DataWindow

We will use .NET DataStore because it is the best migration solution for existing DataWindows. You don't need to re-code each individual DataWindow, and .NET DataStore has virtually the same properties, events and functions as the DataWindow.

Retrieve DataWindow (Plain JSON)

Porting the NVO function nw_customer_select.of_open().

Original PowerScript code
// Set the Transaction Object
adw_cust.SetTransObject(SQLCA)

// Retrieve the DW
Return adw_cust.Retrieve()
Ported code in the Service class

If you have opened the nw_customer_select.sru in SnapDevelop IDE, you can click in the of_Open 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 open() method in the WCustomerSelectService class. In this example, the C# code to be added to the method is as follows:

public IDataStore Open()
{
    // Create the .NET DataStore; set its Dataobject; and set its DataContext
    IDataStore customers = new DataStore("d_customer_select", _context);

    // Retrieve the .NET DataStore
    customers.Retrieve();

    // Return the .NET DataStore
    return customers;
}
Ported code in the Controller class

Update the open() method in WCustomerSelectController class with the following code:

[HttpGet]
[ProducesResponseType(StatusCodes.Status200OK)]
[ProducesResponseType(StatusCodes.Status500InternalServerError)]
public ActionResult<IDataStore> Open()
{
    try
    {
        IDataStore customers = _service.Open();

        // Return Status Code 200 with the Plain JSON
        return Ok(customers);
    }
    catch (Exception e)
    {
        // Return a Status Code of 500 with the Exception Message
        return StatusCode(StatusCodes.Status500InternalServerError, e.Message);
    }
}

Note: theIDataStore object is returned from this action and it will be serialized into plain JSON format by default. 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.

Retrieve DataWindow (DataWindow Standard JSON)

Porting the function nw_order_main.of_dw_orderitem_ue_retrieve().

Original PowerScript code
// Set the Transaction Object
adw_orderitem.SetTransObject(SQLCA);

// Retrieve the DW
Return adw_orderitem.Retrieve(as_custno, as_orderno)
Ported code in the Service class

If you have opened the nw_order_main.sru in SnapDevelop IDE, you can click in the of_dw_orderitem_ue_retrieve 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 DwOrderItemUeRetrieve() method in the WOrderMainService class. In this example, the C# code to be added to the method is as follows:

public IDataStore DwOrderItemUeRetrieve(string custNo, string orderNo)
{
    // Create the .NET DataStore; set its Dataobject; and set its DataContext
    IDataStore orderItems = new DataStore("d_orderitem_list", _context);

    // Retrieve the .NET DataStore
    orderItems.Retrieve(custNo, orderNo);

    // Return the .NET DataStore
    return orderItems;
}
Ported code in the Controller class

Update the DwOrderItemUeRetrieve() method in WOrderMainController class with the following code:

[HttpGet]
[ProducesResponseType(StatusCodes.Status200OK)]
[ProducesResponseType(StatusCodes.Status500InternalServerError)]
public ActionResult<string> DwOrderItemUeRetrieve(string custNo, string orderNo)
{
    try
    {
        IDataStore ds = _service.DwOrderItemUeRetrieve(custNo, orderNo);

        // Export data from the Primary buffer and DDDWs.
        // Return Status Code 200 
        return Ok(ds.ExportJson(true, false, false, true, MappingMethod.Index));
    }
    catch (Exception e)
    {
        // Return a Status Code of 500 with the Exception Message
        return StatusCode(StatusCodes.Status500InternalServerError, e.Message);
    }
}

Note: the IDataStore.ExportJson() method is used 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, using plain JSON instead of DataWindow JSON. You can also consider returning an IDataPacker object to pack data from both the Primary buffer and DDDWs separately.

Update DataWindow

Porting the function nw_order_modify.of_ue_save().

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;
		Return -1
	End If
Else
	ROLLBACK;
	Return -1
End If
Ported code in the Service class

If you have opened the nw_order_modify.sru in SnapDevelop IDE, you can click in the of_ue_save 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 UeSave() method in the WOrderModifyService class. In this example, the C# code to be added to the method is as follows:

public bool UeSave(IDataStore order, IDataStore orderItems)
{
    order.DataContext = _context;
    orderItems.DataContext = _context;

    // Begin the Transaction
    _context.BeginTransaction();

    // Do a try/catch to catch errors
    try
    {
        // Update the Order DataStore
        order.Update();

        // Update the OrderItems DataStore
        orderItems.Update();

        // Commit the transaction
        _context.Commit();

        // Return TRUE for success
        return true;
    }
    catch (Exception)
    {
        // Rollback the transaction
        _context.Rollback();

        // Return FALSE for failure
        return false;
    }
}

Note: .NET DataStore's Update() method will throw an exception instead of returning -1 if an error occurs. So we can use Try Catch block here to rollback the transaction.

Ported code in the Controller class

Update the UeSave() method in WOrderModifyController class with the following code:

[HttpPost]
[ProducesResponseType(StatusCodes.Status200OK)]
[ProducesResponseType(StatusCodes.Status500InternalServerError)]
public ActionResult UeSave(IDataUnpacker dataUnpacker)
{
    // Declare and initialize local .NET DataStore variables
    // By getting the Packaged DataStores
    IDataStore order = dataUnpacker.GetDataStore("order");
    IDataStore orderItems = dataUnpacker.GetDataStore("orderitems");

    // Check if the modification was successful
    if (_service.UeSave(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.

Execute Embedded SQL

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 not only enables you to reuse the embedded SQL from PowerBuilder, but also provides a few more methods that can further empower the code. For example, if I need to obtain the first value from the first column from the result set of a SQL query, instead of using a combination of several methods, I could just use the Scalar method from the SqlExecutor object which obtains the value in one step.

If you'd like to know more about the SqlExecutor object you can refer to the online documentation.

Execute SQL without result set

Porting the function nw_order_main.of_ue_deleteorder().

Original PowerScript code
// 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 nw_order_main.sru in SnapDevelop IDE, you can click in the of_ue_deleteorder 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 UeDeleteOrder() method in the WOrderMainService class. In this example, the C# code to be added to the method is as follows:

public void UeDeleteOrder(string orderNo)
{
    // 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 UeDeleteOrder() method in WOrderMainController class with the following code:

[HttpDelete("{orderNo}")]
[ProducesResponseType(StatusCodes.Status200OK)]
[ProducesResponseType(StatusCodes.Status500InternalServerError)]
public ActionResult UeDeleteOrder(string orderNo)
{
    // Do a Try/Catch for Exception handling
    try
    {
        // Delete the Order and Order Items by calling the service
        _service.UeDeleteOrder(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);
    }
}
Execute SQL and get a value

Porting the function nw_order_new.of_wf_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
Ported code in the Service class

If you have opened the nw_order_new.sru in SnapDevelop IDE, you can click in the of_wf_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 WfGetOrderNo() method in the WOrderNewService class. In this example, the C# code to be added to the method is as follows:

public string WfGetOrderNo(string custNo)
{
    // 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 WfGetOrderNo() method in WOrderNewController class with the following code:

[HttpGet]
[ProducesResponseType(StatusCodes.Status200OK)]
[ProducesResponseType(StatusCodes.Status500InternalServerError)]
public ActionResult<string> WfGetOrderNo(string custNo)
{
    // Do a Try/Catch for Exception handling
    try
    {
        // Return the Formatted Order Number by calling the service
        return _service.WfGetOrderNo(custNo);
    }
    catch (Exception e)
    {
        // Return a Status Code of 500 with the Exception Message
        return StatusCode(StatusCodes.Status500InternalServerError, e.Message);
    }
}
Execute SQL and get a record

Porting the function nw_order_new.of_dw_orderitem_itemchanged().

Original PowerScript code
ab_has_product = True

Choose Case Lower(as_column)
	Case	"fsku"
		// Filled with category and name
		SELECT Top 1 fcategory,fproname,funit_price,fdescription
		INTO :astr_product.fcategory, :astr_product.fproname, :astr_product.funit_price,:astr_product.fdescription
		FROM t_products
		WHERE fsku = :Data;
			
		If sqlca.SQLCode <> 0 Then
			ab_has_product = False
			Return -1
		End If
	Case	"fproname"
		// Filled with category and name
		SELECT Top 1 fcategory, funit_price, fsku, fdescription
		INTO :astr_product.fcategory, :astr_product.funit_price, :astr_product.fsku, :astr_product.fdescription
		FROM t_products
		WHERE fproname = :Data;
			
		If sqlca.SQLCode <> 0 Then
			ab_has_product = False
			Return -1
		End If
	Case Else
		Return 0
End Choose

Return 1
Ported code in the Service class

If you have opened the nw_order_new.sru in SnapDevelop IDE, you can click in the of_dw_orderitem_itemchanged 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 DwOrderItemItemChanged() method in the WOrderNewService class. In this example, the C# code to be added to the method is as follows:

public short DwOrderItemItemChanged(string columnName, string data, out bool hasProduct, out D_Product product)
{
    hasProduct = true;
    product = new D_Product();

    switch (columnName.ToLower())
    {
        case "fsku":
            // Filled with category and name
            product = _context.SqlExecutor.SelectOne<D_Product>(
                @"SELECT Top 1 fcategory,fproname,funit_price,fdescription 
                FROM t_products 
                WHERE fsku = @Data", data);

            if (product == null)
            {
                hasProduct = false;
                return -1;
            }

            break;
        case "fproname":
            // Filled with category and name
            product = _context.SqlExecutor.SelectOne<D_Product>(
                @"SELECT Top 1 fcategory, funit_price, fsku, fdescription 
                FROM t_products 
                WHERE fproname = @Data", data);

            if (product == null)
            {
                hasProduct = false;
                return -1;
            }

            break;
        default:
            return 0;
    }

    return 1;
}

Note: it uses the D_Product model here to receive the record. You can also use DynamicModel provided by SnapObjects to receive this record dynamically if you don't want to create a model.

Ported code in the Controller class

Update the DwOrderItemItemChanged() method in WOrderNewController class with the following code:

[HttpPost]
[ProducesResponseType(StatusCodes.Status200OK)]
[ProducesResponseType(StatusCodes.Status500InternalServerError)]
public ActionResult<IDataPacker> DwOrderItemItemChanged(IDataUnpacker dataUnpacker)
{
    string column = dataUnpacker.GetValue<string>("Column");
    string data = dataUnpacker.GetValue<string>("Data");

    // Do a Try/Catch for Exception handling
    try
    {
        // Execute the business logic
        short result = _service.DwOrderItemItemChanged(
            column, data, out bool hasProduct, out D_Product product);

        // Add values to a DataPacker
        IDataPacker packer = new DataPacker();

        packer.AddValue("Result", result);
        packer.AddValue("HasProduct", hasProduct);

        // Success
        if (result == 1)
        {
            packer.AddValue("fcategory", product.Fcategory);
            packer.AddValue("fproname", product.Fproname ?? "");
            packer.AddValue("fsku", product.Fsku ?? "");
            packer.AddValue("funit_price", product.Funit_Price);
            packer.AddValue("fdescription", product.Fdescription);
        }

        // Return a Status Code of 200
        return Ok(packer);
    }
    catch (Exception e)
    {
        // Return a Status Code of 500 with the Exception Message
        return StatusCode(StatusCodes.Status500InternalServerError, e.Message);
    }
}

Call the Web API from PowerBuilder

Now that the code has been migrated to the Web APIs, the final step to complete the migration is to call the RESTful Web APIs from PowerBuilder.

The demo application used in this tutorial includes a Standard Class named n_RESTClient inherited from RESTClient. This object has been developed with some sample functions to facilitate and standardize the calls to RESTful Web APIs.

n_RESTClient

Convert your DataWindows into JSON DataWindows

The application will no longer use a database connection to perform the CRUD functionalities. Instead, it uses calls to the RESTClient functions. Therefore, all the related DataWindow functions have been commented.

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 for the optimum data transfer performance.

The following example demonstrates how this has been accomplished:

Call the Web APIs

We only demonstrate how to call Web APIs mentioned above here.

Retrieve DataWindow (Plain JSON)

Modify nw_customer_select.of_open():

String ls_url

ls_url = gn_RESTClient.of_get_url("WCustomerSelect", "Open")

Return gn_RESTClient.of_retrieve(adw_cust, ls_url)

Retrieve DataWindow (DataWindow Standard JSON)

Modify nw_order_main.of_dw_orderitem_ue_retrieve():

String ls_url

ls_url = gn_RESTClient.of_get_url("WOrderMain", "DwOrderItemUeRetrieve") + &
		"?custNo=" + as_custno + "&" + "orderNo=" + as_orderno

Return gn_RESTClient.of_retrieve_standard(adw_orderitem, ls_url)

Update DataWindow

Modify nw_order_modify.of_ue_save():

String ls_url
String ls_response
String ls_json
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("WOrderModify", "UeSave")
If gn_RESTClient.of_post(ls_url, lnv_package) > 0 Then
	li_return =  1
Else
	li_return = -1
End If

Destroy lnv_package

Return li_return

Execute SQL without result set

Modify nw_order_main.of_ue_deleteorder():

String ls_url

ls_url = gn_RESTClient.of_get_url("WOrderMain", "UeDeleteOrder") + as_orderNo

Return gn_RESTClient.of_delete(ls_url)

Execute SQL and get a value

Modify nw_order_new.of_wf_get_orderno():

String  ls_OrderNo
String ls_url

ls_url = gn_RESTClient.of_get_url("WOrderNew", "WfGetOrderNo") + "?custNo=" + as_custno
If gn_RESTClient.of_get(ls_url, ls_OrderNo) > 0 Then
	Return ls_OrderNo
Else
	Return ""
End If

Execute SQL and get a record

Modify nw_order_new.of_dw_orderitem_itemchanged():

String ls_url
String ls_json
Int li_return
Int li_result
JsonPackage ljpk_request
JsonPackage ljpk_response

ljpk_request = Create JsonPackage
ljpk_request.SetValueString("Column", as_column)
ljpk_request.SetValueString("Data", data)
ls_json = ljpk_request.GetJsonString()

ls_url = gn_RESTClient.of_get_url("WOrderNew", "DwOrderItemItemChanged") 

ljpk_response  = Create JsonPackage

If gn_RESTClient.of_post(ls_url, ljpk_request, ljpk_response) > 0 Then
	// Get Response data
	li_result =  ljpk_response.GetValueNumber("Result")
	ab_has_product = ljpk_response.GetValueBoolean("HasProduct")
	
	If li_result = 1 Then
		astr_product.fcategory = ljpk_response.GetValueString("fcategory")
		astr_product.fproname = ljpk_response.GetValueString("fproname")
		astr_product.fsku = ljpk_response.GetValueString("fsku")
		astr_product.funit_price = ljpk_response.GetValueNumber("funit_price")
		astr_product.fdescription = ljpk_response.GetValueString("fdescription")
	End If
	
	li_return = li_result
Else
	li_return = -1
End If

Destroy ljpk_request
Destroy ljpk_response

Return li_return

Test the Ported PB application and RESTful Web API

We have only demonstrated some typical PB data processing scenarios above. You can either, port all the other business logics to finish this demo or download the finished demo from here.

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