How to video: Access data on iSeries using ADO.NET

Here is a super simplified video example of a C# application reading from a table on an iSeries. It uses the IBM .net managed provider (See Different ways to access iSeries data and this) . You have this  if you have IBM Client Access. If not, download the technology preview here. Sound problem? jiggle the player control if the sound goes out (I'm trying out demo utility software let me know what one you use.)
.

To view the video click here.

To download the project used in the video, click here.

Subscribe to DotNetFluke to receive weekly useful tips for integrating .NET with the iSeries.
If you have any iSeries/.NET integrations questions or suggestions for articles email me at cbyrne+blog@AranRock.com

Converting RPG code to C# automatically?

What if you could take  your RPG programs and hey presto! have them converted into .NET and instantly sell your product to the rest of the world.   This would be a boon to AS/400 vendors locked into the small(ish) AS/400 market.   The advantage is that the original product is likely  a mature business proven system which can provide a critical edge against the deluge of competition once in the new windows space.  While  webfacing does provide a web front end to applications and is great for hosted models it does not help customers who are windows only and want windows only software.

Granted companies need to migrate on the competition level . However, all the supposed advantages such as performance, cost savings, availability of talent etc sounds hollow to long time AS/400 owners. Anecdotally we know that the uptime for the AS/400 is absolutely stunning compared to windows.  It's super stable and extremely reliable. At windows only shops instead of maintaining one iSeries server  and a handful of windows server you end  up maintaining 20-30 windows servers for a small organization.  But like I said, it's about getting your product out to a bigger market.
A big issue with migration products is that often your migrated solution will not be able to operate without them. Yikes! 

I'll be doing a review of these migration products once I get my hands on them but in the meantime here's an overview. Click the title to go to the product website.

TranSoft ML-Impact 
Well, the boys and girls at TranSoft claim that they can convert your old F-specs, c-specs to .NET .   I would love to see the code generated (post a sample  in comments pleez). They claim that CL and RPG will be converted to C#. Not only that they can take the DB2 tables and migrate them to SQL server. Not everything can be converted directly of course and the end result looks much like a 5250 application.
Without having the product available to me I can't give it the thumbs up or down  but if you have any experience with this tool, please drop a comment.

ADC Austin
Remember Synon? The hot product of 20 years ago is now trying to be cool.  They have jiggered a way to migrate from CA2E (Synon)  to Plex which uses .NET.   Sounds complicated. If you've tried it, drop me a line.

ASNA Monarch and AVR for .NET
ASNA monarch is a code converter that  creates RPG for .NET while AVR is a Visual Studio add-in that allows RPG  for .NET (an ASNA creation) development and compiles as a .net app.   Seems like these two products have the same product base.  The only feedback I've seen on this product is that there is no code completion, no automatic layout and ctl-z doesn't work.  Your comments pls.

Lansa Ramp and the rest
There are a bunch of other sticky migration tools out there that are designed to keep you using their applications. While the products above are somewhat sticky you can still migrate off them.  Products like the Lansa Ramp platform is a staged migration solution that requires you to develop a an application framework using their tools, prototype it, then recreate your screens in that framework. However the end product is a Lansa architecture that you have to stick with for the rest of eternity. 

Reading a C# Grid (Reading a subfile)

Previously I discussed how to write records to a C# grid -  the equivalent of an RPG subfile. But how do you read those records? Say you wanted to add totals entered to store in another table?

You might recall that unlike in RPG where we read a table and write each record from the table directly to a subfile, C# creates a layer between the physical data and the grid by using a data set. This is handy because you don't have to worry about updating multiple tables every time an update/add or delete is made to the grid, this is handled automatically when you define what tables belong to the data set.

Here is some code we used before to fill a data set:

// build a command object and prepare an SQL statement so that you can look at your table 

SqlCommand buildData = conn.CreateCommand(); 

cmd.CommandText = "Select * from Orders where OrdQty >100"; 

// Build the Data Adapater - this fills a data set 

SqlDataAdapter dataAdapter = new SqlDataAdapter(buildData); 

// Now create the data set. The data structure which contains the data retrieved by the data adapter 

DataSet orderDataSet = new DataSet(); 

// Populate the dataset with the Data Adapter 

dataAdapter.Fill(dataSet);

 

The data set defined above would be 'bound' to a grid as follows

DataGridView1.DataSource = ds.Tables.Item(0)

