using System;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Runtime.InteropServices;
using System.Text;

namespace JetDatabase
{
    internal class Program
    {
        private static string shapeFileFileName = "../../../App_Data/WorldCapitals.shp";
        private static string sqlStatement = "";

        private static void Main(string[] args)
        {
            MockUpDatabaseOperation();

            Console.ReadKey();
        }

        private static void MockUpDatabaseOperation()
        {
            string shortName = GetShortFileName(Path.ChangeExtension(shapeFileFileName, ".dbf"));
            string shortNameWithoutExtension = Path.GetFileNameWithoutExtension(shortName);
            string sqlStatement = string.Format("Select * from [{0}] ", shortNameWithoutExtension);

            OleDbCommand command = GetOleDbCommand(sqlStatement);
            DataTable dataTable = new DataTable();

            OleDbDataAdapter dataAdapter = new OleDbDataAdapter(command);
            dataAdapter.Fill(dataTable);

            Console.WriteLine(string.Format("Get {0} rows", dataTable.Rows.Count));
        }

        private static OleDbCommand GetOleDbCommand(string sqlStatement)
        {
            OleDbConnection connection;
            if (Environment.Is64BitProcess)
            {
                connection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path.GetDirectoryName(shapeFileFileName) + "\\;Extended Properties=dBASE IV;User ID=Admin;Password=");
            }
            else
            {
                connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path.GetDirectoryName(shapeFileFileName) + "\\;Extended Properties=dBASE IV;User ID=Admin;Password=");
            }

            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }

            return new OleDbCommand(sqlStatement, connection);
        }

        private static string GetShortFileName(string pathFileName)
        {
            StringBuilder sb = new StringBuilder(1024);
            UnsafeNativeMethods.GetShortPathName(pathFileName, sb, sb.Capacity);

            string shortPath = sb.ToString();
            //TODO: whether throw exception if short path is empty

            return shortPath;
        }
    }

    internal static class UnsafeNativeMethods
    {
        //TODo check this method works fine in chinese string.
        [DllImport("kernel32", EntryPoint = "GetShortPathNameA", ExactSpelling = true, CharSet = CharSet.Ansi, SetLastError = true)]
        internal static extern int GetShortPathName(string lpszLongPath, StringBuilder lpszShortPath, int cchBuffer);
    }
}