Getting database schema using ADO.NET

wahiahia

终于解决了读mdb文件,在如何去除system table上困扰了好久,呵呵.因为bf提示找GetOleDbSchemaTable和TABLE_NAME,嘻嘻,总算找到解决的方法了.

A database schema describes the organisation structure of the database; schema broadly consists of the structure of tables, stores procedures and the views of the database. ADO.NET provides a rich infrastructure to get the database schema information. The OLEDBConnection object exposes a GetOLEDBSchemaTable function that is used to retrieve the schema information from the currosponding database.

The GetOLEDBSchemaTable function accepts two parameters:

 

• OLEDBSchemaGUID
• Restrictions

  The OLEDBSchemaGUID members are used for the schema tables; the members consist of tables, procedures, views, columns, catalogs, etc. The intellisense will give a list of all the members of OLEDBSchemaGUID.

  The restrictions is an object array used to filter the schema result. Each maps to the values of the datacolumn returned. The example below will make it clear.

  Open a new windows application in VS.NET. This is pretty simple code and can be easily mapped to C# or VB.NET.

  Add a datagrid > datagrid1

  Code to get a list of all the tables from the database schema:

  //Open a connection object
  OleDbConnection con = new OleDbConnection
  ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/Test.mdb;Persist Security
  Info=False");
  con.Open(); 
  
  //Declare a Datatable object
  System.Data.DataTable dt ;
  
  //Query for getting the list of all tables from the schema, no
  restriction
  dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
  
  //display the data in a datagrid
  dataGrid1.DataSource = dt;
  

  The above code will return a list of all tables, system as well as user tables. We will now see how we can apply a restriction for only returning the user tables. Notice that the fourth column is "TABLE_TYPE." All the user tables will have a value "TABLE," so this is our filtering criteria. We will pass an object array that will have the filtering/restriction criteria.

  Code to get a list of all user tables by applying restriction:

  //Query for getting only the user tables
  dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new
  object[]{null,null,null,"TABLE"} );
  
  //display the data in a datagrid
  dataGrid1.DataSource = dt;
  
  //Query for getting stored prodedures
  dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Procedures, null );
  
  //display the data in a datagrid
  dataGrid1.DataSource = dt;
  
  

  Code to get a list of all primary keys from the database:

  //Query for getting all primary keys
  OleDbSchemaGuid.dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys,
  null );
  
  //display the data in a datagrid
  dataGrid1.DataSource = dt;
  
  

  Code to get the all supported provider data types:

  //Query for getting all supported types
  dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Provider_Types, null );
  
  //display the data in a datagrid
  dataGrid1.DataSource = dt;
  

  Check out the OLEDBSchemaGUID members to get a list of supported schema members. Happy coding.