Dot Net Fluke: Getting by on C# for iSeries RPG Developers

How to Create Stored Procedures

noreply@blogger.com (Colm P O Byrne) — Tue, 02 Jun 2009 19:09:00 +0000

Stored procedures are programs written in SQL. They are their own object, stored on the iSeries db and can accept parameters. They are programs with their own variables, flow control and can be called by any other application. Their benefit is in being able to stack a bunch of statements together and so improve performance, reduce network traffic and make your life simple! Why have multiple db statements executing when you can issue one that does it all! It encourages separation of the data from logic and forces you to be modular. Begone Spaghetti!

They also help security; especially useful in distributed apps (client/server) so that the code that updates the db is hidden in the stored procedure and not visible at the front end. Finally they are a super simple way to do a remote call.

You create a stored procedure using iSeries Navigator or directly through your 5250 screen. I'll go over the 5250 way since it is more verbose than the GUI.
To create our procedure we will use the CREATE PROCEDURE command in SQL -called an SQL stored procedure. You can also create them in RPG - an external procedure.

There are 4 main parts to the creation:

1. The NAME of the procedure

 CREATE PROCEDURE  MyProcedureName

2. Parameters I have an input parm of 10 chars and an I/O parm of type integer

( IN REQKEY CHARACTER(10), INOUT RETVAL INTEGER )

 3. Procedure properties The language will always be SQL

 LANGUAGE SQL  3. The procedure code
BEGIN  DECLARE CURS1 CURSOR FOR     SELECT CUSTNAME + 1 FROM MYLIB. MYTABLE      WHERE TELNO = REQKEY FOR UPDATE OF CUSTNAME;   Here's an example which creates a stored procedure in SQL for
updating a table called MYTABLE.

This has two fields; CustName and TelNo. When called with a
customer name, it returns the phone number.

CREATE PROCEDURE MYLIB.STOREDPROC

(IN REQKEY CHARACTER(10), INOUT RETVAL INTEGER )
RESULT SETS 0
LANGUAGE SQL
NOT DETERMINISTIC
/*Start of Procedure Code*/
  BEGIN /* Code always begins with 'Begin' */
  DECLARE CURS1 CURSOR FOR
   SELECT CUSTNAME + 1 FROM MYLIB. MYTABLE
   WHERE TELNO = REQKEY
FOR UPDATE OF CUSTNAME; OPEN CURS1 ;

FETCH CURS1 INTO RETVAL ;

UPDATE MYLIB. MYTABLE SET CUSTNAME= RETVAL
WHERE CURRENT OF CURS1 ;
END

I'll cover calling these from .NET in more detail soon.

In the meantime, create yourself a simple Stored

procedure and try calling it. Then take one of your

existing programs and replace your inline SQL

replacing it with a stored procedure.

Learning to learn

noreply@blogger.com (Colm P O Byrne) — Sat, 23 May 2009 18:29:00 +0000

I have had more than a few emails from readers who privately moan that they 'just don't get it'. They find the concepts of object oriented programming too complex and wieldy and are unable to apply what they have learned in practice. Despite having read the books, downloaded the software, written a few programs, they just don't feel competent. Not enough to use it daily in their work or even ace a simple test. They begin to feel as if they are past it, are simply unable to learn, old dogs/tricks etc.

Did the following happen to you?

You said you wanted to learn .NET so you went and bought the book. The first few chapters were simple. You grasped types and flow control. Foreach? no problem. Hello World! I’m a C# programmer! Then you progressed into the depths of .Net. Inheritance, interfaces, delegates, anonymous methods etc. You understood this perhaps but you found it difficult to really do anything with them. You did the exercises and it just got worse. It took you a month to read one chapter. You found the whole thing hard, confusing, ridiculous, hard to apply to the real world and at the end you retained very little of what you learned. Four months later you retained NOTHING.

Then you gave up.

Learning moment: You will not learn C# by reading a book.

Don't despair! Think about it - if you have been solving programming problems day in, day out throughout your career then it stands to reason that you still have all the right amount of grey matter. Yes! You're smart! The problem may not be your programming skills, the problem may be your learning skills. This article describes the learning process and what you can do to improve your learning skills.
Quiz: By the end of this article try and answer this question: What learning activity was I (the writer) involved in by writing this piece?

Reading is not Doing

You can't learn C# solely from a book. Books are the abstract theory that form only part of the process. All the time you spend reading in earnest is just one of the activities required to really learn. Simply reading a book on the subject (or this blog) is not enough. In fact there is a term for this approach- ‘surface learning’ and will likely end in failure. The reader makes the mistake that if they just read and do the exercises then it will all be absorbed like a sponge and magically they will become a .NET developer. Wrong. Would you trust the novice captain of your transatlantic flight who spent hundreds of hours playing Microsoft Flight Simulator but never flew an actual plane?

