MSSQL to DB2 migration, C# client

Introduction
Migration from MSSQL to DB2 is not only limited to the server side (database schema, SQL server code, data etc) but also a client side migration can be a challenge. Even if standard and database agnostic interface (like ADO.NET) is used,  traps can be hidden everywhere.
MSSQL simple stored procedure

CREATE PROCEDURE [dbo].[testDef](
  @outpar varchar(100) output,
  @par1 int = 10,
  @par2 int = 20
)
AS
   SET @outpar = 'par1=' +  CAST(@par1 as VARCHAR(100)) + ' par2=' + CAST(@par2 AS VARCHAR(100))
GO

C# client code

namespace SPFramework
{
    public enum DBType { db2, mssql };

    public static class C
    {
        public const int NOPAR = -1;
    }

    public interface IExecuteSP
    {
        String callSP(int par1, int par2);
    }
}

namespace SPFramework
{
    public class CallSP : IExecuteSP
    {
        private readonly DBType dType;
        private readonly String connName;

        public CallSP(DBType dType, String connName)
        {
            this.dType = dType;
            this.connName = connName;
        }

        public String callSP(int par1, int par2)
        {
            Database db = DatabaseFactory.CreateDatabase(connName);
            DbConnection cn = db.CreateConnection();
            cn.Open();
            DbCommand cm = cn.CreateCommand();
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = "testDef";
            db.AddOutParameter(cm, "outpar", DbType.String, 100);
            if (par1 != C.NOPAR) db.AddInParameter(cm, "par1", DbType.Int16, par1);
            if (par2 != C.NOPAR) db.AddInParameter(cm, "par2", DbType.Int16, par2);
            cm.ExecuteNonQuery();
            String res = (String)db.GetParameterValue(cm, "outpar");
            cn.Close();
            return res;
        }
    }
}

C# unit test

    [TestClass]
    public class UnitTestCallSp
    {
//        private const DBType dType = DBType.mssql;
//        private const String connName = "default";

        private const DBType dType = DBType.db2;
        private const String connName = "db2";

        [TestInitialize()]
        public void Initialize()
        {
            try
            {
                DatabaseFactory.SetDatabaseProviderFactory(new DatabaseProviderFactory());
            }
            catch (System.InvalidOperationException)
            {

            }
        }


        [TestMethod]
        public void TestMethod1()
        {
//            DatabaseFactory.SetDatabaseProviderFactory(new DatabaseProviderFactory());
            IExecuteSP iSP = new CallSP(dType, connName);
            String res = iSP.callSP(C.NOPAR, C.NOPAR);
            Assert.AreEqual("par1=10 par2=20", res);
        }

        [TestMethod]
        public void TestMethod2()
        {
            IExecuteSP iSP = new CallSP(dType, connName);
            String res = iSP.callSP(5, 7);
            Assert.AreEqual("par1=5 par2=7", res);
        }

        [TestMethod]
        public void TestMethod3()
        {
            IExecuteSP iSP = new CallSP(dType,connName);
            String res = iSP.callSP(5, C.NOPAR);
            Assert.AreEqual("par1=5 par2=20", res);
        }

        [TestMethod]
        public void TestMethod4()
        {
            IExecuteSP iSP = new CallSP(dType, connName);
            String res = iSP.callSP(C.NOPAR, 7);
            Assert.AreEqual("par1=10 par2=7", res);
        }

SP migrated to DB2

CREATE OR REPLACE PROCEDURE TESTDEF (OUT OUTPAR VARCHAR(100),in par1 int DEFAULT 10, in par2 int DEFAULT 20)
P1: BEGIN
      SET OUTPAR = 'par1=' || PAR1 || ' par2=' || PAR2;  
END P1 

C# unit test executed against DB2
Unfortunately, the 4th test will fail for DB2, "par1=7 par2=20" is returned. It is explained under this link. 

Note: When the CommandType property is CommandType.StoredProcedure, named parameters are not supported.

Parameter name is ignored here, the parameters are assigned from left to right. The same problem persists also for Java and C clients.
How to tackle the problem
The problem may be resolved in many ways, the straightforward way is to create parameter list reflecting the order of parameters in SP signature and do not rely on default values. But what to do if we have hundreds of places like that in the application client code, every occurence requires review, comparing against SP signature and fixing.
Solution
DB2 supports parameter name while invoking SP but calling sequence should be different. It is explained under this link - point 3. Use calling sequence: "CALL TestDef(par1=>@value1, par2=>@value2)". If parameter is omitted then the default value is used. But it is not enough to modify 'CommandText' in the C# sample code above.

• CommandType.Text should be set.
• Parameter name means parameter marker (@value1) in the calling sequence, not parameter name in the SP signature.
•  All parameter markers in the calling sequence should be covered by parameter values.

The solution is to dynamically create 'CommandText' string reflecting the current parameter list.

namespace SPFramework
{
    public class CallSP : IExecuteSP
    {
        private readonly DBType dType;
        private readonly String connName;

        public CallSP(DBType dType, String connName)
        {
            this.dType = dType;
            this.connName = connName;
        }

        private void fixDB2(DbCommand cm)
        {
            String cmd = "CALL " + cm.CommandText;
            Boolean notfirst = false;
            foreach (DbParameter par in cm.Parameters)
            {
                if (notfirst) cmd += ","; else cmd += "(";
                notfirst = true;
                cmd += par.ParameterName + "=>@" + par.ParameterName;
            }
            if (notfirst) cmd += ")";
            cm.CommandText = cmd;
            cm.CommandType = CommandType.Text;
        }

        public String callSP(int par1, int par2)
        {
            Database db = DatabaseFactory.CreateDatabase(connName);
            DbConnection cn = db.CreateConnection();
            cn.Open();
            DbCommand cm = cn.CreateCommand();
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = "testDef";
            db.AddOutParameter(cm, "outpar", DbType.String, 100);
            if (par1 != C.NOPAR) db.AddInParameter(cm, "par1", DbType.Int16, par1);
            if (par2 != C.NOPAR) db.AddInParameter(cm, "par2", DbType.Int16, par2);
            if (dType ==  DBType.db2) fixDB2(cm);
            cm.ExecuteNonQuery();
            String res = (String)db.GetParameterValue(cm, "outpar");
            cn.Close();
            return res;
        }
    }
}

'fixDB2' method modifies 'CommandText'.
For instance:

String res = iSP.callSP(C.NOPAR, 7);

Calling sequence will look like:

CALL TESTDEF(param2=>@param2)

Conclusion
This solution requires some additional programming (fixDB method) but does not require painstaking modification of the client calling sequence. What more important - the code is not only easy to migrate but also keeps backward compatibility.