VB.Net/Database ADO.net/SqlCommand Delete

Delete database record

<source lang="vbnet"> Imports System Imports System.Data Imports System.Data.SqlClient

public class MainClass

  Shared Sub Main()
     Dim thisConnection As New SqlConnection("server=(local)\SQLEXPRESS;" & _
         "integrated security=sspi;database=MyDatabase")
     Dim sql As String = "SELECT * FROM Employee"
     Dim deleteSql As String = _
        "DELETE FROM Employee " & _
        "WHERE ID = @ID"
     Try
        Dim da As New SqlDataAdapter
        da.SelectCommand = New SqlCommand(sql, thisConnection)
        Dim ds As New DataSet
        da.Fill(ds, "Employee")
        Dim dt As DataTable = ds.Tables("Employee")
        Console.WriteLine("Before altering the dataset")
        For Each row As DataRow In dt.Rows
           Console.WriteLine("{0} | {1} | {2}", _
              row("ID").ToString().PadRight(10), _
              row("FirstName").ToString().PadRight(10), _
              row("LastName"))
        Next
        Dim deleteCmd As New SqlCommand(deleteSql, thisConnection)
        deleteCmd.Parameters.Add("@ID", _
           SqlDbType.Int, 4, "ID")
        Dim filter1 As String = _
           "firstname = "Joe" and lastname = "Yin""
        For Each row As DataRow In dt.Select(filter1)
           row.Delete()
        Next

        da.DeleteCommand = deleteCmd
        da.Update(ds, "employee")
        Console.WriteLine("After altering the dataset")
        For Each row As DataRow In dt.Rows
           Console.WriteLine("{0} | {1} | {2}", _
              row("ID").ToString().PadRight(10), _
              row("FirstName").ToString().PadRight(10), _
              row("LastName"))
        Next
     Catch ex As SqlException
        Console.WriteLine("Error: " & ex.ToString())
     Finally
        thisConnection.Close()
        Console.WriteLine("Connection Closed")
     End Try
  End Sub

End Class

      </source>

Execute Delete command and check the result

<source lang="vbnet"> Imports System Imports System.Data Imports System.Data.SqlClient public class MainClass

  Shared Sub Main()
     Dim thisConnection As New SqlConnection("server=(local)\SQLEXPRESS;" & _
         "integrated security=sspi;database=MyDatabase")
     "Create Command object
     Dim nonqueryCommand As SqlCommand = thisConnection.CreateCommand()
     Try
        " Open Connection
        thisConnection.Open()
        Console.WriteLine("Connection Opened")
        "Create Command objects
        Dim scalarCommand As New SqlCommand("SELECT COUNT(*) FROM Employee", thisConnection)
        " Execute Scalar Query
        Console.WriteLine("Before INSERT, Number of Employees = {0}", _
           scalarCommand.ExecuteScalar())

        " Set up and execute DELETE Command
        nonqueryCommand.rumandText = _
           "DELETE FROM Employee WHERE " & _
           "Firstname="A" AND Lastname="A""
        Console.WriteLine("Executing {0}", nonqueryCommand.rumandText)
        Console.WriteLine("Number of rows affected : {0}", _
           nonqueryCommand.ExecuteNonQuery())
        " Execute Scalar Query
        Console.WriteLine("After Delete, Number of Employee = {0}", _
           scalarCommand.ExecuteScalar())
     Catch ex As SqlException
        " Display error
        Console.WriteLine("Error: " & ex.ToString())
     Finally
        " Close Connection
        thisConnection.Close()
        Console.WriteLine("Connection Closed")
     End Try
  End Sub

End Class

      </source>