Programming is an experiential learning activity.You may not have considered the change required to develop a completely new skill since you were at college. If you are self-taught you may have forgotten that you did make a dramatic shift in thinking at the time. Well, this shift is required again because C# is a not just a different language, it’s a radically different way of programming.
It’s true that you learn all the time. At work you continually develop and enhance your skills but usually you are adding to existing knowledge through active experience and the learning is incremental. What is required now is to learn in a monumental manner i.e understand, retain, practice and implement a complex new skill on a daily basis that is quite different than any you have acquired to date.
To accomplish this you need to develop your learning skills. Once you really change your learning methods to adapt to acquiring new systems of thinking then you will find learning .Net or any similar hefty new skill much easier. This all takes substantial investment in time – more than you have likely done already. But if you take the smart approach it will really pay off , you will retain what you have learned and importantly, be able to apply it to daily programming problems. You might have spent months reading C# books without being able to use it practically, so spending the extra time learning to learn will salvage precious time spent reading. So the hard part is not C#, the hard part is changing how you learn. Let’s look now at what learning really is.

Look at the diagram below by the experiential learning guru David Kolb - it shows the various stages of learning. Now tell me where your $50 C# book is in this cycle? (I have to laugh at the SAM’S programming titles ‘Learn C# in 24 hours’ ). Programming does not mean coding, SAM. Programming means being able to take a customer problem – like a business request for multi country order entry and turn it into a solution using the appropriate programming language. Knowing the definition of a Lambda expression is much different than knowing exactly how to apply it in the search function in the order history module and why.

The stages of learning then are:

Abstract Conceptualization & Theory (Books, blogs, articles, lectures)
Active Experimentation (Applying what you have learned in different situations)
Concrete Experience (Doing a real life project in C#)
Reflection (Complete and go back over your experience documented in your learning journal to change your future behaviour.)

So yes, you need the book but you need to remember that learning C# is not just about reading a book and understanding the concepts. It is a major shift in thinking which requires monumental learning. You read the book but because you weren’t successful and couldn’t apply it you thought .NET and C# was just beyond you. It’s not. You just didn’t complete the other 3 parts of learning.

But Kolb doesn't say HOW you learn in a monumental way. Let’s turn now to improving your learning skills.

One proven method for developing learning skills comes from clinical practice- 'Reflection ON Action and Reflection IN action' according to Price & Maier in their excellent book ‘Effective Study Skills’ (Pearson 2007). This reflection process means to systematically monitor your learning while on a task and after the task.

You will need to add about 10-20% of your time for each task to monitor it through the process called 'Reflection'.
Don’t skip this! You probably think ‘I don’t need to be this explicit, I know all this stuff already’. I agree, you do know this stuff, but you know it implicitly. The point is to improve your learning and to do that, your knowledge needs to become more explicit. Engineers are notorious for being black holes of knowledge. It all goes in but nobody , including themselves, sees clearly into that obscure mass of synaptic lightning. They scoff at others who are ‘all talk and no action’ they find articulation beneath them. They hide in their cubicle and weeks later magic stuff appears on screens. The reality is that we all need to be a little more ‘all talk’ to learn with skill, speed and be able to quickly put it into practice. If we want to keep making magic stuff, we need to articulate our learning, we need to learn how to be reflective.

Reflection is a process whereby you mirror the activities you are engaged in, think about them objectively and determine ways to change your behaviour and then actively apply the changes in your behaviour.

You can start using reflection today. Let’s take a typical project like order entry.

Here’s the three steps to reflection:

1) Recap: Go back over the requirements, how they changed during coding, what delayed the project. Go back over what happened. Articulate the project issues and how they were solved or not solved. Articulate doesn’t mean glossing it over in your head, it means writing it down, presenting it to others, chunk the information in meaningful ways, use mindmaps to look for patterns.

2) Reframe: Take what you have learned and apply it in a new situation.
If the requirements changed substantially during coding then the
A simple example comes from language learning. You take a word and then use it in a new sentence.

3) Reassess: This is a biggie. Here you need to determine how to change your behaviour and activity based on what you learned. You may have noticed that some problems go from project to project. Don’t blame it all on upper management, the users, budget etc. Figure out where you can specifically change your behaviour in the next project and then commit to change. e.g. Create minutes after every meeting so everyone is clear as to their responsibilities.

Remember, the process of reflection happens IN action and AFTER the action. Keep a journal to track your learning (see below).

I recommend a few things to retool your learning.

1) ~~Read~~ Study the book ‘Effective Study Skills' by Price & Maier (Pearson Press)

2) Take a study course at your local university or college. Learn to Learn!

3) Keep a notebook on learning. Track your learning like a kind of resume. State your current ability in the various areas of .NET, state your objectives VERY specifically (e.g. be able to write any subfile type program in ASP.NET in under 6 hours).

I'm only lightly touching on the subject here but take comfort in the fact that you're not dumb, that you CAN learn, and you will be a great .NET developer because you are a great iSeries developer. However you can and you must change. Yes, dog/spots, you can change - through improving your learning. Most programmers are self-taught and when they come up with a learning block they stop right there because they have only learned one way. What they forget is that there are lots of ways to learn. You owe it to yourself to improve your learning, to adapt to what is changing around you. Don’t bank on learning C'# just one way. Take a class. Get a junior position in a .NET team (even if you are 48) and get adopted by a guru (even if he’s 25). Have learning as a systematic process and core part of ALL your activities. Reflect on your learning. OK, try and do it right now. Go through this article again to reflect i.e. Recap, reframe and reassess.

