Working with DataWindow Converter

Last Updated: February 2023

DataWindow Converter is designed to use the DataWindow objects from PowerBuilder to generate the C# data objects and models that can be used with SnapObjects in the C# editor (such as SnapDevelop and Visual Studio). To perform this operation, you need to have a PowerBuilder application which contains the DataWindow objects and a C# project to which you want to export the DataWindow objects.

Launching DataWindow Converter

To launch the DataWindow Converter,

Create a C# project in SnapDevelop IDE (select File > New > Project).
Right click the solution node in the Solution Explorer, and select Open PB Workspace to open a PowerBuilder application which contains the DataWindow object.
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.

Creating Database Connection

Before converting DataWindows to C# models, you must create a connection to the database (or the data source) you used to create the DataWindow objects. Once created successfully, the connection will be saved (in Server dropdown list) in the DataWindow Converter so that you can select it next time, instead of creating it from scratch again. You cannot modify a database connection in the DataWindow Converter, however, you can remove it from the Server dropdown list by clicking the x icon.

To create a new database connection,

On the toolbar of DataWindow Converter, select New Connection from the Server dropdown list.
In the Database Connection window that appears, click New to create a new connection.
In the window that follows, select a data provider, and then specify the basic connection settings according to the provider. You can also click the Advanced button to configure the advanced settings.

The connection settings and the advanced settings are different according to the selected data provider. The Appendix D: Database Connection Settings at the end of the SnapDevelop User Guide lists the databases that are supported by this feature, and the related connection settings.
After specifying the connection settings and/or advanced settings, click Test Connection to verify the connection is successful and then click OK to close the window and return to the Database Connection main window.

On the Database Connection main window, you can verify or change the connection string, and save the connection string in the configuration file.

Once you click OK on the Database Connection main window, DataWindow Converter tries to connect to the selected database and parse the DataWindow objects. For the supported databases, refer to the SnapDevelop Installation Guide.

If the DataWindow objects connect to more than one database, you can refer to Creating multiple database connections for how to create multiple database connections and parse the DataWindow objects.

Configuring DataWindow Converter

Once connected with the database successfully, you can click Parse () on the toolbar of DataWindow Converter to parse the DataWindow objects and generate the source code of C# data models. After the parse, DataWindow objects are marked with dots of different colors to indicate the parse results:

Color	Result
Green ()	Success
Red ()	Failure
Blue ()	Skipped from parse. Possible reasons include: a. The data type of a DataWindow column is not supported by the runtime. b. A table associated with the DataWindow object is missing from the database source. c. The schema of a table associated with the DataWindow object has been changed.

In DataWindow Converter, you can also perform the following tasks:

Setting Global Options

On the toolbar of DataWindow Converter, click the Global Options icon () to set options that are applicable throughout the whole model generation process.

Under DataWindow Converter | General, you can specify a number of settings:

User interface options:

Auto-select listed child DataWindows if their parent DataWindow gets selected Whether to automatically select the child DataWindow, if the parent DataWindow is selected for parsing or exporting.
Single expand DataWindow nodes Whether to single expand DataWindow nodes. If selected, only one DataWindow will be expanded at a time, and if you expand another DataWindow, the previously expanded DataWindow will be collapsed.

Parse settings:

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.

Model generation settings:

Select the .NET DataStore attributes you want to include in the generated C# models.

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

Note that these settings will take effect when you restart DataWindow Converter.

Locating the DataWindow object

All successfully parsed DataWindow objects are selected for export by default. DataWindow objects that are not parsed successfully or skipped from parse cannot be selected to export (Such DataWindow objects will be automatically skipped from export even if they are selected).

You can use the toolbar of DataWindow Converter to quickly find the DataWindow objects you want, for example, you can input the object name in the Search box to find the DataWindow, or you can select Warning, Missing or Error from the State list to show the problematic DataWindow objects which you can make corrections.

State list -- Shows DataWindow objects according to their state.

Select this state	To
All	Show all DataWindow objects regardless of their state. This is the default option.
Wait for Parsing	Show only the DataWindow objects that have not been parsed.
Completed	Show only the DataWindow objects that have been parsed successfully.
Warning	Show only the DataWindow objects whose models cannot be generated correctly because of duplicated class names etc.
	You can click the Warning icon () on the action bar of the DataWindow object to view the warning details.
