Private Sub UseCommandBuilder()
Dim connectionString As String = _
"Integrated Security=SSPI;Persist Security Info=False;" + _
"Initial Catalog=Northwind;Data Source=LAP800" Dim connection As SqlConnection = _
New SqlConnection(connectionString)
connection.Open()
Try
Dim command As SqlCommand = _
New SqlCommand("SELECT * FROM Customers", connection) Dim adapter As SqlDataAdapter = _
New SqlDataAdapter(command)
Dim builder As SqlCommandBuilder = _
New SqlCommandBuilder(adapter)
' Show commands
Console.WriteLine(builder.GetInsertCommand().CommandText)
Console.WriteLine(builder.GetUpdateCommand().CommandText)
Console.WriteLine(builder.GetDeleteCommand().CommandText) Dim table As DataTable = New DataTable
adapter.Fill(table) 'Use Insert
Dim row As DataRow = table.NewRow
row("CustomerID") = "PAULK"
row("CompanyName") = "Pauly's Edibles"
row("ContactName") = "Paul Kimmel"
row("ContactTitle") = "The Fat Man"
table.Rows.Add(row) 'Use Update
Dim results() As DataRow = _
table.Select("CustomerID = 'ALFKI'")
results(0)("Region") = "DE" 'Use Delete
Dim deleteThese() As DataRow = _
table.Select("CustomerID = 'FARLA'")
For Each row In deleteThese
row.Delete()
Next adapter.Update(table)
table.AcceptChanges() Catch ex As Exception
Debug.WriteLine(ex.Message)
Throw
Finally
connection.Close()
End Try
End Sub End Module
| 
