5.4 DataReaders and Schema Information

Schema information is information about the structure of your data. It includes everything from column data types to table relations.

Schema information becomes extremely important when dealing with the ADO.NET DataSet, as you'll learn in the following chapters. However, even if you aren't using the DataSet, you may want to retrieve some sort of schema information from a data source. With ADO.NET, you have two choices: you can use the DataReader.GetSchemaTable( ) method to retrieve schema information about a specific query, or you can explicitly request a schema table from the data source.

5.4.1 Retrieving Schema Information for a Query

As long as a DataReader is open, you can invoke its GetSchemaTable( ) method to return a DataTable object with the schema information for the result set. This DataTable will contain one row for each column in the result set. Each row will contain a series of fields with column information, including the data type, column name, and so on.

Example 5-6 shows code to retrieve schema information for a simple query.

Example 5-6. Retrieving the schema information for a query

// GetSchema.cs - Retrieves a schema table for a query

using System;
using System.Data;
using System.Data.SqlClient;

public class GetSchema
{
    public static void Main() 
    {
        string connectionString = "Data Source=localhost;" +
                     "Initial Catalog=Northwind;Integrated Security=SSPI";
        string SQL = "SELECT * FROM CUSTOMERS";

        // Create ADO.NET objects.
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand(SQL, con);
        SqlDataReader r;
        DataTable schema;

        // Execute the query.
        try
        {
            con.Open();
            r = cmd.ExecuteReader();
            schema = r.GetSchemaTable();
        }
        finally
        {
            con.Close();
        }

        // Display the schema table.
        foreach (DataRow row in schema.Rows)
        {
            foreach (DataColumn col in schema.Columns)
            {
                Console.WriteLine(col.ColumnName + " = " + row[col]);
            }
            Console.WriteLine();
        }
    }
}

