System.Data.OleDb.OleDbDataAdapter
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
AcceptChangesDuringFill | Gets or sets a value indicating whether AcceptChanges is called on a DataRow after it is added to the DataTable during any of the Fill operations. |
AcceptChangesDuringUpdate | (Added 2.0) Gets or sets whether AcceptChanges is called during a Update. |
ContinueUpdateOnError | Gets or sets a value that specifies whether to generate an exception when an error is encountered during a row update. |
FillLoadOption | (Added 2.0) Gets or sets the LoadOption that determines how the adapter fills the DataTable from the DbDataReader. |
MissingMappingAction | Determines the action to take when incoming data does not have a matching table or column. |
MissingSchemaAction | Determines the action to take when existing DataSet schema does not match incoming data. |
ReturnProviderSpecificTypes | (Added 2.0) Gets or sets whether the Fill method should return provider-specific values or common CLS-compliant values. |
TableMappings | Gets a collection that provides the master mapping between a source table and a DataTable. |
Methods
Fill | Adds or refreshes rows in the DataSet to match those in the data source using the DataSet name, and creates a DataTable. |
FillSchema | Adds a DataTable to the specified DataSet. |
GetFillParameters | Gets the parameters set by the user when executing an SQL SELECT statement. |
ResetFillLoadOption | (Added 2.0) Resets FillLoadOption to its default state and causes Fill to honor AcceptChangesDuringFill. |
ShouldSerializeAcceptChangesDuringFill | (Added 2.0) Determines whether the AcceptChangesDuringFill property should be persisted. |
ShouldSerializeFillLoadOption | (Added 2.0) Determines whether the FillLoadOption property should be persisted. |
Update | Calls the respective INSERT, UPDATE, or DELETE statements for each inserted, updated, or deleted row in the specified DataSet from a DataTable named "Table." |
Events
RowUpdating | Raised before any update to a row from the DataSet has been processed at the data source. An Update, Insert, or Delete operation on a row (by a call to one of the Update methods) is about to begin. |
RowUpdated | Raised after any update to a row from the DataSet has been processed at the data source An Update, Insert, or Delete operation on a row (by a call to one of the Update methods) is complete. |
FillError | An error has occurred during a Fill operation. |
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.
© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext