ADO.NET 3.5 Cookbook, 2nd Edition

11.3. 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 the GetSchema() method of the Connection class, catalog views, information schema views, or the OLE DB .NET data provider Connection object. The solution demonstrates all four approaches.

For each approach, the solution retrieves a list of tables in the AdventureWorks sample database and outputs the first 10 tables to the console.

The C# code in Program.cs in the project RetrieveDatabaseSchema is shown in Example 11-3.

Example 11-3. File: Program.cs for RetrieveDatabaseSchema solution

using System; using System.Data; using System.Data.SqlClient; using System.Data.OleDb; namespace RetrieveDatabaseSchema { class Program { static void Main(string[] args) { string sqlConnectString = "Data Source=(local);" + "Integrated security=SSPI;Initial Catalog=AdventureWorks;"; string oledbConnectString = "Provider=SQLOLEDB;" + "Data Source=(local);Integrated Security=SSPI;" + "Initial Catalog=AdventureWorks"; DataTable dtSchema; SqlDataAdapter da; // Retrieve table list using Connection.GetSchema( ) SqlConnection conn = new SqlConnection(sqlConnectString); conn.Open( ); dtSchema = conn.GetSchema("Tables"); conn.Close( ); Console.WriteLine( "---Top 10 tables names using Connection.GetSchema( )---"); for (int i = 0; i < 10; i++) { Console.WriteLine("{0}.{1}.{2}", dtSchema.Rows[i]["TABLE_CATALOG"], dtSchema.Rows[i]["TABLE_SCHEMA"], ...