Delete a Document using an ADO.NET CommandBuilder

This example deletes a document from a data file using the CommandBuilder of an ADO.NET Data Adapter.

The code snippet sets the Select Command of a DataAdapter and calls its Fill method to load two columns of the MASTER table into a DataTable. All rows where [SERIAL NUMBER] < 10 are selected and deleted from the DataTable. The DataAdapter’s Update method is then called to commit these deletions. The CommandBuilder will automatically generate the Delete Command based on the adapter’s Select Command and deletions performed on the DataTable.

Assumptions:

The variable "cn" represents an open connection.

[INCLUDE NCSHEADER = TRUE] in the connection

PublicSub DeleteWithCommandBuilder(ByRef cn As _

System.Data.OleDb.OleDbConnection)

Dim myMasterTable AsNew System.Data.DataTable

'Create a command object that will return the serial number &

'document(number)columns of the master table

Dim cmdLoadRecords AsNew System.Data.Oledb.OleDbCommand

cmdLoadRecords.CommandText = _

"SELECT [SERIAL NUMBER], [DOCUMENT NUMBER] FROM MASTER"

cmdLoadRecords.CommandType = CommandType.Text

cmdLoadRecords.Connection = cn

Dim adpMaster AsNew System.Data.Oledb.OleDbDataAdapter

' Create an OleDbCommandBuilder and let it figure out

' what updates & deletes are required -

Dim cbDetails AsNew System.Data.OleDb.OleDbCommandBuilder(adpMaster)

' Setting the quote prefix and suffix is required

' because of field names containing spaces.

cbDetails.QuotePrefix = "["

cbDetails.QuoteSuffix = "]"

'Set the Select Command of the adapter

adpMaster.SelectCommand = cmdLoadRecords

'Populate a DataTable with the results of the Select Command

'This call would normally be enclosed in a Try/Catch.

adpMaster.Fill(myMasterTable)

'Select all documents whose serial number is < 10

Dim drs As System.Data.DataRow()

Dim dr As System.Data.DataRow

drs = myMasterTable.Select("[SERIAL NUMBER]< 10")

ForEach dr In drs

'Delete each row in the collection

dr.Delete()

Next

Console.WriteLine("No of rows deleted = " & _

myMasterTable.GetChanges(DataRowState.Deleted).Rows.Count())

'Call the adapter’s Update function

'This call would normally be enclosed in a Try/Catch.

adpMaster.Update(myMasterTable)

End Sub