CODEWITHSUNDEEP
c#sqlite
john williams
john williams

Reputation: 43

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

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?

Upvotes: 0

Views: 490

Answers (1)

Steve
Steve

Reputation: 216348

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.

Upvotes: 2

Related Questions