access 表结构详细

Metadata.NET - by Noel Rice

By: Lino Tadros

Abstract: This article will show the capability of requesting information from .NET providers at runtime and deal with Databases, Table, Fields, constraints, Index and data Types.

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 youneed to know whats available at runtime. The examples below written in Delphifor .NET demonstrate retrieving information about a database server andnavigating a result set of that information. . NET provides a consistent wayto get all significant information about database servers that have ADOproviders. 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 bestperformance, but here we want to access any database that has an ADOprovider. The methods we use to retrieve metadata (data describing thedatabases were interested in, such as table and field structure) requireOleDBConnection.

Say you want alltables in the Northwind database. The results are returned as a DataTableand can be hooked up directly to a DataGrid component:

The code to get itdone 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 currentdatabase. GetOleDbSchemaTable next retrieves all tables for the currentdatabase. The first parameter, OleDbSchemaGuid, determines what kind ofmetadata youre looking for. There are over 30 possibilities listed in the.NET Framework SDK help. In our examples here we use Catalog (Micro-Speakfor Database), Table, and Column. The second parameter listsrestrictions to filter that list. Well talk more about restricting the listin a moment but for now pass nil and return all table entries.

Finallythe DataTable returned is hooked up to the DataGrid DataSource allowing theschema to be shown.

Foran example we can create a simple tree view UI that displays databases, tablesand fields for a given connection.

The code thatloads the top-level list of databases demonstrates how to navigate the returnedDataTable 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 toiterate the list. For every row get a DataRow object by ordinal value, andDataColumn using the column name. To extract the actual data use theDataColumn property as an index into the row, along with the DataRowVersionmember Current, and finally use ToString() to get the text from the row itemobject.

Note: DataRowVersion is an enumeration specifying the stateof the data as it changes before and after editing. For example, you couldlook at DataRowVersion.Proposed rows of data before accepting changes made byuser editing. DataRowVersion Members are: Current, Default, Original andProposed.

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

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

The second parameter for GetOleDbSchemaTable uses an arrayof objects that map to columns in the returned DataTable. If null was passedinstead of new object[], all tables, including system tables would bereturned. Take a look back at the earlier screen print showing tables listedin a DataGrid where the fourth column is TABLE_TYPE and the values areTABLE and SYSTEM_TABLE. By passing the array of restrictions criteria weallow the first three columns to have any values and but the last column mustmatch the value TABLE. The .NET Framework SDK help lists for eachOleDbSchemaGuid, column names that can be used to restrict the results. To getthe 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);  .  .  .

Heresthe 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, whatif 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, butdoesnt actually tell you what the data type is. Where is this informationhiding? Call GetOleDBSchemaTable again and pass PROVIDER_TYPES for theOleDBSchemaGuid and you get back a nicely normalized list of all supportedtypes for the provider. (In a future article we can talk about using aDataView object to lookup into the provider types table). The returned DataTablelooks like this:

Onelast piece of housekeeping connecting to the server. Pass an ADO styleconnection string to the OleDbConnection component constructor (You will needto change the connection string SConnect constant in the example belowto fit your environment). This form of the constructor will automatically openthe connection. When youre done using the connection, close it (closingdoesnt 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;

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

Thank you for your time and write to you later.

About Falafel Software Inc:

Falafel Software is all about making the most of software development technology in order to complete the project on time and on budget with best possible user experience. Falafel Software offers a comprehensive suite of software development solutions ranging from strategy to design to implementation that businesses need in order to realize high returns on their investment.

Copyright ) 2003 Noel Rice
ALL RIGHTS RESERVED. NO PART OF THIS DOCUMENT CAN BE COPIED IN ANY FORM WITHOUT THE EXPRESS, WRITTEN CONSENT OF THE AUTHOR.