Introduction
Here is a nice little tip on how you can get list of tables in your database.
This technique is not just limited to enumeration of tables, it can be used to
get a lot of other information about your database. Bur for this article we will
limit the discussion to getting list of tables.
Remember from classic ADO Connection.OpenSchema method that was
used to get the tables. This functionality did not go away. It is present in
OleDb data provider's connection object
OleDbConnection. You can use GetOleDbSchemaTable
method to get DataTable containing rows corresponding to each table
in your database. The first parameter to this method is
OleDbSchemaGuid value. This value represents the type of schema
that you want to return. For getting list of tables, you will use
OleDbSchemaGuid.Tables value. The second parameter to
GetOleDbSchemaTable method is an array representing the
restrictions that will be applied when returning the values.
OleDbSchemaGuid.Tables value will return all the tables and
views in a database. The information returned for this Guid value has 4
restriction columns that you can use to limit the amount of data should be
returned from database. Following is the list of restriction columns.
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
TABLE_TYPE
If you want to get only the list of tables and not views, system tables etc.,
then supply the restriction for fourth column with value,
TABLE. Following is sample code that we have used in the
attached project.
private DataTable GetDatabaseTables_SQL()
{
string strConn = "Provider=SQLOLEDB;Data Source=localhost;
Initial Catalog=Northwind;User ID=sa;Password=;";
OleDbConnection conn = null;
try
{
conn = new OleDbConnection(strConn);
conn.Open();
DataTable schemaTable = conn.GetOleDbSchemaTable(
OleDbSchemaGuid.Tables,
new object[] {null, null, null, "TABLE"});
return schemaTable;
}
catch (OleDbException ex)
{
Trace.Write(ex.Message);
Response.Write(ex.Message);
}
finally
{
conn.Close();
}
return null;
}
We have tested this feature with SQL as well as Oracle database. For SQL
database we used the OleDb provider that ships with .NET framework. And for
Oracle, we used the OLE DB provider that ships with Oracle 9i database. For more
information on how to use Oracle9i OLE DB provider, refer to Use Oracle9i
in ASP.Net Application.
