//	Spludlow Software
//	Copyright © Samuel P. Ludlow 2020 All Rights Reserved
//	Distributed under the terms of the GNU General Public License version 3
//	Distributed WITHOUT ANY WARRANTY; without implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE
//	https://www.spludlow.co.uk/LICENCE.TXT
//	The Spludlow logo is a registered trademark of Samuel P. Ludlow and may not be used without permission
//	v1.14

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.IO;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;

//Type TypeDesc
//-32768  Form
//-32766  Macro
//-32764  Reports
//-32761  Module
//-32758  Users
//-32757  Database Document
//-32756  Data Access Pages
//1   Table - Local Access Tables
//2   Access Object - Database
//3   Access Object - Containers
//4   Table - Linked ODBC Tables
//5   Queries
//6   Table - Linked Access Tables
//8   SubDataSheets



//	 The 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine.
//	Microsoft Access Database Engine 2016 Redistributable
//	AccessDatabaseEngine_X64.exe
//	Does not support bigint

//	 Exception has been thrown by the target of an invocation. ---> System.Data.OleDb.OleDbException: The database you are trying to open requires a newer version of Microsoft Access.


//	FIX IDENTIY

namespace Spludlow.Data
{
	/// <summary>
	/// Currently MS Access specific ONLY, not OleDB at all, needs renaming
	/// </summary>
	public class DALOleDb : Spludlow.Data.IDAL
	{
		private OleDbConnection Connection;
		private OleDbTransaction Transaction = null;

		private string ChangedDatabase = null;
		private string CurrentDatabaseCache = null;

		private string CacheKeyPrefix;

		private Dictionary<string, string> ConvertDataTypes;

		private string QuoteOpen = "[";
		private string QuoteClose = "]";

		private List<string> SystemDatabaseNames = new List<string>(new string[]
		{
//			"sys",
		});

		public DALOleDb()
		{
		}
		public DALOleDb(string connectionString)
		{
			this.Initialize(connectionString);
		}
		public void Initialize(string connectionString)
		{
			if (connectionString == null || connectionString.Length == 0)
				return;

			if (connectionString.StartsWith("@") == true)
				connectionString = Spludlow.Config.ConnectionString(connectionString.Substring(1));

			//	Short hand	DATAPROV@FILENAME for access only currently !!!
			if (connectionString.Contains(";") == false)
			{
				string provider = "ACE";
				string filename = null;

				int index = connectionString.IndexOf("@");
				if (index == -1)
				{
					filename = connectionString;
				}
				else
				{
					provider = connectionString.Substring(0, index).Trim();
					filename = connectionString.Substring(index + 1).Trim();
				}

				connectionString = MakeConnectionStringAccess(provider, filename);
			}

			this.Connection = new OleDbConnection(connectionString);

			this.CacheKeyPrefix = "DALOleDb-Schemas-" + this.DataSource() + "-";

			this.ConvertDataTypes = new Dictionary<string, string>();
			string[] convertLookups = new string[]
			{
				"Numeric		Decimal",
			};
			foreach (string convertLookup in convertLookups)
			{
				string[] words = Spludlow.Text.Split(convertLookup, '\t', true, false);
				this.ConvertDataTypes.Add(words[0], words[1]);
			}

			this.ChangeDatabase(null);
		}

		public string Quotes(string commandText)
		{
			return this.Quotes(commandText, "[", "]");
		}
		public string Quotes(string commandText, string replaceOpen, string replaceClose)
		{
			StringBuilder result = new StringBuilder(commandText);
			result.Replace(replaceOpen, this.QuoteOpen);
			result.Replace(replaceClose, this.QuoteClose);
			return result.ToString();
		}

		public static string MakeConnectionStringAccess(string filename)
		{
			return MakeConnectionStringAccess("ACE", filename);
		}
		public static string MakeConnectionStringAccess(string provider, string filename)
		{
			return MakeConnectionStringAccess(provider, filename, "Admin", "");
		}
		public static string MakeConnectionStringAccess(string provider, string filename, string userName, string password)
		{
			string systemDatabaseFilename = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + @"\Microsoft\Access\System.mdw";

			if (File.Exists(systemDatabaseFilename) == false)
				throw new ApplicationException("Make Connection String Access; System database not there use the overload and specify it: " + systemDatabaseFilename);
			//systemDatabaseFilename = null;
			
			return MakeConnectionStringAccess(provider, filename, userName, password, systemDatabaseFilename);
		}
		public static string MakeConnectionStringAccess(string provider, string filename, string userName, string password, string systemDatabasePath)
		{
			if (provider.Length == 3)
				provider = provider.ToUpper();

			//	Microsoft Office 12.0 Access Database Engine OLE DB Provider;	Long format ?

			if (provider == "ACE")
				provider = "Microsoft.ACE.OLEDB.16.0";      //	should word in x86 and x64

			if (provider == "JET")
				provider = "Microsoft.Jet.OLEDB.4.0";       //	only runs from x86 but can open access 2.0 databases

			string[] names = new string[] { "Provider", "Data Source", "User ID", "Password", "Jet OLEDB:System Database" };
			string[] values = new string[] { provider, filename, userName, password, systemDatabasePath };

			return DALCommon.MakeConnectionString(names, values, "'");
		}
		public static string MakeConnectionString(string provider, string dataSource, string database, string userName, string password)
		{
			string[] names = new string[] { "Provider", "Data Source", "Initial catalog", "User ID", "Password" };
			string[] values = new string[] { provider, dataSource, database, userName, password };

			string connectionString = DALCommon.MakeConnectionString(names, values, "'");

			if (userName == null)
				connectionString += "Integrated Security=True;";

			return connectionString.ToString();
		}




