Sahir Shah
10-January-2006
ADO Connection object's OpenSchema method is one way of obtaining information about tables in a database and fields in a table. Most of the examples given in MSDN resources deal with using these methods in Visual Basic and Visual C++ 6. Due to the absence of sample code available on the net there is some ambiguity about it's use in .NET and is a question frequently encountered in internet forums. This example project demonstrates reading data from an MSAccess database and creating batch files for MySQL. This is a stripped down and modified version of an application I wrote three or four years ago before an official MySQL migration tool became available.
To list tables in the database we connect to the database and then call OpenSchema on the connection object. The OpenSchema method takes three parameters : QueryType, Criteria and SchemaID. The QueryType parameter is a SchemaEnum value and Criteria is an object array. The last parameter is a GUID used for provider specific schema queries. It is not used when working with Jet and other Microsoft providers. In place of the parameters that are not used Missing.Value can be used. The class Missing belongs to the System.Reflection namespace Value is a static member of this class.
ADODB.Recordset rs;
ADODB.Connection connObj = new ADODB.ConnectionClass();
connObj.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:/db1.mdb;";
connObj.Open(null, null , null , 0);
if(connObj.State != ADO_OPEN)
{
MessageBox.Show("Could not connect to database");
return;
}
rs = connObj.OpenSchema(ADODB.SchemaEnum.adSchemaTables, Missing.Value, Missing.Value);
while(!rs.EOF) {
if(rs.Fields["TABLE_TYPE"].Value.ToString().Equals("TABLE"))
listTables.Items.Add( rs.Fields["TABLE_NAME"].Value.ToString());
rs.MoveNext();
}
This call returns a recordset object which contains the following fields :
The TABLE_TYPE field can contain the values : TABLE, VIEW , ACCESS TABLE and SYSTEM TABLE. If you want to exclude other system objects you can test if this field contains the value "TABLE" and then get the table name.
To list the fields in the table we call the same method with a different set of parameters.The first parameter is another member of SchemaEnum enumeration and the second parameter is an object array with the table name as the third element of the array. Again, the third parameter is a missing object.
ADODB.Recordset rs;
object[] arry = new object[] {null, null, "Table1"};
rs = connObj.OpenSchema(ADODB.SchemaEnum.adSchemaColumns, arry , Missing.Value );
while(!rs.EOF) {
listFieldName.Items.Add(rs.Fields["COLUMN_NAME"].Value.ToString());
listType.Items.Add(rs.Fields["DATA_TYPE"].Value.ToString());
if(rs.Fields["CHARACTER_MAXIMUM_LENGTH"].Value != null)
listSize.Items.Add(rs.Fields["CHARACTER_MAXIMUM_LENGTH"].Value.ToString());
rs.MoveNext();
}
The returned recordset contains the following fields :
The fields likely to be of interest are COLUMN_NAME , DATA_TYPE and CHARACTER_MAXIMUM_LENGTH. Data type column contain the respective value of the type in DATATYPEENUM. The definition of DATATYPEENUM can be found here.