OleDbDataAdapter Class

System.Data.OleDb Namespace

Represents a set of data commands and a database connection which are used to fill the DataSet and update the data source.

Visibility	Constructor	Parameters
public	OleDbDataAdapter	( )
public	OleDbDataAdapter	( OleDbCommand selectCommand )
public	OleDbDataAdapter	( String selectCommandText , String selectConnectionString )
public	OleDbDataAdapter	( String selectCommandText , OleDbConnection selectConnection )

Visibility	Name	Value Type	Accessibility
public	DeleteCommand	OleDbCommand	[ Get , Set ]
public	InsertCommand	OleDbCommand	[ Get , Set ]
public	SelectCommand	OleDbCommand	[ Get , Set ]
public	UpdateCommand	OleDbCommand	[ Get , Set ]

Visibility	Name	Parameters	Return Type
protected	CreateRowUpdatedEvent	( DataRow dataRow , IDbCommand command , StatementType statementType , DataTableMapping tableMapping )	RowUpdatedEventArgs
protected	CreateRowUpdatingEvent	( DataRow dataRow , IDbCommand command , StatementType statementType , DataTableMapping tableMapping )	RowUpdatingEventArgs
public	Fill	( DataSet dataSet , Object ADODBRecordSet , String srcTable )	Int32
public	Fill	( DataTable dataTable , Object ADODBRecordSet )	Int32
protected	OnRowUpdated	( RowUpdatedEventArgs value )	Void
protected	OnRowUpdating	( RowUpdatingEventArgs value )	Void

Multicast	Name	Type
multicast	RowUpdated	OleDbRowUpdatedEventHandler
multicast	RowUpdating	OleDbRowUpdatingEventHandler

The OleDbDataAdapter serves as a bridge between an OLEDB data source and a DataSet for retrieving and saving data. The OleDbDataAdapter provides this bridge by using:

Fill to load data from the data source into the DataSet, and
Update to send changes made in the DataSet back to the data source.

When the OleDbDataAdapter fills a DataSet, it will create the necessary tables and columns for the returned data if they do not already exist. However, primary key information will not be included in the implicitly created schema unless the MissingSchemaAction property is set to AddWithKey.

You may also have the OleDbDataAdapter create the schema of the DataSet, including primary key information, before filling it with data using FillSchema.

Note that some OLE DB providers, including the MSDataShape provider, do not return base table or primary key information. As a result, the OleDbDataAdapter cannot properly set the PrimaryKey property on any created DataTable. In such cases you should explicitly specify primary keys for tables in the DataSet.

The OleDbDataAdapter also includes the SelectCommand, InsertCommand, DeleteCommand, UpdateCommand, and TableMappings properties for facilitating the loading and updating of data.

A new instance of OleDbDataAdapter can be constructed in four ways. For a list of the different versions and the parameters that can be used with each, see the OleDbDataAdapter constructor.

The following example shows using an OleDbDataAdapter to fill a DataSet with records retrieved from a data source. Here, the entire logic is encapsulated within a Page_Load event handler that essentially:

establishes a connection to the database,
initializes an instance of a DataSet to contain the database information,
and then enumerates the contents of the DataSet into an html table.

string html;

protected void Page_Load ( Object Src, EventArgs e ) {
   // set up the connection
   OleDbConnection myConn = new OleDbConnection (
      ( "Provider=Microsoft.Jet.OleDb.4.0; Data Source=" + 
      Server.MapPath ( "~/app_data/dbtutor.mdb" ) );
   // set up the query
   OleDbDataAdapter myAdapter = new OleDbDataAdapter (
      "SELECT * FROM Products", myConn );

   // instantiate dataset object
   DataSet myData = new DataSet ( );
   // fill with query results
   myAdapter.Fill ( myData, "Customers" );

   // display dataset contents into html table
   // first open the table and set up the table headers
   html += "<table cellspacing=1 class='data' width=80%>";
   html += "<tr>";
   html += "<th>Customer ID</th>";
   html += "<th>Company Name</th>";
   html += "</tr>";

   // loop thru the dataset
   foreach ( DataRow Customer in myData.Tables [ "Customers" ].Rows ) {   
      html += "<tr>";
      html += "<td>" + Customer [ "CustomerID" ].ToString ( ) + "</td>";
      html += "<td>" + Customer [ "CompanyName" ].ToString ( ) + "</td>";
      html += "</tr>";
   }

   // close the table
   html += "</table>";
}

dim html as string

protected sub Page_Load ( byVal Src as Object, byVal E as EventArgs ) {
   ' set up the connection
   dim myConn as new OleDbConnection (
      ( "Provider=Microsoft.Jet.OleDb.4.0; Data Source=" & _ 
      Server.MapPath ( "~/app_data/dbtutor.mdb" ) )
   ' set up the query
   dim myAdapter as new OleDbDataAdapter ( _
      "SELECT * FROM Products", myConn )

   ' instantiate dataset object
   dim myData as new DataSet ( )
   ' fill with query results
   myAdapter.Fill ( myData, "Customers" )

   ' display dataset contents into html table
   ' first open the table and set up the table headers
   html &= "<table cellspacing=1 class='data' width=80%>"
   html &= "<tr>"
   html &= "<th>Customer ID</th>"
   html &= "<th>Company Name</th>"
   html &= "</tr>"

   ' loop thru the dataset
   foreach Customer as DataRow in myData.Tables ( "Customers" ).Rows
      html &= "<tr>"
      html &= "<td>" & Customer ( "CustomerID" ).ToString ( ) & "</td>"
      html &= "<td>" & Customer ( "CompanyName" ).ToString ( ) & "</td>"
      html &= "</tr>"
   next

   ' close the table
   html &= "</table>"
end sub

Show me

OleDbConnection OleDbCommand DataSet DataTable

Home Getting Started Web Forms Web Applications ADO.NET ASP/ADO Back to Basics

OleDbDataAdapter Class

OleDbDataAdapter Class Members

Constructors

Properties

Methods

Events

Remarks

Example

See Also