How to Call a DB2 Stored Procedure

The current recommended method for calling DB2 stored procedures is to create a helper class to be reused in all instances where stored procedures are necessary. The recommended location for this class is in your BOS.Shared assembly.

Create a Shared Helper Class

Since you will be calling the DB2 database directly, you need to add the following reference to your helper class's project: 

using IBM.Data.DB2.iSeries;

Create a class 'StoredProcedureHelper.cs' in the BOS.Shared project, which should already exist in your 'System' solution folder. This new StoredProcedureHelper class is defined below.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using A4DN.Core.BOS.DALBaseForSQL;
using IBM.Data.DB2.iSeries;

namespace BOS.NewProductManagerShared
{
    public static class StoredProcedureHelper
    {
        public static object[] CallStoredProcedure(string SPName, StoredProcParam[] spParams, IAB_Interpreter ap_SQLHandler)
        {
            if (SPName == null)
                throw new ArgumentException(BOS.NewProductManagerShared.Properties.MessageResource.StoredProcNameError);
            
            object[] rtnvals = null;
            try
            {
                using (iDB2Command cmd = new iDB2Command())
                {
                    cmd.CommandText = ap_SQLHandler.ap_ConnectionParms.Collection + "." + SPName.ToUpper();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Connection = ap_SQLHandler.ap_Connection as iDB2Connection;

                    if (ap_SQLHandler.ap_Connection.State != ConnectionState.Open) {
                        ap_SQLHandler.ap_Connection.Open();
                    }

                    IList<iDB2Parameter> _outs = new List<iDB2Parameter>();
                    foreach (StoredProcParam param in spParams)
                    {
                        var _db2param = new iDB2Parameter(param.Name, param.Type, param.Size)
                        {
                            Direction = param.Dir
                        };

                        if (param.Type == iDB2DbType.iDB2Decimal)
                            _db2param.Precision = (byte)param.Precision;
                        if (_db2param.Direction == ParameterDirection.Input)
                            _db2param.Value = param.Value;
                        else
                            _outs.Add(_db2param);

                        cmd.Parameters.Add(_db2param);
                    }

                    cmd.ExecuteReader();

                    rtnvals = new object[_outs.Count];
                    for (int i = 0; i &lt; _outs.Count; i++)
                        rtnvals[i] = _outs[i].Value;
                }
            }
            catch
            {
                throw;
            }

            return rtnvals;
        }
    }

    public class StoredProcParam
    {
        public string Name;
        public ParameterDirection Dir;
        public object Value;
        public iDB2DbType Type;
        public int Size;
        public int Precision;

        public StoredProcParam()
        {
        }

        public StoredProcParam(string _Name, ParameterDirection _Dir, object _Value, iDB2DbType _Type, int _Size, int _Precision)
        {
            this.Name = _Name;
            this.Dir = _Dir;
            this.Value = _Value;
            this.Type = _Type;
            this.Size = _Size;
            this.Precision = _Precision;
        }
    }
}