Exploration de la structure d'une base de données Access

Messages postés
169
Date d'inscription
vendredi 1 septembre 2006
Statut
Membre
Dernière intervention
24 septembre 2019
-
Bonjour,

Attention : il s'agit ici d'une base Access 2002-2003 (*.mdb - Jet.OLEDB.4.0)
J'utilise en effet toujours ce format pour mes applications C# car le moteur Jet est présent par défaut dans Windows 7 et ultérieur. Il n'y a donc pas besoin d'installateur ni d'environnement spécifique pour faire tourner ces applications. On pourrait sans doute aussi utiliser Jet.OLEDB.12.0, mais je n'ai pas essayé.

Je développe actuellement une appli destinée à transformer une base Access (*.mdb) en base MySql et réciproquement. J'ai donc eu besoin de décortiquer la structure des bases Access pour pouvoir générer les requêtes de création de tables en MySql.

Après avoir farfouillé le net à la recherche de code répondant à mon besoin, et n'en trouvant que des bribes éparses, j'ai généré une classe contenant la plupart des informations de structure après moult recherches en run dans le volet "Variables Locales".

Cette classe est loin d'être complète et beaucoup de points restent obscurs. Les données ramenées par GetOleDbSchemaTable sont très mal documentées, ou je n'ai pas su trouver un document complet et exhaustif.

Toujours est-il que la classe actuelle contient les éléments suffisants pour recréer une base MySql : champs, indexes et relations. Les types de champs sont difficiles à déterminer : j'ai créé une table Access témoin comprenant un champ de chaque type connu par Access (17 types différents). Certains champs de types différents renvoient la même valeur pour Type dans la SchemaTable. Par exemple, les champsTexte et Memo renvoient tous les deux le type numérique 130. Après observation, j'ai pu les différencier grâce au Flag "Long". Mais il y a encore beaucoup de subtilités que je n'ai pas trouvées.

Notamment, il y a un flou entre la propriété Nullable d'un champ et ses flags IsNullable et MayBeNull. J'avoue ne pas avoir compris leur signification précise et leur interaction.

C'est pourquoi, si cela intéresse quelqu'un, il serait bon de continuer à gratter dans la SchemaTable pour trouver les subtilités qui manquent. Je commence à saturer.

Je mets le code de la classe en fin de ce post. Pour l'utiliser, il suffit d'exécuter la ligne ci-dessous et de coller le presse-papiers dans un tableur type Excel.
Cnx est une connection OleDbConnection ouverte.
Le "Replace" est facultatif, mais nécessaire pour coller dans le bloc-note.
Clipboard.SetText(MgdTools.Access.AccessSchema.GetSerializedSchema(Cnx).Replace("\n", "\r\n"));


Merci pour votre collaboration.

Voici le code de ma classe :
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;

namespace MgdTools.Access
{
    public static class AccessSchema
    {
        /// 
        /// Correspondance entre les types de champs Access (numériques) et leur équivalent MySql
        /// 
        /// Table établie expérimentalement - Il peut manquer des valeurs
        private static readonly Dictionary FieldTypes = new Dictionary()
        {
            {2,"smallint"},
            {3,"int"},
            {4,"float"},
            {5,"double"},
            {6,"decimal"},
            {7,"datetime"},
            {8,"varchar"},
            {11,"boolean"},
            {12,"blob"},
            {14,"decimal"},
            {16,"tinyint"},
            {17,"tinyint unsigned"},
            {18,"smallint unsigned"},
            {19,"int unsigned"},
            {20,"int"},
            {21,"bigint unsigned"},
            {128,"binary"},         // Si flag "Long", => LONGBINARY
            {129,"char"},
            {130,"varchar"},        // Si flag "Long", => LONGTEXT (Mémo)
            {131,"decimal"},
            {133,"date"},
            {134,"time"},
            {135,"timestamp"},
            {138,"blob"},
            {200,"varchar"},
            {201,"longtext"},
            {202,"varchar"},
            {203,"varchar"},
            {204,"varbinary"},
            {205,"longblob"}
        };


        //-------------------------------------------------------------------------------------------------------------
        /// Classe contenant tous les paramètres d'une table
        public class TableInfo
        {
            ///  Nom de la table
            public string TableName { get; set; }
            /// Liste des champs
            public List Fields { get; set; } = new List();
            /// Liste des relations
            public List FKeys { get; set; } = new List();
            /// Liste des indexes
            public List Indexes { get; set; } = new List();
            /// Nom du champ clé primaire
            public string PrimaryKey { get; set; }
        }

