HYTOS

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.Common;

using System.Data.SqlClient;

using System.Data.SQLite;

using System.Linq;

using System.Text.RegularExpressions;

namespace ID2PSN

{

    public class DB

    {

        const string PSN_TOPOLOGY_RULE = "T_PSN_TOPOLOGY_RULE";

        const string PSN_HEADER_SETTING = "T_PSN_HEADER_SETTING";

        const string PSN_VENTDRAIN_SETTING = "T_PSN_VENTDRAIN_SETTING";

        const string PSN_VIEW = "T_PSN_VIEW";

        const string PSN_TRANSFORMKEYWORD_SETTING = "T_PSN_TRANSFORMKEYWORD_SETTING";

        const string PSN_VALVEGROUP_SETTING = "SPPIDValveGroup";

        const string PSN_MULTIWAY_SETTING = "T_PSN_MULTIWAY_SETTING";

        const string PSN_PATHITEMS = "SPPIDPathItem";

        const string PSN_SEQUENCEDATA = "SPPIDSequenceData";

        const string PSN_PIPESYSTEMNETWORK = "SPPIDPipeSystemNetwork";

        const string PSN_EQUIPMENT = "SPPIDEquipment";

        const string PSN_NOZZLE = "SPPIDNozzle";

        const string PSN_FLUIDCODE = "SPPIDFluidCode";

        const string PSN_PIPINGMATLCLASS = "SPPIDPipingMatlClass";

        const string PSN_TOPOLOGYSET = "SPPIDTopologySet";

        //2021.11.17 추가 

        const string PSN_PIPELINE = "SPPIDPipeLine";

        //2021.11.26 추가

        const string PSN_INSULATIONPURPOSE = "SPPIDInsulationPurpose";

        //2022.01.12 추가 //ARS_COMMON DB

        const string PSN_REVISION = "PSNRevision";

        const string PSN_COMMON = "ARS_COMMON";

        //2022.01.19 추가

        const string PSN_PIPESYSTEM = "SPPIDPipeSystem";

        //2022.02.03 추가

        const string PSN_NOPOCKETSETTING = "T_PSN_NOPOCKET_SETTING";

        const string PSN_AIRFINCOOLERSETTING = "T_PSN_AIRFINCOOLER_SETTING";

        /// <summary>

        ///  ID2 Project.db 데이터를 가져온다. 

        ///  DB 접속 정보 및 DBType (Sqlite, Mssql) 정보를 가져옴

        ///  - JY

        /// </summary>

        /// <returns></returns>

        public static DataTable GetProject()

        {

            DataTable dt = new DataTable();

            ID2Info id2Info = ID2Info.GetInstance();

            try

            {

                using (SQLiteConnection connection = new SQLiteConnection(@"Data Source = C:\ProgramData\Digital PID\Project.db", true))

                {

                    connection.Open();

                    if (connection.State.Equals(ConnectionState.Open))

                    {

                        using (SQLiteCommand cmd = connection.CreateCommand())

                        {

                            cmd.CommandText = "SELECT p.[Id], p.Name, p.Path, s.DBTypes_UID, s.Host, s.[User], s.[Password], s.FilePath FROM DBSettings s, Projects p WHERE s.Projects_UID = p.[Id]";

                            using (SQLiteDataReader dr = cmd.ExecuteReader())

                                dt.Load(dr);

                        }

                    }

                    connection.Close();

                }

            }

            catch (Exception ex)

            {

                System.Windows.Forms.MessageBox.Show(ex.Message);

            }

            dt.AcceptChanges();

            dt.DefaultView.Sort = "Name";

            dt = dt.DefaultView.ToTable();

            return dt;

        }

        /// <summary>

        ///  SQLite에 초기 DB 생성

        ///  - JY

        /// </summary>

        /// <returns></returns>

        public static bool ConnTestAndCreateTable()

