具有多个值的 System.Data.SQLite 参数化查询？

发布于 2024-08-29 00:04:34 字数 347 浏览 1 评论 0原文

我正在尝试使用参数化查询运行批量删除。目前，我有以下代码：

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

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

pendingDeletions.ExecuteNonQuery();

但是，参数的值似乎每次都会被覆盖，我最终只是删除最后一个中心。使用值列表执行参数化查询的正确方法是什么？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

洋洋洒洒 2024-09-05 00:04:34

仅执行一次创建和映射参数的工作，而不是每次循环返回时，作者还建议使用事务来提高性能 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();
}

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();
}

回复收藏 0 原文

南城追梦 2024-09-05 00:04:34

Rezzie，您当前的代码相当于：

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


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

pendingDeletions.ExecuteNonQuery();

这意味着您只执行一次查询，并使用“选定”枚举中的最后一个值。

这是我总是总是在条件和循环上使用块分隔符的主要原因。

因此，如果将参数分配和查询执行包含在循环中，则应该可以正常进行。

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


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

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();
}

回复收藏 0 原文

荒人说梦 2024-09-05 00:04:34

我从 http://rosettacode.org/wiki/Parametrized_SQL_statement b/c 这里的语法中获取了这个示例（与“$”对我不起作用）

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();

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();

回复收藏 0 原文

~没有更多了~