DataAdaptor

This is the class at the core of the disconnected data access model.
The data adapter can be thought of as a filter for the data that you are interested in.
You need to create a separate DataAdapter for each table.
The DataAdaptor allows you to communicate between the database (or data sources) and the DataSet.
Interaction with existing data sources is controlled through the DataAdapter.
Allows single DataSet to be populated from multiple data sources
There is a specific DataAdapter for each data provider.

Creating a DataAdaptor

Once you have an active connection, you need to create a data adapter inorder to extract specific information. A data adapter defines the specific information you will use and serves as a foundation for the dataset object.
The easiest way to create a data adapter is to drag a graphical table icon from Server explorer to the Windows Forms Designer.
Alternatively you can use the Data Adapter Configuration Wizard which lets you fine-tune your data selection by writing a SQL select query.
You can launch the Wizard by dragging the OleDBDataAdapter control from the toolbox (data tab) onto a form.

Retrieving Data - SelectCommand

• SelectCommand - must be set before the Fill method of the DataAdaptor

You can submit it as an argument when you are creating the new DataAdaptor or you can assign it later.

Dim objDataAdapter = New System.Data.OleDb.OleDbDataAdapter(sSQLQuery,  objDataConnection) 

It is much more intuitive though if you declare and assign the properties on separate lines.

Dim objDataAdapter = New System.Data.OleDb.OleDbDataAdapter 
Dim objdatacommand As New System.Data.OleDb.OleDbCommand 

objDataAdapter.TableMappings.Add("TableName","DataSetTableName") 
sSQLQuery = "SELECT Column1, Column2 FROM TableName" 
objdatacommand.Connection = objDataConnection 
objdatacommand.CommandText = gsSQLQuery 
objDataAdapter.SelectCommand = objdatacommand 

The connection does not have to be assigned seperately because the SelectCommand object has a connection.

Updating Data - DataAdaptor.Fill

Changes can then be made to the data that is contained within the DataTable objects
Before the database can be updated with the necessary changes it must know how to insert / update and delete records.
These should all be set before an update is sent to the database.

• InsertCommand - If rows have been added, the InsertCommand must be set before calling the Update.

• UpdateCommand

• DeleteCommand

Current information about the modified row is passed to the Command object through the Parameters collection.

Dim objDataAdapter = New System.Data.OleDb.OleDbDataAdapter 
Dim objDataSet As System.Data.DataSet 

objDataAdapter = New System.Data.OleDb.OleDbDataAdapter(sSQLQuery,  objDataConnection) 

objDataAdapter.Fill(objDataSet, "DataSetTableName") 
objDataAdapter.Fill(objDataSet) 

By default the Fill method creates a DataTable whose column have the same name and type as the columns in the source database table.
What name is given to the DataTable when you just pass the DataSet ?

DataAdaptor.TableMappings

This collection contains zero or more DataTableMapping objects, each one defining the name of the source table and the name of the corresponding dataset table..
This contains a collection of all the binding objects used to bind any property of the control to a field in the list data source.
Provides mappings between tables and columns
There may be times when you need more control on how the tables and columns are imported.

This can be useful when you are importing the same table more than once, each time with a different WHERE clause.

Each DataTableMapping object also exposes a collection of DataColumnMapping object which store the mappings between the individual columns.

Dim objDataTableMapping As System.Data.DataTableMapping 
objDataTableMapping = objDataAdapter.TableMappings.Add("TableName", "DataSetTableName") 

objDataTableMapping.ColumnMapping.Add("TableName_Column","DataSetTableName_Column") 

Any column that is not explicitly mapped is imported with its original column name.

DataAdaptor.Update

Used to send the changes back to the database.
When the Update method is called the changes are made using the following four commands

DataAdapter.FillSchema

Creates an empty tables with an identical schema.

Properties

Methods

Events

AddHandler objDataAdapter.RowUpdated, 

you can use RowUpdating to modify update behavior before it happens, to provide additional handling when an update will occur, to retain a reference to an updated row, to cancel the current update and schedule it for a batch process to be processed later, and so on.
RowUpdated is useful for responding to errors and exceptions that occur during the update. You can add error information to the DataSet, as well as retry logic, and so on.

The RowUpdatingEventArgs and RowUpdatedEventArgs arguments passed to the RowUpdating and RowUpdated events include the following: a Command property that references the Command object being used to perform the update; a Row property that references the DataRow object containing the updated information; a StatementType property for what type of update is being performed; the TableMapping, if applicable; and the Status of the operation.

You can use the Status property to determine if an error has occurred during the operation and, if desired, to control the actions against the current and resulting rows. When the event occurs, the Status property equals either Continue or ErrorsOccurred.
Setting the Status property to ErrorsOccurred causes an exception to be thrown. You can control which exception is thrown by setting the Errors property to the desired exception. Using one of the other values for Status prevents an exception from being thrown.