Now when the grid is displayed it will display orders from Orders file through the layer of the data set. Any changes made to the grid would be reflected in the data set.

To update the database simply use the adapater's update method

 this.custTableAdapter.Update(ds DataSet);

But back to our original question, how do you read the data in the data set for processing?

In RPG you could read each record in the subfile in a DOW loop, in C# you can use the For Each loop

which can read through data set as follows.

  foreach (DataRow rowx in ds.Tables(0))
            {
                 totalInnings = totalInnings + rowx["Innings"];
            }

The foreach statement is a powerful construct and makes processing data extremely simple. The above statement will process all the records in the subfile

Technorati Tags: Data Set,ado.net,foreach,table adapter,dataset

and any processing just has to occur in between the curly brackets.

Read, write and delete files in windows

Sooner or later you'll need to access files from the Window's file system. You'll need to check if a file exists, delete it, move it  , you'll need to read and write to it and upload data from it to an iSeries table etc.

To illustrate how easy this is, I've included a full C# project.

This program cleans up a desktop. It moves unwanted items automatically to a 'clean up' folder automatically. Items you don't want to remove are maintained in a text file manually. This file is in 'CleanUp' on your desktop after you run it the first time.
Set it up to run in 'scheduled tasks' to run daily. My desktop gets absolutely splattered with downloads, images, files etc. etc.  Now it gets cleaned automatically and the stuff I want to keep is always there. Files are moved but not folders. 

The app demonstrates two things.

1. How to access the windows file system
2. How to write a RPG-like app in C#. i.e. in a procedural way  There is a 'main', an init etc. There are no class objects created, everything runs from the class. Methods are just like subroutines. Variables are defined that are needed throughout the application as public at the top of the class. (If you define a variable in a method - it is only available within that method.)

Please improve on this app and sent it back to share!

Download the attached visual studio project from here. (VS 2008)
If you just want the program executable itself download it here.

using System;
using System.Collections.Generic;

using System.Text;
using System.IO;

using System.Security.Principal;


//Lists each object on the desktop
//Checks if the object is in the list.
//If not, moves it to cleanup.

namespace ClearDesktop
{
    class Program
    {
       
  // Declare class level variables that are accessible from all methods
        public static string cleanUpFolder = @"Cleanup\";
        public static string desktopPathAllUsers = @"C:\Documents and Settings\All Users\Desktop\";
        public static string desktopPath = "";
        public static string cleanUpPath = "";
        public static string cleanUpFile = "";
        public static string allowedOnDesktop = "";
       
        
        
  // Main Routine     
        static void Main(string[] args)
        {
            initializationRoutine();  // Set paths needed
       
            RemoveFilesFromDesktop(desktopPath); // remove unwanted files
            RemoveFilesFromDesktop(desktopPathAllUsers); // some file paths are in 'all users'
        //    Console.ReadLine();
        }




    public static void RemoveFilesFromDesktop(string dir)
        {
// First create directory object from the desktop path
        DirectoryInfo mainDir = new DirectoryInfo(dir);

// This single line fills an array called 'items' of everything on the desktop

// The items in the array are not strings but FileSystemInfo objects.


// What's cool here is each item has a ton of methods and properties


       // available directly from it.

            FileSystemInfo[] items = mainDir.GetFileSystemInfos();

// Now go through each item in the array and check if it is allowed
            foreach (FileSystemInfo item in items)
            {
                // Check if the file on the desktop is in the 'allowed' file
                bool allowedyn =  allowedOnDesktop.Contains(item.Name.ToString());
                
                if (item is FileInfo && !allowedyn)
                {
                   // If it is to be moved any existing same name file has to be deleted
                    String alreadyExists = cleanUpPath + item;

                    if (File.Exists(alreadyExists))
                    {
                        try
                        {
                            // Delete the file with the same name 
                            File.Delete(alreadyExists);
                        }
                        catch (Exception)
                        {
                            
                            
                        }
                        
                    }

                    // Move the file on the desktop to the clean up folder
                    ((FileInfo)item).MoveTo(cleanUpPath + item);
                
                }
             
            }
        }
       

            
      
        // Get a list of all the files allowed on the desktop
          public static string WhatsAllowedOnTheDesktop(string logFileName)
        {
            
            string contents = "";

            using (FileStream fileStream = new FileStream(logFileName,
                        FileMode.Open,
                        FileAccess.Read,
                        FileShare.None))
            {
                using (StreamReader Reader = new StreamReader(fileStream))
                {
                    contents = Reader.ReadToEnd(); // Reads the entire file in one statement
                    Reader.Close();
                    fileStream.Close();
                }
                
               
            }
            return contents;
        }

