ADO.NET Cookbook

10.2. Retrieving Database Schema Information from SQL Server

Problem

You need to retrieve database schema information from a SQL Server database.

Solution

Retrieve table schema information using either information schema views or the OLE DB .NET data provider Connection object.

The sample code retrieves a list of tables in the Northwind sample database.

The C# code is shown in Example 10-2.

Example 10-2. File: DatabaseSchemaForm.cs

// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;

//  . . . 

DataTable schemaTable;

if(sqlServerRadioButton.Checked)
{
    String getSchemaTableText = "SELECT * " +
        "FROM INFORMATION_SCHEMA.TABLES " +
        "WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_TYPE";

    // Retrieve the schema table contents.
    SqlDataAdapter da = new SqlDataAdapter(getSchemaTableText,
        ConfigurationSettings.AppSettings["Sql_ConnectString"]);
    schemaTable = new DataTable( );
    da.Fill(schemaTable);

    schemaDataGrid.CaptionText = "SQL Server .NET Provider";
}
else
{
    OleDbConnection conn = new OleDbConnection(
        ConfigurationSettings.AppSettings["OleDb_ConnectString"]);
    conn.Open( );
    // Get the schema table.
    schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
        new object[] {null, null, null, "TABLE"});
    conn.Close( );

    schemaDataGrid.CaptionText = "OLE DB .NET Provider";
}

// Bind the default view of schema table to the grid.
schemaDataGrid.DataSource = schemaTable.DefaultView;

Discussion

The first ...