bulk insert with DB2 iSeries (C#)

Question

I'm trying to make a bulk insert into db2 using C#.

CREATE TABLE tsdta.ftestbk1
(
   NUM numeric(8,0),
   TEXT varchar(30)
)

And here is my test code:

using IBM.Data.DB2.iSeries;

...

using (iDB2Connection connection = new iDB2Connection("xxx"))
{
   string sql = @"insert into tsdta.ftestbk1 
                  values
                  (
                     @num,
                     @text
                  )";
   connection.Open();
   iDB2Command command = connection.CreateCommand();
   command.CommandType = CommandType.Text;
   command.CommandText = sql;
   command.Prepare();
   command.DeriveParameters();

   for (int i = 0; i < 5; i++)
   {
      command.Parameters.Add("@num", i);
      command.Parameters.Add("@text", $"some text for {i}");
      command.AddBatch();
   }

   int rows = command.ExecuteNonQuery();
   connection.Close();
}

When I execute the code, in the table are only NULL values.

 NUM   | TEXT
------------
<null> |<null>
<null> |<null>
<null> |<null>
<null> |<null>
<null> |<null>

Can anyone tell me what I'm doing wrong?

Answer 1

Without a Db2 instance to double check, I am doing this from memory and some old code but I have the following which is running in our prod env for me (I've adjusted it to look like yours)

using (iDB2Connection connection = new iDB2Connection("xxx"))
{
    iDB2Command command = new iDB2Command("INSERT INTO TSDTA.FTESTBK1 VALUES(@NUM, @TEXT", connection);

    // Initialize the parameters collection
    command.DeriveParameters();

    for (int i = 0; i < 5; i++)
    {
        command.Parameters["@NUM"].Value = i;
        command.Parameters["@TEXT"].Value = $"some text {i}";
        command.AddBatch();
    }
    command.ExecuteNonQuery();
}

We had a similar problem at the time and I got the original code from here...

C# - Insert Multiple Records at once to AS400