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.) through a context menu option.
 
The application does not serve any particular purpose and has only a few key methods associated with it. While 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 using a developer defined query builder.
 
1.gif
 
Figure 1: The demonstration application running
 
2.gif
 
3.gif
 
Figure 2: Pulling up information on a specific field
 
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:
  • frmDataDisplay.cs: Containing the main application and most of the code.
  • frmConnect.cs: Containing a dialog used to connect to a database.
Application Properties:
 
The application properties are used to store elements of the connection string as well as the connection string itself. In the project, open up the properties and select the settings tab to see the collection of properties used in this application. The scope of each property is set to "User" which will allow application users to set and save the properties between uses of the application. Each value is set to temp initially. The property names describe the purpose of each specific property; for example "ConnString", as you can probably guess, is used to hold the connection string property.
 
4.gif
 
Figure 3: Application Properties
 
Connection Dialog.
 
The connection dialog is contained in frmConnect.cs; 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.
 
Untitled-5.gif
 
Figure 4: 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 as follows:
  1. using System;  
  2. using System.Collections;  
  3. using System.Configuration;  
  4. using System.ComponentModel;  
  5. using System.Data;  
  6. using System.Data.OleDb;  
  7. using System.Drawing;  
  8. using System.Text;  
  9. using System.Windows.Forms; 
The class begins with the import of the required libraries; most notably the System.Data and System.Data.OleDb libraries are required to interact with the three database types used by the application (Oracle, Access, and SQL Server).
 
