An Approach to Viewing the Structure of a Database Using VB 2005

Introduction:

This article describes an easy approach to examining all of the tables, views, and columns in a database, the article is written to describe an example application that may be used to connect to a database (Oracle, SQL Server, or MS Access), view that database's tables and views, and drill down into each table or view to generate a list of all of its contained columns.  Further, the application will allow the user to examine the definition associated with each column (e.g., its data type, caption, default value, etc.).

The application does not serve any particular purpose and has only a few key methods associated with it.  Whilst the application does not perform any sort of useful task, the application could be used to form the basis for some useful tool, such as one that would map the fields in one database table to the fields in another database table, or it could be used as the basis for a tool that allowed a user to formulate an ad hoc query.

Figure 1:  The Demonstration Application Running

Getting Started:

In order to get started, unzip the included project and open the solution in the Visual Studio 2005 environment.   In the solution explorer, you should note three significant files:

• AppVars.vb:  Containing some application wide variables contained in a module.
• frmMain.vb:  Containing the main application and most of the code.
• frmConnect.vb:  Containing a dialog used to connect to a database.

Application Variables.

Let's start off with the first of the project files mentioned, AppVars.vb.  Open the file to view it in the IDE.  This is simply a code module and it does contain any methods at all, only a collection of variables shared between frmConnect.vb and frmMain.vb.  The entire body of code is as follows:

The first variable defined is used to contain the name of the data model, this is arbitrary and serves no purpose within this application, it is merely a label used to identify the connection type.

The next variable is used to contain the database type, such MS Access or SQL Server.

The connection string variable, as you might have guessed, holds the connection string.

The initial catalog variable is used only in SQL Server connections and it contains, yep, the initial catalog name.

The provider string contains the name of the data provider.

User ID and Password are pretty self-explanatory; in the real world you may wish to encrypt the password value, in fact you may wish to store all of these items  within the application as resources or in some other format rather than to place them into a module file; but for the sake of this example, the module will do nicely.

The current tables and current views are string arrays used to capture all of the tables and views associated with a connection.  This has been added in the event that one may need to persist these items and make them more quickly available to controls within some more useful application.  I do populate these string arrays but I don't have any real use for them in this demonstration application.

Whilst this application does use the module with these variables, a better approach may be to create a  set of serializable classes that contains properties for each type of database, and instance, populate, and serialize the correct class type given the current connection.

Connection Dialog.

The connection dialog is contained in frmConnect.vb; this dialog is used to capture the variables necessary to create a viable connection to an Oracle, SQL Server, or MS Access database.  The dialog contains a tabbed pane with three panels, one for each connection type.  Each panel contains all of the controls necessary to generate a connection.  The user may test the connections from this dialog, and once the user accepts the dialog, the connection information will be persisted and made available to the application.

Figure 2:  Connection Dialog with SQL Server Options Displayed

The code is pretty simple, if you'd care to open the code view up in the IDE you will see that the code file begins like this:

Note that the imports include both serialization and IO; these are used to persist the connection information.  In the declarations region, note that local copies of the variables used in the AppVars.vb file are declared (except for the view and table arrays).  These are the member variables that will be used within this class.

For each of the OK buttons (one for each database type supported), there will be a handler written that will basically conform to the following code block:

This handler begins by capturing the user input from the dialog  and the local variables are set to the form values; I did not do it in the example but you should validate all of these inputs.  Next, the handler will attempt to use the values to create a connection object and it will try to open this connection to confirm that it works.   If the attempt fails, the user will be alerted, if it passes, the values will be added to a hash table and the hash table will be serialized and persisted into a file in the application's path; this occurs in the SerializeAppVars() call.  After the serialization occurs, the view and table names will be stored by the StoreViewNames() call, and lastly, the form will be disposed of by the Me. Dispose call.

Each of the three database tabs has an OK button and each of those buttons is handled in a manner consistent with this approach.  Examine each specific handler to observe the minor differences related to that particular tab's variables.
The StoreViewNames () subroutine is used to persist the captured values so that they may be recovered the next time the application is started.  This code is a little more interesting:

End Sub

In this bit of code, note in the declarations that a data table is declared and named "SchemaTable"; after establishing an OleDbConnection, note the bit of code that assigns that table to hold the connection's schema table; it is this table that makes it easy to examine the structure of the database and its contents.   After the schema table is set, the rest of the code loops through the table's rows and finds first the tables and next the views.  Found tables are added to the string array containing the table names and found views are added to the string array containing the view names.

Once the arrays are set, the subroutine closes by passing the two arrays to the AppVars current views and current tables variables.

The SerializeAppVars () subroutine is used to persist the variables into a file stored in the application's path; this file is used to permit the application to open with the last established connection already in place.  The code used to serialize the values is as follows:

End Sub

As you can see, this is a pretty simple subroutine.  It accepts the hash table generated in the OK button event handler, opens a file stream to a file called "appset.con" which is placed in the application startup path, a binary formatter is used to serialize the hash table and store it in the file.

