Advanced DB2 for i Data Access Techniques with .NET

There are a lot of shops like mine where one or two people are responsible for everything and we are very busy trying to learn new technologies and push the business forward. My project is very large. It will eventually replace 90 percent of the green-screen menu driven system we currently use. I am working on this project alone and building it slowly from the ground up. I want to have a well-built base. I want to understand how .NET and DB2 work together before I start building a lot on top of the base program. I am working with a few iSeries files to see how things work in the grids and forms. I am looking for some help with a couple of issues:

1. Physical and Logical Files–Our iSeries files are built mainly from DDS. Some physical files do not even have a key, much less a unique key. VB.NET is pretty much forcing me to have a unique identifier on the file in order to enforce integrity through the data adapters. But our logical files are not always unique (like our history files). So, what do we do to get them into the VB application and possibly update them? Do we rebuild them using SQL statements and force a unique identifier at the end of the file? I haven’t had much luck with that one in my testing. Do we create new logicals built specifically for VB.NET? That’s a headache.

Also, there’s the composite key situation. I haven’t had any luck building data adapters over files that have composite keys. I had one guy tell me to only use the physical files from the iSeries and not use any logicals. He also said I needed to set up business logic to handle all DB2 transactions. That is way beyond my scope.

2. Joined Files–I have a need to join three files and display pieces of each in a grid for the user to decide if commission payments will be issued on these sales. The user will decide and mark a checkbox on the grid row. I want users to update one of the files (ARTRAN) that contains a flag to tell the system to pay commissions on the sale. I got to the checkbox part and it all looked great until I found out there was no way to push the updates back through the dataset to DB2. I scrapped it and started over. (This is my life story for the past year with VB and DB2.)

I have to say that I do so many other things during the day I have a hard time keeping my focus on VB.NET. It takes a lot of time to wade through the Internet for more information. Any insight you can give will be helpful.

For the record, we’re using Visual Studio 2008 with the IBM .NET Data provider.

–Duane

Note: The code accompanying this article is available for download here.

Hi Duane,

I feel your pain with your struggles of keeping up with .NET development techniques! It’s tough staying abreast of business rules, current technology, and new technology. So I’ll do my best to answer your questions. For readers who are unfamiliar with the .NET Web environment and the concept of using a grid control to update a database table, I suggest reviewing my article System i Developers and .NET 2.0: ASP.NET and the Declarative Programming Model. This article gives a general introduction to Microsoft‘s Visual Studio IDE (version 2005 or higher) for developing Web applications. In particular, it illustrates how to develop a Web page that uses a special control called the “GridView” to easily update a DB2 database table from a Web browser based application.

Now to the issues! Having an actual unique key defined on a table is not required for doing database updates from a .NET Grid Control. The unique key requirement that you often hear about really refers to having a unique identifier on one or more columns in the table. If your table has one or more fields that can uniquely identify a row, then you can safely update it from a .NET Grid control even if the database doesn’t enforce the uniqueness. However, if possible, I recommend building a unique key on every table. Having a unique index or primary key constraint will give you the benefits of better query optimization and will allow some of the .NET data wizards to automatically identify the unique key columns for you. (Note that with legacy applications this is not always possible.)

Even if a table doesn’t have uniquely identifiable rows it can still be updated from a .NET Grid application, but you may end up inadvertently more rows than anticipated. As for updating tables with composite keys (keys containing more than one field or column), .NET can handle them as I will illustrate in my sample code.

With respect to physical and logical files, when using SQL (regardless of the interface whether .NET, JDBC, ODBC, OLE DB, green screen, etc.) I would recommend using physical files whenever possible. When you write an SQL statement against a logical file, the DB2 optimizer goes back to the physical file and analyzes it and all logical files and indexes in an attempt to find the best way to extract the requested data. In other words, just because you request data from a logical file doesn’t necessarily mean DB2 is going to pull the data directly from that file! Further, depending on your OS version, queries that end up using logical files in the query’s access plan are submitted to the older classic query engine (CQE), which doesn’t have all of the optimization and monitoring advantages of the newer SQL Query Engine (SQE).

