Developing For .NET

At my company our primary database is the IBM System i, formerly known as iSeries, formerly known as AS/400, and still affectionately referred to as simply “the 400” by most people I know.  The 400 is a fantastic database server: fast, reliable, secure, and best of all the darn thing just never breaks.

I spend a great deal of my time writing .NET apps that use the 400 as the backend database.  In the past this has meant a lot of dynamic SQL, which I am extremely comfortable developing.  Unfortunately, this can be pretty brittle, difficult to debug, and performance frequently lags.  I’ve done lots of things to improve performance, like using Parameterized Queries. 

In an effort to wring out the last bit of performance and improve the quality of our code, we have been making an effort to expose our data via Stored Procedures. How to create those procedures on the 400 is a topic I’m not going to delve into, mostly because I haven’t been doing it myself, but also because there are lots of options.  In our case we are writing them in SQL, but they could just as easily execute an RPG program.

Consuming a Stored Procedure

I use the IBM DB2 iSeries .NET Data Provider, V5R4.  I also have SP1 installed (SI35287) which corrects a nasty ObjectDisposedException problem that’s been floating around since .NET 2.0 came out.  The classes are all in the IBM.Data.DB2.iSeries namespace.  Be sure you add a reference to <installed_location>\IBM\Client Access\IBM.Data.DB2.iSeries.dll.

The process is actually pretty simple.  Create a Connection, create a Command, add some Parameters, Execute the Command, Process the results.

var connectionstring = String.Format(ConnectionStringFormat,
    IpAddress, User, Password);
_conn = new iDB2Connection(connectionstring);
_conn.Open();

if (_conn != null && _conn.State == ConnectionState.Open;)
{
    _cmd = _conn.CreateCommand();
    _cmd.CommandTimeout = 0;
    _cmd.CommandType = CommandType.StoredProcedure;
    _cmd.CommandText = "LIBNAME.PROCNAME";

    // Add Parameters
    _cmd.Parameters.Add(name, type, size);
    _cmd.Parameters[name].Value = value;
    _cmd.Parameters[name].Direction = direction;

    _cmd.Prepare();

    // For individual value returns (direction intput/output or output
    _cmd.ExecuteNonQuery();
    var dict = new Dictionary();
    foreach (iDB2Parameter parm in _cmd.Parameters)
    {
        if (parm.Direction == ParameterDirection.Input)
            continue;

        dict.Add(parm.ParameterName, parm.Value);
    }

    // For collection value returns
    var adapter = new iDB2DataAdapter(_cmd);
    var ds = new DataSet();
    adapter.Fill(ds);

    _cmd.Dispose();
    _conn.Close();
    _conn.Dispose();
}

Testing Stored Procedures

I quickly found when we started down this path that I was missing a way to easily test Stored Procedures, and since we were planning on writing lots of them this just would not do.

Executing ad-hoc SQL against the 400 can be a draining experience.  There is a command line tool called Interactive SQL (STRSQL in 400 speak) and a Java tool embedded deep in Operations Navigator which I wrote an article about this back in my ITJungle days. Disappointed in the available tools, my first serious .NET application, written in .NET 1.1 back in 2003, was an ad-hoc SQL tool for the iSeries called iHoc Query.  We still use it in house today but I quit distributing it a while back for various reasons.  Mostly it needs an overhaul and I just don’t have the time to spare.

Unfortunately, I never could get ad-hoc execution of a Stored Procedure to work against the 400, which meant I needed to find a tool. There is a utility out there called SQLThing, which is where we started.  It seemed to work fine, with a few glitches here and there, but I needed to get a good handle on how to code for this data access approach.  It also did not have some of the features I wanted, so naturally I hacked together something myself.

It doesn’t look pretty (I wrote it in WinForms), but it works very well and has these goodies:

• Retains Data Connection information
• Supports Input, Output, and Input/Output parameter directions
• Supports returning multiple result sets, displaying each in a tab
• Allows saving and loading of Stored Procedures, so you can reuse them without reentering the parameters each time

Here is a screen shot:

You can download this tool here.

Some helpful code

I wrote a wrapper class called SprocEngine to isolate the ugly Managed Provider code and make it more palatable to use them in my programs. Here is the code for the SprocEngine class:

using System;
using System.Collections.Generic;
using System.Data;
using IBM.Data.DB2.iSeries;

namespace SprocEngine
{
    public class As400SprocEngine : PropertyChangedBase, IDisposable
    {
        public enum As400DbType
        {
            Numeric,
            Character
        }

        public As400SprocEngine()
        {
        }

        public As400SprocEngine(string ip, string user, string password)
            : this()
        {
            IpAddress = ip;
            User = user;
            Password = password;

            ConnectToDatabase();
        }

        private Dictionary _typeConverter =
            new Dictionary()
                {
                    {As400DbType.Numeric, iDB2DbType.iDB2Numeric},
                    {As400DbType.Character, iDB2DbType.iDB2Char}
                };

        private iDB2Connection _conn = null;
        private iDB2Command _cmd = null;

        private string _ip;
        private string _user;
        private string _pass;

        private const string ConnectionStringFormat = "DataSource={0};UserID={1};Password={2};";

        public string QualifiedProcedureName
        {
            get
            {
                return _cmd.CommandText;
            }
            set
            {
                _cmd.CommandText = value;
                FirePropertyChangedEvent("QualifiedProcedureName");
            }
        }

        public bool IsConnected
        {
            get { return _conn != null && _conn.State == ConnectionState.Open; }
        }

        public string IpAddress
        {
            get { return _ip; }
            set { _ip = value; FirePropertyChangedEvent("IpAddress"); }
        }

        public string User
        {
            get { return _user; }
            set { _user = value; FirePropertyChangedEvent("User"); }
        }

