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

That's right: lots and lots of embedded SQL. I've been using the DataWindow for so long that this seems like a trip back to the stone ages. It means having to provide not only the SELECT statement, but the INSERT, UPDATE, and DELETE statements as well. The DataWindow generates those other statements for you automatically based on the SELECT statement. It also means (and it's not implemented in the .NET Pet Shop) that if you want optimistic concurrency, you're going to have to manually code an update statement that refers to every updateable column and compares the original value with the value currently in the database. That's something else that the DataWindow handles for you automatically (creating that statement, you still have to code for recovery from an update collision).

How DataWindow.NET Changes Things
With the original DALs, whenever a SQL statement requires one or more arguments, an appropriately typed parameter object has to be created and assigned with the value. For example, in the Product class the GetProductsByCategory method has to create a parameter object to represent the category and then assign the passed-in category to it:

OracleParameter parm =
new OracleParameter(PARM_CATEGORY, OracleType.Char, 10);
parm.Value = category;

then that parameter is passed to a helper class that executes the statement:

OracleDataReader rdr =
OraHelper.ExecuteReader(OraHelper.CONN_STRING_NON_DTC, CommandType.Text,

Internally, that helper class performs a PrepareCommand and then an ExecuteReader to get a reader object to pass back to the original class.

Finally - back in the Product class - the values returned are pulled off the result set by position, not by name:

while (rdr.Read()){
ProductInfo product =
new ProductInfo(rdr.GetString(0), rdr.GetString(1),null);
return productsByCategory;

Here rdr.GetString(0) and rdr.GetString(1) represent the ProductID and Name of the product and the null is for the product description. But you wouldn't know that unless you went back and examined the string constant for the original query.

Let's compare that with a DataWindow.NET approach. First, we create a DataWindow object that performs the actual retrieve from the database using DataWindow designer, and then create a local DataStore object to host it. Then we only need to assign a connection to it and retrieve it using the category that was passed in directly:

DataStore productDataStore =
new DataStore ( "OracleDWNativeDAL.pbl", "d_product_category" );
productDataStore.SetTransaction ( SQLCA ) ;
rows = productDataStore.Retrieve ( category ) ;
SQLCA.Disconnect() ;

Note that I'm closing the connection immediately after the retrieve. We don't need to hold the connection open while we process the results as the original DAL does. (A SQLDataReader can close the connection automatically, but it does so only when you close the reader after handling the data.)

Pulling the values back from the result set is even simpler. We just loop through the rows in the result set and access the values by name, not by position. (It's possible to retrieve them by position as well; it's just not commonly done.)

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.