Click here to Skip to main content
15,867,308 members
Articles / Database Development / SQL Server
Article

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

Rate me:
Please Sign up or sign in to vote.
4.55/5 (16 votes)
14 Sep 200612 min read 55.3K   329   51   3
This article describes an easy approach to examining all of the tables, views, and columns in a database.

Screenshot

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.

Image 2

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:

VB
Module AppVars

    Public gCurrentDataModel As String     'current data model selected
    Public gCurrentDatabaseType As String  'type of database used
    Public gConnectionString As String     'full connect string
    Public gInitialCatalog As String       'sql server initial catalog for
    Public gProviderString As String       'provider name used for access
    Public gServerName As String       'server name for live data access
    Public gUserID As String           'its the user id used to connect
    Public gPassword As String         'its the password value used
    Public gCurrentTables() As String  'tables stored to populate lists
    Public gCurrentViews() As String   'views stored to populate lists

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.

Image 3

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:

VB
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:

VB
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

    'Test Connection
    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

    'set app setting global variables
    AppVars.gCurrentDataModel = mCurrentDataModel
    AppVars.gCurrentDatabaseType = mCurrentDatabaseType
    AppVars.gConnectionString = mConnectionString
    AppVars.gProviderString = mProviderString
    AppVars.gServerName = mServerName
    AppVars.gUserID = mUserID
    AppVars.gPassword = mPassword

    'create hashtable to hold settings
    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)

    'serialize data
    SerializeAppVars(ht)

    'cache view names
    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:

VB
Public Sub StoreViewNames()

    Dim SchemaTable As DataTable
    Dim arrViews() As String = Nothing
    Dim arrTables() As String = Nothing

    If AppVars.gConnectionString <> "" Then

        'Connect to the database
        Dim conn As New System.Data.OleDb.
        OleDbConnection(AppVars.gConnectionString)

        Try
            conn.Open()

            'get view names
            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:

VB
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:

VB
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

    'Test Connection
    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.

Image 4

Figure 3: The main form with an SQL Server connection active

The class starts out with the following code:

VB
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.

Image 5

Figure 4: Demonstration project References

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

VB
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:

VB
Private Sub OpenTablesOnStart(ByVal strConn As String)

    Dim SchemaTable As DataTable

    'Connect to the database
    Dim conn As New System.Data.OleDb.OleDbConnection(strConn)

    Try

        conn.Open()

        'Get table and view names
        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:

VB
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:

VB
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.

Image 6

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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Software Developer (Senior)
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralMy vote of 5 Pin
Eduardo_David11-Jul-10 3:09
Eduardo_David11-Jul-10 3:09 
GeneralOpen UDL files for generic OLE DB connection Pin
Michael Freidgeim25-Sep-06 22:35
Michael Freidgeim25-Sep-06 22:35 
GeneralRe: Open UDL files for generic OLE DB connection Pin
salysle26-Sep-06 15:01
salysle26-Sep-06 15:01 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.