        public string Password
        {
            get { return _pass; }
            set { _pass = value; FirePropertyChangedEvent("Password"); }
        }

        public List ParmTypes
        {
            get
            {
                var list = new List();
                list.AddRange(Enum.GetNames(typeof(iDB2DbType)));
                return list;
            }
        }
        public void ConnectToDatabase()
        {
            if (IpAddress.Equals("") || User.Equals("") || Password.Equals(""))
                throw new ArgumentException("Required field is blank.");

            var connectionstring = String.Format(ConnectionStringFormat,
                IpAddress, User, Password);
            _conn = new iDB2Connection(connectionstring);
            _conn.Open();

            FirePropertyChangedEvent("IsConnected");

            if (IsConnected)
            {
                _cmd = _conn.CreateCommand();
                _cmd.CommandTimeout = 0;
                _cmd.CommandType = CommandType.StoredProcedure;
            }
        }

        public void ClearParameters()
        {
            _cmd.Parameters.Clear();
        }


        public void AddParameter(string name, As400DbType type, object value)
        {
            AddParameterIDB2(name, _typeConverter[type], null, value, ParameterDirection.Input);
        }

        public void AddParameterIDB2(string name, iDB2DbType type, object value)
        {
            AddParameterIDB2(name, type, null, value, ParameterDirection.Input);
        }

        public void AddParameter(string name, As400DbType type, int? size, object value, ParameterDirection direction)
        {
            AddParameterIDB2(name, _typeConverter[type], size, value, direction);
        }

        public void AddParameterIDB2(string name, iDB2DbType type, int? size, object value, ParameterDirection direction)
        {
            if (size.HasValue)
            {
                _cmd.Parameters.Add(name, type, size.Value);
            }
            else
            {
                _cmd.Parameters.Add(name, type);
            }
            _cmd.Parameters[name].Value = value;
            _cmd.Parameters[name].Direction = direction;
        }

        public Dictionary ExecuteSproc()
        {
            try
            {
                _cmd.Prepare();
                var q = _cmd.ExecuteNonQuery();

                var dict = new Dictionary();
                foreach (iDB2Parameter parm in _cmd.Parameters)
                {
                    if (parm.Direction == ParameterDirection.Input)
                        continue;

                    dict.Add(parm.ParameterName, parm.Value);
                }

                if (dict.Count <= 0)
                    dict.Add("AffectedRowCount", q);

                return dict;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataSet ExecuteSprocCollection()
        {
            try
            {
                _cmd.Prepare();

                var adapter = new iDB2DataAdapter(_cmd);
                var ds = new DataSet();
                adapter.Fill(ds);

                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #region IDisposable Members
        public void Dispose()
        {
            _cmd.Dispose();
            _conn.Close();
            _conn.Dispose();
        }
        #endregion
    }
}

It inherits a simple class that implements INotifyPropertyChanged called PropertyChangedBase.  If you aren’t planning on binding this to WPF you can remove it, but if you want it here is the code for it:

using System.ComponentModel;
namespace SprocEngine
{
    public class PropertyChangedBase : INotifyPropertyChanged
    {
        #region INotifyPropertyChanged Members

        public event PropertyChangedEventHandler PropertyChanged;

        #endregion

        internal void FirePropertyChangedEvent(string propertyName)
        {
            if (PropertyChanged != null)
                PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
        }
    }
}

Using SprocEngine

Here are a couple of samples showing how to use SprocEngine.

Processing a single returned value:

using (var sproc = new As400SprocEngine(ipaddress, user, password))
{
    sproc.QualifiedProcedureName = ProcName;
    sproc.AddParameter("libname", As400SprocEngine.As400DbType.Character, 10,
        null, ParameterDirection.InputOutput);

    // Execute SPROC
    var retVals = sproc.ExecuteSproc();
    if (retVals == null || retVals.Count <= 0 )
        return null;

    string DataLibrary = retVals["libname"].ToString().Trim();
}

Processing multiple result sets:

using (var sproc = new As400SprocEngine(ipaddress, user, password))
{
    sproc.QualifiedProcedureName = ProcNames[searchType];
    sproc.AddParameter("@prefix", As400SprocEngine.As400DbType.Character, localityPrefix);
    sproc.AddParameter("@record", As400SprocEngine.As400DbType.Numeric, record);
    var header = new ParcelHeaderCollection();
    try
    {
        var ds = sproc.ExecuteSprocCollection();

        foreach (DataRow dr in ds.Tables[0].Rows)
        {
            var parcel = new ParcelHeader();
            parcel.Record = Convert.ToInt32(dr["mrecno"].ToString());
            parcel.Card = Convert.ToInt32(dr["mdwell"].ToString());
            parcel.OwnerName = dr["mlnam"].ToString().Trim();
            parcel.Map = dr["mmap"].ToString().Trim();
            parcel.StreetNumber = Convert.ToInt32(dr["house"].ToString());
            parcel.StreetName = dr["mstrt"].ToString().Trim();
            parcel.OccupancyCode = Convert.ToInt32(dr["moccup"].ToString());
            parcel.SalesPrice = Convert.ToInt32(dr["msellp"].ToString());
            parcel.YearSold = Convert.ToInt32(dr["myrsld"].ToString());
            parcel.MonthSold = Convert.ToInt32(dr["mmosld"].ToString());

            header.Add(parcel);
        }
    }
    // - you should use a more descriptive Exception type here
    catch (Exception ex)
    {
        throw ex;
    }

Final Notes

As always, Your Mileage May Vary.  This is written specifically for the iSeries, but you should be able adapt it to any other provider.  I have not worked much with Stored Procedures on any other system, so I’m not sure how it may need to be varied.

I hope you find this helpful. If you work in .NET with the iSeries, please share any tips or tricks you may have in the comments below.