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

You'll note a couple of places in the assignment of those column values where the value being assigned is being cast as a different data type. The DataWindow object data types are a subset of the data types that the .NET Framework supports. In particular, the DataWindow/DataStore methods for assigning and retrieving data from columns in the DataWindow object handle decimals and doubles. The .NET Framework provides for several other numeric data types (e.g., Int16, Int32, etc.). The result from the SetItem<datatype> methods will have to be cast if the resulting variable type is not a decimal or double. In the same manner, a numeric value coming from a variable that is not a decimal or double data type will have to be cast to a decimal or double when the assignment is made into the DataWindow object.

ASA DAL vs Oracle DAL
Another big change resulting from using DataWindow.NET is the amount of work we don't have to do in order to account for which database we're talking to. I've added three new DALs to the .NET Pet Shop demo: one for Oracle using native drivers, one for Oracle using ADO.Net, and one for Adaptive Server Anywhere using ADO.Net. Each of those DALs has its own PBL with 16 DataWindow objects to do the data access. However, with the following exceptions, all of those DataWindows objects are exactly the same. I just copied the PBL and renamed it for each DAL. The exceptions are:

  1. In the Oracle DALs, the d_ordernum DataWindow object issues a select nextval against a sequence. In the ASA DALs that same DataWindow object issues a select for the @@identity database global variable.
  2. In the ASA DAL, the Update Specifications for the d_order DataWindow object has been modified in order to indicate that the orderid column is an autoincrementing column.
Note that these differences could have been handled in the DAL by dynamically modifying the DataWindow object properties, and one single set of DataWindow objects could be used. It's essentially a matter of determining whether you want to "hide" the database differences in the DAL code or in the DataWindow objects, and I chose to "hide" them in the DataWindow objects.

As for the DALs (aside from the code actually used to create the connection to the database), there's only one difference between the ASA and Oracle DALs, and none between the two Oracle DALs. The one difference between the ASA and Oracle DALs is when the order ID is determined. In both cases it's determined by issuing a retrieve against the ordernum DataStore:

rows = ordernumDataStore.Retrieve ( ) ;
if ( rows == 1 )
   orderId = (int)ordernumDataStore.GetItemDouble ( 1, "ordernum" ) ;

Since we use a sequence to generate the order number for Oracle, we need to get that value before we insert the order, so we retrieve the ordernum DataStore first and then assign the value of the order ID column in the order DataStore before doing the UpdateData. In the case of ASA, we're using an identity column on the order table to generate the order ID, and then retrieving that after performing the UpdateData on the order DataStore.

Using ADO.NET Rather than Native Drivers
Where more of the actual differences between the DALs come from depends on whether ADO.NET or Native drivers are used to connect to the database. In the case of Native drivers, we work exclusively with the DataWindow.NET Transaction class, first setting a number of properties on it to establish the connection with the database:

Transaction SQLCA = new Transaction() ;
SQLCA.Dbms = Sybase.DataWindow.DbmsType.Oracle9i;
SQLCA.ServerName = "oracle";
SQLCA.UserId = "mspetshop";
SQLCA.Password = "password";
SQLCA.Connect() ;

When we're done, if we've done a database update operation we issue a commit using the Transaction class and finally (whether we've done an update or not) disconnect from the database through the Transaction class:

SQLCA.Commit() ;

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)

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.