An efficient learning process is essential for your continued professionalism. Look at Dice.com and see where all the jobs are - in newer technologies like .NET. In 10 years it will be completely transformed again and you will need to change again. Becoming a proficient learner is just another skill but is undoubtedly the greatest skill of all.

The information on learning in this article is partly based on Price & Maier’s book ‘Effective Study Skills'

Fast Paging a .NET grid from an iSeries table

noreply@blogger.com (Colm P O Byrne) — Sun, 10 May 2009 21:45:00 +0000

I recently wrote a simple grid view ASP.NET web application for a client displaying late paying customers. The SQL behind it though had to summarize and group across several different tables on the iSeries. The program worked well but the client complained that the app took too long to start up.

To speed things up, I put all the SQL into a stored procedure used a scrollable cursor and fetched 22 records at a time. FETCH NEXT FROM C1 FOR 22 ROWS

I shouldn't have done a select All - it took too long and left the customer hanging even though there wasn't much data behind it.

Books on writing C# apps for iSeries

noreply@blogger.com (Colm P O Byrne) — Thu, 02 Apr 2009 17:56:00 +0000

One of the reasons I started this blog was to periodically build up enough material for a book on interfacing the iSeries with .NET

Someone has already done this - written a book, I mean. It's called "Develop Microsoft C# Applications for the iSeries" by Craig Pelke. They are priced towards IT departments at $445 not for recession babies like you and me.

He's written a bunch of books about .NET and the iSeries, so if you have got any of them - please let me know what they are like.

Here's a link to where you can buy them

http://www2.systeminetwork.com/str/books/UniqueAuthor.cfm?NextAuthor=150

What is ASP.NET?

noreply@blogger.com (Colm P O Byrne) — Wed, 25 Mar 2009 22:03:00 +0000

ASP.NET is the platform to create web pages in .NET.

We say 'I'm writing this app in ASP.NET' to indicate the technologies used.

To create an ASP.NET page, open up Visual Studio and create it from there.

I would then use C# as the code with HTML to provide the controls to process the page.

That code is either embedded in the web page code itself or included in a separate file.

When embedded, it is called INLINE, when separate it is called CODE-BEHIND.

I prefer to use code behind since it keeps the application logic separate.

Visual Studio saves an ASP.NET file as .apx page type.

A web config file is part of the files in your application which contains your connection string to the iSeries.

ASP.NET may seem daunting and the mix of html and C# makes it especially so. The number of files generated by Visual Studio may seem excessive but the aspx and web config are the ones to focus on.

Connection String Stinger - Phantom Library List

noreply@blogger.com (Colm P O Byrne) — Mon, 22 Sep 2008 22:57:00 +0000

.Net can look for your tables in a library the same name as your user id even if you supply a library list. This can cause your SQL 'selects' to fail.

Take a look:
Here's a basic connection string using the IBM supplied iDB2 Classes:

DataSource=192.168.0.1; UserID=colm;Password=xxxx;LibraryList= CusLib,ARlib, QGPL

At first glance everything looks good, right?

So now I want to get at my table MYCUST in CusLib. Should be a no brainer. However if I run the SQL command 'Select * from MYCUST' guess what I get? - A big fat error like this:

SQL0204 - File MYCSUT in COLM not found

What??. CusLib is on the TOP - I don't even mention library Colm. What's going on?

IBM says that if you are using the SQL naming convention that it sets the default collection or library to the library with the same name as the user id. I have no idea why they do this! Anyway if you have this issue the simple fix is to specify the default collection. i.e. defaultcollection=cuslib

Here's the new connection string

DataSource=192.168.0.1; UserID=colm;Password=xxxx;LibraryList= CusLib,ARlib, QGPL;DefaultCollection=CUSLIB

How to run your .net batch programs without showing a dos screen

noreply@blogger.com (Colm P O Byrne) — Fri, 12 Sep 2008 20:57:00 +0000

If you are writing a 'batch' .NET program, that is a program that does not have user interaction- a Console program you may not want the console (the DOS screen) to pop up at all - especially if it is running frequently as a scheduled task in windows.

There are a couple of ways to prevent the DOS screen flashing up every time your program runs. Here's a simple way:

Create a batch file (Myapp.bat for example) to run your app.
Create a shortcut (.lnk) to Myapp.bat
Right click on the shortcut and select Properties
Change the drop down for Run from Normal to Minimized
Now run your shortcut. The dos screen should not appear and the program should appear on your taskbar as it runs.
If it doesn't work for some reason try entering the Target name manually rather than using what was entered when you created it. Sounds nuts but people have found this as a solution when the shortcut still shows the dos prompt

iSeries Users prefer .NET to Websphere/Java

