Welcome!

PowerBuilder Authors: Chris Pollach, Yeshim Deniz, Jayaram Krishnaswamy, Kevin Benedict, Avi Rosenthal

Related Topics: PowerBuilder

PowerBuilder: Article

DataWindow.NET Pet Shop

It's easy and productive

There is a huge potential downside to using the Native drivers though. Unlike ADO.NET, the Native drivers don't have any built-in capability for connection caching. Oracle does provide some capability for this within the native OCI layer. But if something like that is not implemented, each of those Connects and Disconnects will be performing a literal connect and disconnect from the database, which can be a rather significant performance liability.

The .NET Pet Shop application is also set up so that the Order transactions can be issued against a different database than the other database interactions (a distributed transaction). The .NET Framework provides support for automatically handling coordinated commits and rollbacks from such distributed transactions. If you choose to use the Native drivers and require support for distributed transactions, you will need to develop such support yourself.

Rather than using the DataWindow Transaction class for our transaction object for the DataWindows, we can use the AdoTransaction class instead to use ADO.Net drivers and gain all the additional functionality provided by ADO.NET. To do so, though, we'll need to start dealing with additional Connection and Transaction classes.

To use ADO.NET drivers, we first open a standard OleDbConnection to the database (see Listing 8). Then we create our transaction from the AdoTransaction class, passing in the Connection we just opened, and then BindConnection the connection to the transaction:

AdoTransaction SQLCA = new AdoTransaction(conn);
SQLCA.BindConnection();

For those connections where we will be performing a database update operation with the ASA provider, we need to assign the Transaction property of the AdoTransaction class by issuing a BeginTransaction on the Connection:

SQLCA.Transaction = SQLCA.Connection.BeginTransaction();

That was not required for the Oracle provider, and in fact caused an exception if included.

With AdoTransaction we normally issue the Commit through the Transaction member of the AdoTransaction class and disconnect from the database through the Connection member of that same class:

SQLCA.Transaction.Commit() ;
   SQLCA.Connection.Close();

However, because the application is configured as a distributed transaction, the commits are being handled for us. So you'll see in the source code that the SQLCA.Tranasction.Commit statements are actually commented out in the ADO.NET-based DALs. In an application where you aren't implementing a distributed transaction, you'll want to include those commits.

The one last thing you'll need to make sure of if you're using the Oracle ADO DAL I've created is that you have the Oracle Services for Microsoft Transaction Server running (it shows up as OracleMTSRecoveryService in my services list). Otherwise the Oracle ADO provider will throw an exception on the second connection open attempt in a distributed transaction.

Dealing with the Global Assembly Catch
Another side effect of having the application configured to support distributed transactions is that the modules that are involved in the distributed transaction have to be registered in the global assembly cache (GAC). The .NET Pet Shop already handles that in the pre- and post- build steps via the gacutil utility; we just need to modify those steps to include our additional modules. For the pre-build step, add the following to uninstall the older modules:

gacutil /u OracleDWNativeDAL\bin\$(ConfigurationName)\
   PetShop.OracleDWNativeDAL.dll
gacutil /u OracleDWADODAL\bin\$(ConfigurationName)\
   PetShop.OracleDWADODAL.dll
gacutil /u ASADAL\bin\$(ConfigurationName)\PetShop.ASADAL.dll

More Stories By Bruce Armstrong

Bruce Armstrong is a development lead with Integrated Data Services (www.get-integrated.com). A charter member of TeamSybase, he has been using PowerBuilder since version 1.0.B. He was a contributing author to SYS-CON's PowerBuilder 4.0 Secrets of the Masters and the editor of SAMs' PowerBuilder 9: Advanced Client/Server Development.

Comments (1) View Comments

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.


Most Recent Comments
Tanveer 08/29/05 11:12:27 PM EDT

Excellent article.
I have a question from where i can download source code for this article.
i dont see any link to download @http://pbdj.sys-con.com