VMK_DAO_FOR_SQLITE3 v1.1 by ManMan89

/***
    -------

    VMK_DAO_FOR_SQLITE3 by ManMan89

    Version 1.1

    Release 2016.12.05

    ------- HOW TO USE - EXAMPLE FOR .NET 4.0 X86 -------

    STEP 1 - DOWNLOAD SQLITE-NETFX40-BINARY-WIN32-2010-1.0.103.0.ZIP FROM HTTPS://SYSTEM.DATA.SQLITE.ORG/INDEX.HTML/DOC/TRUNK/WWW/DOWNLOADS.WIKI
    STEP 2 - ADD REFERENCE SYSTEM.DATA.SQLITE.DLL TO VISUAL STUDIO
    STEP 3 - SET COPY LOCAL = TRUE
    STEP 4 - COPY FILE SQLITE.INTEROP.DLL TO LOCAL FOLDER WITH EXE FILE

    ------- EXAMPLE CODE -------

    EXAMPLE SELECT

        using System.Collections;

        VMK_DAO_FOR_SQLITE3 vmk = new VMK_DAO_FOR_SQLITE3();

        vmk.SQLITE_DATABASE_FILE = @"D:\ManMan89.SQLITE3";

        vmk.SQLITE_QUERY = "SELECT vmk_value FROM manman89 WHERE vmk_key = @vmk_key";

        vmk.SQLITE_PARAMETERS = vmk.CREATE_SQLITE_PARAMETERS();

        vmk.SQLITE_PARAMETERS.Clear();

        vmk.SQLITE_PARAMETERS.Rows.Add("@vmk_key", "12-05-1989");

        ArrayList KQ = vmk.SQLITE_SELECT();

        MessageBox.Show(KQ[0].ToString());
        MessageBox.Show(KQ[1].ToString());

        DGV1.DataSource = (DataTable)KQ[2];

        // KQ[0] -> "OK" or "ERROR"
        // KQ[1] -> "" or "ERROR MESSAGE"
        // KQ[2] -> DATATABLE or EMPTY DATATABLE

    -------

    EXAMPLE INSERT

        VMK_DAO_FOR_SQLITE3 vmk = new VMK_DAO_FOR_SQLITE3();

        vmk.SQLITE_DATABASE_FILE = @"D:\ManMan89.SQLITE3";

        vmk.SQLITE_QUERY = "INSERT INTO manman89 (vmk_key, vmk_value) VALUES (@vmk_key, @vmk_value)";

        vmk.SQLITE_PARAMETERS = vmk.CREATE_SQLITE_PARAMETERS();

        vmk.SQLITE_PARAMETERS.Clear();

        vmk.SQLITE_PARAMETERS.Rows.Add("@vmk_key", "12-05-1989");
        vmk.SQLITE_PARAMETERS.Rows.Add("@vmk_value", "Have a nice day");

        String[] KQ = vmk.SQLITE_INSERT_DELETE_UPDATE();

        MessageBox.Show(KQ[0].ToString());
        MessageBox.Show(KQ[1].ToString());

        // KQ[0] -> "OK" or "ERROR"
        // KQ[1] -> "SQL STATUS > 0" or "ERROR MESSAGE"

    -------

    EXAMPLE DELETE

        VMK_DAO_FOR_SQLITE3 vmk = new VMK_DAO_FOR_SQLITE3();

        vmk.SQLITE_DATABASE_FILE = @"D:\ManMan89.SQLITE3";

        vmk.SQLITE_QUERY = "DELETE FROM manman89 WHERE vmk_key = @vmk_key";

        vmk.SQLITE_PARAMETERS = vmk.CREATE_SQLITE_PARAMETERS();

        vmk.SQLITE_PARAMETERS.Clear();

        vmk.SQLITE_PARAMETERS.Rows.Add("@vmk_key", "12-05-1989");

        String[] KQ = vmk.SQLITE_INSERT_DELETE_UPDATE();

        MessageBox.Show(KQ[0].ToString());
        MessageBox.Show(KQ[1].ToString());

        // KQ[0] -> "OK" or "ERROR"
        // KQ[1] -> "SQL STATUS > 0" or "ERROR MESSAGE"

    -------

    EXAMPLE UPDATE

        VMK_DAO_FOR_SQLITE3 vmk = new VMK_DAO_FOR_SQLITE3();

        vmk.SQLITE_DATABASE_FILE = @"D:\ManMan89.SQLITE3";

        vmk.SQLITE_QUERY = "UPDATE manman89 SET vmk_value = @vmk_value WHERE vmk_key = @vmk_key";

        vmk.SQLITE_PARAMETERS = vmk.CREATE_SQLITE_PARAMETERS();

        vmk.SQLITE_PARAMETERS.Clear();

        vmk.SQLITE_PARAMETERS.Rows.Add("@vmk_key", "12-05-1989");
        vmk.SQLITE_PARAMETERS.Rows.Add("@vmk_value", "Have a good day");

        String[] KQ = vmk.SQLITE_INSERT_DELETE_UPDATE();

        MessageBox.Show(KQ[0].ToString());
        MessageBox.Show(KQ[1].ToString());

        // KQ[0] -> "OK" or "ERROR"
        // KQ[1] -> "SQL STATUS > 0" or "ERROR MESSAGE"

    -------
***/