noreply@blogger.com (Colm P O Byrne) — Sat, 23 Aug 2008 01:42:00 +0000

At the annual ASNA conference a poll amongst users report that .NET is the way to go and Java is on the way out. According to the press release "An overwhelming majority of conference attendees stated that .NET offered a better environment than WebSphere/Java for transforming critical corporate applications running on legacy iSeries systems and built with RPG code."

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

noreply@blogger.com (Colm P O Byrne) — Thu, 07 Aug 2008 21:46:00 +0000

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?

noreply@blogger.com (Colm P O Byrne) — Tue, 05 Aug 2008 15:50:00 +0000

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)

noreply@blogger.com (Colm P O Byrne) — Wed, 30 Jul 2008 21:05:00 +0000

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

noreply@blogger.com (Colm P O Byrne) — Wed, 16 Jul 2008 04:59:00 +0000

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.

How to access the windows file system
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

noreply@blogger.com (Colm P O Byrne) — Mon, 07 Jul 2008 23:02:00 +0000

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

noreply@blogger.com (Colm P O Byrne) — Fri, 27 Jun 2008 14:45:00 +0000

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

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

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

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

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

    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'

noreply@blogger.com (Colm P O Byrne) — Tue, 17 Jun 2008 13:17:00 +0000

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.

LINQ to DB2 Beta available tomorrow

noreply@blogger.com (Colm P O Byrne) — Mon, 05 May 2008 14:12:00 +0000

As you probably know the geeks at IBM have been scrambling to put together a LINQ to DB2 Entity framework ever since LINQ was announced - and by jove they've done it!
You know what LINQ is right? It allows you to query data in C# and refer to columns in tables by their column names directly just as you would any field in your C# program. Yes I know, this seems like an uber basic requirement for any language but at least it is done.
Initially LINQ had only SQL, XML and in-memory fields were supported but IBM, Oracle and MySQL quickly started getting in on the act to support their dbs.
We've learned here at Dot Net Fluke that IBM will announce tomorrow (May 6th) a beta of the LINQ to DB2 beta. We'll post the link when it comes available.
The bad news is that it is not yet available for the iSeries. So we'll all just have to sit and wait!

Update: Here's the announcement IBM Announces LINQ to DB2 connector

.Net powering LED Screens

noreply@blogger.com (Colm P O Byrne) — Sat, 12 Apr 2008 21:02:00 +0000

A client asked me recently to build an auction application to manage several county fairs. No problem you'd think. But it also needed to drive an LED screen 12 feet long and 5 feet high. I went and had a look at the screen and it had a worn RS232 serial cable sticking out the back. I connected that up to my laptop with USB to Serial converter, opened up the manual and started hacking away.

Turns out the System.IO.Ports namespace had everything I needed. In a surprisingly short amount of time the LED lit up with auction data. Of course that was just the beginning - the application had to subscribe to different events and manage the board at the same time so that became a little tricky.

The board I was using was from EDI inc, so if you have a different manufacturer then the codes sent to the board might well differ.

using System;
using System.IO.Ports;
using System.Threading;

public class PortChat
{
    static bool _continue;
    static SerialPort _serialPort;

