Retrieving Schema Information Using ADO.NET and C#

At times, in addition to querying and updating data in a database, you also need to retrieve information about the database as well as its contents. This information is called Database Metadata. In this article, I will describe the ways to retrieve the database schema information using System.Data.SqlClient class and the System.Data.OleDb class.

The database schema also known as metadata is how the database defines itself. By using the database schema information, you can see the database structure and all the data in it. 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. We will explore both ways.

The OleDbConnection object exposes a GetOleDbSchemaTable method that you can use to retrieve schema information for your databases, such as a list of tables or columns. GetOleDbSchemaTable returns a DataTable that is populated with the schema information. There is no direct equivalent to this feature in the SQL Client .NET Data Provider. The SQL Server .NET Data Provider exposes backend schema information through stored procedures and informational views. You simply query SQL Server's Information_Schema and restrict the results to Table_Type = 'BASE TABLE' or Table_Type = 'VIEW'. However do remember that for information schema views, the metadata returned is limited to that which the user has permission to view.

You can execute the following piece of code to retrieve information regarding the various tables on your SQL Server database:

When we retrieve schema information with OleDb, we don't need a DataAdapter. We're going to let the connection object get the information for us. The GetOleDbSchemaTable method returns schema information from a data source as indicated by a Guid.( A GUID represents a globally unique identifier (GUID). This identifier has a low probability of being duplicated as it is composed of a 128-bit integer (16 bytes) that can be used across all computers and networks wherever a unique identifier is required.) You supply a value from the OleDbSchemaGuid enumeration to specify the type of schema information you want, such as tables, columns, and procedures. In addition to taking the Guid schema argument, you can further restrict the results of the GetOleDbSchemaTable( ) through the second argument. This argument is an object array specifying column restrictions. Using the restrictions parameter, you can retrieve information for just a few selected columns in a particular table rather than retrieving information for all columns in your database. The syntax for the method is :

Each value in the Object array corresponds to a DataColumn in the resulting DataTable. The Restrictions array for the member should have the following structure:

{TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE}

When you pass values in the restriction array, include the 'null' keyword for array elements that do not contain values.

For E.g.: You can use the following code to retrieve just the columns from the Customers table: