PowerBuilder applications can share database ADO.NET connections with third-party .NET assemblies exposed as COM through a connection proxy. The connection proxy is an instance of type IAdoConnectionProxy.
The IAdoConnectionProxy interface is defined in the Sybase.PowerBuilder.DataSource.Sharing.dll assembly as follows:
IAdoConnectionProxy {
object Connection; //accepts System.Data.IDbConnection
object Transaction; //accepts System.Data.IDbTransaction
event EventHandler TransactionChanged;
}
Both the PowerBuilder application and the third-party assembly manage connections and transactions by referencing the proxy.
The assembly must be registered as COM by using regasm.exe under the Microsoft.NET\Framework\v4.0 folder. Please refer to the Microsoft MSDN library for information about regasm.exe.
The PowerBuilder Transaction object is the standard PowerBuilder nonvisual object used in database connections. To manage the shared connection, the Transaction object references the AdoConnectionProxy object using these methods:
-
bool SetAdoConnection (oleobject connectionProxy) -- accepts an imported ADO.NET connection.
-
oleobject GetAdoConnection() -- accepts an ADO.NET connection exported from the Transaction object.
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
//Sample PowerScript code
SQLCA.DBMS = "ADO.NET"
SQLCA.AutoCommit = true
SQLCA.DBParm = "Namespace='System.Data.Odbc', DataSource='SQL Anywhere 11 Demo'"
bool retVal = SQLCA.SetAdoConnection(emp.AdoConnectionProxy)
// emp is an instance of a type in the 3rd-party .NET assembly
if (retVal = true) then
connect using SQLCA;
// db operations
end if
Sample C# Code
Here is an example of C# code in the third-party assembly:
public class Emp {
private IDbConnection conn;
private IDbTransaction trans;
...
private IAdoConnectionProxy proxy;
...
public object AdoConnectionProxy {
get {
//disposing/clean-up actions.
if (null == proxy) {
proxy = new AdoConnectionProxy();
}
proxy.Connection = conn;
proxy.Transaction = trans;
return proxy;
}
set {
//disposing/clean-up actions.
proxy = value as IAdoConnectionProxy;
if (null != proxy) {
if (conn != proxy.Connection as IDbConnection)
this.Disconnect();
conn = proxy.Connection as IDbConnection;
trans = proxy.Transaction as IDbTransaction;
proxy.TransactionChanged += new
EventHandler(proxy_TransactionChanged);
} else {
//disposing/clean-up actions.
}
}
}
...
}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
//Sample PowerScript code SQLCA.DBMS = "ADO.NET" SQLCA.AutoCommit = false SQLCA.DBParm = "Namespace='System.Data.Odbc', DataSource='SQL Anywhere 11 Demo'" Connect Using SQLCA; emp.ConnectionProxy = SQLCA.GetAdoConnection() // db operations disconnect using SQLCA;
Sample C# Code
Here is an example of C# code in the third-party assembly:
// Manage the transaction
public class Emp {
...
IAdoConnectionProxy proxy;
IDbTransaction trans;
...
public object ConnectionProxy {
get { return proxy; }
set {
proxy = value as IAdoConnectionProxy;
...
proxy.TransactionChanged += new
EventHandler(proxy_TransactionChanged);
}
}
void proxy_TransactionChanged(object sender, EventArgs e) {
...
trans = sender as IDbTransaction;
...
}
...
}