iseries As/400 Setup In Windows Operating System And Accessing Its Database

What is iSeries?

iSeries is an IBM product, formerly known as AS/400. It is not an open source. You have to buy its licence to work with this product. Let's say, you have this product already and you want to communicate with this product in Windows operating system.

Let's say the requirement is, you want to design one Web Application, using .NET framework, where the database is iSeries AS/400. To communicate with iSeries AS/400 database, you have to follow the steps, given below, to configure iSeries AS/400 in Windows platform. As I said, iSeries AS/400 is not a free version, so consider that you already have this product in the path, given below

...\iSeries\iSeries Access {version}

Step 1. Install iSeries Access {version} from the following path

...\iSeries\iSeries Access {version}

Step 2. Open Control Panel

Now, go for System and Security selection.

Step 3. Go for Administration Tools selection

Step 4. Select Data Sources (ODBC) and double click it.

Click Add button to add new User DSN or System DSN. The difference between these two options is that, if you choose User DSN ,it will work only for a user for whom you configured. If you go with System DSN, it will work for the system, irrespective of any user. After selecting either User DSN or System DSN,it will give an option to Add new DSN. Once you click it, it prompts you for the selection. Go for Client Access ODBC Driver (32-bit) option and hit Finish button.

Step 5. A Window will prompt. Enter Data Source Name as "****", System as "********"

Now, go to "Server" tab. Enter the library list as "TEMP XYZ490BD1 XYZ6490BS XYZ490BDF XYZ490BD XYZ490BI XYZ490BQ XYZ490BP XYZ6490BD XYZ6490BQ XYZ6490BP QXYZ" Here, "TEMP"," XYZ490BD1", " XYZ6490BS" etc. are the library lists , where the database objects (Tables, Stored Procedures and Functions etc.) resides. Hit Apply button and then hit OK button.

If you observe, User DSN is being created with the name "****"

Step 6. Add the following connection string to your Application to access the database

<add name="AS400DBConnString" connectionString="DSN=****; UID=****: PWD=***" />

Sample C# code to communicate with iSeries AS/400 database given below

public resultDTO NewPoRequest(NexPoRequestDTO newPoRequest)
{
    using (OdbcConnection conn = new OdbcConnection(ConfigurationManager.ConnectionStrings["AS400DBConnString"].ConnectionString))
    {
        try
        {
            using (OdbcCommand cmd = new OdbcCommand("{CALL NEWPOC.CREATEPO(?,?,?,?,?,?)}", conn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                
                OdbcParameter userID = new OdbcParameter("USERID", OdbcType.Char, 10);
                userID.Direction = ParameterDirection.Input;
                userID.Value = newPoRequest.userID;
                cmd.Parameters.Add(userID);
                
                OdbcParameter poTypeCode = new OdbcParameter("POCODE", OdbcType.Char, 2);
                poTypeCode.Direction = ParameterDirection.Input;
                poTypeCode.Value = newPoRequest.poTypeCode.Trim();
                cmd.Parameters.Add(poTypeCode);
                
                OdbcParameter description = new OdbcParameter("PODESC", OdbcType.Char, 60);
                description.Direction = ParameterDirection.Input;
                description.Value = newPoRequest.description.Trim();
                cmd.Parameters.Add(description);
                
                OdbcParameter amount = new OdbcParameter("POAMT", OdbcType.Char, 60);
                amount.Direction = ParameterDirection.Input;
                amount.Value = newPoRequest.amount.Trim();
                cmd.Parameters.Add(amount);
                
                OdbcParameter errorFlag = new OdbcParameter("ERR", OdbcType.Char, 1);
                errorFlag.Direction = ParameterDirection.InputOutput;
                errorFlag.Value = "";
                cmd.Parameters.Add(errorFlag);
                
                OdbcParameter message = new OdbcParameter("ERRM", OdbcType.Char, 50);
                message.Direction = ParameterDirection.InputOutput;
                message.Value = "";
                cmd.Parameters.Add(message);
                
                conn.Open();
                int status = cmd.ExecuteNonQuery();
                
                resultDTO resObj = new resultDTO();
                if (errorFlag.Value.ToString() == "1")
                    resObj.result = "success";
                else
                    resObj.result = "failed";
                
                resObj.resultWeb = message.Value.ToString();
                resObj.status = Convert.ToInt32(errorFlag.Value);
                return resObj;
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            conn.Close();
        }
    }
}

Note. Please consider that in this article a few keywords may match online.