Advanced Visual Basic .NET - Project 3

Database Snooper - An Access Database browsing utility

Project 3

Database Snooper will allow the user to browse their computer or network and examine the contents of Microsoft Access database files.   What Database Snooper does:

1. Lets the user browse their computer or network to select a database file.
2. Lists all the tables that the database file contains.
3. Displays the fields--names and their data types--of any table the user selects from the tables list.
4. Displays the records of the selected table in a Data Grid control.
5. Allows the user to change the sort order of the records in the data grid by selecting the field to sort by from the fields list.

Removing your old project and creating a new one

Open your Solution from project 2 (<your name>.sln).

Right-click on the DatabaseViewer project in the Solution Explorer window and select Remove from the context menu: 

This does not delete your project, but just removes it from this Solution.  Click the OK button on the following dialog:

Drop down the File menu and select New Project under the Add Project menu item:

The Add New Project dialog appears:

Make sure that the Visual Basic Projects folder is open in the Project Types pane, and that the Windows Application template is selected in the Templates pane.  Type DatabaseSnooper in the Name textbox.  Then click the OK button.  This creates a new folder inside your Projects folder for the DatabaseSnooper project:

        ...My Documents\Visual Studio Projects\<Your Name>\DatabaseSnooper.

Note: When class is over, be sure to follow the instructions at the end of this project that tell you how to copy your project to your floppy diskette so you can take it home with you.

Rename the Form file

With the form file selected in the Solution Explorer window (as shown above)--so that it's properties are displayed in the Properties window--change the File Name property of the form file to frmSnooper.vb (don't forget to include the .vb extension). 

Change the Name and Text properties of the Form

Now click on the form in the designer window so that it's properties are displayed in the Properties window. Change it's Name property to frmSnooper, and it's Text property to Database Snooper.

Setting the Startup Object

Right-click on the DatabaseSnooper project in your Solution Explorer window, click on the Properties item at the bottom of the context-menu.  On the DatabaseSnooper Property Pages dialog, drop down the Startup Object combobox and choose frmSnooper.  Now click the OK button.

Unlike projects 1 and 2, in this project we cannot pre-connect to a database at design-time, because will are allowing the user to select database files dynamically, at run-time.  So, gone is the luxury of placing a Data Adapter control on our form and having the Data Adapter Configuration Wizard guide us through the process of configuring the Data Adapter and the Connection object--which was then created automatically for us.  In fact, we will not be adding any OleDb (database) controls to this project at design-time. 

Part A - Connecting to a Database and listing the Tables it contains in a Listbox

Stretch the blank form wider and add the controls shown in the above illustration.  They include, from the top down:

• Two Labels to display the name of the open database file:

• A GroupBox with a ListBox control inside it:

• Two Buttons:

• An OpenFileDialog control:

Leave the OpenFileDialog's default name property (OpenFileDialog1) unchanged. 

The first thing we'll do is add the code to the Open button's click event procedure to display the OpenFileDialog.  This will allow the user to browse their computer or network for a database file.  Add the following code to the btnOpen_Click event procedure:

'Set the Title and Filter properties, so that only .mdb files are listed
OpenFileDialog1.Title = "Select a Database"
OpenFileDialog1.Filter = "MDB Files (*.mdb) | *.mdb"
'Set the CheckFileExists property so that a warning appears if the user
'    types a filename of a non-existent file.  Then show the dialog.
OpenFileDialog1.CheckFileExists = True
OpenFileDialog1.ShowDialog()
'Abort the process and Exit Sub if the user doesn't select a database file
If OpenFileDialog1.FileName = "" Then Exit Sub

Using a control Constructor (New) to dynamically create a Data Connection object

Insert the following Imports statement above the Public Class frmSnooper declaration at the very top of the code window (the following line will be the first line of code in the code window):

Imports System.Data.OleDb

An Imports specification allows you to refer to a sub-class by name throughout your program without specifying the entire class structure that it belongs to.  For example, to create a reference to the Connection object class: OleDbConnection, you would normally need to specify the fully qualified class hierarchy, like this:

    System.Data.OleDb.OleDbConnection 

