Add list of IDs as parameter for Firebird query in C#

Question

I would like to update a list of rows given by a list of IDs. Normally, the parameters of the query can be passed via the parameter property. But if I pass a list of IDs, Firebird will not accept the query. I'm using the FirebirdSql.Data.FirebirdClient from NuGet.

Code (simplified):

List<string> ids = someList.Select(_ => _.Id).ToList();
using (var fbCommand = new FbCommand("UPDATE someTable SET NAME='foo' WHERE ID IN (@ids)", fbConnection))
{
    fbCommand.Parameters.Add("ids", ids);
    fbCommand.ExecuteNonQuery();
}

The Table "someTable" is defined like this:

CREATE TABLE someTable (
    ID CHAR(36),
    NAME CHAR(20)
);

This is the exception:

Exception thrown: 'FirebirdSql.Data.FirebirdClient.FbException' in FirebirdSql.Data.FirebirdClient.dll An exception of type 'FirebirdSql.Data.FirebirdClient.FbException' occurred in >FirebirdSql.Data.FirebirdClient.dll but was not handled in user code arithmetic exception, numeric overflow, or string truncation string right truncation

Answer 1

I got kind of around the problem by simply using a foreach loop to update all the rows:

List<string> ids = someList.Select(_ => _.Id).ToList();
foreach (string id in ids) {
    using (var fbCommand = new FbCommand("UPDATE someTable SET NAME='foo' WHERE ID = @id", fbConnection))
    {
        fbCommand.Parameters.Add("id", id);
        fbCommand.ExecuteNonQuery();
    }
}