		//
		//	Open Close
		//
		private void Open()
		{
			this.Connection.Open();

			if (this.ChangedDatabase != null)
			{
				try
				{
					string database = this.ChangedDatabase;
					if (database == "")
						database = "";		//	!!!!!!!!!!!!!!!

					this.Connection.ChangeDatabase(database);
				}
				catch
				{
					this.Connection.Close();
					throw;
				}
			}
		}
		private void Close()
		{
			this.Connection.Close();
		}

		public string DataSource()
		{
			this.Open();
			try
			{
				return this.Connection.DataSource;
			}
			finally
			{
				this.Close();
			}
		}

		public void CommandTimeout(int minutes)
		{

		}

		//
		//	Current
		//

		public string CurrentDatabase()
		{
			return this.CurrentDatabaseCache;
		}
		public void ChangeDatabase(string databaseName)
		{
			this.ChangedDatabase = databaseName;

			this.Open();
			try
			{
				databaseName = this.Connection.Database;
			}
			finally
			{
				this.Close();
			}

			this.CurrentDatabaseCache = databaseName;
		}

		//
		//	Transactions
		//

		public void Begin()
		{
			if (this.Transaction != null)
				throw new ApplicationException("DAL OleDb: Begin Transaction already present.");

			this.Open();

			try
			{
				this.Transaction = this.Connection.BeginTransaction();
			}
			catch
			{
				this.Close();
				throw;
			}
		}

		public void Commit()
		{
			if (this.Transaction == null)
				throw new ApplicationException("DAL OleDb: Commit Transaction not present.");

			try
			{
				this.Transaction.Commit();
				this.Transaction = null;
			}
			finally
			{
				this.Close();
			}
		}

		public void Rollback()
		{
			if (this.Transaction == null)
				throw new ApplicationException("DAL OleDb: Rollback Transaction not present.");

			try
			{
				this.Transaction.Rollback();
				this.Transaction = null;
			}
			finally
			{
				this.Close();
			}
		}

		//
		//	Schema
		//

		public DataSet Schema(string tableName)
		{
			return this.Schema(new string[] { tableName });
		}

		public DataSet Schema(string[] tableNames)
		{
			DataSet fullSchema = this.Schema();

			DataSet partSchema = new DataSet();

			foreach (string tableName in tableNames)
			{
				foreach (string suffix in new string[] { "_Columns", "_Keys" })
					partSchema.Tables.Add(fullSchema.Tables[tableName + suffix].Copy());
			}

			return partSchema;
		}
		public DataSet Schema()
		{
			string databaseName = this.CurrentDatabase();
			if (databaseName == null)
				throw new ApplicationException("DAL OleDb; To get the schema you must have a current database");

			string key = this.CacheKeyPrefix + databaseName;

			DataSet schema = (DataSet)Spludlow.Caching.Get(key);

			if (schema != null)
				return schema;

			schema = new DataSet();

			DataSet schemaReader = this.SchemaReader();
			DataSet schemaNative = this.SchemaNative();
			DataSet schemaStandard = this.SchemaConnection();

			foreach (string tableName in this.TableList())
			{
				this.SchemaColumns(schema, tableName, schemaReader, schemaNative, schemaStandard);
				this.SchemaKeys(schema, tableName, schemaReader, schemaNative, schemaStandard);
			}

			Spludlow.Caching.Set(key, schema);

			return schema;
		}