using System;
using System.Data;
using System.Data.SQLite;
using System.Collections;

namespace ManMan89
{
    public class VMK_DAO_FOR_SQLITE3
    {
        public string SQLITE_QUERY = "";
        public DataTable SQLITE_PARAMETERS;
        public string SQLITE_DATABASE_FILE = "";

        private string SQLITE_CONNECTION_STRING = "";

        public DataTable CREATE_SQLITE_PARAMETERS()
        {
            DataTable SQLITE_PARAMETERS = new DataTable();
            SQLITE_PARAMETERS.Columns.Add("KEY", typeof(string));
            SQLITE_PARAMETERS.Columns.Add("VALUE", typeof(string));
            return SQLITE_PARAMETERS;
        }

        public static bool VMK_SQLITE_CHECK_DBFILE_EXISTS(string DATABASE_FILE) { return System.IO.File.Exists(DATABASE_FILE.Trim()); }

        public ArrayList SQLITE_SELECT()
        {
            ArrayList DATA_RETURN = new ArrayList(3);
            DataTable DATA_TABLE_RETURN = new DataTable();

            if (VMK_SQLITE_CHECK_DBFILE_EXISTS(SQLITE_DATABASE_FILE) == false)
            {
                DATA_RETURN.Clear();
                DATA_RETURN.Add("ERROR");
                DATA_RETURN.Add("KHÔNG TÌM THẤY FILE  " + Convert.ToChar(34) + SQLITE_DATABASE_FILE.ToUpper() + Convert.ToChar(34));
                DATA_RETURN.Add(DATA_TABLE_RETURN);
                return DATA_RETURN;
            }

            SQLITE_CONNECTION_STRING = @"Data Source=" + SQLITE_DATABASE_FILE + ";Version=3;Foreign Keys=True;";

            SQLiteConnection SQLITE_CONNECTION = null;
            SQLiteCommand SQLITE_COMMAND = null;

            try
            {
                // MỞ KẾT NỐI CSDL

                SQLITE_CONNECTION = new SQLiteConnection(SQLITE_CONNECTION_STRING);
                SQLITE_CONNECTION.Open();

                // THỰC HIỆN TRUY VẤN CSDL

                SQLITE_COMMAND = new SQLiteCommand(SQLITE_QUERY, SQLITE_CONNECTION);
                SQLITE_COMMAND.Parameters.Clear();

                for (int i = 0; i < SQLITE_PARAMETERS.Rows.Count; i++)
                {
                    SQLITE_COMMAND.Parameters.Add(new SQLiteParameter(SQLITE_PARAMETERS.Rows[i][0].ToString(), SQLITE_PARAMETERS.Rows[i][1].ToString()));
                }

                SQLiteDataReader SQLITE_DATA_READER = SQLITE_COMMAND.ExecuteReader();

                // LẤY DỮ LIỆU CÓ ĐƯỢC TỪ TRUY VẤN ĐƯA VÀO ĐỐI TƯỢNG DATA TABLE

                DATA_TABLE_RETURN.Load(SQLITE_DATA_READER);

                // ĐÓNG KẾT NỐI CSDL

                SQLITE_COMMAND.Dispose();
                SQLITE_CONNECTION.Close();

                DATA_RETURN.Clear();
                DATA_RETURN.Add("OK");
                DATA_RETURN.Add("");
                DATA_RETURN.Add(DATA_TABLE_RETURN);
            }
            catch (Exception ex)
            {
                if(SQLITE_COMMAND != null) { SQLITE_COMMAND.Dispose(); }
                if(SQLITE_CONNECTION != null) { SQLITE_CONNECTION.Close(); }

                DATA_RETURN.Clear();
                DATA_RETURN.Add("ERROR");
                DATA_RETURN.Add(ex.ToString());
                DATA_RETURN.Add(DATA_TABLE_RETURN);
            }

            return DATA_RETURN;
        }

