System.Data.SQLite parameterized queries with multiple values?

Question

I am trying to do run a bulk deletion using parameterized queries. Currently, I have the following code:

pendingDeletions = new SQLiteCommand(@"DELETE FROM [centres] WHERE [name] = $name", conn);

foreach (string name in selected)
    pendingDeletions.Parameters.AddWithValue("$name", name);

pendingDeletions.ExecuteNonQuery();

However, the value of the parameter seems to be overwritten each time and I end up just removing the last centre. What is the correct way to execute a parameterized query with a list of values?

Community · Accepted Answer · 2020-12-18 03:33:20Z

15

Only go through the work of creating and mapping out the parameter once instead of each time the loop cycles back, also using transactions is suggested by the author to improve performance https://www.sqlite.org/faq.html#q19

using(SQLiteTransaction trans=conn.BeginTransaction())
{
    pendingDeletions = new SQLiteCommand(@"DELETE FROM [centres] WHERE [name] = '$name'", conn);
    p=pendingDeletions.Parameters.AddWithValue("$name", "");  <--

    foreach (string name in selected) 
    {
        p.Value = name;
        pendingDeletions.ExecuteNonQuery(); 
    }
    trans.Commit();
}

edited Dec 18, 2020 at 3:33

CommunityBot

11 silver badge

answered Apr 18, 2010 at 16:39

Robert Harvey

182k48 gold badges349 silver badges516 bronze badges

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

Comments

Sky Sanders · Accepted Answer · 2010-04-18 16:46:48Z

8

Rezzie, your current code is equivalent to:

pendingDeletions = new SQLiteCommand(@"DELETE FROM [centres] WHERE [name] = $name", conn);


foreach (string name in selected)
{
    pendingDeletions.Parameters.AddWithValue("$name", centre.Name);
}

pendingDeletions.ExecuteNonQuery();

Which means you are only executing the query once, with the last value in your 'selected' enumerable.

This is the prime reason that I ALWAYS ALWAYS ALWAYS use block delimiters on conditionals and loops ALWAYS.

So, if you enclose the parameter assignment and the query execution in the loop you should be good to go.

pendingDeletions = new SQLiteCommand(@"DELETE FROM [centres] WHERE [name] = $name", conn);


foreach (string name in selected)
{
    pendingDeletions.Parameters.AddWithValue("$name", centre.Name);
    pendingDeletions.ExecuteNonQuery();
}

answered Apr 18, 2010 at 16:46

Sky Sanders

37.2k9 gold badges71 silver badges91 bronze badges

2 Comments

Sky Sanders Over a year ago

p.s. did i mention to ALWAYS enclose conditionals and loops? ;-)

Rezzie Over a year ago

Yes, I realised that the execution was outside of the loop - I'd (wrongly) assumed that I was building a list of substitutions to the command, when I was actually just overwriting a single substitution repeatedly.

samus · Accepted Answer · 2013-08-01 15:43:06Z

0

I took this example from http://rosettacode.org/wiki/Parametrized_SQL_statement b/c the syntax here (with the '$' didn't work for me)

SqlConnection tConn = new SqlConnection("ConnectionString");

SqlCommand tCommand = new SqlCommand();
tCommand.Connection = tConn;
tCommand.CommandText = "UPDATE players SET name = @name, score = @score, active = @active WHERE jerseyNum = @jerseyNum";

tCommand.Parameters.Add(new SqlParameter("@name", System.Data.SqlDbType.VarChar).Value = "Smith, Steve");
tCommand.Parameters.Add(new SqlParameter("@score", System.Data.SqlDbType.Int).Value = "42");
tCommand.Parameters.Add(new SqlParameter("@active", System.Data.SqlDbType.Bit).Value = true);
tCommand.Parameters.Add(new SqlParameter("@jerseyNum", System.Data.SqlDbType.Int).Value = "99");

tCommand.ExecuteNonQuery();

answered Aug 1, 2013 at 15:43

samus

6,2126 gold badges37 silver badges76 bronze badges

1 Comment

Fredulom Over a year ago

The '$' may not have worked for you because it doesn't look like you're using the SQLite class library :)

Collectives™ on Stack Overflow

System.Data.SQLite parameterized queries with multiple values?

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related