GetSchema - DbConnection.GetSchema in ADO.NET 2.0 - Retrieve Databases Tables Columns Views etc. from Database Connection

by David Hayden ( Florida .NET Developer )

I have mentioned the new GetSchema in ADO.NET 2.0 before in reference to the Code Generator and Database Explorer I have been building in my spare time.

The DbConnection Class in System.Data.Common has a new GetSchema method in ADO.NET 2.0 that can help you get metadata ( Databases, Tables, Columns, Views, Stored Procedures, Indexes, Foreign Keys, etc. ) from the data source ( SQL Server, Oracle, etc.). The possibililities of the GetSchema method are limitless if you are interested in building simple O/R Mappers and Code Generators.

Database metadata can be obtained in other (more powerful ) ways, of course, but in this article I just want to briefly talk about how to use the GetSchema method to get metadata from a SQL Server Database, since it is a new feature in ADO.NET 2.0.

DbConnection.GetSchema

GetSchema grabs metadata associated with various MetaDataCollections. If you call GetSchema() with no parameters on your DbConnection class you will get a list of the various MetaDataCollection CollectionNames for which you can gather information:

SqlConnection connection =
    new SqlConnection(connectionString);
connection.Open();
DataTable dt = connection.GetSchema();
connection.Close();

Using the information above, if you want to get a list of databases for a given data source, you can call GetSchema with the parameter Databases. The list of databases for the datasource is returned as a DataTable.

SqlConnection connection =
      new SqlConnection(connectionString);
connection.Open();
DataTable dt = connection.GetSchema("Databases");
connection.Close();

GetSchema Restrictions

To filter the results returned by GetSchema, you can specify certain restrictions on each MetaDataCollection. You can see all the restrictions for each collection using the following code:

SqlConnection connection =
        new SqlConnection(connectionString);
connection.Open();
DataTable dt = connection.GetSchema("Restrictions");
connection.Close();

Here is a snapshot of the restrictions on Columns from the list:

Let's grab the Columns from the Orders Table in the Northwind Database with the following code:

SqlConnection connection =
        new SqlConnection(connectionString);
string[] restrictions = new string[4]
        { "Northwind", null, "Orders", null };
connection.Open();
DataTable dt = connection.GetSchema("Columns",
                                 restrictions);
connection.Close();

Conclusion

GetSchema is another new feature in ADO.NET 2.0. You can use it in your O/R Mapper and Code Generation Tools to help get information from SQL Server or Oracle.

Source: David Hayden ( Florida .NET Developer )