Data access by ADO-Disconnected mode

1. Configure DataAdapter to retrieve information

1.1. DataAdapter

? Data adapters are a bridge between datasets interacting with data sources
– Data sets that correspond to local copies of the data source can interact with the data source.
? The primary two data adapters for a database
–sqldataadapter: Direct interaction with SQL Server without the OLE DB layer is faster than OleDbDataAdapter.
–oledbdataadapter: Applies to any data source that can be accessed with OLE DB data providers.

1.2. Xxxdataadapter Object Model

? Command Object
– a command object that reads data source information through a data adapter and saves it in the SelectCommand property of the data adapter.
– The data adapter can submit changes in the dataset to the command object in the data source and save the InsertCommand, UpdateCommand, and
The DeleteCommand property.
? DataTableMapping (data table Mapping) collection
The –datatablemapping collection holds the mappings between the tables in the DataSet, the fields and the tables and fields in the database.

? Properties of the data adapter
–selectcommand reading data from the data source
–insertcommand inserting data into a data source from a dataset
–updatecommand writes the updated rows of the dataset back to the data source
–deletecommand deleting data from a data source
? Methods of data Adapters
–fill () reads/updates data from the data source to the dataset using the SELECT statement specified by the SelectCommand property.
–update () invokes the corresponding command object that performs an insert, Delete, Update operation on a particular row in the DataSet DataTable object.

1.3. Create a DataAdapter that uses the new SELECT statement
? Create a data adapter that executes a SELECT statement
– Apply read-only access to data in a non-connected way.
? When you define a data adapter, you must specify
– A SELECT statement for the query
– A new or existing database connection

1.4. Create a DataAdapter that uses an existing stored procedure
? Can be programmed to create a execute stored procedure
– Specify a stored procedure for SelectCommand.
– You can specify stored procedures for InsertCommand, UpdateCommand, and deletecommond if needed.
? Must specify
– A new or existing database connection
– Stored procedure that is called

2. Populating a DataSet with DataAdapter

2.1. Populating a DataSet with DataAdapter

? Data sets can be populated with data adapters
– Call the data adapter's fill () method.
? Fill () method execution SelectCommand
– Populate the data tables in the dataset with the contents and structure of the query results.
? Performance can be improved by the following methods
–dataset.enforceconstraints=false.
– Call the Beginloaddata () method on the DataTable object.

2.2. Efficiently populate the Dataset
? To explicitly define a data structure before populating a dataset
– Data tables, data columns, and data associations are determined before the data is loaded.
– Enable data to be loaded more efficiently.
? How to explicitly define a data structure for a dataset
– Create a typed dataset class
DsCustomers.Customers.BeginLoadData ();
Dacustomers.fill (dscustomers.customers);
DsCustomers.Customers.EndLoadData ();
DataGrid1.DataSource = DsCustomers.Customers.DefaultView;
– or programmatically create data tables, data columns, data associations, and so on.

2.3. Assigning additional constraints to a DataSet
? Can populate datasets with unknown data structures
– The data structure of the dataset is unknown in the design phase.
– The data structure of the dataset is determined at run time based on the resulting data.
– You can control how the data structure of a dataset is created and generated at run time through a data adapter.
? Controlling data structure generation using the MissingSchemaAction property
? Call the FillSchema () method to create a new data structure for the dataset

2.4. Populating a DataSet with multiple DataAdapter
? One data set can be populated with multiple data adapters
– Each data adapter populates a separate table in the data set.
? Call the Fill () method for each data adapter
– Specify which table to populate in the data set
Dacustomers.fill (dscustomerorders.customers);
daOrders.Fill (dscustomerorders.orders);
DataGrid1.DataSource =
Dscustomerorders.customers;

3. Configure DataAdapter to update the background data source

3.1. How the DataSet tracks changes

? Each data row object has a RowState property
– Identifies the status of each row of data in the dataset.
– Type of Status
? DataRowState.Added the row has been inserted into the dataset
? DataRowState.Deleted The row has been deleted from the data set
? Datarowstate.detached The row has been created, but not added to the DataRowCollection in the data set
? Datarowstate.modified the row has changed
? Datarowstate.unchanged the line has not changed

