C# Calling a Parameterised DB2 SP Failed to Return Records

If you are facing this problem while you are trying to connect to the DB2 via  IBM iSeries driver, make sure that you are parsing the  iDB2Parameter in the correct order as specified in the stored procedure.

Ex.
-- DB2 SP
CREATE PROCEDURE SOMESCHEMA.SOME_SP(
IN SOME_PAR1 VARCHAR(100) ,
IN SOME_PAR2 VARCHAR(100) ,
IN SOME_PAR3 INTEGER)
.......
....
..

// C# code block that calls the SP

 // Create command initiates the iDB2Command and set the 
 // iDBConnection
 using (iDB2Command command = CreateCommand())  
 {
     // GetProcedure() will return the name of the SP as a string
     command.CommandText = GetProcedure(); 
     command.CommandType = CommandType.StoredProcedure;

    // Note these three lines, Parameters are not given in to the  
    // specific order of the SP
     command.Parameters.AddWithValue("@SOME_PAR3", SomeValue);
     command.Parameters.AddWithValue("@SOME_PAR1", SomeValue);
     command.Parameters.AddWithValue("@SOME_PAR2", SomeValue);

     using (iDB2DataReader dataReader = command.ExecuteReader())
     {
        while (dataReader.Read())
        {
           // You breakpoint will never hit inside to this block
        }
     }
}


So make sure you pass the iDB2Parameters in the same order as specified inside the SP.

       command.Parameters.AddWithValue("@SOME_PAR1", SomeValue);
       command.Parameters.AddWithValue("@SOME_PAR2", SomeValue);
       command.Parameters.AddWithValue("@SOME_PAR3", SomeValue);

I have a doubt whether iSeries driver doesn't care about the name you have given as the parameter name, i.e specify as parameter 1 and pass the value of parameter 2. But it's clear that iDB2Command expects the SP parameters in the exact order as specified in the stored procedure.