    public static void Main()
    {
        string name;
        string message;
        StringComparer stringComparer = StringComparer.OrdinalIgnoreCase;
        Thread readThread = new Thread(Read);

        // Create a new SerialPort object with default settings.
        _serialPort = new SerialPort();

        // Allow the user to set the appropriate properties.
        _serialPort.PortName = "COM2";
        _serialPort.BaudRate = "1200";

        _serialPort.Parity = "0"
        _serialPort.DataBits = "8";
        _serialPort.StopBits = "1";
             // Set the read/write timeouts
        _serialPort.ReadTimeout = 500;
        _serialPort.WriteTimeout = 500;

        _serialPort.Open();
        _continue = true;
        readThread.Start();

        Console.Write("0x00 This writes to Line 1 0x00");

          Console.Write("0x01 This writes to Line 2 0x01");

       name = Console.ReadLine();

C# Subfile - iSeries data in a C# Grid -2 no coding (with Video)

noreply@blogger.com (Colm P O Byrne) — Tue, 18 Mar 2008 16:26:00 +0000

Well almost no coding.

If you look at my previous post where I showed how to code a grid in C# using data from the iSeries we manually coded the grid, the data set and the connection. This example accomplishes the same result except you'll be done in under 5 minutes! It uses the IDE to build the grid, data set, adapter, connection and SQL command.

The video also shows how to add in the iSeries .net data components to your Visual Studio toolbox.
This post easier shown than discussed - so check out the following video.

Steps

Create a windows form application project
Add in the IBM dll to your references
Add in the IBM data tools to your toolbox using Tools, Choose Toolbox items and filter 'idb2'
Add a basic grid to your form
Drag the iDB2Connection, IDB2Command, iDB2DataAdapter and a data set to your form
Configure each by right clicking and selecting properties
Double click outside the grid to bring up the code for the Load method of the form.
Add in the following code
iDB2DataAdapter1.Fill(dataSet1);
dataGrid1.DataMember = dataSet1.Tables[0].TableName;
Run the program!

C# Subfile - Display an iSeries table in a Grid

noreply@blogger.com (Colm P O Byrne) — Tue, 18 Mar 2008 00:31:00 +0000

We're all used to subfiles on the iSeries. Who can fondly recall many a debate over page by page vs. load all subfile? How do you create the equivalent of a subfile in .Net?

Easy.

Download the visual studio project from here.

Here's a basic example to get started. The Visual Studio IDE can actually do a lot of the work for you. You can even create a grid (read subfile) with just one or two lines of code. (Post to come) However, it's more prudent to begin with code you can understand rather than wading through what looks like binary spaghetti.

This example also introduces data sets and data tables. Data sets are just like data structures but without any definition and data tables are just like the field definitions of data structures -not to be confused with tables in databases. We use Data sets and data tables to disconnect from the data source. Connect to the data source, get the data, fill the data set with the data, disconnect from the data source and use the data set in lieu of the actual data. Another way to think of data sets is to think of them as a cache, bucket, container, plastic bag. See my earlier post on data sets for more info.

Steps

Define your grid (i.e. subfile)
Attach the grid to your form (VS creates one for you auto-)
Create a data set to hold your data
Create a data table to define the fields in the data set
Add the data table to the data set
Connect to your iSeries
Execute an SQL statement to read from a table
Read each row and add to the data set
Disconnect from the iSeries
Attach the data set to the grid
Display the grid

It sounds like a lot of work just to output data to a grid - and it is. Why bother with the data set and the table - can't I just write directly out the grid? Yes you can - but this example is here to show you not only how to display data in a grid from the iSeries but how to best manage that data as well. A Data Set will help you do that.
It is true that there are much simpler approaches on the iSeries but that comes at a price. Once you get out of the db2 and green screen box things get quite tricky on the As/400. .Net is more complicated yes but its complexity comes from flexibility.

iSeries prerequisites:

The table on the iSeries in this example is called customers. Create it in library QGPL
create the table in DDS or go into SQL by typing 'strsql' in the iSeries command prompt and create the table as follows:

CREATE TABLE QGPL/CUSTOMERS (NAME CHAR (30 ) NOT NULL WITH DEFAULT,
BALANCE DEC (5 ) NOT NULL WITH DEFAULT)

Add records using the INSERT sql command, DBU or your favorite data editor on the iSeries

C# Code:

This is the code for the form. The 'Program.cs' in solution explorer is unchanged. Simply create a windows project, double click on the form that appears and replace all the code with the code below. Insert your iSeries IP address and make sure you have created the iSeries table as describe above or replace with your own ensuring that you correctly specify the columns.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using IBM.Data.DB2.iSeries; // Make sure you add this under 'References' in Solution Explorer
// You need the above reference as a dll which is part of iSeries client access.

namespace iSeries_Grid
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            //Define the grid size
            DataGrid subfile = new DataGrid();
            subfile.Location = new Point(0, 0);
            subfile.Size = new Size(400, 500);

            //Attach it to the form
            Controls.AddRange(new Control[] { subfile });

            // Create a DataSet to hold data from iSeries Table
            DataSet dataStructure = new DataSet();

            //Create a table to hold the iSeries data
            DataTable dt = new DataTable("Customers");
            dt.Columns.Add("Name");
            dt.Columns.Add("Balance");

            //Add the datatable to the data set
            dataStructure.Tables.Add(dt);

            // Open connection to the iSeries
            iDB2Connection conn = new iDB2Connection();
           conn.ConnectionString = "DataSource=192.168.0.1";

 // You can put "UserID=myuserid;Password=mypass" 
// if you don't want to be prompted

   //  Create a command to select records from the customer table
            iDB2Command command = new iDB2Command();
          command.CommandText = "Select * from qgpl.customers";
         command.Connection = conn; 
 // ties the command to the connection to the iSeries

         conn.Open();

            // Execute the sql statement. Get a Data Reader object 
            iDB2DataReader readFile = command.ExecuteReader();

            // Read each row from the table and output the results into the data set

            while (readFile.Read())
            {
                // Create a row to hold data
                DataRow datarow = dataStructure.Tables["customers"].NewRow();

                datarow["Name"] = readFile.GetString(0);
                datarow["Balance"] = readFile.GetiDB2Integer(1);
                
                // add the row to the data table customer
                dataStructure.Tables["customers"].Rows.Add(datarow);


                }       

            // Clean up - Close connections
            readFile.Close();
            command.Dispose();
            conn.Close();

         //   Attach the data set to the data grid
            subfile.DataSource = dataStructure;
            subfile.DataMember = dataStructure.Tables[0].TableName;



            // Display the subfile
            subfile.Show();
      

        } // End of Method


    } //End of Class

} // End of Namespace

This code is based on an example in the IBM .Net Redbook modified

for this post.

Reading an iSeries table in C# (with video)

noreply@blogger.com (Colm P O Byrne) — Mon, 17 Mar 2008 06:47:00 +0000