        // Write to the 'Allowed files' directory those files you don't want to move
          public static void WriteToLog(string logFileName, string data)
          {
              using (FileStream fileStream = new FileStream(logFileName,
                      FileMode.Append,
                      FileAccess.Write,
                      FileShare.None))
              {
                  using (StreamWriter streamWriter = new StreamWriter(fileStream))
                  {
                      streamWriter.WriteLine(data);
                  }
              }
          }

        // Do basic setup - path names etc.
        public static void initializationRoutine() {
              string username = getUserName();
             
            desktopPath = @"C:\Documents and Settings\" + username + @"\Desktop\";
            cleanUpPath  = desktopPath + cleanUpFolder;
            cleanUpFile = cleanUpPath + @"CleanUpFile.txt";

            //  If the cleanup directory does not exist, create it
            if (!Directory.Exists(cleanUpPath))
            {
                Directory.CreateDirectory(cleanUpPath);
            }

            // Create the log file of items you want to keep on the desktop 
            
            if (!File.Exists(cleanUpFile))
            {
                File.Create(cleanUpFile);
            }
            
            allowedOnDesktop = WhatsAllowedOnTheDesktop(cleanUpFile);
            }

        // Get the log on name of the user who started the app in order
        // to know the path of their desktop
        public static string getUserName()
        {
            WindowsIdentity ident = WindowsIdentity.GetCurrent();

            string userid = ident.Name;
            string username = "";
            int pos = userid.IndexOf("\\");
            if (pos > -1)
            {

                username = userid.Substring(pos + 1);
            }
            else
            {
                username = userid;
            }


            return username;

        }

    }



}

When a method calls itself

This you can't do in RPG - have a method call itself. Sounds like something you would never use? Think again.

See this small console program that recurses through directories to get a list of folders and files. When the method finds a directory, it calls itself with the sub folder as a parameter whereupon the File part of the IF statement is executed.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;


namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            Recurse(@"c:\temp");
        }
       
               public static void Recurse(string directory)
    {
        DirectoryInfo path = new DirectoryInfo(directory);
        FileSystemInfo[] files = path.GetFileSystemInfos( );
        foreach (FileSystemInfo file in files)
        {
            if (file is DirectoryInfo)
            { 
                Console.WriteLine("Folder-> " + ((DirectoryInfo)file).FullName);

// Now the method calls itself passing in the subfolder name. When the method is called
all the files in the subfolder are listed 
                Recurse(((DirectoryInfo)file).FullName);
            }
            if (file is FileInfo)
            {
                Console.WriteLine("File-> " + ((FileInfo)file).FullName); 
            } 
            
        }
    


        }

    }
}

LiveJournal Tags: recursion,i/o,filesystem,directory,folders

SQL Crib Sheet for iSeries and .NET

A friend was asking me for some SQL samples the other day so I dug up this old crib sheet from some time ago. These can be used with slight modification in both iSeries and .NET queries. Enjoy!

Select Statements

Return all records all columns in a table:

    select * from MyTableq3

Return all records but only cqcus and cqcush in a table:

    select cqcus, cqcush from MyTableq3

Return cqcus for all records in a table with a specific value for cqcush:

    select cqcus from MyTableq3 where cqcush=123

Return all records in a table where cqcus is one of three possible values:

    select * from MyTableq3 where cqcus in (value1,value2,value3)

Return the number of records in a table:

    select count(*) from TheTable

Return the number of records in a table with a specific value for cqcush:

    select count(*) from MyTableq3 where cqcush=123

Simple join:

    select * from MyTableq3, MyTable
    where MyTableq3.cqcus=MyTable .cqcusA

or

    select MyTableq3.cqcus, MyTable .cqcusA from MyTableq3, MyTable
    where MyTableq3.cqcush=MyTable .cqcusB

Select all unique values in cqcus from a table:

    select distinct(cqcus) from MyTableq3

or

    select distinct cqcus from MyTableq3

Select all unique values for cqcus from a table together with the number of records with that unique value:

    select cqcus, count(*) from MyTableq3
    group by cqcus

Select all unique values for combinations of cqcus and cqcush from a table together with the number of records with that combination:

    select cqcus, cqcush, count(*) from MyTableq3
    group by cqcus, cqcush