Now we need to cover how to join three tables and display information from each table in a grid control on a Web page. This is easy enough to do with the exception of the requirement that one of the tables has an updateable column. This is where the .NET wizards will fail you because not all database engines allow updates against queries with multiple joins so the wizards don’t even bother offering update capabilities. The good news is that this is still relatively easy as long as you’re familiar with writing SQL.

To illustrate how to accomplish this query from three tables and update one table, I’ve created three simplified tables representing real tables in Duane’s application The table names are ARTRAN (A/R transactions), COSTTRAN (cost transactions), and TERRITORY (containing territory codes and names). In my contrived sample, the ARTRAN and COSTTRAN tables are related by three columns: COMPANY, CUSTOMER, and INVOICE. The TERRITORY table is related to the COSTTRAN table by a common column called TERR. The three tables are joined to allow a user to compare the cost of an invoice vs. the invoice amount in order to determine if a commission should be paid. The updateable “pay commission” flag is called PAYCOMM and is in table ARTRAN. For convenience, the rows are sorted by territory name.

You can re-create these table definitions and sample data with this SQL Script. I placed my sample data in a library called i3.

To demonstrate how the Microsoft GridView control would handle this situation, I created a sample ASP.NET page and placed it in a Word document that you can view here. The good news is that this scenario can be done using the .NET declarative programming model, which requires no .NET code, no C#, no VB, no nothing!

The trick to this method is to use an SqlDataSource control that will handle the communication between .NET and DB2. Recall that the SqlDataSource control has four very important properties:

• SelectCommand–a SQL statement used to retrieve data when a query request is made
• UpdateCommand–a SQL statement used to update data when an update request is made
• InsertCommand–a SQL statement used to insert data when an insert request is made
• DeleteCommand–a SQL statement used to delete data when a delete request is made

Each of the properties stores an SQL statement, which can be a SELECT, UPDATE, INSERT, DELETE, or a stored procedure call. Each of these statements can have one or more parameters. In this example, we only need to select data to populate our grid and update data to set the “pay commission” flag, so we will only fill in the SelectCommand and UpdateCommand properties. Once the SQL Data Source is defined properly, the GridView control will interact (almost automatically) with the SqlDataSource control for requesting data and performing updates as the user changes the data.

Let’s examine a few important aspects of the SqlDataSource definition:

        <asp:SqlDataSource ID="SqlDataSource1" 
		runat="server" 
            ConnectionString="DataSource=AS400.i3.local;User ID=myuser;
		Password=mypassword;LibraryList=I3;Naming=System"
            ProviderName="IBM.Data.DB2.iSeries" 
            SelectCommand="
Select T.TERRNAME,AR.COMPANY,AR.CUSTOMER,
       AR.INVOICE,AR.INVDATE,AR.INVAMT,
       PAYAMT,INVCOST,(INVAMT-INVCOST)/INVAMT As Est_Profit,
       SLSREP,Cast(Case When PayComm='0' Then 0 Else 1 End As SmallInt)
	   As PayComm
  From ARTRAN AR
  Join COSTTRAN Cost On Cost.COMPANY=AR.COMPANY
                    And Cost.CUSTOMER=AR.CUSTOMER
                    And Cost.INVOICE=AR.INVOICE
  Join TERRITORY T On T.TERR=Cost.TERR                    
 Where INVAMT>0
 Order By TERRNAME,INVOICE" 
            UpdateCommand="
 Update ARTran
    Set PAYCOMM=Case When Cast(? As VarChar(10))='True' Then '1' Else '0' End
  Where COMPANY=?
    And CUSTOMER=?  
    And INVOICE=?">
            <UpdateParameters>
                <asp:Parameter Name="PAYCOMM" Type="String" />
                <asp:Parameter Name="COMPANY" Type="Decimal" />
                <asp:Parameter Name="CUSTOMER" Type="Decimal" />
                <asp:Paramete Name="INVOICE" Type="Decimal" />
            </UpdateParameters>
        </asp:SqlDataSource>

