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

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

I still get the same exception using "@ids" as parameter name... — Jan B.
– Jan B., Commented Nov 13, 2020 at 15:22
The ID in the database is has max size of 36 characters. Is the ID in c# exceeding 36 characters? — jdweng
– jdweng, Commented Nov 13, 2020 at 15:26
there are some tricks, but in general a parameter is not scalar, not value. Is your ID only integer and nothoing else ? — Arioch 'The
– Arioch 'The, Commented Nov 13, 2020 at 15:36
@Miamy True, and you can make it parametrized like where ID IN (@Param1, @Param2, @Param3, @Param4). You can have many params, indeed. But each param will be one scalar value. Equally in C+ you can have int i1=1, i2=2, i3=3, i4=4; but it will be not one variable holding four numbers, it would be four variables holding one number each. Same with params. There is no text-to-text translation, there is not "string splicing" or "stirng interpolation" or what is the name. There really is a creation of variables and putting values into variables OUTSIDE of SQL query text. And that is intention! — Arioch 'The
– Arioch 'The, Commented Nov 13, 2020 at 15:47

Jan B. · Accepted Answer · 2020-11-13 17:27:13Z

0

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();
    }
}

answered Nov 13, 2020 at 17:27

Jan B.

16410 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Arioch 'The Over a year ago

Bad structure: the query is loop invariant. using should be outside the for not inside. Creating the query, adding the parameter and PREPARING the query ("compiling" it in SQL lingo) should be done before the loop. Then inside the loop you have to change parameter value and execute the previously prepared query.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related