Introduction
A company's data might exist scattered in legacy systems, in custom-built business applications and in databases in different parts of the organization. The ability to integrate and leverage all this data and applications helps us in reducing the reuse of existing applications, development time and the risk.
I also encountered a similar scenario where I had to reuse the data and business logic (which is implemented in COBOL) developed on AS/400, from a .NET application. This article is a modest approach which describes the above, using an example..
About the application
The application explained here has three buttons. The Add button sends the input data to a COBOL program, which is available on an AS/400 machine. This COBOL program validates the entered user information. If its a valid data, it updates the physical file (USERS) which is residing on AS/400. The Remove button deletes a row from the same physical file based on the user ID provided .The Quit button closes the application.
The .NET application, which I�ve developed, reads the user input and calls the COBOL program. The COBOL program passes the data entered by the user and receives the values returned by the COBOL program. The three textboxes provide the input parameters for the program. They contain the data to be updated.
The AS/400 machine name (connection string) and the library in which the COBOL program is available are configured in the app.config file. This allows us to change these values without the need to recompile the program.
="1.0" ="utf-8"
<configuration>
<appSettings>
<add key="ProgramName" value="pgm1" />
<add key="Library" value="lib1" />
<add key="ConnectionString" value="conn1"/>
</appSettings>
</configuration>
where
pgm1 = Name of the COBOL program
lib1 = Library on AS/400 in which COBOL program is available
conn1 = connection string for AS/400
"Provider=IBMDA400.DataSource.1;User ID=XXXX;Password=YYYYY;Data
Source=123.123.123.123;Connect Timeout=_
30;SSL=DEFAULT;Transport Product=Client Access"
This application uses ADODB as an interface to connect to the database. It has three primary objects- the connection object, the command object and the recordset. The command object is used to execute the command text from the connection object. The parameters are stored in the command parameters and the values are read during runtime. When the command type in the command object is text, we must specify a placeholder for the number of parameters. IBM Client Express�s IBMDA400 OLEDB provider is used for connecting to AS/400.
The following code shows how we have used the command object for passing the three parameters:
command = New ADODB.Command
OpenConnection()
command.ActiveConnection = conn
command.CommandType = ADODB.CommandTypeEnum.adCmdText
command.Parameters.Append(command.CreateParameter("P1",_
ADODB.DataTypeEnum.adChar, _
ADODB.ParameterDirectionEnum.adParamInputOutput,5))
command.Parameters.Append(command.CreateParameter("P2",_
ADODB.DataTypeEnum.adChar, _
ADODB.ParameterDirectionEnum.adParamInputOutput,10))
command.Parameters.Append(command.CreateParameter("P3", _
ADODB.DataTypeEnum.adChar, _
ADODB.ParameterDirectionEnum.adParamInputOutput, 10))
The following code calls the COBOL program:
command.CommandText = "{{CALL /QSYS.LIB/" & _
lib_str & ".LIB/" & program_str & ".PGM(?,?,?)}}"
command.Prepared = True
Dim Rcds As Object = New Object
command.Execute(Rcds, a, command.CommandType.adCmdText)
We can also execute the SQL queries directly on AS/400 physical files. This is a right solution when we need to change the data without any prior processing. For implementing delete functionality I am using this procedure.
command.CommandType = ADODB.CommandTypeEnum.adCmdText
command.CommandText = "DELETE FROM " & _
libr_str & ".USERS1 WHERE USERID =" & txtUserId.Text
command.Execute()
Conclusion
We can manipulate the data that is stored on AS/400 from our .NET applications. We can integrate applications or use the business logic already implemented on AS/400 in our .NET solutions using this approach.