Following the imports, the namespace and class are defined and a default constructor added.
  1. namespace DBSpy {  
  2.     public partial class frmConnect: Form {  
  3.             /// <summary>  
  4.             /// Default constructor  
  5.             /// </summary>  
  6.             public frmConnect() {  
  7.                 InitializeComponent();  
  8.             } 
Next up is the button click event handler for the button used to save a defined connection to an Oracle database as property settings made available across the application. The handler saves all of the user defined elements of the connection string as well as the formatted connection string itself and also tests the connection string to make sure that it works. The code is annotated to describe what is happening in each section of the code. 
  1.     /// <summary>  
  2.     /// Store the Oracle settings and test the connection  
  3.     /// string  
  4.     /// </summary>  
  5.     /// <param name="sender"></param>  
  6.     /// <param name="e"></param>  
  7.     private void btnOracleOK_Click(object sender, EventArgs e) {  
  8.     // Future use; if a current data model and database  
  9.     // type need to be identified and saved with the connect  
  10.     // string to identify its purpose  
  11.     Properties.Settings.Default.CurrentDataModel = "MyOracle";  
  12.     Properties.Settings.Default.CurrentDatabaseType = "Oracle";  
  13.   
  14.     // Set the actual connection string properties into  
  15.     // the application settings  
  16.     Properties.Settings.Default.ProviderString = txtOracleProvider.Text;  
  17.     Properties.Settings.Default.Password = txtOraclePassword.Text;  
  18.     Properties.Settings.Default.UserID = txtOracleUserID.Text;  
  19.     Properties.Settings.Default.ServerName = txtOracleDBname.Text;  
  20.   
  21.     // Set the connection string  
  22.     Properties.Settings.Default.ConnString = "Provider=" + Properties.Settings.Default.ProviderString + ";Password=" +  
  23.         Settings.Default.Password + ";User ID=" + Properties.Settings.Default.UserID + ";Data Source=" + Properties.Settings  
  24.         .Default.ServerName;  
  25.   
  26.     // Save the property settings  
  27.     Properties.Settings.Default.Save();  
  28.   
  29.     //Test Connection  
  30.     if (Properties.Settings.Default.ConnString != string.Empty) {  
  31.         using(OleDbConnection conn = new OleDbConnection(Properties.Settings.Default.ConnString)) {  
  32.             try {  
  33.                 // test with an open attempt  
  34.                 conn.Open();  
  35.                 this.Dispose();  
  36.             } catch (Exception ex) {  
  37.                 // if the connection fails, inform the user  
  38.                 // so they can fix the properties  
  39.                 MessageBox.Show(ex.Message, "Connection Error");  
  40.             }  
  41.         }  
  42.     }  
  43. } 
The next section of the code is used to handle the Oracle connection string test; even though attempts to save the connection properties also tests the connection, this method is made available to allow the user to test a connection and view whether or not the connection string passes. Again, the code is annotated to describe each section of the code.
  1. /// <summary>  
  2. /// Test the Oracle Connection String  
  3. /// </summary>  
  4. /// <param name="sender"></param>  
  5. /// <param name="e"></param>  
  6. private void btnOracleTest_Click(object sender, EventArgs e) {  
  7.    try {  
  8.        // Future use; if a current data model and database  
  9.        // type need to be identified and saved with the connect\  
  10.        // string to identify its purpose  
  11.        Properties.Settings.Default.CurrentDataModel = "MyOracle";  
  12.        Properties.Settings.Default.CurrentDatabaseType = "Oracle";  
  13.   
  14.        // Set the actual connection string properties into  
  15.        // the application settings  
  16.        Properties.Settings.Default.ProviderString = txtOracleProvider.Text;  
  17.        Properties.Settings.Default.Password = txtOraclePassword.Text;  
  18.        Properties.Settings.Default.UserID = txtOracleUserID.Text;  
  19.        Properties.Settings.Default.ServerName = txtOracleDBname.Text;  
  20.   
  21.        // Set the connection string  
  22.        Properties.Settings.Default.ConnString = "Provider=" + Properties.Settings.Default.ProviderString + ";Password=" +  
  23.            Properties.Settings.Default.Password + ";User ID=" + Properties.Settings.Default.UserID + ";Data Source=" +  
  24.            Properties.Settings.Default.ServerName;  
  25.   
  26.        // Save the property settings  
  27.        Properties.Settings.Default.Save();  
  28.    } catch (Exception ex) {  
  29.        MessageBox.Show(ex.Message, "Error saving connection informaiton");  
  30.    }  
  31.    //Test Connection  
  32.    if (Properties.Settings.Default.ConnString != string.Empty) {  
  33.        using(OleDbConnection conn = new OleDbConnection(Properties.Settings.Default.ConnString)) {  
  34.            try {  
  35.                // test the connection with an open attempt  
  36.                conn.Open();  
  37.                MessageBox.Show("Connection attempt successful.", "Connection Test");  
  38.            } catch (Exception ex) {  
  39.                // inform the user if the connection fails  
  40.                MessageBox.Show(ex.Message, "Connection Error");  
  41.            }  
  42.        }  
  43.    }  
  44. } 
The next method is merely used to close the form if the user decides to cancel the operation.
  1. /// <summary>  
  2. /// Close the form  
  3. /// </summary>  
  4. /// <param name="sender"></param>  
  5. /// <param name="e"></param>  
  6. private void btnOracleCancel_Click(object sender, EventArgs e) {  
  7.    this.Dispose();  
  8. } 
The next bit of code handles the check changed event for the integrated security check box control found on the SQL Server tab.  If the control is checked, the user name and password are not used and the connection string will be formatted to use integrated security.
 
The next event handler closes the form if the user decides to cancel the operation.
  1. /// <summary>  
  2. /// Close the form  
  3. /// </summary>  
  4. /// <param name="sender"></param>  
  5. /// <param name="e"></param>  
  6. private void btnSQLserverCancel_Click(object sender, EventArgs e) {  
  7.    this.Dispose();  
  8. } 
The next section of code is used to test the SQL Server connection string; it functions much the same as does the Oracle connection test with the only exception being that it formats the connection string differently based upon the user's selection of the Use Integrated Security check box control.
  1. /// <summary>  
  2. /// Test the SQL Server connection string  
  3. /// based upon the user supplied settings  
  4. /// </summary>  
  5. /// <param name="sender"></param>  
  6. /// <param name="e"></param>  
  7. private void btnSqlServerTest_Click(object sender, EventArgs e) {  
  8.    try {  
  9.        // Future use; if a current data model and database  
  10.        // type need to be identified and saved with the connect  
  11.        // string to identify its purpose  
  12.        Properties.Settings.Default.CurrentDataModel = "MySqlServer";  
  13.        Properties.Settings.Default.CurrentDatabaseType = "SqlServer";  
  14.   
  15.        // Set the properties for the connection string  
  16.        Properties.Settings.Default.ProviderString = txtSqlServerProvider.Text;  
  17.        Properties.Settings.Default.Password = txtSqlServerPassword.Text;  
  18.        Properties.Settings.Default.UserID = txtSqlServerUserID.Text;  
  19.        Properties.Settings.Default.ServerName = txtSqlServerDBName.Text;  
  20.        Properties.Settings.Default.InitialCatalog = txtSqlServerInitialCat.Text;  
  21.   
  22.        // configure the connection string based upon the use  
  23.        // of integrated security  
  24.        if (cbxIntegratedSecurity.Checked == true) {  
  25.            Properties.Settings.Default.ConnString = "Provider=" + Properties.Settings.Default.ProviderString + ";DataSource = " + Properties.Settings.Default.ServerName + ";Initial Catalog = " + Properties.Settings.Default.InitialCatalog + ";Integrated Security=SSPI;";  
  26.        } else {  
  27.            Properties.Settings.Default.ConnString = "Provider=" + Properties.Settings.Default.ProviderString +  
  28.                ";Password=" + Properties.Settings.Default.Password + ";User ID=" + Properties.Settings.Default.UserID +  
  29.                ";Data Source=" + Properties.Settings.Default.ServerName + ";Initial Catalog=" +  
  30.                Properties.Settings.Default.InitialCatalog;  
  31.        }  
  32.        // Save the property settings  
  33.        Properties.Settings.Default.Save();  
  34.    } catch (Exception ex) {  
  35.        // inform the user if the connection was not saved  
  36.        MessageBox.Show(ex.Message, "Error saving connection information");  
  37.    }  
  38.   
  39.    //Test Connection  
  40.    if (Properties.Settings.Default.ConnString != string.Empty) {  
  41.        using(OleDbConnection conn = new OleDbConnectionProperties.Settings.Default.ConnString) {  
  42.            try {  
  43.                // test the connection with an open attempt  
  44.                conn.Open();  
  45.                MessageBox.Show("Connection Attempt Successful.", "Connection Test");  
  46.            } catch (Exception ex) {  
  47.                // inform the user if the connection test failed  
  48.                MessageBox.Show(ex.Message, "Connection Test");  
  49.            }  
  50.        }  
  51.    }  
  52. } 
The following event handler sets the connection string and tests the SQL Server connection as defined by the user's entries into the form.
  1. private void btnSqlServerOK_Click(object sender, EventArgs e) {  
  2.     try {  
  3.         // Future use; if a current data model and database  
  4.         // type need to be identified and saved with the connect  
  5.         // string to identify its purpose  
  6.         Properties.Settings.Default.CurrentDataModel = "MySqlServer";  
  7.         Properties.Settings.Default.CurrentDatabaseType = "SqlServer";  
  8.   
  9.         // Set the properties for the connection  
  10.         Properties.Settings.Default.ProviderString = txtSqlServerProvider.Text;  
  11.         Properties.Settings.Default.Password = txtSqlServerPassword.Text;  
  12.         Properties.Settings.Default.UserID = txtSqlServerUserID.Text;  
  13.         Properties.Settings.Default.ServerName = txtSqlServerDBName.Text;  
  14.         Properties.Settings.Default.InitialCatalog = txtSqlServerInitialCat.Text;  
  15.   
  16.         // configure the connection string based upon  
  17.         // the use of integrated security  
  18.         if (cbxIntegratedSecurity.Checked == true) {  
  19.             Properties.Settings.Default.ConnString = "Provider=" + Properties.Settings.Default.ProviderString + ";Data Source=" + Properties.Settings.Default.ServerName + ";Initial Catalog=" + Properties.Settings.Default.InitialCatalog + "; Integrated Security=SSPI;";  
  20.         } else {  
  21.             Properties.Settings.Default.ConnString = "Provider=" + Properties.Settings.Default.ProviderString Password = "+ Properties.Settings.Default.Password + ";  
  22.             User ID = " + Properties.Settings.Default.UserID +";  
  23.             Data Source = " + Properties.Settings.Default.ServerName + ";  
  24.             Initial Catalog = " +Properties.Settings.Default.InitialCatalog;  
  25.         }  
  26.   
  27.         // Save the property settings  
  28.         Properties.Settings.Default.Save();  
  29.     } catch (Exception ex) {  
  30.         // inform the user if the connection information was not  
  31.         // saved  
  32.         MessageBox.Show(ex.Message, "Error saving connection information.");  
  33.     }  
  34.     //Test Connection  
  35.     if (Properties.Settings.Default.ConnString != string.Empty) {  
  36.         using(OleDbConnection conn = new OleDbConnection(Properties.Settings.Default.ConnString)) {  
  37.             try {  
  38.                 // test the connection with an open attempt  
  39.                 conn.Open();  
  40.                 this.Dispose();  
  41.             } catch (Exception ex) {  
  42.                 // inform the user if the connection was not saved  
  43.                 MessageBox.Show(ex.Message, "Connection Test");  
  44.             }  
  45.         }  
  46.     }  
  47. } 
The next event handler closes the form if the user decides to cancel the operation.
  1. /// <summary>  
  2. /// Close the form  
  3. /// </summary>  
  4. /// <param name="sender"></param>  
  5. /// <param name="e"></param>  
  6. private void btnAccessCancel_Click(object sender, EventArgs e) {  
  7.     this.Dispose();  
  8. } 
This event handler is used to open an open file dialog used to allow the user to navigate to and select an Access database.     
  1. /// <summary>  
  2. /// Browse for an access database  
  3. /// </summary>  
  4. /// <param name="sender"></param>  
  5. /// <param name="e"></param>  
  6. private void btnBrowse_Click(object sender, EventArgs e) {  
  7.     OpenFileDialog openFile = new OpenFileDialog();  
  8.     openFile.Title = "MS Access Database";  
  9.     openFile.DefaultExt = "mdb";  
  10.     openFile.Filter = "Access Database (*.mdb)|*mdb";  
  11.     openFile.ShowDialog();  
  12.     txtAccessDBname.Text = openFile.FileName;  
  13. } 
The next bit of code is used to test a connection to an Access database.
  1. /// <summary>  
  2. /// Test an MS Access database connection  
  3. /// </summary>  
  4. /// <param name="sender"></param>  
  5. /// <param name="e"></param>  
  6. private void btnAccessTest_Click(object sender, EventArgs e) {  
  7.    try {  
  8.        // Future use; if a current data model and database  
  9.        // type need to be identified and saved with the connect  
  10.        // string to identify its purpose  
  11.        Properties.Settings.Default.CurrentDataModel = "MyAccess";  
  12.        Properties.Settings.Default.CurrentDatabaseType = "Access";  
  13.   
  14.        // Set the access database connection properties  
  15.        Properties.Settings.Default.ProviderString = txtAccessProvider.Text;  
  16.        Properties.Settings.Default.Password = txtAccessPassword.Text;  
  17.        Properties.Settings.Default.UserID = txtAccessUserID.Text;  
  18.        Properties.Settings.Default.ServerName = txtAccessDBname.Text;  
  19.   
  20.        // Set the access database connection string  
  21.        Properties.Settings.Default.ConnString = "Provider=" + Properties.Settings.Default.ProviderString + ";Password=" +  
  22.            Properties.Settings.Default.Password + ";User ID=" + Properties.Settings.Default.UserID + ";Data Source=" +  
  23.            Properties.Settings.Default.ServerName;  
  24.   
  25.        // Save the properties  
  26.        Properties.Settings.Default.Save();  
  27.    } catch (Exception ex) {  
  28.        // inform the user if the connection could not be saved  
  29.        MessageBox.Show(ex.Message, "Error saving connection information.");  
  30.    }  
  31.    //Test Connection  
  32.    if (Properties.Settings.Default.ConnString != string.Empty) {  
  33.        using(OleDbConnection conn = new OleDbConnection(Properties.Settings.Default.ConnString)) {  
  34.            try {  
  35.                // test the connection with an open attempt  
  36.                conn.Open();  
  37.                MessageBox.Show("Access connection test successful", "Connection Test");  
  38.            } catch (Exception ex) {  
  39.                // inform the user if the connection failed  
  40.                MessageBox.Show(ex.Message, "Connection Error");  
  41.            }  
  42.        }  
  43.    }  
  44. } 
The next event handler is used to persist and test a connection string for an MS Access database.
  1.        /// <summary>  
  2.        /// Persist and test an Access database connection  
  3.        /// </summary>  
  4.        /// <param name="sender"></param>  
  5.        /// <param name="e"></param>  
  6.        private void btnAccessOK_Click(object sender, EventArgs e) {  
  7.         try {  
  8.             // Future use; if a current data model and database  
  9.             // type need to be identified and saved with the connect  
  10.             // string to identify its purpose  
  11.             Properties.Settings.Default.CurrentDataModel = "MyAccess";  
  12.             Properties.Settings.Default.CurrentDatabaseType = "Access";  
  13.   
  14.             // Set the access database connection properties  
  15.             Properties.Settings.Default.ProviderString = txtAccessProvider.Text;  
  16.             Properties.Settings.Default.Password = txtAccessPassword.Text;  
  17.             Properties.Settings.Default.UserID = txtAccessUserID.Text;  
  18.             Properties.Settings.Default.ServerName = txtAccessDBname.Text;  
  19.   
  20.             // Set the access database connection string  
  21.             Properties.Settings.Default.ConnString = "Provider=" + Properties.Settings.Default.ProviderString + ";Password=   
  22.             "+Properties.Settings.Default.Password+";  
  23.             User ID = " + Properties.Settings.Default.UserID + ";  
  24.             Data Source = " + Properties.Settings.Default.ServerName;  
  25.   
  26.             // Save the properties  
  27.             Properties.Settings.Default.Save();  
  28.         } catch (Exception ex) {  
  29.             // Inform the user if the connection was not saved  
  30.             MessageBox.Show(ex.Message, "Error saving connection information.");  
  31.         }  
  32.   
  33.         //Test Connection  
  34.   
  35.         if (Properties.Settings.Default.ConnString != string.Empty) {  
  36.             using(OleDbConnection conn = new OleDbConnection(Properties.Settings.Default.ConnString)) {  
  37.                 try {  
  38.                     // test the database connection string with an open  
  39.                     // attempt  
  40.                     conn.Open();  
  41.                     this.Dispose();  
  42.                 } catch (Exception ex) {  
  43.                     // inform the user if the connection failed  
  44.                     MessageBox.Show(ex.Message, "Connection Error");  
  45.                 }  
  46.             }  
  47.         }  
  48.        }  
  49.    }  
  50. } 