First, note the ConnectionString and ProviderName properties are populated. I’m using the DB2 data provider for the iSeries and have populated the connection string to talk to our host (as400.i3.local). Normally these settings would be stored in the Web.Config file, but for simplicity I defined them in the Web page itself. Of course, substitute your own connection and library list information.

Second, note the SelectCommand property is defined with a simple query that joins the three tables together. A more sophisticated example would probably limit the amount of data returned using a parameterized criteria restriction.

Also, the UpdateCommand property is populated with a parameterized UPDATE statement. In this statement, we’re updating the PAYCOMM (pay commission) flag, which will be attached to a checkbox column in the GridView. This sample assumes that a composite key consisting of COMPANY, CUSTOMER, and INVOICE will be able to uniquely identify a row in this table. Parameter markers are used in the SET and WHERE clauses. Further, an UPDATEPARAMETERS collection is also defined to tell .NET how to pass these parameters to DB2 when the user actually updates data in the GridView control. In case you’re wondering, these statement parameters will be populated with information supplied from the GridView control from the particular row the user is editing. I’ll illustrate how to connect the GridView to the SqlDataSource a little later.

One special thing to note in this example is the implementation of a boolean checkbox on the grid. Typically, legacy database tables define a boolean field as a CHAR(1) column that can hold value pairs such as ‘Y’/’N’ or ‘1’/’0′. However, we don’t want to show these values in the .NET world. We just want the user to check or uncheck a checkbox. In order to do this, we need to implement a special conversion in our SQL.

My sample assumes the PAYCOMM boolean column can hold a ‘0’ (no) or ‘1’ (yes). On the SELECT statement, I coded an expression to return the data as a small integer, which .NET can convert to its own boolean format:

Cast(Case When PayComm='0' Then 0 Else 1 End As SmallInt) As PayComm

However, when the GridView control gives data back to the SqlDataSource (which is responsible for sending the data to DB2), I’ve defined the PAYCOMM parameter in the UPDATE statement to represent the checkbox value from the DataGrid as a string. In the .NET world, boolean values are represented as strings with the values of ‘True’ or ‘False’. Therefore, in the Update statement, these string values from .NET need to be converted back to the original legacy values of ‘0’ or ‘1’ as follows:

Set PAYCOMM=Case When Cast(? As VarChar(10))='True' Then '1' Else '0' End

Remember, the parameter marker in the above statement will be supplied its value from a checkbox on the GridView. If the user checks the box, this parameter will contain the value ‘True’. Otherwise it will contain the value ‘False’.

The definition of the GridView control is pretty self explanatory. Here is the start of the control’s definition:

        <asp:GridView ID="GridView1" runat="server" 
	AllowPaging="True" DataKeyNames="Company,CUSTOMER,INVOICE"
            AutoGenerateColumns="False" DataSourceID="SqlDataSource1">

In order to edit or delete data from the GridView, the DatakeyNames property must contain a comma delimited list of columns that form the unique identifier for the table. Usually, if you use a .NET wizard to populate the grid from an SQL Data Source, this value will be filled in automatically if the database has a unique index or primary key constraint defined. In our case, we need to fill in these names manually. The other important property is the DataSourceID, which is set to SqlDataSource1 (our previously defined SqlDataSource control.) This property is important as it defines the linkage between the data access and the user interface (Grid). All the internal communication between these two layers is handled by .NET for us.