        //-------------------------------------------------------------------------------------------------------------
        /// Classe contenant les paramètres d'un champ
        /// Il est impératif de remplir la valeur de Type et de Flags avant d'appeler TypeString
        public class Field : IComparable
        {
            /// Nom du champ
            public string Name { get; set; }
            /// Drapeaux
            public int Flags { get; set; }
            /// Type du champ
            public int Type { get; set; }
            /// Champ pouvant être null
            public bool Nullable { get; set; }
            /// Nombre max de caractères (champs alphanumériques)
            public int MaxChars { get; set; }
            /// Nombre de digits (champs numériques)
            public int Digits { get; set; }
            /// Nombre de décimales (champs numériques)
            public int Decimals { get; set; }
            /// Champ de grande taille (mémo, OLE, etc.)
            public bool IsLong { get { return (Flags & 0x80) != 0; } }
            /// >Champ pouvant avoir la propriété nullable (?)
            public bool IsNullable { get { return (Flags & 0x20) != 0; } }
            /// >Champ pouvant avoir une valeur null
            public bool MayBeNull { get { return (Flags & 0x40) != 0; } }
            /// Champ à longueur fixe
            public bool FixedLength { get { return (Flags & 0x10) != 0; } }
            /// Position ordinale du champ
            public int Position { get; set; }
            /// Type de champ MySql correspondant
            public string SqlTypeString
            {
                get
                {
                    try
                    {
                        if (Type == 130 && IsLong) return FieldTypes[201];          // varchar => longtext
                        else if (Type == 128 && IsLong) return FieldTypes[205];     // binary => longblob
                        else return FieldTypes[Type];
                    }
                    catch { return "unknown"; }
                }
            }

            /// Routine de comparaison pour le tri par position ordinale
            public int CompareTo(object obj)
            {
                Field Fd = obj as Field;
                if (Fd == null)
                    return 0;
                if (Fd.Position > this.Position)
                    return -1;
                else if (Fd.Position < this.Position)
                    return 1;
                else
                    return 0;
            }
        }

        //-------------------------------------------------------------------------------------------------------------
        /// Classe contenant les paramètres d'un index
        /// Les indexes à champs multiples sont définis par un même nom, et des positions incrémentées
        public class Index : IComparable
        {
            /// Nom de l'index
            public string Name { get; set; }
            /// Type d'index (toujours 1 ?)
            public string Type { get; set; }
            /// Champ objet de l'index
            public string Field { get; set; }
            /// Index primaire
            public bool Primary { get; set; }
            /// Index unique
            public bool Unique { get; set; }
            /// Index avec mise à jour automatique
            public bool AutoUpdate { get; set; }
            /// Position ordinale de l'index
            public long Position { get; set; }

            /// Routine de comparaison pour le tri
            /// Tri par nom, puis par index pour regrouper les indexes multi-champs
            public int CompareTo(object obj)
            {
                Index Ix = obj as Index;
                if (Ix == null)
                    return 0;
                if (Ix.Name == this.Name)
                {
                    if (Ix.Position > this.Position)
                        return -1;
                    else if (Ix.Position < this.Position)
                        return 1;
                    else
                        return 0;
                }
                else
                    return string.Compare(this.Name, Ix.Name);
            }
        }

        //-------------------------------------------------------------------------------------------------------------
        ///  Classe contenant les paramètres des relations
        public class FKey : IComparable
        {
            /// Nom du champ local
            public string Name { get; set; }
            /// Table étrangère
            public string Table { get; set; }
            /// Nom du champ étranger
            public string Column { get; set; }
            /// Postion ordinale de la clé
            public long Position { get; set; }

            /// Routine de comparaison pour le tri
            public int CompareTo(object obj)
            {
                FKey Key = obj as FKey;
                if (Key == null)
                    return 0;
                if (Key.Position > this.Position)
                    return -1;
                else if (Key.Position < this.Position)
                    return 1;
                else
                    return 0;
            }
        }

        //=============================================================================================================

        //-------------------------------------------------------------------------------------------------------------
        public static Dictionary getSchema(OleDbConnection Cnx)
        {
            Dictionary BaseStruct = new Dictionary();
            DataTable dtTables = Cnx.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            foreach (DataRow row in dtTables.Rows)
            {
                // Recherche des tables
                if (row["TABLE_TYPE"] as string == "TABLE")
                {
                    TableInfo Table = new TableInfo { TableName = row["TABLE_NAME"] as string };
                    if (Table.TableName[0] == '~')      // Table temporaire
                        continue;

                    BaseStruct[Table.TableName] = Table;

                    // Recherche des champs de la table
                    DataTable dtFields = Cnx.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, Table.TableName });
                    foreach (DataRow DC in dtFields.Rows)
                    {
                        Field Champ = new Field();
                        Champ.Name = DC["COLUMN_NAME"].ToString();
                        Champ.Position = GetIntValue(DC["ORDINAL_POSITION"].ToString());
                        Champ.Flags = GetIntValue(DC["COLUMN_FLAGS"].ToString());
                        Champ.Type = GetIntValue(DC["DATA_TYPE"].ToString());
                        Champ.MaxChars = GetIntValue(DC["CHARACTER_MAXIMUM_LENGTH"].ToString());
                        Champ.Digits = GetIntValue(DC["NUMERIC_PRECISION"].ToString());
                        Champ.Decimals = GetIntValue(DC["NUMERIC_SCALE"].ToString());
                        BaseStruct[Table.TableName].Fields.Add(Champ);
                    }
                }
            }

