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:
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