        {

            bool result = false;

            ID2Info id2Info = ID2Info.GetInstance();

            using (IAbstractDatabase connection = id2Info.CreateConnection())

            {

                try

                {

                    var names = connection.GetTableNames();

                    var matched = names.FirstOrDefault(param => param == PSN_HEADER_SETTING);

                    Dictionary<string, string> dicColCheck = new Dictionary<string, string>();

                    dicColCheck.Add("GROUP_ID", "TEXT");

                    dicColCheck.Add("DESCRIPTION", "TEXT");

                    dicColCheck.Add("INDEX", "INTEGER");

                    dicColCheck.Add("NAME", "TEXT");

                    if (matched == null)

                    {

                        var query = $"CREATE TABLE {PSN_HEADER_SETTING} (GROUP_ID TEXT, DESCRIPTION TEXT, [INDEX] INTEGER, NAME TEXT)";

                        using (var cmd = connection.GetSqlStringCommand(query))

                        {

                            cmd.ExecuteNonQuery();

                        }

                    }

                    else

                    {

                        AddColumn(PSN_HEADER_SETTING, dicColCheck);

                    }

                    matched = names.FirstOrDefault(param => param == PSN_VENTDRAIN_SETTING);

                    dicColCheck.Clear();

                    dicColCheck.Add("GROUP_ID", "TEXT");

                    dicColCheck.Add("DESCRIPTION", "TEXT");

                    dicColCheck.Add("INDEX", "INTEGER");

                    dicColCheck.Add("NAME", "TEXT");

                    if (matched == null)

                    {

                        var query = $"CREATE TABLE {PSN_VENTDRAIN_SETTING} (GROUP_ID TEXT, DESCRIPTION TEXT, [INDEX] INTEGER, NAME TEXT)";

                        using (var cmd = connection.GetSqlStringCommand(query))

                        {

                            cmd.ExecuteNonQuery();

                        }

                    }

                    else

                    {

                        AddColumn(PSN_VENTDRAIN_SETTING, dicColCheck);

                    }

                    matched = names.FirstOrDefault(param => param == PSN_TOPOLOGY_RULE);

                    dicColCheck.Clear();

                    dicColCheck.Add("UID", "TEXT");

                    if (matched == null)

                    {

                        var query = $"CREATE TABLE {PSN_TOPOLOGY_RULE} (UID TEXT)";

                        using (var cmd = connection.GetSqlStringCommand(query))

                        {

                            cmd.ExecuteNonQuery();

                        }

                        DataTable topologyRule = new DataTable();

                        topologyRule.Columns.Add("NAME", typeof(string));

                        topologyRule.Rows.Add("FluidCode");

                        topologyRule.Rows.Add("-");

                        topologyRule.Rows.Add("PipingMaterialsClass");

                        topologyRule.Rows.Add("-");

                        topologyRule.Rows.Add("Tag Seq No");

                        SaveTopologyRule(topologyRule);

                    }

                    //else

                    //{

                    //    AddColumn(PSN_TOPOLOGY_RULE, dicColCheck);

                    //}

                    matched = names.FirstOrDefault(param => param == PSN_PIPESYSTEMNETWORK);

                    dicColCheck.Clear();

                    dicColCheck.Add("OID", "NVARCHAR(255)");

                    dicColCheck.Add("Type", "NVARCHAR(255)");

                    dicColCheck.Add("OrderNumber", "NVARCHAR(255)");

                    dicColCheck.Add("Pipeline_OID", "NVARCHAR(255)");

                    dicColCheck.Add("From_Data", "NVARCHAR(255)");

                    dicColCheck.Add("To_Data", "NVARCHAR(255)");

                    dicColCheck.Add("TopologySet_OID_Key", "NVARCHAR(125)");

                    dicColCheck.Add("PSNRevisionNumber", "NVARCHAR(255)");

                    dicColCheck.Add("IsValid", "INT");

                    dicColCheck.Add("Status", "NVARCHAR(255)");

                    dicColCheck.Add("PBS", "NVARCHAR(255)");

                    dicColCheck.Add("Drawings", "NVARCHAR(255)");

                    dicColCheck.Add("IncludingVirtualData", "NVARCHAR(10)");

                    dicColCheck.Add("PSNAccuracy", "REAL");

                    dicColCheck.Add("Pocket", "NVARCHAR(10)");

                    dicColCheck.Add("EGTag", "NVARCHAR(255)");

                    dicColCheck.Add("HasMLTags", "NVARCHAR(50)");

                    dicColCheck.Add("GroundLevel", "NVARCHAR(20)");

                    if (matched == null)

                    {

                        var query = $"CREATE TABLE {PSN_PIPESYSTEMNETWORK} (OID NVARCHAR(255), Type NVARCHAR(255), OrderNumber NVARCHAR(255), Pipeline_OID NVARCHAR(255), From_Data NVARCHAR(255), " +

                            "To_Data NVARCHAR(255), TopologySet_OID_Key NVARCHAR(125), PSNRevisionNumber NVARCHAR(255), IsValid INTEGER, Status NVARCHAR(255), PBS NVARCHAR(255), Drawings NVARCHAR(255), " +

                            "IncludingVirtualData NVARCHAR(10), PSNAccuracy REAL, Pocket NVARCHAR(10), EGTag NVARCHAR(255), HasMLTags NVARCHAR(50), GroundLevel NVARCHAR(50))";

                        using (var cmd = connection.GetSqlStringCommand(query))

                        {

                            cmd.ExecuteNonQuery();

                        }

                    }

                    else

                    {

                        AddColumn(PSN_PIPESYSTEMNETWORK, dicColCheck);

                    }

                    matched = names.FirstOrDefault(param => param == PSN_EQUIPMENT);

                    dicColCheck.Clear();

                    dicColCheck.Add("OID", "NVARCHAR(255)");

                    dicColCheck.Add("ItemTag", "NVARCHAR(255)");

                    dicColCheck.Add("Xcoords", "REAL");

                    dicColCheck.Add("Ycoords", "REAL");

                    if (matched == null)

                    {

                        var query = $"CREATE TABLE {PSN_EQUIPMENT} (OID NVARCHAR(255), ItemTag NVARCHAR(255), Xcoords REAL, Ycoords REAL)";

                        using (var cmd = connection.GetSqlStringCommand(query))

                        {

                            cmd.ExecuteNonQuery();

                        }

                    }

                    else

                    {

                        AddColumn(PSN_EQUIPMENT, dicColCheck);

                    }

                    dicColCheck.Clear();

                    dicColCheck.Add("OID", "NVARCHAR(255)");

                    dicColCheck.Add("ItemTag", "NVARCHAR(255)");

                    dicColCheck.Add("Xcoords", "REAL");

                    dicColCheck.Add("Ycoords", "REAL");

                    dicColCheck.Add("Equipment_OID", "NVARCHAR(255)");

                    dicColCheck.Add("Fluid", "NVARCHAR(255)");

                    dicColCheck.Add("NPD", "NVARCHAR(255)");

                    dicColCheck.Add("PMC", "NVARCHAR(255)");

                    dicColCheck.Add("Rotation", "REAL");

                    dicColCheck.Add("FlowDirection", "NVARCHAR(255)");

                    matched = names.FirstOrDefault(param => param == PSN_NOZZLE);

                    if (matched == null)

                    {

                        var query = $"CREATE TABLE {PSN_NOZZLE} (OID NVARCHAR(255), ItemTag NVARCHAR(255), Xcoords REAL, Ycoords REAL, Equipment_OID NVARCHAR(255), " +

                            "Fluid NVARCHAR(255), NPD NVARCHAR(255), PMC NVARCHAR(255), Rotation REAL, FlowDirection NVARCHAR(255))";

                        using (var cmd = connection.GetSqlStringCommand(query))

                        {

                            cmd.ExecuteNonQuery();

                        }

                    }

                    else

                    {

                        AddColumn(PSN_NOZZLE, dicColCheck);

                    }

                    matched = names.FirstOrDefault(param => param == PSN_FLUIDCODE);

                    dicColCheck.Clear();

                    dicColCheck.Add("UID", "NVARCHAR(50)");

                    dicColCheck.Add("Code", "NVARCHAR(255)");

                    dicColCheck.Add("Description", "NVARCHAR(255)");

                    dicColCheck.Add("Condition", "NVARCHAR(255)");

                    dicColCheck.Add("Remarks", "NVARCHAR(255)");

                    dicColCheck.Add("GroundLevel", "NVARCHAR(20)");

                    if (matched == null)

                    {

                        var query = $"CREATE TABLE {PSN_FLUIDCODE} (UID NVARCHAR(50), Code NVARCHAR(255), Description NVARCHAR(255), Condition NVARCHAR(255), Remarks NVARCHAR(255), GroundLevel NVARCHAR(20))";

                        using (var cmd = connection.GetSqlStringCommand(query))

                        {

                            cmd.ExecuteNonQuery();

                        }

                    }

                    else

                    {

                        AddColumn(PSN_FLUIDCODE, dicColCheck);

                    }

                    matched = names.FirstOrDefault(param => param == PSN_PIPINGMATLCLASS);

                    dicColCheck.Clear();

                    dicColCheck.Add("UID", "NVARCHAR(50)");

                    dicColCheck.Add("Priority", "INTEGER");

                    dicColCheck.Add("Code", "NVARCHAR(255)");

                    dicColCheck.Add("Description", "NVARCHAR(255)");

                    dicColCheck.Add("Condition", "NVARCHAR(255)");

                    dicColCheck.Add("Remarks", "NVARCHAR(255)");

                    dicColCheck.Add("GroundLevel", "NVARCHAR(20)");

                    if (matched == null)

                    {

                        var query = $"CREATE TABLE {PSN_PIPINGMATLCLASS} (UID NVARCHAR(50), Priority INTEGER, Code NVARCHAR(255), Description NVARCHAR(255), Condition NVARCHAR(255), Remarks NVARCHAR(255), GroundLevel NVARCHAR(20))";

                        using (var cmd = connection.GetSqlStringCommand(query))

                        {

                            cmd.ExecuteNonQuery();

                        }

                    }

                    else

                    {

                        AddColumn(PSN_PIPINGMATLCLASS, dicColCheck);

                    }

                    matched = names.FirstOrDefault(param => param == PSN_SEQUENCEDATA);

                    dicColCheck.Clear();

                    dicColCheck.Add("OID", "NVARCHAR(255)");

                    dicColCheck.Add("SERIALNUMBER", "NVARCHAR(255)");

                    dicColCheck.Add("PathItem_OID", "NVARCHAR(255)");

                    dicColCheck.Add("TopologySet_OID_Key", "NVARCHAR(255)");

                    if (matched == null)

                    {

                        var query = $"CREATE TABLE {PSN_SEQUENCEDATA} (OID NVARCHAR(255), SERIALNUMBER NVARCHAR(255), PathItem_OID NVARCHAR(255), TopologySet_OID_Key NVARCHAR(255))";

                        using (var cmd = connection.GetSqlStringCommand(query))

                        {

                            cmd.ExecuteNonQuery();

                        }

                    }

                    else

                    {

                        AddColumn(PSN_SEQUENCEDATA, dicColCheck);

                    }

                    matched = names.FirstOrDefault(param => param == PSN_PATHITEMS);

                    dicColCheck.Clear();

                    dicColCheck.Add("OID", "NVARCHAR(255)");

                    dicColCheck.Add("SequenceData_OID", "NVARCHAR(255)");

                    dicColCheck.Add("TopologySet_OID", "NVARCHAR(255)");

                    dicColCheck.Add("BranchTopologySet_OID", "NVARCHAR(255)");

                    dicColCheck.Add("PipeLine_OID", "NVARCHAR(255)");

                    dicColCheck.Add("ItemName", "NVARCHAR(255)");

                    dicColCheck.Add("ItemTag", "NVARCHAR(255)");

                    dicColCheck.Add("DESCRIPTION", "NVARCHAR(255)");

                    dicColCheck.Add("CLASS", "NVARCHAR(255)");

                    dicColCheck.Add("SUBCLASS", "NVARCHAR(255)");

                    dicColCheck.Add("TYPE", "NVARCHAR(255)");

                    dicColCheck.Add("MULTIWAY", "NVARCHAR(255)");

                    dicColCheck.Add("PIDNAME", "NVARCHAR(255)");

                    dicColCheck.Add("Equipment_OID", "NVARCHAR(255)");

                    dicColCheck.Add("NPD", "NVARCHAR(255)");

                    dicColCheck.Add("GROUPTAG", "NVARCHAR(255)");

                    dicColCheck.Add("PipeSystemNetwork_OID_ID2", "NVARCHAR(255)");

                    dicColCheck.Add("PipeRun_OID", "NVARCHAR(255)");

                    dicColCheck.Add("PipeSystemNetwork_OID", "NVARCHAR(255)");

                    dicColCheck.Add("EqpGroupTag", "NVARCHAR(255)");

                    dicColCheck.Add("MainLineTag", "NVARCHAR(255)");

                    dicColCheck.Add("EGTConnectedPoint", "INT");

                    dicColCheck.Add("EGFlowDirection", "NVARCHAR(10)");

                    if (matched == null)

                    {

                        var query = $"CREATE TABLE {PSN_PATHITEMS} (OID NVARCHAR(255), SequenceData_OID NVARCHAR(255), " +

                            "TopologySet_OID NVARCHAR(255), BranchTopologySet_OID NVARCHAR(255), PipeLine_OID NVARCHAR(255), ItemName NVARCHAR(255), ItemTag NVARCHAR(255), DESCRIPTION NVARCHAR(255), " +

                            "CLASS NVARCHAR(255), SUBCLASS NVARCHAR(255), TYPE NVARCHAR(255), MULTIWAY NVARCHAR(255), PIDNAME NVARCHAR(255), Equipment_OID NVARCHAR(255), NPD NVARCHAR(255), GROUPTAG NVARCHAR(255), PipeSystemNetwork_OID_ID2 NVARCHAR(255), PipeRun_OID NVARCHAR(255), " +

                            "PipeSystemNetwork_OID NVARCHAR(255), EqpGroupTag NVARCHAR(255), MainLineTag NVARCHAR(255), EGTConnectedPoint INT, EGFlowDirection NVARCHAR(10))";

                        using (var cmd = connection.GetSqlStringCommand(query))

                        {

                            cmd.ExecuteNonQuery();

                        }

                    }

                    else

                    {

                        AddColumn(PSN_PATHITEMS, dicColCheck);

                    }

                    matched = names.FirstOrDefault(param => param == PSN_VIEW);

                    dicColCheck.Clear();

                    dicColCheck.Add("OID", "TEXT");

                    if (matched == null)

                    {

                        var query = $"CREATE TABLE {PSN_VIEW} (OID TEXT)";

                        using (var cmd = connection.GetSqlStringCommand(query))

                        {

                            cmd.ExecuteNonQuery();

                        }

                    }

                    matched = names.FirstOrDefault(param => param == PSN_TOPOLOGYSET);

                    dicColCheck.Clear();

                    dicColCheck.Add("OID", "NVARCHAR(255)");

                    dicColCheck.Add("Type", "NVARCHAR(255)");

                    dicColCheck.Add("SubType", "NVARCHAR(255)");

                    dicColCheck.Add("HeadItemTag", "NVARCHAR(255)");

                    dicColCheck.Add("TailItemTag", "NVARCHAR(255)");

                    dicColCheck.Add("HeadItemSPID", "NVARCHAR(255)");

                    dicColCheck.Add("TailItemSPID", "NVARCHAR(255)");

                    if (matched == null)

                    {

                        var query = $"CREATE TABLE {PSN_TOPOLOGYSET} (OID NVARCHAR(255), Type NVARCHAR(255), SubType NVARCHAR(255), HeadItemTag NVARCHAR(255), TailItemTag NVARCHAR(255), HeadItemSPID NVARCHAR(255), TailItemSPID NVARCHAR(255))";

                        using (var cmd = connection.GetSqlStringCommand(query))

                        {

                            cmd.ExecuteNonQuery();

                        }

                    }

                    else

                    {

                        AddColumn(PSN_TOPOLOGYSET, dicColCheck);

                    }

                    matched = names.FirstOrDefault(param => param == PSN_TRANSFORMKEYWORD_SETTING);

                    dicColCheck.Clear();

                    dicColCheck.Add("INDEX", "INTEGER");

                    dicColCheck.Add("NAME", "TEXT");

                    dicColCheck.Add("KEYWORD", "TEXT");

                    if (matched == null)

                    {

                        var query = $"CREATE TABLE {PSN_TRANSFORMKEYWORD_SETTING} ([INDEX] INTEGER, [NAME] TEXT, [KEYWORD] TEXT)";

                        using (var cmd = connection.GetSqlStringCommand(query))

                        {

                            cmd.ExecuteNonQuery();

                        }

                    }

                    else

                    {

                        AddColumn(PSN_TRANSFORMKEYWORD_SETTING, dicColCheck);

                    }

                    matched = names.FirstOrDefault(param => param == PSN_MULTIWAY_SETTING);

                    dicColCheck.Clear();

                    dicColCheck.Add("INDEX", "INTEGER");

                    dicColCheck.Add("NAME", "TEXT");

                    if (matched == null)

                    {

                        var query = $"CREATE TABLE {PSN_MULTIWAY_SETTING} ([INDEX] INTEGER, [NAME] TEXT)";

                        using (var cmd = connection.GetSqlStringCommand(query))

                        {

                            cmd.ExecuteNonQuery();

                        }

                    }

                    else

                    {

                        AddColumn(PSN_MULTIWAY_SETTING, dicColCheck);

                    }

                    matched = names.FirstOrDefault(param => param == PSN_PIPELINE);

                    dicColCheck.Clear();

                    dicColCheck.Add("OID", "NVARCHAR(255)");

                    dicColCheck.Add("PipeSystem_OID", "NVARCHAR(255)");

                    dicColCheck.Add("FLUID", "NVARCHAR(255)");

                    dicColCheck.Add("PMC", "NVARCHAR(255)");

                    dicColCheck.Add("SEQNUMBER", "NVARCHAR(255)");

                    dicColCheck.Add("INSULATION", "NVARCHAR(255)");

                    dicColCheck.Add("FROM_DATA", "NVARCHAR(255)");

                    dicColCheck.Add("TO_DATA", "NVARCHAR(255)");

                    dicColCheck.Add("Unit", "NVARCHAR(100)");

                    if (matched == null)

                    {

                        var query = $"CREATE TABLE {PSN_PIPELINE} (OID NVARCHAR(255), PipeSystem_OID NVARCHAR(255), FLUID NVARCHAR(255), PMC NVARCHAR(255), SEQNUMBER NVARCHAR(255), INSULATION NVARCHAR(255), " +

                            $"FROM_DATA NVARCHAR(255), TO_DATA NVARCHAR(255), Unit NVARCHAR(100))";

                        using (var cmd = connection.GetSqlStringCommand(query))

                        {

                            cmd.ExecuteNonQuery();

                        }

                    }

                    else

                    {

                        AddColumn(PSN_PIPELINE, dicColCheck);

                    }

                    matched = names.FirstOrDefault(param => param == PSN_INSULATIONPURPOSE);

                    dicColCheck.Clear();

                    dicColCheck.Add("UID", "NVARCHAR(50)");

                    dicColCheck.Add("Code", "NVARCHAR(255)");

                    dicColCheck.Add("Description", "NVARCHAR(255)");

                    dicColCheck.Add("Remarks", "NVARCHAR(255)");

                    if (matched == null)

                    {

                        var query = $"CREATE TABLE {PSN_INSULATIONPURPOSE} (UID NVARCHAR(50), Code NVARCHAR(255), Description NVARCHAR(255), Remarks NVARCHAR(255))";

                        using (var cmd = connection.GetSqlStringCommand(query))

                        {

                            cmd.ExecuteNonQuery();

                        }

                    }

                    else

                    {

                        AddColumn(PSN_INSULATIONPURPOSE, dicColCheck);

                    }

                    matched = names.FirstOrDefault(param => param == PSN_VALVEGROUP_SETTING);

                    dicColCheck.Clear();

                    dicColCheck.Add("OID", "NVARCHAR(255)");

                    dicColCheck.Add("GroupType", "NVARCHAR(255)");

                    dicColCheck.Add("TagIdentifier", "NVARCHAR(50)");

                    dicColCheck.Add("AttributeName", "NVARCHAR(255)");

                    dicColCheck.Add("SppidSymbolName", "NVARCHAR(255)");

                    if (matched == null)

                    {

                        var query = $"CREATE TABLE {PSN_VALVEGROUP_SETTING} (OID NVARCHAR(255), GroupType NVARCHAR(255), TagIdentifier NVARCHAR(50), AttributeName NVARCHAR(255), SppidSymbolName NVARCHAR(255))";

                        using (var cmd = connection.GetSqlStringCommand(query))

                        {

                            cmd.ExecuteNonQuery();

                        }

                    }

                    else

                    {

                        AddColumn(PSN_VALVEGROUP_SETTING, dicColCheck);

                    }

                    matched = names.FirstOrDefault(param => param == PSN_PIPESYSTEM);

                    dicColCheck.Clear();

                    dicColCheck.Add("OID", "NVARCHAR(255)");

                    dicColCheck.Add("DESCRIPTION", "NVARCHAR(255)");

                    dicColCheck.Add("FLUID", "NVARCHAR(255)");

                    dicColCheck.Add("PMC", "NVARCHAR(255)");

                    dicColCheck.Add("PipeLineQty", "NVARCHAR(255)");

                    dicColCheck.Add("GroundLevel", "NVARCHAR(20)");

                    if (matched == null)

                    {

                        var query = $"CREATE TABLE {PSN_PIPESYSTEM} (OID NVARCHAR(255), DESCRIPTION NVARCHAR(255), FLUID NVARCHAR(255), PMC NVARCHAR(255), PipeLineQty NVARCHAR(255), GroundLevel NVARCHAR(20))";

                        using (var cmd = connection.GetSqlStringCommand(query))

                        {

                            cmd.ExecuteNonQuery();

                        }

                    }

                    else

                    {

                        AddColumn(PSN_PIPESYSTEM, dicColCheck);

                    }

                    matched = names.FirstOrDefault(param => param == PSN_NOPOCKETSETTING);

                    dicColCheck.Clear();

                    dicColCheck.Add("INDEX", "INTEGER");

                    dicColCheck.Add("TYPE", "TEXT");

                    dicColCheck.Add("NAME", "TEXT");

                    if (matched == null)

                    {

                        var query = $"CREATE TABLE {PSN_NOPOCKETSETTING} ([INDEX] INTEGER, [TYPE] TEXT, [NAME] TEXT)";

                        using (var cmd = connection.GetSqlStringCommand(query))

                        {

                            cmd.ExecuteNonQuery();

                        }

                    }

                    else

                    {

                        AddColumn(PSN_NOPOCKETSETTING, dicColCheck);

                    }

                    matched = names.FirstOrDefault(param => param == PSN_AIRFINCOOLERSETTING);

                    dicColCheck.Clear();

                    dicColCheck.Add("INDEX", "INTEGER");

                    dicColCheck.Add("TYPE", "TEXT");

                    dicColCheck.Add("TagIdentifier", "TEXT");

                    dicColCheck.Add("AttributeName", "TEXT");

                    dicColCheck.Add("NAME", "TEXT");

                    if (matched == null)

                    {

                        var query = $"CREATE TABLE {PSN_AIRFINCOOLERSETTING} ([INDEX] INTEGER, [TYPE] TEXT, [TagIdentifier] TEXT, [AttributeName] TEXT, [NAME] TEXT)";

                        using (var cmd = connection.GetSqlStringCommand(query))

                        {

                            cmd.ExecuteNonQuery();

                        }

                    }

                    else

                    {

                        AddColumn(PSN_AIRFINCOOLERSETTING, dicColCheck);

                    }

                    void AddColumn(string TableName, Dictionary<string, string> dicCol)

                    {

                        var colnames = connection.GetColumnNames(TableName);

                        bool check = false;

                        if (colnames != null)

                        {

                            foreach (KeyValuePair<string, string> col in dicCol)

                            {

                                check = false;

                                foreach (string c in colnames)

                                {

                                    if (col.Key.ToUpper().Equals(c.ToUpper()))

                                    {

                                        check = true;

                                        break;

                                    }

                                }

                                if (!check) //없으면 추가

                                {

                                    string i = string.Empty;

                                    if (col.Value.ToString().ToUpper().Contains("INT") || col.Value.ToString().ToUpper().Contains("REAL"))

                                        i = "DEFAULT 0";

                                    var query = $"ALTER TABLE " + TableName + " ADD [" + col.Key + "] " + col.Value + " " + i + ";";

                                    using (var cmd = connection.GetSqlStringCommand(query))

                                    {

                                        cmd.ExecuteNonQuery();

                                    }

                                }

                            }

                        }

                    }

                    result = true;

                }

                catch (Exception ex)

                {

                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);

                }

            }

