The Missing Link

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.

Background

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!