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.
Figure 1: The demonstration application running
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.
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.
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:
using System;
using System.Collections;
using System.Configuration;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Text;
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.
namespace DBSpy
{
public partial class frmConnect : Form
{
/// <summary>
/// Default constructor
/// </summary>
public frmConnect()
{
InitializeComponent();
}
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.
/// <summary>
/// Store the Oracle settings and test the connection
/// string
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnOracleOK_Click(object sender, EventArgs e)
{
// Future use; if a current data model and database
// type need to be identified and saved with the connect
// string to identify its purpose
Properties.Settings.Default.CurrentDataModel = "MyOracle";
Properties.Settings.Default.CurrentDatabaseType = "Oracle";
// Set the actual connection string properties into
// the application settings
Properties.Settings.Default.ProviderString = txtOracleProvider.Text;
Properties.Settings.Default.Password = txtOraclePassword.Text;
Properties.Settings.Default.UserID = txtOracleUserID.Text;
Properties.Settings.Default.ServerName = txtOracleDBname.Text;
// Set the connection string
Properties.Settings.Default.ConnString = "Provider=" +Properties.Settings.Default.ProviderString + ";Password=" +
Settings.Default.Password + ";User ID="+Properties.Settings.Default.UserID + ";Data Source=" + Properties.Settings
.Default.ServerName;
// Save the property settings
Properties.Settings.Default.Save();
//Test Connection
if (Properties.Settings.Default.ConnString != string.Empty)
{
using (OleDbConnection conn = new OleDbConnection(Properties.Settings.Default.ConnString))
{
try
{
// test with an open attempt
conn.Open();
this.Dispose();
}
catch (Exception ex)
{
// if the connection fails, inform the user
// so they can fix the properties
MessageBox.Show(ex.Message, "Connection Error");
}
}
}
}
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.
/// <summary>
/// Test the Oracle Connection String
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnOracleTest_Click(object sender, EventArgs e)
{
try
{
// Future use; if a current data model and database
// type need to be identified and saved with the connect
// string to identify its purpose
Properties.Settings.Default.CurrentDataModel = "MyOracle";
Properties.Settings.Default.CurrentDatabaseType = "Oracle";
// Set the actual connection string properties into
// the application settings
Properties.Settings.Default.ProviderString = txtOracleProvider.Text;
Properties.Settings.Default.Password = txtOraclePassword.Text;
Properties.Settings.Default.UserID = txtOracleUserID.Text;
Properties.Settings.Default.ServerName = txtOracleDBname.Text;
// Set the connection string
Properties.Settings.Default.ConnString = "Provider="+ Properties.Settings.Default.ProviderString + ";Password="
+Properties .Settings.Default.Password + ";User ID="+ Properties.Settings.Default.UserID + ";Data Source="
+ Properties.Settings.Default.ServerName;
// Save the property settings
Properties.Settings.Default.Save();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error saving connection informaiton");
}
//Test Connection
if (Properties.Settings.Default.ConnString != string.Empty)
{
using (OleDbConnection conn = new OleDbConnection(Properties.Settings.Default.ConnString))
{
try
{
// test the connection with an open attempt
conn.Open();
MessageBox.Show("Connection attempt successful.","Connection Test");
}
catch (Exception ex)
{
// inform the user if the connection fails
MessageBox.Show(ex.Message, "Connection Error");
}
}
}
}
The next method is merely used to close the form if the user decides to cancel the operation.
/// <summary>
/// Close the form
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnOracleCancel_Click(object sender, EventArgs e)
{
this.Dispose();
}
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.
/// <summary>
/// SQL Server
/// Configure for the use of integrated
/// security
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void cbxIntegratedSecurity_CheckedChanged(object sender, EventArgs e)
{
// if the user has checked the SQL Server connection
// option to use integrated security, configure the
//user ID and password controls accordingly
if (cbxIntegratedSecurity.Checked == true)
{
txtSqlServerUserID.Text = string.Empty;
txtSqlServerPassword.Text = string.Empty;
txtSqlServerUserID.Enabled = false;
txtSqlServerPassword.Enabled = false;
}
else
{
txtSqlServerUserID.Enabled = true;
txtSqlServerPassword.Enabled = true;
}
}
The next event handler closes the form if the user decides to cancel the operation.
/// <summary>
/// Close the form
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnSQLserverCancel_Click(object sender, EventArgs e)
{
this.Dispose();
}
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.
/// <summary>
/// Test the SQL Server connection string
/// based upon the user supplied settings
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnSqlServerTest_Click(object sender, EventArgs e)
{
try
{
// Future use; if a current data model and database
// type need to be identified and saved with the connect
// string to identify its purpose
Properties.Settings.Default.CurrentDataModel = "MySqlServer";
Properties.Settings.Default.CurrentDatabaseType = "SqlServer";
// Set the properties for the connection string
Properties.Settings.Default.ProviderString = txtSqlServerProvider.Text;
Properties.Settings.Default.Password = txtSqlServerPassword.Text;
Properties.Settings.Default.UserID = txtSqlServerUserID.Text;
Properties.Settings.Default.ServerName = txtSqlServerDBName.Text;
Properties.Settings.Default.InitialCatalog = txtSqlServerInitialCat.Text;
// configure the connection string based upon the use
// of integrated security
if (cbxIntegratedSecurity.Checked == true)
{
Properties.Settings.Default.ConnString = "Provider=" + Properties.Settings.Default.ProviderString + ";Data
Source=" + Properties.Settings.Default.ServerName + ";Initial Catalog=" +
Properties.Settings.Default.InitialCatalog + ";Integrated Security=SSPI;";
}
else
{
Properties.Settings.Default.ConnString = "Provider=" +Properties.Settings.Default.ProviderString +
";Password="+Properties.Settings.Default.Password+ ";User ID=" + Properties.Settings.Default.UserID
+ ";Data Source=" +Properties.Settings.Default.ServerName + ";Initial Catalog="
+ Properties.Settings.Default.InitialCatalog;
}
// Save the property settings
Properties.Settings.Default.Save();
}
catch (Exception ex)
{
// inform the user if the connection was not saved
MessageBox.Show(ex.Message, "Error saving connection information");
}
//Test Connection
if (Properties.Settings.Default.ConnString != string.Empty)
{
using (OleDbConnection conn = new OleDbConnectionProperties.Settings.Default.ConnString))
{
try
{
// test the connection with an open attempt
conn.Open();
MessageBox.Show("Connection Attempt Successful.", "Connection Test");
}
catch (Exception ex)
{
// inform the user if the connection test failed
MessageBox.Show(ex.Message, "Connection Test");
}
}
}
}
The following event handler sets the connection string and tests the SQL Server connection as defined by the user's entries into the form.
private void btnSqlServerOK_Click(object sender, EventArgs e)
{
try
{
// Future use; if a current data model and database
// type need to be identified and saved with the connect
// string to identify its purpose
Properties.Settings.Default.CurrentDataModel = "MySqlServer";
Properties.Settings.Default.CurrentDatabaseType = "SqlServer";
// Set the properties for the connection
Properties.Settings.Default.ProviderString = txtSqlServerProvider.Text;
Properties.Settings.Default.Password = txtSqlServerPassword.Text;
Properties.Settings.Default.UserID = txtSqlServerUserID.Text;
Properties.Settings.Default.ServerName = txtSqlServerDBName.Text;
Properties.Settings.Default.InitialCatalog = txtSqlServerInitialCat.Text;
// configure the connection string based upon
// the use of integrated security
if (cbxIntegratedSecurity.Checked == true)
{
Properties.Settings.Default.ConnString = "Provider=" +Properties.Settings.Default.ProviderString +";Data Source="
+ Properties. Settings. Default.ServerName +";Initial Catalog=" +Properties.Settings.Default.InitialCatalog +";
Integrated Security=SSPI;";
}
else
{
Properties.Settings.Default.ConnString = "Provider=" + Properties .Settings.Default.ProviderString Password=
"+ Properties.Settings.Default.Password + ";User ID=" + Properties.Settings.Default.UserID +";Data Source=" +
Properties.Settings.Default.ServerName + ";Initial Catalog=" +Properties.Settings.Default.InitialCatalog;
}
// Save the property settings
Properties.Settings.Default.Save();
}
catch (Exception ex)
{
// inform the user if the connection information was not
// saved
MessageBox.Show(ex.Message, "Error saving connection information.");
}
//Test Connection
if (Properties.Settings.Default.ConnString != string.Empty)
{
using (OleDbConnection conn = new OleDbConnection(Properties.Settings.Default.ConnString))
{
try
{
// test the connection with an open attempt
conn.Open();
this.Dispose();
}
catch (Exception ex)
{
// inform the user if the connection was not saved
MessageBox.Show(ex.Message, "Connection Test");
}
}
}
}
The next event handler closes the form if the user decides to cancel the operation.
/// <summary>
/// Close the form
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnAccessCancel_Click(object sender, EventArgs e)
{
this.Dispose();
}
This event handler is used to open an open file dialog used to allow the user to navigate to and select an Access database.
/// <summary>
/// Browse for an access database
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnBrowse_Click(object sender, EventArgs e)
{