Data Access using ADO.NET Objects

prepared by John Papproth
on June 29th, 2005

ADO.NET is a disconnected data access model.
The model contains several classes that are found in the System.Data namespace.

There are two mirrored class groups in Visual Studio.NET 2003:
SQL prefixed classes (SQLConnection, SQLDataAdapter) are especially tuned for a Microsoft SQL Server Environment.
OleDb prefixed classes (OleDbConnection, OleDbDataAdapter) are intended for non SQL Server Environments such as Oracle, DB2, and Microsoft Access.

The following example uses Microsoft Access, the OleDb ADO.NET objects, and Visual Basic.NET to create a simple form driven update.

Example:
1. Create the data base and tables.
2. Create the form.
3. Create the ADO.NET class references.
4. Write code to instantiate the classes.

1. Create the data base and tables.
Create an Access Database.
Create a Table within the Access Database.

2. Create the form.
Create one label and textbox pair for each column in your table.

3. Create the ADO.NET class references.
Private cn As OleDb.OleDbConnection
Private da As OleDb.OleDbDataAdapter
Private ds As DataSet
Private dt As DataTable

4. Write code to instantiate the classes.

Create the connection
cn = New OleDb.OleDbConnection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Application.StartupPath & "\YourDatabaseNameHere.mdb"

Create a new adapter, and set the connection in the command objects
(Replace the Select * with your Column Names)
da = New OleDb.OleDbDataAdapter("Select * from YourTableNameHere", cn)
da.SelectCommand.Connection = cn

Create an empty dataset
ds = New DataSet

Fill the dataset
da.Fill(ds, "YourTableNameHere")

Create the data table
dt = ds.Tables("YourTableNameHere")

Bind the fields to the data table

Binding a combo box control:
cboComboBox.DataSource = dt
cboComboBox.DisplayMember = "YourColumnNameHere"

Binding a text box control:
txtTextBox.DataBindings.Add("Text", dt, "YourColumnNameHere")