            return result;

        }

        // ID2 DB 데이터

        /// <summary>

        /// ID2 데이타베이스에서 OPC 데이터를 조회

        /// </summary>

        /// <returns></returns>

        public static DataTable SelectOPCRelations()

        {

            DataTable dt = null;

            ID2Info id2Info = ID2Info.GetInstance();

            using (IAbstractDatabase connection = id2Info.CreateConnection())

            {

                try

                {

                    var query = "SELECT * FROM OPCRelations;";

                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))

                    {

                        dt = ds.Tables[0].Copy();

                    }

                }

                catch (Exception ex)

                {

                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);

                }

            }

            return dt;

        }

        /// <summary>

        /// ID2 데이타베이스에서 도면 데이터를 조회

        /// </summary>

        /// <returns></returns>

        public static DataTable SelectDrawings()

        {

            DataTable dt = null;

            ID2Info id2Info = ID2Info.GetInstance();

            using (IAbstractDatabase connection = id2Info.CreateConnection())

            {

                try

                {

                    var query = "SELECT DISTINCT NAME FROM [Drawings] order by Name";

                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))

                    {

                        dt = ds.Tables[0].Copy();

                    }

                }

                catch (Exception ex)

                {

                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);

                }

            }

            return dt;

        }

        public static DataTable AllDrawings()

        {

            DataTable dt = null;

            ID2Info id2Info = ID2Info.GetInstance();

            using (IAbstractDatabase connection = id2Info.CreateConnection())

            {

                try

                {

                    var query = "SELECT * FROM [Drawings]";

                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))

                    {

                        dt = ds.Tables[0].Copy();

                    }

                }

                catch (Exception ex)

                {

                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);

                }

            }

            return dt;

        }

        public static DataTable SelectLineProperties()

        {

            DataTable dt = null;

            ID2Info id2Info = ID2Info.GetInstance();

            using (IAbstractDatabase connection = id2Info.CreateConnection())

            {

                try

                {

                    var query = "SELECT * FROM LineProperties";

                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))

                    {

                        dt = ds.Tables[0].Copy();

                    }

                }

                catch (Exception ex)

                {

                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);

                }

            }

            return dt;

        }

        public static DataTable SelectFluidCode()

        {

            DataTable dt = null;

            ID2Info id2Info = ID2Info.GetInstance();

            using (IAbstractDatabase connection = id2Info.CreateConnection())

            {

                try

                {

                    var query = "SELECT * FROM FluidCode";

                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))

                    {

                        dt = ds.Tables[0].Copy();

                    }

                }

                catch (Exception ex)

                {

                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);

                }

            }

            return dt;

        }

        public static DataTable SelectPipingMaterialsClass()

        {

            DataTable dt = null;

            ID2Info id2Info = ID2Info.GetInstance();

            using (IAbstractDatabase connection = id2Info.CreateConnection())

            {

                try

                {

                    var query = "SELECT * FROM PipingMaterialsClass";

                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))

                    {

                        dt = ds.Tables[0].Copy();

                    }

                }

                catch (Exception ex)

                {

                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);

                }

            }

            return dt;

        }

        public static DataTable SelectPSNPIPINGMATLCLASS()

        {

            DataTable dt = null;

            ID2Info id2Info = ID2Info.GetInstance();

            using (IAbstractDatabase connection = id2Info.CreateConnection())

            {

                try

                {

                    var query = $"SELECT * FROM {PSN_PIPINGMATLCLASS}";

                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))

                    {

                        dt = ds.Tables[0].Copy();

                    }

                }

                catch (Exception ex)

                {

                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);

                }

            }

            return dt;

        }

        public static DataTable SelectInsulationPurpose()

        {

            DataTable dt = null;

            ID2Info id2Info = ID2Info.GetInstance();

            using (IAbstractDatabase connection = id2Info.CreateConnection())

            {

                try

                {

                    var query = "SELECT * FROM InsulationPurpose";

                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))

                    {

                        dt = ds.Tables[0].Copy();

                    }

                }

                catch (Exception ex)

                {

                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);

                }

            }

            return dt;

        }

        public static DataTable SelectPSNINSULATIONPURPOSE()

        {

            DataTable dt = null;

            ID2Info id2Info = ID2Info.GetInstance();

            using (IAbstractDatabase connection = id2Info.CreateConnection())

            {

                try

                {

                    var query = $"SELECT * FROM {PSN_INSULATIONPURPOSE}";

                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))

                    {

                        dt = ds.Tables[0].Copy();

                    }

                }

                catch (Exception ex)

                {

                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);

                }

            }

            return dt;

        }

        public static DataTable SelectNominalDiameter()

        {

            DataTable dt = null;

            ID2Info id2Info = ID2Info.GetInstance();

            using (IAbstractDatabase connection = id2Info.CreateConnection())

            {

                try

                {

                    var query = "SELECT * FROM NominalDiameter ORDER BY Metric DESC";

                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))

                    {

                        dt = ds.Tables[0].Copy();

                    }

                }

                catch (Exception ex)

                {

                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);

                }

            }

            ///TODO: need to check below code

            dt.Rows.RemoveAt(0);

            dt.Rows.RemoveAt(0);

            dt.Rows.RemoveAt(0);

            dt.Rows.RemoveAt(0);

            return dt;

        }

        public static DataTable SelectAllSymbolAttribute()

        {

            DataTable dt = null;

            ID2Info id2Info = ID2Info.GetInstance();

            using (IAbstractDatabase connection = id2Info.CreateConnection())

            {

                try

                {

                    var query = "SELECT B.Name AS SymbolName, Attribute AS SymbolAttribute, A.SymbolType_UID ,Attribute ,DisplayAttribute ,AttributeType, Expression FROM SymbolAttribute A INNER JOIN Symbol B ON A.SymbolType_UID = B.SymbolType_UID; ";

                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))

                    {

                        dt = ds.Tables[0].Copy();

                    }

                }

                catch (Exception ex)

                {

                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);

                }

            }

            return dt;

        }

        public static DataTable SelectSymbolAttribute()

        {

            DataTable dt = null;

            ID2Info id2Info = ID2Info.GetInstance();

            using (IAbstractDatabase connection = id2Info.CreateConnection())

            {

                try

                {

                    // var query = "SELECT DISTINCT Attribute, DisplayAttribute FROM SymbolAttribute;";

                    var query = "SELECT DISTINCT Attribute FROM SymbolAttribute;";

                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))

                    {

                        dt = ds.Tables[0].Copy();

                    }

                }

                catch (Exception ex)

                {

                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);

                }

            }

            return dt;

        }

        public static DataTable SelectTieInSymbolAttribute()

        {

            DataTable dt = null;

            ID2Info id2Info = ID2Info.GetInstance();

            using (IAbstractDatabase connection = id2Info.CreateConnection())

            {

                try

                {

                    // var query = "SELECT DISTINCT Attribute, DisplayAttribute FROM SymbolAttribute;";

                    var query = string.Empty;

                    if (id2Info.ID2DBType == ID2DB_Type.MSSQL)

                        query = "SELECT DISTINCT Attribute FROM SymbolAttribute WHERE SymbolType_UID IN(SELECT SymbolType_UID FROM Symbol WHERE CONVERT(varchar(Max), isnull([Name], '')) = 'TIEINPOINT');"; //얘는 UPPER안해줘도 나옴

                    else

                        query = "SELECT DISTINCT Attribute FROM SymbolAttribute WHERE SymbolType_UID IN(SELECT SymbolType_UID FROM Symbol WHERE ifnull(UPPER([Name]), '') = 'TIEINPOINT');";

                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))

                    {

                        dt = ds.Tables[0].Copy();

                    }

                }

                catch (Exception ex)

                {

                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);

                }

            }

            return dt;

        }

        public static DataTable SelectSymbolName()

        {

            DataTable dt = null;

            ID2Info id2Info = ID2Info.GetInstance();

            using (IAbstractDatabase connection = id2Info.CreateConnection())

            {

                try

                {

                    var query = "SELECT * FROM SymbolName;";

                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))

                    {

                        dt = ds.Tables[0].Copy();

                    }

                }

                catch (Exception ex)

                {

                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);

                }

            }

            return dt;

        }

        public static double[] GetDrawingSize()

        {

            double[] result = null;

            ID2Info id2Info = ID2Info.GetInstance();

            using (IAbstractDatabase connection = id2Info.CreateConnection())

            {

                try

                {

                    var query = "SELECT value FROM Configuration WHERE Section = 'Area' AND [Key] = 'Drawing';";

                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))

                    {

                        if (ds.Tables[0].Rows.Count == 1)

                        {

                            string value = ds.Tables[0].Rows[0][0].ToString();

                            string[] split = value.Split(new char[] { ',' });

                            result = new double[] {

                                Convert.ToDouble(Regex.Replace(split[0], @"[^0-9]", string.Empty)),

                                Convert.ToDouble(Regex.Replace(split[1], @"[^0-9]", string.Empty)),

                                Convert.ToDouble(Regex.Replace(split[2], @"[^0-9]", string.Empty)),

                                Convert.ToDouble(Regex.Replace(split[3], @"[^0-9]", string.Empty))

                                };

                            result = new double[] {

                                Math.Min(result[0], result[2]),

                                Math.Min(result[1], result[3]),

                                Math.Max(result[0], result[2]),

                                Math.Max(result[1], result[3])

                                };

                        }

                    }

                }

                catch (Exception ex)

                {

                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);

                }

            }

            return result;

        }

        public static DataTable GetEquipmentType()

        {

            DataTable dt = null;

            ID2Info id2Info = ID2Info.GetInstance();

            using (IAbstractDatabase connection = id2Info.CreateConnection())

            {

                try

                {

                    var query = "SELECT Type FROM SymbolType WHERE Category = 'Equipment';";

                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))

                    {

                        dt = ds.Tables[0].Copy();

                    }

                }

                catch (Exception ex)

                {

                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);

                }

            }

            return dt;

        }

        /// <summary>

        ///  Zoom 을 위하여 저장 T_PSN_VIEW 데이터로 PSN 데이터지만 Msqql 과 Sqlite 둘다 저장되어 있음

        ///  => ID2 가 DB 타입에 따라서 바라보는 DB가 mssql이냐 sqlite냐로 지정되기 때문에 zoom 기능을 그대로 사용하려면 해당 데이터는 psn도 id2 dbtype에 맞춰서 저장 및 로드해야함

        ///  => 더 이상 사용하지 않음

        /// </summary>

        /// <param name="values"></param>

        /// <returns></returns>

        public static bool SaveView(List<string> values)

        {

            ID2Info id2Info = ID2Info.GetInstance();

            bool result = true;

            using (IAbstractDatabase connection = id2Info.CreateConnection())

            {

                try

                {

                    using (var txn = connection.BeginTransaction())

                    {

                        try

                        {

                            var query = $"DELETE FROM {PSN_VIEW}";

                            connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);

                            foreach (string value in values)

                            {

                                query = $"INSERT INTO {PSN_VIEW} (OID) VALUES (@OID)";

                                var cmd = connection.GetSqlStringCommand(query);

                                AddWithValue(cmd, "@OID", value);

                                connection.ExecuteNonQuery(cmd, txn);

                            }

                            txn.Commit();

                        }

                        catch (Exception ex)

                        {

                            txn.Rollback();

                            result = false;

                        }

                    }

                }

                catch (Exception ex)

                {

                    System.Windows.Forms.MessageBox.Show(ex.Message);

                    result = false;

                }

            }

            return result;

        }

        public static bool DeleteView()

        {

            ID2Info id2Info = ID2Info.GetInstance();

            bool result = true;

            using (IAbstractDatabase connection = id2Info.CreateConnection())

            {

                try

                {

                    using (var txn = connection.BeginTransaction())

                    {

                        try

                        {

                            var query = $"DELETE FROM {PSN_VIEW}";

                            connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);

                            txn.Commit();

                        }

                        catch (Exception ex)

                        {

                            txn.Rollback();

                            result = false;

                        }

                    }

                }

                catch (Exception ex)

                {

                    System.Windows.Forms.MessageBox.Show(ex.Message);

                    result = false;

                }

            }

            return result;

        }

        //PSN Sqlite 

        public static DataTable SelectHeaderSetting()

        {

            DataTable dt = null;

            ID2Info id2Info = ID2Info.GetInstance();

            using (IAbstractDatabase connection = id2Info.CreateConnection())

            {

                try

                {

                    var query = $@"SELECT GROUP_ID, DESCRIPTION, [INDEX], NAME FROM {PSN_HEADER_SETTING};";

                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))

                    {

                        dt = ds.Tables[0].Copy();

                    }

                }

                catch (Exception ex)

                {

                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);

                }

            }

            return dt;

        }

        public static DataTable SelectVentDrainSetting()

        {

            DataTable dt = null;

            ID2Info id2Info = ID2Info.GetInstance();

            using (IAbstractDatabase connection = id2Info.CreateConnection())

            {

                try

                {

                    var query = $@"SELECT GROUP_ID, DESCRIPTION, [INDEX], NAME FROM {PSN_VENTDRAIN_SETTING};";

                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))

                    {

                        dt = ds.Tables[0].Copy();

                    }

                }

                catch (Exception ex)

                {

                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);

                }

            }

            return dt;

        }

        public static DataTable SelectKeywordsSetting()

        {

            DataTable dt = null;

            ID2Info id2Info = ID2Info.GetInstance();

            using (IAbstractDatabase connection = id2Info.CreateConnection())

            {

                try

                {

                    var query = $@"SELECT [KEYWORD], [INDEX], [NAME] FROM {PSN_TRANSFORMKEYWORD_SETTING};";

                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))

                    {

                        dt = ds.Tables[0].Copy();

                    }

                }

                catch (Exception ex)

                {

                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);

                }

            }

            return dt;

        }

        public static DataTable SelectMultiwaysSetting()

        {

            DataTable dt = null;

            ID2Info id2Info = ID2Info.GetInstance();

            using (IAbstractDatabase connection = id2Info.CreateConnection())

            {

                try

                {

                    var query = $@"SELECT [INDEX], [NAME] FROM {PSN_MULTIWAY_SETTING};";

                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))

                    {

                        dt = ds.Tables[0].Copy();

                    }

                }

                catch (Exception ex)

                {

                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);

                }

            }

            return dt;

        }

        public static DataTable SelectValveGroupItemsSetting()

        {

            DataTable dt = null;

            ID2Info id2Info = ID2Info.GetInstance();

            using (IAbstractDatabase connection = id2Info.CreateConnection())

            {

                try

                {

                    var query = $@"SELECT OID, GroupType, TagIdentifier, AttributeName, SppidSymbolName FROM {PSN_VALVEGROUP_SETTING};";

                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))

                    {

                        dt = ds.Tables[0].Copy();

                    }

                }

                catch (Exception ex)

                {

                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);

                }

            }

            return dt;

        }

        public static DataTable SelectEquipmentNoPocketSetting()

        {

            DataTable dt = null;

            ID2Info id2Info = ID2Info.GetInstance();

            using (IAbstractDatabase connection = id2Info.CreateConnection())

            {

                try

                {

                    var query = $@"SELECT [INDEX], [TYPE], [NAME] FROM {PSN_NOPOCKETSETTING};";

                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))

                    {

                        dt = ds.Tables[0].Copy();

                    }

                }

                catch (Exception ex)

                {

                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);

                }

            }

            return dt;

        }

        public static DataTable SelectAirFinCoolerSetting()

        {

            DataTable dt = null;

            ID2Info id2Info = ID2Info.GetInstance();

            using (IAbstractDatabase connection = id2Info.CreateConnection())

            {

                try

                {

                    var query = $@"SELECT [INDEX], [TYPE], [TagIdentifier], [AttributeName], [NAME] FROM {PSN_AIRFINCOOLERSETTING};";

                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))

                    {

                        dt = ds.Tables[0].Copy();

                    }

                }

                catch (Exception ex)

                {

                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);

                }

            }

            return dt;

        }

        public static bool SaveHeaderSetting(List<HeaderInfo> headerInfos)

        {

            ID2Info id2Info = ID2Info.GetInstance();

            using (IAbstractDatabase connection = id2Info.CreateConnection())

            {

                try

                {

                    using (var txn = connection.BeginTransaction())

                    {

                        var query = $"DELETE FROM {PSN_HEADER_SETTING}";

                        connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);

                        foreach (HeaderInfo headerInfo in headerInfos)

                        {

                            foreach (HeaderItem item in headerInfo.HeaderItems)

                            {

                                query = $"INSERT INTO {PSN_HEADER_SETTING} (GROUP_ID, DESCRIPTION, [INDEX], NAME) VALUES (@GROUP_ID, @DESCRIPTION, @INDEX, @NAME)";

                                var cmd = connection.GetSqlStringCommand(query);

                                AddWithValue(cmd, "@GROUP_ID", headerInfo.UID);

                                AddWithValue(cmd, "@DESCRIPTION", headerInfo.Description);

                                AddWithValue(cmd, "@INDEX", item.Index);

                                AddWithValue(cmd, "@NAME", item.Name);

                                connection.ExecuteNonQuery(cmd, txn);

                            }

                        }

                        txn.Commit();

                    }

                }

                catch (Exception ex)

                {

                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);

                    return false;

                }

            }

            return true;

        }

        public static bool SaveVentDrainSetting(List<VentDrainInfo> ventDrainInfos)

        {

            ID2Info id2Info = ID2Info.GetInstance();

            using (IAbstractDatabase connection = id2Info.CreateConnection())

            {

                using (var txn = connection.BeginTransaction())

                {

                    try

                    {

                        var query = $"DELETE FROM {PSN_VENTDRAIN_SETTING}";

                        connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);

                        foreach (VentDrainInfo ventDrainInfo in ventDrainInfos)

                        {

                            foreach (VentDrainItem item in ventDrainInfo.VentDrainItems)

                            {

                                query = $"INSERT INTO {PSN_VENTDRAIN_SETTING} (GROUP_ID, DESCRIPTION, [INDEX], NAME) VALUES (@GROUP_ID, @DESCRIPTION, @INDEX, @NAME)";

                                var cmd = connection.GetSqlStringCommand(query);

                                AddWithValue(cmd, "@GROUP_ID", ventDrainInfo.UID);

                                AddWithValue(cmd, "@DESCRIPTION", ventDrainInfo.Description);

                                AddWithValue(cmd, "@INDEX", item.Index);

                                AddWithValue(cmd, "@NAME", item.Name);

                                connection.ExecuteNonQuery(cmd, txn);

                            }

                        }

                        txn.Commit();

                    }

                    catch (Exception ex)

                    {

                        Log.Write(ex.Message + "\r\n" + ex.StackTrace);

                        return false;

                    }

                }

            }

            return true;

        }

        public static bool SaveValveGroupItemsSetting(List<ValveGroupItem> valveGroupItems)

        {

            ID2Info id2Info = ID2Info.GetInstance();

            using (IAbstractDatabase connection = id2Info.CreateConnection())

            {

                using (var txn = connection.BeginTransaction())

                {

                    try

                    {

                        var query = $"DELETE FROM {PSN_VALVEGROUP_SETTING}";

                        connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);

                        foreach (ValveGroupItem item in valveGroupItems)

                        {

                            query = $"INSERT INTO {PSN_VALVEGROUP_SETTING} (OID, GroupType, TagIdentifier, AttributeName, SppidSymbolName) " +

                                $"VALUES (@OID, @GroupType, @TagIdentifier, @AttributeName, @SppidSymbolName)";

                            var cmd = connection.GetSqlStringCommand(query);

                            AddWithValue(cmd, "@OID", item.OID);

                            AddWithValue(cmd, "@GroupType", item.GroupType);

                            AddWithValue(cmd, "@TagIdentifier", item.TagIdentifier);

                            AddWithValue(cmd, "@AttributeName", item.AttributeName);

                            AddWithValue(cmd, "@SppidSymbolName", item.SppidSymbolName);

                            connection.ExecuteNonQuery(cmd, txn);

                        }

                        txn.Commit();

                    }

                    catch (Exception ex)

                    {

                        Log.Write(ex.Message + "\r\n" + ex.StackTrace);

                        return false;

                    }

                }

            }

            return true;

        }

        public static bool SaveKeywordsSetting(List<KeywordItem> keywordItems)

        {

            ID2Info id2Info = ID2Info.GetInstance();

            using (IAbstractDatabase connection = id2Info.CreateConnection())

            {

                using (var txn = connection.BeginTransaction())

                {

                    try

                    {

                        var query = $"DELETE FROM {PSN_TRANSFORMKEYWORD_SETTING}";

                        connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);

                        foreach (KeywordItem item in keywordItems)

                        {

                            query = $"INSERT INTO {PSN_TRANSFORMKEYWORD_SETTING} ([INDEX], NAME, KEYWORD) VALUES (@INDEX, @NAME, @KEYWORD)";

                            var cmd = connection.GetSqlStringCommand(query);

                            AddWithValue(cmd, "@INDEX", item.Index);

                            AddWithValue(cmd, "@NAME", item.Name);

                            AddWithValue(cmd, "@KEYWORD", item.Keyword);

                            connection.ExecuteNonQuery(cmd, txn);

                        }

                        txn.Commit();

                    }

                    catch (Exception ex)

                    {

                        Log.Write(ex.Message + "\r\n" + ex.StackTrace);

                        return false;

                    }

                }

            }

            return true;

        }

        public static bool SaveMultiwaysSetting(List<MultiwayItem> multiwayItems)

        {

            ID2Info id2Info = ID2Info.GetInstance();

            using (IAbstractDatabase connection = id2Info.CreateConnection())

            {

                using (var txn = connection.BeginTransaction())

                {

                    try

                    {

                        var query = $"DELETE FROM {PSN_MULTIWAY_SETTING}";

                        connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);

                        foreach (MultiwayItem item in multiwayItems)

                        {

                            query = $"INSERT INTO {PSN_MULTIWAY_SETTING} ([INDEX], NAME) VALUES (@INDEX, @NAME)";

                            var cmd = connection.GetSqlStringCommand(query);

                            AddWithValue(cmd, "@INDEX", item.Index);

                            AddWithValue(cmd, "@NAME", item.Name);

                            connection.ExecuteNonQuery(cmd, txn);

                        }

                        txn.Commit();

                    }

                    catch (Exception ex)

                    {

                        Log.Write(ex.Message + "\r\n" + ex.StackTrace);

                        return false;

                    }

                }

            }

            return true;

        }

        public static bool SaveEquipmentNopocketSetting(List<EquipmentNoPocketItem> keywordItems)

        {

            ID2Info id2Info = ID2Info.GetInstance();

            using (IAbstractDatabase connection = id2Info.CreateConnection())

            {

                using (var txn = connection.BeginTransaction())

                {

                    try

                    {

                        var query = $"DELETE FROM {PSN_NOPOCKETSETTING}";

                        connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);

                        foreach (EquipmentNoPocketItem item in keywordItems)

                        {

                            query = $"INSERT INTO {PSN_NOPOCKETSETTING} ([INDEX], [TYPE], [NAME]) VALUES (@INDEX, @TYPE, @NAME)";

                            var cmd = connection.GetSqlStringCommand(query);

                            AddWithValue(cmd, "@INDEX", item.Index);

                            AddWithValue(cmd, "@TYPE", item.Type);

                            AddWithValue(cmd, "@NAME", item.Name);

                            connection.ExecuteNonQuery(cmd, txn);

                        }

                        txn.Commit();

                    }

                    catch (Exception ex)

                    {

                        Log.Write(ex.Message + "\r\n" + ex.StackTrace);

                        return false;

                    }

                }

            }

            return true;

        }

        public static bool SaveAirFinCoolerSetting(List<EquipmentAirFinCoolerItem> keywordItems)

        {

            ID2Info id2Info = ID2Info.GetInstance();

            using (IAbstractDatabase connection = id2Info.CreateConnection())

            {

                using (var txn = connection.BeginTransaction())

                {

                    try

                    {

                        var query = $"DELETE FROM {PSN_AIRFINCOOLERSETTING}";

                        connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);

                        foreach (EquipmentAirFinCoolerItem item in keywordItems)

                        {

                            query = $"INSERT INTO {PSN_AIRFINCOOLERSETTING} ([INDEX], [TYPE], [TagIdentifier], [AttributeName], [NAME]) VALUES (@INDEX, @TYPE, @TagIdentifier, @AttributeName, @NAME)";

                            var cmd = connection.GetSqlStringCommand(query);

                            AddWithValue(cmd, "@INDEX", item.Index);

                            AddWithValue(cmd, "@TYPE", item.Type);

                            AddWithValue(cmd, "@TagIdentifier", item.TagIdentifier);

                            AddWithValue(cmd, "@AttributeName", item.AttributeName);

                            AddWithValue(cmd, "@NAME", item.Name);

                            connection.ExecuteNonQuery(cmd, txn);

                        }

                        txn.Commit();

                    }

                    catch (Exception ex)

                    {

                        Log.Write(ex.Message + "\r\n" + ex.StackTrace);

                        return false;

                    }

                }

            }

            return true;

        }

        public static bool SaveTopologyRule(DataTable dt)

        {

            ID2Info id2Info = ID2Info.GetInstance();

            using (IAbstractDatabase connection = id2Info.CreateConnection())

            {

                using (var txn = connection.BeginTransaction())

                {

                    try

                    {

                        var query = $"DELETE FROM {PSN_TOPOLOGY_RULE}";

                        var cmd = connection.GetSqlStringCommand(query);

                        //connection.ExecuteNonQuery(connection.GetSqlStringCommand(query), txn);

                        connection.ExecuteNonQuery(cmd, txn);

                        foreach (DataRow row in dt.Rows)

                        {

                            query = $"INSERT INTO {PSN_TOPOLOGY_RULE} (UID) VALUES (@UID)";

                            cmd = connection.GetSqlStringCommand(query);

                            AddWithValue(cmd, "@UID", row["NAME"].ToString());

                            connection.ExecuteNonQuery(cmd, txn);

                        }

                        txn.Commit();

                    }

                    catch (Exception ex)

                    {

                        txn.Rollback();

                        Log.Write(ex.Message + "\r\n" + ex.StackTrace);

                        return false;

                    }

                }

            }

            return true;

        }

        public static DataTable SelectTopologyRule()

        {

            DataTable dt = null;

            ID2Info id2Info = ID2Info.GetInstance();

            using (IAbstractDatabase connection = id2Info.CreateConnection())

            {

                try

                {

                    var query = $"SELECT * FROM {PSN_TOPOLOGY_RULE}";

                    using (var ds = connection.ExecuteDataSet(connection.GetSqlStringCommand(query)))

                    {

                        dt = ds.Tables[0].Copy();

                    }

                }

                catch (Exception ex)

                {

                    Log.Write(ex.Message + "\r\n" + ex.StackTrace);

                }

            }