Write your own Database code in VB .NET

In this next section, we'll take a look at the objects that you can use to open and read data from a Database. We'll stick with our Access database, the AddressBook.mdb one, and recreate what the Wizard has done. That way, you'll see for yourself just what is going on behind the scenes.

So close any open projects, and create a new one. Give it whatever name you like, and let's begin.

If you haven't yet downloaded the Address Book database, you can get it here:

Download the Address Book Database

 

The Connection Object

The Connection Object is what you need if you want to connect to a database. There are a number of different connection objects, and the one you use depends largely on the type of database you're connecting to. Because we're connecting to an Access database, we'll need something called the OLE DB connection object.

OLE stands for Object Linking and Embedding, and its basically a lot of objects (COM objects) bundled together that allow you to connect to data sources in general, and not just databases. You can use it, for example, to connect to text files, SQL Server, email, and a whole lot more.

There are a number of different OLE DB objects (called data providers), but the one we'll use is called "Jet". Others are SQL Server and Oracle.

So place a button on your form. Change the Name property to btnLoad. Double click your button to open up the code window. Add the following line:

Dim con As New OleDb.OleDbConnection

The variable con will now hold the Connection Object. Notice that there is a full stop after the OleDB part. You'll then get a pop up box from where you can select OleDbConnection. We're also creating a New object on this line.This is the object that you use to connect to an Access database.

 

 

Setting a Connection String

There are Properties and Methods associated with the Connection Object, of course. We want to start with the ConnectionString property. This can take MANY parameters . Fortunately, we only need a few of these.

We need to pass two things to our new Connection Object: the technology we want to use to do the connecting to our database; and where the database is. (If your database was password and user name protected, you would add these two parameters as well. Ours isn't, so we only need the two.)

The technology is called the Provider; and you use Data Source to specify where your database is. So add this to your code:

Dim dbProvider As String
Dim dbSource As String
Dim MyDocumentsFolder As String
Dim TheDatabase As String
Dim FullDatabasePath As String

dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"

First, we set up five string variables. The last line specifies the provider technology we're going to use to do the connecting, in this case Jet.OLEDB.4.0:

"PROVIDER=Microsoft.Jet.OLEDB.4.0;"

Notice the semicolon at the end of the line. This is needed.

If you're trying to connect to a modern Access database, however, then you may need a different provider, one called ACE:

dbProvider = "PROVIDER=Microsoft.ACE.OLEDB.12.0;"

There's even a version 15.0:

dbProvider = "PROVIDER=Microsoft.ACE.OLEDB.15.0;"

Next, you need the name of your database, and where it is. This could be a location on your computer, or on a server. What we'll do is to copy our database to the Documents folder of Windows. First, add a line for the name of your database:

TheDatabase = "/AddressBook.mdb"

To point to the Documents folder, add this line:

MyDocumentsFolder = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)

The line is a bit complex. After the equal sign, though, we have this:

Environment.GetFolderPath( )

The folder path you're getting goes between the round brackets of GetFolderPath:

Environment.SpecialFolder.MyDocuments

The Special Folder in this case is the MyDocuments folder (MyDocuments is the name of the Documents folder.) Other special folders are:

After setting a database name and pointing to the Documents folder, we can combine it for a data source:

dbSource = "Data Source = " & FullDatabasePath

What we're doing here is adding a path and a database to the text string "Data Source = ".

Your coding window should now look like like this:

VB NET code with a data source set up

We now need a connection string.

At the top of the code, we set up this line:

Dim con As New OleDb.OleDbConnection

The variable con now holds an OleDbConnection object. This con object needs a database provider, and a data source. You do this with the ConnectionString property:

con.ConnectionString = PROVIDER_AND_PATH_HERE

After the equal sign, you need a database provider and a data source (the data source is the path to your database).

We could have put these two things all on one rather long line:

con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = 'C:/databases/AddressBook.mdb'"

Here, we have the PROVIDER and the DATA SOURCE together. Separating the two is a semicolon. The Source is a file path pointing to a folder on our hard drive. This works perfectly well for a simple connection string.

But the path to our database (the Documents folder) is rather long, so we've spread out our code a bit more.

It does the same thing, though: passes the ConnectionString property the name of a data provider, and a path to the database.

So add this line to your code:

con.ConnectionString = dbProvider & dbSource

Here, we're using an ampersand character to combine the provider and data source.

Now that we have a ConnectionString, we can go ahead and open the database. This is quite easy - just use the Open method of the Connection Object:

con.Open( )

Once open, the connection has to be closed again. This time, just use the Close method:

con.Close( )

Add the following four lines to your code:

con.Open( )
MessageBox.Show("Database is now open")

con.Close()
MessageBox.Show("Database is now Closed")

Your coding window will then look like this:

Data Source and Connection String added in VB NET code

Before testing out your code, make sure you copy the AddressBook database file over to the Documents folder on your computer.

Test out your new code by running your programme. Click your button and the two message boxes should display. If they don't, make sure your Data Source path is correct. If it isn't, you might see this error message:

OleDbException Error

The error message is a bit on the vague and mysterious side. But what it's saying is that it can't find the path to the database, so it can't Open the connection. The line con.Open in your code will then be highlighted in green. You need to specify the correct path to your database. When you do, you'll see the message boxes from our code, and not the big one above.

 

Now that we've opened a connection to the database, we need to read the information from it. This is where the DataSet and the DataAdapter come in.

Buy our VB NET Book Here