		private void SchemaColumns(DataSet schema, string tableName, DataSet schemaReader, DataSet schemaNative, DataSet schemaStandard)
		{
			DataTable schemaTable = Spludlow.Data.Schemas.NewSchemaColumnsTable(tableName);

			foreach (DataRow readerRow in schemaReader.Tables[tableName].Rows)
			{
				string columnName = (string)readerRow["ColumnName"];
				int columnSize = (int)readerRow["ColumnSize"];

				OleDbType dbType = (OleDbType)(int)readerRow["ProviderType"];
				string typeName = dbType.ToString();

				if (this.ConvertDataTypes.ContainsKey(typeName) == true)
					typeName = this.ConvertDataTypes[typeName];

				DataRow schemaRow = schemaTable.NewRow();

				schemaRow["ColumnName"] = columnName;
				schemaRow["Ordinal"] = (int)readerRow["ColumnOrdinal"];
				schemaRow["DataTypeId"] = Spludlow.Data.Schemas.NativeToCommon(typeName, "OleDbType");
				schemaRow["MaxLength"] = columnSize;
				schemaRow["Precision"] = (Int16)readerRow["NumericPrecision"];
				schemaRow["Scale"] = (Int16)readerRow["NumericScale"];
				schemaRow["PrimaryKey"] = (bool)readerRow["IsKey"];
				schemaRow["AllowDBNull"] = (bool)readerRow["AllowDBNull"];
				schemaRow["AutoIncrement"] = (bool)readerRow["IsAutoIncrement"];
				schemaRow["Longest"] = 0;

				schemaTable.Rows.Add(schemaRow);
			}

			Spludlow.Data.Schemas.TidySchemaTable(schemaTable);
			schema.Tables.Add(schemaTable);
		}

		private void SchemaKeys(DataSet schema, string tableName, DataSet schemaReader, DataSet schemaNative, DataSet schemaStandard)
		{
			//	DESC ?

			DataTable keysTable = Spludlow.Data.Schemas.NewSchemaKeysTable(tableName);

			List<string> indexNames = new List<string>();
			
			foreach (DataRow indexRow in schemaStandard.Tables["Indexes"].Select("TABLE_NAME = '" + tableName + "'"))
			{
				string name = (string)indexRow["INDEX_NAME"];
				if (indexNames.Contains(name) == false)
					indexNames.Add(name);
			}

			foreach (string indexName in indexNames)
			{
				DataRow[] indexRows = schemaStandard.Tables["Indexes"].Select("TABLE_NAME = '" + tableName + "' AND INDEX_NAME = '" + indexName + "'", "ORDINAL_POSITION");

				string keyType = "I";

				if ((bool)indexRows[0]["PRIMARY_KEY"] == true)
				{
					keyType = "P";
				}
				else
				{
					if ((bool)indexRows[0]["UNIQUE"] == true)
						keyType = "U";
				}

				string relationshipTemplate = @"szColumn = '@ColumnName' AND szObject = '@TableName' AND szRelationship = '@IndexName'";
				relationshipTemplate = relationshipTemplate.Replace("@TableName", tableName);
				relationshipTemplate = relationshipTemplate.Replace("@IndexName", indexName);

				foreach (DataRow indexRow in indexRows)
				{
					int ordinal = (int)(Int64)indexRow["ORDINAL_POSITION"];
					string columnName = (string)indexRow["COLUMN_NAME"];

					string relationshipText = relationshipTemplate.Replace("@ColumnName", columnName);

					DataRow[] rows = schemaNative.Tables["MSysRelationships"].Select(relationshipText);

					if (rows.Length > 1)
						throw new ApplicationException("DAL OleDb SchemaKeys; More that one row found in MSysRelationships, indexName:" + indexName);

					string parentTableName = null;
					string parentColumnName = null;

					if (rows.Length == 1)
					{
						keyType = "F";
						parentTableName = (string)rows[0]["szReferencedObject"];
						parentColumnName = (string)rows[0]["szReferencedColumn"];
					}

					keysTable.Rows.Add(new object[] { tableName + "_" + indexName, keyType, ordinal, false, columnName, parentTableName, parentColumnName });
				}
			}


			schema.Tables.Add(keysTable);
		}

		public DataSet SchemaNative()
		{
			string databaseName = this.CurrentDatabase();
			if (databaseName == null)
				throw new ApplicationException("DAL OleDb; To get the Native schema you must have a current database");

			DataSet dataSet = new DataSet();

			//this.Connection.GetOleDbSchemaTable()

			string[] names = new string[]
			{
				"MSysObjects",
				//"MSysACEs",
				//"MSysQueries",
				"MSysRelationships",
				//"MSysComplexColumns",
				//"MSysAccessStorage",
				//"MSysNameMap",
				//"MSysNavPaneGroupCategories",
				//"MSysNavPaneGroups",
				//"MSysNavPaneGroupToObjects",
				//"MSysNavPaneObjectIDs",
				//"MSysResources",
			};

			foreach (string name in names)
			{
				this.Fill(dataSet, "SELECT * FROM " + name);
				Spludlow.Data.ADO.NameLastTable(dataSet, name);
			}

			return dataSet;
		}

