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
|
