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

and the following to the post-build step to install the new modules:

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

Selecting the DAL to Use
How does the application know which of the DALs it's supposed to use? It does that based on a couple of parameters in the Web.config file. Look in particular for entries called WebDAL and OrderDAL in the appSettings subsection of the configuration section:

     <add key="WebDAL" value="PetShop.ASADAL" />
     <add key="OrdersDAL" value="PetShop.ASADAL" />

Simply change the reference there to the DAL layer you wish to use. Note that two different DALs can be referenced - utilizing two entirely different databases - which is why the distributed transaction feature is so important.

Some Necessary Tweaks
A couple of minor tweaks were required to get the DataWindow.NET Pet Shop working correctly. The first item is a bit minor: when Microsoft created the ORDERNUM sequence used to automatically generate the order numbers for the Oracle database, they set the minimum value to 1 and an increment value of 1 (no surprise there). But they created it with a cache factor of 10000 and a maximum value of 1 E+27 (one with 27 zeros behind it)! Perhaps Amazon.com needs a cache and maximum value in that range, but a mom and pop type pet store shouldn't (and it could be adjusted upward later if they did). Note that Oracle will throw away all of the sequence values in the cache each time the database is restarted. Since I'm only doing demos, I eliminated the cache for the sequence entirely.

The second item was a much bigger issue. The ORDERS table that Microsoft created in the MSPETSHOPORDERS schema was created with a COURIER column and a LOCALE column that were both marked NOT NULL and did not have DEFAULT values. However, there is no place in the .NET Pet Shop code where the SQL used to create the orders attempts to populate those columns. You have to wonder how much testing was done, or if there weren't some modifications to the program underway when the sample was released. Without providing a value for those columns, assigning a default value, or changing them to nullable columns, it would not be possible to actually create orders with the code as provided. I dealt with this by assigning default values for the columns within the DataWindow object used to access that table.

Finally, the database scripts provided with .NET Pet Shop don't create indexes for foreign keys. For Oracle the worse result might be poor performance on joins between the tables. Adaptive Server Anywhere, however, had problems inserting records into the order table without those indexes defined. You'll find that the database creation scripts I've provided for Adaptive Server Anywhere include those additional indexes not installed by the scripts for the other databases.

Using DataWindow.NET technology in the data access layer for the .NET Pet Shop has provided a number of benefits, including:

  1. Making the source code more self-describing by allowing references to the data in the result set by column name rather than position.
  2. Reducing the amount of literal SQL we needed to script, as we only need to define the SELECT statements against the tables in question.
  3. Reducing the amount of effort and code required to prepare SQL being sent to the database, in particular eliminating the requirement to create parameter objects for each and every value being transmitted to the database.
  4. Reducing the amount of work needed to address database vendor-specific differences in implementation.
  5. Allowing for optimistic concurrency on updates with little additional code (although this was not demonstrated in this particular example).

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