		public DataSet SchemaReader()
		{
			string[] tableNames = this.TableList();

			DataSet schema;

			if (this.Transaction == null)
				this.Open();
			try
			{
				schema = Spludlow.Data.DALCommon.SchemaReader(this.Connection, this.Transaction, tableNames, this.QuoteOpen, this.QuoteClose);
			}
			finally
			{
				if (this.Transaction == null)
					this.Close();
			}

			return schema;
		}

		public DataSet SchemaConnection()
		{
			string databaseName = this.CurrentDatabase();
			if (databaseName == null)
				throw new ApplicationException("DAL OleDb; To get the Standard schema you must have a current database");

			DataSet schema;

			if (this.Transaction == null)
				this.Open();
			try
			{
				schema = Spludlow.Data.DALCommon.SchemaConnection(this.Connection);	//, "Schema", "public");
			}
			finally
			{
				if (this.Transaction == null)
					this.Close();
			}

			//DataTable table;

			//table = schema.Tables["Columns"];
			//table.PrimaryKey = new DataColumn[] { table.Columns["TABLE_NAME"], table.Columns["COLUMN_NAME"] };

			//table = schema.Tables["PrimaryKeys"];
			//table.PrimaryKey = new DataColumn[] { table.Columns["CONSTRAINT_NAME"] };

			//table = schema.Tables["UniqueKeys"];
			//table.PrimaryKey = new DataColumn[] { table.Columns["INDEX_NAME"] };

			return schema;
		}

		public void SchemaRefresh()
		{
			string databaseName = this.CurrentDatabase();
			if (databaseName == null)
				throw new ApplicationException("DAL OleDb; To Refresh the schema you must have a current database");

			string key = this.CacheKeyPrefix + databaseName;

			Spludlow.Caching.Remove(key);
		}

		//
		//	Tables
		//
		public string[] TableList()
		{
			string database = this.CurrentDatabase();
			if (database == null)
				throw new ApplicationException("DAL OleDb; To list tables you must have a current database");

			DataTable table = this.Select("SELECT Name FROM MSysObjects WHERE ((Type = 1 OR Type = 4 OR Type = 6) AND Flags >= 0);");

			List<string> tableNames = new List<string>();
			foreach (DataRow row in table.Rows)
			{
				string tableName = (string)row[0];

				if (tableName.StartsWith("MSys") == true)
					continue;

				tableNames.Add(tableName);
			}

			return tableNames.ToArray();
		}

		//
		//	Command
		//

		private void SetDataType(DAL.CommandInfo info, DataRow columnRow, int index)
		{
			string dataTypeId = (string)columnRow["DataTypeId"];
			string nativeType = Spludlow.Data.Schemas.CommonToNative(dataTypeId, "OleDbType");

			OleDbType dbType = (OleDbType)Enum.Parse(typeof(OleDbType), nativeType);

			info.ParameterTypes[index] = (int)dbType;
			info.ParameterMaxLengths[index] = (int)columnRow["MaxLength"];
		}

		public DAL.CommandInfo MakeCommandInfo(string commandText)
		{
			return MakeCommandInfo(commandText, DALCommon.ExtractParameterNames(commandText));
		}

		public DAL.CommandInfo MakeCommandInfo(string commandText, string[] parameterNames)
		{
			return Spludlow.Data.DALCommon.MakeCommandInfo(commandText, parameterNames, this.Schema(), this.SetDataType);
		}

		private OleDbCommand MakeCommandNative(string commandText)
		{
			OleDbCommand command = new OleDbCommand(commandText, this.Connection, this.Transaction);

			//	The time in seconds to wait for the command to execute. The default is 30 seconds.
			//command.CommandTimeout = 20 * 60;
			return command;
		}

		public DbCommand MakeCommand(string commandText)
		{
			return this.MakeCommandNative(commandText);
		}

		public DbCommand MakeCommand(string commandText, object[] parameterValues)
		{
			DAL.CommandInfo commandInfo = this.MakeCommandInfo(commandText);
			return this.MakeCommand(commandInfo, parameterValues);
		}

		public DbCommand MakeCommand(string commandText, string[] parameterNames, object[] parameterValues)
		{
			DAL.CommandInfo commandInfo = this.MakeCommandInfo(commandText, parameterNames);
			return this.MakeCommand(commandInfo, parameterValues);
		}