Each of the dialog's three tabs also contains a test button.  As with the OK button, the functionality is virtually the same for all three test buttons and so I will only show one in this document.  Review the example project to see the differences between the three handlers but note that basically only the variables and the format of the connections string vary from button to button. The Oracle test button's handler looks like this:

Oddly enough, I used an ADO connection to perform the test, you may replace this with an OleDbConnection object and perform the same test.  The code is straight forward, you will note that the form variables are collected and passed to the local variables.  These variables are used to format a connection string, the connection string is opened as a test, if it opens, the test passes, if it does not open, the test fails.  The user is informed of either outcome.

The rest of the code in the connection dialog is pretty standard and is used to do things like close the form or browse for a file.  Review the contents of the class in the IDE to review the rest of the content; there is sufficient commenting applied to make it self-explanatory.

The Main Form.

The main application is contained in the frmMain.vb class.  This form is used to gain access to the connection dialog, and to load the database information into the form's controls.  The form is structured with a menu at the top, this menu contains the options to exit the application, to create a new connection, to view the current connection, and to load the schema information for the current database associated with the connection.  In the main area of the form, there are three group boxes, one contains a list box control used to display the tables contained in the current database, one contains a list box control used to display the views contained in the current database, and one contains a list box used to display the columns contained in any view or table selected from the table or view list box controls.

Figure 3:  The Main Form with an SQL Server Connection Active

The class starts out with the following code:

#End Region

In the imports section, note the data and data related class library additions; you may need to open the project references and add any missing references to the project if any of these imports is not supported when you load the application into the IDE.  Following the imports, a declarations region was added and three variables declared.  These variables are subsequently used within this class.

Figure 4:  Demonstration Project References

The next section of code worth looking at is the form load event handler, it looks like this:

This event handler is simple enough, if an existing connection file exists it is opened and deserialized.  The form label is updated to show the current database type and if the connection string exists, it is passed to a subroutine called OpenTablesOnStart which subsequently loads the tables and views associated with the current connection into the table and view list box controls.

The OpenTablesOnStart subroutine called by the form load event handler is used to populate the table and view list boxes, its content looks like this:

A quick scan of the subroutine will reveal that is operates in a manner consistent with the subroutine used in the frmConnect.vb class to populate the view and table arrays.  Again, a data table variable is declared and it is set to contain the schema for the target database.  This table is then looped twice, once to capture the tables and once to capture the views.  The views and tables collected are dropped into the list boxes on the main form.

Each of the list boxes containing the views and the tables has a handler used to recover the columns from that view or table and to display the column collection in the fields list box.  Both handlers operate in basically the same way, here is an example showing the table list's handler:

Again, this is all pretty simple stuff, the Boolean set in the beginning of the subroutine is used to tell the application that the last item selected was a table; this is used elsewhere.  The view list handler sets this Boolean to false indicating that views are active rather than tables.

Next, the subroutine captures the selected items and sets a table name variable to contain the selected text.  A connection is created using the current database connection string.  The connection is opened, the list box is cleared of any existing content, and a data table is declared and set to hold the schema table for the current database as based on the connection.  Once that is set, the schema table's rows are looped and each column name is captured and added to the list.  At the end of it, the fields list box will contain all of the column names associated with last selected table.  The view list handler does the exact same thing.

There are a few more simple functions contained in the class but the last one that I will discuss is the handler for the context menu used to recover the field properties for a column name selected from the fields list box.  This context menu is active whenever the user selects an item from the fields list and right clicks on it.  The context menu will reveal a single option and that option will call a function used to gather a bit of information regarding the selected field such as the data type, the default value, caption, etc.  That handler looks like this:

At the beginning of the subroutine, a select statement is formatted based upon whether or not the last item selected was a table or a view (remember the Boolean values set in the table and view list box handlers).  The  SQL statement is used along with a connection to the database to execute a query  against that database; this query pulls the field name selected from the fields list box from the last selected table or view.  A data adapter and data set are defined and populated.  Once these tasks have been accomplished, a data table is defined and set to the data set's only table, an empty data column is declared and then used to iterate through the table columns collection.  When the data column is set to the first column in the table column collection (there is only one column and one table) a string builder is configured to capture some of the column properties.  This string builder is then passed to a message box and shown to the user.  After the first pass completes, the "for" loop is manually exited.  Any errors are trapped and sent to the user in the catch block.

Figure 4:  Displaying Column Properties from the Context Menu Option

Summary.

This application is intended to demonstrate one approach to building an application capable of viewing the contents of a database dynamically and based strictly upon making a connection to either an MS Access, Oracle, or SQL Server database.  It is not the only way to accomplish this task, it is just one way to do it.  You can easily modify the approach to use other connection types, or to add new connection types, and you can modify the application to display information that I did not address in this demonstration.  I did not cover all of the code contained in the demonstration application and therefore I would recommend that you review each of the solution's classes to note the manner in which some of the unaddressed functionality was accomplished.