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

  • Artykuł

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.

Comments

  • Anonymous
    August 03, 2004
    Thanks for the great posting. I have been wanting this feature for some time now as I hate calling those stored procs to get my schema info. Great feature and great posting! You have finally answered a long standing question about ADO.Net 2.0 that I have been having for a long time now.

    Again thanks.
  • Anonymous
    August 03, 2004
    good news :)

    ADO should have great revalution
  • Anonymous
    August 03, 2004
    Angel:

    Is all of that info derived through the connection exclusively?
  • Anonymous
    August 03, 2004
    Hi,

    have you looked at 1.1's GetSchemaTable() - this does something very similar. Obvioulsy it is not that powerful as GetSchema()

    dominick
  • Anonymous
    August 03, 2004
    Thanks for the posting !, Good to see lot of stuff that we have been coding is being given off the shelf
  • Anonymous
    August 04, 2004
    Bill,
    I am not sure I understand the question, GetSchema is a method of the Connection and the scope of the schema you retrieve is set by the connection. When you GetSchema("Databases") you will get all databases for the Sql Server that you are connected to, when you GetSchema("tables") you will only see tables for the database you are currently connected to. Makes sense?

    dominick,
    Good point, GetOledbSchemaInfo was not the only tool we had before GetSchema, please note that GetSchemaTable is a method off of the datareader and it is useful for some table schema information and little else.

    rcallaby, steeven and Krishna
    thanks for your comments!

  • Anonymous
    August 04, 2004
    Yep, that's exactly what I was asking - Thanks!
  • Anonymous
    June 16, 2006
    The comment has been removed
  • Anonymous
    June 16, 2006
    The comment has been removed