Select the number of unique values:

    select count(distinct cqcus) from MyTableq3

Select all duplicate records in a table, where two (or more) records are considered duplicates if they share a common value for a single cqcus:

    select cqcus, count(cqcus) from MyTableq3
    group by cqcus
    having count(*) > 1

Select all duplicate records in a table, where two (or more) records are considered duplicates if they share common values for a pair of cqcuss:

    select cqcus, cqcush, count(*) from MyTableq3
    group by cqcus, cqcush
    having count(*) > 1

Select similar records, i.e. all records which have duplicate cqcus and cqcush in a table but with different cqcus3 (i.e. specifying which cqcuss must be the same and which different):

    select * from table as A, table as B
    where A.cqcus=B.cqcus
    and A.cqcush=B.cqcush
    and A.cqcus3<>B.cqcus3;

Note:

    * It is important to specify at least one cqcus which is different between the two records otherwise this query will list a record as being the same as itself.
    * This query will not find duplicate records, i.e. records with every cqcus the same.

Select all records from a table which do not share a common ID with records from a second table:

    select * from MyTableq3
    where cqcus not in (select cqcush from MyTable )

Note:

    * Sub-queries are quite slow.
    .

An alternative using a join (which can be much faster):

    select MyTableq3.* from MyTableq3
    left join MyTable  on (MyTableq3.cqcus = MyTable .cqcush)
    where MyTable .cqcush is null;

The following method (which has been suggested by Michael Miller) is to use EXISTS. It is much faster on SQL Server than the above (but Michael says it is comparable with the left join technique on Oracle):

    select * from MyTableq3
    where not exists (select cqcush from MyTable  where MyTable .cqcush = MyTableq3.cqcus)

To perform a two way join:

    select * from
    MyTableq3 left join MyTable  on (MyTableq3.cqcus = MyTable .cqcus),
    MyTableq3 left join table3 on (MyTableq3.cqcush = table3.cqcus3)

this has been tested on SQL Server, but not on Oracle or MySql. It does not work with MS-Access.

To combine the results of two queries (be aware that the number and types of cqcuss in both queries must agree):

    select * from MyTableq3
    union select * from MyTable

To return a value based on the contents of a cqcus. This can be done using either Iif, Decode or Case, depending on the database.

The following works with MSAccess:

    select Iif(cqcus = 1, 'one', 'not one')
    from MyTableq3

This is equivalent to the following on SqlServer:

    select Case when cqcus = 1 then 'One' else 'Two' End

    from MyTableq3

Direct join to see if any records from table 1 match table 2
SELECT statement run complete.          
  select * from MyTableq3, MyTable        
    where cqcus=cfcus and  cqcush=cfcush
and cqvar = cfvar                      
and cqlv = cflv                        
and cqsizgrp = cfsizgrp                

For Oracle use the DECODE function.

To create a new table to hold the results of the select query:

    select * into MyTable  from MyTableq3

Be aware that this will fail if MyTable  exists, and that the new table will be created without any indexes.
Insert

Insert new record into a table:

    insert into MyTableq3 values (1,2,3)

Insert new record into a table explicitly naming cqcuss:

    insert into MyTableq3 (cqcus,cqcush,cqcus3) values (1,2,3)

Insert new record into a table using values from another table:

    insert into MyTableq3 (cqcus,cqcush,cqcus3)
    select cqcusA,2,cqcusC from SomeTable

Update

Update all records in a table:

    update MyTableq3 set cqcus=2

Update specific records in a table:

    update MyTableq3 set cqcus=2 where cqcus=1

To update more than one cqcus at a time:

    update MyTableq3 set cqcus=2, cqcush=3

Update a cqcus in a table using a value from another table where both records are referenced by a common key - warning, different databases support different syntax!

    This works in MS-Access and MySQL (5) but not in SQL Server:

    update TableOne
        inner join TableTwo on TableOne.commonID = TableTwo.commonID
        set TableOne.cqcus = TableTwo.cqcusX

or

    This works in MS-Access but not in SQL Server:

    update TableOne, TableTwo
        set TableOne.cqcus = TableTwo.cqcusX
        where TableOne.commonID = TableTwo.commonID

or

   

    update tableOne
    set tableOne.cqcus=tableTwo.cqcusX
    from tableOne, tableTwo
    where tableOne.commonID=tableTwo.commonID

 

 

Delete

Delete all records in a table (dangerous):

    delete from MyTableq3

