Page 1 of 1

Obtaining Database Schema Information

#1 djjeavons  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 19
  • View blog
  • Posts: 89
  • Joined: 09-January 09

Posted 11 December 2014 - 03:34 AM

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


Attached Image

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.

public class Table
{
	public string TableName { get; set; }
	public List<Column> Columns { get; set; }
	public List<Index> Indexes { get; set; }

	public Table(string tableName, List<Column> columns, List<Index> indexes)
	{
		TableName = tableName;
		Columns = columns;
		Indexes = indexes;
	}

	public override string ToString()
	{
		return TableName;
	}
}



public class View
{
	public string ViewName { get; set; }
	public string ViewDefinition { get; set; }
	public List<Column> Columns { get; set; }

	public View(string viewName, string viewDefinition, List<Column> columns)
	{
		ViewName = viewName;
		ViewDefinition = viewDefinition;
		Columns = columns;
	}

	public override string ToString()
	{
		return ViewName;
	}
}



public class Column
{
	public string ColumnName { get; set; }

	public Column(string columnName)
	{
		ColumnName = columnName;
	}

	public override string ToString()
	{
		return ColumnName;
	}
}



public class Index
{
	public string IndexName { get; set; }
	public bool PrimaryKey { get; set; }
	public bool Unique { get; set; }

	public Index(string indexName, bool primaryKey, bool unique)
	{
		IndexName = indexName;
		PrimaryKey = primaryKey;
		Unique = unique;
	}

	public override string ToString()
	{
		return String.Format("{0} (Primary Key={1}), (Unique={2})", IndexName, PrimaryKey.ToString(), Unique.ToString());
	}
}



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

private List<Table> Tables = null;
private List<View> Views = null;



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.

private void openDatabaseButton_Click(object sender, EventArgs e)
{
	OpenFileDialog fileDialog = new OpenFileDialog();

	fileDialog.Filter = "Microsoft Access (*.accdb)|*.accdb";
	fileDialog.Title = "Select Microsoft Access Database";
	if (fileDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
		databasePathTextBox.Text = fileDialog.FileName;
}

private void exitButton_Click(object sender, EventArgs e)
{
	Application.Exit();
}

private void databasePathTextBox_TextChanged(object sender, EventArgs e)
{
	retrieveSchemaButton.Enabled = databasePathTextBox.TextLength > 0;
}



GetSchema code

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

using System.Data;
using System.Data.OleDb;



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:

private void retrieveSchemaButton_Click(object sender, EventArgs e)
{
	string connectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Persist Security Info=False;", databasePathTextBox.Text);

	//Open a connection to the database
	OleDbConnection connection = null;

	try
	{
		connection = new OleDbConnection(connectionString);
		connection.Open();

		GetTables(connection);
		GetViews(connection);

		//Add tables and associated data to the schemaTreeView
		UpdateSchemaTreeView();

	}
	catch (InvalidOperationException ex)
	{
		MessageBox.Show(ex.Message);
	}
	catch (OleDbException ex)
	{
		MessageBox.Show(ex.Message);
	}
	catch (Exception ex)
	{
		MessageBox.Show(ex.Message);
	}
	finally
	{
		if (connection != null) connection.Dispose();
	}
}



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.

private void GetTables(OleDbConnection connection)
{
	//Get all tables associated to this database along with the columns and indexes for each table. 
	DataTable schema = connection.GetSchema("Tables", new String[] { null, null, null, "TABLE" });

	if (schema != null && schema.Rows.Count > 0)
	{
		Tables = new List<Table>();
		foreach (DataRow schemaRow in schema.Rows)
		{
			Tables.Add(new Table(schemaRow["TABLE_NAME"].ToString().Trim(),
				GetColumns(connection, schemaRow["TABLE_NAME"].ToString().Trim()),
				GetIndexes(connection, schemaRow["TABLE_NAME"].ToString().Trim())));
		}
	}
}

private List<Column> GetColumns(OleDbConnection connection, string tableName)
{
	List<Column> columns = null;

	DataTable columnsTable = connection.GetSchema("Columns", new String[] { null, null, tableName, null });

	if (columnsTable != null && columnsTable.Rows.Count > 0)
		columns = new List<Column>();
	{
		foreach (DataRow columnRow in columnsTable.Rows)
		{
			columns.Add(new Column(columnRow["COLUMN_NAME"].ToString().Trim()));
		}
	}

	return columns;
}

private List<Index> GetIndexes(OleDbConnection connection, string tableName)
{
	List<Index> indexes = null;

	DataTable indexesTable = connection.GetSchema("Indexes", new String[] { null, null, null, null, tableName });

	if (indexesTable != null && indexesTable.Rows.Count > 0)
		indexes = new List<Index>();
	{
		foreach (DataRow indexRow in indexesTable.Rows)
		{
			indexes.Add(new Index(indexRow["INDEX_NAME"].ToString().Trim(),
				Convert.ToBoolean(indexRow["PRIMARY_KEY"].ToString()),
				Convert.ToBoolean(indexRow["UNIQUE"].ToString())));
		}
	}

	return indexes;
}



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

private void GetViews(OleDbConnection connection)
{
	//Get all views associated to this database. 
	DataTable viewsTable = connection.GetSchema("Views");

	if (viewsTable != null && viewsTable.Rows.Count > 0)
	{
		Views = new List<View>();

		foreach (DataRow viewRow in viewsTable.Rows)
		{
			Views.Add(new View(viewRow["TABLE_NAME"].ToString().Trim(),
				viewRow["VIEW_DEFINITION"].ToString().Trim(),
				GetColumns(connection, viewRow["TABLE_NAME"].ToString().Trim())));
		}
	}
}



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.

private void UpdateSchemaTreeView()
{
	schemaTreeView.Nodes.Clear();

	TreeNode rootNode = new TreeNode("Schema");

	//Add tables, columns and indexes
	TreeNode tableNode = new TreeNode("Tables");

	foreach (Table table in Tables)
	{
		//Table
		TreeNode currentTableNode = tableNode.Nodes.Add(table.TableName);

		//Current tables columns
		TreeNode columnsNode = new TreeNode("Columns");

		foreach (Column column in table.Columns)
		{
			columnsNode.Nodes.Add(column.ColumnName);
		}

		currentTableNode.Nodes.Add(columnsNode);

		if (table.Indexes != null)
		{
			//Current tables indexes
			TreeNode indexesNode = new TreeNode("Indexes");

			foreach (Index index in table.Indexes)
			{
				indexesNode.Nodes.Add(index.ToString());
			}

			currentTableNode.Nodes.Add(indexesNode);
		}
	}

	//Add views
	//NOTE for the Views we are going to add the View object to the tag property of the node so that 
	//we can display the view definition when the view node is selected.
	TreeNode viewNode = new TreeNode("Views");

	foreach (View view in Views)
	{
		TreeNode currentViewNode = new TreeNode(view.ViewName);
		currentViewNode.Tag = view;
		viewNode.Nodes.Add(currentViewNode);
	}

	rootNode.Nodes.Add(tableNode);
	rootNode.Nodes.Add(viewNode);
	schemaTreeView.Nodes.Add(rootNode);
}

private void schemaTreeView_AfterSelect(object sender, TreeViewEventArgs e)
{
	//If there is anything in the tag property then we will check if it is a View 
	//object and if it is display the definition of the view.
	if (e.Node.Tag != null)
	{
		View view = (View)e.Node.Tag;
		viewDefinitionTextBox.Text = view.ViewDefinition;
	}
	else
		viewDefinitionTextBox.ResetText();
}



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



Is This A Good Question/Topic? 2
  • +

Page 1 of 1