		public DbCommand MakeCommand(DAL.CommandInfo commandInfo, object[] parameterValues)
		{
			if (commandInfo.ParameterNames.Length != parameterValues.Length)
				throw new ApplicationException("Making Command with different parameterNames and parameterValues counts: " + commandInfo.ParameterNames.Length + ", " + parameterValues.Length);

			OleDbCommand command = this.MakeCommandNative(commandInfo.CommandText);

			for (int index = 0; index < commandInfo.ParameterNames.Length; ++index)
			{
				string paramterName = commandInfo.ParameterNames[index];

				OleDbType dbType = (OleDbType)commandInfo.ParameterTypes[index];
				int maxLength = commandInfo.ParameterMaxLengths[index];

				OleDbParameter parameter = command.CreateParameter();

				parameter.ParameterName = paramterName;

				if ((int)dbType != -1)
					parameter.OleDbType = dbType;

				parameter.Value = parameterValues[index];

				command.Parameters.Add(parameter);
			}

			return command;
		}

		//
		//	ExecuteScalar
		//
		public object ExecuteScalar(string commandText)
		{
			using (OleDbCommand command = (OleDbCommand)this.MakeCommand(commandText))
				return this.ExecuteScalar(command);
		}
		public object ExecuteScalar(string commandText, object[] parameterValues)
		{
			using (OleDbCommand command = (OleDbCommand)this.MakeCommand(commandText, parameterValues))
				return this.ExecuteScalar(command);
		}
		public object ExecuteScalar(string commandText, string[] parameterNames, object[] parameterValues)
		{
			using (OleDbCommand command = (OleDbCommand)this.MakeCommand(commandText, parameterNames, parameterValues))
				return this.ExecuteScalar(command);
		}
		public object ExecuteScalar(DAL.CommandInfo commandInfo, object[] parameterValues)
		{
			using (OleDbCommand command = (OleDbCommand)this.MakeCommand(commandInfo, parameterValues))
				return this.ExecuteScalar(command);
		}
		public object ExecuteScalar(DbCommand command)
		{
			return this.ExecuteScalar((OleDbCommand)command);
		}

		private object ExecuteScalar(OleDbCommand command)
		{
			if (this.Transaction != null)
				return command.ExecuteScalar();

			this.Open();
			try
			{
				return command.ExecuteScalar();
			}
			finally
			{
				this.Close();
			}
		}

		//
		//	ExecuteNonQuery
		//
		public int ExecuteNonQuery(string commandText)
		{
			using (OleDbCommand command = (OleDbCommand)this.MakeCommand(commandText))
				return this.ExecuteNonQuery(command);
		}
		public int ExecuteNonQuery(string commandText, object[] parameterValues)
		{
			using (OleDbCommand command = (OleDbCommand)this.MakeCommand(commandText, parameterValues))
				return this.ExecuteNonQuery(command);
		}
		public int ExecuteNonQuery(string commandText, string[] parameterNames, object[] parameterValues)
		{
			using (OleDbCommand command = (OleDbCommand)this.MakeCommand(commandText, parameterNames, parameterValues))
				return this.ExecuteNonQuery(command);
		}
		public int ExecuteNonQuery(DAL.CommandInfo commandInfo, object[] parameterValues)
		{
			using (OleDbCommand command = (OleDbCommand)this.MakeCommand(commandInfo, parameterValues))
				return this.ExecuteNonQuery(command);
		}
		public int ExecuteNonQuery(DbCommand command)
		{
			return this.ExecuteNonQuery((OleDbCommand)command);
		}

		private int ExecuteNonQuery(OleDbCommand command)
		{
			if (this.Transaction != null)
				return command.ExecuteNonQuery();

			this.Open();
			try
			{
				return command.ExecuteNonQuery();
			}
			finally
			{
				this.Close();
			}
		}

		//
		//	Fill
		//

		private void ExecuteAdapter(OleDbCommand command, DataSet dataSet, DataTable table)
		{
			if (this.Transaction == null)
				this.Open();

			try
			{
				using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
				{
					if (dataSet != null)
						adapter.Fill(dataSet);

					if (table != null)
						adapter.Fill(table);
				}
			}
			finally
			{
				if (this.Transaction == null)
					this.Close();
			}
		}

		public void Fill(DataSet dataSet, string commandText)
		{
			using (OleDbCommand command = (OleDbCommand)this.MakeCommand(commandText))
				this.Fill(dataSet, command);
		}
		public void Fill(DataSet dataSet, string commandText, object[] parameterValues)
		{
			using (OleDbCommand command = (OleDbCommand)this.MakeCommand(commandText, parameterValues))
				this.Fill(dataSet, command);
		}
		public void Fill(DataSet dataSet, string commandText, string[] parameterNames, object[] parameterValues)
		{
			using (OleDbCommand command = (OleDbCommand)this.MakeCommand(commandText, parameterNames, parameterValues))
				this.Fill(dataSet, command);
		}
		public void Fill(DataSet dataSet, DAL.CommandInfo commandInfo, object[] parameterValues)
		{
			using (OleDbCommand command = (OleDbCommand)this.MakeCommand(commandInfo, parameterValues))
				this.Fill(dataSet, command);
		}
		public void Fill(DataSet dataSet, DbCommand command)
		{
			this.Fill(dataSet, (OleDbCommand)command);
		}