If you run this test, you'll find that it returns a significant amount of information. Here's the output for just a single column in the query (omitting columns that don't return any information):

ColumnName = CustomerID
ColumnOrdinal = 0
ColumnSize = 5
NumericPrecision = 255
NumericScale = 255
IsUnique = False
BaseColumnName = CustomerID
DataType = System.String
AllowDBNull = False
ProviderType = 10
IsIdentity = False
IsAutoIncrement = False
IsRowVersion = False
IsLong = False
IsReadOnly = False

Although you must retrieve the schema DataTable while the DataReader is open, you can store it in a variable and access it later, after the connection is closed. That's because the DataTable is a disconnected data container. For more information about the DataTable object, refer to Chapter 7.

5.4.2 Retrieving Schema Tables

The GetSchemaTable( ) method is ideal if you need schema information based on a query, but it won't allow you to retrieve anything else. For example, you might want to retrieve a list of databases, tables and views, constraints, or stored procedures from a data source. The DataReader has no built-in support for this type of information. However, it is possible to retrieve schema information directly with a specialized command, depending on the data source and data provider you use.

5.4.2.1 Retrieving schema tables with SQL Server

SQL Server exposes schema information through dedicated stored procedures and informational schema views. Informational schema views allow you to retrieve metadata as a table, using a SQL SELECT statement. However, the information is generated internally by the data source, not stored in a table. For example, the code in Example 5-7 shows how you can use one of the information schema views (TABLES) to retrieve a list of all the tables and views in the Northwind database. Though it appears to be querying information from a table, there is no physical table named INFORMATION_SCHEMA.TABLES in the data source.

A full description of information schemas is beyond the scope of this book, but they are described in detail in the SQL Server Books Online (just search for INFORMATION_SCHEMA).

Example 5-7. Retrieving a list of tables using an information schema

// GetTableList.cs - Retrieves a list of tables in a database

using System;
using System.Data.SqlClient;

public class GetTableList
{
    public static void Main() 
    {
        string connectionString = "Data Source=localhost;" +
                     "Initial Catalog=Northwind;Integrated Security=SSPI";
        string SQL = "SELECT TABLE_TYPE, TABLE_NAME FROM " +
                     "INFORMATION_SCHEMA.TABLES";

        // Create ADO.NET objects.
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand(SQL, con);
        SqlDataReader r;

        // Execute the query.
        try
        {
            con.Open();
            r = cmd.ExecuteReader();
            while (r.Read())
            {
                Console.WriteLine(r[0] + ": " + r[1]);
            }

        }
        finally
        {
            con.Close();
        }
    }
}

Here's a partial listing of the information this code returns:

VIEW: Alphabetical list of products
BASE TABLE: Categories
VIEW: Category Sales for 1997
VIEW: Current Product List
VIEW: Customer and Suppliers by City
BASE TABLE: CustomerCustomerDemo
BASE TABLE: CustomerDemographics
BASE TABLE: Customers
BASE TABLE: Employees
BASE TABLE: EmployeeTerritories
...

If you want to include only tables (not views), you can modify the query by adding an extra WHERE clause as follows:

SELECT TABLE_TYPE, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
  WHERE TABLE_TYPE = 'BASE TABLE'

Other views provide information about stored procedure parameters, columns, keys, constraints, user privileges, and more. You can also use specialized system stored procedures to perform tasks that the informational schema views can't, such as retrieving a list of all databases (via sp_catalog). All system stored procedures start with "sp_" and are documented in the SQL Server Books Online.

5.4.2.2 Retrieving schema tables with the OLE DB provider

Information views are limited to SQL Server and won't work with other data sources. However, if you use the OLE DB provider, you have another option. The OleDbConnection object provides a GetOleDbSchemaTable( ) method that can return various types of schema information, similar to what SQL Server accomplishes with its built-in informational views. Each data source handles this task differently, depending on the data source, but the ADO.NET code is generic.

GetOleDbSchemaTable( ) takes two parameters. The first is a value from the OleDbSchemaGuid class that specifies the type of schema information you want to return. The second is an array of objects that represent column restrictions. You apply these in the same order as the columns of the schema table.

Each field in the OleDbSchemaGuid class maps to an OLE DB schema rowset. Your OLE DB provider doesn't necessarily support all values from the OleDbSchemaGuid class. Refer to your database documentation for specific information.

GetOleDbSchemaTable( ) returns the schema information as a DataTable, similar to the GetSchemaTable( ) method of the DataReader. Example 5-8 uses GetOleDbSchemaTable( ) to retrieve a list of tables and views, similar to Example 5-7.

Example 5-8. Retrieving a list of tables using the OleDbSchemaGuid

// GetOleDbTableList.cs - Retrieves a list of tables in a database

using System;
using System.Data;
using System.Data.OleDb;

public class GetSchema
{
    public static void Main() 
    {
        string connectionString = "Data Source=localhost;" +
          "Provider=SQLOLEDB;Initial Catalog=Northwind;" +
          "Integrated Security=SSPI";

        // Create ADO.NET objects.
        OleDbConnection con = new OleDbConnection(connectionString);

        DataTable schema;

        // Execute the query.
        try
        {
            con.Open();
            schema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
                     new object[] {null, null, null, null});

        }
        finally
        {
            con.Close();
        }

        // Display the schema table.
        foreach (DataRow row in schema.Rows)
        {
            Console.WriteLine(row["TABLE_TYPE"] + ": " +
                              row["TABLE_NAME"]);
        }
    }
}

The resulting output is similar to the previous example. To create a list that includes only tables, you would need to realize that the TABLE_NAME column is the fourth column returned from the GetOleDbSchemaTable( ) method. You can then specify a restriction by supplying a filter string as the fourth element of the restriction array:

schema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
         new object[] {null, null, null, "TABLE"});

For more information about the type of schema information you can retrieve with the OLE DB provider, refer to the OleDbSchemaGuid in the class library reference at the end of this book.