Using SQLITE SELECT ... IN statement with string arrays in C# gives empty results with more than one value in array

Question

I have table with columns something like this:

table(INT id, serial, data)

And I want to create following SELECT command:

SELECT * FROM table WHERE serial IN (list-of-serial)

But problem is that when I add more than one value into list-of-serial, I get empty query.

For example:

id serial
1  1234
2  5678
3  1234
4  9012
5  1234
6  5678
7  9012

SELECT * FROM table WHERE serial IN ('1234','9012')

In this form, it works, but I need to convert it into C# query.

string file="my-sqlite-database";
SQLiteConnection database = new SQLiteConnection($@"URI=file:{file}");
database.Open();
SQLiteCommand C = new SQLiteCommand("SELECT * FROM table WHERE serial IN (@serials)", database);
C.Parameters.AddWithValue("@serials","('1234','9012')");
C.Prepare();
SQLiteDataReader R = C.ExecuteReader();
While(R.Read()){} // 0 rows

What am I doing wrong? I have tried passing string arrays, string with and without quotes, no difference.

It won't work like this. This will essentially look for a row where the serial is literally ('1234','9012') — Lasse V. Karlsen
– Lasse V. Karlsen, Commented Nov 18, 2021 at 9:27

Charlieface · Accepted Answer · 2021-11-18 11:10:01Z

3

Unless you inject it directly into the SQL statement, a parameter will only ever be interpreted as a literal value. So it will be looking for a single string matching ('1234','9012'). And injection is a bad idea, which you should generally avoid.

Unfortunately, SQLite does not support Table-Valued parameters. So you need to use multiple parameters here:

string file = "my-sqlite-database";
var parms = new string[]{"1234", "9012"};
using (SQLiteConnection database = new SQLiteConnection($@"URI=file:{file}"))
using (SQLiteCommand C = new SQLiteCommand("", database))
{
    C.Parameters.AddRange(parms.Select((p, ind) => new SqliteParameter("@p" + ind, p)));
    C.CommandText = $@"
SELECT *
FROM table
WHERE serial IN ({string.Join(",", parms.Select((p, ind) => "@p" + ind))})
";
    database.Open();
    C.Prepare();
    using (SQLiteDataReader R = C.ExecuteReader())
    {
        while(R.Read())
        { //do something
        }
    }
}

Dapper can do the above natively, so you may want to look at that.

Note also correct use of using blocks to dispose.

answered Nov 18, 2021 at 11:10

Charlieface

78.6k8 gold badges35 silver badges77 bronze badges

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

1 Comment

Teet Kalm Over a year ago

Thanks, it works! And is probably most safe way to pass variables.

The Blind Hawk · Accepted Answer · 2021-11-18 09:24:38Z

0

Just a hunch, I might be wrong.
I have never used C# but I feel like what is happening might be that "('1234','9012')" is counted as a string.
Did you try making an array and passing the array instead?

answered Nov 18, 2021 at 9:24

The Blind Hawk

1,70219 silver badges39 bronze badges

1 Comment

Teet Kalm Over a year ago

Yes, it does not work like this.

Collectives™ on Stack Overflow

Using SQLITE SELECT ... IN statement with string arrays in C# gives empty results with more than one value in array

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related