		private void Fill(DataSet dataSet, OleDbCommand command)
		{
			this.ExecuteAdapter(command, dataSet, null);
		}

		public void Fill(DataTable table, string commandText)
		{
			using (OleDbCommand command = (OleDbCommand)this.MakeCommand(commandText))
				this.Fill(table, command);
		}
		public void Fill(DataTable table, string commandText, object[] parameterValues)
		{
			using (OleDbCommand command = (OleDbCommand)this.MakeCommand(commandText, parameterValues))
				this.Fill(table, command);
		}
		public void Fill(DataTable table, string commandText, string[] parameterNames, object[] parameterValues)
		{
			using (OleDbCommand command = (OleDbCommand)this.MakeCommand(commandText, parameterNames, parameterValues))
				this.Fill(table, command);
		}
		public void Fill(DataTable table, DAL.CommandInfo commandInfo, object[] parameterValues)
		{
			using (OleDbCommand command = (OleDbCommand)this.MakeCommand(commandInfo, parameterValues))
				this.Fill(table, command);
		}
		public void Fill(DataTable table, DbCommand command)
		{
			this.Fill(table, (OleDbCommand)command);
		}

		private void Fill(DataTable table, OleDbCommand command)
		{
			this.ExecuteAdapter(command, null, table);
		}

		//
		//	Select
		//

		public DataTable Select(string commandText)
		{
			using (OleDbCommand command = (OleDbCommand)this.MakeCommand(commandText))
				return this.Select(command);
		}
		public DataTable Select(string commandText, object[] parameterValues)
		{
			using (OleDbCommand command = (OleDbCommand)this.MakeCommand(commandText, parameterValues))
				return this.Select(command);
		}
		public DataTable Select(string commandText, string[] parameterNames, object[] parameterValues)
		{
			using (OleDbCommand command = (OleDbCommand)this.MakeCommand(commandText, parameterNames, parameterValues))
				return this.Select(command);
		}
		public DataTable Select(DAL.CommandInfo commandInfo, object[] parameterValues)
		{
			using (OleDbCommand command = (OleDbCommand)this.MakeCommand(commandInfo, parameterValues))
				return this.Select(command);
		}
		public DataTable Select(DbCommand command)
		{
			return this.Select((OleDbCommand)command);
		}

		private DataTable Select(OleDbCommand command)
		{
			DataSet dataSet = this.SelectDS(command);
			DataTable table = dataSet.Tables[0];
			dataSet.Tables.Remove(table);
			return table;
		}


		public DataSet SelectDS(string commandText)
		{
			using (OleDbCommand command = (OleDbCommand)this.MakeCommand(commandText))
				return this.SelectDS(command);
		}
		public DataSet SelectDS(string commandText, object[] parameterValues)
		{
			using (OleDbCommand command = (OleDbCommand)this.MakeCommand(commandText, parameterValues))
				return this.SelectDS(command);
		}
		public DataSet SelectDS(string commandText, string[] parameterNames, object[] parameterValues)
		{
			using (OleDbCommand command = (OleDbCommand)this.MakeCommand(commandText, parameterNames, parameterValues))
				return this.SelectDS(command);
		}
		public DataSet SelectDS(DAL.CommandInfo commandInfo, object[] parameterValues)
		{
			using (OleDbCommand command = (OleDbCommand)this.MakeCommand(commandInfo, parameterValues))
				return this.SelectDS(command);
		}
		public DataSet SelectDS(DbCommand command)
		{
			return this.SelectDS((OleDbCommand)command);
		}
		private DataSet SelectDS(OleDbCommand command)
		{
			DataSet dataSet = new DataSet();
			this.ExecuteAdapter(command, dataSet, null);
			return dataSet;
		}















		public void Backup(string serverFilename)
		{
			throw new NotImplementedException();
		}



		public void DatabaseCreate(string databaseName)
		{
			throw new NotImplementedException();
		}

		public void DatabaseDelete(string databaseName)
		{
			throw new NotImplementedException();
		}

		public bool DatabaseExists(string databaseName)
		{
			throw new NotImplementedException();
		}

		public string[] DatabaseList()
		{
			throw new NotImplementedException();
		}



		public DAL.CommandInfo MakeInserter(string tableName)
		{
			return DALCommon.MakeInserter(this, tableName, "");	// ; SELECT @@IDENTITY");
		}

		public long Insert(DataRow row)
		{
			return Insert(row.Table.TableName, row);
		}

