多行删除功能 [英] Multirow delete function

问题描述

我可以使用单元格值一次删除一行，但我尝试为mutlirow delete设置相同的代码并且我不断收到错误：无法输入类型为'System.Int32'的case对象'System.collections.IEnumerable'。



这是代码：

 Private Sub Datagridview_multiselct_delete（）处理DataGridView1.AllowUserToDeleteRowsChanged 
 
为每个dgvr尝试
 DataGridView1.SelectedCells.Item（0）.Value 
 
 Dim sqlcon As New SqlConnection （Data Source =（LocalDB）\ MSSQLLocalDB; AttachDbFilename = c：\ usersrs \jj \ _documents \ visual studio 2015 \Projects\WindowsApplication3 \ WindowsApplication3 \Database1.mdf; Integrated Security = True ）
 Dim sqladapt = New SqlDataAdapter（Select * from+[+ TreeView1.SelectedNode.Text.ToString，sqlcon）
 
 sqlcon.Open（）
 Dim cmd作为SqlClient .SqlCommand 
 Dim sql As String =Delete from+[+ TreeView1.SelectedNode.Text.ToString +]+其中Id =+ dgvr 
 cmd = New SqlClient.SqlCommand（ sql，sqlcon）
 
 cmd.Parameters.AddWithValue（@ id，dgvr）
 
 cmd.ExecuteNonQuery（）
 sqlcon.Close（）
 Next 
 MessageBox.Show（Record deleted）
 Catch ex As Exception 
 MsgBox（ex.Message）
 
结束尝试
 
刷新_数据库（）
 
结束次级
 

我尝试过：



我尝试将所选单元格更改为选定行并尝试将结果字符串。当我尝试将所选行中的值用作字符串时，我不断收到奇怪的消息，例如无效的列名T或无效的列名S.

这里是我尝试的其他一些方法：<每个dgvr在DataGridView1.SelectedRows.Item（0）.DataGridView.MultiSelect.ToString
每个dgvr在DataGridView1.SelectedRows中每个dgvr每个b $ b

 .Item（0）.DataBoundItem.ToString 
 For Each dgvr In DataGridView1.SelectedRows.Item（0）.DataGridView.SelectedRows.ToString 
 For Each dgvr In DataGridView1.SelectedRows.Item（0）.DataGridView。 Rows.Item（0）.Selected.ToString 

仅举几个

解决方案

 对于 每个 dgvr 在 DataGridView1.SelectedCells.Item（ 0 ）。值

你不能对单个值执行 For Each 子句。您必须在某种类型的集合对象上执行此操作。在上面的例子中，我希望它是附加到 SelectedCells 的行。

错误消息告诉你问题，你试图枚举一些无法枚举的东西。



枚举gridview中的行并访问行中的每个id。



每个dgvr As DataGridViewRow在DataGridView1.SelectedRows

... WHERE id =+ dgvr.Cells（0）.Value.ToString



然后，您尝试将参数添加到不包含参数占位符的SQL字符串中，如果您这样做，您实际上已将参数连接到字符串中（不推荐）您将需要.ToString整数。

即使你把它标记为已解决，我也会推荐一些不同的方法。



目前，您为每一行单独执行一个删除语句。如果网格包含100行要删除，则执行100个单独的SQL tatement。这导致大量往返数据库并返回，并且耗费时间和资源。



相反，为什么不收集每个循环内的所有键然后使用 IN 比较使用单个语句删除所有行。



另一件事是你打开和关闭循环内部的连接。这导致两件事：

- 资源使用

- 行删除之间缺少交易



你应该打开连接一次，删除行，检查一切顺利，然后关闭连接



第三件事是你没有使用块。它们应该被用来正确处理资源。



代码建议怎么样，伪代码看起来像

< pre lang =text>每个网格行
将id值存储在字符串列表中

使用连接
使用命令
sql =DELETE FROM TableName WHERE ID IN（& String.Join（，，stringlist.ToArray）&）
try
open connection
execute statement
close connection
catch
句柄错误

示例删除语句看起来像

  DELETE   FROM  TableName  WHERE  ID  IN （ 1 ， 5 ， 99 ， 12 ， 55 ）

有关利用使用块，交易等的更多信息，请查看正确执行数据库操作 [ ^ ]

I am able to delete one row at a time using the cell value, but I tried to setup the same code for a mutlirow delete and I keep getting the error: unable to case object of type 'System.Int32' to type 'System.collections.IEnumerable'.

Here is the code:

Private Sub Datagridview_multiselct_delete() Handles DataGridView1.AllowUserToDeleteRowsChanged

       Try
           For Each dgvr In DataGridView1.SelectedCells.Item(0).Value

               Dim sqlcon As New SqlConnection("Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=c:\users\jj\documents\visual studio 2015\Projects\WindowsApplication3\WindowsApplication3\Database1.mdf;Integrated Security=True")
               Dim sqladapt = New SqlDataAdapter("Select * from " + "[" + TreeView1.SelectedNode.Text.ToString, sqlcon)

               sqlcon.Open()
               Dim cmd As SqlClient.SqlCommand
               Dim sql As String = "Delete from " + "[" + TreeView1.SelectedNode.Text.ToString + "]" + " where Id= " + dgvr
               cmd = New SqlClient.SqlCommand(sql, sqlcon)

               cmd.Parameters.AddWithValue("@id", dgvr)

               cmd.ExecuteNonQuery()
               sqlcon.Close()
           Next
           MessageBox.Show("Record deleted")
       Catch ex As Exception
           MsgBox(ex.Message)

       End Try

       Refresh_database()

   End Sub

What I have tried:

I have tried changing the selected cell to selected row and trying to string the results. When I do try to use the values in selected rows as a string I keep getting weird messages like invalid column name T or invalid column name S.
here are some of the other methods I have tried:

For Each dgvr In DataGridView1.SelectedRows.Item(0).DataGridView.MultiSelect.ToString
For Each dgvr In DataGridView1.SelectedRows.Item(0).DataBoundItem.ToString
For Each dgvr In DataGridView1.SelectedRows.Item(0).DataGridView.SelectedRows.ToString
For Each dgvr In DataGridView1.SelectedRows.Item(0).DataGridView.Rows.Item(0).Selected.ToString

just to name a few

解决方案

For Each dgvr In DataGridView1.SelectedCells.Item(0).Value

You cannot execute a For Each clause on a single value. You must do it on some type of collection object. And in the above case I would expect it to be the rows attached to the SelectedCells.

The error message tells you the problem, you are trying to enumerate something that cannot be enumerated.

Enumerate the rows in the gridview and access each id in the row.

For Each dgvr As DataGridViewRow In DataGridView1.SelectedRows
...WHERE id="+dgvr.Cells(0).Value.ToString

You then try to add a parameter to an SQL string that does not contain a parameter place holder, you have actually concatenated the parameter into the string if you do it that way (not recommended) you'll need to .ToString the integer.

Even though you have marked this as solved I would recommend a bit different approach.

Currently you execute a delete statement separately for each row. If the grid contains 100 rows to delete, you execute 100 separate SQL statement. This causes a lot of round-trips to the database and back and is both time and resource consuming.

Instead, why not gather all the keys inside the for each loop and then delete all the rows using a single statement using IN comparison.

Another thing is that you open and close the connection inside the loop. This causes two things:
- resource usage
- lack of transaction between row deletions

You should open the connection once, delete the rows, check that everything went smoothly and then close the connection

Third thing is that you're not using using blocks. They should be used in order to dispose resources properly.

What comes to the code suggestion, a pseudo-code would look like

for each grid row
   store the id value in a list of strings

using connection
   using command
      sql = "DELETE FROM TableName WHERE ID IN (" & String.Join(", ", stringlist.ToArray)  & ")
      try
         open connection
         execute statement
         close connection
      catch
         handle errors

An example delete statement would look like

DELETE FROM TableName WHERE ID IN (1, 5, 99, 12, 55)

For more information about utilizing the using blocks, transaction etc, have a look at Properly executing database operations[^]

这篇关于多行删除功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！