EGR 141Computer Problem Solving in

Engineering and Computer Science

Interfacing with a Database

in Visual Basic .NET 2005

Three components

• Database Connection– A connection to the database file

• Table Adapter– A connection to a specific Table

• Data Set– A recordset of a Table– Can be the whole table or some subset of a

table for example

Three Steps

• Connect to the Database

• Setup the Table Adapter using one or more tables

• Create the Data Set

Connect to the Database

Click on the DataGridView control and drag and drop it on your form.

We will establish a connection, setup a Table adapter, and create a dataset using this control.

Connect to the Database

Using the wizard, click the pull-down arrow next to ‘Choose Data Source’ and select ‘Add Project Data Source’

Connect to the Database

Select ‘Database’ and click NEXT

Then, click the ‘New Connection’ button and select ‘Microsoft Access Database File’ and click ‘Continue’

Connect to the Database

Using the ‘Browse’ button, search for the Database File Name.

Click ‘Test Connection’ to make sure the connection works properly.

Click OK

Connect to the Database

If you want VB .NET 2005 to make a copy of your database in your project directory so that all of the files, including the database, are in one folder on your hard drive, click YES. I would recommend doing this so that your project can be transferred to your USB stick and other computer easily.

Remember, no matter where you originally put your database file, the one that your program is using will now be in your project directory.

Connect to the Database

Click Next to save the Connection to the database

Create the Table Adapter & Dataset

Next, we will setup the table adapter and dataset in one step. Use the ‘plus’ sign to expand the list of Tables and select the Table(s) that we want to use in our program.

Note the name of the dataset that will be created. Click Finish.

Back at the Form

Back at your form, you will notice that the Data Grid shows the columns in the Data Set that was just created.

More importantly, notice that 3 things have been created for you to reference in your program:• Database Connection (Source)• Table Adapter• Data Set

Data Grid

If you do not wish to use the visible Data Grid, you may turn it invisible by setting the VISIBLE property to FALSE or simply delete it from your form. The connection, adapter, and data set will remain.

• Database Connection (Source)• Table Adapter• Data Set

Objects for using the database

Connection to database

Connection to table

A dataset to work with

Notice that VB .NET added an underscore to your dataset…

Fill the Table Adapter

Fill the data adapter with the dataset on the FORM LOAD event. Notice that the wizard put the code there automatically!

Code for manipulating records

Using WITH we can avoid retyping _141DataSet and just start with the dot

Dataset name

Table name

Field Name

Code for manipulating records

Don’t forget to Update your Database using the Table Adapter!