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

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.

CodePudding user response：

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?

CodePudding user response：

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.

CodePudding user response：

You need to remove brackets from "SELECT * FROM table WHERE serial IN (@serials)". It should be like this

string parameter = "('1234','9012')";
string query = $"SELECT * FROM table WHERE serial IN {parameter}";

Also, please don't use * in the query, instead, you can mention the columns with an alias.

You can remove C.Parameters.AddWithValue("@serials","('1234','9012')");

As commented by lasse-v-karlsen , you can either remove brackets from the defined parameter in your code.