Beginning ADO.Net

ADO.Net programming is all about objects... and how to knit the objects together. To get the contents of a table in a database, you need at least four different ADO.Net objects.

The ADO.Net Object Model

The basis of the ADO.Net object model is shown below:

ADO Object Model

Opening a DataBase

Let's start with opening a Microsoft Access database..

This simple operation requires just a connection object. There are several "flavors" of the ADO.Net object libraries... one of which is designed for OleDb-style databases (such as Microsoft Access). So, in this case we tack on "OleDb" to the underlying object names.... "Connection" now becomes "OleDbConnection"

' Imports statement is not required, but makes the syntax easier to read
' (it must be at the very top)
Imports System.Data.oledb
 
Dim con As New OleDbConnection()

' Can't put the connection string as argument to open method like you did in VB6
con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\Test.mdb"
Try
      con.Open()
Catch ex As Exception
      MsgBox("Yikes! Can't open the database" & vbCr & ex.Message, MsgBoxStyle.Critical, _
       "Error!")
      Exit Sub
End Try

Filling a DataTable

Next, let's get the contents of one of the tables in the database. This requires four objects... the Connection object (described above), the Command object, the DataTable object (kinda self explanatory), and the DataAdapter object. The Command object is used to execute SQL commands on the database. The DataAdapter object is new to .Net and as its name implies... it is the "adapter" part of ADO.Net puzzle that is used to read/write to the database.

So, putting the pieces together, we need to use the Command object to execute an SQL statement that will read the contents of the table, and use the DataAdapter object to fill our DataTable. Notice how all of the objects are connected... con -> cmd -> da

' Imports statement is not required, but makes the syntax easier to read
' (it must be at the very top)
Imports System.Data.oledb

Dim con As New OleDbConnection()
Dim cmd As New OleDbCommand()
Dim dt As New DataTable
Dim da As New OleDbDataAdapter
' Tell the Connection object where the database is
con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\Test.mdb"

' The Command object needs to know which database it's connected to
cmd.Connection = con

' What SQL command do you want it to perform?
cmd.CommandText = "Select * from Table1"

' We need to tell the DataAdapter which Command object we're using
da.SelectCommand = cmd

' Finally we instruct the DataAdapter to fill our DataTable
da.Fill(dt)

The Fill() method of the DataAdapter contains an implied con.Open() and con.Close()... so if you hadn't opened the connection object "by hand" (using the code from the first block), then the DataAdapter itself would have opened the connection, done its thing, and then closed the connection.

Documentation Links

Binding to a Control

OK, we've got data in our DataTable! Let's display this data in a DataGrid on a windows form. First you'll have to drag and resize a DataGrid control onto a Form. After that you add the following code to "bind" the DataTable to the DataGrid.

DataGrid1.DataSource = dt

Yep.. that's it... Just tell the DataGrid that the source of its information is the DataTable you just filled. The DataGrid will now automagically show the contents of Table1 from the database.

Downloads/Links

Read the next section on Saving Data in ADO.Net
Download the VB.Net Source code example used in this article:ADONet_Test.zip