This is required because OleDbConnection is a sub-class of the System.Data.OleDb class.  By including the above Imports specification at the top of the code window, we only need to specify OleDbConnection in our code from then on.  Think of an Imports specification as a giant With clause for an entire module.

The following three lines of code are all that are required to create an OleDbConnection object dynamically (Note: Don't type the following three lines of code yet).  The actual control Constructor line of code is the second line that contains the New keyword. The New keyword calls the constructor of the OleDbConnection class, dynamically creating a new instance of a Connection object:

Dim dbConnection As OleDbConnection
'Use the New keyword to call the constructor of the OleDbConnection class,
'        and dynamically create a new instance of a Connection object.
dbConnection = New OleDbConnection(<ConnectionString >)
dbConnection.Open()

The ConnectionString  parameter is a literal string or string object that must, as a minimum, specify the Provider (specifying the type of database file), and Data Source (specifying the location and name of the database file).

To allow other enhancement options later, we will dimension the dbConnection class variable in the Declarations section--so that it has persistent, public scope. Add the following line of code to the Declarations section at the top of the code window now:

Dim dbConnection As OleDbConnection

Once the user has selected a database file--via the code we previously added to the btnOpen_Click event procedure--we will have all the information we need to build the ConnectionString parameter for the OleDbConnection Constructor code.  Add the following dimension statement at the beginning of the btnOpen_Click event procedure (where you have already entered the code to display the FileOpen dialog):

'Dimension a string object for our ConnectionString
Dim sConStr As String

Now add the following code to build the ConnectionString below all the previously added code in the btnOpen_Click event procedure:

'Specify the ConnectionString of the Connection object so that it includes, at a minimum,
'        the Provider (OLEDB) and Data Source (a user selected .mdb file) parameters.
sConStr = "Provider=Microsoft.Jet.OLEDB.4.0;"
'Use the user-selected database file as the Data Source value.
sConStr &= "Data Source=" & OpenFileDialog1.FileName & ";"

Now that we have built our ConnectionString (sConStr) we can dynamically create a Connection object (dbConnection).  Add the following code to the btnOpen_Click event procedure, below the above code:

'Close the Connection if it was previously Open, and destroy (Nothing) any previously
'        created instance of it to completely disconnect it from any database.
If Not dbConnection Is Nothing Then
    dbConnection.Close()
    dbConnection = Nothing
End If
'Dynamically create a New instance of a Connection object and connect
'        it to the database specified in the ConnectionString (sConStr)
dbConnection = New OleDbConnection(sConStr)
'Open the Connection
dbConnection.Open()

Here is a summary of the code entered into the the btnOpen_Click event procedure so far:

'Dimension a string object for our ConnectionString
Dim sConStr As String
'Set the Title and Filter properties, so that only .mdb files are listed
OpenFileDialog1.Title = "Select a Database"
OpenFileDialog1.Filter = "MDB Files (*.mdb) | *.mdb"
'Set the CheckFileExists property so that a warning appears if the user
'    types a filename of a non-existent file.  Then show the dialog.
OpenFileDialog1.CheckFileExists = True
OpenFileDialog1.ShowDialog()
'Abort the process and Exit Sub if the user doesn't select a database file
If OpenFileDialog1.FileName = "" Then Exit Sub
'Specify the ConnectionString of the Connection object so that it includes, at a minimum,
'        the Provider (OLEDB) and Data Source (a user selected .mdb file) parameters.
sConStr = "Provider=Microsoft.Jet.OLEDB.4.0;"
'Use the user-selected database file as the Data Source value.
sConStr &= "Data Source=" & OpenFileDialog1.FileName & ";"
'Close the Connection if it was previously Open, and destroy (Nothing) any previously
'        created instance of it to completely disconnect it from any database.
If Not dbConnection Is Nothing Then
    dbConnection.Close()
    dbConnection = Nothing
End If
'Dynamically create a New instance of a Connection object and connect
'        it to the database specified in the ConnectionString (sConStr)
dbConnection = New OleDbConnection(sConStr)
'Open the Connection
dbConnection.Open()

Using the GetOleDbSchemaTable method to enumerate the Tables inside a database

Our OleDb Connection object (dbConnection) has a GetOleDbSchemaTable method that can be used to list the tables inside the database that it is connected to. GetOleDbSchemaTable returns a DataTable object that contains the names and other information about the tables in the database in its rows (records).  The first parameter passed to the GetOleDbSchemaTable call is a OleDbSchemaGuid value that specifies the schema table to return. In this case we want just the Tables that the database contains so we specify OleDbSchemaGuid.Tables as the first parameter.  The second parameter is an Object array of restriction values that we can specify to filter the rows to be returned in the DataTable. When we pass values in this array, we must include empty strings or nulls for array elements that do not contain values.  We are only including "TABLE" as the last parameter to extract the table names--other information that is available includes type, owner, or schema.  Add the following code to the btnOpen_Click event procedure, below the code you've added so far (summarized above):

'Use the GetOleDbSchemaTable method of the Connection object to extract a DataTable
'        that contains the names of the Tables that the database contains.
Dim schemaTable As DataTable = _
        dbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, _
        New Object() {Nothing, Nothing, Nothing, "TABLE"})