            // Recherche des clés primaires
            dtTables = Cnx.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, null);
            foreach (DataRow row in dtTables.Rows)
            {
                string table_name = row["TABLE_NAME"] as string;
                if (BaseStruct.ContainsKey(table_name))
                    BaseStruct[table_name].PrimaryKey = row["COLUMN_NAME"] as string;
            }

            // Recherche des indexes
            dtTables = Cnx.GetOleDbSchemaTable(OleDbSchemaGuid.Indexes, null);
            foreach (DataRow row in dtTables.Rows)
            {
                Index Ix = new Index();
                string table_name = row["TABLE_NAME"] as string;
                Ix.Name = row["INDEX_NAME"].ToString();
                Ix.Field = row["COLUMN_NAME"].ToString();
                Ix.Position = (long)row["ORDINAL_POSITION"];
                Ix.Primary = (bool)row["PRIMARY_KEY"];
                Ix.Unique = (bool)row["UNIQUE"];
                Ix.AutoUpdate = (bool)row["AUTO_UPDATE"];
                Ix.Type = row["TYPE"].ToString();
                KeyValuePair Info = BaseStruct.FirstOrDefault(x => x.Key == table_name);
                if (!Info.Equals(default(KeyValuePair)))
                    Info.Value.Indexes.Add(Ix);
            }

            // Recherche des clés étrangères
            dtTables = Cnx.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, null);
            foreach (DataRow row in dtTables.Rows)
            {
                string fk_table_name = row["FK_TABLE_NAME"] as string;
                if (BaseStruct.ContainsKey(fk_table_name))
                {
                    FKey key = new FKey();
                    key.Name = row["FK_COLUMN_NAME"].ToString();
                    key.Table = row["PK_TABLE_NAME"].ToString();
                    key.Column = row["PK_COLUMN_NAME"].ToString();
                    key.Position = (long)row["ORDINAL"];
                    BaseStruct[fk_table_name].FKeys.Add(key);
                }
            }

            // Tri des listes
            foreach (KeyValuePair Info in BaseStruct)
            {
                TableInfo Table = Info.Value;
                Table.Fields.Sort();
                Table.Indexes.Sort();
                Table.FKeys.Sort();
            }
            return BaseStruct;
        }

        //-------------------------------------------------------------------------------------------------------------
        /// 
        /// Retourne la structure de la base sous forme de texte tablulé à coller dans un tableur pour visualisation
        /// 
        /// OleDbConnection ouverte
        /// Chaine de caractères tabulée
        public static string GetSerializedSchema(OleDbConnection Cnx)
        {
            string Resu = "";

            Dictionary Struct = getSchema(Cnx);
            foreach (KeyValuePair Info in Struct.OrderBy(key => key.Key))    // Tri alphabétique des tables
            {
                TableInfo Table = Info.Value;
                Resu += Table.TableName + "\n";

                // Champs
                string FmtFields = "";
                for (int i = 0; i < 14; i++)
                    FmtFields += "{" + i.ToString() + "}\t";
                FmtFields = FmtFields.Substring(0, FmtFields.Length - 1) + "\n";
                Resu += string.Format(FmtFields, "Champ", "Nom", "Primary", "Type Num", "Type MySql", "Flags", "Nullable",
                                                 "Chars", "Digits", "Dec.", "Long", "Null", "Nullable", "Long.Fixe");
                foreach (Field FD in Table.Fields)
                {
                    Resu += string.Format(FmtFields,
                        FD.Position,
                        FD.Name,
                        FD.Name == Table.PrimaryKey,
                        FD.Type,
                        FD.SqlTypeString,
                        FD.Flags,
                        FD.Nullable,
                        FD.MaxChars,
                        FD.Digits,
                        FD.Decimals,
                        FD.IsLong,
                        FD.IsNullable,
                        FD.MayBeNull,
                        FD.FixedLength
                        );
                }

                // Indexes
                if (Table.Indexes.Count > 0)
                {
                    string FmtIndex = "{0}\t{1}\t{2}\t{3}\t{4}\t{5}\t{6}\n";
                    Resu += string.Format(FmtIndex, "Indexes", "Nom", "Champ", "Type", "Primary", "Unique", "AutoUpd");
                    foreach (Index Ix in Table.Indexes)
                    {
                        Resu += string.Format(FmtIndex,
                            Ix.Position, Ix.Name, Ix.Field, Ix.Type, Ix.Primary, Ix.Unique, Ix.AutoUpdate);
                    }
                }


                // Relations
                if (Table.FKeys.Count > 0)
                {
                    string FmtKeys = "{0}\t{1}\t{2}\t{3}\n";
                    Resu += string.Format(FmtKeys, "Relations", "Champ local", "Table étrangère", "Champ étranger");
                    foreach (FKey Key in Table.FKeys)
                        Resu += string.Format(FmtKeys, Key.Position, Key.Name, Key.Table, Key.Column);
                }

                Resu += "\n";
            }

            return Resu;
        }

        //-------------------------------------------------------------------------------------------------------------
        private static int GetIntValue(string StrValue)
        {
            int Result = 0;
            int.TryParse(StrValue, out Result);
            return Result;
        }
    }
}
Afficher la suite