Scripting Microsoft Access

using System;
using System.Collections.Generic;
using System.Data.OleDb;
using System.IO;

namespace AccessScripter
{
    internal static class Program
    {
        private static IDictionary<string, string> providers = new Dictionary<string, string>
        {
            { ".mdb", "Microsoft.Jet.OLEDB.4.0" },
            { ".accdb", "Microsoft.ACE.OLEDB.12.0" }
        };

        public static void Main(string[] args)
        {
            try
            {
                if (args.Length < 1 || args.Length > 2)
                {
                    Console.Error.WriteLine("Usage: {0} DATABASE [SCRIPT]", Environment.GetCommandLineArgs()[0]);
                    Environment.Exit(1);
                }
                string connectionString = GetConnectionString(args[0]);
                string script = args.Length == 2 ? File.ReadAllText(args[1]) : Console.In.ReadToEnd();
                using (OleDbConnection connection = new OleDbConnection(connectionString))
                {
                    connection.Open();
                    foreach (string sql in script.Split(';'))
                    {
                        if (string.IsNullOrWhiteSpace(sql))
                        {
                            continue;
                        }
                        Console.Error.WriteLine();
                        Console.Error.WriteLine(sql.Trim());
                        try
                        {
                            using (OleDbCommand command = new OleDbCommand(sql, connection))
                            {
                                int count = command.ExecuteNonQuery();
                                Console.Error.WriteLine("{0} record(s) affected.", count);
                            }
                        }
                        catch (Exception ex)
                        {
                            Console.Error.WriteLine("ERROR: {0}", ex.Message);
                            Console.Error.Write("Continue? ");
                            ConsoleKey key = Console.ReadKey(true).Key;
                            Console.Error.WriteLine();
                            if (key != ConsoleKey.Y)
                            {
                                break;
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.Error.WriteLine(ex);
            }
        }

        private static string GetConnectionString(string dataSource)
        {
            FileInfo file = new FileInfo(dataSource);
            OleDbConnectionStringBuilder connectionString = new OleDbConnectionStringBuilder();
            string provider;
            if (!providers.TryGetValue(file.Extension.ToLower(), out provider))
            {
                throw new ArgumentException(string.Format("Unrecognized file extension '{0}'.", file.Extension));
            }
            connectionString.Provider = provider;
            connectionString.DataSource = dataSource;
            return connectionString.ConnectionString;
        }
    }
}

Leave a Reply

Your email address will not be published.