Application Techniques
Calling .NET Assembly in an Application

Although you can write scripts to load a .NET DLL, create the DotNetObject object, and then call the .NET class functions through the DotNetObject object, it is difficult to ensure the functions/parameters/return values are called correctly. Therefore, we recommend that you use the .NET DLL Importer tool to import the .NET class to PowerBuilder first, and then call the imported object and function to execute the corresponding .NET code.

.NET DLL Importer can import the names and data types of the .NET classes, functions, properties, and parameters from the .NET assembly to the application PBL. It creates the DotNetObject object as an NVO for each .NET class and then imports the .NET functions to the NVO. After that you can write scripts to call the NVO and functions to execute the corresponding .NET code. It can also create the DotNetAssembly object for each DotNetObject object and add try-catch scripts to catch and handle the errors, which can greatly simplify the scripts that you need to write.

Note that PowerBuilder does not check the syntax of the DotNetObject NVO (such as mismatched data type etc.) when compiling this NVO; and PowerBuilder calls the .NET function in this order: it searches and calls the function in the NVO first; if no function is found in the NVO, it searches and calls the function in the corresponding .NET class.

Compared to calling the .NET function in DotNetObject, calling the NVO function has the following advantages and disadvantages:

Advantages:

Disadvantages:

Step 1: Select Tools | .NET DLL Importer menu in the PowerBuilder IDE.

Step 2: In the .NET DLL Importer window, select the .NET DLL file, the framework type, and the destination PBT and PBL files in the upper part.

The framework type specifies the framework for the assembly; it can be .NET Framework, .NET Core, or .NET. Different functions and runtime files will be used to load the DLL.

You can edit the Source .NET DLL field to specify the relative path for the .NET DLL file. The DLL file will be loaded when the cursor is moved away from this field.

Once you select a DLL, the DLL file as well as all the classes/functions/properties it contains will be automatically listed in the lower left corner of the window.

The first level will be the DLL name

- The second level will be the namespace

-- The third level will be the class name

--- The fourth level will be the function and property name

---- The fifth level will be the function name that accesses the property value

Step 3: Select the classes and functions that you want to import.

Once you select an item to import, the corresponding PowerBuilder object and function that will be created can be previewed on the right.

Each .NET class will be imported as a DotNetObject object which is an NVO; and the functions contained in the .NET class will be imported as functions of the NVO.

The names are case insensitive in both .NET and PowerBuilder. The naming conventions for the PowerBuilder objects/functions can be configured by clicking the Advanced Settings button.

If any function(s) cannot be imported, you can click the View Failed Item link to view all of the failed items and the reasons (most of them are unsupported features).

You can also click the Advanced Settings button to specify more detailed settings for the import:

Step 4: Click Import in the .NET DLL Importer window.

After the .NET classes and functions are imported to the PBL successfully, you can call the corresponding NVO and function directly to execute the corresponding .NET code.

Here is a sample of the C# assembly source code:

namespace Appeon.PowerBuilder.DotNet.Test
{
    public class TestCLR1
    {
        public int m_iTest
        {
            set;
            get;
        }
        public string m_strTest
        {
            set;
            get;
        }

        //int argument
        public int Add(int iFirst, int iSecond)
        {
            return iFirst + iSecond;
        } 

        //string argument
        public string StringCat(string strFirst, string strSecond)
        {
            return strFirst + strSecond;
        }

        //array argument
        public void TestByteArray(byte[] bArray)
        {
            for(int i = 0; i < bArray.Length; i++)
            {
            }
        }

        //reference argument
        public void TestReference(ref int iTest)
        {
            iTest = 1;
        }
    } 
}

Here is a sample of the automatically imported scripts with DotNetAssembly object and try-catch error handling incorporated in the DotNetObject object:

//public function long of_add (string as_parm1, string as_parm2);
//*-----------------------------------------------------------------*/
//*  .NET function : Add
//*   Argument：
//*              String as_parm1
//*              String as_parm2
//*   Return : Long
//*-----------------------------------------------------------------*/
/* .NET  function name */
String ls_function
Long ll_result

/* Set the dotnet function name */
ls_function = "Add"

Try
 /* Create .NET object */
 If Not This.of_createOnDemand( ) Then
  SetNull(ll_result)
  Return ll_result
 End If

 /* Trigger the dotnet function */
 ll_result = This.add(as_parm1,as_parm2)
 Return ll_result
Catch(runtimeerror re_error)

 If This.ib_CrashOnException Then Throw re_error

 /*   Handle .NET error */
 This.of_SetDotNETError(ls_function, re_error.text)
 This.of_SignalError( )

 /*  Indicate error occurred */
 SetNull(ll_result)
 Return ll_result
