Retrieving an Access table' structure

Anyone could help me in figuring out how to programmatically understand the structure of an Access table
I mean, given a DB connection and the name of a table, how to determine:

- the name of the fields of that table

- the type of the fields

- any eventually relationship between the fields of this table with others in the DB

Thanks a lot,

Davide

Answer this question

Retrieving an Access table' structure

ChangXU
Thanks a lot, very helpful.

Davide
JGordon
I have working code for you to retrieve the datatable structure. I'm at the office right now, give me some time. When I'm at home I'll search for the code and modify it to a easy to use library.
MattP88

Hi,

i just saw this message thread on the forum, could you please send me same code.

my email address is muhdatif@gmail.com

Many Thanks,

regards,

atif
&#214;mer KU&#350;CU
hmm how will you send me the code or will you just post it here anyway, thanks alot man..
EvanY
Hi, I saw your post and would like to know if you still have the code for SQL Server Would you mind sending it to me as well My email is: mkduffi@yahoo.com

Thanks
Sillyman
I know this post is a little old(last year), but I noted people asking for the SQL Server version that does the same thing. Just out of curiosity, couldn't you just use the getSchema method to retrieve the database and table information

Just curious.

Aaron
Nele B.

take a look here:

http://aspalliance.com/542
Chris Bennet
You are inherrinting from ModelGenerator. Could you tell where to find that Class

Anonymous123123132

Here is the core of a little generator for the Database Update software i have writen:

using System;
using System.Collections;
using System.Data;
using System.Data.OleDb;
using System.Diagnostics;

