SQLCommand 不清除循环中的命令文本

发布于 2024-08-23 11:07:18 字数 1023 浏览 5 评论 0原文

我遇到了一些奇怪的事情，我不确定为什么会这样。在 foreach 循环中，我将行添加到表中以供交叉引用。使用以下代码：

For Each cp In pCheckPoints
    If cp <> String.Empty Then
        Dim insertSQL As New StringBuilder
        With insertSQL
            .Append("INSERT INTO [CheckpointMessage] ( ")
            .Append(" [MessageID] ")
            .Append(", [CheckPoint] ")
            .Append(" ) VALUES ( ")
            .Append(" @MessageID ")
            .Append(", @Checkpoint ")
            .Append(" ) ")
        End With
        Using objCommand As New SqlCommand(insertSQL.ToString, MySQLConnection)
            With objCommand.Parameters
                .AddWithValue("@MessageID", pMessageID)
                .AddWithValue("@Checkpoint", cp)
            End With
            objCommand.ExecuteNonQuery()
            objCommand.CommandText = String.Empty
        End Using
    End If
Next

如果没有 objCommand.CommandText = String.Empty 行，CommandText 会附加 insertSQL，但这对我来说没有任何意义，因为我希望 objCommand 的 commandText 为空，因为它位于 using 块中。

原文

I ran into something odd, and I'm not precisely sure why it is behaving this way. In a for each loop I am adding rows to a table for a cross reference. Using the following code:

For Each cp In pCheckPoints
    If cp <> String.Empty Then
        Dim insertSQL As New StringBuilder
        With insertSQL
            .Append("INSERT INTO [CheckpointMessage] ( ")
            .Append(" [MessageID] ")
            .Append(", [CheckPoint] ")
            .Append(" ) VALUES ( ")
            .Append(" @MessageID ")
            .Append(", @Checkpoint ")
            .Append(" ) ")
        End With
        Using objCommand As New SqlCommand(insertSQL.ToString, MySQLConnection)
            With objCommand.Parameters
                .AddWithValue("@MessageID", pMessageID)
                .AddWithValue("@Checkpoint", cp)
            End With
            objCommand.ExecuteNonQuery()
            objCommand.CommandText = String.Empty
        End Using
    End If
Next

Without the objCommand.CommandText = String.Empty line the CommandText is appending the insertSQL but that doesn't make any sense to me because I would expect the objCommand's commandText to be empty since it is in a using block.

分享到QQ

分享到微博

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

发布评论

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

后来的我们 2024-08-30 11:07:18

您的命令文本每次都是相同的。不要重建它。尝试一下：

Dim insertSql As String = _
    "INSERT INTO [CheckpointMessage] " & _
        "([MessageID], [CheckPoint]) " & _
        "VALUES " & _
        "(@MessageID, @ChceckPoint)"

Using cmd As New SqlCommand(insertSql, MySQLConnection)
    cmd.Parameters.Add("@MessageID", SqlDbType.Int).Value = pMessageID
    cmd.Parameters.Add("@CheckPoint", SqlDbType.NVarChar, 255) ''# I had to guess at this type

    For Each cp As String In pCheckPoints.Where(Function(c) Not String.IsNullOrEmpty(c))
        cmd.Parameters("@CheckPoint").Value = cp
        cmd.ExecuteNonQuery()
    Next cp
End Using

由于很多原因，它更好：

编译器可以优化字符串连接，而 StringBuilder 强制它在运行时执行该工作
显式类型化参数避免了一些可能真正影响 sql server 性能的边缘情况，甚至打破你的查询。
这只会创建插入查询字符串一次，而不是每个检查点一次
这只会创建一个 SqlCommand 对象

Your command text is the same every time. Don't rebuild it. Try this:

Dim insertSql As String = _
    "INSERT INTO [CheckpointMessage] " & _
        "([MessageID], [CheckPoint]) " & _
        "VALUES " & _
        "(@MessageID, @ChceckPoint)"

Using cmd As New SqlCommand(insertSql, MySQLConnection)
    cmd.Parameters.Add("@MessageID", SqlDbType.Int).Value = pMessageID
    cmd.Parameters.Add("@CheckPoint", SqlDbType.NVarChar, 255) ''# I had to guess at this type

    For Each cp As String In pCheckPoints.Where(Function(c) Not String.IsNullOrEmpty(c))
        cmd.Parameters("@CheckPoint").Value = cp
        cmd.ExecuteNonQuery()
    Next cp
End Using

It's better for a lot of reasons:

The compiler can optimize your string concatenations away, where the StringBuilder forced it do that work at run time
Explicitly typed parameters avoid a few edge cases that can really kill performance in sql server, or even break your query.
This only creates your insert query string once, not once per checkpoint
This only creates one SqlCommand object

回复收藏 0 原文

战皆罪 2024-08-30 11:07:18

除了原始问题之外，以下几行不需要在循环中

Dim insertSQL As New StringBuilder

        With insertSQL
            .Append("INSERT INTO [CheckpointMessage] ( ")
            .Append(" [MessageID] ")
            .Append(", [CheckPoint] ")
            .Append(" ) VALUES ( ")
            .Append(" @MessageID ")
            .Append(", @Checkpoint ")
            .Append(" ) ")
        End With

        Using objCommand As New SqlCommand(insertSQL.ToString, MySQLConnection)

您可以使用该字符串一次来创建带有参数化查询的命令，并在循环中使用相同的 objCommand 实例。唯一属于循环的就是动态值。

Other than the original question, the following lines need not be in the loop

Dim insertSQL As New StringBuilder

        With insertSQL
            .Append("INSERT INTO [CheckpointMessage] ( ")
            .Append(" [MessageID] ")
            .Append(", [CheckPoint] ")
            .Append(" ) VALUES ( ")
            .Append(" @MessageID ")
            .Append(", @Checkpoint ")
            .Append(" ) ")
        End With

        Using objCommand As New SqlCommand(insertSQL.ToString, MySQLConnection)

You could use the string once to create a command with parameterized query and use the same objCommand instance in the loop. The only thing that belongs in the loop is the dynamic values.

回复收藏 0 原文

~没有更多了~