变量名称在查询批处理或存储过程中必须唯一

我正在尝试实现一个异步搜索“引擎”，但是我遇到了一些困难。

由于某种原因，偶尔会抛出一次SqlException，表明：

“变量名称'@input'已经声明。变量名称在查询批处理或存储过程中必须是唯一的。”

样品申请

以下代码以sys.messages表为目标，因此您要做的就是更改连接字符串。

Public Class Form1

    Public Sub New()
        Me.InitializeComponent()
        Me.input = New TextBox() With {.Dock = DockStyle.Top, .TabIndex = 0}
        Me.output = New RichTextBox() With {.Dock = DockStyle.Fill, .TabIndex = 1, .ReadOnly = True, .WordWrap = False}
        Me.Controls.AddRange({Me.output, Me.input})
    End Sub

    Private Sub Search(sender As Object, e As EventArgs) Handles input.TextChanged
        Dim input As String = Me.input.Text
        Static command As SqlCommand
        Static source As CancellationTokenSource
        If (Not command Is Nothing) Then command.Cancel()
        If (Not source Is Nothing) Then source.Cancel()
        command = New SqlCommand()
        source = New CancellationTokenSource()
        Task.Factory.StartNew(Sub() Me.SearchingAsync(input, command, source.Token))
    End Sub

    Private Sub SearchingAsync(input As String, command As SqlCommand, token As CancellationToken)
        Dim [error] As Exception = Nothing
        Dim cancelled As Boolean = False
        Dim result As List(Of sys_message) = Nothing
        Try
            Using connection As New SqlConnection("Server=instance\name;Database=name;Trusted_Connection=True;")
                connection.Open()
                command.Connection = connection
                command.CommandType = CommandType.Text
                command.CommandText = "select * from sys.messages where [text] like '%' + @input + '%';"
                command.Parameters.AddWithValue("@input", input)
                Using reader As SqlDataReader = command.ExecuteReader()
                    result = New List(Of sys_message)()
                    Do While (reader.Read() AndAlso (Not token.IsCancellationRequested))
                        result.Add(New sys_message() With {
                            .message_id = CInt(reader.Item("message_id")),
                            .language_id = CInt(reader.Item("language_id")),
                            .severity = CInt(reader.Item("severity")),
                            .is_event_logged = CBool(reader.Item("is_event_logged")),
                            .text = CStr(reader.Item("text"))
                        })
                    Loop
                End Using
            End Using
            cancelled = token.IsCancellationRequested
        Catch ex As SqlException When ex.Message.ToLower().Contains("operation cancelled by user")
            cancelled = True
        Catch ex As ThreadAbortException
            cancelled = True
        Catch ex As OperationCanceledException
            cancelled = True
        Catch ex As Exception
            [error] = ex
        Finally
            Me.Invoke(
                Sub()
                    'If (String.CompareOrdinal(input, Me.input.Text) = 0) Then
                    If (Not [error] Is Nothing) Then
                        Me.output.Text = String.Concat("Input='", input, "', Output={Result: 'error', Type: '", [error].GetType.Name, "', Message: '", [error].Message.Replace(Environment.NewLine, " "), "'}", Environment.NewLine, Me.output.Text).Trim()
                    ElseIf (cancelled) Then
                        Me.output.Text = String.Concat("Input='", input, "', Output={Result: 'cancelled'}", Environment.NewLine, Me.output.Text).Trim()
                    Else
                        Me.output.Text = String.Concat("Input='", input, "', Output={Result: 'success', Count: ", result.Count, "}", Environment.NewLine, Me.output.Text).Trim()
                    End If
                    'End If
                End Sub
            )
        End Try
    End Sub

    Private WithEvents input As TextBox
    Private WithEvents output As RichTextBox

    Private Class sys_message
        Public message_id As Integer
        Public language_id As Integer
        Public severity As Integer
        Public is_event_logged As Boolean
        Public text As String
    End Class

End Class