using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Data.SqlClient;
using MySql.Data.MySqlClient;
using System.Data;
using System.Collections;
using System.Data.OracleClient;
using System.Diagnostics;
using System.Data.SQLite;
using System.Data.Odbc;
using System.IO;
using Npgsql;

namespace SE290A_BackOfficeSim_CoreApi
{
    public enum DBaseType
    {
        CSV = 0,    // CSV - > Not supported by this DOA
        MsSQL = 1,
        MySQL = 2,
        Oracle = 3,
        SQLite = 4,
        PgSQL = 5,
        ODBC = 6,
        DB2 = 7,
    };

    public class DoaSqlConnection : IDisposable
    {
        internal MySqlConnection MySqlConn = null;
        internal SqlConnection MsSqlConn = null;
        internal OracleConnection OrSqlConn = null;
        internal SQLiteConnection SQLiteConn = null;
        internal NpgsqlConnection PgSqlConn = null;
        internal OdbcConnection OdbcConn = null;

        string FolderSqlite = "."; // @"C:\ESL\Kit\Source\WebApp-Full";
        string SqlUserId = "root";


        public DoaSqlConnection(string Server, string Database, string UserId, string Password, string Options, DBaseType Type)
        {
            string ConnectionString;
            switch (Type)
            {
                case DBaseType.MsSQL:
                    try
                    {
                        ConnectionString = "Server=" + Server + ";Database=" + Database + ";UID=" + UserId + ";password=" + Password;

                        if (!string.IsNullOrEmpty(Options) && !Options.Contains("Charset=utf8"))
                            ConnectionString += ";Connection Timeout=120;" + Options + ";";
                        else
                            ConnectionString += ";Connection Timeout=120;";

                        MsSqlConn = new SqlConnection(ConnectionString);
                    }
                    catch (SqlException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    break;

                case DBaseType.MySQL:
                    try
                    {
                        ConnectionString = "Server=" + Server + ";Database=" + Database + ";UID=" + UserId + ";password=" + Password;

                        if (!string.IsNullOrEmpty(Options))
                            ConnectionString += ";" + Options + ";";
                        else
                            ConnectionString += ";";

                        MySqlConn = new MySqlConnection(ConnectionString);
                    }
                    catch (MySqlException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    break;

                case DBaseType.Oracle:
                    try
                    {
                        ConnectionString = "Data Source = " + Database + ";User Id = " + UserId + ";Password = " + Password;

                        if (!string.IsNullOrEmpty(Options) && !Options.Contains("Charset=utf8"))
                            ConnectionString += ";" + Options + ";";
                        else
                            ConnectionString += ";";

                        OrSqlConn = new OracleConnection(ConnectionString);
                    }
                    catch (OracleException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    break;

                case DBaseType.SQLite:
                    try
                    {
                        if (string.IsNullOrEmpty(Path.GetDirectoryName(Database)))
                            Database = Path.Combine(FolderSqlite, Database);

                        if (string.IsNullOrEmpty(Path.GetExtension(Database)))
                            ConnectionString = "Data Source = " + Database + ".sqlite3;Version=3";
                        else
                            ConnectionString = "Data Source = " + Database + ";Version=3";

                        //if (!String.IsNullOrEmpty(Password))
                        //	ConnectionString += ";Password=" + Password;

                        if (!string.IsNullOrEmpty(Options))
                            ConnectionString += ";" + Options + ";";
                        else
                            ConnectionString += ";";

                        SQLiteConn = new SQLiteConnection(ConnectionString);
                    }
                    catch (SQLiteException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    break;

                case DBaseType.PgSQL:
                    try
                    {
                        ConnectionString = "Server=" + Server + ";Database=" + Database + ";UID=" + UserId + ";password=" + Password;

                        if (!string.IsNullOrEmpty(Options) && !Options.Contains("Charset=utf8"))
                            ConnectionString += ";" + Options + ";";
                        else
                            ConnectionString += ";";

                        PgSqlConn = new NpgsqlConnection(ConnectionString);
                    }
                    catch (NpgsqlException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    break;

                case DBaseType.ODBC:
                    try
                    {
                        ConnectionString = "Dsn=" + Database + ";" +
                                            "Uid=" + UserId + ";" +
                                                "Pwd=" + Password;

                        if (!string.IsNullOrEmpty(Options) && !Options.Contains("Charset=utf8"))
                            ConnectionString += ";" + Options + ";";
                        else
                            ConnectionString += ";";

                        OdbcConn = new OdbcConnection(ConnectionString);
                    }
                    catch (OdbcException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    break;
            }
        }

        public DBaseType DbaseType
        {
            get
            {
                if (MySqlConn != null)
                    return DBaseType.MySQL;
                else if (MsSqlConn != null)
                    return DBaseType.MsSQL;
                else if (OrSqlConn != null)
                    return DBaseType.Oracle;
                else if (SQLiteConn != null)
                    return DBaseType.SQLite;
                else if (PgSqlConn != null)
                    return DBaseType.PgSQL;

                if (OdbcConn != null)
                {
                    try
                    {
                        if (OdbcConn.Driver == null)
                            return DBaseType.ODBC;

                        string driver = OdbcConn.Driver.ToUpper();

                        if (driver.StartsWith("MY"))
                            return DBaseType.MySQL;
                        else if (driver.Contains("ORA"))
                            return DBaseType.Oracle;
                        else if (driver.Contains("SQLITE"))
                            return DBaseType.SQLite;
                        else if (driver.Contains("PSQL"))
                            return DBaseType.PgSQL;
                        else if (driver.StartsWith("SQL"))
                            return DBaseType.MsSQL;
                        else if (driver.Contains("DB2"))
                            return DBaseType.DB2;
                        else
                            return DBaseType.ODBC;
                    }
                    catch (OdbcException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                }

                return DBaseType.CSV;
            }
        }


        public void Open()
        {
            if (MySqlConn != null)
            {
                try
                {
                    MySqlConn.Open();
                }
                catch (MySqlException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (MsSqlConn != null)
            {
                try
                {
                    MsSqlConn.Open();
                }
                catch (SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (OrSqlConn != null)
            {
                try
                {
                    OrSqlConn.Open();
                }
                catch (OracleException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (SQLiteConn != null)
            {
                try
                {
                    SQLiteConn.Open();
                }
                catch (SQLiteException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (PgSqlConn != null)
            {
                try
                {
                    PgSqlConn.Open();
                }
                catch (NpgsqlException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (OdbcConn != null)
            {
                try
                {
                    OdbcConn.Open();
                }
                catch (OdbcException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        public void Close()
        {
            if (MySqlConn != null)
            {
                try
                {
                    MySqlConn.Close();
                }
                catch (MySqlException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (MsSqlConn != null)
            {
                try
                {
                    MsSqlConn.Close();
                }
                catch (SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (OrSqlConn != null)
            {
                try
                {
                    OrSqlConn.Close();
                }
                catch (SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (SQLiteConn != null)
            {
                try
                {
                    SQLiteConn.Close();
                }
                catch (SQLiteException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (PgSqlConn != null)
            {
                try
                {
                    PgSqlConn.Close();
                }
                catch (NpgsqlException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (OdbcConn != null)
            {
                try
                {
                    OdbcConn.Close();
                }
                catch (OdbcException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        public DataTable GetSchema(string collectionName)
        {
            if (MySqlConn != null)
            {
                try
                {
                    return MySqlConn.GetSchema(collectionName);
                }
                catch (MySqlException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (MsSqlConn != null)
            {
                try
                {
                    return MsSqlConn.GetSchema(collectionName);
                }
                catch (SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (OrSqlConn != null)
            {
                try
                {
                    return OrSqlConn.GetSchema(collectionName, new string[1] { SqlUserId });
                }
                catch (OracleException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (SQLiteConn != null)
            {
                try
                {
                    return SQLiteConn.GetSchema(collectionName);
                }
                catch (SQLiteException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (PgSqlConn != null)
            {
                try
                {
                    return PgSqlConn.GetSchema(collectionName);
                }
                catch (NpgsqlException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (OdbcConn != null)
            {
                try
                {
                    return OdbcConn.GetSchema(collectionName);
                }
                catch (OdbcException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            return null;
        }

        internal MySqlTransaction MySqlTrans = null;
        internal SqlTransaction MsSqlTrans = null;
        internal OracleTransaction OrSqlTrans = null;
        internal SQLiteTransaction SQLiteTrans = null;
        internal NpgsqlTransaction PgSqlTrans = null;
        internal OdbcTransaction OdbcTrans = null;

        public void BeginTransaction()
        {
            if (MySqlConn != null)
            {
                try
                {
                    MySqlTrans = MySqlConn.BeginTransaction();
                }
                catch (MySqlException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (MsSqlConn != null)
            {
                try
                {

                    MsSqlTrans = MsSqlConn.BeginTransaction();
                }
                catch (SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (OrSqlConn != null)
            {
                try
                {
                    OrSqlTrans = OrSqlConn.BeginTransaction();
                }
                catch (OracleException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (SQLiteConn != null)
            {
                try
                {
                    SQLiteTrans = SQLiteConn.BeginTransaction();
                }
                catch (SQLiteException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (PgSqlConn != null)
            {
                try
                {
                    PgSqlTrans = PgSqlConn.BeginTransaction();
                }
                catch (NpgsqlException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (OdbcConn != null)
            {
                try
                {
                    OdbcTrans = OdbcConn.BeginTransaction();
                }
                catch (OdbcException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        public void Commit()
        {
            if (MySqlTrans != null)
            {
                try
                {
                    MySqlTrans.Commit();
                }
                catch (MySqlException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (MsSqlTrans != null)
            {
                try
                {
                    MsSqlTrans.Commit();
                }
                catch (SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (OrSqlTrans != null)
            {
                try
                {
                    OrSqlTrans.Commit();
                }
                catch (OracleException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (SQLiteTrans != null)
            {
                try
                {
                    SQLiteTrans.Commit();
                }
                catch (SQLiteException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (PgSqlTrans != null)
            {
                try
                {
                    PgSqlTrans.Commit();
                }
                catch (NpgsqlException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (OdbcTrans != null)
            {
                try
                {
                    OdbcTrans.Commit();
                }
                catch (OdbcException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        #region IDisposable

        public void Dispose()
        {
            if (MySqlConn != null)
            {
                MySqlConn.Dispose();
            }
            else if (MsSqlConn != null)
            {
                MsSqlConn.Dispose();
            }
            else if (OrSqlConn != null)
            {
                OrSqlConn.Dispose();
            }
            else if (SQLiteConn != null)
            {
                SQLiteConn.Dispose();
            }
            else if (PgSqlConn != null)
            {
                PgSqlConn.Dispose();
            }
            else if (OdbcConn != null)
            {
                OdbcConn.Dispose();
            }
        }
        #endregion
    }

    public class DoaSqlCommand : IDisposable
    {
        internal DoaSqlConnection SqlConn;
        internal MySqlCommand MySqlCmd = null;
        internal SqlCommand MsSqlCmd = null;
        internal OracleCommand OrSqlCmd = null;
        internal SQLiteCommand SQLiteCmd = null;
        internal NpgsqlCommand PgSqlCmd = null;
        internal OdbcCommand OdbcCmd = null;

        public static string ConvertQueryToType(string Query, DBaseType Type)
        {
            switch (Type)
            {
                case DBaseType.MySQL:
                    bool AutoIncrementId = Query.Contains("IDENTITY(1,1)");

                    if (AutoIncrementId)
                    {
                        Query = Query.Replace("IDENTITY(1,1) PRIMARY KEY", "NOT NULL PRIMARY KEY AUTO_INCREMENT");
                        Query += " AUTO_INCREMENT=1";       // Set seed to 1
                    }

                    Query = Query.Replace("BLOB", "MEDIUMBLOB");
                    Query = Query.Replace('[', '`');
                    Query = Query.Replace(']', '`');
                    Query = Query.Replace("\\", "\\\\");
                    break;

                case DBaseType.MsSQL:
                    Query = Query.Replace("BLOB", "VARBINARY(MAX)");
                    break;

                case DBaseType.Oracle:
                    Query = Query.Replace("BIGINT", "NUMBER(19)");
                    //Query = Query.Replace("BLOB", "RAW");
                    Query = Query.Replace("[DELETE]", "[DEL]");
                    Query = Query.Replace("[", "\"");
                    Query = Query.Replace("]", "\"");
                    Query = Query.Replace("VARCHAR", "VARCHAR2");
                    Query = Query.Replace("DATETIME", "TIMESTAMP(0)");
                    Query = Query.Replace("DECIMAL", "NUMBER");
                    Query = Query.Replace("\" INT", "\" NUMBER(10)");   // prevents changing INSERT INTO as well
                    Query = Query.Replace("IDENTITY(1,1)", "NOT NULL");
                    Query = Query.Replace(" IF EXISTS", "");

                    if (!Query.EndsWith("END;"))
                        Query = Query.TrimEnd(new char[] { '\r', '\n', ';' });
                    break;

                case DBaseType.SQLite:
                    Query = Query.Replace("INT IDENTITY(1,1) PRIMARY KEY", "INTEGER PRIMARY KEY AUTOINCREMENT");
                    Query = Query.Replace("DATETIME", "TEXT");
                    Query = Query.Replace('[', '`');
                    Query = Query.Replace(']', '`');
                    Query = Query.Replace("\\", "\\\\");
                    break;

                case DBaseType.PgSQL:
                    Query = Query.Replace("BLOB", "BYTEA");
                    Query = Query.Replace('[', '"');
                    Query = Query.Replace(']', '"');
                    Query = Query.Replace("\\", "\\\\");
                    Query = Query.Replace("DATETIME", "TIMESTAMP(0)");
                    Query = Query.Replace("INT IDENTITY(1,1)", "SERIAL");
                    break;

                case DBaseType.ODBC:
                    Query = Query.Replace('[', '`');
                    Query = Query.Replace(']', '`');
                    Query = Query.Replace("\\", "\\\\");
                    Query = Query.Replace("DATETIME", "TIMESTAMP(0)");
                    break;

                case DBaseType.DB2:
                    Query = Query.Replace('[', '"');
                    Query = Query.Replace(']', '"');
                    Query = Query.Replace("\\", "\\\\");
                    Query = Query.Replace("DATETIME", "TIMESTAMP(0)");
                    Query = Query.Replace("INT IDENTITY(1,1)", "integer not null GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1)");
                    break;

            }

            return Query;
        }

        public DoaSqlCommand(string Query, DoaSqlConnection SqlConn)
        {
            this.SqlConn = SqlConn;

            Query = ConvertQueryToType(Query, SqlConn.DbaseType);

            if (SqlConn.MySqlConn != null)
            {
                try
                {
                    MySqlCmd = new MySqlCommand(Query, SqlConn.MySqlConn);

                    if (SqlConn.MySqlTrans != null)
                        MySqlCmd.Transaction = SqlConn.MySqlTrans;
                }
                catch (MySqlException ex)
                {
                    Trace.WriteLine(Query);
                    throw new Exception(ex.Message);
                }
            }
            else if (SqlConn.MsSqlConn != null)
            {
                try
                {
                    MsSqlCmd = new SqlCommand(Query, SqlConn.MsSqlConn);

                    if (SqlConn.MsSqlTrans != null)
                        MsSqlCmd.Transaction = SqlConn.MsSqlTrans;
                }
                catch (SqlException ex)
                {
                    Trace.WriteLine(Query);
                    throw new Exception(ex.Message);
                }
            }
            else if (SqlConn.OrSqlConn != null)
            {
                try
                {
                    OrSqlCmd = new OracleCommand(Query, SqlConn.OrSqlConn);

                    if (SqlConn.OrSqlTrans != null)
                        OrSqlCmd.Transaction = SqlConn.OrSqlTrans;
                }
                catch (OracleException ex)
                {
                    Trace.WriteLine(Query);
                    throw new Exception(ex.Message);
                }
            }
            else if (SqlConn.SQLiteConn != null)
            {
                try
                {
                    SQLiteCmd = new SQLiteCommand(Query, SqlConn.SQLiteConn);

                    if (SqlConn.SQLiteTrans != null)
                        SQLiteCmd.Transaction = SqlConn.SQLiteTrans;
                }
                catch (SQLiteException ex)
                {
                    Trace.WriteLine(Query);
                    throw new Exception(ex.Message);
                }
            }
            else if (SqlConn.PgSqlConn != null)
            {
                try
                {
                    PgSqlCmd = new NpgsqlCommand(Query, SqlConn.PgSqlConn);

                    if (SqlConn.PgSqlTrans != null)
                        PgSqlCmd.Transaction = SqlConn.PgSqlTrans;
                }
                catch (NpgsqlException ex)
                {
                    Trace.WriteLine(Query);
                    throw new Exception(ex.Message);
                }
            }
            else if (SqlConn.OdbcConn != null)
            {
                try
                {
                    OdbcCmd = new OdbcCommand(Query, SqlConn.OdbcConn);

                    if (SqlConn.OdbcTrans != null)
                        OdbcCmd.Transaction = SqlConn.OdbcTrans;
                }
                catch (OdbcException ex)
                {
                    Trace.WriteLine(Query);
                    throw new Exception(ex.Message);
                }
            }
        }

        public bool HasRows()
        {
            if (MySqlCmd != null)
            {
                try
                {
                    using (MySqlDataReader reader = MySqlCmd.ExecuteReader())
                    {
                        if (reader != null)
                            return reader.HasRows;
                    }
                }
                catch (MySqlException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (MsSqlCmd != null)
            {
                try
                {
                    using (SqlDataReader reader = MsSqlCmd.ExecuteReader())
                    {
                        if (reader != null)
                            return reader.HasRows;
                    }
                }
                catch (SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (OrSqlCmd != null)
            {
                try
                {
                    using (OracleDataReader reader = OrSqlCmd.ExecuteReader())
                    {
                        if (reader != null)
                            return reader.HasRows;
                    }
                }
                catch (OracleException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (SQLiteCmd != null)
            {
                try
                {
                    using (SQLiteDataReader reader = SQLiteCmd.ExecuteReader())
                    {
                        if (reader != null)
                            return reader.HasRows;
                    }
                }
                catch (SQLiteException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (PgSqlCmd != null)
            {
                try
                {
                    using (NpgsqlDataReader reader = PgSqlCmd.ExecuteReader())
                    {
                        if (reader != null)
                            return reader.HasRows;
                    }
                }
                catch (NpgsqlException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (OdbcCmd != null)
            {
                try
                {
                    using (OdbcDataReader reader = OdbcCmd.ExecuteReader())
                    {
                        if (reader != null)
                            return reader.HasRows;
                    }
                }
                catch (OdbcException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            return false;
        }

        public List<string> Parameters
        {
            set
            {
                if (MySqlCmd != null)
                {
                    try
                    {
                        MySqlCmd.Parameters.Clear();

                        for (int i = 0; i < value.Count; i += 2)
                        {
                            MySqlCmd.Parameters.AddWithValue(value[i], value[i + 1]);
                        }
                    }
                    catch (MySqlException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                }
                else if (MsSqlCmd != null)
                {
                    try
                    {
                        MsSqlCmd.Parameters.Clear();

                        for (int i = 0; i < value.Count; i += 2)
                        {
                            MsSqlCmd.Parameters.AddWithValue(value[i], value[i + 1]);
                        }
                    }
                    catch (SqlException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                }
                else if (OrSqlCmd != null)
                {
                    try
                    {
                        OrSqlCmd.Parameters.Clear();

                        for (int i = 0; i < value.Count; i += 2)
                        {
                            OrSqlCmd.Parameters.Add(value[i], value[i + 1]); //TODO: Test if this is working as expected
                            //OrSqlCmd.Parameters.AddWithValue(value[i], value[i + 1]);
                        }
                    }
                    catch (OracleException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                }
                else if (SQLiteCmd != null)
                {
                    try
                    {
                        SQLiteCmd.Parameters.Clear();

                        for (int i = 0; i < value.Count; i += 2)
                        {
                            SQLiteCmd.Parameters.AddWithValue(value[i], value[i + 1]);
                        }
                    }
                    catch (SQLiteException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                }
                else if (PgSqlCmd != null)
                {
                    try
                    {
                        PgSqlCmd.Parameters.Clear();

                        for (int i = 0; i < value.Count; i += 2)
                        {
                            PgSqlCmd.Parameters.AddWithValue(value[i], value[i + 1]);
                        }
                    }
                    catch (NpgsqlException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                }
                else if (OdbcCmd != null)
                {
                    try
                    {
                        OdbcCmd.Parameters.Clear();

                        for (int i = 0; i < value.Count; i += 2)
                        {
                            OdbcCmd.Parameters.AddWithValue(value[i], value[i + 1]);
                        }
                    }
                    catch (OdbcException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                }
            }
        }

        public int ExecuteNonQuery()
        {
            if (MySqlCmd != null)
            {
                try
                {
                    return MySqlCmd.ExecuteNonQuery();
                }
                catch (MySqlException ex)
                {
                    Trace.WriteLine(MySqlCmd.CommandText);
                    throw new Exception(ex.Message);
                }
            }
            else if (MsSqlCmd != null)
            {
                try
                {
                    return MsSqlCmd.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    Trace.WriteLine(MsSqlCmd.CommandText);
                    throw new Exception(ex.Message);
                }
            }
            else if (OrSqlCmd != null)
            {
                try
                {
                    return OrSqlCmd.ExecuteNonQuery();
                }
                catch (OracleException ex)
                {
                    Trace.WriteLine(OrSqlCmd.CommandText);
                    throw new Exception(ex.Message);
                }
            }
            else if (SQLiteCmd != null)
            {
                try
                {
                    return SQLiteCmd.ExecuteNonQuery();
                }
                catch (SQLiteException ex)
                {
                    Trace.WriteLine(SQLiteCmd.CommandText);
                    throw new Exception(ex.Message);
                }
            }
            else if (PgSqlCmd != null)
            {
                try
                {
                    return PgSqlCmd.ExecuteNonQuery();
                }
                catch (NpgsqlException ex)
                {
                    Trace.WriteLine(PgSqlCmd.CommandText);
                    throw new Exception(ex.Message);
                }
            }
            else if (OdbcCmd != null)
            {
                try
                {
                    return OdbcCmd.ExecuteNonQuery();
                }
                catch (OdbcException ex)
                {
                    Trace.WriteLine(OdbcCmd.CommandText);
                    throw new Exception(ex.Message);
                }
            }
            return 0;
        }

        public int ExecuteScaler()
        {
            if (MySqlCmd != null)
            {
                try
                {
                    return Convert.ToInt32(MySqlCmd.ExecuteScalar());
                }
                catch (MySqlException ex)
                {
                    Trace.WriteLine(MySqlCmd.CommandText);
                    throw new Exception(ex.Message);
                }
            }
            else if (MsSqlCmd != null)
            {
                try
                {
                    return Convert.ToInt32(MsSqlCmd.ExecuteScalar());
                }
                catch (SqlException ex)
                {
                    Trace.WriteLine(MsSqlCmd.CommandText);
                    throw new Exception(ex.Message);
                }
            }
            else if (OrSqlCmd != null)
            {
                try
                {
                    return Convert.ToInt32(OrSqlCmd.ExecuteScalar());
                }
                catch (OracleException ex)
                {
                    Trace.WriteLine(OrSqlCmd.CommandText);
                    throw new Exception(ex.Message);
                }
            }
            else if (SQLiteCmd != null)
            {
                try
                {
                    return Convert.ToInt32(SQLiteCmd.ExecuteScalar());
                }
                catch (SQLiteException ex)
                {
                    Trace.WriteLine(SQLiteCmd.CommandText);
                    throw new Exception(ex.Message);
                }
            }
            else if (PgSqlCmd != null)
            {
                try
                {
                    return Convert.ToInt32(PgSqlCmd.ExecuteScalar());
                }
                catch (NpgsqlException ex)
                {
                    Trace.WriteLine(PgSqlCmd.CommandText);
                    throw new Exception(ex.Message);
                }
            }
            else if (OdbcCmd != null)
            {
                try
                {
                    return Convert.ToInt32(OdbcCmd.ExecuteScalar());
                }
                catch (OdbcException ex)
                {
                    Trace.WriteLine(OdbcCmd.CommandText);
                    throw new Exception(ex.Message);
                }
            }
            return 0;
        }

        public string CommandText
        {
            get
            {
                if (MySqlCmd != null)
                    return MySqlCmd.CommandText;
                else if (MsSqlCmd != null)
                    return MsSqlCmd.CommandText;
                else if (OrSqlCmd != null)
                    return OrSqlCmd.CommandText;
                else if (SQLiteCmd != null)
                    return SQLiteCmd.CommandText;
                else if (PgSqlCmd != null)
                    return PgSqlCmd.CommandText;
                else if (OdbcCmd != null)
                    return OdbcCmd.CommandText;

                return "";
            }
        }


        #region IDisposable

        public void Dispose()
        {
            if (MySqlCmd != null)
            {
                MySqlCmd.Dispose();
            }
            else if (MsSqlCmd != null)
            {
                MsSqlCmd.Dispose();
            }
            else if (OrSqlCmd != null)
            {
                OrSqlCmd.Dispose();
            }
            else if (SQLiteCmd != null)
            {
                SQLiteCmd.Dispose();
            }
            else if (PgSqlCmd != null)
            {
                PgSqlCmd.Dispose();
            }
            else if (OdbcCmd != null)
            {
                OdbcCmd.Dispose();
            }
        }

        #endregion
    }

    public class DoaSqlDataAdapter : IDisposable
    {
        internal DoaSqlConnection SqlConn;
        internal MySqlDataAdapter MySqlDA = null;
        internal SqlDataAdapter MsSqlDA = null;
        internal OracleDataAdapter OrSqlDA = null;
        internal SQLiteDataAdapter SQLiteDA = null;
        internal NpgsqlDataAdapter PgSqlDA = null;
        internal OdbcDataAdapter OdbcDA = null;

        public DoaSqlDataAdapter(DoaSqlCommand cmd)
        {
            this.SqlConn = cmd.SqlConn;

            if (cmd.MySqlCmd != null)
            {
                try
                {
                    MySqlDA = new MySqlDataAdapter(cmd.MySqlCmd);
                }
                catch (MySqlException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (cmd.MsSqlCmd != null)
            {
                try
                {
                    MsSqlDA = new SqlDataAdapter(cmd.MsSqlCmd);
                }
                catch (SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (cmd.OrSqlCmd != null)
            {
                try
                {
                    OrSqlDA = new OracleDataAdapter(cmd.OrSqlCmd);
                }
                catch (OracleException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (cmd.SQLiteCmd != null)
            {
                try
                {
                    SQLiteDA = new SQLiteDataAdapter(cmd.SQLiteCmd);
                }
                catch (SQLiteException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (cmd.PgSqlCmd != null)
            {
                try
                {
                    PgSqlDA = new NpgsqlDataAdapter(cmd.PgSqlCmd);
                }
                catch (NpgsqlException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (cmd.OdbcCmd != null)
            {
                try
                {
                    OdbcDA = new OdbcDataAdapter(cmd.OdbcCmd);
                }
                catch (OdbcException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        public void UpdateCommand(string Query)
        {
            Query = DoaSqlCommand.ConvertQueryToType(Query, SqlConn.DbaseType);

            if (MySqlDA != null)
                MySqlDA.UpdateCommand = new MySqlCommand(Query);
            else if (MsSqlDA != null)
                MsSqlDA.UpdateCommand = new SqlCommand(Query);
            else if (OrSqlDA != null)
                OrSqlDA.UpdateCommand = new OracleCommand(Query);
            else if (SQLiteDA != null)
                SQLiteDA.UpdateCommand = new SQLiteCommand(Query);
            else if (PgSqlDA != null)
                PgSqlDA.UpdateCommand = new NpgsqlCommand(Query);
            else if (OdbcDA != null)
                OdbcDA.UpdateCommand = new OdbcCommand(Query);
        }

        public void InsertCommand(string Query)
        {
            Query = DoaSqlCommand.ConvertQueryToType(Query, SqlConn.DbaseType);

            if (MySqlDA != null)
                MySqlDA.InsertCommand = new MySqlCommand(Query);
            else if (MsSqlDA != null)
                MsSqlDA.InsertCommand = new SqlCommand(Query);
            else if (OrSqlDA != null)
                OrSqlDA.InsertCommand = new OracleCommand(Query);
            else if (SQLiteDA != null)
                SQLiteDA.InsertCommand = new SQLiteCommand(Query);
            else if (PgSqlDA != null)
                PgSqlDA.InsertCommand = new NpgsqlCommand(Query);
            else if (OdbcDA != null)
                OdbcDA.InsertCommand = new OdbcCommand(Query);
        }

        public void DeleteCommand(string Query)
        {
            Query = DoaSqlCommand.ConvertQueryToType(Query, SqlConn.DbaseType);

            if (MySqlDA != null)
                MySqlDA.DeleteCommand = new MySqlCommand(Query);
            else if (MsSqlDA != null)
                MsSqlDA.DeleteCommand = new SqlCommand(Query);
            else if (OrSqlDA != null)
                OrSqlDA.DeleteCommand = new OracleCommand(Query);
            else if (SQLiteDA != null)
                SQLiteDA.DeleteCommand = new SQLiteCommand(Query);
            else if (PgSqlDA != null)
                PgSqlDA.DeleteCommand = new NpgsqlCommand(Query);
            else if (OdbcDA != null)
                OdbcDA.DeleteCommand = new OdbcCommand(Query);
        }

        public int Fill(out DataTable Table)
        {
            Table = new DataTable();

            if (MySqlDA != null)
            {
                try
                {
                    MySqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                    return MySqlDA.Fill(Table);
                }
                catch (MySqlException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (MsSqlDA != null)
            {
                try
                {
                    MsSqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                    return MsSqlDA.Fill(Table);
                }
                catch (SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (OrSqlDA != null)
            {
                try
                {
                    OrSqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                    return OrSqlDA.Fill(Table);
                }
                catch (OracleException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (SQLiteDA != null)
            {
                try
                {
                    SQLiteDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                    return SQLiteDA.Fill(Table);
                }
                catch (SQLiteException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (PgSqlDA != null)
            {
                try
                {
                    PgSqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                    return PgSqlDA.Fill(Table);
                }
                catch (NpgsqlException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (OdbcDA != null)
            {
                try
                {
                    OdbcDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                    return OdbcDA.Fill(Table);
                }
                catch (OdbcException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            return -1;
        }

        public int Update(DataTable dt)
        {
            if (MySqlDA != null)
            {
                try
                {
                    return MySqlDA.Update(dt);
                }
                catch (MySqlException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (MsSqlDA != null)
            {
                try
                {
                    return MsSqlDA.Update(dt);
                }
                catch (SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (OrSqlDA != null)
            {
                try
                {
                    return OrSqlDA.Update(dt);
                }
                catch (OracleException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (SQLiteDA != null)
            {
                try
                {
                    return SQLiteDA.Update(dt);
                }
                catch (SQLiteException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (PgSqlDA != null)
            {
                try
                {
                    return PgSqlDA.Update(dt);
                }
                catch (NpgsqlException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (OdbcDA != null)
            {
                try
                {
                    return OdbcDA.Update(dt);
                }
                catch (OdbcException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            return -1;
        }

        public int Update(DataRow dr)
        {
            DataRow[] drc = { dr };

            if (MySqlDA != null)
            {
                try
                {
                    return MySqlDA.Update(drc);
                }
                catch (MySqlException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (MsSqlDA != null)
            {
                try
                {
                    return MsSqlDA.Update(drc);
                }
                catch (SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (OrSqlDA != null)
            {
                try
                {
                    return OrSqlDA.Update(drc);
                }
                catch (OracleException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (SQLiteDA != null)
            {
                try
                {
                    return SQLiteDA.Update(drc);
                }
                catch (SQLiteException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (PgSqlDA != null)
            {
                try
                {
                    return PgSqlDA.Update(drc);
                }
                catch (NpgsqlException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (OdbcDA != null)
            {
                try
                {
                    return OdbcDA.Update(drc);
                }
                catch (OdbcException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            return -1;
        }

        #region IDisposable

        public void Dispose()
        {
            if (MySqlDA != null)
            {
                MySqlDA.Dispose();
            }
            else if (MsSqlDA != null)
            {
                MsSqlDA.Dispose();
            }
            else if (OrSqlDA != null)
            {
                OrSqlDA.Dispose();
            }
            else if (SQLiteDA != null)
            {
                SQLiteDA.Dispose();
            }
            else if (PgSqlDA != null)
            {
                PgSqlDA.Dispose();
            }
            else if (OdbcDA != null)
            {
                OdbcDA.Dispose();
            }
        }

        #endregion
    }

    public class DoaSqlCommandBuilder : IDisposable
    {
        MySqlCommandBuilder MySqlCmdBuilder = null;
        SqlCommandBuilder MsSqlCmdBuilder = null;
        OracleCommandBuilder OrSqlCmdBuilder = null;
        SQLiteCommandBuilder SQLiteCmdBuilder = null;
        NpgsqlCommandBuilder PgSqlCmdBuilder = null;
        OdbcCommandBuilder OdbcCmdBuilder = null;

        public DoaSqlCommandBuilder(DoaSqlDataAdapter da)
        {
            if (da.MySqlDA != null)
            {
                try
                {
                    MySqlCmdBuilder = new MySqlCommandBuilder(da.MySqlDA);
                }
                catch (MySqlException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (da.MsSqlDA != null)
            {
                try
                {
                    MsSqlCmdBuilder = new SqlCommandBuilder(da.MsSqlDA);
                }
                catch (SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (da.OrSqlDA != null)
            {
                try
                {
                    OrSqlCmdBuilder = new OracleCommandBuilder(da.OrSqlDA);
                }
                catch (OracleException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (da.SQLiteDA != null)
            {
                try
                {
                    SQLiteCmdBuilder = new SQLiteCommandBuilder(da.SQLiteDA);
                }
                catch (SQLiteException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (da.PgSqlDA != null)
            {
                try
                {
                    PgSqlCmdBuilder = new NpgsqlCommandBuilder(da.PgSqlDA);
                }
                catch (NpgsqlException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            else if (da.OdbcDA != null)
            {
                try
                {
                    OdbcCmdBuilder = new OdbcCommandBuilder(da.OdbcDA);
                }
                catch (OdbcException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        #region IDisposable

        public void Dispose()
        {
            if (MySqlCmdBuilder != null)
            {
                MySqlCmdBuilder.Dispose();
            }
            else if (MsSqlCmdBuilder != null)
            {
                MsSqlCmdBuilder.Dispose();
            }
            else if (OrSqlCmdBuilder != null)
            {
                OrSqlCmdBuilder.Dispose();
            }
            else if (SQLiteCmdBuilder != null)
            {
                SQLiteCmdBuilder.Dispose();
            }
            else if (PgSqlCmdBuilder != null)
            {
                PgSqlCmdBuilder.Dispose();
            }
            else if (OdbcCmdBuilder != null)
            {
                OdbcCmdBuilder.Dispose();
            }
        }
        #endregion
    }
}
