Exporting an ADO.NET Connection to a Third-Party .NET
Assembly
To export an ADO.NET connection from a PowerBuilder application,
use the GetAdoConnection method:
|
1 |
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.
Note
Use the LoadWithDotNet method instead of LoadWithDotNetframework
or LoadWithDotNetCore, to load the .NET assembly.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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; } } |