.NET: Accessing JDEdwards Data

Recently I have developed an integration module between JDEdwards (IBM DB2) database and Oracle Database using .NET Web service. Core objective of the module is to read the Payments data from JDEdwards Database and update to Oracle Database.
It was quite challenging and interesting task to work with. Being a beginner to JDEdwards Database, I had come across with some hurdles especially while reading JDEdwards Data during my implementation process. So I just would like to share this article with a hope that it could really be useful to someone who are on the same boat .

Download Integrating DB2 Universal Database for iSeries with Microsoft ADO.NET Book :
http://www.redbooks.ibm.com/redbooks/pdfs/sg246440.pdf

 PC setup:Before we begin, the following software components must be installed onto your development PC:

• Microsoft .NET Framework and Microsoft Visual Studio .NET: Visu al Studio .NET will install a version of the .NET Framework if a required version is not already installed.
• iSeries Access for Windows (5722-XE1), V5R3M0 or later
• 
  
  Most import thing to remember during the installation of IBM iSeries Access for Windows is to check .NET Data Provider Data Access component.

Install available Service Packs (Fixes) from the iSeries Access for Windows Web site at:
http://www.ibm.com/servers/eserver/iseries/access/windows/

Note: The IBM DB2 UDB for iSeries .NET provider is also referred to by the namespace it defines, IBM.Data.DB2.iSeries. The two terms are used interchangeably.

IBM.Data.DB2.iSeries provider object model:

• 
  
  

Adding an assembly reference to the provider:

1. Solution Explorer (Figure 4-8), right-click References and select Add Reference
2. Select IBM DB2 UDB for iSeries .NET provider and click Select.

IBM.Data.DB2.iSeries Namespace will allow applications using Microsoft's .NET framework to access iSeries databases using ADO.NET.

Imports IBM.Data.DB2.iSeries

Accessing JDEdwards Data using .NET:
Configure your JDEdwards connection parameters in Web.Config file:
You could always use Encryption and Decryption of this connection strings in Web.Config file.

-- Development Server configuration -->
<add key="JDE_IP" value="xxx.xxx.xxx.xxx" />
<add key="JDE_UserName" value="username" />
<add key="JDE_Password" value="password" />


Protected  Friend Function JDEConnection() As iDB2Connection
'Open JDE Connection
Try
    Dim JDEConn As iDB2Connection = New iDB2Connection
    Dim JDEConnString As String = String.Empty
    Dim JDE_Ip As String = ConfigurationSettings.AppSettings("JDE_Ip")
    Dim JDE_UserName As String = ConfigurationSettings.AppSettings("JDE_UserName")
    Dim JDE_Password As String = ConfigurationSettings.AppSettings("JDE_Password ")

    JDEConnString = "DataSource=" & JDE_Ip & ";UserID=" & JDE_UserName &
                                ";Password=" & JDE_Password & ";EnablePreFetch=false;"
    JDEConn.ConnectionString = JDEConnString
    JDEConn.Open()
    Return JDEConn
Catch ex As Exception
       'Handle the exceptions
End Try

End Function

Using Kerberos authentication
Before opening a connection using Kerberos, you must use iSeries Navigator to define the connection and configure the connection for Kerberos. In your ConnectionString, do not specify either the
UserID or the Password property; the connection will use the configured default of Kerberos.

For more information about using Kerberos with iSeries Access for Windows, see the IBM Redbook iSeries Access for Windows V5R2 Hot Topics.

Connection pooling
The IBM.Data.DB2.iSeries .NET provider supports connection pooling. This support is on by default.
Use the Pooling property to turn connection pooling on or off.

Example 4-12 Specifying the Pooling property in the ConnectionString:
iDB2Connection cn = new iDB2Connection();
cn.ConnectionString = "DataSource=myiSeries; Pooling=false;";

Some Important Notes:
DataAdapter with the iDB2CharBitData and iDB2VarCharBitData data type of column results array of bytes to Dataset.

So, Alter your SQL SELECT statement to explicitly cast the data.
Example :CAST(COL1 AS VARCHAR(10) CCSID 037)

sSQL = "SELECT CAST(COL1 AS VARCHAR(10) CCSID 037) AS COL1, COL2, COL2 FROM <Table> <Where clause>"

All numeric columns in JDE always hold a negative value, so you must convert the data to a proper numeric value. Following ConvertAmount function  will do the job for you.

Private Function ConvertAmount(ByVal Amount As Double) As Double
    Dim dReturn As
    Double = Amount

   If dReturn < 0 Then
      dReturn = CDbl((Amount * -1) / 100)
  End If
Return dReturn
End Function

JDEdwards stores the date values in Julian date format which is a numeric value.
0 --> represents no date.
So you must convert the Julian date from to Oracle Date format (Or human readable format). Use the following function to Convert Julian Date to Normal Date format.

Private Function Julian2Date(ByVal pDate As String) As String
'Julian date in JDE database is in number format. 0 represents no date
Try
     Dim sReturn As String = String.Empty
     Dim jDate As Integer
    Dim dtIn As Date

   If Not IsDBNull(pDate.Trim) AndAlso pDate.Trim.Length > 0 Then
    jDate = CInt(pDate)
   'jDate = 0 means the invoice
        If jDate > 0 Then
              dtIn = DateSerial(CInt(1900 + jDate / 1000), 1, CInt(jDate Mod 1000))
               sReturn = dtIn.ToString("yyyyMMdd")
       End  If

End If
Return sReturn
Catch ex As Exception
      'Handle the exceptions
End Try
End Function

Public Function Date2Julian(ByVal lstrDate As String) As Long

Dim lstrDt As Date
Dim lintYear As Integer
Dim lintDays As Integer
lstrDt = System.DateTime.ParseExact(lstrDate, "yyyyMMdd", Nothing)
lintDays = lstrDt.DayOfYear
lintYear = CInt(Mid(lstrDate, 1, 4))
Date2Julian = (lintYear - 1900) * 1000 + lintDays
End Function

Injucting JDEdwards Data into a DataSet:
Public Function GetJDEPaymentInfo As DataSet
Dim _JDEConnection As iDB2Connection
Dim daJDEPayments As New iDB2DataAdapter
Dim dsJDEPayments As DataSet = Nothing 'traditional ADO.NET Dataset Object   
Try

_JDEConnection = JDEConnection() 'Open JDE Connection

If Not _JDEConnection Is  Nothing Then

    dsJDEPayments = New DataSet("dsJDEPayments")

    daJDEPayments = New iDB2DataAdapter(<SQLString>, _JDEConnection)

          daJDEPayments.Fill(dsJDEPayments, "tbJDEData")
      End If
Catch ex As Exception
    Throw ex
Finally

If Not _JDEConnection Is Nothing AndAlso _JDEConnection.State <> onnectionState.Closed Then

           _JDEConnection.Close()
           _JDEConnection.Dispose()
End If
End Try
Return dsJDEPayments
End Function