Here's another simple example of reading a table in C# from the iSeries.
See the video for this here.

Download the Visual Studio Project here

As you can see (line 13) all you need in the connection string is the IP address of your machine. If you don't include the log on parameters -UserID=myuser; Password=mypass, then you will be prompted for them by the iSeries.
You need to include the IBM .net provider in your 'References' in your Visual Studio project. It's located in the Client Access directory. If you don't have client access, then download the 'technology preview' from IBM for free.

   1:  using System;

   2:  using System.Collections.Generic;

   3:  using System.Text;

   4:  using IBM.Data.DB2.iSeries;

5:

   6:  namespace Prez_Rank

   7:  {

   8:      class Program

   9:      {

  10:          static void Main(string[] args)

  11:          {

  12:              iDB2Connection conn = new iDB2Connection();

  13:              conn.ConnectionString = "DataSource=192.168.0.1";

  14:              conn.Open();

15:

  16:              iDB2Command cmd = new iDB2Command();

  17:              cmd.CommandText = "Select * from colm.customers";

  18:              cmd.Connection = conn;

19:

20:

21:

  22:              iDB2DataReader dr= cmd.ExecuteReader();

23:

  24:              while (dr.Read())

  25:              {

  26:                  Console.WriteLine(dr.GetString(0));

27:

  28:              }

  29:              Console.ReadLine();

30:

  31:          }

  32:      }

  33:  }

RPG Variables vs C# Variables

noreply@blogger.com (Colm P O Byrne) — Thu, 06 Mar 2008 07:48:00 +0000

In RPG we define variables in our 'D or C Specs. We don't have to uniquely declare character or numeric- just the inclusion of a value in the decimal places column is enough. Leave it out and you just defined a character variable.
For the most part this is all you need to write 90% of business applications. There are other variable types such Binary, Graphic etc but these are encountered less often.

DName+++++++++++ETDsFrom+++To/L+++IDc.Keywords++

D NameofDog       S             24              
D Counter         S              2  0  
D  Price          S              5  2           
D Datefield       S               D             

Here's the equivalent in C#

String NameofDog;
int Counter;
decimal Price;
DateTime datefield;

The first thing that you will notice is that there is no length defined for the fields in C#. That's a great improvement over RPG where you usually explicitly declare length and end up in lots of trouble when a value gets chopped off when moved to a smaller field.

In C# there are way more value types than needed such as byte (for numbers 0-255), sbyte, short, long, float etc.

Common Value Types in C#

Int - integer - whole numbers (no decimals). Useful for counting.

string - holds alphanumeric values

bool - true or false (similar to indicators)

double any decimal up to 15 significant digits

decimal - any decimal up to 28 sig. digits

The variable declarations in RPG did not quite translate to C#. There is no Date value type in C#. The statement ' DateTime datefield;' declares an object reference called datefield. How do I know this? Only because I know there is not value type of date in C#. The fact that I am declaring an object called datefield just as I would a variable value brings up all sorts of interesting things about C# - mainly that objects are just another kind of variable. More on that later.

What about Operations on variables?

Let's add 1 to Counter in RPG and C#

RPG

Counter = Counter + 1;

C#

Counter = Counter + 1;

The code is the exact same in both! There are other differences but we'll get operations in another post.

Lot's of free stuff inside

When you declare a variable in C# whether it is a value type like an integer or a 'reference' type like the DateTime object I created, along with the variable comes packaged actions you might need on it. In RPG if I want to convert my counter to a string variable, I would do the following.

RPG

Character = %char(Numeric);

In C#, there is a method or function included in the variable when you declare it. The brackets indicate that ToString is a method.

Character = Numeric.ToString();

This is a fundamental and important aspect of C# - The actions you perform on variables and objects are often methods of that variable or object. If you want to trim the end of a string in C# you just say Character.Trim(); . Because you declared Character as a type String and String has all these methods now Character has them. There is no 'trim' opcode or expression. It's a method of string and when you declare a variable as a type string it gets all the methods belonging to string.

What's great about this is that you don't have to wait 10 years for IBM to come up with another op-code - you just write one yourself! But, you may be asking, how do I know what method to use? There must be thousands. There are. However the Visual Studio IDE makes it simple and has a feature called intellisense - it fills out the statement as you type showing a drop down of all the methods and properties of an object or variable. Don't worry if this doesn't make a huge amount of sense right now. You'll get the hang of it with practice but it is one of those kind of mind-flips that is a big switch from Procedural RPG and Object Oriented C#. It also makes sense. While RPG is an awesome language it has hit a stone wall. Creating lots of more opcodes or expressions is just going to make the language more complex. C# doesn't need lots of 'op-codes' - in fact there are about 77 in C# 2.0 and many you will never use. Most actions come from methods in classes.

Comparing Loops in RPG and C#

noreply@blogger.com (Colm P O Byrne) — Tue, 12 Feb 2008 06:47:00 +0000

