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 not 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:
Module AppVars
Public gCurrentDataModel As String
Public gCurrentDatabaseType As String
Public gConnectionString As String
Public gInitialCatalog As String
Public gProviderString As String
Public gServerName As String
Public gUserID As String
Public gPassword As String
Public gCurrentTables() As String
Public gCurrentViews() As String
End Module
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.
gUserID and gPassword 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 gCurrentTables and gCurrentViews 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 contain 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:
Imports System.Collections
Imports System.IO
Imports System.Runtime.Serialization.Formatters.Binary
Imports System.Runtime.Serialization
Public Class frmConnect
#Region "Declarations"
Private mCurrentDataModel As String
Private mCurrentDatabaseType As String
Private mConnectionString As String
Private mProviderString As String
Private mServerName As String
Private mInitialCatalog As String
Private mServerPort As String
Private mDatabaseName As String
Private mUserID As String
Private mPassword As String
#End Region
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:
Private Sub btnOracleOK_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnOracleOK.Click
mCurrentDataModel = "MyOracle"
mCurrentDatabaseType = "Oracle"
mProviderString = txtOracleProvider.Text
mPassword = txtOraclePassword.Text
mUserID = txtOracleUserID.Text
mServerName = txtOracleDBname.Text
mConnectionString = "Provider=" & mProviderString & _
";Password=" & mPassword & _
";User ID=" & mUserID & _
";Data Source=" & mServerName
Dim cn As ADODB.Connection
cn = New ADODB.Connection
cn.CursorLocation = ADODB.CursorLocationEnum.adUseClient
Try
cn.Open(mConnectionString)
cn.Close()
cn = Nothing
Catch ex As Exception
MessageBox.Show(ex.Message.ToString(), _
"Application Settings Error", _
MessageBoxButtons.OK, _
MessageBoxIcon.Exclamation)
End Try
cn = Nothing
AppVars.gCurrentDataModel = mCurrentDataModel
AppVars.gCurrentDatabaseType = mCurrentDatabaseType
AppVars.gConnectionString = mConnectionString
AppVars.gProviderString = mProviderString
AppVars.gServerName = mServerName
AppVars.gUserID = mUserID
AppVars.gPassword = mPassword
Dim ht As New Hashtable
ht.Add("CurrentDataModel", gCurrentDataModel)
ht.Add("CurrentDatabaseType", gCurrentDatabaseType)
ht.Add("ConnectionString", gConnectionString)
ht.Add("ProviderString", gProviderString)
ht.Add("ServerName", gServerName)
ht.Add("UserID", gUserID)
ht.Add("Password", gPassword)
SerializeAppVars(ht)
StoreViewNames()
Me.Dispose()
End Sub
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:
Public Sub StoreViewNames()
Dim SchemaTable As DataTable
Dim arrViews() As String = Nothing
Dim arrTables() As String = Nothing
If AppVars.gConnectionString <> "" Then
Dim conn As New System.Data.OleDb.
OleDbConnection(AppVars.gConnectionString)
Try
conn.Open()
SchemaTable = _
conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, _
New Object(){Nothing, Nothing, Nothing, Nothing})
Dim int As Integer
Dim incr As Integer = 0
For int = 0 To SchemaTable.Rows.Count - 1
If SchemaTable.Rows(int)!TABLE_TYPE.ToString = "TABLE"
Then
Dim strTempTableName As String = _
SchemaTable.Rows(int)!TABLE_NAME.ToString()
ReDim Preserve arrTables(incr + 1)
arrTables(incr) = strTempTableName
incr = incr + 1
End If
Next
For int = 0 To SchemaTable.Rows.Count - 1
If SchemaTable.Rows(int)!TABLE_TYPE.ToString = "VIEW"
Then
Dim strTempViewName As String =
SchemaTable.Rows(int)!TABLE_NAME.ToString()
ReDim Preserve arrViews(incr + 1)
arrViews(incr) = strTempViewName
incr = incr + 1
End If
Next
Catch ex As Exception
MessageBox.Show(ex.Message.ToString(), _
"Application Settings Error", _
MessageBoxButtons.OK, _
MessageBoxIcon.Exclamation)
Finally
conn.Close()
conn = Nothing
End Try
AppVars.gCurrentViews = arrViews
AppVars.gCurrentTables = arrTables
End If
End Sub
In this bit of code, note in the declarations that a DataTable 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. The found tables are added to the string array containing the table names, and the 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's 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:
Private Sub SerializeAppVars(ByVal ht As Hashtable)
Dim strPath As String
strPath = Application.StartupPath & "\appset.con"
Dim fs As New FileStream(strPath, FileMode.OpenOrCreate)
Dim formatter As New BinaryFormatter
Try
formatter.Serialize(fs, ht)
fs.Close()
Catch ex As SerializationException
MessageBox.Show(ex.Message, "Application Settings " & _
"Serialization Error", _
MessageBoxButtons.OK, _
MessageBoxIcon.Exclamation)
End Try
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, and 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:
Private Sub btnOracleTest_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnOracleTest.Click
mProviderString = txtOracleProvider.Text
mPassword = txtOraclePassword.Text
mUserID = txtOracleUserID.Text
mServerName = txtOracleDBname.Text
mConnectionString = "Provider=" & mProviderString & _
";Password=" & mPassword & _
";User ID=" & mUserID & _
";Data Source=" & mServerName
Dim cn As ADODB.Connection
cn = New ADODB.Connection
cn.CursorLocation = ADODB.CursorLocationEnum.adUseClient
Try
cn.Open(mConnectionString)
cn.Close()
cn = Nothing
MessageBox.Show("Connection attempt successful, the " & _
"database connection information " & _
"provided has been successfully used " & _
"to connect to the database.", _
"Connection Successful", _
MessageBoxButtons.OK, _
MessageBoxIcon.Information)
Catch ex As Exception
MessageBox.Show("Connection attempt failed.", _
"Unable to Connect", _
MessageBoxButtons.OK, _
MessageBoxIcon.Error)
End Try
cn = Nothing
End Sub
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 straightforward; 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 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:
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports System.Collections
Imports System.Text
Imports System.IO
Imports System.Runtime.Serialization.Formatters.Binary
Imports System.Runtime.Serialization
Public Class frmMain
#Region "Declarations"
Public SelectedTable As String
Private mConnectionString As String
Private mTableSelected As Boolean
#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 are 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:
Private Sub frmMain_Load(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles Me.Load
Dim mConnectionString As String = ""
DeserializeAppSettings()
If AppVars.gConnectionString <> "" Then
mConnectionString = AppVars.gConnectionString.ToString()
Me.Text = "Database - " & _
AppVars.gCurrentDatabaseType.ToString
Else
Exit Sub
End If
If Not Trim(mConnectionString) = Nothing Then
Try
OpenTablesOnStart(Trim(mConnectionString))
Catch ex As Exception
MessageBox.Show(ex.Message.ToString(), _
"Data Load Error", _
MessageBoxButtons.OK, _
MessageBoxIcon.Exclamation)
End Try
End If
End Sub
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 contents look like this:
Private Sub OpenTablesOnStart(ByVal strConn As String)
Dim SchemaTable As DataTable
Dim conn As New System.Data.OleDb.OleDbConnection(strConn)
Try
conn.Open()
SchemaTable = _
conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, _
New Object(){Nothing, Nothing, Nothing, Nothing})
lstTables.Items.Clear()
lstViews.Items.Clear()
Dim int As Integer
For int = 0 To SchemaTable.Rows.Count - 1
If SchemaTable.Rows(int)!TABLE_TYPE.ToString = "TABLE" Then
lstTables.Items.Add(SchemaTable.Rows(int)!TABLE_NAME.ToString())
End If
Next
For int = 0 To SchemaTable.Rows.Count - 1
If SchemaTable.Rows(int)!TABLE_TYPE.ToString = "VIEW" Then
lstViews.Items.Add(SchemaTable.Rows(int)!TABLE_NAME.ToString())
End If
Next
Catch ex As Exception
MessageBox.Show(ex.Message.ToString(), "Data Load Error", _
MessageBoxButtons.OK, _
MessageBoxIcon.Exclamation)
End Try
conn.Close()
End Sub
A quick scan of the subroutine will reveal that it operates in a manner consistent with the subroutine used in the frmConnect class to populate the view and table arrays. Again, a DataTable 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:
Private Sub lstTables_SelectedIndexChanged(ByVal sender As _
System.Object, ByVal e As System.EventArgs) _
Handles lstTables.SelectedIndexChanged
mTableSelected = True
Dim tblName As String
tblName = lstTables.SelectedItem.ToString()
Dim conn As New System.Data.OleDb.OleDbConnection(mConnectionString)
Try
conn.Open()
lstFields.Items.Clear()
Dim dt_field As System.Data.DataTable = _
conn.GetOleDbSchemaTable( _
OleDb.OleDbSchemaGuid.Columns, _
New Object() {Nothing, Nothing, tblName})
For Each dr_field As DataRow In dt_field.Rows
Me.lstFields.Items.Add(dr_field("COLUMN_NAME").ToString)
Next
Catch ex As Exception
MessageBox.Show(ex.StackTrace, "Error", _
MessageBoxButtons.OK, _
MessageBoxIcon.Exclamation)
End Try
End Sub
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 DataTable 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 the 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:
Private Sub GetFieldInformationToolStripMenuItem_Click(ByVal sender _
As System.Object, ByVal e As System.EventArgs) _
Handles GetFieldInformationToolStripMenuItem.Click
Try
Dim cn As New OleDbConnection(mConnectionString)
Dim sSql As String
If mTableSelected = True Then
sSql = "SELECT [" & Me.lstFields.SelectedItem.ToString() & _
"] FROM [" & lstTables.SelectedItem.ToString() & "]"
Else
sSql = "SELECT [" & Me.lstFields.SelectedItem.ToString() & _
"] FROM [" & lstViews.SelectedItem.ToString() & "]"
End If
Dim da As New OleDbDataAdapter(sSql, cn)
cn.Open()
Dim ds As New DataSet
da.Fill(ds)
Dim sb As New StringBuilder
Dim tbl As DataTable = ds.Tables(0)
Dim col As New DataColumn
For Each col In tbl.Columns
sb.Append("Field Characteristics:" & _
Environment.NewLine & Environment.NewLine)
sb.Append("Data Type:" & Environment.NewLine)
sb.Append(col.DataType.ToString() & _
Environment.NewLine & Environment.NewLine)
sb.Append("Is Unique?" & Environment.NewLine)
sb.Append(col.Unique.ToString() & _
Environment.NewLine & Environment.NewLine)
sb.Append("Allow Nulls?" & Environment.NewLine)
sb.Append(col.AllowDBNull.ToString() & _
Environment.NewLine & Environment.NewLine)
sb.Append("Default:" & Environment.NewLine)
sb.Append(col.DefaultValue.ToString() & _
Environment.NewLine & Environment.NewLine)
sb.Append("Auto Incr:" & Environment.NewLine)
sb.Append(col.AutoIncrement.ToString() & _
Environment.NewLine & Environment.NewLine)
sb.Append("Caption:" & Environment.NewLine)
sb.Append(col.Caption.ToString())
MessageBox.Show(sb.ToString(), "Database Item " & _
"Specification for Column: " & _
col.ColumnName.ToString(), _
MessageBoxButtons.OK, _
MessageBoxIcon.Information)
Exit For
Next
Catch ex As Exception
MessageBox.Show(ex.StackTrace, ex.Message, _
MessageBoxButtons.OK, _
MessageBoxIcon.Exclamation)
End Try
End Sub
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 a 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 StringBuilder is configured to capture some of the column properties. This StringBuilder 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 functionalities were accomplished.
