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;

using System.Data.SqlClient;

using System.IO;

using Newtonsoft.Json;

using AVEVA.PID.CustomizationUtility;

using AVEVA.PID.CustomizationUtility.Model;

namespace AVEVA.PID.CustomizationUtility.DB

{

    public class Project_DB

    {

        const string SPPID_DB_INFO_TABLE = "T_SPPID_CONNECTION_INFO";

        const string SPPID_SYMBOL_MAPPING_TABLE = "T_SPPID_SYMBOL_MAPPING";

        const string SPPID_ATTRIBUTE_MAPPING_TABLE = "T_SPPID_ATTRIBUTE_MAPPING";

        const string SPPID_SETTING_TABLE = "T_SPPID_SETTING_TABLE";

        const string SPPID_LABEL_INFO_TABLE = "T_SPPID_LABEL_INFO";

        const string SPPID_DRAWING_INFO = "T_SPPID_DRAWING_INFO";

        const string SPPID_OPC_INFO = "T_SPPID_OPC_INFO";

        const string LineProperties_TABLE = "LineProperties";

        const string LineTypes_TABLE = "LineTypes";

        const string SymbolType_TABLE = "SymbolType";

        const string SymbolAttribute_TABLE = "SymbolAttribute";

        const string Symbol_TABLE = "Symbol";

        const string OPCRelations_TABLE = "OPCRelations";

        private static SqlConnection GetSqlConnection()

        {

            Project_Info projectInfo = Project_Info.GetInstance();

            SqlConnection connection = null;

            try

            {

                connection = new SqlConnection(string.Format(CultureInfo.CurrentCulture,

                    @"Data Source = {0},{1}; Initial CataLog = {2}; User ID = {3}; Password = {4};",

                    projectInfo.ServerIP,

                    projectInfo.Port,

                    System.IO.Path.GetFileName(projectInfo.DefaultPath),

                    projectInfo.DBUser,

                    projectInfo.DBPassword));

                connection.Open();

            }

            catch (Exception ex)

            {

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

                if (connection != null)

                    connection.Dispose();

                connection = null;

            }

            return connection;

        }

        public static bool ConnTestAndCreateTable()

        {

            bool result = false;

            Project_Info projectInfo = Project_Info.GetInstance();

            if (projectInfo.DBType == ID2DB_Type.SQLite)

            {

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

                {

                    try

                    {

                        connection.Open();

                        if (connection.State == ConnectionState.Open)

                        {

                            using (SQLiteCommand cmd = connection.CreateCommand())

                            {

                            }

                            result = true;

                        }

                        connection.Close();

                    }

                    catch (Exception ex)

                    {

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

                    }

                    finally

                    {

                        connection.Dispose();

                    }

                }

            }

            else if (projectInfo.DBType == ID2DB_Type.MSSQL)

            {

                using (SqlConnection connection = GetSqlConnection())

                {

                    try

                    {

                        if (connection != null && connection.State == ConnectionState.Open)

                        {

                            using (SqlCommand cmd = connection.CreateCommand())

                            {

                            }

                            result = true;

                        }

                    }

                    catch (Exception ex)

                    {

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

                    }

                    finally

                    {

                        if (connection != null)

                            connection.Dispose();

                    }

                }

            }

            return result;

        }

        public static DataTable SelectID2SymbolTable()

        {

            DataTable dt = new DataTable();

            Project_Info projectInfo = Project_Info.GetInstance();

            if (projectInfo.DBType == ID2DB_Type.SQLite)

            {

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

                {

                    try

                    {

                        connection.Open();

                        using (SQLiteCommand cmd = connection.CreateCommand())

                        {

                            cmd.CommandText = @"SELECT * FROM Symbol";

                            using (SQLiteDataReader dr = cmd.ExecuteReader())

                                dt.Load(dr);

                        }

                        connection.Close();

                    }

                    catch (Exception ex)

                    {

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

                    }

                    finally

                    {

                        connection.Dispose();

                    }

                }

            }

            else if (projectInfo.DBType == ID2DB_Type.MSSQL)

            {

                using (SqlConnection connection = GetSqlConnection())

                {

                    try

                    {

                        if (connection != null && connection.State == ConnectionState.Open)

                        {

                            using (SqlCommand cmd = connection.CreateCommand())

                            {

                                cmd.CommandText = @"SELECT * FROM Symbol";

                                using (SqlDataReader dr = cmd.ExecuteReader())

                                    dt.Load(dr);

                            }

                            connection.Close();

                        }

                    }

                    catch (Exception ex)

                    {

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

                    }

                    finally

                    {

                        if (connection != null)

                            connection.Dispose();

                    }

                }

            }

            return dt;

        }

    }

}