The .NET DataStore project is available only in the PowerBuilder CloudPro and Professional editions.
You can use the .NET DataStore project to convert DataWindow objects to C# data objects and models and generate controllers and services from the models.
A C# solution will be generated automatically to hold the C# models, services, and controllers. You can build and run the C# solution to expose the Retrieve and Update services as standard RESTFul Web APIs, so that they can be called from PowerScripts.
You can create a .NET DataStore project by selecting the .NET DataStore project type.
To create a .NET DataStore project:
-
Select File>New or click the New button in the PowerBar to open the New dialog box.
-
Select the Project Object tab.
-
Select the target in which you want to create the project from the Target drop-down list on the top of the window.
-
Select the .NET DataStore project type and click OK.
The Project painter for .NET DataStore opens so that you can specify the various properties of the .NET DataStore models.
-
When you have finished defining the project object, save the object by selecting File>Save from the menu bar. PowerBuilder saves the project as an independent object in the specified library. Like other objects, projects are displayed in the System Tree and the Library painter.
You must create a connection to the database where the DataWindow objects access data. The database connection is required when parsing and converting the DataWindow objects.
Step 1: Click the Config Database
Profile button (
) in the .NET DataStore Toolkit toolbar.
Step 2: In the Choose database profile window, click New to create a new connection.
Step 3: In the Database Configuration window that appears, select a data provider and specify the connection settings according to the provider. Select the check box of "I have read and agree to the license ...".
For more information, refer to Download the database driver and Configure the database connection.
Step 4: After creating the database profile, return to the Choose database profile window and select this profile.
Only one database connection will be used when parsing the DataWindow objects. If the DataWindow objects connect to more than one database, some objects may not be parsed successfully. There will be an error message ("Failed: Invalid object name 'xxx'", "Failed: Table ‘xxx’ not found" etc.) shown in the State field. You can try to resolve the error with the following steps:
Step 1: Create a connection for the second database and select it.
Step 2: Select the failed DataWindow objects in the project painter > Converter page (the State field is indicated with the "Failed: xxx" error message).
Step 3: Click the Parse DataWindows button in the toolbar to parse these failed objects again. Make sure the state changed to "Succeed".
Once you have created a .NET DataStore project, you can open it from the System Tree and modify the properties if necessary. The Project painter for the .NET DataStore project looks like this.
The following sections describe each of the options you can specify in the Project painter for .NET DataStore.
Note
If you want to view an example of the .NET DataStore project, you can open the demo app, for example, from Windows Start | Appeon PowerBuilder [version] | Example Sales App, and view the .NET DataStore project included in the demo.
The Project page has the following settings:
-
.NET Project
Project name -- Specify a name for the project.
Solution -- Specify the location for the C# solution.
Target framework -- Select if the C# solution is targeted to .NET 6.0 or .NET 8.0.
-
Build
Rebuild -- Specify whether to perform a full or incremental build to the C# solution when running from the IDE (when you click the Run Project button in the toolbar, the C# solution will be built and run locally).
Platform target -- Select which platform (32-bit, 64-bit, ARM32, ARM64, or any) the C# solution will target when running from the IDE.
-
Project launch settings
Host -- Specify the host name or IP address of the API server.
Port -- Specify the port number of the API server.
HTTPS -- Specify the connection method for the API server.
Site URL -- The site URL is automatically generated based on the host name, port number, & connection method.
The Converter page allows you to:
-
Create the database connection
Select an existing connection or select Choose connection from the Database profile list to create a new connection.
-
Locate the DataWindow object
All of the DataWindow objects contained in the current target are selected by default in the project painter > Converter page.
You can quickly find the DataWindow objects you want, for example, you can select the PBL to view the DataWindow objects contained in it, you can input the object name in the search box to find the DataWindow, or you can select to show the modified or failed DataWindow objects which you can make corrections.
-
Select all -- Selects or de-selects all DataWindow objects.
-
View selected only -- Shows only the DataWindow objects that are selected right now.
-
View modified only -- Shows only the DataWindow objects whose C# class names or property names have been modified.
-
View failed only -- Shows only the DataWindow objects which failed to be parsed.
-
Search box -- Searches for a particular DataWindow object.
-
-
Modify the class name or property name
For each C# class that is generated from the DataWindow object, it will be named after the DataWindow object by default; and likewise, for each C# property that is generated from the DataWindow column, it will be named after the DataWindow column by default. Instead of using these default names, you can modify the name of the C# class and property to be generated.
To add name prefix and/or suffix to the name of all of the model classes at one time, you can specify the Model name settings in the Options window (clicking the Converter Settings button in the toolbar to show the Options window).
To modify the name of each individual C# class, you can click the class name and then change it.
To modify the name of each individual C# property, you can click the Edit button and then click the property name and modify it.
After you modify the class name or the property name, the corresponding object will be shown when you select View modified only.
The Scaffolding page has the following settings:
-
Generate Service(s) -- Select this option if you want to generate services from the models.
Template: Select a template from the list.
Method: Select to generate asynchronous or synchronous methods.
Subfolder: Specify the folder where you want to generate the target services. You can select a folder from the list, or choose New Folder to create a new one.
Namespace: Designate the namespace in the target services.
DataContext: The data source that the target service will use.
Base Class: Specify a base class for the target service. If you specify a base class that already exists in the current project, the base class can be directly used for the target services; if you specify a base class that does not exist, the base class will first be created, and then will be used for the target services.
-
Generate Controller(s) -- Select this option if you want to generate controllers from the models.
Subfolder: Specify the folder where you want to generate the target controllers. You can select a folder from the list, or choose New Folder to create a new one.
Namespace: Designate the namespace in the target controllers.
-
Specify scaffolding items: Modify the default name of the target service(s) and controller(s).
These settings will be used to generate services and controllers (when Generate & Build Project is clicked).
The Security page has the following settings:
Auth template -- Specify the authentication template for the Web APIs. The C# solution will automatically include the corresponding built-in auth server and/or template.
-
Do not use auth service: Provides no authentication template.
-
Use built-in JWT server: Includes a built-in authentication server that supports JWT or bearer tokens. See Authenticate > Using JWT for more information.
-
Use built-in AWS Cognito server: Includes a built-in authentication server that works with the Amazon Cognito user pool. See Authenticate > Using Amazon Cognito for more information.
-
Use external Microsoft Entra ID service: Includes templates that work with Microsoft Entra ID (formerly known as Azure AD or Azure Active Directory) or Azure AD B2C. See Authenticate > Using Microsoft Entra ID for more information.
-
Use external auth service: Includes templates that can be easily extended to support the other identity providers that work with the OAuth flows or JWT, such as Okta OIDC (OpenID Connect). See Authenticate > Using other auth servers for more information.
You can click the Converter
Settings button (
) in the .NET DataStore Toolkit toolbar to set
options that are applicable throughout the whole model generation
process:
-
Converter > General settings
You can choose from the following three options if a model with the same name already exists in the project:
1) Increment the file name -- to append a number (1, 2, 3...) to the end of the file name.
2) Overwrite the existing file -- to overwrite the existing model.
3) Skip the file -- to keep the existing model and the model with the same name will not be exported.
-
Converter > Model generation settings
Namespace -- The namespace of the selected C# project will be automatically displayed. You can change the namespace if you want.
Model name settings -- Whether to add prefix and/or suffix to the name of the generated model class.
For example, if you add {pbt} as prefix and {pbl} as suffix, the model class whose DataWindow is "d_Example_Report_Detail" may have a name like "Pb_Examplesd_Example_Report_Detailpbexamsa".
Generate a subfolder for -- You can also specify whether to generate subfolders for PBT and PBL using their corresponding names.
Use a column name placeholder @(_COLUMNS_PLACEHOLDER) to replace all column names in DwSelect -- Using the placeholder will make it easier to maintain the model DwSelect attribute when you add or delete columns later. But it is not recommended to use the column placeholder if the model uses the stored procedure as data source.
For example, the DwSelect attribute may look like below when using the column placeholder:
[DwSelect("PBSELECT( VERSION(400) TABLE(NAME=\"examples\" ) @(_COLUMNS_PLACEHOLDER_) WHERE( EXP1 =\"( examples.window\" OP =\"=\" EXP2 =\":win )\" ) ) ARG(NAME = \"app\" TYPE = string) ARG(NAME = \"win\" TYPE = string)")]The DwSelect attribute may look like below when using the actual column name:
[DwSelect("PBSELECT( VERSION(400) TABLE(NAME=\"examples\" ) COLUMN(NAME=\"examples.description\") COLUMN(NAME=\"examples.window\") COLUMN(NAME=\"examples.version\") COLUMN(NAME=\"examples.technique\") COLUMN(NAME=\"examples.new\") COLUMN(NAME=\"examples.enhanced\") COLUMN(NAME=\"examples.title\")WHERE( EXP1 =\"( examples.window\" OP =\"=\" EXP2 =\":win )\" ) ) ARG(NAME = \"app\" TYPE = string) ARG(NAME = \"win\" TYPE = string)")]Note: It is recommended that this option should be selected, because coding complexity can be simplified. However, if a DataWindow uses stored procedure as its data source, the option won't take effect.
Generate models with .NET DataStore attributes -- Select the .NET DataStore attributes you want to include in the generated C# models.
Note that these settings will take effect when you re-open the project painter.
-
Scaffolding > General settings
You can choose from the following three options if a service or controller with the same name already exists in the project:
1) Increment the file name -- to append a number (1, 2, 3...) to the end of the file name.
2) Overwrite the existing file -- to overwrite the existing model.
3) Skip the file -- to keep the existing model and the model with the same name will not be exported.
Parse DataWindows
(
) will parse the DataWindow objects only. You
can use this tool to quickly parse the selected DataWindow objects,
especially, the failed DataWindows. You should not export the models
or generate & build project, until the selected DataWindows all
have a successful parsing result.
Export Models
(
) will parse DataWindows and generate C#
solution, project, and models. You can use this tool to convert the
selected DataWindow objects to C# models, refresh the existing models
in the project, or add new models to the project.
Generate & Build Project
(
) will parse the selected DataWindow objects,
generate the C# solution, project, models, services, and controllers,
and build the C# project.
To parse DataWindows:
If some DataWindows failed to parse, you can make changes to these DataWindows and then parse them again to quickly find out if that corrects the error.
-
Close any anti-virus tool on the development machine.
-
Select the DataWindow objects in the project painter > Converter page that you want to parse (by default all DataWindow objects are selected).
-
Select a database connection from the Database profile list on the Converter page.
-
Click the Parse DataWindows button in the toolbar.
To export models:
If you have changed some DataWindows, or added new DataWindows, you can export and update/add models to the existing solution.
-
Close any anti-virus tool on the development machine.
-
Select the DataWindow objects in the project painter > Converter page that you want to generate models (by default all DataWindow objects are selected).
-
Select a database connection from the Database profile list on the Converter page.
-
Click the Export Models button in the toolbar.
A C# solution is generated under the specified location (by default C:\Users\[username]\[projectname]). The C# solution will contain a C# project and models.
To generate and build a .NET DataStore project:
If you want to not only parse DataWindows and export models, but also generate a complete C# project (including the services and controllers), you should do the following:
-
Close any anti-virus tool on the development machine.
-
Select the DataWindow objects in the project painter > Converter page that you want to generate models, services and controllers (by default all DataWindow objects are selected).
-
Select a database connection from the Database profile list on the Converter page.
-
Click the Generate & Build Project button in the toolbar, or right-click the .NET DataStore project in the System Tree and then select Generate & Build Project.
A C# solution is generated under the specified location (by default C:\Users\[username]\[projectname]). The C# solution will contain a C# project, models, services, and controllers.
To run a .NET DataStore project in the local Kestrel server:
-
Run the project by right-clicking the project in the System Tree and then select Run Project.
Or click the Run Project button in the toolbar. The Run Project button will be available in the toolbar when the Project painter for .NET DataStore is opened; if more than one Project painter for .NET DataStore is opened, then the settings in the currently active painter will be used to run the project.
A local Kestrel server is automatically created and run when you run the project from the IDE.
When the server console window displays the following messages, it indicates the server starts successfully:
-
"Now listening on: http://x.x.x.x:yyyy" -- This is the root URL of the Web APIs.
-
"Application started" -- The Web APIs is started successfully.
-
"Hosting environment: Development" -- The Web APIs is currently running in the development environment.
-
-
Input the Swagger URL http://x.x.x.x:yyyy/swagger/index.html in a Web browser to view the exposed Web APIs.
To edit or publish a .NET DataStore project:
-
Click the Open C# Solution in SnapDevelop button in the toolbar to launch the C# solution in SnapDevelop.
Or go to the location where the C# solution is generated (%username%\source\repos by default); and double click [projectname].sln to launch the solution in SnapDevelop.
-
Edit the .NET DataStore project, or publish the .NET DataStore project to the server just like how you publish any ASP.NET Core app.
For more information, refer to the SnapDevelop documents.
The following are a few code examples showing how to call the .NET DataStore Service APIs from PowerScript.
For more examples, refer to RetrieveWithModel, UpdateWithModel, and RetrieveOrUpdateWithModels.
Example 1: To retrieve data for only one DataWindow
long ll_return
RestClient lnv_RestClient
lnv_RestClient = Create RestClient
// Retrieve data via .NET DataStore Service
ll_return = lnv_RestClient.RetrieveWithModel(dw_emp, "http://localhost:5099/RetrieveWithModel", 102)
//// Update data via .NET DataStore Service
// ll_return = lnv_RestClient.UpdateWithModel(dw_emp, "http://localhost:5099/UpdateWithModel")
// Check the return value
if ll_return >= 0 then
MessageBox("Success", "Rows = " + String(ll_return))
else
MessageBox("Error", "Failed to retrieve data.")
end ifExample 2: To retrieve data for more than one DataWindow
Integer li_return, li_index
RestClient lnv_RestClient
BatchDataObjects lnv_objects
lnv_RestClient = Create RestClient
lnv_objects.AddUpdateObject(dw_dept)
lnv_objects.AddUpdateObject(dw_emp)
lnv_objects.AddRetrieveObject(dw_emp, 129)
if lnv_objects.GetCount() <= 0 then
MessageBox("Error", "Error when add dwControl to BatchDataObjects")
return
end if
// Send request to .NET DataStore Service
li_return = lnv_RestClient.RetrieveOrUpdateWithModels (lnv_objects ,"http://localhost:5099/RetrieveOrUpdateWithModels")
// Check the return value
if li_return = 1 then
MessageBox("Success", "Return = " + String(li_return))
else
li_index = lnv_objects.GetErrorIndex()
if li_index > 0 then
MessageBox("RetrieveOrUpdateWithModels Error(Index:" + String(li_index) + ")", &
"Error DB Code:"+ string(lnv_objects.GetSQLDBCode()) + "\r\n Error Message:" + lnv_objects.GetSQLErrText())
else
//handle other error
end if
end if