Missing	Show only the DataWindow objects whose objects are invalid because of the database connection or table/column not found.
	You can click the Parsing Error icon () to view error details and the Try Another Connection icon () on the action bar of the DataWindow object to create a new database connection.
Error	Show only the DataWindow objects in which exceptions occurred. You can click the Parsing Error icon () on the action bar of the DataWindow object to view the error details.

View Modified Only option -- Shows only the DataWindow objects whose C# class names or property names have been modified.

For how to modify the class name or property, see Modifying class name or property name.
View Selected Only option -- Shows the DataWindow objects that are selected right now. You can use the Select All check box to select or de-select all DataWindow objects; or use the check box next to the individual DataWindow object to select or de-select a particular DataWindow object.
Search box -- Searches for a particular DataWindow object.

Modifying 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 all C# classes,

You can add prefix and/or suffix to the name of all of the model classes at one time, by setting the Model name settings in the Global Options window.

To modify the name of each individual C# class and property,

Expand to view the columns of the DataWindow in DataWindow Converter.
Modify the class name and/or the property name.

Once you change the name, a number of restore buttons appear on the action bar of the DataWindow object and the DataWindow column. You can use these restore buttons to efficiently restore to the default names.

To restore a DataWindow, click the Table Restore icon () on the action bar of that DataWindow.

To restore a single column, click the Column Restore icon () on the action bar of that column, to restore all columns of a DataWindow, click the Columns Restore icon () on the action bar of that DataWindow.

To restore all columns and the DataWindow, click All Restore icon () on the action bar of that DataWindow.

Configuring data type mappings for DataWindow with stored procedure

When converting a DataWindow with stored procedure, you can manually specify the mapping C# data type for each column in the DataWindow.

To configure the mapping C# data type for a DataWindow with stored procedure,

Click the Column Type Mapping icon () for the DataWindow you want to configure.
In the window that opens, select the mapping C# data type in the dropdown list by the Property Type (C#) column in the table.
Click OK.

Creating multiple database connections

DataWindow Converter uses only one database connection when parsing; therefore, if the DataWindow objects connect to more than one database, some objects may not be parsed successfully. They will be reported as "Missing" (Invalid object name 'xxx') or "Error" (Table 'xxx' not found) in the Output window; and a Parsing Error icon ( or ) will appear on the action bar of the problematic DataWindow.

To resolve this problem,

Click the Try Another Connection icon () (if available) on the action bar of the problematic DataWindow object, or select New Connection from the Server dropdown list on the toolbar of DataWindow Converter.
In the Database Connection window that appears, create a new database connection for the object.
After you create a new database connection and click OK, you will be prompted "Whether to reparse all DataWindows?" Click Yes to parse all failed DataWindows or No to parse the current DataWindow only. If the project contains only one DataWindow object, the parse starts immediately without prompting the message.

You can also click the Parse icon () on the toolbar to parse all failed DataWindow objects.

Exporting C# models

After all of the DataWindow objects are parsed successfully, you can export the DataWindow objects as data models to a C# project.

To export the DataWindow objects to a C# project,

Select the desired DataWindow objects.
Click the Export icon () on the toolbar of DataWindow Converter.
In the Export window that appears, select the C# project from the Project list or click Browse to select the project to which you want to export the DataWindow objects.

In the Model Export window, you can also specify the following options:

Save Strategy: 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.
Project: The name and location of the project.

Model Folder: The folder where the models will be exported under the project. The folder name is Models by default. You can also specify whether to generate subfolders using the name of PBT and PBL.

Namespace: The namespace of the selected C# project will be automatically displayed. You can change the namespace if you want.
To generate a DataContext file for the models you export, click New DataContext. Specify the file name in the New Item window that opens, and click OK.
Click Export to output the DataWindow objects.

There will be a popup window indicating the number of objects that are successfully exported.

Overview of the exported files

After the DataWindow objects are exported as data models to a C# project, the C# project will include the following files.

Folder structure

The structure is the same as the PowerBuilder application. There will be a newly added folder whose name is Models by default. The Models folder contains subfolder(s) named after the PBT file, which in turn contains subfolder(s) named after the PBL file.

.cs files

The CS file contains the source code of the C# model. Model is short for the data model class which represents the application data entry. It maps columns to database tables and contains the related SQL. The SQL of the model is generated based on various attributes, which gives developers control over the SQL.

When using the .NET DataStore, database operations such as Insert, Update or Delete are performed via its corresponding model.

.xml files