Looping operations are quite similar in C# and RPG. Note that RPG uses 'EndDo' or 'EndFor' and C# uses curly braces {} as the beginning and end of a loop (or any code block for that matter).

Here's the different loops
A = 0; B=10
'Statements' is all the code in the loop that you wish to iterate over.

RPG Loop	C# Loop
DOW (A<b); statements EndDo;	While (a <b) {statements}
FOR A = 1 to 10; statements EndFor;	For (int a=0; a<10; a++) {statements}
DOU A > B; statements EndDo;	Do {statements} while (A >B);

Both languages have ways to skip statements or break out of the loop

iter;	Continue;
Leave;	break;

Here's sample code showing the C# while loop (same as RPG DOW)
Note that the While operation code has {} braces to denote the beginning and end of the loop.

The Do While loop...

            // The while loop
            // The while statement is equivalent to the RPG 'DOW'
            int ebayJunk = 0;
            int CreditLeft = 500;
            bool HaveMoney = true;

// The loop will execute as long as HaveMoney is true;
            while (HaveMoney) // checks before going into the loop
            { // beginning of while loop
                ebayJunk += 1;
                CreditLeft -= 100;
                Console.WriteLine("Useless Gadgets={0}", ebayJunk);
                Console.WriteLine("Money left=${0}", CreditLeft);

        if (CreditLeft <= 0) { HaveMoney = false; } // Set condition for while loop
                // Entire loop is executed a
            } // end of while loop (same as EndDO in RPG 
            Console.WriteLine("You're Broke!!");
            Console.ReadLine();

And the For loop....

// For loop example. Finds the first blank space in a string
// The block of code between the curly braces gets iterated
// over. The loop decrements from the length

String field = "To the Galaxy and Beyond";

for(int i = field.Length; i > 0; i--)

{
Console.WriteLine("Letter is not blank: "+ field[i - 1]);

if(field[i - 1] == ' ') // note how I can treat a string like an array

break; // exit once I reach a blank space

}

Share this post :

Converting from a numeric date to a date type in C# and RPG

noreply@blogger.com (Colm P O Byrne) — Thu, 07 Feb 2008 23:22:00 +0000

Let's compare one of the most common and confusing tasks for all programmers - date conversion.
Given that this is such as a common programming task I am always surprised by how complex this is in many languages.
Here's our problem. Our iSeries legacy table 'Orders' is holding its order date not in the iSeries Date type but in numeric 8,0 in ISO format YYYYMMDD. e.g. ORDATE = 20081021
We need to convert this to a date type in RPG so that we can do some date work - adding a day on it subtracting order date from ship date etc.

RPG Code
Line 1 is the D-spec entry to define the converted date.
Line 2 defines the input order date (typically this comes from a db)

D OrderDateD S d datfmt(*iso)
D ORDATE S 8 0
OrderDateD = %date(ORDATE:*ISO);
// now we cand do some date work with OrderDateD!

Now lets do the same thing in C#
C# Code

Console.WriteLine("Method 2 - ParseExact Method a little simpler");
int ORDATE = 20081210;
DateTime OrderDateD = DateTime.ParseExact(ORDATE.ToString(), "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture);
Console.WriteLine("Date as numeric entered YYYYYMMDD {0}", OrderDateD);

In C# you use the ParseExact method to convert from YYYYMMDD or any other format.
1. Create a date type OderDate with the statement 'DateTime OrderDateD'
2. Assign it the value of ORDATE after parsing it first

The ParseExact method takes 3 parameters
a) The input date in whatever format but converted to a string. We use ToString to convert
b) The fomat of the input date which is specified here as YYYYMMDD. Click here for formats
c) The 'invariant' culture property to tell the method that this is a non standard format. How geeky!

There is another method called 'Parse' but ParseExact is used here because you can specify (in the second parm) the EXACT format that may not confom to any particular standard. This is really handy for handling obscue legacy stored dates in old iSeries tables.
The Parse method though is really clever so when you are using standard date formats it can automatically figue out your date format without telling it what format you are using.

I think its fair to say that the RPG approach is simpler and easier to understand. However there is more flexibility in the C# approach.

DOWNLOADS
See the C# code as a full project which shows both Parse and ParseExact
Also here's an RPG date cheat sheet code adapated from a post by Mitchel Laman
Which fomats to use when describing your input? Click here

Getting data off the iSeries part 2

noreply@blogger.com (Colm P O Byrne) — Fri, 01 Feb 2008 22:36:00 +0000

Someone asked me to expand on the Data Access overview from .NET I gave previously.
If you want more information on this, go to the Programmer's Toolkit included with IBM's client access. It has excellent information with sample code.
Here's an overview from the Programmer's Toolkit :

.NET Framework Classes - The Managed Provider
If you are using the .NET framework to connect to the iseries use the IBM.Data.DB2.iSeries database provider for application development. This is in c:/program files/IBM/Client Access/ as a dll. This managed provider will provide better performance than using the System.Data.OleDb provider to bridge to the iSeries Access OLE DB provider, or using the Microsoft.Data.Odbc provider to bridge to the iSeries Access ODBC driver.

