The DataAdapter is used for retrieving data from the database and populating the dataset. The DataAdapter class is also responsible for propagating changes back to the database. In other words, the DataAdapter is the connector class that sits between the disconnected and the connected parts of ADO.NET. The DataAdapter connects to a database using Connection object and then it uses Command objects to retrieve data from the database and to send data back to the database. With the exception of the DataReader class, all data access in ADO.NET goes through the data adapter. The DataAdapter is the bridge between the database and the dataset.
The below statements instantiate the DataAdapter objects. We need to instantiate DataAdapters one for the data we need to retrieve and one for update.
myAccountAdapter = New SqlDataAdapter(str_Account_Select, myConnection)
Setting the DataAdapter command properties.
The DataAdapter class has four properties:
Each of the property perform a specific operation on a database. Listed below are the four properties and their purpose.
This read-write property returns or sets the SQL statement that will be used when rows are selected/retrieved from the data source.
This read-write property returns or sets the SQL statement that will be used when rows are inserted in to the data source.
This read-write property returns or sets the SQL statement that will be used when rows are updated in the data source.
This read-write property returns or sets the SQL statement that will be used when rows are deleted from the data source. When the DataAdapter needs to retrieve or send data to and from the database, it uses Command objects which must be specified. We specify the command objects for selecting, updating, and deleting rows in the database. This is done by creating command objects, and then assigning them to the appropriate DataAdapter property: SelectCommand, InsertCommand, UpdateCommand, and DeleteCommand are shown below.
myAccountAdapter.SelectCommand = comAccountSelect
myAccountAdapter.InsertCommand = comAccountInsert
myAccountAdapter.DeleteCommand = comAccountDelete
myAccountAdapter.UpdateCommand = comAccountUpdate
Add Update Command parameters.
comAccountUpdate.Parameters.Add("@id", SqlDbType.Char, 6, "AccountCode")
comAccountUpdate.Parameters.Add("@name", SqlDbType.Char, 30, "AccountName")
Add Delete Command parameters.
comAccountDelete.Parameters.Add("@id", SqlDbType.Char, 6, "AccountCode")
Here is the Updating the database using DataAdapter
The statement myAccountAdapter.Update(myAccountDataset, âAccountsTableâ) updates the AccoutsTable.
Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
MsgBox("Updation is over")
Database programming usingVisual Basic 2005