namespace MyNamespace
{
/// <summary>
/// Summary description for AccessModelGenerator.
/// </summary>
/// <remarks>
/// When we retrieve schema information with OleDb, we don't need a DataAdapter.
/// We're going to let the connection object get the information for us. The GetOleDbSchemaTable
/// method returns schema information from a data source as indicated by a Guid.( A GUID represents
/// a globally unique identifier (GUID). This identifier has a low probability of being duplicated
/// as it is composed of a 128-bit integer (16 bytes) that can be used across all computers and
/// networks wherever a unique identifier is required.) You supply a value from the OleDbSchemaGuid
/// enumeration to specify the type of schema information you want, such as tables, columns,
/// and procedures. In addition to taking the Guid schema argument, you can further restrict the
/// results of the GetOleDbSchemaTable( ) through the second argument. This argument is an object
/// array specifying column restrictions. Using the restrictions parameter, you can retrieve
/// information for just a few selected columns in a particular table rather than retrieving
/// information for all columns in your database. The syntax for the method is :
/// <c>public DataTable GetOleDbSchemaTable(Guid schId, object[] restrictions)</c>
/// Each value in the Object array corresponds to a DataColumn in the resulting DataTable.
/// The Restrictions array for the member should have the following structure:
/// <c>{TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE}</c>.
/// </remarks>
public class AccessModelGenerator : ModelGenerator
{
private OleDbConnection _dbConn;

/// <summary>
/// Provides a <c>ModelGenerator</c> to generate <c>DatabaseModel</c> from an Access Database.
/// </summary>
public AccessModelGenerator( OleDbConnection dbConn )
{
_dbConn = dbConn;
}

/// <summary>
/// Generate a <c>DatabaseModel</c> from an existing Access Database.
/// </summary>
/// <returns>A <c>DatabaseModel</c> that represents the Access Database.</returns>
public override DatabaseModel GetDatabaseModel()
{
// Get the Tables of the database and put them in a collection.
TableCollection tables = GetTables( _dbConn );

// Return new DatabaseModel with the retrieved tables.
return new DatabaseModel( new Version( 1, 0, 0, 0 ), tables );
}

/// <summary>
/// Retrieve all tables that are in the database.
/// </summary>
/// <param name="dbConn">The database connection that is connected or can connect to the database.</param>
/// <returns>A <c>TableCollection</c> that contains the Tables that are in a Access Database.</returns>
protected TableCollection GetTables( OleDbConnection dbConn )
{
TableCollection tables = new TableCollection();

// Get all the non-system table name.
string[] tableNames = GetTableNames( dbConn );

for (int i = 0; i < tableNames.Length; i++)
{
string tableName = tableNames[ i ];

// Retrieve all fields and indexes of the table.
FieldCollection fields = GetFieldsFromTable( dbConn, tableName );
IndexCollection indexes = GetIndexesFromTable( dbConn, tableName );

foreach (Index index in indexes)
{
foreach (Field field in fields)
{
// If the index is for the field and the field does is not allready
// marked as a PrimairyKey that will indicate that this field is
// allready handeled by an overruling PrimaryKey index.
if (index.ContainsFieldName( field.Name ) && !field.IsPrimairyKey)
{
// Lookup identity and Primary Key.
// Note: AllowsNull is not defined by the index. But is retrieved in the
// GetFieldsFromTable method by the OleDbSchema.Columns IS_NULLABLE value.
field.IsPrimairyKey = index.PrimaryKey;
}
}
}

long fieldTopID = 0;

foreach(Field field in fields)
{
if(fieldTopID < field.ID)
{
fieldTopID = field.ID;
}
}

// Create new Table with the retrieved fields and indexes and adds it to the collection.
// Uses variable i as ID.
Table table = new Table( i, tableName, fields.ToArray(), indexes.ToArray(), fieldTopID );
tables.Add( table );

// Rise TableRetrived event.
OnTableRetrieved( table );
}

// Return the filled table collection.
return tables;
}

/// <summary>
/// Counts the number of Non-sytem Tables that are in a Access Database.
/// </summary>
/// <remarks>
/// It will count the number of Non-system Tables and it will identify System Table by
/// starting with MSys and TABLE_TYPE is not "TABLE".
/// </remarks>
/// <returns>The number of Non-system Tables.</returns>
public override int CountTables()
{
// Gets all non-system table names and returns the number of found tables.
return GetTableNames( _dbConn ).Length;
}

/// <summary>
/// Retieves all fields from a specified table.
/// </summary>
/// <param name="dbConn">The database connection that is connected or can connect to the database.</param>
/// <param name="tablename">The name of the table you want to lookup.</param>
/// <returns>A <c>FieldCollection</c> that contains all fields.</returns>
protected FieldCollection GetFieldsFromTable( OleDbConnection dbConn, string tablename )
{
// Write debug info.
Debug.WriteLine( "GetFieldsFromTable::" + tablename );
Debug.Indent();

// Ensure the arguments are not null.
if (tablename == null) throw new ArgumentNullException( "tablename" );
if (dbConn == null) throw new ArgumentNullException( "dbConn" );

FieldCollection fields = new FieldCollection();

try
{
if (dbConn.State != ConnectionState.Open)
dbConn.Open();

// Retrieve the the Column Schema, that contains all the column that are in the database.
Guid fieldSchema = OleDbSchemaGuid.Columns;
DataTable schema = dbConn.GetOleDbSchemaTable( fieldSchema, new object[] {null, null, tablename, null} );

for (int i = 0; i < schema.Rows.Count; i++)
{
#if(DEBUG)
// Write debug info.
//Debug.WriteLine( "Field #" + i.ToString( "N2" ) );
//Debug.Indent();

// for(int j = 0; j < schema.Columns.Count; j++)
// {
// string columnName = schema.Columns[j].ColumnName;
// Debug.WriteLine( string.Format( "{0} = {1}", columnName, schema.Rows Idea

[j] ), "Field #" + i.ToString() );
// }

//Debug.Unindent();
#endif

DataRow row = schema.Rows[ i ];

// These values could not be retrieved this way.
bool isRowGuid = false;
bool isPrimaryKey = false;

// Retrieve the name of the field.
string name = string.Empty;
if (row[ "COLUMN_NAME" ] != DBNull.Value)
{
name = row[ "COLUMN_NAME" ] as String;
}

// Retrieve the data type of the field.
FieldDataType dataType = FieldDataType.Unknown;
if (row[ "DATA_TYPE" ] != DBNull.Value)
{
// When it is an Integer (DATA_TYPE == 3) and COLUMN_FLAGS == 122 it is an AutoNumberField; otherwise
// it is a normal integer.
// DATA_TYPE is and Int32 and COLUMN_FLAGS is an Int64.
if ((int) row[ "DATA_TYPE" ] == 3 && (long) row[ "COLUMN_FLAGS" ] == 90)
{
dataType = FieldDataType.AutoNumber;
}
// Lookup of it isn't a Memo type.
else if ((int) row[ "DATA_TYPE" ] == 130 && (long) row[ "COLUMN_FLAGS" ] == 234)
{
dataType = FieldDataType.Text;
}
else
{
OleDbType oleDbType = (OleDbType) row[ "DATA_TYPE" ];
dataType = FieldDataTypeConverter.Parse( oleDbType );
}
}

// Retrieve the size of the field.
int size = Field.DEFAULT_SIZE;
if (row[ "CHARACTER_MAXIMUM_LENGTH" ] != DBNull.Value)
{
size = Convert.ToInt32( row[ "CHARACTER_MAXIMUM_LENGTH" ] );

if (dataType == FieldDataType.VarChar && size == 0)
dataType = FieldDataType.Text;
}

// Retrieve the default value of the field, if it has one.
string defaultValue = string.Empty;
if ((bool) row[ "COLUMN_HASDEFAULT" ])
{
defaultValue = Convert.ToString( row[ "COLUMN_DEFAULT" ] );
}

// Retrieve the numeric presision of the field.
int precision = -1;
if (row[ "NUMERIC_PRECISION" ] != DBNull.Value)
{
precision = Convert.ToInt32( row[ "NUMERIC_PRECISION" ] );
}

// Retrieve the numeric scale of the field.
int scale = -1;
if (row[ "NUMERIC_SCALE" ] != DBNull.Value)
{
scale = Convert.ToInt32( row[ "NUMERIC_SCALE" ] );
}

// Retrieve the name of the field.
bool allowsNull = true;
if (row[ "IS_NULLABLE" ] != DBNull.Value)
{
allowsNull = Convert.ToBoolean( row[ "IS_NULLABLE" ] );
}

// Retrieve the ordinal position of the field.
int ordinal = -1;
if (row[ "ORDINAL_POSITION" ] != DBNull.Value)
{
ordinal = Convert.ToInt32( row[ "ORDINAL_POSITION" ] );
}

// Create a new field and adds it to the field collection.
// The ordinal will be used as id to. The ordinal is allways unique
// and represents the ordinal position of the field.
Field field = new Field( ordinal, name, ordinal, dataType, size, defaultValue, precision, scale,
isRowGuid, allowsNull, isPrimaryKey );
fields.Add( field );

// Rise FieldRetrieved event.
OnFieldRetrieved( field );
}
}
finally
{
// Ensure the database connection will be closed.
dbConn.Close();
}

Debug.Unindent();
// Return the retrieved fields.
return fields;
}

