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 ...

Get ADO.NET Cookbook now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.