ADO DataReader Vs DataAdapter

DataReader Objects
A data reader is like a firehose cursor. It is simular to a read-only/forward-only view of the data. A DataReader is returen after a commeand is executed against a database. Use this when you want to populate a listbox or a read only stand alone Table. Use the SqlCommand objects and ExecuteREader method to transfer data into a DataReader. The datareader behavior is database specific.

DataSet Objects
The dataset represents a cache of data, with database-like behavior. The dataset is a collection of one or more tables, more specifically, they are DataTable objects. The DataSet contains tables, columns, relationsships, constraints, and data. The DatasetSet is always disconnected. the Dataset provides a rich object model to employ passing data between various components. The dataset behavior is completely consistant and independent of the underlying database. As changes are made to the Dataset, they are tracked in a way similar to the way changes are tracked in a word processing document.

A dataset contain a collection of DataTable objects. A DataTable repesents the table's schema. A datatable also contains a collection of rows representing the data that is contained in the table.

You use the SqlDataAdapter object and the Fill method to populate the Dataset.

DataView Objects
A dataview enables you to crate different views of the data that is stored in a DataTable, a capability that is often used in data-binding applications. By using a DataView, you can display the data in a table with different sort orders, and you can filter the data by row state or based on a filter expression.

Note: This is different from the Select method of the DataTable, which returns a DataRow array from a table in accordance with a particular filter and/or sort order, and whose content reflects changes to the underlying table, but whose membership and ordering remain static.

The dynamic capabilities of the DataView make it ideal for data-binding applications.

DataAdapter Object
The DataAdapter class allows for the retrieval and saving of data between a DataSet and the source data store. It is responsible for pulling ou data from the physical store and pushing it into data tables and relations. The DataAdapter class is also responsible for transmitting any update, insertion, or deletion to the physical database. There are four command objects to make any updates: UpdateCommand, InsertCommand, DeleteCommand and SelectCommand.

Use the Fill method to populate the dataset table.
Because the Fill with not cause primary key and unique key information to be retrieved unless the AddWithKey is specified, you must set the must set the MissingSchemaAction property to AddWithKey.

mySqlDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

Name spaces for ADO.NET

• System.Data - base object and types for ADO.NET
• System.Data.OleDb - Managed OLE DB data store objects
• System.Data.SqlClient - SQL Server-specific implementation of ADO.NET objects
• System.Data.Common - Classes shared by the .NET providers
• System.Data.SqlTypes - SQL data types

Looping throw a DataSet table

Because the data is stored a a collection of rows in a table....

dim my DataRow as DataRow
For Each myDataRow In myDataSet.Tables("Customers").Rows
Console.Writeline(" CustomerId: {0}, myDataRow("CustmerId").ToString())
Next

Use the .Delete to remove a row from a DataSet.
Use the .RejectChanges on the Dataset to restore a row back to the orginal state.

The RowChanging event allows you to access both the original and new values to decide whether you want the edit to proceed.