Get database schema using GetSchema

This tutorial demonstrates the use of the GetSchema method to find out the structure of a database. This includes tables within the database, columns and indexes of a table and views.

The GetSchema method was introduced as of .NET Framework 2.0, and is a method available as part of the System.Data.Common.DBConnection class which is inherited by the OleDbConnection and the SqlConnection classes meaning that it is very easy to obtain schema information for both SQL Server databases and OleDb databases such as Access and even Excel.

In this tutorial I will be focusing on OleDb and Access databases but I encourage you to play around with the code and see how easy it is to change to work with an SQL Server. Also, I am only focusing on a small amount of data that is available so again, please explore the schema information that is returned at various points in the code to see the wealth of information that can be obtained.

Creating the project

To start, create a new C# Windows Forms Application and when the project has loaded rename Form1 to mainForm.cs.

To create the UI, add the following controls to the form and set their properties according to the list below. There is a screen shot of the UI to assist in laying out the controls:

  • Label. Name: databasePathLabel, Text: Database
  • TextBox. Name: databasePathTextBox, Anchor: Top, Left, Right
  • Button. Name: openDatabaseButton, Anchor: Top, Right, Text: “…”
  • Label. Name: schemaLabel, Text: Schema
  • TreeView. Name: schemaTreeView, Anchor: Top, Bottom, Left, Right
  • Label. Name: viewDefinitionLabel, Text: View Definition, Anchor: Bottom, Left
  • TextBox. Name: viewDefinitionTextBox, Anchor: Bottom, Left, Right, Multiline: True, ScrollBars: Both
  • Button. Name: retrieveSchemaButton, Anchor: Bottom, Left, Text: Retrieve Schema, Enabled: False
  • Button. Name: exitButton, Anchor, Bottom, Right, Text: Exit

UI Layout

Classes

The following classes are used to store schema details. These are very basic as to capture all information returned would detract from the focus of this tutorial. However, you are encouraged to expand on these to suit your requirements.

Add the following classes to your mainForm code file, outside of the “public partial class mainForm : Form” but within the namespace.

Note that the Table class has properties for both the Column and Index classes so that we essentially have a one to many relationship, one table > many columns and many indexes.

We need two form level variables to reference both the Table and the View classes so add the following two declarations to your code file below “public partial class mainForm : Form”

 Basic UI code

Now add the following code to your form for the basic functions such as opening a dialogue to select a database, exiting the application etc.

 GetSchema code

Before getting into the code below, ensure you have the following two using statements:

In order to get schema information from the database we have to have an open connection to a database. The following code is the click event of the retrieveSchemaButton:

This simply connects to the database chosen and then calls two sub routines (GetTables and GetViews). These two sub routines which are shown below will populate our classes before then calling a final routine to update our TreeView with the output.

The GetTables routine below calls the GetSchema method of the OleDbConnection to request table information. When calling GetSchema, there are three overloads, the default returns high level schema information. The second returns schema information related to a specific type such as Tables or Views and the third which we are using in this instance provides a set of restriction values. In the GetTables routine below you will note for the first argument we are asking for Tables and in the second argument we provide a string array containing the restrictions which in this instance is set to TABLE. (More on restrictions later in further reading). If we were to omit the restrictions then we would have all tables returned regardless of their type. For example, we would retrieve information on Tables, Linked Tables, System Tables etc.

Note also that this code makes additional calls to GetColumns and GetIndexes for each table that is returned. Within those routines you will see a different set of restrictions that are passed, in this case the name of the table for which we want schema information.

The next piece of code is responsible for returning Views (queries) from the database:

In this routine we don’t supply any restrictions to the GetSchema call and simply ask it to return all Views. You will also note that we obtain the VIEW_DEFINITION, this is essentially the SQL statement for the view.

Displaying the output

The final piece of code is responsible for updating the TreeView to show all tables with their columns and indexes and all views.

Note that when we are adding the View information to the TreeView we also add the view object to the Tag property of the node. This is then used in the AfterSelect event of the TreeView to obtain the View Definition in order to display it in the Text Box below.

Conclusion

As you can see, it is relatively straightforward to retrieve information about an Access database. As mentioned previously, I would recommend that you set breakpoints within the code for each of the [inline]GetSchema[/inline] calls and use the inspector to view the contents of the DataTable returned to see what type of information can be retrieved.

Further Reading

The following MSDN pages should be consulted for additional information:

One thought on “Get database schema using GetSchema

Leave a Reply

Your email address will not be published. Required fields are marked *