O'Reilly logo

ADO.NET Cookbook by Bill Hamilton

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required