About the OLE-DB adapter

The OleDbAdapter item stores SQL query statements and parameters so that you can specify what dataset to return and what data to move into and out of the resultant dataset. You can use an adapter to read, insert, update, and delete records in a data source.

You can configure sub-properties for the following properties of the adapter to specify how these operations occur:

• SelectCommand – Reference to an SQL statement or stored procedure name that retrieves records from the data source into a dataset that contains one or more data tables.

• InsertCommand – Reference to an SQL statement or stored procedure to insert new records of the dataset into the data source.

• UpdateCommand – Reference to an SQL statement or stored procedure to modify records in the data source according to the modified records of the dataset.

• DeleteCommand – Reference to an SQL statement or stored procedure to delete records from the data source according to deleted records of the dataset.

You use the Insert, Update, and Delete commands to reconcile changes made to the dataset with the associated data source.

The properties are themselves objects—they are instances of the OleDbCommandclass. The objects support a CommandText property that contains a reference to an SQL statement or stored procedure. The adapter supports specific methods to move data back and forth between the data source and the dataset.

Updating datasets

When you want to populate a table in a dataset, you call an adapter method that executes an SQL statement or stored procedure. Similarly, when you want to update the data source, you invoke an adapter method that calls an appropriate SQL statement or stored procedure to make the actual update in the data source.

Retrieving rows from a data source into corresponding tables in a dataset uses the Read method on the OleDbDataAdapter item. When you invoke the method, it transmits an SQL SELECT statement to the data source.

Transmitting changes made to a dataset table to the corresponding data source uses the Update method of the adapter. When you invoke the method, it executes whatever SQL INSERT, UPDATE or DELETE statements are required, depending on whether the affected record is new, changed, or deleted. For more information on these SQL statements, see Using parameters in OLE-DB adapter.

The design functionality of the OleDbAdapter item is two-fold:

• It is designed to have the query exist in only one location in Project Explorer, and for it to be accessible through code from multiple independent items elsewhere in the project.

• It enables you to define the query structure with the use of placeholders for parameters in the statement, then for specific parameter values to be passed to it.

Some guidelines for the OLE-DB adapter

• You can add OleDbAdapter items to only an OleDbDataSource item.

• Each OleDbAdapter item is a child of its parent OleDbDataSource item and is bound to the database connection settings of the OleDbConnection item, which determines to which OLE DB compliant database to connect.

• An OleDbDataSource item can have multiple OleDbVariable items, one for each SQL Select query.

• You manually trigger an OleDbAdapter item.

• If you want to poll the OLE DB provider, or you want to return only scalar values instead of datasets, use a variable instead of an adapter.

Related topics

• OLE-DB connector

• Add and configure an OLE-DB data source

• Add and configure an OLE-DB data connection