ADO.NET 2.0 new GetSchema method. Schema is finally a first class citizen of the managed providers.

ADO.NET 2.0 has finally added support for retrieving Schema information. In v1.1 your only option was to go through the OLEDB native provider through the GetOledbSchemaInfo class, now Schema is a first class citizen in all of the Managed Providers.

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

namespace DataViewer.Repro
{
public class Repro
{
public static int Main(string[] args)
{
SqlConnection sqlconnection1 = new SqlConnection("Server=.\\SQLEXPRESS ;Integrated security=sspi");
sqlconnection1.Open();
SqlCommand sqlcommand1 = sqlconnection1.CreateCommand();
DataTable datatable1 = sqlconnection1.GetSchema("databases");
foreach (DataRow row in datatable1.Rows)
{
foreach (DataColumn col in datatable1.Columns)
{
Console.WriteLine(col.ToString() +" = " +row[col].ToString());
}
}

return 1;
}
}
}
 

The Schema that you can retrieve (GetSchema(<Schema that you can retrieve>) ) is Backend specific. For a complete list of the valid values you can call GetSchema() with no parameters:

DataTable datatable1 = sqlconnection1.GetSchema();
foreach (DataRow row in datatable1.Rows)
{
Console.WriteLine(row["CollectionName"]);
}

valid Schema that you can retrieve for SqlServer 2005:

MetaDataCollections, DataSourceInformation, DataTypes, Restrictions, ReservedWords, Users, Databases, Tables, Columns, Views, ViewColumns, ProcedureParameters, Procedures, ForeignKeys, IndexColumns, Indexes, UserDefinedTypes

Rambling out. Standard disclaimer. This post is provided “AS IS” and confers no rights.