Removing records from a database via gridview using vb.net

Imports System.ComponentModel
Imports System.Data.SqlClient
Imports System.Runtime.CompilerServices

Public Class Form1
    Private Sub OnLoad(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim table As New DataTable
        Using connection = New SqlConnection(My.Settings.ConnectionString)
            Using adapter = New SqlDataAdapter("SELECT * FROM SUPERVISORS", connection)
                connection.Open()
                adapter.Fill(table)
                DataGridView1.DataSource = table
            End Using
        End Using
    End Sub

    Private Sub OnClick(sender As Object, e As EventArgs) Handles act_Insert.Click, act_Delete.Click
        Dim btn = CType(sender, Button)
        If Equals(btn, act_Delete) Then
            If DataGridView1.SelectedRows.Count > 0 Then
                Using connection = New SqlConnection(My.Settings.ConnectionString)
                    connection.Open()

                    Dim text = $"DELETE FROM SUPERVISORS WHERE ID IN ({String.Join(", ", DataGridView1.SelectedRows.Cast(Of DataGridViewRow).Select(Function(r) CType(r.DataBoundItem, DataRowView).Row("ID")))})"
                    Using command = New SqlCommand(text, connection)
                        command.ExecuteNonQuery()
                    End Using

                    Dim table As New DataTable
                    Using adapter = New SqlDataAdapter("SELECT * FROM SUPERVISORS", connection)
                        adapter.Fill(table)
                        DataGridView1.DataSource = table
                    End Using
                End Using
            End If
        ElseIf Equals(btn, act_Insert) Then
            Dim index = 0
            Using connection = New SqlConnection(My.Settings.ConnectionString)
                connection.Open()

                Using command = New SqlCommand("SELECT MAX(ID) FROM SUPERVISORS", connection)
                    Dim temp = command.ExecuteScalar()
                    If TypeOf temp Is DBNull Then
                        index = 0
                    Else
                        index = CInt(temp)
                    End If
                End Using

                Dim supervisors = (From i In Enumerable.Range(index, 10) Select New With {.Name = $"Supervisor{i}"}).ConvertToDataTable("dbo.Supervisors")
                Using bulkCopy = New SqlBulkCopy(connection, SqlBulkCopyOptions.TableLock, Nothing) With {.DestinationTableName = supervisors.TableName}
                    bulkCopy.ColumnMappings.Add("Name", "Name")
                    bulkCopy.WriteToServer(supervisors)
                End Using

                Dim table As New DataTable
                Using adapter = New SqlDataAdapter("SELECT * FROM SUPERVISORS", connection)
                    adapter.Fill(table)
                    DataGridView1.DataSource = table
                End Using
            End Using
        End If
    End Sub
End Class

Module Extensions
    <Extension>
    Public Function ConvertToDataTable(Of T)(ByVal source As IEnumerable(Of T), Optional ByVal name As String = Nothing) As DataTable
        Dim properties As PropertyDescriptorCollection = TypeDescriptor.GetProperties(GetType(T))
        Dim table As DataTable = If(Not String.IsNullOrWhiteSpace(name), New DataTable(name), New DataTable())

        For i As Integer = 0 To properties.Count - 1
            Dim [property] As PropertyDescriptor = properties(i)
            If [property].PropertyType.IsGenericType AndAlso [property].PropertyType.GetGenericTypeDefinition().Equals(GetType(Nullable)) Then
                table.Columns.Add([property].Name, [property].PropertyType.GetGenericArguments()(0))
            Else
                table.Columns.Add([property].Name, [property].PropertyType)
            End If
        Next

        Dim values(properties.Count - 1) As Object
        For Each item As T In source
            For i As Integer = 0 To properties.Count - 1
                values(i) = properties(i).GetValue(item)
            Next
            table.Rows.Add(values)
        Next

        Return table
    End Function
End Module

Select all Open in new window

<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Partial Class Form1
    Inherits System.Windows.Forms.Form

    'Form overrides dispose to clean up the component list.
    <System.Diagnostics.DebuggerNonUserCode()> _
    Protected Overrides Sub Dispose(ByVal disposing As Boolean)
        Try
            If disposing AndAlso components IsNot Nothing Then
                components.Dispose()
            End If
        Finally
            MyBase.Dispose(disposing)
        End Try
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    <System.Diagnostics.DebuggerStepThrough()> _
    Private Sub InitializeComponent()
        Me.SplitContainer1 = New System.Windows.Forms.SplitContainer()
        Me.DataGridView1 = New System.Windows.Forms.DataGridView()
        Me.act_Insert = New System.Windows.Forms.Button()
        Me.act_Delete = New System.Windows.Forms.Button()
        CType(Me.SplitContainer1, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SplitContainer1.Panel1.SuspendLayout()
        Me.SplitContainer1.Panel2.SuspendLayout()
        Me.SplitContainer1.SuspendLayout()
        CType(Me.DataGridView1, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'SplitContainer1
        '
        Me.SplitContainer1.Dock = System.Windows.Forms.DockStyle.Fill
        Me.SplitContainer1.Location = New System.Drawing.Point(0, 0)
        Me.SplitContainer1.Name = "SplitContainer1"
        Me.SplitContainer1.Orientation = System.Windows.Forms.Orientation.Horizontal
        '
        'SplitContainer1.Panel1
        '
        Me.SplitContainer1.Panel1.Controls.Add(Me.DataGridView1)
        '
        'SplitContainer1.Panel2
        '
        Me.SplitContainer1.Panel2.Controls.Add(Me.act_Insert)
        Me.SplitContainer1.Panel2.Controls.Add(Me.act_Delete)
        Me.SplitContainer1.Size = New System.Drawing.Size(415, 202)
        Me.SplitContainer1.SplitterDistance = 165
        Me.SplitContainer1.TabIndex = 0
        '
        'DataGridView1
        '
        Me.DataGridView1.AllowUserToAddRows = False
        Me.DataGridView1.AllowUserToDeleteRows = False
        Me.DataGridView1.AutoSizeColumnsMode = System.Windows.Forms.DataGridViewAutoSizeColumnsMode.Fill
        Me.DataGridView1.AutoSizeRowsMode = System.Windows.Forms.DataGridViewAutoSizeRowsMode.AllCells
        Me.DataGridView1.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize
        Me.DataGridView1.Dock = System.Windows.Forms.DockStyle.Fill
        Me.DataGridView1.Location = New System.Drawing.Point(0, 0)
        Me.DataGridView1.Name = "DataGridView1"
        Me.DataGridView1.RowHeadersVisible = False
        Me.DataGridView1.SelectionMode = System.Windows.Forms.DataGridViewSelectionMode.FullRowSelect
        Me.DataGridView1.Size = New System.Drawing.Size(415, 165)
        Me.DataGridView1.TabIndex = 0
        '
        'act_Insert
        '
        Me.act_Insert.Anchor = CType(((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Bottom) _
            Or System.Windows.Forms.AnchorStyles.Right), System.Windows.Forms.AnchorStyles)
        Me.act_Insert.Location = New System.Drawing.Point(247, 3)
        Me.act_Insert.Name = "act_Insert"
        Me.act_Insert.Size = New System.Drawing.Size(79, 25)
        Me.act_Insert.TabIndex = 1
        Me.act_Insert.Text = "Insert"
        Me.act_Insert.UseVisualStyleBackColor = True
        '
        'act_Delete
        '
        Me.act_Delete.Anchor = CType(((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Bottom) _
            Or System.Windows.Forms.AnchorStyles.Right), System.Windows.Forms.AnchorStyles)
        Me.act_Delete.Location = New System.Drawing.Point(328, 3)
        Me.act_Delete.Name = "act_Delete"
        Me.act_Delete.Size = New System.Drawing.Size(79, 25)
        Me.act_Delete.TabIndex = 0
        Me.act_Delete.Text = "Delete"
        Me.act_Delete.UseVisualStyleBackColor = True
        '
        'Form1
        '
        Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0!, 13.0!)
        Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
        Me.ClientSize = New System.Drawing.Size(415, 202)
        Me.Controls.Add(Me.SplitContainer1)
        Me.Name = "Form1"
        Me.Text = "Form1"
        Me.SplitContainer1.Panel1.ResumeLayout(False)
        Me.SplitContainer1.Panel2.ResumeLayout(False)
        CType(Me.SplitContainer1, System.ComponentModel.ISupportInitialize).EndInit()
        Me.SplitContainer1.ResumeLayout(False)
        CType(Me.DataGridView1, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub

    Friend WithEvents SplitContainer1 As SplitContainer
    Friend WithEvents DataGridView1 As DataGridView
    Friend WithEvents act_Insert As Button
    Friend WithEvents act_Delete As Button
End Class

Select all Open in new window