        public string[] SQLITE_INSERT_DELETE_UPDATE()
        {
            string[] DATA_RETURN = { "", "" };

            if (VMK_SQLITE_CHECK_DBFILE_EXISTS(SQLITE_DATABASE_FILE) == false)
            {
                DATA_RETURN[0] = "ERROR";
                DATA_RETURN[1] = "KHÔNG TÌM THẤY FILE  " + Convert.ToChar(34) + SQLITE_DATABASE_FILE.ToUpper() + Convert.ToChar(34);
                return DATA_RETURN;
            }

            SQLITE_CONNECTION_STRING = @"Data Source=" + SQLITE_DATABASE_FILE + ";Version=3;Foreign Keys=True;";

            SQLiteConnection SQLITE_CONNECTION = null;
            SQLiteCommand SQLITE_COMMAND = null;

            try
            {
                // MỞ KẾT NỐI CSDL

                SQLITE_CONNECTION = new SQLiteConnection(SQLITE_CONNECTION_STRING);
                SQLITE_CONNECTION.Open();

                // THỰC HIỆN TRUY VẤN CSDL

                SQLITE_COMMAND = new SQLiteCommand(SQLITE_QUERY, SQLITE_CONNECTION);
                SQLITE_COMMAND.Parameters.Clear();

                for (int i = 0; i < SQLITE_PARAMETERS.Rows.Count; i++)
                {
                    SQLITE_COMMAND.Parameters.Add(new SQLiteParameter(SQLITE_PARAMETERS.Rows[i][0].ToString(), SQLITE_PARAMETERS.Rows[i][1].ToString()));
                }

                int SQLITE_COMMAND_RESULT = SQLITE_COMMAND.ExecuteNonQuery();

                // ĐÓNG KẾT NỐI CSDL

                SQLITE_COMMAND.Dispose();
                SQLITE_CONNECTION.Close();

                DATA_RETURN[0] = "OK";
                DATA_RETURN[1] = SQLITE_COMMAND_RESULT.ToString();
            }
            catch (Exception ex)
            {
                if (SQLITE_COMMAND != null) { SQLITE_COMMAND.Dispose(); }
                if (SQLITE_CONNECTION != null) { SQLITE_CONNECTION.Close(); }

                DATA_RETURN[0] = "ERROR";
                DATA_RETURN[1] = ex.ToString();
            }

            return DATA_RETURN;
        }

        public string[] SQLITE_INSERT_DELETE_UPDATE_MULTIPLE_PARAMETERS_EACH_QUERY(ArrayList LIST_SQLITE_PARAMETERS)
        {
            string[] DATA_RETURN = { "", "" };

            if (VMK_SQLITE_CHECK_DBFILE_EXISTS(SQLITE_DATABASE_FILE) == false)
            {
                DATA_RETURN[0] = "ERROR";
                DATA_RETURN[1] = "KHÔNG TÌM THẤY FILE  " + Convert.ToChar(34) + SQLITE_DATABASE_FILE.ToUpper() + Convert.ToChar(34);
                return DATA_RETURN;
            }

            SQLITE_CONNECTION_STRING = @"Data Source=" + SQLITE_DATABASE_FILE + ";Version=3;Foreign Keys=True;";

            SQLiteConnection SQLITE_CONNECTION = null;
            SQLiteCommand SQLITE_COMMAND = null;

            try
            {
                int SQLITE_COMMAND_RESULT = 0;

                // MỞ KẾT NỐI CSDL

                SQLITE_CONNECTION = new SQLiteConnection(SQLITE_CONNECTION_STRING);
                SQLITE_CONNECTION.Open();

                // THỰC HIỆN TRUY VẤN CSDL

                SQLITE_COMMAND = new SQLiteCommand(SQLITE_CONNECTION);
                SQLITE_COMMAND.Parameters.Clear();

                SQLiteTransaction SQLITE_TRANS = SQLITE_CONNECTION.BeginTransaction();

                for (int i = 0; i < LIST_SQLITE_PARAMETERS.Count; i++)
                {
                    SQLITE_COMMAND_RESULT = 0;
                    SQLITE_COMMAND.CommandText = SQLITE_QUERY;

                    DataTable DT = (DataTable)LIST_SQLITE_PARAMETERS[i];
                    for (int x = 0; x < DT.Rows.Count; x++)
                    {
                        SQLITE_COMMAND.Parameters.Add(new SQLiteParameter(DT.Rows[x][0].ToString(), DT.Rows[x][1].ToString()));
                    }
                    DT = null;

                    SQLITE_COMMAND_RESULT = SQLITE_COMMAND.ExecuteNonQuery();
                }

                SQLITE_TRANS.Commit();

                // ĐÓNG KẾT NỐI CSDL

                SQLITE_COMMAND.Dispose();
                SQLITE_CONNECTION.Close();

                DATA_RETURN[0] = "OK";
                DATA_RETURN[1] = SQLITE_COMMAND_RESULT.ToString();
            }
            catch (Exception ex)
            {
                if (SQLITE_COMMAND != null) { SQLITE_COMMAND.Dispose(); }
                if (SQLITE_CONNECTION != null) { SQLITE_CONNECTION.Close(); }

                DATA_RETURN[0] = "ERROR";
                DATA_RETURN[1] = ex.ToString();
            }

            return DATA_RETURN;
        }
    }
}