The Data Display Form
 
The main application is contained in the frmDataDisplay.cs 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.
 
6.gif
 
Figure 5:  The Main Form with an SQL Server Connection Active
 
The class starts out with the following code used to import the required libraries:
  1.  using System;  
  2. using System.Collections;  
  3. using System.Configuration;  
  4. using System.ComponentModel;  
  5. using System.Data;  
  6. using System.Data.OleDb;  
  7. using System.Drawing;  
  8. using System.Text;  
  9. using System.Windows.Forms; 
The next section of code declares the namespace, some local variables, and the default constructor. The array lists are used to contain the views and tables displayed on this form.
  1. namespace DBSpy {  
  2.    public partial class frmDataDisplay: Form {  
  3.            #region Declarations  
  4.            public string mSelectedTable;  
  5.            private bool mTableSelected;  
  6.            ArrayList arrViews;  
  7.            ArrayList arrTables;  
  8.            #endregion  
  9.   
  10.            /// <summary>  
  11.            /// default constructor  
  12.            /// </summary>  
  13.            public frmDataDisplay() {  
  14.                InitializeComponent();  
  15.            } 
The next section of code defines a method used to capture and store the table and view names for the database identified by the connection string. The schema is stored in a data table populated with the results of calling the connection's GetOleDbSchemaTable method. This method is called twice; once to obtain the list of views and once to obtain the list of tables; these items are then added to new instances of the array lists used to hold the view and table lists. The code is annotated and should be pretty straight forward.
  1. /// <summary>  
  2. /// Populate to arrays with list of all of the tables and views used  
  3. /// in the database defined by the current connection string  
  4. /// </summary>  
  5. public void StoreTableAndViewNames() {  
  6.     // temporary holder for the schema information for the current  
  7.     // database connection  
  8.     DataTable SchemaTable;  
  9.   
  10.     // used to hold a list of views and tables  
  11.     arrViews = new ArrayList();  
  12.     arrTables = new ArrayList();  
  13.   
  14.     // clean up the menu so the menu item does not  
  15.     // hang while this function executes  
  16.     this.Refresh();  
  17.   
  18.     // make sure we have a connection  
  19.     if (Properties.Settings.Default.ConnString != string.Empty) {  
  20.       // start up the connection using the current connection  
  21.       // string  
  22.       using(OleDbConnection conn = new OleDbConnection(Properties.Settings.Default.ConnString)) {  
  23.         try {  
  24.           // open the connection to the database  
  25.           conn.Open();  
  26.   
  27.           // Get the Tables  
  28.           SchemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new Object[] { null, null, null, TABLE }  
  29.   
  30.             // Store the table names in the class scoped array  
  31.             // list of table names  
  32.             for (int i = 0; i < SchemaTable.Rows.Count; i++) {  
  33.               arrTables.Add(SchemaTable.Rows[i].ItemArray[2].ToString());  
  34.             }  
  35.   
  36.             // Get the Views  
  37.             SchemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new Object[] { null, null, null, "VIEW" });  
  38.   
  39.             // Store the view names in the class scoped array  
  40.             // list of view names  
  41.             for (int i = 0; i < SchemaTable.Rows.Count; i++) {  
  42.               arrViews.Add(SchemaTable.Rows[i].ItemArray[2].ToString());  
  43.             }  
  44.           }  
  45.           catch (Exception ex) {  
  46.             // break and notify if the connection fails  
  47.             MessageBox.Show(ex.Message, "Connection Error");  
  48.           }  
  49.        }  
  50.     }  
  51.  } 
The next method is used to close the main form and terminate the application.
  1. /// <summary>  
  2. /// Close the form  
  3. /// </summary>  
  4. /// <param name="sender"></param>  
  5. /// <param name="e"></param>  
  6. private void CloseToolStripMenuItem_Click(object sender, EventArgs e) {  
  7.  // dispose of this form  
  8.  this.Dispose();  
  9. } 
The next method is used to create an instance of the connection form.  Once displayed, the user may define a new connection string.
  1. /// <summary>  
  2. /// Open a new connection to a database - present the connection  
  3. /// string builder  
  4. /// form so the user can define a connection  
  5. /// </summary>  
  6. /// <param name="sender"></param>  
  7. /// <param name="e"></param>  
  8. private void OpenANewConnectionToolStripMenuItem_Click(object sender, EventArgs e) {  
  9.  // open an instance the connect form so the user  
  10.  // can define a new connection  
  11.  frmConnect f = new frmConnect();  
  12.  f.Show();  
  13. } 
This method is used to display the value stored for the connection string in the application's settings
  1. /// <summary>  
  2. /// Display the current connection string to the user  
  3. /// </summary>  
  4. /// <param name="sender"></param>  
  5. /// <param name="e"></param>  
  6. private void ViewCurrentConnectionToolStripMenuItem_Click(object sender, EventArgs e) {  
  7.  // display the current connection string  
  8.  MessageBox.Show(Properties.Settings.Default.ConnString, "Current Connection");  
  9. } 
Based upon the current connection string, this method will recover the tables and views for the current database connection and will display those items in the form's table and view list view controls.
  1. /// <summary>  
  2. /// Get and display the current tables and views contained in the  
  3. /// database  
  4. /// pointed to by the connection string  
  5. /// </summary>  
  6. /// <param name="sender"></param>  
  7. /// <param name="e"></param>  
  8. private void LoadDataForCurrentConnectionToolStripMenuItem_Click(object sender, EventArgs e) {  
  9.  // get tables and views  
  10.  StoreTableAndViewNames();  
  11.   
  12.  // clear internal lists  
  13.  lstTables.Items.Clear();  
  14.  lstViews.Items.Clear();  
  15.   
  16.  // update the lists from the arrays holding the  
  17.  // tables and views  
  18.  lstTables.Items.AddRange(arrTables.ToArray());  
  19.  lstViews.Items.AddRange(arrViews.ToArray());  
  20. } 
The next method will display the list of columns displayed for the last selected table
  1. private void lstTables_SelectedIndexChanged(object sender, EventArgs e) {  
  2.   mTableSelected = true;  
  3.   string tblName;  
  4.   try {  
  5.     tblName = lstTables.SelectedItem.ToString();  
  6.   } catch {  
  7.     return;  
  8.   }  
  9.   // make sure we have a connection  
  10.   if (Properties.Settings.Default.ConnString != string.Empty) {  
  11.     // start up the connection using the current connection  
  12.     // string  
  13.     using(OleDbConnection conn = new OleDbConnection(Properties.Settings.Default.ConnString)) {  
  14.       try {  
  15.         // open the connection to the database  
  16.         conn.Open();  
  17.         lstFields.Items.Clear();  
  18.   
  19.         DataTable dtField = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, blName });  
  20.         foreach(DataRow dr in dtField.Rows) {  
  21.           lstFields.Items.Add(dr["COLUMN_NAME"].ToString());  
  22.         }  
  23.       } catch (Exception ex) {  
  24.         MessageBox.Show(ex.Message, "Connection Error");  
  25.       }  
  26.     }  
  27.   } else {  
  28.     MessageBox.Show("There is no connection string current defined.", "Connection String");  
  29.   }  
  30. } 