IBM.Data.DB2.iSeries database provider aka Managed Provider aka IBM DB2 UDB for iSeries .NET Provider. They all refer to the same integrated iSeries Access for processing DB2 using ADO.NET.
These are the interfaces supported
Provider Class Name Interface(s) supported
iDB2Connection DbConnection
iDB2Command DbCommand
iDB2DataReader DataReader; IDataRecord
iDB2DataAdapter DbDataAdapter (inherits from IDataAdapter)
iDB2Parameter DataParameter; IDbDataParameter
iDB2ParameterCollection IDataParameterCollection
iDB2Transaction DbTransaction
iDB2DbType iSeries-specific types
iDB2Error iSeries-specific errors
iDB2ErrorCollection Collection, IEnumerable
iDB2Exception Provider-specific exceptions
iDB2CommandBuilder iSeries-specific command builder
advantages

advantages
Easy to program
Allows easy integration of SQL commands and result data with other data sources
Application processes SQL commands directly into an ADO.NET DataSet object
Application places SQL result data into an ADO.NET DataSet object
Provides a minimal set of interfaces between the application and the iSeries database
Increases performance without sacrificing functionality

disadvantage
Record level access, program calls, and data queues are not supported.

ADO/OLE DB
The IBMDA400 and IBMDASQL providers allow you to send remote SQL statements to the iSeries server. There is support for calling most SQL statements on the iSeries server.
The OLE DB provider record-level access support provides the ability to do the following:

- open logical or physical files
- access records sequentially or by key
- read, insert, update, and delete records
- perform commitment control
- work with multiple file members
- work with multiple record formats

advantages
Easy to program to.
Provides SQL access and record-level access to database files.
Excellent record level access performance.

disadvantages
Limited application optimization potential due to standard interface.
Limited support for native DB2 UDB functions.

ActiveX automation objects
The iSeries Access for Windows ActiveX automation object library provides a set of automation objects to allow easy access to iSeries host systems, data queues, programs and commands and call RPG programs.
This is the CWBX.DLL library that you need to add to solution explorer.

- accessing iSeries data queues
- calling iSeries server APIs and user programs
- managing connections and validating security
- running CL commands on the iSeries server
- performing data type and code page conversions
- transferring database data to and from the iSeries server

advantages
Easy to program to.
Automatic conversion to and from many popular PC file formats.
Provides high-level object for easiest data transfers and low-level objects for customized data transfers.
Supports creating, modifying, saving, and executing data transfer request files.
Request files are interchangeable with the Data Transfer GUI and batch applications.

disadvantages
The database SQL support is limited to SELECTs and full file updates.
No support for record level access.
Slower performance in performing individual file/individual row updates than OLE DB record level access.
No future updates planned for this interface.

ODBCadvantagesApplications capable of accessing database files on servers other than iSeries (using non-iSeries Access for Windows ODBC driver) disadvantagesDifficult to program to, though easier than C API.Limited support for native DB2 UDB functions.
.
The above is summarized from IBM's Programmer's toolkit documentation.

RPG Subroutine = C# Method?

noreply@blogger.com (Colm P O Byrne) — Sun, 27 Jan 2008 03:17:00 +0000

Someone asked me the other day if RPG subroutines are the same as C# methods.
The answer is yes - like C# static methods but RPG Procedures are even more similar to C# static methods.
The reason being is that while C# methods are discrete named blocks of code you can call from within a program (C# Class) - just like an RPG Subroutine; C# methods allow parameters in the call - just like RPG procedures do. Methods have lots of other features that I’ll get in to but first lets draw comparisons.
Here’s an example:
Both applications read a table called ‘People’ and print out the name of each person on the console.
They both call a subroutine/method called Get_People that takes no parameters from the main block of code.

RPG
Fpeople IF E K DISK
/FREE
exsr Get_People ;
*inlr = *on;

BegSr Get_People;
read People ;
dow not %eof ;
Dsply name ;
Read People
Enddo;
Endsr;

C#


using System;
using System.IO;
namespace ConsoleApplication1
{
class ProgramClass
{
static void Main() // Main block of code 'Main' must be in the class
{
get_People();
}
static public void get_People( )
{
using (FileStream fileStream = new FileStream(@"C:\csharp\people.txt",
FileMode.Open,
FileAccess.Read,
FileShare.None))
{
using (StreamReader streamReader = new StreamReader(fileStream))
{
string text = streamReader.ReadLine();
while (text != null)
{
Console.WriteLine(text);
text = streamReader.ReadLine();
}
}
}
Console.ReadLine(); // Pause the screen
}
}
}

As you can see, RPG is easier to read and less verbose than its C# counterpart. To be fair DB2 is part of the iSeries operating system so all the file handling is already part of the DB.

Remember these are Static methods shown in C#. A static method in C# is where there is only one instance of the method. Believe it or not you can multiple instances of a method in C#. These are called instance methods and are used more often than static methods. I'll get into the difference but the purpose of this post is to show some similarity between RPG and C# first.