'Once we're extracted the Table information, the Connection can be closed
dbConnection.Close()

Now we can iterate through the rows (records) of our newly created schemaTable to extract the names of the tables in the selected database and copy them into the lstTables listbox.  Add the following code to the btnOpen_Click event procedure, below the above code:

'Clear the lstTables listbox before adding items to it
lstTables.Items.Clear()
'The Rows.Count property of the schemaTable, is equal to the number
'        of tables that the database contains.
For i = 0 To schemaTable.Rows.Count - 1
     'The Item collection is the columns of the schemaTable.  Columns 0,
     '        and 1 are Null, column 2 contains the table names.
    lstTables.Items.Add(schemaTable.Rows(i).Item(2))
Next i

Testing the program so far

Save the project now.  Run the program.  You should be able to browse for an Access database after clicking the Open Database button.  Selecting a database file should then populate the lstTables listbox with the tables the database contains.  

Part B - Listing the Fields and their Data Types of the selected Table in a Listbox

Add a new GroupBox and Listbox that we will use to display field (column) information as shown in the above illustration:

Using a control Constructor (New) to dynamically create a Data Adapter object

Like we did with the Connection object above (dbConnection), we can also dynamically create a Data Adapter.  Let's begin by adding a new dimension statement to the Declarations section of the form:

Dim dbAdapter As OleDbDataAdapter = New OleDbDataAdapter()

Notice in the above code how we are using the New keyword to actually create an instance of a Data Adapt at the moment it is dimensioned.  Unlike our dynamically created Connection object (dbConnection)--which must be destroyed (set to Nothing) and recreated with each new database it is connected to--a Data Adapter need only to be created once. It can then be reused to connect to different tables from different databases, without destroying it and recreating it each time.

To bind the Data Adapter to a table in the selected database (dbConnection), we need to create an OleDbCommand object (selectCMD) that needs two parameters.  The first parameter is an SQL statement that specifies the fields and table name of the table we want the adapter bound to. The second parameter is the name of the connection object (dbConnection) that contains the table specified in the SQL statement of the first parameter.  Add the following code to the  lstTables_SelectedIndexChanged event procedure: 

Dim sFieldInfo, sDataType As String
Dim sTableName As String
Dim i As Integer
'Store the Table name selected in lstTables in sTableName
sTableName = lstTables.Items(lstTables.SelectedIndex)
'Create an OleDbCommand object that specifies an SQL Select Command
'        string that includes all the fields from the selected Table as the first
'        parameter, and the name of the Connection object that contains the
'        table (dbConnection) as the second parameter.
Dim selectCMD As OleDbCommand = _
          New OleDbCommand("SELECT * FROM " & "[" & sTableName & "]", _
          dbConnection)

