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

namespace EQTrack
{
	/// <summary>
	/// Summary description for AccessDatabase.
	/// </summary>
	public class AccessDatabase
	{
		public static bool IsOK = false;
		public static string DatabaseErrorMessage = "";
		public static string LastSQLString = "";
		public static OleDbConnection DatabaseConnection = null;

		public static bool OpenDatabase() {
			DatabaseConnection = new OleDbConnection(EQTrack.Properties.Settings.Default.CustomerDBConnectionString);
			try {
				DatabaseConnection.Open();
				DatabaseErrorMessage = "";
				return true;
			}
			catch (Exception e)
			{
				DatabaseErrorMessage = e.Message;
				DatabaseConnection = null;
			}
			return false;
		}

		public static void CloseDatabase() {
			if (DatabaseConnection != null) {
				DatabaseConnection.Close();
				DatabaseConnection.Dispose();
				DatabaseConnection = null;
			}
		}

		public static bool ExecuteSQLCommand(String SQLString) {
			if (DatabaseConnection == null && !OpenDatabase())
				return false;

			try {
				OleDbCommand Command = DatabaseConnection.CreateCommand();
				Command.CommandText = SQLString;
				LastSQLString = SQLString;
				Command.ExecuteNonQuery();
				Command.Dispose();
				DatabaseErrorMessage = "";
			} catch (Exception e) {
				DatabaseErrorMessage = e.Message;
				return false;
			}
			return true;
		}

		public static object ExecuteScalar(String SQLString) {
			if (DatabaseConnection == null && !OpenDatabase())
				return false;

			object ReturnObject = null;
			try {
				OleDbCommand Command = DatabaseConnection.CreateCommand();
				Command.CommandText = SQLString;
				LastSQLString = SQLString;
				ReturnObject = Command.ExecuteScalar();
				Command.Dispose();
				DatabaseErrorMessage = "";
			} catch (Exception e) {
				DatabaseErrorMessage = e.Message;
			}
			if (ReturnObject != null && ReturnObject.GetType().Equals(typeof(DBNull)))
				ReturnObject = null;
			return ReturnObject;
		}

		public static OleDbDataReader GetDataReader(String SQLString) {
			if (DatabaseConnection == null && !OpenDatabase())
				return null;

			OleDbDataReader DBDataReader = null;
			OleDbCommand Command = DatabaseConnection.CreateCommand();
			Command.CommandText = SQLString;
			LastSQLString = SQLString;
			try
			{
				DBDataReader = Command.ExecuteReader();
				DatabaseErrorMessage = "";
			} catch (Exception e) {
				DBDataReader = null;
				DatabaseErrorMessage = e.Message;
			}
			return DBDataReader;
		}

		public static DataTable GetDataTable(String SQLString) {
			if (DatabaseConnection == null && !OpenDatabase())
				return null;

			DataTable DBDataTable = null;
			OleDbCommand Command = DatabaseConnection.CreateCommand();
			Command.CommandText = SQLString;
			LastSQLString = SQLString;
			try
			{
				if (SQLString.ToUpper().IndexOf("CREATE") == 0 || SQLString.ToUpper().IndexOf("INSERT") == 0 || SQLString.ToUpper().IndexOf("UPDATE") == 0) {
					Command.ExecuteNonQuery();
				} else {
					DBDataTable = new DataTable();
					OleDbDataAdapter myAdapter = new OleDbDataAdapter();
					myAdapter.SelectCommand = Command;
					myAdapter.Fill(DBDataTable);
					myAdapter.Dispose();
				}
				Command.Dispose();
				DatabaseErrorMessage = "";
			} catch (Exception e) {
				if (DBDataTable != null)
					DBDataTable.Dispose();
				DBDataTable = null;
				DatabaseErrorMessage = e.Message;
			}
			return DBDataTable;
		}

		public static bool FillDataTable(DataTable ThisTable, String SQLString) {
			if (DatabaseConnection == null && !OpenDatabase())
				return false;

			OleDbCommand Command = DatabaseConnection.CreateCommand();
			Command.CommandText = SQLString;
			LastSQLString = SQLString;
			try
			{
				if (SQLString.ToUpper().IndexOf("CREATE") == 0 || SQLString.ToUpper().IndexOf("INSERT") == 0 || SQLString.ToUpper().IndexOf("UPDATE") == 0) {
					Command.ExecuteNonQuery();
				} else {
					OleDbDataAdapter myAdapter = new OleDbDataAdapter();
					myAdapter.SelectCommand = Command;
					ThisTable.Clear();
					myAdapter.Fill(ThisTable);
					myAdapter.Dispose();
				}
				Command.Dispose();
				DatabaseErrorMessage = "";
			} catch (Exception e) {
				DatabaseErrorMessage = e.Message;
				return false;
			}
			return true;
		}
	}
}