? Each data set maintains two copies of each row of data
– Current version DataRowVersion.Current
if (row. RowState = = datarowstate.added)
row["FieldName", DataRowVersion.Current]
– Original Version DataRowVersion.Original
if (row. RowState = = datarowstate.deleted)
row["FieldName", DataRowVersion.Original]

3.2. Data Update command
? A SqlDataAdapter or OleDbDataAdapter object has some command objects that can be used to change data from the data source
–insertcommand
–updatecommand
–deletecommand
? Syntax: Data adapters for SQL and OLE DB and individual command objects are exactly the same
–public SqlCommand InsertCommand {get; set;}
3.3. Using CommandBuilder to generate commands

? InsertCommand
– Insert a row at the data source for all rows in the table that are rowstate to added. Inserts the values of all updatable columns (but does not include columns such as identities, expressions, or timestamps).
? UpdateCommand
– Update all rows in the table that are RowState to Modified at the data source. Updates the values of all columns, except for columns that are not updatable, such as identity columns or expression columns.
– Update all rows where the column values in the data source match the primary key column values of the row, and the remaining columns in the data source match the original values of the rows.
For more information, see "updated and deleted optimistic concurrency model" later in this topic.
? DeleteCommand
– Delete all rows in the table that are rowstate to deleted at the data source. Delete all rows that match the value of the primary key column for the row, and the values in the data source
The remaining columns match the original value of the row.

4. Saving data changes to a data source

4.1. Time to use the GetChanges method of the DataSet object
? Call the GetChanges () method when you need to pass data changes to another class that is used by another object

If  Then Dim  as  == dstemp.tables (0). DefaultViewEndIf

? Use the GetChanges () method to get a copy of the dataset that contains all the data changes in the data set

– Start with the data being loaded.
– Starting from the last Call of the AcceptChanges () method.

4.2. Merging changes into a DataSet object
? Use the merge () method to combine two datasets: an original dataset and a dataset containing only changes to the original dataset Adataset.merge (Anotherdataset)
? The two datasets to be merged have the same data structure

4.3. Updating a data source using a dataset
? The update () method of the data adapter invokes the appropriate SQL statement for each row in the specified data table that has been updated
–insert
–update
–delete
? code example
Adataadapter.update (Adataset, adatatable)

4.4. DataSet how data changes are accepted
? The AcceptChanges () method of the dataset is submitted since the data was loaded or all data changes from that dataset since the call
? You can call the AcceptChanges () method on the entire dataset, or on a data row object of a DataTable object

5. Conflict management

5.1. Conflicts
? The non-connected environment uses an optimistic concurrency mechanism
– The database lock is released immediately after the one-step data operation is complete.
– The non-connected environment uses an optimistic concurrency mechanism to ensure that other resources synchronize access to the database.
– The pessimistic concurrency mechanism keeps the database locked during the entire data operation.
? Data conflicts occur when you update a database
– Another app or service may have changed the data
? For example
– Delete rows that do not already exist
– Update columns that have been updated

5.2. Detecting conflicts
? The Data Adapter Configuration Wizard can generate SQL statements to monitor conflicts
? When you update a database
– The Data Update command compares the current data in the database with the original value.
– Any difference will throw a conflict.

5.3. Conflict resolution
? Use the HasErrors property to test for errors
–dataset.haserrors
–datatable.haserrors
–datarow.haserrors
? Select one of the following policies to resolve conflicts
– Overwrite data operations that were ever made with values in the dataset.
? Applies to the administrator system to force data to be overwritten in the data source
– Keep conflicting rows in the data set for subsequent re-update of the database.
? Save conflicting data in a data set for retry
? Default policy for the "Use optimistic concurrency" option

? Reject conflicting rows and roll back to the initial values in the data set
– Reject data that is conflicting in the local dataset, and roll back the data to the initial values that were loaded from the database.
– Call the RejectChanges () method on the conflicting dataset, data table, data row.
? Reject conflicting rows and get the most recent data from the database
– Call the Clear () method of the dataset to re-load the data from the database.

Data access by ADO-Disconnected mode