End Try
//end function

Here is a sample of the PowerScript code that calls the C# assembly after the import, when the DotNetAssembly object is encapsulated and the try-catch error handling is incorporated in the DotNetObject object.

//When the DotNetAssembly object is encapsulated
//and try-catch error handling is incorporated
nvo_TestCLR1  lnv_TestCLR1
long  ll_return

//Instantiates the object
lnv_TestCLR1 = create nvo_TestCLR1

//Calls the NVO function
ll_return = lnv_TestCLR1.of_Add(1, 2)
//Accesses the property
lnv_TestCLR1.set_m_iTest(1)
//Checks the result
if lnv_TestCLR1.il_ErrorType < 0 then
   messagebox("Failed", lnv_TestCLR1.is_ErrorText)
end if

Here is a sample of the PowerScript code that calls the C# assembly after the import, without encapsulating the DotNetAssembly object or incorporating the try-catch error handling in the DotNetObject object.

//Instantiates PB objects
nvo_TestCLR1 lnv_TestCLR1
DotNetAssembly lnv_Assembly
long ll_return

lnv_Assembly = create DotNetAssembly
lnv_TestCLR1 = create nvo_TestCLR1

//Loads assembly
ll_return = lnv_Assembly.LoadWithDotNetFramework ("Appeon.PowerBuilder.DotNet.Test.dll")
//ll_return = lnv_Assembly.LoadWithDotNetCore ("Appeon.PowerBuilder.DotNet.Test.dll")

if ll_return < 0 then
  MessageBox("Error", "Failed to load assembly: " + lnv_Assembly.errortext)
  return
end if

//Creates the instance and binds it to DotNetObject
ll_return = lnv_Assembly.CreateInstance ("Appeon.PowerBuilder.DotNet.Test.TestCLR1", lnv_TestCLR1)

if ll_return = 1 then
  try
     //tests long argument by calling the nvo function
     ll_return = lnv_TestCLR1.of_Add(1, 2)
     //or by calling the C# function
     ll_return = lnv_TestCLR1.Add(1, 2)

     //tests property by calling the nvo function
     lnv_TestCLR1.set_m_iTest(1)
     //or by accessing the C# property
     lnv_TestCLR1.m_iTest=1
  catch(Runtimeerror re)
     messagebox("Failed to call C# function", re.text)
  end try
else
     MessageBox("Error", "Failed to create instance: " + lnv_Assembly.errortext)
     return
end if

Here is a sample of PowerScript code that uses the variable-length array to receive the double-type data from C#. C# uses list to process the data from a PowerBuilder variable-length array, and then assign the data to the PowerBuilder ref variable-length array or return the data directly.

public void GetBigvalue(ref double[] darr, ref string[] sarr, bool max)
{
    //Uses list to process data
    IList<double> dvalues = new List<double>();
    IList<string> svalues = new List<string>();
    if (max)
    {
        //Process the largest value
        for (var d = double.MaxValue; d >= 8.72501618486925E+307; d /= 1.00001)
        {
            dvalues.Add(d);
            svalues.Add(d.ToString());
        }
    }
    else
    {
        //Process the smallest value
        for (var d = double.MinValue; d <= -8.72501618486925E+307; d /=1.00001)
        {
            dvalues.Add(d);
            svalues.Add(d.ToString());
        }
    }
    darr = dvalues.ToArray();
    sarr = svalues.ToArray();
}

If the .NET process uses IAdoConnectionProxy connection proxy, use the SQLCA.GetAdoConnection method in PowerBuilder. Note that IAdoConnectionProxy is supported in .NET Framework, but not in .NET Core. PowerBuilder ref oleObject maps to .NET [ref,out] IAdoConnectionProxy, and reference array is unsupported.

Here is a sample that PowerScript code shares its connection to a SQL Server database with C# code via ADO.NET, and C# code retrieves data successfully using the shared connection.

Sample C# function that makes SQL queries:

public string GetDatabyDS(IAdoConnectionProxy ado, string sql,Boolean native=false)
{
    if (native)
    {
        //Returns data via dataset
        SqlConnection con = ado.Connection as SqlConnection;
        SqlTransaction tran = ado.Transaction as SqlTransaction;
        
        SqlDataAdapter adp = new SqlDataAdapter();
        //Populates the SQL statements
        adp.SelectCommand = new SqlCommand();
        adp.SelectCommand.CommandText = sql;
        adp.SelectCommand.CommandType = CommandType.Text;
        adp.SelectCommand.Connection = con;
        adp.SelectCommand.Transaction = tran;
        //Populates data
        DataSet dataSet = new DataSet();
        adp.Fill(dataSet);
        return JsonConvert.SerializeObject(dataSet);
    }
    else
    { 
        SqlConnection tmp = ado.Connection as SqlConnection;
        tmp.Close();
        SqlServerDataContext context = new SqlServerDataContext(tmp);
        //Makes SQL query
        var ds = context.SqlExecutor.SelectToStore<DynamicModel>(sql);
        return ds.ExportPlainJson();
    }
}

