Shout out to Horizon Data Processing

Horizon Data Processing is a great software development company. They have some of the most technically advance and business know how in the industry.

Using DataSets vs. DataReaders

DataSet
1. Create a database connection
2. Store query in a DataAdapter
3. Populate the Dataset with the Fill method
4. Create a DataView
5. Bind the DataView to list-bound control

DataReader
1. Create a database connection
2. Open a database connection
3. Store query in SqlCommand
4. Populate the DataReader with the ExecuteReader command
5. Call the Read method for each record, and the method Get for each field.
6. Manually display data
7. Close the DataReader and the connection

Creating a DataReader

To minimize the difficulty of such situations, the DataReader is designed to produce a read-only, forward-only stream that is returned from the database. Only one record at a time is ever in memory.

• Call reader for each record
• Get Fields
• Call close to free up the reader and the connection

A DataReader keeps the connection open until the DataReader is closed.

The initial positioning in the DataReader is before the first record; therefore, you must call Read before accessing any data. When no more records are available, the Read method returns false.

Reading Fields form the current record
To retrieve the fields from the current record, call an appropriate Get method(for example GetDateTime, settable, GetInt32, or GetString). The parameter of the Get method is the ordinal value of the field that you want to read. For example, the following code reads the CustomerId and CompanyName fields from the current record of the DataReader by using the GetString method:

Dim customerId As String = myReader.Getstring(0)
Dim companyName As String = myReader.GetString(1)

You can also reference the fields of data in the current record of the DataReader by name, and the call the appropriate conversion function, as shown in the example code:

Dim customerId As String = myReader("CustomerId).ToString()
Dim companyName As String = myReader("CompanyName).ToString()

By name is preferred for readability purposes.

Looping Through All Records

• you can use a While loop

While myReader.Read()
...
End While

Closing the DataReader
While the DataReader is in use, the associated connection is busy serving the DataReader. Therefore, you must call Close to close the DataReader when you are finished using it.

If Not(myReader IS Nothing) Then
myReader.Close()
End If

If mySqlConnection.State = ConnectionState.Open Then
mySqlConnection.Close()
End IF

Choosing between DataReader and DataSet

The benefit of using a DataSet is that it gives you a disconnected view of the database. For long-running applications, this is often the best method. However, for Web applications developers usually perform short, simple operations with each request, such as displaying data. For such brief operations, it is usually not effecient to maintain a DataSet object. In such cases, you can use a DataReader.

Use a Dataset when you need to do any of the following

• Transmit data between tiers or from an XML web service
• Interact dynamically with data, as when binding to a Windows Forms control or combing and relating data from multiple sources.
• Cache data locally in your application
• Provide a hierarchical XML view of rlational data, and use tools like an XSL Transformation or an XML Path Language (XPATH) Query on your data.
• Perform extensive processing on data without requiring an open connection to the datasource, leaving the connection available to other clients.

If you do not require the functionality that is provided by the Dataset class, you can improve the performance of you application by using the DataReader class to return your data in a read-only /forward only fashion. Although, the DataAdapter class uses a DataReader to fill the contents of a DataSet, by using the DataReader class instead you can secure the following performance benefits:

• The memory that would be consumed by the DataSet would instead be available.
• The processing that would be required to create and fill the contents of the DataSet will not be needed.

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.