Connecting to Your Database
Using the ADO.NET Interface

IAdoConnectionProxy {
    object Connection; //accepts System.Data.IDbConnection
    object Transaction; //accepts System.Data.IDbTransaction
    event EventHandler TransactionChanged;
}

You can import an ADO.NET connection from an external .NET assembly into a PowerBuilder application, enabling the application and the assembly to share the connection.

Use the SetAdoConnection method:

bool SetAdoConnection(oleobject proxy)

where proxy is the instance of type IAdoConnectionProxy that is passed in by the third-party assembly.

The imported connection and any transaction are assigned to the IAdoConnectionProxy instance.

The method returns true if the parameter is available (that is, the parameter is an instance of IAdoConnectionProxy or null). It returns false if the operation fails.

Start the connection after invoking SetAdoConnection.

Sample PowerScript Code

The following sample uses the Oracle database as an example to demonstrate how C# shares the database information with PB for executing ESQL in PB.

DotNetAssembly           lcs_ass
DotNetObject             lcs_obj
long                     ll_return,ll_result
boolean                  lb_return

lcs_ass = Create DotNetAssembly
lcs_obj = Create DotNetObject
//Load the .NET assembly
ll_return = lcs_ass.LoadWithDotNet("appeon.dll")
if ll_return < 0 then
    messagebox("Load Failed",lcs_ass.errortext)
    return
end if
ll_return = lcs_ass.createinstance("appeon.emp",lcs_obj)
if ll_return < 0 then
    messagebox("createinstance failed",lcs_ass.errortext)
    return;
end if
SQLCA.DBMS = "ADO.NET"
SQLCA.LogPass = "en_ora9i"
SQLCA.LogId = "en_ora9i"
SQLCA.AutoCommit = False
SQLCA.DBParm = "Provider='Oracle',DataSource='(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.3.98)(PORT = 1521))(CONNECT_DATA =(SERVICE_NAME = pdborcl)))'"
disconnect;
//Get the C# database connection information
lb_return = sqlca.setadoconnection(lcs_obj.orasql )
if lb_return then
    connect;
    if sqlca.sqlcode <> 0 then
              messagebox("Error",sqlca.sqlerrtext)
    end if
end if
//Use the C# connection to execute ESQL in PB
select count(*) into :ll_result from employee;
messagebox("Result",ll_result)

Sample C# Code

Here is an example of C# code in the third-party assembly:

public class Emp
{
        public IAdoConnectionProxy orasql { get; set; }
       //Construct a method to send database information to orasql
        public Emp()
        {           
            OracleConnection con = new OracleConnection();
            con.ConnectionString = "Data Source=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.3.98)(PORT = 1521))(CONNECT_DATA =(SERVICE_NAME = pdborcl)));User Id=en_ora9i;Password=en_ora9i";
            OracleCommand sql = new OracleCommand();
            con.Open();
            IAdoConnectionProxy ado = new AdoConnectionProxy();
            ado.Connection = con;
            orasql = ado;
        }
}

To export an ADO.NET connection from a PowerBuilder application, use the GetAdoConnection method:

oleobject GetAdoConnection()

The method returns an instance of IAdoConnectionProxy. The proxy's ADO connection object is assigned to property IAdoConnectionProxy.Connection.

When a transaction starts, the proxy's active Transaction object is assigned to property IAdoConnectionProxy.Transaction, and AutoCommit is false. When AutoCommit is true, the exported IAdoConnectionProxy.Transaction is null.

The method returns null if the connection fails, and false if the operation fails.

To use the shared connection, your third-party assembly must reference the exported connection proxy and manage the transaction. To be notified when the active transaction is changed, you can subscribe the IAdoConnection.TransactionChanged event. Remember to close the connection.

Sample PowerScript Code

The following sample uses the Oracle database as an example to demonstrate how PB shares the database information with C# and C# executes ESQL and returns the result to PB.

DotNetAssembly      lcs_ass
DotNetObject        lcs_obj
long                ll_return,ll_result
boolean             lb_return

lcs_ass = Create DotNetAssembly
lcs_obj = Create DotNetObject
//Create the PB transaction
SQLCA.DBMS = "ADO.NET"
SQLCA.LogPass = "en_ora9i"
SQLCA.LogId = "en_ora9i"
SQLCA.AutoCommit = False
SQLCA.DBParm = "Provider='Oracle',DataSource='(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.3.98)(PORT = 1521))(CONNECT_DATA =(SERVICE_NAME = pdborcl)))'"
connect using sqlca;
//Load the .NET assembly
ll_return = lcs_ass.LoadWithDotNet("OraClient_net.dll")
if ll_return < 0 then
         messagebox("Load Failed",lcs_ass.errortext)
         return
end if
ll_return = lcs_ass.createinstance("appeon.emp",lcs_obj)
if ll_return < 0 then
         messagebox("createinstance failed",lcs_ass.errortext)
         return;
end if
//Share the transaction to C# execution and get the return result
ll_result = lcs_obj.getemployees(sqlca.getadoconnection())
messagebox("PBToCsharpTrans",ll_result)

Sample C# Code

Here is an example of C# code in the third-party assembly:

public class Emp
{
        public IAdoConnectionProxy orasql { get; set; }
        //Get the database information from PB and execute SQL
        public int GetEmployees(IAdoConnectionProxy ado)
        {
            OracleConnection con = (OracleConnection)ado.Connection;
            OracleTransaction tran = (OracleTransaction)ado.Transaction;
            int result;
            OracleCommand sql = new OracleCommand();
            sql.CommandType = CommandType.Text;
            sql.CommandText = "select count(*) from employee";
            sql.Connection = con;
            sql.Transaction = tran;
            var re = sql.ExecuteScalar();
            result = (int)Convert.ChangeType(re, typeof(int));
            return result;
        }
}

For C# code to support sharing ADO.NET database connections, the following NuGet package and DLL file are required: