using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace KHModbus
{
    public class DBConnectionSingleton
    {
        private static DBConnectionSingleton DBConnection;

        // Connection 정보를 세팅한다.
        private string DBURL = "192.168.1.17,1433";
        private string DBPASSWORD = "signus1!";
        //private string DBURL = "signus-smes.koreacentral.cloudapp.azure.com,14443";
        //private string DBPASSWORD = "tlrmsjtm~1@3";
        private string DBNAME = "U3SMES";
        private string DBID = "sa";
        SqlConnection mConn;

        public struct DBValue
        {
            public string name;
            public string value;
            public SqlDbType type;
        };


        public static DBConnectionSingleton Instance()
        {
            if (DBConnection == null)
            {
                DBConnection = new DBConnectionSingleton();
            }
            return DBConnection;
        }

        public bool isConnect()
        {
            if (mConn == null) return false;
            if(mConn.State == ConnectionState.Open)
            {
                return true;
            }
            return false;
        }

        public bool Connect()
        {
            string strconn = string.Format(" Data Source={0};Initial Catalog={1};Persist Security Info=false;Integrated Security=false;User ID={2};Password={3};enlist=true;Connect Timeout=2", DBURL, DBNAME, DBID, DBPASSWORD);
            mConn = new SqlConnection(strconn);
            try
            {
                // DB 연결
                mConn.Open();

                // 연결여부에 따라 다른 메시지를 보여준다
                if (mConn.State == ConnectionState.Open)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                return false;
            }
        }


        public bool Close()
        {
            try
            {
                // DB 연결해제
                mConn.Close();

                // 연결여부에 따라 다른 메시지를 보여준다
                if (mConn.State == ConnectionState.Closed)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                return false;
            }
        }


        public DBValue getParams(string name, string value)
        {
            DBValue d = new DBValue();

            d.value = value;
            d.name = name;

            return d;

        }


        public bool SetCommand(string sql, params DBValue[] data)
        {
            if (!isConnect())
            {
                Connect();
            }
            try
            {
                SqlCommand com = new SqlCommand(sql, mConn);
                com.CommandType = CommandType.StoredProcedure;
                for (int i = 0; i < data.Length; i++)
                {
                    SqlParameter pInput = new SqlParameter("@" + data[i].name, SqlDbType.NVarChar, data[i].value.Length);
                    pInput.Direction = ParameterDirection.Input;
                    pInput.Value = data[i].value;
                    com.Parameters.Add(pInput);
                    //com.Parameters.AddWithValue("@" + data[i].name, data[i].value);
                }

                com.ExecuteNonQuery();

                Close();
            }
            catch(Exception ex)
            {
                return false;
            }
            return true;

        }


        public DataTable GetSqlData(string sql)
        {
            try
            {
                if (!isConnect())
                {
                    if (!Connect())
                    {
                        return null;
                    }
                }
                SqlDataAdapter da = new SqlDataAdapter(sql, mConn);
                

                DataTable dt = new DataTable();


                da.Fill(dt);

                Close();
                return dt;
            }catch(Exception ex)
            {
                return null;
            }
        }





    }
}