An inside look at AVR for .NET's memory file

AVR for .NET's memory file is an RPG programming interface over .NET's System.Data.DataSet. Under the covers, what really lurks in a memory file is .NET's System.Data.DataSet. The memory file surfaces this DataSet with its dataset property. The DataSet is the central nervous system of the memory file. To understand the memory file you must understand the DataSet.

The DataSet is an in-memory cache of data retrieved from a data source. In AVR for .NET, this datasource is usually an AVR for .NET memory file. The DataSet is a parent object that consists of one more DataTable objects and, optionally, one or more DataRelation objects. For most purposes, you'll have one table per dataset. However, for advanced uses (such as providing a hierarchical drill-down for summary and related detail items. Each DataTable object consists of DataColumn and DataRow objects.

AVR for .NET's memory file is an RPG programming interface over .NET's System.Data.DataSet.

Figure 1. DataSet and its primary child objects

The DataSet is most typically used to populate a grid (think "subfile," sorta) in either a Windows or ASP.NET application. However, it has many other uses, including:

a superb data "pipeline" with which to pass data from one computer to another through a Web Service method
a good way to cache data across pages in an ASP.NET application
a good way to persist data to ensure appropriate concurrent behavior is enforced for optimistic record locking during record updates

To define a memory file's schema, its layout, you must specify:

a database name in DBDesc (Note this can be either a literal database name or the name of a database object (a DclDB) defined elsewhere in the program)
a physical or logical file in the FileDesc keyword and you must specify

DclMemoryFile CustMem                             +
      DBDesc( "*Public/DG NET Local" )            +
      Prefix( Cust_ )                             + 
      FileDesc( "Examples/CMastNewL1" )           +          
      ImpOpen( *Yes )                             +
      RnmFmt( RCustMem )

It's very important to remember that the file used to describe a memory file is not used at runtime. This file is at compile-time only. It does not need to be deployed. Thus, the best practice is to create a library of files dedicated for the use of defining datasets. Think of these files as external descriptions of datagrid contents (they have the potential to be used for many more things than just datagrid contents; but because that is a primary reason to use datasets, that's a good way to think of these files).

Many AVR programmers have agonized over this need to define DataSets with external files. VB and C# let you define DataSets on the fly. AVR for .NET, because it persists RPG's externally described file model (where files must be known at compile time) must have the DataSet definition at compile time to be able to read and write to the DataSet as though it were an RP file. You'll see a trick later in this article where you can indeed, if you insist, dynamically add columns to an existing memory file's table.

DataTables

The DataSet contains one or more (usually one) DataTable. To work with the data in a DataSet, you must first select (get a reference to) the table that you want to work with. There are several ways to get a reference to a table in a DataSet:

1. You can use the ordinal position of the table as it was added to the DataSet (If you only have one table in the DataSet, this is the easiest way):

DclFld Table     System.Data.DataTable
Table = CustMem.DataSet.Tables[ 0 ]

2. You can use a hard-coded reference to the underlying file format name:

DclFld Table System.Data.DataTable
Table = CustMem.DataSet.Tables[ "RCMMastL1" ]

3. You can use an indirect reference to the underlying file format name:

DclFld Table System.Data.DataTable
DclFld FormatName *String

FormatName = CustMem.DataSet.GetFormatName( 0 )
Table = CustMem.DataSet.Tables[ FormatName ]

With a DataTable reference in hand, you can now traverse the contents of that table.

DataColumns and DataRows

Each DataTable is composed of a collection of DataColumn objects. These DataColumns define the file's layout (i.e., its fields, their types and names). The contents of each DataTable are stored in a collection of DataRows.

First, get the table reference. With it, now you're ready to work with the table's rows and columns:

Table = CustMem.DataSet.Tables[ FormatName ]

You can read a DataTable's columns with:

ForEach Col Type( DataColumn ) Collection( Table.Columns )
// Col now contains a reference to a column in the table
EndFor
You can read a DataTable's rows with:

ForEach Row Type( DataRow ) Collection( Table.Rows ) 
    // Row contains a reference to a row in the table
EndFor

To traverse every column of every row, use this:

ForEach Row Type( DataRow ) Collection( Table.Rows ) 
    ForEach Col Type( DataColumn ) Collection( Table.Columns ) 
        MsgBox Row[ Col.ColumnName ].ToString()
    EndFor        
EndFor

Note that because the DataSet is strongly typed (that is, its column data types have been explicitly established), you can reference a column's value with any of:

