HYTOS

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Globalization;

using System.Data.SQLite;

using System.Data;

namespace Converter.BaseModel

{

    public class Project_DB

    {

        const string SPPID_DB_INFO_TABLE = "SPPID_CONNECTION_INFO";

        public static void Conn()

        {

            Project_Info projectInfo = Project_Info.GetInstance();

            using (SQLiteConnection connection = new SQLiteConnection(string.Format(CultureInfo.CurrentCulture, "Data Source = {0}", projectInfo.DBFilePath)))

            {

                connection.Open();

            }

        }

        public static bool ConnTestAndCreateTable()

        {

            bool result = false;

            Project_Info projectInfo = Project_Info.GetInstance();

            SQLiteConnection connection = new SQLiteConnection(string.Format(CultureInfo.CurrentCulture, @"Data Source = {0}", projectInfo.DBFilePath));

            try

            {

                connection.Open();

                if (connection.State == ConnectionState.Open)

                {

                    CreateTable(connection);

                    result = true;

                }

                connection.Close();

            }

            catch (Exception ex)

            {

            }

            finally

            {

                connection.Dispose();

            }

            return result;

        }

        public static bool SaveSPPID_DB_INFO(string jsonString)

        {

            Project_Info projectInfo = Project_Info.GetInstance();

            using (SQLiteConnection connection = new SQLiteConnection(string.Format(CultureInfo.CurrentCulture, "Data Source = {0}", projectInfo.DBFilePath)))

            {

                try

                {

                    connection.Open();

                    using (SQLiteCommand cmd = connection.CreateCommand())

                    {

                        cmd.CommandText = string.Format("DELETE FROM {0}", SPPID_DB_INFO_TABLE);

                        cmd.ExecuteNonQuery();

                        cmd.CommandText = string.Format("INSERT INTO {0} VALUES (@jsonString)", SPPID_DB_INFO_TABLE);

                        cmd.Parameters.AddWithValue("@jsonString", jsonString);

                        cmd.ExecuteNonQuery();

                    }

                    connection.Close();

                }

                catch (Exception ex)

                {

                    return false;

                }

                finally

                {

                    connection.Dispose();

                }

            }

            return true;

        }

        public static DataTable SelectSPPID_DB_INFO()

        {

            DataTable dt = new DataTable();

            Project_Info projectInfo = Project_Info.GetInstance();

            using (SQLiteConnection connection = new SQLiteConnection(string.Format(CultureInfo.CurrentCulture, "Data Source = {0}", projectInfo.DBFilePath)))

            {

                try

                {

                    connection.Open();

                    using (SQLiteCommand cmd = connection.CreateCommand())

                    {

                        cmd.CommandText = string.Format("SELECT * FROM {0}", SPPID_DB_INFO_TABLE);

                        using (SQLiteDataReader dr = cmd.ExecuteReader())

                            dt.Load(dr);

                    }

                    connection.Close();

                }

                catch (Exception ex)

                {

                }

                finally

                {

                    connection.Dispose();

                }

            }

            return dt;

        }

        private static void CreateTable(SQLiteConnection connection)

        {

            using (SQLiteCommand cmd = connection.CreateCommand())

            {

                cmd.CommandText = "SELECT NAME FROM sqlite_master WHERE type='table'";

                using (SQLiteDataReader dr = cmd.ExecuteReader())

                using (DataTable dt = new DataTable())

                {

                    dt.Load(dr);

                    if (dt.Select(string.Format("NAME = '{0}'", SPPID_DB_INFO_TABLE)).Length == 0)

                    {

                        cmd.CommandText = string.Format("CREATE TABLE {0} (JsonString TEXT)", SPPID_DB_INFO_TABLE);

                        cmd.ExecuteNonQuery();

                    }

                }

            }

        }

    }

}