Sqlite SELECT with data from c# list without iterating in a for loop?

Question

I have a c# List intlist that contains (1,2,3,4,5)

I have a TABLE in a Sqlite db that also contains a int COLUMN with (1,2,3,4,5) as well as other fields

If I run a select using sqlite connection in c# like:

"SELECT COLUMN, COLUMN2 from TABLE where COLUMN in" + ('1','2','3','4','5');

it executes in < .001 sec But it requires me to type each value into the statement as above, when I already have that data in a list

So instead if I iterate over the list and run the same query like:

foreach (int i in intlist) {
"SELECT COLUMN, COLUMN2 from TABLE where COLUMN = " + i;
}

It runs 5 separate selects and takes > 1 sec

Obviously over large data sets this is amplified to the point it becomes unusable to iterate over and run all those selects. If I have 2,000 ints for example. I have tried batching the selects in transactions and it doesn't speed it up at all.

Is there some other way to iterate over a c# list and use values in that list in a Sqlite select? So I don't have to type them all in manually and can take advantage of the fact I already have the values in a list?

Off the top of my head, create a temp table with your values (1,2,3,etc.) . Inner join that temp table on your aptly named TABLE table. — Crowcoder
– Crowcoder, Commented Jan 22, 2019 at 21:41
@Steve Agreed. This would also fix the SQL injection vulnerability. — Lews Therin
– Lews Therin, Commented Jan 22, 2019 at 21:43
@Steve - do you mind showing me an example of how that would be done? What you're saying makes sense but I am not sure how exactly to do it. Thanks! — john williams
– john williams, Commented Jan 22, 2019 at 21:51

Steve · Accepted Answer · 2019-01-22 21:53:01Z

You should use a loop but to create the parameters required by your command. Something like these lines

List<int> intList = new List<int> {1,2,3,4,5};
string cmdText = "SELECT COLUMN, COLUMN2 from TABLE where COLUMN in({0})";
List<SQLiteParameter> prms = new List<SQLiteParameter>();
List<string> placeholders = new List<string>();
int x = 1;
foreach (int n in intList)
{
    prms.Add(new SQLiteParameter {ParameterName = "@p" + x, DbType = DbType.Int32, Value = n});
    placeholders.Add("@p" + x);
    x++;
}
cmdText = string.Format(cmdText, string.Join(",", placeholders));

// At this point your command text looks like
// SELECT COLUMN, COLUMN2 from TABLE where COLUMN in(@p1,@p2,@p3,@p4,@p5)

using(SQLiteConnection con = new SQLiteConnection("Data Source=|DataDirectory|\\mydb.db"))
using(SQLiteCommand g = new SQLiteCommand(cmdText, con))
{
    con.Open();
    g.Parameters.AddRange(prms.ToArray());
    SQLiteDataReader reader = g.ExecuteReader();
    ....
}

However I am not very comfortable with this solution if your number of parameters starts climbing. You talk about 2000 values for the IN clause and if this is true I cannot be sure of the performances, not just for this solution, but also the whole performances of an IN clause with so many values. Probably the approach mentioned by Crowcoder in its comment can be another solution.

Collectives™ on Stack Overflow

Sqlite SELECT with data from c# list without iterating in a for loop?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related