		public long Insert(string tableName, object[] row)
		{
			DAL.CommandInfo insertInfo = this.MakeInserter(tableName);
			return Insert(insertInfo, row);
		}

		public long Insert(DAL.CommandInfo insertInfo, DataRow row)
		{
			object[] objectRow = DALCommon.ObjectsFromRow(row, insertInfo);
			return Insert(insertInfo, objectRow);
		}

		public long Insert(string tableName, DataRow row)
		{
			DAL.CommandInfo insertInfo = this.MakeInserter(tableName);
			return Insert(insertInfo, row);
		}

		public long Insert(DAL.CommandInfo insertInfo, object[] row)
		{
			using (OleDbCommand command = (OleDbCommand)this.MakeCommand(insertInfo, row))
			{
				if (insertInfo.Identity == false)
				{
					this.ExecuteNonQuery(command);
					return 0;
				}
				else
				{
					object obj = this.ExecuteScalar(command);

					return 0;
					//if (obj == null)
					//	throw new ApplicationException("DAL Insert; Identity object is null.");

					//if (obj is UInt64)
					//	return (long)((UInt64)obj);

					//throw new ApplicationException("DAL Insert; Identity object bad type:\t" + obj.GetType().Name);
				}
			}
		}





		public long InsertOrUpdate(DataRow row)
		{
			throw new NotImplementedException();
		}

		public long InsertOrUpdate(string tableName, DataRow row)
		{
			throw new NotImplementedException();
		}

		public void LoginCreate(string loginName, bool administrator)
		{
			throw new NotImplementedException();
		}

		public void LoginCreate(string loginName, string password, bool administrator)
		{
			throw new NotImplementedException();
		}

		public void LoginDelete(string loginName)
		{
			throw new NotImplementedException();
		}

		public bool LoginExists(string loginName)
		{
			throw new NotImplementedException();
		}

		public string[] LoginList()
		{
			throw new NotImplementedException();
		}




		public DAL.CommandInfo MakeUpdater(string tableName)
		{
			throw new NotImplementedException();
		}

		public DAL.CommandInfo MakeUpdater(string tableName, string[] updateColumns)
		{
			return DALCommon.MakeUpdater(this, this.Schema(), tableName, updateColumns);
		}

		public void Operation(string details)
		{
			throw new NotImplementedException();
		}

		public string PageCommandText(string commandText, int pageIndex, int pageSize)
		{
			throw new NotImplementedException();
		}

		public void Restore(string serverFilename)
		{
			throw new NotImplementedException();
		}



		public string RunScript(string localFilename, string server, string database)
		{
			throw new NotImplementedException();
		}

		public string RunScript(string localFilename, string server, string database, string userName, string password)
		{
			throw new NotImplementedException();
		}





		public void TableAddKeys(DataSet schema, string keyTypesPUIF)
		{
			string[] tableNames = Spludlow.Data.Schemas.TableNamesInOrder(schema);

			this.TableAddKeys(tableNames, schema, keyTypesPUIF);
		}

		public void TableAddKeys(string tableName, DataSet schema, string keyTypesPUIF)
		{
			this.TableAddKeys(new string[] { tableName }, schema, keyTypesPUIF);
		}

		public void TableAddKeys(string[] tableNames, DataSet schema, string keyTypesPUIF)
		{
			string commandTextPrimary = "ALTER TABLE [@TableName] ADD CONSTRAINT [@KeyName] PRIMARY KEY (@Columns)";
			string commandTextUnique = "CREATE UNIQUE INDEX [@KeyName] ON [@TableName] (@Columns)";
			string commandTextIndex = "CREATE INDEX [@KeyName] ON [@TableName] (@Columns)";
			string commandTextForeign = "ALTER TABLE [@TableName] ADD CONSTRAINT [@KeyName] FOREIGN KEY (@Columns) REFERENCES [@ParentTable] (@ParentColumns)";

			foreach (string commandText in DALCommon.TableAddKeysCommandText(tableNames, schema, keyTypesPUIF, commandTextPrimary, commandTextUnique, commandTextIndex, commandTextForeign, this.QuoteOpen, this.QuoteClose))
				this.ExecuteNonQuery(commandText);
		}



		public void TableClear(string tableName)
		{
			throw new NotImplementedException();
		}

		public void TableCreate(string tableName, DataSet schema)
		{
			this.TableCreate(tableName, schema.Tables[tableName + "_Columns"], schema.Tables[tableName + "_Keys"]);
		}

