Technology and Community

Jim O'Neil

Subscribe to Jim O'Neil: eMailAlertsEmail Alerts
Get Jim O'Neil: homepageHomepage mobileMobile rssRSS facebookFacebook twitterTwitter linkedinLinkedIn

PowerBuilder: Article

The Debut of DataWindow.NET

Building your first DataWindow application in C#

In Part 1 we looked at the overall architecture of DataWindow.NET (PBDJ, Vol. 11, issue 4); in Part 2 we'll start coding our first application. We'll code a simple client application that contains two DataWindows with a master-detail relationship using the Department and Employee tables from the PowerBuilder 10 Demo Database (EAS Demo DB V10). For simplicity's sake, we'll provide the option to update only the employee salaries, but obviously the application could be easily extended to allow for inserting and deleting employees as well as departments.

Creating the DataWindow Objects

Begin by creating the master and detail DataWindow objects using either PowerBuilder or DataWindow Designer. The master DataWindow, d_department, retrieves from the Department table and has a simple select statement as its source. The detail DataWindow, d_employee, retrieves information from the Employee table using the ID of the currently selected department from the master DataWindow as the retrieval argument. d_employee also makes use of two computed fields - one involving an aggregate sum - as well as an edit-mask input format on the salary column, a validation criterion preventing the entry of negative salary values, and an expression to cause the detail lines to alternate color from white to a pale yellow as a visual aid. The two DataWindow objects are portrayed in Figure 1 and Figure 2.

Including the DataWindow Assemblies in Your .NET Development Tool

After saving these DataWindow objects in a library (PBL file), you're ready to start your .NET development tool and begin coding the application. For this example, I used Visual Studio .NET 2003 and wrote code in C#; however, any development environment that supports the Microsoft .NET Framework 1.1 will suffice. If you use Visual Studio .NET 2003, you have the added benefit that the DataWindowControl, DataStore, and Transaction classes are automatically included in your Toolbox as part of the product installation.

For Borland's C#Builder users, the classes can be easily installed by navigating to the DataWindow.DLL from within the .NET Installed Components menu option. Keep in mind that DataWindowInterop.DLL (and PBDATA100.DLL if used) is a dependent assembly so it must be available in one of the directories specified in the list of Assembly Search Paths as shown in Figure 3.

In Microsoft Visual Studio .NET, start your new .NET application by creating a new solution and a new C# Windows application; in this case, I've named the project MyFirstDWNetApplication. By default, a blank form will be created and your Visual Studio .NET environment should look something like Figure 4 (depending upon your layout preferences).

At this point you can choose to add the DataWindow assemblies to the list of references for this project. This should not be required if the controls have been successfully installed in your Toolbox; however, should you have the need to add the references explicitly, you can right-mouse click on the References item in the Solution Explorer tree view and select the option to Add Reference.... From the resulting dialog, browse to the location where you installed the product and select the DataWindow.DLL and DataWindowInterop.DLL files.

Creating the Main Form

To start designing the form, create two DataWindow controls by selecting the DataWindowControl option from the Sybase DataWindow tab of the Toolbox. You'll notice a stock bitmap displays within the control, since a DataWindow object has not been assigned yet. At this point, the references to the DataWindow assemblies will also be added automatically to your project if they were not already in place. Next, add a Transaction object to the form. Since this is a nonvisual object, you'll see a representation of the object below the form that allows you to select it and set properties within the development environment. Figure 5 shows the form after a bit of cosmetic work, including some command buttons for which we shall write code shortly. At this point we still haven't assigned a DataWindow object to either of the DataWindow controls on the form, so that will be our next step.

Specifying the DataWindowControl Properties
Just as in PowerBuilder, DataWindow objects reside in a PowerBuilder Library (PBL). When you create a PowerBuilder application, the application object includes a library list as a property; however, in a .NET application the LibraryList property applies to each DataWindowControl or DataStore that you include within your application. You can assign the library list via the Properties pane or programmatically via the LibraryList property defined on the IDataStore interface. In this case, we'll define the library list in the Properties pane by selecting the LibraryList property and clicking the ellipsis button that appears in the value field (see Figure 6).

Selecting that button brings up a modal dialog through which you can browse individually to each of the PBLs that contain DataWindow objects you'll be using in the application. For both of the DataWindow controls on this form, browse to the PBL in which you saved the DataWindow objects from either PowerBuilder 10 or DataWindow Designer. Once you have specified the library list, you can select the DataWindowObject property and its ellipsis button to list all the DataWindow objects in the configured library list in another modal dialog. For the top DataWindow, select d_department, and for the bottom, select d_employee. Once you've done that, you'll see that the stock bitmap image is replaced with a representation of the DataWindow object you selected, just as in PowerBuilder.

Populating the Database Connection Parameters
The next step is to assign properties to the Transaction object. We could also do this in code, but using the Properties pane is sufficient for this introductory example. Select the transaction1 object reference that appears below the representation of the form to bring up the properties for that object. Since we are using the PowerBuilder sample demo database, we only need to provide the Dbms value (the enumerated value of Odbc) and the DbParameter (the string value "ConnectString='DSN=EAS Demo DB V10;UID=dba;PWD=sql;"). At this point, also rename the object from transaction1 to sqlca. Your Visual Studio .NET environment should now look something like Figure 6. Note that within the Properties pane there is a hyperlink through which you can test the connectivity to the database based on the property values you specified for the Transaction object.

Writing the Code

Now let's get to some coding! To reduce your typing requirements, first add a reference to the Sybase.DataWindow namespace at the top of the code module for the form as follows:

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using Sybase.DataWindow;

This allows you to avoid having to preface every instance of a class or interface with the Sybase.DataWindow namespace identifier.

Form Initialization
When the form initializes we'll want to connect to the database and retrieve the department list. In addition, we'll select the first department in the list and populate the detail window with the employees in that department, all before the form is displayed. One obvious place to do this is in the Loaded event of the form. The code to do this in Listing 1 should be straightforward for PowerBuilder developers with the possible exception of the try-catch construct. Of all the DataWindow methods coded in this method, only the Retrieve actually returns a value (the number of rows in the DataWindow); the other methods will either succeed or throw an exception. In this case, the code is set up to catch the most likely exceptions: DbErrorException, the analog of the PowerBuilder DBError event, and TransactionException, which will occur if the Connect call fails. In this example, any exception will result in a message box and then application termination.

Assuming the code in Listing 1 runs without raising an exception, the form should appear with a list of departments displayed in the top DataWindow control and the list of employees, sorted alphabetically, in that department in the bottom DataWindow control. To complete the example, we have a number of remaining programming tasks to carry out:

  1. When the Update Changes button is pressed, update the salary modifications to the database.
  2. When the Cancel Changes button is pressed, refresh the employees for that department and discard all changes that were in progress.
  3. When a new department is selected, retrieve the list of employees for that department, but prompt the user to save first if there are any pending changes for the current department.
  4. Don't allow the user to terminate the application if there are unsaved changes. To simplify coding for this example, just deny terminating the application until the user presses either the Update Changes or Cancel Changes button to save or discard pending modifications.
  5. When the Exit command button or the Close button at the top of the form is clicked, end the application but only if there are no pending changes as handled by step 4.

More Stories By Jim O'Neil

Jim is a Technology Evangelist for Microsoft who covers the Northeast District, namely, New England and upstate New York. He is focused on engaging with the development community in the area through user groups, code camps, BarCamps, Microsoft-sponsored events, etc., and just in general serve as ambassador for Microsoft. Since 2009, Jim has been focusing on software development scenarios using cloud computing and Windows Azure. You can follow Jim on Twitter at @jimoneil

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.