/// <summary>
/// Retrieve all indexes from a specified table.
/// </summary>
/// <param name="dbConn">The database connection that is connected or can connect to the database.</param>
/// <param name="tableName">The name of the table to lookup.</param>
/// <returns>A <c>IndexCollection</c> that contains all indexes of the specified table.</returns>
protected IndexCollection GetIndexesFromTable( OleDbConnection dbConn, string tableName )
{
// Ensure the arguments is not null.
if (dbConn == null) throw new ArgumentNullException( "dbConn" );
if (tableName == null) throw new ArgumentNullException( "tableName" );

IndexCollection indexes = new IndexCollection();

try
{
if (dbConn.State != ConnectionState.Open)
dbConn.Open();

// Retrieve the the Tables Schema.
Guid tablesSchema = OleDbSchemaGuid.Indexes;
DataTable schema = dbConn.GetOleDbSchemaTable( tablesSchema, null );

for (int i = 0; i < schema.Rows.Count; i++)
{
DataRow row = schema.Rows[ i ];

// Retrieve the name of the index.
string indexName = string.Empty;
if (row[ "INDEX_NAME" ] != DBNull.Value)
{
indexName = Convert.ToString( row[ "INDEX_NAME" ] );
}
else
throw new FormatException("Missing field INDEX_NAME .");

// Retrieve the table name of the index.
string indexTableName = string.Empty;
if (row[ "TABLE_NAME" ] != DBNull.Value)
{
indexTableName = Convert.ToString( row[ "TABLE_NAME" ] );
}
else
throw new FormatException("Missing field TABLE_NAME .");

// Retrieve the field name of the index.
string indexFieldName = string.Empty;
if (row[ "COLUMN_NAME" ] != DBNull.Value)
{
indexFieldName = Convert.ToString( row[ "COLUMN_NAME" ] );
}
else
throw new FormatException("Missing field COLUMN_NAME .");

// Retrieve a indication if the index is a Primary Key index.
bool indexPrimaryKey = false;
if (row[ "PRIMARY_KEY" ] != DBNull.Value)
{
indexPrimaryKey = Convert.ToBoolean( row[ "PRIMARY_KEY" ] );
}
else
throw new FormatException("Missing field PRIMARY_KEY .");

// Retrieve a indication if the index is unique.
bool indexUnique = false;
if (row[ "UNIQUE" ] != DBNull.Value)
{
indexUnique = Convert.ToBoolean( row[ "UNIQUE" ] );
}
else
throw new FormatException("Missing field UNIQUE .");

// Retrieve a indication if the index is clustered.
bool indexClustered = false;
if (row[ "CLUSTERED" ] != DBNull.Value)
{
indexClustered = Convert.ToBoolean( row[ "CLUSTERED" ] );
}
else
throw new FormatException("Missing field CLUSTERED .");

// If the table name of the index is the same as the specified table name
// by the method argument, adds it to the index collection.
// When the index name starts with a '{' and ends with '}' it will be ignored. Because
// it is a System Index.
if (indexTableName == tableName && !(indexName.StartsWith( "{" ) && indexName.EndsWith( "}" )))
{
bool isRelationIndex = false;

string[] tables = GetTableNames( dbConn );

foreach (string startTableName in tables)
{
if (indexName.StartsWith( startTableName ))
{
string endIndexName = indexName.Substring( startTableName.Length );

foreach (string endTableName in tables)
{
if (endIndexName.Equals( endTableName ))
{
isRelationIndex = true;
break;
}
}

if (isRelationIndex)
{
#if( DEBUG )
string message = string.Format( "{0} was an RelationIndex.", indexName );
Debug.WriteLine( message );
#endif
break;
}
}
}

if (!isRelationIndex)
{
// Check if the index doesn't allready exists with the same name.
// This will indicate a Index with more fields.
bool exists = false;
for (int j = 0; j < indexes.Count; j++)
{
if (indexes[ j ].Name == indexName)
{
// The index allready exists, so add the current fieldname of the index.
indexes[ j ].AddFieldName( indexFieldName );
exists = true;
}
}

Index index = new Index( indexName, indexTableName, indexFieldName,
indexPrimaryKey, indexUnique, indexClustered );

if (!exists)
{
// Adds the index, because it did not exists.
indexes.Add( index );
}

// Rise IndexRetrieved event.
OnIndexRetrieved( index );
}
}
else
{
// The table name of the index was not the same as the specified table name
// by the method argument or the index was a system index, so just continue.
continue;
}
}
}
finally
{
// Ensure the database connection will be closed.
dbConn.Close();
}

// Return the retrieved indexes.
return indexes;
}

