It is possible to quickly view all the tables in your database
from a Web page by using a few simple lines of code. By using the database schema
information you will readily see the database structure and all the data in
it from your ASP.NET page.
Definition and Namespaces
The database schema, sometimes called metadata, is how the database defines itself. To oversimplify things, when an action is taken against the database such as an update, the database checks the command against the schema information and makes sure there are no conflicts. You have probably received an error when you tried to pass a string to the database when it was expecting an integer. The database schema is responsible for this.
When working with databases in .NET, you either use the System.Data.SqlClient class or the System.Data.OleDb class. These two classes retrieve the table schema differently. First, we will look at the SqlClient namespace, then the OleDb namespace. The examples in the article will be in C#; however, you can download examples in both C# and VB.
The ASP Page
In the ASP portion of the page, we will have a DropDownList control (id="DBTableDropDown") to display the tables from the database, a DataGrid control (id="TableSchemaDataGrid") to display the column schema of the selected table, and another DataGrid (id="TableDataGrid") to display all the data from the chosen table. Do not forget to import the proper namespaces at the top of the page.
DB Connection String
The connection string to the database will be created with the connstring property. Edit as needed.
private string connstring
{
get //get you one!
{
return "server=000.000.000.000; uid=DataUser; pwd=DataPass; database=DatabaseName;";
}
set //set
{
connstring = ConfigurationSettings.AppSettings["ConnectionString"];
}
}
|
The System.Data.SqlClient Namespace
Retrieving the Table Names
We will now enter the world of strange SQL statements. When using the SqlClient class, you will use SQL statements to retrieve the schema information. In the Page_Load event, we call the GetTables method that we have created for this example.
The GetTables method is just like any other where you want to populate a DropDownList control from a database, but the SQL statement reads,
|
"SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_NAME"
|
This statement will return all the tables in the database that you are working with. If you want, you can select everything (*) instead of TABLE_NAME, populate a DataGrid, and you will be able to get the database name (TABLE_CATALOG), owner name (TABLE_SCHEMA) and whether it is a base table or a view (TABLE_TYPE).
Here is the code for the GetTables method:
private void GetTables()
{
SqlConnection myConnection = new SqlConnection(connstring);
string SQL = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES " +
"WHERE TABLE_TYPE = 'BASE TABLE' " +
"ORDER BY TABLE_NAME";
SqlDataAdapter schemaDA = new SqlDataAdapter(SQL, myConnection);
DataTable schemaTable = new DataTable();
schemaDA.Fill(schemaTable);
DBTableDropDown.DataSource=schemaTable;
DBTableDropDown.DataTextField = "TABLE_NAME";
DBTableDropDown.DataValueField = "TABLE_NAME";
DBTableDropDown.SelectedIndex = DBTableDropDown.SelectedIndex;
DBTableDropDown.DataBind();
DBTableDropDown.Items.Insert(0, new ListItem("Select a Table"));
DBTableDropDown.SelectedIndex = 0;
}
|
The Column Schema and the Table Data
The DropDownList shows the database tables. When the OnSelectedIndexChanged event is fired we will call two methods: GetDatabaseSchema and GetTableData.
public void DBTableDropDown_Changed(Object sender, EventArgs e)
{
//display column schema
TableSchemaDataGrid.DataSource = GetDatabaseSchema(DBTableDropDown.SelectedItem.Value);
TableSchemaDataGrid.DataBind();
//display the data
TableDataGrid.DataSource = GetTableData(DBTableDropDown.SelectedItem.Value);
TableDataGrid.DataBind();
}
|
The first method, GetDatabaseSchema, will display the columns metadata from the table we have selected using the following statement.
|
"SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '"+DBTable+"'"
|
This method returns a DataView to TableSchemaDataGrid.DataSource and we then bind it. We now get to view the column names, data types, max lengths, numeric precision, as well as the table names, database names, owner and other information.
private DataView GetDatabaseSchema(string DBTable)
{
//gets the database column schema.
SqlConnection myConnection = new SqlConnection(connstring);
string SQL = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS " +
"WHERE TABLE_NAME = '"+DBTable+"'";
SqlDataAdapter myCommand = new SqlDataAdapter(SQL, myConnection);
DataSet ds = new DataSet();
myCommand.Fill(ds, DBTable);
return ds.Tables[DBTable].DefaultView;
}
|
The second method we call when we select a table from the drop
down list is GetTableData. This just returns
all the data in the table as a collection. If you think the table contains excessive
amounts of data and might adversely affect performance, you will want to tweak
the SQL statement or the DataGrid so that it pages through the data.
private ICollection GetTableData(string DBTable)
{
//Gets all the data from the table
SqlConnection myConnection = new SqlConnection(connstring);
string SQL = "select * from "+DBTable;
SqlDataAdapter myCommand = new SqlDataAdapter(SQL, myConnection);
DataSet ds = new DataSet();
myCommand.Fill(ds, DBTable);
return ds.Tables[DBTable].DefaultView;
}
|
Why does this last method return a Collection and not a DataView? No reason, I just like how there are so many different ways to do things in .NET. Good coding practice would probably suggest that both of these methods return the same data type!
The System.Data.OleDb Namespace
Retrieving the Table Names the OleDb Way
If you are using the OleDb class, you gain access to the schema information in a different manner.
Instead of using SQL statements, we will call the GetOleDbSchemaTable
method of the OleDbConnection class. We create
the connection object , myConnection, and we
open it. Then we create a DataTable object, schemaTable,
passing two items to the GetOleDbSchemaTable
method. The first parameter is the schema interface we desire; in this case,
Tables. The second parameter is the restrictions we want to place on the information
being presented. I will explain the restrictions parameter when we display the
columns in the DataGrid.
Here is the GetTables method using OleDb
private void GetTables()
{
OleDbConnection myConnection = new OleDbConnection(connstring);
myConnection.Open();
DataTable schemaTable = myConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
new object[] {null,null,null,"TABLE"});
myConnection.Close();
DBTableDropDown.DataSource=schemaTable;
DBTableDropDown.DataTextField = "TABLE_NAME";
DBTableDropDown.DataValueField = "TABLE_NAME";
DBTableDropDown.SelectedIndex = DBTableDropDown.SelectedIndex;
DBTableDropDown.DataBind();
DBTableDropDown.Items.Insert(0, new ListItem("Select a Table"));
DBTableDropDown.SelectedIndex = 0;
}
|
Column Schema with OleDb
When we display the column metadata from the table, we will just need to make a few small changes to the code. Since we want to view the column information, we will pass OleDbSchemaGuid.Columns as the first parameter of the GetOleDbSchema method. The second parameter will pass the restrictions we want to place on the information being returned to us. In this case, we just want the table that we selected from the drop down list.
private DataView GetDatabaseSchema(string DBTable)
{
//gets the database column schema.
OleDbConnection myConnection = new OleDbConnection(connstring);
myConnection.Open();
DataTable schemaTable = myConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,
new object[] {null, null, DBTable});
myConnection.Close();
return schemaTable.DefaultView;
}
|
How the Restrictions Work
The restrictions follow the column order of the schema information. The order for the OleDbSchemaGuid.Columns array is TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME and so on. Thus, when we ask for the column schema, we will make the first two items null, and the third item will be the table name that we want to grab. If you had a column name called UserID and you only wanted this metadata, then you could enter a fourth item into the array with the value of "UserID" like so,
|
new object[] {null, null, DBTable,"UserID"});
|
If you want all the schema information, just leave the array blank.
Conclusion
In this article we have seen how to use database schema information to grab a quick view of the database tables from an ASP.NET page. I hope you found this article useful and will explore other ways to use database schemas to make your projects more robust.
For more information do a search on your favorite search engine for "INFORMATION_SCHEMA" or go to
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconobtainingschemainformationfromdatabase.asp