csharp/NaverCloudPlatform/SqlServerDbaTool/NaverCloudPlatform-SqlServerDbaTool-ffd4834/HaTool/HaTool/Tools/UcExecuterSql.cs

UcExecuterSql.cs
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Threading;
using HaTool.Config;
using HaTool.Global;
using CsLib;
using Newtonsoft.Json;
using HaTool.Model.NCloud;
using LogClient;
using Newtonsoft.Json.Linq;
using HaTool.Model;
using System.Text.RegularExpressions;
using System.Data.SqlClient;
using System.IO;

namespace HaTool.Tools
{

    public partial clast UcExecuterSql : UserControl
    {
        private static readonly Lazy lazy =
            new Lazy(() => new UcExecuterSql(), LazyThreadSafetyMode.ExecutionAndPublication);

        public static UcExecuterSql Instance { get { return lazy.Value; } }

        LogClient.Config logClientConfig = LogClient.Config.Instance;
        DataManager dataManager = DataManager.Instance;
        FileDb fileDb = FileDb.Instance;
        TemplateManager templateManager;
        List loadBalancerInstances = new List();

        public UcExecuterSql()
        {
            InitializeComponent();
        }

        private async void LoadData(object sender, EventArgs e)
        {
            try
            {
                dataManager.LoadUserData();
                templateManager = new TemplateManager(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Tools", "TemplatesSql.txt"));
                templateManager.LoadTemplate();
                InitComboBoxScriptTemplates();
                comboBoxScriptTemplates.SelectedIndex = 0;

                await fileDb.ReadTable(FileDb.TableName.TBL_CLUSTER);
                await fileDb.ReadTable(FileDb.TableName.TBL_SERVER);
                await fileDb.ReadTable(FileDb.TableName.TBL_CLUSTER_SERVER);

                comboBoxloadBalancerName.Items.Clear();
                foreach (var a in fileDb.TBL_CLUSTER.Data)
                {
                    comboBoxloadBalancerName.Items.Add(a.Key.clusterName);
                }
                ComboBoxDefaultValueSetting();
                radioButtonDomain.Checked = true;
                checkBoxUsePrivateIp.Checked = false;
                FillConnectionInfo(comboBoxloadBalancerName.Text, "DOMAIN", checkBoxUsePrivateIp.Checked);
                DbListUpdate();
                
            }
            catch (Exception)
            {
            }

        }


        private void InitComboBoxScriptTemplates()
        {
            comboBoxScriptTemplates.Items.Clear();
            foreach (var a in templateManager.Templates)
            {
                comboBoxScriptTemplates.Items.Add(a.Key.ToString());
            }
        }

        private void FillConnectionInfo(string clusterName, string radioConnectionType, bool usePrivateIp)
        {                                                          // master slave domain     
            try
            {
                string ip = string.Empty;
                string port = string.Empty;
                string userid = string.Empty;
                string pastword = string.Empty;
                string db = "master";
                int connectTimeoutSec = 3;
                int commandTimeoutSec = 30;
                string masterServerName = string.Empty;
                string slaveServerName = string.Empty;
                string serverName = string.Empty;

                foreach (var a in fileDb.TBL_CLUSTER_SERVER.Data)
                {
                    if (a.Key.clusterName.Equals(clusterName) && a.Value.serverRole.Equals("MASTER", StringComparison.OrdinalIgnoreCase))
                        masterServerName = a.Key.serverName;
                    else if ((a.Key.clusterName.Equals(clusterName) && a.Value.serverRole.Equals("SLAVE", StringComparison.OrdinalIgnoreCase)))
                        slaveServerName = a.Key.serverName;
                }

                serverName = radioConnectionType.Equals("SLAVE", StringComparison.OrdinalIgnoreCase) == false ? masterServerName : slaveServerName;


                if (!serverName.Equals(""))
                {
                    if (usePrivateIp)
                        ip = fileDb.TBL_SERVER.Data[new TBL_SERVER_KEY { serverName = serverName }].serverPrivateIp;
                    else
                        ip = fileDb.TBL_SERVER.Data[new TBL_SERVER_KEY { serverName = serverName }].serverPublicIp;

                    if (radioConnectionType.Equals("DOMAIN", StringComparison.OrdinalIgnoreCase))
                        ip = fileDb.TBL_CLUSTER.Data[new TBL_CLUSTER_KEY { clusterName = clusterName }].domainName;

                    port = fileDb.TBL_SERVER.Data[new TBL_SERVER_KEY { serverName = serverName }].serverPort;
                    userid = fileDb.TBL_SERVER.Data[new TBL_SERVER_KEY { serverName = serverName }].serverUserId;
                    pastword = TranString.DecodeRijndael(
                                    fileDb.TBL_SERVER.Data[new TBL_SERVER_KEY { serverName = serverName }].serverPastword,
                                    LogClient.Config.Instance.GetCryptionKey());

                    textBoxServerName.Text = serverName;
                    textBoxIP.Text = ip;
                    textBoxPort.Text = port;
                    textBoxUserId.Text = userid;
                    textBoxPastword.Text = pastword;
                    textBoxConnectionTimeoutSec.Text = connectTimeoutSec.ToString();
                    textBoxCommandTimeoutSec.Text = commandTimeoutSec.ToString();
                    comboBoxDatabase.Text = db;
                }
                else
                {
                    textBoxServerName.Text = "";
                    textBoxIP.Text = "";
                    textBoxPort.Text = "";
                    textBoxUserId.Text = "";
                    textBoxPastword.Text = "";
                    textBoxConnectionTimeoutSec.Text = "";
                    textBoxCommandTimeoutSec.Text = "";
                    comboBoxDatabase.Text = "";
                }
            }
            catch (Exception ex)
            {
                //MessageBox.Show(ex.Message);
                throw;
            }

        }


        private async void buttonLoadServerList_Click(object sender, EventArgs e)
        {
            try
            {
                await LoadServerList();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private async Task LoadServerList()
        {
            try
            {

                await fileDb.ReadTable(FileDb.TableName.TBL_CLUSTER);
                await fileDb.ReadTable(FileDb.TableName.TBL_SERVER);
                await fileDb.ReadTable(FileDb.TableName.TBL_CLUSTER_SERVER);

                comboBoxloadBalancerName.Items.Clear();
                foreach (var a in fileDb.TBL_CLUSTER.Data)
                {
                    comboBoxloadBalancerName.Items.Add(a.Key.clusterName);
                }
                ComboBoxDefaultValueSetting();
            }
            catch (Exception)
            {
                throw;
            }
        }


        private void ComboBoxDefaultValueSetting()
        {
            if (comboBoxloadBalancerName.Items.Count > 0)
                comboBoxloadBalancerName.SelectedIndex = 0;
        }


        private void ConnectionTypeProperty_ClickOrChanged(object sender, EventArgs e)
        {
            try
            {
                string buttonText = string.Empty;

                if (radioButtonDomain.Checked)
                    checkBoxUsePrivateIp.Enabled = false;
                else
                    checkBoxUsePrivateIp.Enabled = true;

                if (radioButtonMaster.Checked)
                    buttonText = "MASTER";
                if (radioButtonSlave.Checked)
                    buttonText = "SLAVE";
                if (radioButtonDomain.Checked)
                    buttonText = "DOMAIN";

                FillConnectionInfo(comboBoxloadBalancerName.Text, buttonText, checkBoxUsePrivateIp.Checked);

                DbListUpdate();
            }
            catch (Exception ex)
            {
                
                if (ex.Message.Contains("Padding is invalid"))
                    MessageBox.Show("Check Encryption Key");
                else
                    MessageBox.Show(ex.Message);

            }
        }

        private bool QueryExecuter(string listStringQuery, int commandTimeout = 30)
        {
            bool bReturn = false;
            sbResultAll.Clear();
            List querys = TranString.ReadQuery(listStringQuery);

            sbResultAll.Append("-->>---------------------------------" + Environment.NewLine);
            sbResultAll.Append(DateTime.Now + Environment.NewLine);
            sbResultAll.Append("------------------------------------------------------------------");
                    sbResultAll.Append(query + Environment.NewLine);
                    sbResultAll.Append("--------------------------------->---------------------------------" + Environment.NewLine);
                                        sbResultAll.Append("--SQL Exception" + Environment.NewLine);
                                        sbResultAll.Append("-->>---------------------------------" + Environment.NewLine);
                                        for (int i = 0; i < ex.Errors.Count; i++)
                                        {
                                            sbResultAll.Append("Inner SqlException No #" + i + Environment.NewLine +
                                            "Message: " + ex.Errors[i].Message + Environment.NewLine +
                                            "Source: " + ex.Errors[i].Source + Environment.NewLine +
                                            "Procedure: " + ex.Errors[i].Procedure + Environment.NewLine);
                                        }
                                    }
                                    finally
                                    {
                                        sb.Append(Environment.NewLine);
                                        sbResultAll.Append(sb);
                                        sbResultAll.Append(string.Format("({0} {1} affected)" + Environment.NewLine + Environment.NewLine, recordCount, (recordCount == 1) ? "row" : "rows"));
                                    }
                                }
                                else
                                {
                                    string[] Query = query.Trim().Split(new[] { Environment.NewLine, " " }, StringSplitOptions.None);
                                    if (
                                        Query[0].Equals("update", StringComparison.OrdinalIgnoreCase)
                                        || Query[0].Equals("insert", StringComparison.OrdinalIgnoreCase)
                                        || Query[0].Equals("delete", StringComparison.OrdinalIgnoreCase)
                                        || Query[1].Equals("update", StringComparison.OrdinalIgnoreCase)
                                        || Query[1].Equals("insert", StringComparison.OrdinalIgnoreCase)
                                        || Query[1].Equals("delete", StringComparison.OrdinalIgnoreCase)
                                        )
                                        sbResultAll.Append(string.Format("({0} {1} affected)" + Environment.NewLine + Environment.NewLine, recordCount, (recordCount == 1) ? "row" : "rows"));
                                    else
                                        sbResultAll.Append(string.Format("Commands completed successfully." + Environment.NewLine + Environment.NewLine));
                                }
                                reader.NextResult();
                            } while (reader.HasRows);
                        }
                        conn.Close();
                        bReturn = true;
                    }

                    if (checkBoxResultUpdateByGo.Checked)
                        fastColoredTextBoxResult.InvokeIfRequired(s =>
                        {
                            s.Text = sbResultAll.ToString();
                        });
                }

                catch (SqlException ex)
                {
                    errorCnt++;
                    sbResultAll.Append("-->>---------------------------------" + Environment.NewLine);
                    sbResultAll.Append("--SQL Exception" + Environment.NewLine);
                    sbResultAll.Append("-->>---------------------------------" + Environment.NewLine);

                    for (int i = 0; i < ex.Errors.Count; i++)
                    {
                        sbResultAll.Append("SqlException No #" + i + Environment.NewLine +
                        "Message: " + ex.Errors[i].Message + Environment.NewLine +
                        "Source: " + ex.Errors[i].Source + Environment.NewLine +
                        "Procedure: " + ex.Errors[i].Procedure + Environment.NewLine);
                    }

                    sbResultAll.Append("--------------------------------->---------------------------------" + Environment.NewLine);
                    sbResultAll.Append("--Exception" + Environment.NewLine);
                    sbResultAll.Append("-->>---------------------------------" + Environment.NewLine);
                    sbResultAll.Append(ex.Message);
                    sbResultAll.Append("---------------------------------