Delete specific records in a table:

    delete from MyTableq3 where cqcus=value

Delete records from one table which do not have a matching cqcus in another table:

    delete from MyTableq3 where cqcus not in
    (select cqcush from TableTwo)

Keys

Be aware that there are often subtle syntax variations between different database systems. Also other key properties (for example 'clustered') will vary between database systems. Therefore please treat this part of the SQL crib sheet as a guide only.

Create a primary key on a table:

    Alter Table TheTable Add Primary Key (cqcus, cqcush)

To add an index on a cqcus:

    alter table MyTableq3 Add Index (cqcus)

To remove a primary key:

    alter table drop primary key

C# -'The worst bloody language in the world'

My brother-in-law, a Phd wielding university prof.who has a penchant for the complex and abstruse called around yesterday to declare the whole .Net  bouquet and the languages contained therein 'the worst bloody language in the world'.  He came to me asking some very simple questions such as how to read a customer file, display it in a windows form, process it etc and was soon bogged down in connection strings, data binding, data sets etc. "But I just want to display the data and read it!" he exclaimed. He was astounded that the columns in a table aren't easily directly accessible when using SQL which isn't checked until run-time (LINQ I suggest? " Yeah but you still have to go thru hoops!"). 
Coming from procedural languages like RPG, I can sympathize. If I want to process a table in RPG  to check spending limits of a customer and update the table I do this

F CustomerFile IF   K DISK

C  Read CustomerFile;
C  DOW not %eof;
C  If AmountSpent > CreditLimit;
C  AllowSpending =False;
C  Update CustomerFileR;   
C  Endif;

C  Read CustomerFile;
C EndDO;

The equivalent in C# is much more complicated and includes connection strings, command text and a data reader which all have to be set up.

Note that for the ONE 'F' declaration in RPG there are 3 in C#.  To even do a read needs to be setup - you need to create a data reader first. Imagine having to 'create' a read statement in RPG! The code doesn't even include the update functionality!  But the biggest problem is that column names are not typed directly in C# meaning you can't refer to the column name directly. The column 'AmountSpent' is not known to the C# program.   Linq alleviates this situation but you still have to do the setup work first.  I understand the frustration.

using System;
using IBM.Data.DB2.iSeries;

namespace iSeriesADOexample
{
    class Program
    {
        static void Main(string[] args)
        {
            iDB2Connection connection = 
                new iDB2Connection("DataSource=PUB1.RZKH.DE; UserID=XXX; Password=xxx; DefaultCollection=COLMBYRNE1; LibraryList=COLMBYRNE1, *USRLIBL");
            iDB2Command cmd = connection.CreateCommand();
            connection.Open();
            
            cmd.CommandText = "Select * from COLMBYRNE1.CUSTOMERFILE";

            try
            {
                iDB2DataReader dataReader = cmd.ExecuteReader();
                while (dataReader.Read() == true)
                {

                   Double AmountSpent =  dataReader.GetDouble(3);
                    Double CreditLimit = dataReader.GetDouble(4);

                    if (AmountSpent > CreditLimit)
                    {
                        DoUpdate();
                    }

                        }
                }
                catch (iDB2SQLErrorException e)
            {Console.WriteLine("Error:" + e.MessageDetails);
                }
            Console.Read();
            cmd.Dispose();
        }
        }

        }
    

Programming is the art of bringing ideas to life. That's what my brother-in-law came to me for. He had an idea for a program and wanted to make it real. Unfortunately the initial  hurdle to create his simple program was far too great. The wizard functionality in Visual Studio only highlights how difficult it is to do basic actions like read a table, display the contents, process the results and update the table. This is one of the most common programs that every programmer creates yet in .NET it is difficult for a beginner.

It is clear Microsoft have 5-10 years to go before .NET is really mature and allows both beginner and seasoned developer to easily bring their ideas into the world. We program to make things better, faster, more fun, more interesting - not harder.  The tools we use then should also be better, faster - not harder. The effort to use a tool must never be greater than the effort it takes to solve the problem logically.  (Byrne's first hypothesis*) If you want to add 2 and 5 , C# should do that as easily as it does to solve it- and it does  int sum = 2+ 5;    E.g. if a client says that some of his customers are over their credit limit and needs to halt their spending then the logical solution which is to flag those spenders. The effort to implement that in C# should be that easy.  This is the benchmark which Microsoft must follow - just to keep up.