如何从多个表中有效删除 [英] How do I efficently delete from multiple tables

问题描述

我目前正在删除5个表。以下是它们的设置方式：

I have 5 tables I am currently deleting from. Here is how they all are setup:

For Each dgvr As DataGridViewRow In DataGridView1.SelectedRows
 Using sqlcon As New SqlConnection(connectionstring + ComboBox1.Text + ".mdf;Integrated Security=True")
                    Try
                       sqlcon.Open()
                        Dim Sources As String = "Delete from " + "[Profile]" + " where Id = @ID"

                        Dim cmd As SqlClient.SqlCommand
                        cmd = New SqlClient.SqlCommand(Sources, sqlcon)
                        cmd.Parameters.AddWithValue("@ID", dgvr.Cells(0).Value.ToString)

                        cmd.ExecuteNonQuery()

                        sqlcon.Close()

                    Catch ex As Exception

                    End Try

                End Using
Next

唯一区别从其他表中删除时，ID是个人_ ID，但它是对初始ID的引用。但是，我仍然使用dgvr.Cells（0）.Value.ToString



我尝试了一批大约8,000个删除，大约需要20分钟才能删除一小批。



有什么建议吗？



我的尝试：



我试图使用连接语句，但是，当我尝试运行代码时，语句从未触发过。所以，我认为语句错误或者vb.net很难理解连接代码。

The only difference when deleting from the other tables is the ID is individual_ID, but it is a reference to the initial ID. However, I still use the dgvr.Cells(0).Value.ToString

I tried a batch of about 8,000 to delete and it takes about 20 minutes to delete such a small batch.

any suggestions?

What I have tried:

I have tried to use a join statement, however, when I tried to run the code the statement never fired. So, I assumed that either the statement was wrong or vb.net has a hard time understanding the join code.

推荐答案

除了代码中明显的安全漏洞之外，在你的循环中，只需创建一个由逗号分隔的所有用户的字符串（例如'BILL'，'JANE'，'TOM'）。然后运行您的查询，但将where ID =更改为其中ID IN（+您的Id的+）。这样查询只运行一次。



但是再次，正如其他人所说，你正在打开SQL注入攻击。

Aside from the obvious security vulnerabilities in your code, in your loop just create a string of all users, separated by a comma (e.g. "'BILL', 'JANE', 'TOM'"). Then run your query but change "where ID=" to "where ID IN (" + your Id's + ")". This way the query runs only once.

But again, and as others have said, you're opening yourself up to a SQL Injection attack.

这篇关于如何从多个表中有效删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！