/// <summary>
/// Retrieve all Non-system Table names.
/// </summary>
/// <remarks>
/// It will count the number of Non-system Tables and it will identify System Table by
/// starting with MSys and TABLE_TYPE is not "TABLE".
/// </remarks>
/// <param name="dbConn">The database connection that is connected or can connect to the database.</param>
/// <returns>A string array that represents all Non-system Table names.</returns>
protected string[] GetTableNames( OleDbConnection dbConn )
{
// Ensure the argument is not null.
if (dbConn == null) throw new ArgumentNullException( "dbConn" );

ArrayList tableNames = new ArrayList();

try
{
if (dbConn.State != ConnectionState.Open)
dbConn.Open();

// Retrieve the the Tables Schema.
Guid tablesSchema = OleDbSchemaGuid.Tables;
DataTable schema = dbConn.GetOleDbSchemaTable( tablesSchema, null );

for (int i = 0; i < schema.Rows.Count; i++)
{
DataRow row = schema.Rows[ i ];

// Get the name and type of the table.
string tableName = row[ "TABLE_NAME" ] as string;
string tableType = row[ "TABLE_TYPE" ] as string;

// Ensure it is not a system table (SYSTEM_TABLE) or something else and
// filter out the temporary tables. They can be reconized by the first char
// that is a ~.
if (tableType == "TABLE" && !tableName.StartsWith( "MSys" ) && !tableName.StartsWith( "~" ))
{
// Add table name.
tableNames.Add( tableName );
}
}

// Return the table name collection.
return (string[]) tableNames.ToArray( typeof (string) );
}
finally
{
// Ensure the database connection will be closed.
dbConn.Close();
}
}
}
}

Peter Saddow MSFT
No, but i can send you the code the retrieves the structure of an SQL Database
Ultimaniac
hi PJ,

will that code be able to retrive SQL table structure also
Devendersys
How did you figure out what the DATA_TYPE return value represent I am getting integer values, but lord only knows what they represent. Could you point me to your reference Thanks.
Regis

The DATA_TYPE values correspond to the System.Data.OleDb.OleDbType enumerated constants. You can view these in the Object Browser.
Bishi
oh wow! can you do that for me PJ i'll be really greatful..
Retrieving an Access table' structure

Answer this question

Retrieving an Access table' structure

Hot Topic

.NET Development

Retrieving an Access table' structure

Retrieving an Access table' structure

ChangXU

JGordon

MattP88

&#214;mer KU&#350;CU

EvanY

Sillyman

Nele B.

Chris Bennet

Anonymous123123132

Peter Saddow MSFT

Ultimaniac

Devendersys

Regis

Bishi