It is with great pleasure to have Noel Rice, Senior Architect and a Delphi veteran, be the first guest author on my "Caught in the .NET" column.  This article is extremely important to understand the advanced capabilities of the .NET providers.  Enjoy!
Lino Tadros

To create flexible open-ended database applications you need to know whats available at runtime. The examples below written in Delphi for .NET demonstrate retrieving information about a database server and navigating a result set of that information. . NET provides a consistent way to get all significant information about database servers that have ADO providers. A sampling of whats available:

• Databases (Catalogs)
• Tables
• Columns
• Keys & Indexes
• Permissions
• Constraints
• Data types

You get all this for free with the OleDBConnection component from System.Data.OleDb.

Note: For typical access to an MSSQL server you would use SQLConnection for best performance, but here we want to access any database that has an ADO provider. The methods we use to retrieve metadata (data describing the databases were interested in, such as table and field structure) require OleDBConnection.

Say you want all tables in the Northwind database. The results are returned as a DataTable and can be hooked up directly to a DataGrid component:

The code to get it done is deceptively brief:

procedure TfrmMain.btnLoadGrid_Click(Sender: TObject; E: EventArgs);
var
  tblDatabases: System.Data.DataTable;
begin
  Connection.ChangeDatabase('Northwind');
  tblDatabases := Connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, nil);
  DBGrid.DataSource := tblDatabases;
end;

The call to ChangeDatabase makes Northwind the current database. GetOleDbSchemaTable next retrieves all tables for the current database. The first parameter, OleDbSchemaGuid, determines what kind of metadata youre looking for. There are over 30 possibilities listed in the .NET Framework SDK help. In our examples here we use Catalog (Micro-Speak for Database), Table, and Column. The second parameter lists restrictions to filter that list. Well talk more about restricting the list in a moment but for now pass nil and return all table entries.

Finally the DataTable returned is hooked up to the DataGrid DataSource allowing the schema to be shown.

For an example we can create a simple tree view UI that displays databases, tables and fields for a given connection.

The code that loads the top-level list of databases demonstrates how to navigate the returned DataTable in code:

procedure TfrmMain.btnLoadTree_Click(Sender: TObject; E: EventArgs);
var
  I: integer;
  NodeDatabases: TreeNode;
  tblDatabases: System.Data.DataTable;
  Row: System.Data.DataRow;
  Col: System.Data.DataColumn;
  SDBName: string;
begin
  TV.Nodes.Clear;
  NodeRoot := TV.Nodes.Add('Databases');
  tblDatabases := Connection.GetOleDbSchemaTable(OleDbSchemaGuid.Catalogs, nil);
  for I := 0 to tblDatabases.Rows.Count - 1 do
  begin
    Row := tblDatabases.Rows[I];
    Col := tblDatabases.Columns['CATALOG_NAME'];
    SDBName := Row[Col, DataRowVersion.Current].ToString();
    NodeDatabases := NodeRoot.Nodes.Add(SDBName);
...

DataTable has DataRow and DataColumn properties used to iterate the list. For every row get a DataRow object by ordinal value, and DataColumn using the column name. To extract the actual data use the DataColumn property as an index into the row, along with the DataRowVersion member Current, and finally use ToString() to get the text from the row item object.

Note: DataRowVersion is an enumeration specifying the state of the data as it changes before and after editing. For example, you could look at DataRowVersion.Proposed rows of data before accepting changes made by user editing. DataRowVersion Members are: Current, Default, Original and Proposed.

Getting a list of tables from a given database is slightly more involved because the list needs to be restricted. GetOleDbSchemaTable brings back all tables (including system tables) and we want user tables only. A parallel example in C# looks like this:

DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
new object[] {null, null, null, "TABLE"});

The second parameter for GetOleDbSchemaTable uses an array of objects that map to columns in the returned DataTable. If null was passed instead of new object[], all tables, including system tables would be returned. Take a look back at the earlier screen print showing tables listed in a DataGrid where the fourth column is TABLE_TYPE and the values are TABLE and SYSTEM_TABLE. By passing the array of restrictions criteria we allow the first three columns to have any values and but the last column must match the value TABLE. The .NET Framework SDK help lists for each OleDbSchemaGuid, column names that can be used to restrict the results. To get the same effect in Delphi for .NET code, use an array of System.Object:

procedure TfrmMain.btnLoadTree_Click(Sender: TObject; E: EventArgs);
var
  T: integer;
  NodeTables: TreeNode;
  tblTables: System.Data.DataTable;
  Row: System.Data.DataRow;
  Col: System.Data.DataColumn;
  STableName: string;
  ArrFilter: array[0..3] of System.Object;
begin
  .
  .
  .
  Connection.ChangeDatabase(SDBName);
  ArrFilter[0] := nil;
  ArrFilter[1] := nil;
  ArrFilter[2] := nil;
  ArrFilter[3] := 'TABLE'; // only user tables
  tblTables := Connection.GetOleDBSchemaTable(OleDbSchemaGuid.Tables, ArrFilter);
  .
  .
  .

Heres the code for the Load Tree button altogether including column level metadata:

procedure TfrmMain.btnLoadTree_Click(Sender: TObject; E: EventArgs);
var
  I, T, F: integer;
  NodeDatabases, NodeTables, NodeRoot: TreeNode;
  tblDatabases, tblTables, tblFields: System.Data.DataTable;
  Row: System.Data.DataRow;
  Col: System.Data.DataColumn;
  SDBName, STableName, SFieldName: string;
  ArrFilter: array[0..3] of System.Object;
begin
  TV.Nodes.Clear;
  NodeRoot := TV.Nodes.Add('Databases');
  tblDatabases := Connection.GetOleDbSchemaTable(OleDbSchemaGuid.Catalogs, nil);
  for I := 0 to tblDatabases.Rows.Count - 1 do
  begin
    Row := tblDatabases.Rows[I];
    Col := tblDatabases.Columns['CATALOG_NAME'];
    SDBName := Row[Col, DataRowVersion.Current].ToString();
    NodeDatabases := NodeRoot.Nodes.Add(SDBName);
     
    Connection.ChangeDatabase(SDBName);
    ArrFilter[0] := nil;
    ArrFilter[1] := nil;
    ArrFilter[2] := nil;
    ArrFilter[3] := 'TABLE'; // only user tables
    tblTables := Connection.GetOleDBSchemaTable(OleDbSchemaGuid.Tables, ArrFilter);
     
    for T := 0 to tblTables.Rows.Count - 1 do
    begin
      Row := tblTables.Rows[T];
      Col := tblTables.Columns['TABLE_NAME'];
      STableName := Row[Col, DataRowVersion.Current].ToString();
      NodeTables := NodeDatabases.Nodes.Add(STableName);
       
      ArrFilter[0] := SDBName;
      ArrFilter[1] := nil;
      ArrFilter[2] := STableName;
      ArrFilter[3] := nil;
      tblFields := Connection.GetOleDBSchemaTable(OleDbSchemaGuid.Columns, ArrFilter);
       
      for F := 0 to tblFields.Rows.Count - 1 do
      begin
        Row := tblFields.Rows[F];
        Col := tblFields.Columns['COLUMN_NAME'];
        SFieldName := Row[Col, DataRowVersion.Current].ToString();
        NodeTables.Nodes.Add(SFieldName);
      end;

    end;
  end;
end;

Now that you can get databases, tables and columns, what if you want to get more information on the column itself such as data type, size or precision? Column data happens to include a DATA_TYPE identifier, but doesnt actually tell you what the data type is. Where is this information hiding? Call GetOleDBSchemaTable again and pass PROVIDER_TYPES for the OleDBSchemaGuid and you get back a nicely normalized list of all supported types for the provider. (In a future article we can talk about using a DataView object to lookup into the provider types table). The returned DataTable looks like this:

One last piece of housekeeping  connecting to the server. Pass an ADO style connection string to the OleDbConnection component constructor (You will need to change the connection string SConnect constant in the example below to fit your environment). This form of the constructor will automatically open the connection. When youre done using the connection, close it (closing doesnt occur automatically by going out of scope).

constructor TfrmMain.Create;
const
  SConnect = 'Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security ' +
    'Info=False;Use Procedure for Prepare=1;Auto Translate=True;Packet ' +
    'Size=4096;Workstation ID=NR1;Use Encryption for Data=False;Tag with ' +
    'column collation when possible=False';
begin
  inherited Create;
  Initialize();
  Connection := OleDbConnection.Create(SConnect);
  Connection.Open();
end;
 
destructor TfrmMain.Destroy;
begin
  Connection.Close();
end;

I especially like this one size fits all approach to querying server data structure regardless of database type. If an ADO provider exists for the server, you can find out everything you need to know and then some.