使用可变数量的IN(p1，p2，p3)参数进行动态查询 [英] Dynamic query using variable number of IN (p1, p2, p3) arguments

问题描述

我有以下内容:

Dim strdepToDelete As String
Dim strOtherToDelete As String
strdepToDelete = String.Join(",", depToDelete)
strOtherToDelete = String.Join(",", otherToDelete)
strdepToDelete = strdepToDelete.Replace(Chr(34), Chr(39)).Substring(1, 77)
strOtherToDelete = strOtherToDelete.Replace(Chr(34), Chr(39)).Substring(1, 77)

cmd.CommandText = "delete from database.tableA where guid in(@strdepToDelete, @strOtherToDelete);"
cmd.Parameters.Add("@strdepToDelete", MySql.Data.MySqlClient.MySqlDbType.String)
cmd.Parameters("@strdepToDelete").Value = strdepToDelete
cmd.Parameters.Add("@strOtherToDelete", MySql.Data.MySqlClient.MySqlDbType.String)
cmd.Parameters("@strOtherToDelete").Value = strOtherToDelete
Dim answer = MsgBox("Data will be deleted if you continue", MsgBoxStyle.YesNo + MsgBoxStyle.DefaultButton2, "Are You Sure")
If answer = vbYes Then
    Try
        cmd.ExecuteNonQuery()
        success = True
    Catch ex As Exception

    End Try
    Return success
End If

我将鼠标悬停在

cmd.Parameters("@strdepToDelete").Value = strdepToDelete 

和

cmd.Parameters("@strOtherToDelete").Value = strOtherToDelete

，并具有MySql正在寻找的格式的字符串值.为什么未从表中删除记录?

and have a string value in format that MySql is looking for. Why are the records not deleted from the table?

推荐答案

来自评论:

depToDelete和otherToDelete是从函数调用传递的列表(字符串).它们包含我要删除的1个或多个Guid

为此，您的代码未针对SQL正确设置其格式.要获得2个Guid字符串的列表，请在加入后得到以下信息:

For that, your code doesnt format it correctly for the SQL. For a list of 2 Guid Strings, after the join you get this:

"af489fbf-982a-49de-b73e-2ac3f3192225, 0feab28d-4f96-456a-9f36-0a0376627128"

然后，strOtherToDelete = strOtherToDelete.Replace(Chr(34), Chr(39)).Substring(1, 77)显然想尝试删除引号并用勾号代替.问题在于字符串本身不包含引号.您可以在IDE中看到它，因为VS告诉您它是一个字符串.

Then, strOtherToDelete = strOtherToDelete.Replace(Chr(34), Chr(39)).Substring(1, 77) apparently wants to try to remove the quote and replace with a tick. The problem is that the string itself doesnt include a Quote. You see it in the IDE because that is VS's way of telling you it is a string.

SubString步骤正在从结果中修剪有效的Guid字符(77的幻数使它在不正确数量的情况下崩溃):

The SubString step is trimming valid Guid chars from the result (and a Magic Number of 77 allows it to crash when there are not just the right number of them):

之前:"9b842f14-7932-4e3d-8483-07790ccc674c, ...
&之后:"b842f14-7932-4e3d-8483-07790ccc674c,...

Before: "9b842f14-7932-4e3d-8483-07790ccc674c, ...
& After: "b842f14-7932-4e3d-8483-07790ccc674c,...

这行不通，因为内容不是很长的Guid. List中的每个 都需要打勾.要勾选列表中的每个元素，您需要循环并构建一个字符串，或者使用linq.

This wont work because the contents are not one very long Guid. Each element in the List needs to be ticked. To tick each element in the list you need to loop and build a string, or use linq.

但是那也行不通. MySQL只是不喜欢NET提供程序生成的字符串，而且它也不做参数数组，所以...

But that wont work either. MySQL just doesn't like the resulting string from the NET provider that way and it doesnt do parameter arrays so...

使用两套Guid是没有意义的，因此将它们连接起来(这些是包含guid的实际List(of String)，不是其他东西，不是json):

There is no sense working with 2 sets of Guids, so concat them (these are an actual List(of String) containing guids, not something else, not json):

Dim depVals = depToDelete.Concat(otherToDelete).ToList

' your sql here
Dim sql = "DELETE FROM DEMO WHERE GuidString IN (@magic)"
' param storage
Dim gvalues As New List(Of String)

' create a list of "@g" param placeholders
Dim ndx As Int32 = 0
For ndx = 0 To depVals.Count - 1
    ' adds a "@gN" value to the List
    gvalues.Add(String.Format("@g{0}", (ndx + 1).ToString))
Next

' insert them into the SQL string
sql = sql.Replace("@magic", String.Join(", ", gvalues))
' '@magic' replaced with "@g1, @g2, @g3..." 

Using cmd As New MySqlCommand(sql, dbcon)
    dbcon.Open()

    ' create an equal number of Paramerters, set the value of each
    For n As Int32 = 0 To gvalues.Count - 1
       ' add parm "@gN", assign value from 'depVals`
       cmd.Parameters.Add(gvalues(n), MySqlDbType.String).Value = depVals(n)
    Next

   ' debug:
   Dim fullSQL = GetFullCommandSQL(cmd)
   Console.WriteLine(fullSQL)

   Dim rows = cmd.ExecuteNonQuery()
End Using

调试输出在语法上是正确的:

The debug output is syntactically correct:

从DEMO WHERE的GuidString IN('2abfa4c4-36e2-47ea-8218-6f568a31fa88'，'fd02865e-5da2-4d4e-ba4b-84bf5b5f1444'，'7376d9a3-35c9-4b44-8b85-4cd663b31d5e')中删除

DELETE FROM DEMO WHERE GuidString IN ('2abfa4c4-36e2-47ea-8218-6f568a31fa88', 'fd02865e-5da2-4d4e-ba4b-84bf5b5f1444', '7376d9a3-35c9-4b44-8b85-4cd663b31d5e')

...并且带有这些GUID的3行将被删除！

...and the 3 rows with those GUIDs get deleted!

也:

• 空捕获块很糟糕，因为它们向唯一可以修复它的人(您)隐藏了问题.
• 您应该使用Option Strict，以避免VB猜测某些事情是什么意思.

这篇关于使用可变数量的IN(p1，p2，p3)参数进行动态查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！