The next method will display the list of columns displayed for the last selected view.
  1. private void lstViews_SelectedIndexChanged(object sender, EventArgs e) {  
  2.  mTableSelected = false;  
  3.  string tblName;  
  4.  try {  
  5.    tblName = lstViews.SelectedItem.ToString();  
  6.  } catch {  
  7.    return;  
  8.  }  
  9.  // make sure we have a connection  
  10.  if (Properties.Settings.Default.ConnString != string.Empty) {  
  11.    // start up the connection using the current connection                 
  12.    // string  
  13.    using(OleDbConnection conn = new OleDbConnection(Properties.Settings.Default.ConnString)) {  
  14.      try {  
  15.        // open the connection to the database  
  16.        conn.Open();  
  17.        lstFields.Items.Clear();  
  18.   
  19.        // get the schema table  
  20.        DataTable dtField = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, tblName });  
  21.   
  22.        // read the column names into the fields list  
  23.        foreach(DataRow dr in dtField.Rows) {  
  24.          lstFields.Items.Add(dr["COLUMN_NAME"].ToString());  
  25.        }  
  26.      } catch (Exception ex) {  
  27.        MessageBox.Show(ex.Message, "Connection Error");  
  28.      }  
  29.    }  
  30.  } else {  
  31.    MessageBox.Show("There is no connection string current defined.", "Connection String");  
  32.  }  
  33. } 