If the DataWindow object uses the XML template, the XML template will be converted automatically when DataWindows are exported. The converted template can be used by the .NET DataStore later.

Handling Parsing Failures or Errors

Due to the flexible and various use of the DataWindow features, a few DataWindow objects might not be successfully parsed by the DataWindow Converter or work as expected in the C# project. When a DataWindow object failed to be parsed or failed to work in the C# project, you should check the reported errors or warnings in the DataWindow Converter to analyze the cause or check the following sections to determine the cause.

Causes of parsing failure in model generation

DataWindow Converter parses DataWindows according to their SRD files and then generates C# models. The following cases may result in parsing failure, which means that model cannot be successfully generated from a DataWindow.

Database Connection

If you connect to Oracle database, it is strongly recommended that you use Oracle 12c or later.

DataWindow

DataWindow types that may result in a parsing failure include:

Crosstab, OLE, and RichText
Nested reports

DropDownDWs that may result in parsing failure include:

A DropDownDW sets itself as its DataWindow object

DataWindow Column

Column types that may result in parsing failure include:

(If it is SQL Server connection) Datetime2
Char(-1) being the column type

Column names/dbnames that may result in parsing failure include:

Contain some database reserved words, for example, USER, NAME (reserved word in Oracle)
A column has duplicated name with the DataWindow that it belongs (This is a limitation in C# development: member names cannot be the same as their enclosing type.)
Case-insensitive duplicate dbnames.

DataWindow SQL

Elements in SQL syntax that may result in parsing failure include:

SQLCA.DBParm parameters (including DelimitIdentifier, TrimSpaces, StaticBind, DisableBind) are ignored in the model generation. Therefore, if the SQL syntax itself is incompatible with the DBMS, there may be parsing error. For example, if you access SQL Anywhere or Oracle, you cannot use single quotes to enclose table and column names in the SQL syntax.
Contain comments
Contain keywords such as WITH, SET, DECLARE, EXECUTE
Contain user defined function(s)
Contain ('*=') operator
Use double bars ("||") for concatenation
Contain SELECT Distinct COL1 but COL1 is text type
Complex SQL sub-queries or nested queries. For example, IF ... then select ... else if ... then select ... else select ... end if
(If the data source is a stored procedure) A parameter in the stored procedure is not declared but is assigned with a value.
A default value is directly assigned to an argument in the SQL. For example, TABLE(... arguments=(("a_plant", string, "test"),("a_return", string)) )
Missing spaces between words. Such as, missing a space after the SELECT DISTINCT keyword (for example, SELECT Distinct"audit"."p_lname"); missing a space between parameters (for example, export.pdf(method=0distill.customPostScript="0" xslfop.print="0"))

Other SRD Settings

Other SRD settings that may result in parsing failure include:

Any of the X/Y/Width/Height settings has decimal value

Causes of errors in generated models

Although a model can be generated successfully for a DataWindow, the model may not work correctly in the following cases:

If a DataWindow has used expression in its computed field, the GetItem function of the model generated from the DataWindow sometimes may not work well.
If the SELECT statement in the DataWindow SQL refers to a computed column with an empty string and no alias, the parser will use a default name for the column. The generated model may fail to work because the column in the model cannot be mapped back to the DataWindow column by name.
If the columns returned by the DataWindow SQL are more than the columns defined in the DataWindow painter, the generated model may fail to work because the column in the model cannot be mapped back to the DataWindow column by name.
If a DataWindow column has default value that contains double quotes, you must add escape character () in the double quotes in the generated model, to make sure the default value can be interpreted correctly at runtime.
If the DataWindow SRD contains some parameters in the UI or Group By section, the model generated from the DataWindow may fail to create the datastore.
Contains some parameters that will display in the UI
If a DataWindow contains expression which calls a global function, the global function won't work well in the generated model.

This issue can be resolved by the following workaround steps:

Create a static function in the class file that provides the same functionality as the global function;

For example, using the following script to create the static function:
```
public class MyGlobalFunc     { 
     public static Int32 gf_getid2(Int32 id)         { 
         return id;
       } 
   }
```
Register the function by calling PbExpressionFactory.Current.AddGlobalFunc<MyGlobalfunc>(), where MyGlobalfunc is the class that the newly created static function belongs to and it should not be a static class.

For example, registering the function in Program.cs:
```
...
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();

PbExpressionFactory.Current.AddGlobacFunc<MyGlobalFunc>();

var app = builder.Build();
...
```