Sample PowerScript code that makes the database connection:

SQLCA.DBMS = "ADO.Net"
SQLCA.LogPass = "admin"
SQLCA.LogId = "sa"
SQLCA.AutoCommit = False
SQLCA.DBParm = "Namespace='System.Data.SqlClient',DataSource='localhost',Database='adventureworks'"
Connect;

Sample PowerScript code that calls the C# function:

string ls_result,ls_sql
dotnetobject lcs_obj
dotnetassembly lcs_ass
long ll_return

lcs_obj = create dotnetobject
lcs_ass = create dotnetassembly
//Loads DLL
ll_return = lcs_ass.LoadWithDotNetFramework("resource\AppeonAssembly.dll")
if ll_return < 0 then
 messagebox("Load Failed",lcs_ass.errortext)
 return
end if
//Creates instances of dotnetobject and dotnetassembly
ll_return = lcs_ass.createinstance("AppeonAssembly.PBCsharpTrans",lcs_obj,false)
if ll_return < 0 then
 messagebox("createinstance failed",lcs_ass.errortext)
 return
end if
ls_sql = "Select * from esq_dept"
//Calls C# function
ls_result = lcs_obj.GetDatabyDS(sqlca.getadoconnection(),ls_sql)

//Below are samples for calling NVO (instead of dotnetobject)
//public function string of_getdatabyds(OleObject aado_ado,string as_sql,boolean abln_native)
//eon_pbcsharp  leon_pbcsharp
//leon_pbcsharp = create eon_pbcsharp
//ls_result = leon_pbcsharp.of_getdatabyds(sqlca.getadoconnection(),ls_sql,false)

//Creates datawindow
wf_createdw(ls_sql)
//Shows result
dw_1.importjsonbykey( ls_result)
destroy lcs_obj
destroy lcs_ass

Here is a sample that C# code shares its connection to a SQL Server database with PowerScript code, and PowerScript code retrieves data successfully using the shared connection.

Sample C# function that returns the connection:

public IAdoConnectionProxy GetpostgreConnection()
{
    string connection = "";
    connection = "Data Source=Localhost;Initial Catalog=adventureworks;User ID=sa;Password=admin;";
    SqlConnection sqlcon = new SqlConnection(connection);
    sqlcon.Open();
    IAdoConnectionProxy adosql = new AdoConnectionProxy();
    adosql.Connection = sqlcon;
    return adosql;
}

Sample PowerScript code that calls the connection returned by C# code, connects with the database, and executes the static SQL queries.

//Gets SQL Server connection from C#
oleobject leon_sql   
boolean lbn_result
string ls_sql
dotnetobject lcs_obj
dotnetassembly lcs_ass
long ll_return,ll_count

lcs_obj = create dotnetobject
lcs_ass = create dotnetassembly
//Loads DLL
ll_return = lcs_ass.LoadWithDotNetFramework("resource\AppeonAssembly.dll")
if ll_return < 0 then
 messagebox("Load Failed",lcs_ass.errortext)
 return
end if
//Creates instances of dotnetobject and dotnetassembly
ll_return = lcs_ass.createinstance("AppeonAssembly.PBCsharpTrans",lcs_obj,false)
if ll_return < 0 then
 messagebox("createinstance failed",lcs_ass.errortext)
 return
end if

leon_sql   = create oleobject
//Specifies transaction object to ADO
SQLCA.DBMS = "ADO.Net"
SQLCA.DBParm = "Namespace='test123'"
//Gets ADO connection info from C#
leon_sql   = lcs_obj.GetpostgreConnection()

//Below are samples for calling NVO (instead of dotnetobject)
//public function OleObject of_getsqlserverconnection()
//eon_pbcsharp  leon_pbcsharp
//leon_pbcsharp = create eon_pbcsharp
//leon_sql   = leon_pbcsharp.of_getsqlserverconnection ()

//Connects with the database
lbn_result = sqlca.setadoconnection( leon_sql  )
if lbn_result then
 connect;
 if sqlca.sqlcode <> 0 then
  messagebox("Error",sqlca.sqlerrtext)
  return
 end if
end if
//Checks result
select count(*) into :ll_count from customer;
messagebox("customer count ",string(ll_count))