Executing AS400 Commands Programmatically using WPF and C#

• Download source (ZIP) - 105.5 KB
• Download source (RAR) - 87.9 KB

Introduction

In this tip, you are going to learn how to execute AS400 commands using one of the powerful languages, i.e., C#. Though AS400 gives flexibility to run AS400 commands, executing the commands dynamically using C# is always highly preferable. As we all know that when we do like this, we will reap the benefits from both AS400 and .NET environment.

To understand it better, we are going to take one simple example. In real-time scenario, when we have a very huge number of records, it is preferable to take the data in text files with columns delimited by some special characters like |. And then execute AS400/SQL command to transfer the data filled text file to AS400 DB2 or SQL database respectively.

Using the Code

To access AS400, the cwbx DLL (IBM AS/400 iSeries Access for Windows ActiveX Object Library) should be added to the project. This DLL is available in C:\Program Files\IBM\Client Access\Shared folder.

CREATE TABLE TFRLIB/XYZ (CODE CHAR (10 ) NOT NULL WITH DEFAULT,  
NAME CHAR (40 ) NOT NULL WITH DEFAULT,
DESG CHAR (30 ) NOT NULL  WITH DEFAULT)                           

Model table on AS400 DB2 has been created using this code.

using cwbx;

using System.Data.Odbc;

string lsConnection = "Dsn=Security;uid= KBK9898;pwd=SEP2015d;Force Translate=0;";

OdbcConnection lodbcConn = new OdbcConnection(lsConnection);

string lsInputCommand = string.Format_
	("CPYFRMIMPF FROMSTMF(''{0}'') TOFILE(TFRLIB/xyz) 
MBROPT(*ADD) RCDDLM(*CRLF) FLDDLM(''{1}'') RPLNULLVAL(*FLDDFT) ", _
	"/TESTING/inputxyz.txt", "%");

Here CPYFRMIMPF is Copy from import file command. It is highly desirable to login AS400 make the command and copy it to C# code. In the above command, inputxyz.txt is my huge records input text file which needs to be available in TESTING folder on AS400 system. And in text file, columns are delimited by %.

string lsInputCmdLen = lsInputCommand.Length.ToString ();

lsInputCmdLen = (Convert.ToInt16(lsInputCmdLen) - 4).ToString(); 
//this is because we have added 4 escape characters ' in above lsInputCommand.

string lsAs400Command = string.Format
	("CALL QSYS.QCMDEXC('{0}',0000000{1}.00000)", 
lsInputCommand,Convert.ToInt16(lsInputCmdLen) < 100 ? 
	"0" + lsInputCmdLen : lsInputCmdLen);

lsInputCmdLen should be calculated correctly. If it is wrong, the program will give error as “Error occured.ERROR [HY000] [IBM][iSeries Access ODBC Driver][DB2 UDB]CPF0006 - Errors occurred in command.”

OdbcCommand lodbcCommand = new OdbcCommand(lsAs400Command, lodbcConn);
lodbcCommand.CommandType = CommandType.Text;
lodbcConn.Open();
lodbcCommand.ExecuteNonQuery();
lodbcConn.Close();

In real-time scenario, we may use multiple huge text files to insert into different DB2 tables that can be easily achieved by looping the above code in automated/interactive applications.

Please refer to the attachment for a workable test project.