How to upsert row into DB2 iSeries using parameters in C# ADO.NET?

Question

I'm trying to write SQL statement to insert multiple rows in batch with parameters into table only when the row doesn't exist in the target table.

I have a problem how to pass parameter markers into SQL query. When I use the code below I got exception: "SQL0584 NULL or parameter marker in VALUES not allowed."

using (var conn = new iDB2Connection(_connectionString)) {
    await conn.OpenAsync();
    using (var tran = conn.BeginTransaction()) {
        using (var cmd = conn.CreateCommand()) {
            cmd.Transaction = tran;
            cmd.CommandText = @"
                MERGE INTO TableXYZ AS mt 
                USING (
                    VALUES(@column1, @column2)
                ) AS vt(Column1, Column2)
                ON (
                    mt.Column1 = vt.Column1 AND mt.Column2 = vt.Column2
                )
                WHEN NOT MATCHED THEN
                    INSERT (Column1, Column2) VALUES (vt.Column1, vt.Column2)
            ";
            cmd.DeriveParameters();

            foreach (var item in items) { 
                cmd.Parameters["@column1"].Value = item.Column1;
                cmd.Parameters["@column2"].Value = item.Column2;
                cmd.AddBatch();
            }

            await cmd.ExecuteNonQueryAsync();
        }
        tran.Commit();
    }
}

Any suggestions, please?

The question is How to pass parameter markers into MERGE query. There is no problem with c# code and it's not helpful to send answers how to pass parameters in INSERT or UPDATE statements.

Thanks.

Possible duplicate of [Parameterized DB2 Query From .NET](https://stackoverflow.com/questions/2374698/parameterized-db2-query-from-net) — BugFinder, Sep 11 '17 at 10:05
Why would it not be helpful? Parameters are passed the same way for all SQL statement types. — jmarkmurphy, Sep 11 '17 at 11:12
Try providing explicit data type information in the `VALUES` clause, e.g. `VALUES( CAST (@column1 AS VARCHAR(100) ) , CAST (@column2 AS INTEGER))` (use appropriate data types, of course). — mustaccio, Sep 11 '17 at 11:28
@mustaccio Thank you. `VALUES(CAST(NULL AS DATE))` was just the trick I needed. — Mike, Nov 11 '20 at 14:38

score 5 · Accepted Answer · answered Sep 11 '17 at 12:05

Thank you, @mustaccio!

The explicit datatype in VALUES(...) statement helped.

cmd.CommandText = @"
    MERGE INTO TableXYZ AS mt 
    USING (
        VALUES(CAST(@column1 AS BIGINT), CAST(@column2 AS BIGINT))
    ) AS vt(Column1, Column2)
    ON (
        mt.Column1 = vt.Column1 AND mt.Column2 = vt.Column2
    )
    WHEN NOT MATCHED THEN
        INSERT (Column1, Column2) VALUES (vt.Column1, vt.Column2)
";

How to upsert row into DB2 iSeries using parameters in C# ADO.NET?

1 Answers1