When reviewing the GridView’s columns, I’ve defined the columns to appear in the Grid and set them all to ReadOnly (except for the PayComm field). The PayComm column uses a special definition called a TemplateField. Normally, a string or numeric database column will display a text box by default on the GridView. However, we want to show this column as a checkbox. The TemplateField allows us to override the default behavior by specifying alternative HTML to render our column. As shown in the definition below, when a GridView row is in “edit” mode (EditItemTemplate), the user will see an editable checkbox that is bound to the database column called PayComm. Likewise, when a GridView row is in the normal “read only” mode (ItemTemplate), the user will see a locked checkbox bound to the PayComm table column.

<asp:TemplateField HeaderText="Pay Comm?" SortExpression="paycomm">
<EditItemTemplate>
    <asp:CheckBox ID="CheckBox2" runat="server" Checked='' />
</EditItemTemplate>
<ItemTemplate>
    <asp:CheckBox ID="CheckBox1" runat="server" Enabled="false" Checked='' />
    </ItemTemplate>
</asp:TemplateField>

When this Web page is displayed in a browser, it looks like this:

For the record, I created each of these tables with no keys at all, and the updates will still work correctly so long as the data rows in the table can be uniquely identified. If you are lacking a sure collection of unique row identifying columns, you risk the SqlDataSource control updating more rows than it should when it executes its Update statement.

There are all kinds of tricks you can play to get around this. For instance, you can have your query return a relative record number (using the RRN function) to the grid, which can then be passed back to an RPG stored procedure defined in the SqlDataSource’s UPDATECOMMAND. This RPG program can quickly lookup a unique table row by relative record number and update the current row.

Another possibility to consider when doing an update against a query with multiple joins is to create an SQL View of the query along with an INSTEAD OF trigger. The INSTEAD OF trigger will allow a developer to define code to make the normally read-only view updateable by specifying custom code. This method will allow the SELECT, UPDATE, INSERT, and DELETE properties to all reference the same View object. For more information on INSTEAD OF Triggers, see Perform the Impossible: Update a Multi-Table View using INSTEAD OF Triggers.

Since Duane mentioned he is using “data adapters,” I should note that my example instead uses the SQLDataSource and the .NET 2.0 compatible DB2 for iSeries .NET Managed Data Provider that comes with iSeries Access (a.k.a. System i Access) version V6R1. If you’re still using the older .NET 1.0 compatible DB2 for iSeries .NET managed provider that comes with prior versions of iSeries Access, you will need to do some manual .NET coding with the data adapters. This is because the older .NET 1.0 managed provider cannot be used with the SqlDataSource control. However, the SqlDataSource concepts shown here should be applicable as the data adapter also has similar select, update, insert, and delete commands and parameter collections.

It is important to note that one way to replace data adapters with the SQLDataSource control would be to obtain a copy of the .NET 2.0 compatible managed provider by installing V6R1 System i Access–even if you are not able to update your i/OS to 6.1. Another alternative that would allow you to use the SqlDataSource control with DB2 without upgrading iSeries Access is to use one of the IBM OLE DB providers (IBMDA400, IBMDASQL) or the IBM ODBC driver instead of using the .NET managed provider. All of these components come with the iSeries Access product.

In summary, the SqlDataSource control provides the real plumbing between .NET and DB2 for i. Since the SELECT, UPDATE, INSERT, and DELETE commands are user-defined, we can make the database update our data any way we want. We can Select data from four tables, but insert into one (or many if we call a stored procedure). Or, we can select data from one table, but call a stored procedure to change two tables; for instance, update the base data table and insert a row into an audit table.

Combining the SqlDataSource with a GridView (or other Grid control) provides an easy way to update data without having a busy one-person show coding all of the DB2 business transactions manually!

Michael Sansoterra is a programmer/analyst for i3 Business Solutions, an IT services firm based in Grand Rapids, Michigan. Send your questions or comments for Mike to Ted Holt via the IT Jungle Contact page.

RELATED STORIES

System i Developers and .NET 2.0: ASP.NET and the Declarative Programming Model

Perform the Impossible: Update a Multi-Table View using INSTEAD OF Triggers

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot