Now that we know what objects we will be using for this first part of the article, it is time to get stuck in and produce something a little more interesting.
Show me the Data!
Open the design of Form1 and double click in the form area to create the Form_Load event where we will place the code to retrieve all Contacts from our Address Book database. Now, before moving onto the actual code, we will add an "Imports" statement to the top of the code file in order to access the System.Data.OleDb objects without having to refer to the full namespace each time:
Imports System.Data.OleDb
So your code file should now look like the following screen shot:
Code file with Imports statement
By adding the above "Imports" statement, it allows us to write code such as "Dim connection As OleDbConnection". Without the above "Imports" statement we still have access to the OleDbConnection object, but in order to use it we would have to write code such as "Dim connection As System.Data.OleDb.OleDbConnection". You can think of the "Imports" statement as a shortcut to coding.
The next set of code to write will retrieve all data from the Contacts table within the database and show that data on the DataGrid. We will use the Form_Load event for this so that all data will be immediately visible when the program starts.
The first thing we need to do however is to create a Connection String for the database. A Connection String informs the OleDbConnection object what parameters to use when attempting to connect to the database. In it's most simplest form, the Connection String (for OleDb) will state the Provider (Microsoft Access, Microsoft Excel etc.) to use and the location of the data file.
The ConnectionString that we will be using looks like:
"Provider=Microsoft.Jet.OleDb.4.0;Data Source=" & Application.StartupPath & "\AddressBook.mdb"
This specifies that we will be using the Microsoft.Jet.OleDb.4.0 provider and the location of the database is in the StartupPath of the application (should be your bin\Debug folder of your project) and the database is called AddressBook.mdb.
So putting this together with the code necessary to populate the DataGrid, we should have the following code in our Form_Load event:
Code to retrieve data and populate DataGrid
If you want to run this now then press F5 or select "Start Debugging" from the "Debug" menu. When the form loads you should see all entries from the Contacts table of the Address Book database loaded into the DataGrid.
Looking at the above code we can see that Line 7 declares a variable called "conn" which is declared as an OleDbConnection object. This declaration creates a new instance of the OleDbConnection object and makes use of its overloaded constructor to supply the Connection String. Any communication from our program to the database will occur through this connection object.
Line 8 creates a new OleDbDataAdapter object. You can think of a DataAdapter as a bridge between the program and the database. Again, the OleDbDataAdapter has a number of overloaded constructors and in the above example, we are making use of one that allows us to specify a SELECT command and OleDbConnection object to use.
Note, in the above example we are selecting all fields from the Contacts table. However, it is considered (and rightly so) bad programming practice to make use of SELECT * queries. Usually, you would specify which fields to return from the database, such as SELECT ContactName, Telephone etc. but for the purpose of this tutorial, we will keep it simple. We will come back to the DataAdapter in a minute.
Line 9 creates a new DataTable variable. A DataTable is simply a collection of rows and fields and represents a database table in memory.
Line 11 makes use of the DataAdapter's Fill method to populate the DataTable with data retrieved from the database based on the SELECT statement used when constructing the DataAdapter. Quite a bit is actually happening at this point, namely:
-
A connection to the database is opened (based on the conn variable)
-
The DataTable is populated with data (based on the DataAdapter's SelectCommand Text). This includes both actual data and the table's schema (it's design definition).
-
The connection to the database is closed.
That's quite a lot of work for a single line call.
Finally, line 13 sets the DataSource property of the DataGrid to the DataTable. The DataGrid reads the schema information from the DataTable and uses the column names of the DataTable to create it's own columns and set the column heading text.