How to: Retrieve database schema information using ADO.NET

Sometimes we need to know what is the internal structure of a database in addition to its data. This might turn very useful when the database structure is dynamic. This information is called Database Metadata or Database Schema.

Read database schema with OleDbConnection.GetOleDbSchemaTable method

The OleDbConnection class provides a special method for reading the database schema. It is called GetOleDbSchemaTable and returns a DataTable that is populated with the database schema information.

Here is an example of getting the database schema with this method:

C# .NET

DataTable tblDbSchema = oleDb.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, null);

This method accepts two parameters:

• The first one is an enumeration value that specifies what kind of information to be contained by the database schema output, such as tables, columns, procedures, indexes and keys.
• The second parameter is a restriction array. You can use this array to filter the schema table’s content. First array item will be used against the first column in the schema table; the second array item will be used against the second column in the schema table, and so on.

Here is an example of getting the column schema from the "Customer" table using an Access database. Because each value in the Object array corresponds to a DataColumn in the resulting DataTable, the Restrictions array should have the following structure:

{TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE}

C# .NET

string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=DatabaseFile.mdb";

OleDbConnection oleDb = new OleDbConnection(connectionString);

oleDb.Open();

 

object[] arrRestrict;

arrRestrict = new object[] { null, null, "Customers", null };

 

DataTable tblDbSchema = oleDb.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, arrRestrict);

 

oleDb.Close();

Read database schema with the SqlDataReader.GetSchemaTable method

The SqlDataReader class provide a special method for this called GetSchemaTable.

C# .NET

string strQuery = "SELECT * FROM Employees";

string strConn = "user id=sa;password=foo;initial catalog=northwind;data source=localhost;";

SqlConnection conn = new SqlConnection(strConn);

SqlCommand cmd = new SqlCommand(strQuery, conn);

conn.Open();

SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

 

// Get the schema table.

DataTable tblSchema = rdr.GetSchemaTable();

conn.Close();

rdr.Close();

Each row in the returned table represent each data field in the database table. And each column has predefined name corresponding to different attribute of the fields. For a complete list of these column names and their usage, please read the documentation.

Read SQL Server's database schema with a query

Another way to get the schema for a SQL Server database is to execute a SQL query like in the following example:

C# .NET

string strConn = "user id=sa;password=foo;initial catalog=northwind;data source=localhost;";

SqlConnection conn = new SqlConnection(strConn);

SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Information_Schema.Tables where Table_Type = 'BASE TABLE'", conn);

DataTable dt = new DataTable();

da.Fill(dt);