{"id":156,"date":"2015-05-11T19:14:25","date_gmt":"2015-05-11T18:14:25","guid":{"rendered":"http:\/\/sizious.com\/fr\/?p=156"},"modified":"2015-05-11T19:14:48","modified_gmt":"2015-05-11T18:14:48","slug":"comment-charger-une-base-de-donnees-access-dans-un-objet-dataset-en-c","status":"publish","type":"post","link":"https:\/\/sizious.com\/fr\/2015\/05\/11\/comment-charger-une-base-de-donnees-access-dans-un-objet-dataset-en-c\/","title":{"rendered":"Comment charger une base de donn\u00e9es Access dans un objet DataSet en C#"},"content":{"rendered":"

Aujourd’hui j’ai eu besoin de charger un fichier Microsoft Access<\/strong> (*.ACCDB\/*.MDB) dans un objet DataSet<\/span>\u00a0en utilisant C# au sein de la plateforme .NET.<\/p>\n

Apr\u00e8s avoir trouv\u00e9 diverses solutions (partielles) j’ai finalement d\u00e9velopp\u00e9 une petite classe C# permettant de le faire tr\u00e8s simplement. Cette classe charge un fichier Access<\/strong> (*.ACCDB ou *.MDB, peu importe) et met le contenu dans un objet DataSet<\/span>. Il ne reste plus qu’\u00e0 utiliser la propri\u00e9t\u00e9 .Tables<\/span>\u00a0 de l’objet DataSet<\/span>…<\/p>\n

\/* \r\n * Microsoft Access Database DataSet Loader for .NET\r\n * Version 20150511\r\n *\r\n * Created by SiZiOUS\r\n * sizious (at) gmail (dot) com - @sizious - www.sizious.com - fb.com\/sizious\r\n *\r\n * Licensed under the WTFPL licence\r\n * See http:\/\/www.wtfpl.net\/txt\/copying\/\r\n *\/\r\nusing System;\r\nusing System.Collections.Generic;\r\nusing System.Data;\r\nusing System.Data.OleDb;\r\nusing System.IO;\r\nusing System.Linq;\r\nusing System.Text;\r\n\r\nnamespace DbLoader\r\n{\r\n    \/\/\/ <summary>\r\n    \/\/\/ Useful utilities for Microsoft Access Database files.\r\n    \/\/\/ <\/summary>\r\n    public static class AccessDbLoader\r\n    {\r\n        \/\/\/ <summary>\r\n        \/\/\/ Loads a Microsoft Access Database file into a DataSet object.\r\n        \/\/\/ The file can be the in the newer ACCDB format or MDB legacy format.\r\n        \/\/\/ <\/summary>\r\n        \/\/\/ <param name=\"fileName\">The file name to load.<\/param>\r\n        \/\/\/ <returns>A DataSet object with the Tables object populated with the contents of the specified Microsoft Access Database.<\/returns>\r\n        public static DataSet LoadFromFile(string fileName)\r\n        {\r\n            DataSet result = new DataSet();\r\n\r\n            \/\/ For convenience, the DataSet is identified by the name of the loaded file (without extension).\r\n            result.DataSetName = Path.GetFileNameWithoutExtension(fileName).Replace(\" \", \"_\");\r\n\r\n            \/\/ Compute the ConnectionString (using the OLEDB v12.0 driver compatible with ACCDB and MDB files)\r\n            fileName = Path.GetFullPath(fileName);            \r\n            string connString = string.Format(\"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};User Id=Admin;Password=\", fileName);\r\n\r\n            \/\/ Opening the Access connection\r\n            using (OleDbConnection conn = new OleDbConnection(connString))\r\n            {\r\n                conn.Open();\r\n\r\n                \/\/ Getting all user tables present in the Access file (Msys* tables are system thus useless for us)\r\n                DataTable dt = conn.GetSchema(\"Tables\");\r\n                List<string> tablesName = dt.AsEnumerable().Select(dr => dr.Field<string>(\"TABLE_NAME\")).Where(dr => !dr.StartsWith(\"MSys\")).ToList();\r\n\r\n                \/\/ Getting the data for every user tables\r\n                foreach (string tableName in tablesName)\r\n                {\r\n                    using (OleDbCommand cmd = new OleDbCommand(string.Format(\"SELECT * FROM [{0}]\", tableName), conn))\r\n                    {\r\n                        using (OleDbDataAdapter adapter = new OleDbDataAdapter(cmd))\r\n                        {\r\n                            \/\/ Saving all tables in our result DataSet.\r\n                            DataTable buf = new DataTable(\"[\"+tableName+\"]\");\r\n                            adapter.Fill(buf);\r\n                            result.Tables.Add(buf);\r\n                        } \/\/ adapter\r\n                    } \/\/ cmd\r\n                } \/\/ tableName\r\n            } \/\/ conn\r\n\r\n            \/\/ Return the filled DataSet\r\n            return result;\r\n        }\r\n    }\r\n}<\/pre>\n
Pour l’utiliser, c’est tr\u00e8s simple :<\/p>\n
    \n
  1. Ajoutez la classe AccessDbLoader<\/span>\u00a0 fournie \u00e0 votre projet.<\/li>\n
  2. Chargez le fichier Access en utilisant le code suivant :
    \nDataSet ds = AccessDbLoader.LoadFromFile(\u00ab\u00a0mon.accdb\u00a0\u00bb);<\/span><\/li>\n
  3. Vous pouvez d\u00e9sormais utiliser la propri\u00e9t\u00e9 ds.Tables<\/span>.<\/li>\n<\/ol>\n
    Pour votre confort vous trouverez ci-dessous un programme exemple :<\/p>\n
    using System;\r\nusing System.Collections.Generic;\r\nusing System.Data;\r\nusing System.Data.Common;\r\nusing System.Data.OleDb;\r\nusing System.IO;\r\nusing System.Linq;\r\nusing System.Text;\r\n\r\nnamespace DbLoader\r\n{\r\n    public class Program\r\n    {\r\n        private static void DumpDataSet(DataSet ds)\r\n        {\r\n            Console.Out.WriteLine(\"DataSet: {0}\", ds.DataSetName);\r\n\r\n            \/\/ For every tables in the DataSet ...\r\n            foreach (DataTable dt in ds.Tables)\r\n            {\r\n                Console.Out.WriteLine(\"\\tTableName: {0}\", dt.TableName);\r\n\r\n                \/\/ ... Write the table schema\r\n                foreach (DataColumn col in dt.Columns)\r\n                {\r\n                    Console.Out.Write(\"\\t\\t\" + col.ColumnName + \" \");\r\n                }\r\n                Console.Out.WriteLine(\"\\t\\t\");\r\n\r\n                \/\/ ... Write the table contents\r\n                foreach (DataRow row in dt.Rows)\r\n                {\r\n                    for (int i = 0; i < dt.Columns.Count; i++)\r\n                    {\r\n                        Console.Out.Write(\"\\t\\t\" + row[i]);\r\n                    }\r\n                    Console.Out.WriteLine(\"\");\r\n                }\r\n            }\r\n        }\r\n\r\n        public static void Main(string[] args)\r\n        {\r\n            \/\/ Load an Access ACCDB file\r\n            DataSet ds1 = AccessDbLoader.LoadFromFile(\"test.accdb\");\r\n            DumpDataSet(ds1);\r\n\r\n            \/\/ Load an Access MDB (legacy) file\r\n            DataSet ds2 = AccessDbLoader.LoadFromFile(\"test.mdb\");\r\n            DumpDataSet(ds2);\r\n\r\n            Console.Out.WriteLine(\"\\nStrike <ENTER> to exit . . .\");\r\n            Console.In.ReadLine();\r\n        }\r\n    }\r\n}<\/pre>\n
    Je suis certain que cette classe peut s\u00fbrement \u00eatre am\u00e9lior\u00e9e (par exemple : meilleure gestion des clefs primaires, etc.) mais en l’\u00e9tat, c’est suffisant pour mes besoins.<\/p>\n","protected":false},"excerpt":{"rendered":"
    Aujourd’hui j’ai eu besoin de charger un fichier Microsoft Access (*.ACCDB\/*.MDB) dans un objet DataSet\u00a0en utilisant C# au sein de la plateforme .NET. Apr\u00e8s avoir trouv\u00e9 diverses solutions (partielles) j’ai finalement d\u00e9velopp\u00e9 une petite classe C# permettant de le faire tr\u00e8s simplement. Cette classe charge un fichier Access (*.ACCDB ou *.MDB, peu importe) et met\u2026 Lire la suite »<\/a><\/span><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[5],"tags":[],"class_list":["post-156","post","type-post","status-publish","format-standard","hentry","category-code"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t