Brackets ([ ]) are required around the table name (sTableName) in case spaces were used in the name.  Once we have our OleDbCommand object (selectCMD) we can bind the Data Adapter to a table with this single line of code (add this code below the above code, inside the lstTables_SelectedIndexChanged event procedure):

'Bind the Data Adapter to the selected Table
dbAdapter.SelectCommand = selectCMD

Using a control Constructor (New) to dynamically create a DataSet object

To enumerate the fields (columns) and the field types in the table, we need to use a DataSet.  The code to dynamically create a DataSet should look familiar to you after what we've done already with the Connection and Data Adapter constructors.  But instead of dimensioning the DataSet in the Declarations section of the form, we will dimension it locally (inside the  lstTables_SelectedIndexChanged event procedure) so that it is recreated each time we connect to a new table.  This is important because a DataSet can be filled with multiple tables!  Referencing the multiple tables that might fill a DataSet can be done with the dataset's Tables collection:

dbDataSet.Tables(<index>)

But we don't want to keep adding tables to our DataSet each time the user selects a new table.  So by dimensioning it locally, it is recreated and destroyed each time.  Add the following code below the previously entered code in the lstTables_SelectedIndexChanged event procedure:

'Create the DataSet object. By creating it locally (as apposed to creating it in
'        Declarations section) it is recreated each time, so new tables are NOT
'        appended to the DataSet.
Dim dbDataSet As DataSet = New DataSet()
'Clear and fill the new DataSet with the records from the selected Table
dbDataSet.Clear()
dbAdapter.Fill(dbDataSet, sTableName)

Once the Dataset is created and filled we can use its Columns collection to enumerate the names and data types of the fields in a record (row).  Add the following code below the above code in the lstTables_SelectedIndexChanged event procedure:

'Clear the lstFields listbox before filling it
lstFields.Items.Clear()
'Fill the lstFields listbox with the names and data type of each field in a record
For i = 0 To dbDataSet.Tables(0).Columns.Count - 1
     'Use a With clause here to make the code more readable
    With dbDataSet.Tables(0).Columns(i)
          '.DataType.ToString returns the data type preceded by the word System, like this:
          '        System.Integer
          'The following line of code extracts just the data type (i.e. Integer) that follows System.
        sDataType = .DataType.ToString.Substring(InStrRev(.DataType.ToString, "."))
          'Concatenate the DataType info to the ColumnName for display in the listbox
        sFieldInfo = .ColumnName & " -- " & sDataType.ToLower
        lstFields.Items.Add(sFieldInfo)
    End With
Next i

Testing the program so far

Save the project now.  Run the program.  Use the Open Database button to browse and select a database.  Now click on a table in the Tables listbox.  Are the names and data types of the fields displayed in the Fields listbox?

Part C - Adding a DataGrid to display the records of the selected Table

Add a new GroupBox and DataGrid to display the records of the selected table as shown in the above illustration:

Add these last two lines of code at the end of the lstTables_SelectedIndexChanged event procedure to display the name of the table in the DataGrid's caption, and bind it to the table in the Dataset object:

'Set the caption of the DataGrid to the name of the table
DataGrid1.CaptionText = sTableName
'Bind the DataGrid to the Dataset, so that it's records are displayed
DataGrid1.SetDataBinding(dbDataSet, sTableName)

Testing the program so far

Save the project now.  Run the program.  Use the Open Database button to browse and select a database.  Click on a table in the Tables listbox.  Are the records in the table displayed in the DataGrid?

Required Enhancements

• Display the Database file name

You've already added the label (lblDatabaseName) to display the name of the selected database file.  Now populate it.

• Sort the items in the DataGrid by the selected field in the Fields listbox

When the user clicks on a field in the Field listbox (lstFields) make sure that the records in the DataGrid are sorted by that field.  (Hint: Get help on the Data Adapter's SelectCommand.CommandText property).