Helping Developers Master PowerBuilder Classic and .NET

Yakov Werde

Subscribe to Yakov Werde: eMailAlertsEmail Alerts
Get Yakov Werde: homepageHomepage mobileMobile rssRSS facebookFacebook twitterTwitter linkedinLinkedIn

Related Topics: Enterprise Architecture, Enterprise Application Performance


The Missing Link

Creating efficient, maintainable, partitioned applications

Application partitioning is a hot topic lately. If you've been following my articles in this publication and in ISUG Journal, you are no doubt beginning to get the picture of what application partitioning is all about. You now know that an application has three logical layers: user interface; business access and data access. You also know that the ultimate goal of partitioning in a .NET application is to extract business and data access logic into distinct sets of assemblies whose methods can be called from the GUI layer to accomplish application processing.

With reference to distinct data access assemblies, one key element was troubling me. How inefficient it would be if each assembly needed its own connection. How could multiple assemblies, each running in their own memory space, share a database connection? How could methods spread across multiple assemblies be able to participate in a single database transaction? The answer to these questions would be the missing link whose discovery would enable robust partitioned applications.

Unlike the elusive Bigfoot ape, .NET and PowerBuilder provide an "out of the box" shared connection solution. In this article I'll explain some theory behind it and show you the basic mechanics of using a shared connection in a partitioned, assembly-ized PowerScript application.

The Microsoft engineers who designed the .NET Framework thought of the shared connection problem and provided a solution to it. Their solution comes in the form of an ADO.NET Interface called IDbConnection. In the words of the Microsoft Online Library, "IDbConnection represents an open connection to a data source, and is implemented by .NET Framework data providers that access relational databases." More specifically, "The IDbConnection interface enables an inheriting class to implement a Connection class, which represents a unique session with a data source (for example, a network connection to a server)." IDbConnection is a member of the System.Data namespace. Figure 1 shows IDbConnection's members as seen from the Object Browser.

More important for developers in the PowerScript world, Sybase engineers implemented this interface in a class called ADOConnectionProxy. It's a member of the Sybase.PowerBuilder.DataSource.Sharing namespace. To view the members of the class, you must add the assembly Sybase.PowerBuilder.DataSource.Sharing as a reference to your application and navigate to it in the Solution Explorer. Figure 2 shows what you'll see. Note: If you are coding in PowerScript it is not necessary to add this reference.

With the implicit understanding that PowerScript developers use a Transaction object as their conduit to a database, Sybase engineers extended the ability of the Transaction class to manage a shared connection. The good news is that existing PowerScript database-centric coding will not need modification to work with shared connections. Good ol' SQLCA lives on! Plus even better news is that your PowerScript code does not even need to know about the existence of IDbConnection or the ADOConnectionProxy.

Using this class, it is possible to pass a connection, including transactional state, between PowerBuilder and C# assemblies. The C# code would add a reference to the Sybase Assembly and interact with the members of the class. In the balance of this article, I'm going to focus on the basics of a pure PowerBuilder solution.

Documentation note: As of this writing the documentation for ADOConnectionProxy can only be found in the PowerBuilder .NET Features Guide, section titled Database Management in PowerBuilder.NET. The new methods and abilities have not filtered down into the online help or the Object and Controls book Transaction object section.

Process by Example
This simplified example has two targets: a WPF GUI and an assembly. Figure 3 shows my solution. The GUI will open a database connection and then pass it to a method in the assembly. The assembly will use the connection it receives to issue a SQL select and send the result back to the caller.

In the event the connection or SQL statement fails, it will throw an exception. When you look at the code, it is almost no-brainer simple.

First and foremost, you must be connecting to your database using the PowerBuilder ADO.NET driver. If you don't already have one, you will need to install the ADO.NET provider assemblies for your DBMS. (In case you don't know what a provider is: The ADO.NET Data Provider model provides a common managed interface in the .NET Framework for connecting to and interacting with a data store. There is a provider for most every modern DBMS.) In our little proof-of-concept example we will use the System.Data.ODBC provider. Figure 4 shows my DBProfile setup which uses ODBC to connect to an ASA Engine hosting the EAS Demo DB V120 database. Listing 1 shows the runtime connection code

Note: I wanted to demonstrate my result by looking at the Trace database output. However, at the time of this writing, database tracing using the TRACE ADO.NET driver did not open a trace file. I don't know if this is a feature or a flaw. I reported this issue to engineering.

As mentioned earlier, Sybase engineers extended the ability of the Transaction class to manage shared connections. They added two methods to support a shared connection. The first method GetAdoConnection( ) returns a live ADOConnectionProxy object from a connection maintained by a Transaction object. The second method, SetAdoConnection(), applies an ADOConnectionProxy to a Transaction object.

Listing 2 shows the caller's code in the WPF application. It instantiates the assembly, gets the database connection and passes it to the assembly on a method call.

Listing 3 shows the assembly method. Note, that in order to avoid adding the reference to the Sybase.PowerBuilder.DataSource.Sharing assembly, the parameter is passed using a System.Object ancestor type reference. Also note that if something goes wrong with any database-centric code, the method will notify the caller by throwing an exception.

Pretty simple, eh? Building on this technique, I believe it will be possible to create efficient, maintainable, partitioned applications.

Long Live PowerBuilder!

More Stories By Yakov Werde

Yakov Werde, a 25 year IT industry veteran, is a member of TeamSybase and the newly formed Sybase Customer Evangelist Team. Yakov is a recognized author, speaker and trainer who has been designing and delivering PowerBuilder, .NET, EaServer, Web App Development, and Java training for over 14 years to corporate, military and government developers. Prior to discovering his aptitude as an educator, Yakov worked as an architect, project manager and application coder in the trenches of application software development. Yakov holds a Masters in Education with a specialty in instructional design for online learning from Capella University and a BS in math and computer science from Florida International University. Yakov, managing partner of eLearnIT LLC (, authors and delivers workshops and web based eLearning tutorials to guide professional developers toward PowerBuilder Classic and .NET mastery. Follow Yakov on Twitter as @eLearnPB

Comments (0)

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.