		public void TableCreate(string tableName, DataTable schemaColumns, DataTable schemaKeys)
		{
			//string[] schemaTableName = DALCommon.SplitSchemaTableName(tableName);
			//if (schemaTableName[0] != null)
			//{
			//	this.CreateSchema(schemaTableName[0]);
			//	tableName = schemaTableName[0] + "]" + "." + "[" + schemaTableName[1];
			//}

			//string databaseName = this.CurrentDatabase();
			//if (databaseName == null)
			//	throw new ApplicationException("DAL SQL; To create a table you must have a current database");

			Spludlow.Data.DALCommon.CreateTableInfo info = new DALCommon.CreateTableInfo();

			info.DatabaseType = "MsAccess";

			info.QuoteOpen = this.QuoteOpen;
			info.QuoteClose = this.QuoteClose;

			info.AllowNull = "NULL";
			info.DontAllowNull = "NOT NULL";

			info.Identity = "IDENTITY";

			info.ColumnLine = "@ColumnName @DataType @Identity @Null";
			info.PrimaryLine = "CONSTRAINT @KeyName PRIMARY KEY (@Columns)";
			info.UniqueLine = null;     //	"CONSTRAINT @KeyName UNIQUE (@Columns)";
			info.ForeignLine = null;    //	"CONSTRAINT @KeyName FOREIGN KEY (@Columns) REFERENCES @ParentTable(@ParentColumns)";
			info.IndexLine = null;      //	"INDEX @KeyName NONCLUSTERED (@Columns)";

			//	Fix for access
			foreach (DataRow columnRow in schemaColumns.Rows)
			{
				string dataTypeId = (string)columnRow["DataTypeId"];

				if (dataTypeId == "NVarChar")
				{
					int maxLength = (int)columnRow["MaxLength"];
					int longest = (int)columnRow["Longest"];

					int length = Math.Max(maxLength, longest);

					if (length > 255)
					{
						//columnRow["MaxLength"] = 255;
						columnRow["MaxLength"] = -1;
						columnRow["DataTypeId"] = "NText";
					}
				}
			}

			string commandText = Spludlow.Data.DALCommon.MakeCreateTableCommandText(tableName, schemaColumns, schemaKeys, info);

			Spludlow.Log.Report("commandText", commandText);

			this.ExecuteNonQuery(commandText);
		}

		public void TableDelete(string tableName)
		{
			throw new NotImplementedException();
		}

		public bool TableExists(string tableName)
		{
			throw new NotImplementedException();
		}



		public int Update(DataRow row)
		{
			return this.Update(row.Table.TableName, row);
		}

		public int Update(DAL.CommandInfo info, object[] row)
		{
			using (OleDbCommand command = (OleDbCommand)this.MakeCommand(info, row))
			{
				//Spludlow.Log.Warning(command.CommandText + " # " + command.Parameters[0].ParameterName + " # " + command.Parameters[0].DbType + " # " + (int)command.Parameters[0].Value);  //prop// row[0].GetType().Name + " # " + row[1].GetType().Name);
				//Spludlow.Log.Warning(command.CommandText + " # " + command.Parameters[1].ParameterName + " # " + command.Parameters[1].DbType + " # " + (string)command.Parameters[1].Value);  //prop// row[0].GetType().Name + " # " + row[1].GetType().Name);


				return this.ExecuteNonQuery(command);
			}
		}

		public int Update(DAL.CommandInfo info, DataRow row)
		{
			throw new NotImplementedException();
		}

		public int Update(string tableName, object[] row)
		{
			throw new NotImplementedException();
		}

		public int Update(string tableName, DataRow row)
		{
			throw new NotImplementedException();
		}

		public int Update(string tableName, string[] columns, object[] row)
		{
			DAL.CommandInfo updateInfo = this.MakeUpdater(tableName, columns);

			for (int index = 0; index < updateInfo.ParameterNames.Length; ++index)
				updateInfo.ParameterNames[index] = "?";

			return this.Update(updateInfo, row);
		}

		public int Update(string tableName, string[] columns, DataRow row)
		{
			throw new NotImplementedException();
		}

		public void UserCreate(string userName, string loginName, bool administrator)
		{
			throw new NotImplementedException();
		}

		public void UserDelete(string userName)
		{
			throw new NotImplementedException();
		}

		public bool UserExists(string userName)
		{
			throw new NotImplementedException();
		}

		public string[] UserList()
		{
			throw new NotImplementedException();
		}

		public void BulkInsert(DataTable table, string tableName, string tempDirectory)
		{
			DAL.CommandInfo insertInfo = this.MakeInserter(tableName);

			this.Begin();

			try
			{
				foreach (DataRow row in table.Rows)
					this.Insert(insertInfo, row);

				this.Commit();
			}
			catch
			{
				this.Rollback();
				throw;
			}
		}

		public DataSet SchemaANSI()
		{
			throw new NotImplementedException();
		}

		public void DatabaseCreate(string databaseName, DataSet schema)
		{
			throw new NotImplementedException();
		}
	}
}