— a hardcoded reference to the field name:

    Row[ "CMMaster" ].ToString()

— an indirect reference to the field name (mostly likely using the ColumnName property of a DataColumn):

    Row[ Col.ColumnName ].ToString()

— the field's ordinal position (the code below fetches the fourth field's value as it was defined in its underlying data file):

    Row[ 3 ].ToString()

Remember that when you attempt to use column values, you'll need to cast them appropriately (they are parked in the column as *Objects).

Not only can you read data in this fashion from a table in a dataset, you can also change it. For example, to change all customer names to upper case, you could use this:

ForEach Row Type( DataRow ) Collection( Table.Rows ) 
    Row[ "CMName" ] = Row[ "CMName" ].ToString().ToUpper()
EndFor

Find a given row in the DataSet by value

It possible to read a row randomly from the DataSet. Actually, we should be more clear with the language here--what we're really doing is finding a row in a DataSet's underlying DataTable. To find a row:

To read a row randomly, first you must establish the underlying DataTable's key structure. The code below shows how to do it. This works with single- or multi-part keys. This only needs to be done once for a table. Once you've done it, the key structure persists for as long as the table does.

BegSr EstablishCustMemKey      
    DclFld Table   System.Data.DataTable

    Table = CustMem.DataSet.Tables[ 0 ]   
    
    DclArray KeyStructure Type( DataColumn ) Rank( 1 ) 
    DclArray KeyValue     Type( *Object )    Rank( 1 )         

    KeyStructure      = *New DataColumn[ 1 ]       
    KeyStructure[ 0 ] = Table.Columns[ "CMCustNo" ] 
    Table.PrimaryKey  = KeyStructure
EndSr

Now, write a "chain" function for the table. This function would take as many parameters as the key as fields. Again, in this case, one.

BegFunc ChainMemCust Type( System.Data.DataRow ) 
    DclSrParm CustNo Type( *Integer4 ) 

    DclFld Table          System.Data.DataTable     
    DclArray KeyValue     Type( *Object )  Dim( 1 ) 
    DclFld Row            System.Data.DataRow

    Table = CustMem.DataSet.Tables[ 0 ]   

    KeyValue[ 0 ] = CustNo

    Row = Table.Rows.Find( KeyValue ) 
    
    LeaveSr Row 
EndFunc

Then call it like this:

DclFld Row System.Data.DataRow

    Row = ChainMemCust( 200 )
    If ( Row = *Nothing )
        MsgBox "Row not found"
    Else
        MsgBox Row[ "CMName" ].ToString()
    EndIf

Dynamically adding and populating a field to a memory file

As a best practice, we recommend using a physical file to describe a DataSet's underlying DataTable. However, there is a way, without much grief, to add columns to a previously defined memory file.

1. Add a column to the memory file's table
Use the Columns collection's Add method to add a column to a table. This only needs to be done for the life of the target table object.

CustMem.DataSet.Tables[ 0 ].Columns.Add( "EMail", *TypeOf( *String ) )

If you need to type a column to *Zoned or *Packed, type it as System.Decimal. For example to add a column that will contain a packed value:

CustMem.DataSet.Tables[ 0 ].Columns.Add( "HourlyRate", *TypeOf( System.Decimal ) )

2. Write data to the new column
AVR's Write operation won't write data to the newly added column. To write data to it, first you need to get a reference to the row you want to update, then you can update the column.

// Before your write loop, declare a variable typed as the DataRow class.

DclFld Row DataRow
DclFld Table System.Data.DataTable

// Get a reference to the table.
Table = CustMem.DataSet.Tables[ 0 ]

// Start loop to write records to memory file (for a datagrid, for example).

// Assign fields defined by memory file's FileDesc file here.
Write CustMem

    // Get a reference to the last row in the table (the row you just wrote).
    // Alternatively, if you're randomly updating data, you can use the above-mentioned
    // techniques to get a reference to a row by key.
    Row = Table.Rows[ Table.Rows.Count - 1 ]

// With the reference to the row just added, manually populate any
// fields you've added here.

    // Get the value you want to write to the added column. In this
    // case we're chaining to a secondary file for its EmailAddress field.
    Chain EmailList Key( CustCMCustNo ) // Gets EmailAddress for you

// Populate the Email field.
Row.Item[ "Email" ] = EmailAddress
// End loop

Article Downloads

Keywords:

memory file

Article ID: 311

Category: ASNA Visual RPG

Applies To:

Article Date: 1/1/2007