This method will display the field information associated with the last selected column for the last selected view or table.
  1.        /// <summary>  
  2.       /// Collect and display the field information for a selected column  
  3.       /// name  
  4.       /// </summary>  
  5.       /// <param name="sender"></param>  
  6.       /// <param name="e"></param>  
  7.       private void GetFieldInformationToolStripMenuItem_Click(object sender, EventArgs e)  
  8.       {  
  9.           try  
  10.           {  
  11.               using (OleDbConnection conn = new OleDbConnection(Properties.Settings.Default.ConnString))  
  12.               {  
  13.                   string sSql = string.Empty;  
  14.    
  15.                   if (mTableSelected == true)  
  16.                   {  
  17.                       sSql = "SELECT [" + lstFields.SelectedItem.ToString().Trim() + "] FROM ["+ lstTables.SelectedItem.ToString  
  18.                       ().Trim()+ "]";  
  19.                   }  
  20.                   else  
  21.                   {  
  22.                       sSql = "SELECT [" + lstFields.SelectedItem.ToString().Trim() + "] FROM [" + lstViews.SelectedItem.ToString.Trim  
  23.                       () + "]";  
  24.                   }  
  25.                     
  26.                   OleDbCommand cmd = new OleDbCommand(sSql, conn);  
  27.                   conn.Open();  
  28.                     
  29.                   OleDbDataReader rdr = cmd.ExecuteReader(CommandBehavior.KeyInfo);  
  30.                   DataTable schemaTable = rdr.GetSchemaTable();  
  31.                   StringBuilder sb = new StringBuilder();  
  32.                     
  33.                   foreach (DataRow myField in schemaTable.Rows)  
  34.                   {  
  35.                       foreach (DataColumn myProperty in schemaTable.Columns)  
  36.                       {  
  37.                           sb.Append(myProperty.ColumnName + " = " + myField[myProperty].ToString()+Environment.NewLine);  
  38.                       }  
  39.    
  40.                       // report  
  41.                       MessageBox.Show(sb.ToString(), "Field Information");  
  42.                       
  43.                       // burn the reader  
  44.                       rdr.Close();  
  45.    
  46.                       // exit  
  47.                       return;  
  48.                   }  
  49.               }  
  50.           }  
  51.           catch  
  52.           {  
  53.               MessageBox.Show("Unable to attach to this table with current user; check database security permissions.",   
  54.               "Field information");  
  55.           }   
  56.       }   
  57.   }  
  58. } 

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.