ExportDataToFile/Form1.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Xml.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Npgsql;
using MySqlConnector;
using Oracle.ManagedDataAccess.Client;
using ManagementSystem.Utility;

namespace ExportDataToFile
{
    public partial class Form1 : Form
    {
        //參數設定
        int intExportIndex = -1;
        bool blSourceConnStatus = false;
        bool blTargetConnStatus = false;
        string strSourceClass = "";
        string strTargetClass = "";

        //MS-SQL
        SqlConnection sqlMSSourceConn = null; //new SqlConnection();
        SqlConnection sqlMSTargetConn = null; //new SqlConnection();
        //PostgreSQL
        NpgsqlConnection sqlPsgSourceConn = null; //new NpgsqlConnection();
        NpgsqlConnection sqlPsgTargetConn = null; //new NpgsqlConnection();
        //MySQL
        MySqlConnection sqlMySourceConn = null; //new MySqlConnection();
        MySqlConnection sqlMyTargetConn = null; //new MySqlConnection();
        //Oracle
        OracleConnection sqlOraSourceConn = null; //new OracleConnection();
        OracleConnection sqlOraTargetConn = null; //new OracleConnection();

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {            

        }

        private void btnSourceConnTest_Click(object sender, EventArgs e)
        {
            try
            {
                string strCheck = CheckSource();
                //查詢來源資料庫中的資料表
                string strIP = txtSourceIP.Text.Trim();
                string strPort = txtSourcePort.Text.Trim();
                string strDBName = txtSourceDBName.Text.Trim();
                string strID = txtSourceID.Text.Trim();
                string strPWD = txtSourcePWD.Text.Trim();
                DataTable dtResult = null;

                if (strCheck == "")
                {
                    if (cbSourceClass.Enabled)
                    {
                        LockSourceForm();

                        switch (cbSourceClass.SelectedItem.ToString().Trim())
                        {
                            case "MS-SQL":
                                //MS-SQL
                                sqlMSSourceConn = new SqlConnection();
                                sqlMSTargetConn = new SqlConnection();
                                
                                sqlMSSourceConn = MSSQLUtility.GetConn(strIP, strDBName, strID, strPWD, strPort);
                                if (sqlMSSourceConn == null)
                                {
                                    MessageBox.Show("連線失敗，請查詢連線資訊");
                                }
                                dtResult = ShowMSSQLTableList(sqlMSSourceConn);
                                strSourceClass = "MS-SQL";
                                Application.DoEvents();
                                break;
                            case "MySQL":                                
                                //MySQL
                                sqlMySourceConn = new MySqlConnection();
                                sqlMyTargetConn = new MySqlConnection();
                                
                                sqlMySourceConn = MySQLUtility.GetConn(strIP, strDBName, strID, strPWD, strPort);
                                if (sqlMySourceConn == null)
                                {
                                    MessageBox.Show("連線失敗，請查詢連線資訊");
                                }
                                dtResult = ShowMySQLTableList(sqlMySourceConn, strDBName);

                                strSourceClass = "MySQL";
                                Application.DoEvents();
                                break;
                            case "Oracle":
                                //Oracle
                                sqlOraSourceConn = new OracleConnection();
                                sqlOraTargetConn = new OracleConnection();

                                sqlOraSourceConn = OracleUtility.GetConn(strIP, strDBName, strID, strPWD, strPort);
                                if (sqlOraSourceConn == null)
                                {
                                    MessageBox.Show("連線失敗，請查詢連線資訊");
                                }
                                dtResult = ShowOracleTableList(sqlOraSourceConn, strDBName);

                                strSourceClass = "Oracle";
                                Application.DoEvents();
                                break;
                            case "PostgreSQL":
                                //PostgreSQL
                                sqlPsgSourceConn = new NpgsqlConnection();
                                sqlPsgTargetConn = new NpgsqlConnection();

                                sqlPsgSourceConn = PostgreSQLUtility.GetConn(strIP, strDBName, strID, strPWD, strPort);
                                if (sqlPsgSourceConn == null)
                                {
                                    MessageBox.Show("連線失敗，請查詢連線資訊");
                                }
                                dtResult = ShowPostgreSQLTablesList(sqlPsgSourceConn);
                                strSourceClass = "PostgreSQL";
                                Application.DoEvents();
                                break;
                        }
                        dgvExportList.DataSource = dtResult;
                        blSourceConnStatus = true;
                        EnableExport();  //啟動匯出鈕
                    }
                    else
                    {
                        UnLockSourceForm();
                    }
                }
                else
                {
                    MessageBox.Show(strCheck);
                }
            }
            catch (Exception ex)
            {
                UnLockSourceForm();
                MessageBox.Show("連線失敗，請查詢連線資訊");
                ErrorHandler.WriteErrorLog("Form1.btnSourceConnTest_Click", ex);
            }
        }
        
        private void btnTargetConnTest_Click(object sender, EventArgs e)
        {
            try
            {
                string strCheck = CheckTarget();
                //查詢目標資料庫中的資料表
                string strIP = txtTargetIP.Text.Trim();
                string strPort = txtTargetPort.Text.Trim();
                string strDBName = txtTargetDBName.Text.Trim();
                string strID = txtTargetID.Text.Trim();
                string strPWD = txtTargetPWD.Text.Trim();
                DataTable dtResult = null;

                if (strCheck == "")
                {
                    if (cbTargetClass.Enabled)
                    {
                        LockTargetForm();

                        switch (cbTargetClass.SelectedItem.ToString().Trim())
                        {
                            case "MS-SQL":
                                sqlMSTargetConn = MSSQLUtility.GetConn(strIP, strDBName, strID, strPWD, strPort);
                                dtResult = ShowMSSQLTableList(sqlMSTargetConn);
                                strTargetClass = "MS-SQL";                                
                                break;
                            case "MySQL":
                                sqlMyTargetConn = MySQLUtility.GetConn(strIP, strDBName, strID, strPWD, strPort);
                                dtResult = ShowMySQLTableList(sqlMyTargetConn, strDBName);
                                strTargetClass = "MySQL";
                                Application.DoEvents();
                                break;
                            case "Oracle":
                                sqlOraTargetConn = OracleUtility.GetConn(strIP, strDBName, strID, strPWD, strPort);
                                dtResult = ShowOracleTableList(sqlOraTargetConn, strDBName);
                                strTargetClass = "Oracle";                                
                                Application.DoEvents();
                                break;
                            case "PostgreSQL":
                                sqlPsgTargetConn = PostgreSQLUtility.GetConn(strIP, strDBName, strID, strPWD, strPort);
                                dtResult = ShowPostgreSQLTablesList(sqlPsgTargetConn);
                                strTargetClass = "PostgreSQL";
                                break;
                        }
                        
                        cbTargetTable.DataSource = dtResult;
                        cbTargetTable.SelectedIndex = -1;
                        blTargetConnStatus = true;
                        EnableExport();  //啟動匯出鈕
                        Application.DoEvents();
                    }
                    else
                    {
                        UnLockTargetForm();
                        Form1_FormClosing(null,null);
                    }
                }
                else
                {
                    MessageBox.Show(strCheck);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("連線失敗，請查詢連線資訊");
                ErrorHandler.WriteErrorLog("Form1.btnTargetConnTest_Click", ex);
            }
        }

        private void dgvExportList_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                if (intExportIndex != e.RowIndex)
                {
                    LocatedTarget(e.RowIndex);
                }
            }
            catch (Exception ex)
            {
                ErrorHandler.WriteErrorLog("Form1.dgvExportList_CellClick", ex);
            }
        }

        private void dgvExportList_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                var senderGrid = (DataGridView)sender;

                if (e.RowIndex >= 0)
                {
                    //清除該列資料
                    if (senderGrid.Columns[e.ColumnIndex] is DataGridViewButtonColumn && senderGrid.Columns[e.ColumnIndex].Name == "clCancel")
                    {
                        CleanExpRow(dgvExportList, e.RowIndex);
                    }
                    //顯示結果資料
                    if (senderGrid.Columns[e.ColumnIndex] is DataGridViewButtonColumn && senderGrid.Columns[e.ColumnIndex].Name == "clShow")
                    {
                        ShowRow(dgvExportList, e.RowIndex);
                    }
                }
            }
            catch (Exception ex)
            {
                ErrorHandler.WriteErrorLog("Form1.dgvExportList_CellContentClick", ex);
            }
        }
                
        private void cbTargetTable_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                DataTable dtResult = null;
                if (strTargetClass != "" && intExportIndex != -1)
                {
                    if (cbTargetTable.SelectedValue != null)
                    {
                        //重新修改DataGridView的ComboBox的內容
                        switch (strTargetClass)
                        {
                            case "MS-SQL":
                                dtResult = ShowMSSQLColumnList(sqlMSTargetConn, cbTargetTable.SelectedValue.ToString());
                                this.clTargetColumn.DisplayMember = "COLUMN_NAME";
                                break;
                            case "MySQL":
                                dtResult = ShowMySQLColumnList(sqlMyTargetConn, cbTargetTable.SelectedValue.ToString(), txtTargetDBName.Text.Trim());
                                this.clTargetColumn.DisplayMember = "Field";
                                break;
                            case "Oracle":
                                dtResult = ShowOracleColumnList(sqlOraTargetConn, cbTargetTable.SelectedValue.ToString());
                                this.clTargetColumn.DisplayMember = "COLUMN_NAME";
                                break;
                            case "PostgreSQL":
                                dtResult = ShowPostgreSQLColumnList(sqlPsgTargetConn, cbTargetTable.SelectedValue.ToString());
                                this.clTargetColumn.DisplayMember = "COLUMN_NAME";
                                break;
                        }
                        this.clTargetColumn.DataSource = dtResult;
                    }
                }
            }
            catch (Exception ex)
            {
                ErrorHandler.WriteErrorLog("Form1.cbTargetTable_SelectedIndexChanged", ex);
            }
        }

        private void dgvColumnMapping_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                var senderGrid = (DataGridView)sender;
                //觸發清除事件
                if (senderGrid.Columns[e.ColumnIndex] is DataGridViewButtonColumn && e.RowIndex >= 0)
                {
                    senderGrid.Rows[e.RowIndex].Cells["clExpColumn"].Value = false;
                    ((DataGridViewComboBoxCell)senderGrid.Rows[e.RowIndex].Cells["clTargetColumn"]).Value = null;                      
                }
            }
            catch (Exception ex)
            {
                ErrorHandler.WriteErrorLog("Form1.dgvColumnMapping_CellContentClick", ex);
            }
        }
                
        private void btnExport_Click(object sender, EventArgs e)
        {
            try
            {
                if (cbSourceClass.SelectedIndex > 0)
                {
                    string strPath = "";
                    sfPath.AddExtension = false;
                    sfPath.Filter = "All files (*.*)|*.*";
                    sfPath.FileName = "Output" + DateTime.Now.ToString("_yyyyMMddHHmmss");

                    if (sfPath.ShowDialog() == DialogResult.Cancel)  //如果按下取消，放棄後面動作
                        return;
                    
                    strPath = sfPath.FileName;
                    
                    //應依不同的來源，處理查詢字串
                    switch (strTargetClass)
                    {
                        case "MS-SQL":  //匯出MS-SQL檔
                            ExportToMSSQLData(sfPath.FileName,sfPath.DefaultExt);
                            break;
                        case "MySQL":
                            ExportToMySQLData(sfPath.FileName, sfPath.DefaultExt);
                            break;
                        case "Oracle":
                            ExportToOracleData(sfPath.FileName, sfPath.DefaultExt);
                            break;
                        case "PostgreSQL":
                            ExportToPostgreSQLData(sfPath.FileName, sfPath.DefaultExt);
                            break;
                    }
                }

            }
            catch (Exception ex)
            {
                ErrorHandler.WriteErrorLog("Form1.btnExport_Click", ex);
            }
        }

        private void Form1_FormClosing(object sender, FormClosingEventArgs e)
        {
            //MS-SQL
            //sqlMSSourceConn.Close();
            sqlMSSourceConn = null;
            //sqlMSTargetConn.Close();
            sqlMSTargetConn = null;
            //PostgreSQL
            //sqlPsgSourceConn.Close();
            sqlPsgSourceConn = null;
            //sqlPsgTargetConn.Close();
            sqlPsgTargetConn = null;
            //MySQL
            //sqlMySourceConn.Close();
            sqlMySourceConn = null;
            //sqlMyTargetConn.Close();
            sqlMyTargetConn = null;
            //Oracle
            //sqlOraSourceConn.Close();
            sqlOraSourceConn = null;
            //sqlOraTargetConn.Close();
            sqlOraTargetConn = null;
        }

        private void btnMapping_Click(object sender, EventArgs e)
        {
            if (dgvColumnMapping.DataSource == null)
                return;

            //自動預設產生所有的Mapping檔案
            dgvExportList.Rows[intExportIndex].Cells["clMappingData"].Value = GenMappingColumn();
            dgvExportList.Rows[intExportIndex].Cells["clExport"].Value = true;
            //設定來源與目標的資料表對應
            if (cbTargetTable.SelectedValue == null)
            {
                MessageBox.Show("請選擇目標資料表");
            }
            else
            {
                dgvExportList.Rows[intExportIndex].Cells["clTargetTable"].Value = cbTargetTable.SelectedValue.ToString();
                dgvExportList.Rows[intExportIndex].Cells["clWhere"].Value = txtWhere.Text.Trim();
            }
        }

        private void cbSourceClass_SelectedValueChanged(object sender, EventArgs e)
        {
            if (((ComboBox)sender).SelectedIndex != -1)
            {
                switch (cbSourceClass.SelectedItem.ToString())
                {
                    case "MS-SQL":
                        txtSourcePort.Text = "1433";
                        break;
                    case "MySQL":
                        txtSourcePort.Text = "3306";
                        break;
                    case "Oracle":
                        txtSourcePort.Text = "1521";
                        break;
                    case "PostgreSQL":
                        txtSourcePort.Text = "5432";
                        break;
                }
            }

        }

        private void cbTargetClass_SelectedValueChanged(object sender, EventArgs e)
        {
            if (((ComboBox)sender).SelectedIndex != -1)
            {
                switch (cbTargetClass.SelectedItem.ToString())
                {
                    case "MS-SQL":
                        txtTargetPort.Text = "1433";
                        break;
                    case "MySQL":
                        txtTargetPort.Text = "3306";
                        break;
                    case "Oracle":
                        txtTargetPort.Text = "1521";
                        break;
                    case "PostgreSQL":
                        txtTargetPort.Text = "5432";
                        break;
                }
            }
        }

        private void btnExportXML_Click(object sender, EventArgs e)
        {
            try
            {
                //參數設定
                string strXMLFile = ".\\File.xml";
                string strSourceTable = "";
                string strTargetTable = "";
                string strDeleteTable = "";
                string strWhere = "";
                string strMappingData = "";

                XElement xmlRoot = new XElement("Root");
                //匯出來源設定
                XElement xHead = new XElement("Head");
                xmlRoot.Add(xHead);
                if (cbSourceClass.SelectedIndex != -1)
                {
                    XElement xSourceDetail = new XElement("SourceDetail");
                    xSourceDetail.Add(new XAttribute("SourceClass", cbSourceClass.SelectedItem.ToString()));
                    xSourceDetail.Add(new XAttribute("SourceIP", txtSourceIP.Text.Trim()));
                    xSourceDetail.Add(new XAttribute("SourcePort", txtSourcePort.Text.Trim()));
                    xSourceDetail.Add(new XAttribute("SourceDBName", txtSourceDBName.Text.Trim()));
                    xSourceDetail.Add(new XAttribute("SourceID", txtSourceID.Text.Trim()));
                    xSourceDetail.Add(new XAttribute("SourcePWD", txtSourcePWD.Text.Trim()));
                    xHead.Add(xSourceDetail);
                }

                if (cbTargetClass.SelectedIndex != -1)
                {
                    XElement xTargetDetail = new XElement("TargetDetail");
                    xTargetDetail.Add(new XAttribute("TargetClass", cbTargetClass.SelectedItem.ToString()));
                    xTargetDetail.Add(new XAttribute("TargetIP", txtTargetIP.Text.Trim()));
                    xTargetDetail.Add(new XAttribute("TargetPort", txtTargetPort.Text.Trim()));
                    xTargetDetail.Add(new XAttribute("TargetDBName", txtTargetDBName.Text.Trim()));
                    xTargetDetail.Add(new XAttribute("TargetID", txtTargetID.Text.Trim()));
                    xTargetDetail.Add(new XAttribute("TargetPWD", txtTargetPWD.Text.Trim()));
                    xHead.Add(xTargetDetail);


                    XElement xDetail = new XElement("Detail");
                    xmlRoot.Add(xDetail);
                    //匯出內容
                    foreach (DataGridViewRow dgvRow in dgvExportList.Rows)
                    {
                        if (dgvRow.Cells["clExport"].Value != null && (bool)dgvRow.Cells["clExport"].Value == true)
                        {
                            XElement xColumnSetting = new XElement("ColumnSetting");
                            strSourceTable = dgvRow.Cells["clSourceTable"].Value.ToString();
                            strTargetTable = dgvRow.Cells["clTargetTable"].Value.ToString();
                            if (dgvRow.Cells["clTableDel"].Value != null && Convert.ToBoolean(dgvRow.Cells["clTableDel"].Value) != false)
                            {
                                strDeleteTable = "true";
                            }
                            else
                            {
                                strDeleteTable = "false";
                            }
                            strWhere = dgvRow.Cells["clWhere"].Value.ToString();
                            strMappingData = dgvRow.Cells["clMappingData"].Value.ToString();
                            xColumnSetting.Add(new XAttribute("SourceTable", strSourceTable));
                            xColumnSetting.Add(new XAttribute("TargetTable", strTargetTable));
                            xColumnSetting.Add(new XAttribute("DeleteTable", strDeleteTable));
                            xColumnSetting.Add(new XAttribute("Where", strWhere));
                            xColumnSetting.Add(new XAttribute("MappingData", strMappingData));
                            xDetail.Add(xColumnSetting);
                        }
                    }
                    xmlRoot.Save(strXMLFile);
                    MessageBox.Show("匯出成功");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("匯出失敗");
                ErrorHandler.WriteErrorLog("Form1.cs", ex);
            }
        }

        private void btnImportXML_Click(object sender, EventArgs e)
        {
            string strFilePath = "";
            string strXMLSourceTable = "";
            ofPath.Filter = "XML Files(*.xml) | *.xml";
            ofPath.InitialDirectory = ".\\";            

            if (ofPath.ShowDialog() == DialogResult.OK)
            {
                strFilePath = ofPath.FileName;
                if (File.Exists(strFilePath))
                {
                    XDocument xmlContent = XDocument.Load(strFilePath);
                    foreach (XElement xmlHeadData in xmlContent.Descendants("Head"))
                    {
                        //匯入來源設定
                        cbSourceClass.SelectedItem = xmlHeadData.Element("SourceDetail").Attribute("SourceClass").Value.ToString();
                        txtSourceIP.Text = xmlHeadData.Element("SourceDetail").Attribute("SourceIP").Value.ToString();
                        txtSourcePort.Text = xmlHeadData.Element("SourceDetail").Attribute("SourcePort").Value.ToString();
                        txtSourceDBName.Text = xmlHeadData.Element("SourceDetail").Attribute("SourceDBName").Value.ToString();
                        txtSourceID.Text = xmlHeadData.Element("SourceDetail").Attribute("SourceID").Value.ToString();
                        txtSourcePWD.Text = xmlHeadData.Element("SourceDetail").Attribute("SourcePWD").Value.ToString();
                        btnSourceConnTest_Click(null, null);                        

                        //匯入目標設定
                        cbTargetClass.SelectedItem = xmlHeadData.Element("TargetDetail").Attribute("TargetClass").Value.ToString();
                        txtTargetIP.Text = xmlHeadData.Element("TargetDetail").Attribute("TargetIP").Value.ToString();
                        txtTargetPort.Text = xmlHeadData.Element("TargetDetail").Attribute("TargetPort").Value.ToString();
                        txtTargetDBName.Text = xmlHeadData.Element("TargetDetail").Attribute("TargetDBName").Value.ToString();
                        txtTargetID.Text = xmlHeadData.Element("TargetDetail").Attribute("TargetID").Value.ToString();
                        txtTargetPWD.Text = xmlHeadData.Element("TargetDetail").Attribute("TargetPWD").Value.ToString();
                        btnTargetConnTest_Click(null, null);
                    }

                    foreach (XElement xmlDetail in xmlContent.Descendants("ColumnSetting"))
                    {
                        strXMLSourceTable = xmlDetail.Attribute("SourceTable").Value.ToString();
                        foreach (DataGridViewRow dgvRow in dgvExportList.Rows)
                        {
                            if (dgvRow.Cells["clSourceTable"].Value.ToString() == strXMLSourceTable)
                            {
                                dgvRow.Cells["clExport"].Value = true;
                                dgvRow.Cells["clTargetTable"].Value = xmlDetail.Attribute("TargetTable").Value.ToString();
                                dgvRow.Cells["clTableDel"].Value = Convert.ToBoolean(xmlDetail.Attribute("DeleteTable").Value.ToString());
                                dgvRow.Cells["clWhere"].Value = xmlDetail.Attribute("Where").Value.ToString();
                                dgvRow.Cells["clMappingData"].Value = xmlDetail.Attribute("MappingData").Value.ToString();
                                break;
                            }
                        }
                    }
                }
            }
            
        }

        private void btnClean_Click(object sender, EventArgs e)
        {
            UnLockSourceForm();
            cbSourceClass.SelectedIndex = -1;
            txtSourceIP.Text = "";
            txtSourcePort.Text = "";
            txtSourceDBName.Text = "";
            txtSourceID.Text = "";
            txtSourcePWD.Text = "";
            UnLockTargetForm();
            cbTargetClass.SelectedIndex = -1;
            txtTargetIP.Text = "";
            txtTargetPort.Text = "";
            txtTargetDBName.Text = "";
            txtTargetID.Text = "";
            txtTargetPWD.Text = "";
            cbTargetTable.SelectedIndex = -1;
            cbTargetTable.Enabled = false;
            CleanRows(dgvExportList);
            CleanRows(dgvColumnMapping);

            sqlMSSourceConn = null;
            sqlMSTargetConn = null;
            sqlPsgSourceConn = null;
            sqlPsgTargetConn = null;
            sqlMySourceConn = null;
            sqlMyTargetConn = null;
            sqlOraSourceConn = null;
            sqlOraTargetConn = null;

            btnImportXML.Enabled = true;
            btnExportXML.Enabled = false;
            btnExport.Enabled = false;
            btnMapping.Enabled = false;

            
        }
        #region 自定義功能

        #region MS-SQL
        public DataSet GetMSSQLResult(string strSQL,SqlConnection conn)
        {
            DataSet dsData = new DataSet();
            try
            {                
                using (SqlDataAdapter sqlAdapter = new SqlDataAdapter(strSQL, conn))
                {
                    if (conn.State == ConnectionState.Closed)  //判斷連線狀態
                    {
                        conn.Open();
                        blSourceConnStatus = true;
                    }
                    sqlAdapter.Fill(dsData, "Result");
                }
                
                return dsData;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private DataTable ShowMSSQLTableList(SqlConnection sqlConn)
        {
            try
            {
                string strGetMSSQLTableList = "Select [name] as TableName from sys.tables order by name";
                return MSSQLUtility.GetSQLResult(strGetMSSQLTableList, sqlConn);
            }
            catch (Exception ex)
            {
                ErrorHandler.WriteErrorLog("Form1.cs", ex);
                return null;
            }
        }

        private DataTable ShowMSSQLColumnList(SqlConnection sqlConn, string strTableName)
        {
            try
            {
                string strGetMSSQLColumnList = "Select COLUMN_NAME,DATA_TYPE From INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME ='" + strTableName + "' Order by ORDINAL_POSITION";
                return MSSQLUtility.GetSQLResult(strGetMSSQLColumnList, sqlConn);
            }
            catch (Exception ex)
            {
                ErrorHandler.WriteErrorLog("Form1.cs", ex);
                return null;
            }
        }

        private void ShowMSSQLTargetColumnList(SqlConnection sqlConn, string strTableName)
        {
            try
            {
                string strGetMSSQLColumnList = "Select COLUMN_NAME From INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME ='" + strTableName + "' Order by ORDINAL_POSITION";
                this.clTargetColumn.DisplayMember = "COLUMN_NAME";
                this.clTargetColumn.DataSource = MSSQLUtility.GetSQLResult(strGetMSSQLColumnList, sqlConn);
                Application.DoEvents();
            }
            catch (Exception ex)
            {
                ErrorHandler.WriteErrorLog("Form1.cs", ex);
            }
        }

        private void ExportToMSSQLData(string strPath,string subFileName)
        {
            ProgressForm pgsForm = new ProgressForm();
            int intDataCount = 0;
            int intMaxData = 0;
            int intProgress = 0;
            string strGenResult = "";
            string strSourceTable = "";
            string strTargetColumns = "";
            string strWhere = "";
            string strTargetTable = "";
            string strDeleteCommand = "";
            string strValues = "";
            string strFileCount = "";
            string[] strColumns = null;
            string[,] strColumnResults = new string[dgvExportList.Rows.Count, 2];
            string strSelectCommand = "";

            try
            {                
                //命令字串處理
                strTargetColumns = "";

                //取得每個檔案最大筆數
                if (Int32.Parse(txtMaxCount.Text.Trim()) > 0)
                {
                    intMaxData = Int32.Parse(txtMaxCount.Text.Trim());
                }
                else
                {
                    intMaxData = 1;
                }

                //取得欄位對應字串陣列                
                pgsForm.WindowState = FormWindowState.Normal;
                pgsForm.pbExport.Minimum = 0;
                pgsForm.Show();
                foreach (DataGridViewRow dgvRow in dgvExportList.Rows)
                {
                    if (dgvRow.Cells["clExport"].Value != null && (bool)dgvRow.Cells["clExport"].Value == true)
                    {
                        strSelectCommand = "";
                        strSourceTable = "";
                        strTargetColumns = "";
                        strDeleteCommand = "";
                        strValues = "";

                        DataTable dtResult = null;

                        //取得欄位對應
                        if (dgvRow.Cells["clMappingData"].Value != null)
                        {
                            strColumns = dgvRow.Cells["clMappingData"].Value.ToString().Split('|');
                        }
                        //取得來源Table
                        if (dgvRow.Cells["clSourceTable"].Value != null)
                        {
                            if (dgvRow.Cells["clSourceTable"].Value.ToString().Trim() != "")
                            {
                                strSourceTable = dgvRow.Cells["clSourceTable"].Value.ToString();
                            }
                        }
                        //取得目標Table
                        if (dgvRow.Cells["clTargetTable"].Value != null)
                        {
                            if (dgvRow.Cells["clTargetTable"].Value.ToString().Trim() != "")
                            {
                                strTargetTable = dgvRow.Cells["clTargetTable"].Value.ToString();
                            }
                        }

                        //取得Where條件
                        if (dgvRow.Cells["clWhere"].Value != null)
                        {
                            if (dgvRow.Cells["clWhere"].Value.ToString().Trim() != "")
                            {
                                strWhere = " " + dgvRow.Cells["clWhere"].Value.ToString();
                            }
                        }
                        #region 產生來源資料查詢命令
                        strGenResult = GenExpSourceCommand(strColumns, strSourceClass, strSourceTable, strWhere);
                        strSelectCommand = strGenResult.Substring(0, strGenResult.IndexOf('|')).Trim();
                        strTargetColumns = strGenResult.Substring(strGenResult.IndexOf('|') + 1).Trim();

                        switch (strSourceClass)
                        {
                            case "MS-SQL":
                                dtResult = GetMSSQLResult(strSelectCommand, sqlMSSourceConn).Tables["Result"];
                                break;
                            case "MySQL":
                                dtResult = GetMySQLResult(strSelectCommand, sqlMySourceConn).Tables["Result"];
                                break;
                            case "Oracle":
                                dtResult = GetOracleResult(strSelectCommand, sqlOraSourceConn).Tables["Result"];
                                break;
                            case "PostgreSQL":
                                dtResult = GetPostgreSQLResult(strSelectCommand.ToString(), sqlPsgSourceConn).Tables["Result"];
                                break;
                        }
                        #endregion
                        //產生匯出資料語法
                        if (dtResult.Rows.Count > 0)
                        {
                            //Progess bar
                            intProgress = 0;
                            pgsForm.pbExport.Value = 0;
                            pgsForm.pbExport.Maximum = dtResult.Rows.Count;

                            if (dgvRow.Cells["clTargetTable"].Value != null)
                            {
                                strTargetTable = dgvRow.Cells["clTargetTable"].Value.ToString();

                                //撰寫實體檔案
                                if (intMaxData == 1)
                                {
                                    //無設定最大筆數                                
                                    Utility.WriteFile(strPath + ".sql", string.Format("/*======================================================={0}======================================================*/", strTargetTable));
                                    Utility.WriteFile(strPath + ".sql", "Go");
                                    Utility.WriteFile(strPath + ".sql", "  ");
                                }
                                else
                                {
                                    //有設定最大筆數                                
                                    if (strFileCount == "0")
                                        strFileCount = "";

                                    Utility.WriteFile(strPath + strFileCount + ".sql", string.Format("/*======================================================={0}======================================================*/", strTargetTable));
                                    Utility.WriteFile(strPath + strFileCount + ".sql", "Go");
                                    Utility.WriteFile(strPath + strFileCount + ".sql", "  ");
                                }
                            }

                            //勾選清除目標資料表
                            if (dgvRow.Cells["clTableDel"].Value != null && Convert.ToBoolean(dgvRow.Cells["clTableDel"].Value) == false)
                            {
                                strDeleteCommand = string.Format("Delete From {0} ", strTargetTable);

                                if (dgvRow.Cells["clWhere"].Value != null)
                                {
                                    if (dgvRow.Cells["clWhere"].Value.ToString().Trim() != "")
                                    {
                                        strDeleteCommand += "Where 1=1 And " + dgvRow.Cells["clWhere"].Value.ToString();
                                    }
                                }
                                //撰寫實體檔案
                                if (intMaxData == 1)
                                {
                                    //無設定最大筆數                                
                                    Utility.WriteFile(strPath + ".sql", strDeleteCommand + ";");
                                    Utility.WriteFile(strPath + ".sql", "Go");
                                }
                                else
                                {
                                    //有設定最大筆數                                
                                    if (strFileCount == "0")
                                        strFileCount = "";

                                    Utility.WriteFile(strPath + strFileCount + ".sql", strDeleteCommand + ";");
                                    Utility.WriteFile(strPath + strFileCount + ".sql", "Go");
                                }
                            }

                            //處理筆數上限似資料匯出
                            foreach (DataRow dr in dtResult.Rows)
                            {
                                foreach (DataColumn dc in dtResult.Columns)
                                {
                                    if (strValues == "")
                                    {
                                        switch (dc.DataType.ToString().ToUpper())
                                        {
                                            case "SYSTEM.DATETIME":
                                                strValues = ConvertMSSQLDataType(DateTime.Parse(dr[dc].ToString()), dc.DataType.ToString());
                                                break;
                                            default:
                                                strValues = ConvertMSSQLDataType(dr[dc].ToString().Trim(), dc.DataType.ToString());
                                                break;
                                        }
                                    }
                                    else
                                    {
                                        switch (dc.DataType.ToString().ToUpper())
                                        {
                                            case "SYSTEM.DATETIME":
                                                strValues += "," + ConvertMSSQLDataType(DateTime.Parse(dr[dc].ToString()), dc.DataType.ToString());
                                                break;
                                            default:
                                                strValues += "," + ConvertMSSQLDataType(dr[dc].ToString().Trim(), dc.DataType.ToString());
                                                break;
                                        }
                                        
                                    }
                                }

                                //撰寫實體檔案
                                if (intMaxData == 1)
                                {
                                    //無設定最大筆數
                                    Utility.WriteFile(strPath + ".sql", string.Format("Insert Into {0} ({1}) Values ({2}) ", strTargetTable, strTargetColumns, strValues));
                                }
                                else
                                {
                                    //有設定最大筆數                                
                                    if (strFileCount == "0")
                                        strFileCount = "";
                                    Utility.WriteFile(strPath + strFileCount + ".sql", string.Format("Insert Into {0} ({1}) Values ({2}) ", strTargetTable, strTargetColumns, strValues));
                                }
                                intDataCount += 1;
                                intProgress += 1;
                                pgsForm.pbExport.Value = intProgress;  //變化Progess的狀態
                                Application.DoEvents();
                                strFileCount = (intDataCount / intMaxData).ToString();  //切分檔案
                                strValues = ""; //清除已經存在的資料                            
                            }

                            //撰寫實體檔案
                            if (intMaxData == 1)
                            {
                                //無設定最大筆數
                                Utility.WriteFile(strPath + ".sql", "Go");
                            }
                            else
                            {
                                //有設定最大筆數                                
                                if (strFileCount == "0")
                                    strFileCount = "";
                                Utility.WriteFile(strPath + strFileCount + ".sql", "Go");
                            }
                        }
                        
                    }
                }
                
                MessageBox.Show("匯出完成");
            }
            catch (Exception ex)
            {
                
                MessageBox.Show("匯出失敗");
                ErrorHandler.WriteErrorLog("Form1.ExportToMSSQLData", ex);
            }
            finally
            {
                pgsForm.Close();
            }
        }

        private string ConvertMSSQLColumnType(string strSourceCol, string strTargetCol, string strType)
        {
            switch (strType.ToUpper())
            {
                case "VARCHAR":
                    return "[" + strSourceCol + "] AS " + strTargetCol;
                case "VAR":
                    return "[" + strSourceCol + "] AS " + strTargetCol;
                case "INT":
                    return "[" + strSourceCol + "] AS " + strTargetCol;
                case "DATETIME":
                    return "Convert(varchar,[" + strSourceCol + "],21) AS " + strTargetCol;
                default:
                    return "[" + strSourceCol + "] AS " + strTargetCol;
            }
        }

        private string ConvertMSSQLDataType(string strData, string strType)
        {
            if (strData == "")
            {
                return "Null";
            }
            switch (strType.ToUpper())
            {
                case "STRING":
                    return "'" + strData.ToString().Replace("'","''") + "'";
                case "INT32":
                    return strData.ToString().Trim();
                case "DECIMAL":
                    return strData.ToString().Trim();
                default:
                    return "'" + strData.ToString().Replace("'", "''").Trim() + "'";
            }
        }

        private string ConvertMSSQLDataType(DateTime dtData, string strType)
        {
            return "'" + dtData.ToString("yyyy-MM-dd HH:mm:ss") + "'";
        }
        #endregion  //End of MS-SQL

        #region PostgreSQL
        public DataSet GetPostgreSQLResult(string strSQL, NpgsqlConnection conn)
        {
            DataSet dsData = new DataSet();
            try
            {
                using (NpgsqlDataAdapter sqlAdapter = new NpgsqlDataAdapter(strSQL, conn))
                {
                    if (conn.State == ConnectionState.Closed)  //判斷連線狀態
                    {
                        conn.Open();
                        blSourceConnStatus = true;
                    }
                    sqlAdapter.Fill(dsData, "Result");
                }

                return dsData;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private DataTable ShowPostgreSQLTablesList(NpgsqlConnection sqlConn)
        {
            try
            {
                string strGetSQLTableList = "SELECT tablename FROM pg_tables where schemaname = 'public' order by tablename";
                return PostgreSQLUtility.GetSQLResult(strGetSQLTableList, sqlConn);
            }
            catch (Exception ex)
            {
                ErrorHandler.WriteErrorLog("Form1.ShowPostgreSQLTablesList", ex);
                return null;
            }
        }

        private DataTable ShowPostgreSQLColumnList(NpgsqlConnection sqlConn, string strTableName)
        {
            try
            {
                string strGetMSSQLColumnList = "select Column_name, data_type from information_schema.columns where table_schema='public' And table_name ='" + strTableName + "' order by ordinal_position";
                return PostgreSQLUtility.GetSQLResult(strGetMSSQLColumnList, sqlConn);
            }
            catch (Exception ex)
            {
                ErrorHandler.WriteErrorLog("Form1.ShowPostgreSQLColumnList", ex);
                return null;
            }
        }

        private void ExportToPostgreSQLData(string strPath, string subFileName)
        {
            ProgressForm pgsForm = new ProgressForm();
            int intDataCount = 0;
            int intMaxData = 0;
            int intProgress = 0;
            string strGenResult = "";
            string strSourceTable = "";
            string strTargetColumns = "";
            string strWhere = "";
            string strTargetTable = "";
            string strDeleteCommand = "";
            string strValues = "";
            string strFileCount = "";
            string[] strColumns = null;
            string[,] strColumnResults = new string[dgvExportList.Rows.Count, 2];
            string strSelectCommand = "";
            try
            {
                

                //命令字串處理
                strTargetColumns = "";

                //取得每個檔案最大筆數
                if (Int32.Parse(txtMaxCount.Text.Trim()) > 0)
                {
                    intMaxData = Int32.Parse(txtMaxCount.Text.Trim());
                }
                else
                {
                    intMaxData = 1;
                }

                //取得欄位對應字串陣列                
                pgsForm.WindowState = FormWindowState.Normal;
                pgsForm.pbExport.Minimum = 0;
                pgsForm.Show();
                foreach (DataGridViewRow dgvRow in dgvExportList.Rows)
                {
                    if (dgvRow.Cells["clExport"].Value != null && (bool)dgvRow.Cells["clExport"].Value == true)
                    {
                        strSelectCommand = "";
                        strSourceTable = "";
                        strTargetColumns = "";
                        strDeleteCommand = "";
                        strValues = "";

                        DataTable dtResult = null;

                        //取得欄位對應
                        if (dgvRow.Cells["clMappingData"].Value != null)
                        {
                            strColumns = dgvRow.Cells["clMappingData"].Value.ToString().Split('|');
                        }
                        //取得來源Table
                        if (dgvRow.Cells["clSourceTable"].Value != null)
                        {
                            if (dgvRow.Cells["clSourceTable"].Value.ToString().Trim() != "")
                            {
                                strSourceTable = dgvRow.Cells["clSourceTable"].Value.ToString();
                            }
                        }
                        //取得Where條件
                        if (dgvRow.Cells["clWhere"].Value != null)
                        {
                            if (dgvRow.Cells["clWhere"].Value.ToString().Trim() != "")
                            {
                                strWhere = " " + dgvRow.Cells["clWhere"].Value.ToString();
                            }
                        }
                        #region 產生來源資料查詢命令
                        strGenResult = GenExpSourceCommand(strColumns, strSourceClass, strSourceTable, strWhere);
                        strSelectCommand = strGenResult.Substring(0, strGenResult.IndexOf('|')).Trim();
                        strTargetColumns = strGenResult.Substring(strGenResult.IndexOf('|') + 1).Trim();

                        switch (strSourceClass)
                        {
                            case "MS-SQL":
                                dtResult = GetMSSQLResult(strSelectCommand, sqlMSSourceConn).Tables["Result"];
                                break;
                            case "MySQL":
                                dtResult = GetMySQLResult(strSelectCommand, sqlMySourceConn).Tables["Result"];
                                break;
                            case "Oracle":
                                dtResult = GetOracleResult(strSelectCommand, sqlOraSourceConn).Tables["Result"];
                                break;
                            case "PostgreSQL":
                                dtResult = GetPostgreSQLResult(strSelectCommand.ToString(), sqlPsgSourceConn).Tables["Result"];
                                break;
                        }
                        #endregion
                        //產生匯出資料語法
                        if (dtResult.Rows.Count > 0)
                        {
                            //Progess bar
                            intProgress = 0;
                            pgsForm.pbExport.Value = 0;
                            pgsForm.pbExport.Maximum = dtResult.Rows.Count;

                            if (dgvRow.Cells["clTargetTable"].Value != null)
                            {
                                strTargetTable = dgvRow.Cells["clTargetTable"].Value.ToString();
                                //strFileCount = (intDataCount / intMaxData).ToString();  //切分檔案

                                //撰寫實體檔案
                                if (intMaxData == 1)
                                {
                                    //無設定最大筆數                                
                                    Utility.WriteFile(strPath + ".sql", string.Format("/*======================================================={0}======================================================*/", strTargetTable));
                                    Utility.WriteFile(strPath + ".sql", "Go");
                                    Utility.WriteFile(strPath + ".sql", "  ");
                                }
                                else
                                {
                                    //有設定最大筆數                                
                                    if (strFileCount == "0")
                                        strFileCount = "";

                                    Utility.WriteFile(strPath + strFileCount + ".sql", string.Format("/*======================================================={0}======================================================*/", strTargetTable));
                                    Utility.WriteFile(strPath + strFileCount + ".sql", "  ");
                                }
                            }

                            //勾選清除目標資料表
                            if (dgvRow.Cells["clTableDel"].Value != null && Convert.ToBoolean(dgvRow.Cells["clTableDel"].Value) == false)
                            {
                                strDeleteCommand = string.Format("Delete From \"{0}\" ;", strTargetTable);

                                if (dgvRow.Cells["clWhere"].Value != null)
                                {
                                    if (dgvRow.Cells["clWhere"].Value.ToString().Trim() != "")
                                    {
                                        strDeleteCommand += "Where 1=1 And " + dgvRow.Cells["clWhere"].Value.ToString();
                                    }
                                }
                                //撰寫實體檔案
                                if (intMaxData == 1)
                                {
                                    //無設定最大筆數                                
                                    Utility.WriteFile(strPath + ".sql", strDeleteCommand);
                                }
                                else
                                {
                                    //有設定最大筆數                                
                                    if (strFileCount == "0")
                                        strFileCount = "";

                                    Utility.WriteFile(strPath + strFileCount + ".sql", strDeleteCommand);
                                }
                            }

                            //處理筆數上限似資料匯出
                            foreach (DataRow dr in dtResult.Rows)
                            {
                                foreach (DataColumn dc in dtResult.Columns)
                                {
                                    if (strValues == "")
                                    {
                                        switch (dc.DataType.ToString().ToUpper())
                                        {
                                            case "TIMESTAMP WITH TIME ZONE":
                                                strValues = ConvertMSSQLDataType(DateTime.Parse(dr[dc].ToString()), dc.DataType.ToString());
                                                break;
                                            case "TIMESTAMP WITHOUT TIME ZONE":
                                                strValues = ConvertMSSQLDataType(DateTime.Parse(dr[dc].ToString()), dc.DataType.ToString());
                                                break;
                                            default:
                                                strValues = ConvertMSSQLDataType(dr[dc].ToString().Trim(), dc.DataType.ToString());
                                                break;
                                        }
                                    }
                                    else
                                    {
                                        switch (dc.DataType.ToString().ToUpper())
                                        {
                                            case "TIMESTAMP WITH TIME ZONE":
                                                strValues += "," + ConvertMSSQLDataType(DateTime.Parse(dr[dc].ToString()), dc.DataType.ToString());
                                                break;
                                            case "TIMESTAMP WITHOUT TIME ZONE":
                                                strValues += "," + ConvertMSSQLDataType(DateTime.Parse(dr[dc].ToString()), dc.DataType.ToString());
                                                break;
                                            default:
                                                strValues += "," + ConvertMSSQLDataType(dr[dc].ToString().Trim(), dc.DataType.ToString());
                                                break;
                                        }
                                        
                                    }
                                }

                                //撰寫實體檔案
                                if (intMaxData == 1)
                                {
                                    //無設定最大筆數
                                    Utility.WriteFile(strPath + ".sql", string.Format("Insert Into \"{0}\" ({1}) Values ({2}) ;", strTargetTable, strTargetColumns, strValues));
                                }
                                else
                                {
                                    //有設定最大筆數                                
                                    if (strFileCount == "0")
                                        strFileCount = "";
                                    Utility.WriteFile(strPath + strFileCount + ".sql", string.Format("Insert Into \"{0}\" ({1}) Values ({2}) ;", strTargetTable, strTargetColumns, strValues));
                                }
                                intDataCount += 1;
                                intProgress += 1;
                                pgsForm.pbExport.Value = intProgress;  //變化Progess的狀態
                                Application.DoEvents();
                                strFileCount = (intDataCount / intMaxData).ToString();  //切分檔案
                                strValues = ""; //清除已經存在的資料                            
                            }
                        }

                    }
                }
                MessageBox.Show("匯出完成");
            }
            catch (Exception ex)
            {
                MessageBox.Show("匯出失敗");
                ErrorHandler.WriteErrorLog("Form1.ExportToPostgreSQLData", ex);
            }
            finally
            {
                pgsForm.Close();
            }
        }

        private string ConvertPostgreSQLColumnType(string strSourceCol, string strTargetCol, string strType)
        {
            switch (strType.ToUpper())
            {
                case "TEXT":
                    return strSourceCol + " AS " + strTargetCol;
                case "CHARATER VARYING":
                    return strSourceCol + " AS " + strTargetCol;
                case "DOUBLE PRECISION":
                    return strSourceCol + " AS " + strTargetCol;
                case "BOOLEAN":
                    return strSourceCol + " AS " + strTargetCol;
                case "BIGINT":
                    return strSourceCol + " AS " + strTargetCol;
                case "INTEGER":
                    return strSourceCol + " AS " + strTargetCol;
                //case "TIMESTAMP WITH TIME ZONE":
                //    return "to_char(" + strSourceCol + ", 'yyyy-MM-dd hh24:mm:ss')";
                //case "TIMESTAMP WITHOUT TIME ZONE":
                //    return "to_char(" + strSourceCol + ", 'yyyy-MM-dd hh24:mm:ss')";

                default:
                    return strSourceCol + " AS " + strTargetCol;
            }
        }

        private string ConvertPostgreSQLDataType(string strData, string strType)
        {
            if (strData == "")
            {
                return "Null";
            }
            switch (strType.ToUpper())
            {
                case "SYSTEM.STRING":
                    return "'" + strData.ToString().Replace("'","''").Trim() + "'";
                case "SYSTEM.INT32":
                    return strData.ToString().Trim();
                //case "SYSTEM.DATETIME":
                    //return "'" + strData.ToString().Trim() + "'";
                case "SYSTEM.BOOLEAN":
                    return strData.ToString().Trim();
                default:
                    return "'" + strData.ToString().Replace("'", "''").Trim() + "'";
            }
        }

        private string ConvertPostgreSQLDataType(DateTime dtData, string strType)
        {
            return "'" + dtData.ToString("yyyy-MM-dd HH:mm:ss") + "'";
        }

        #endregion  //End of PostgreSQL

        #region MySQL
        public DataSet GetMySQLResult(string strSQL, MySqlConnection conn)
        {
            DataSet dsData = new DataSet();
            try
            {
                using (MySqlDataAdapter sqlAdapter = new MySqlDataAdapter(strSQL, conn))
                {
                    if (conn.State == ConnectionState.Closed)  //判斷連線狀態
                    {
                        conn.Open();
                        blSourceConnStatus = true;
                    }
                    sqlAdapter.Fill(dsData, "Result");
                }

                return dsData;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private DataTable ShowMySQLTableList(MySqlConnection sqlConn, string strDBName)
        {
            try
            {
                DataTable dtTemp = null;
                string strGetMSSQLTableList = "Show tables  from " + strDBName;
                dtTemp = MySQLUtility.GetSQLResult(strGetMSSQLTableList, sqlConn);
                dtTemp.Columns[0].ColumnName = "TableName";
                return dtTemp;
            }
            catch (Exception ex)
            {
                ErrorHandler.WriteErrorLog("Form1.ShowMySQLTableList", ex);
                return null;
            }
        }

        private DataTable ShowMySQLColumnList(MySqlConnection sqlConn, string strTableName, string strDBName)
        {
            try
            {
                string strGetMSSQLColumnList = "Show COLUMNS FROM " + strTableName + " FROM " + strDBName;
                return MySQLUtility.GetSQLResult(strGetMSSQLColumnList, sqlConn);
            }
            catch (Exception ex)
            {
                ErrorHandler.WriteErrorLog("Form1.cs", ex);
                return null;
            }
        }

        private void ShowMySQLTargetColumnList(MySqlConnection sqlConn, string strTableName, string strDBName)
        {
            try
            {
                string strGetMySQLColumnList = "Show COLUMNS FROM " + strTableName + " FROM " + strDBName;
                DataTable dtTemp = MySQLUtility.GetSQLResult(strGetMySQLColumnList, sqlConn);
                dtTemp.Columns[0].ColumnName = "COLUMN_NAME";
                this.clTargetColumn.DisplayMember = "COLUMN_NAME";
                this.clTargetColumn.DataSource = dtTemp;
                Application.DoEvents();
            }
            catch (Exception ex)
            {
                ErrorHandler.WriteErrorLog("Form1.ShowMySQLTargetColumnList", ex);
            }
        }

        private void ExportToMySQLData(string strPath, string subFileName)
        {
            ProgressForm pgsForm = new ProgressForm();
            int intDataCount = 0;
            int intMaxData = 0;
            int intProgress = 0;
            string strGenResult = "";
            string strSourceTable = "";
            string strTargetColumns = "";
            string strWhere = "";
            string strTargetTable = "";
            string strDeleteCommand = "";
            string strValues = "";
            string strFileCount = "";
            string[] strColumns = null;
            string[,] strColumnResults = new string[dgvExportList.Rows.Count, 2];
            string strSelectCommand = "";
            try
            {
                //命令字串處理
                strTargetColumns = "";

                //取得每個檔案最大筆數
                if (Int32.Parse(txtMaxCount.Text.Trim()) > 0)
                {
                    intMaxData = Int32.Parse(txtMaxCount.Text.Trim());
                }
                else
                {
                    intMaxData = 1;
                }

                //取得欄位對應字串陣列                
                pgsForm.WindowState = FormWindowState.Normal;
                pgsForm.pbExport.Minimum = 0;
                pgsForm.Show();
                foreach (DataGridViewRow dgvRow in dgvExportList.Rows)
                {
                    if (dgvRow.Cells["clExport"].Value != null && (bool)dgvRow.Cells["clExport"].Value == true)
                    {
                        strSelectCommand = "";
                        strSourceTable = "";
                        strTargetColumns = "";
                        strDeleteCommand = "";
                        strValues = "";

                        DataTable dtResult = null;

                        //取得欄位對應
                        if (dgvRow.Cells["clMappingData"].Value != null)
                        {
                            strColumns = dgvRow.Cells["clMappingData"].Value.ToString().Split('|');
                        }
                        //取得來源Table
                        if (dgvRow.Cells["clSourceTable"].Value != null)
                        {
                            if (dgvRow.Cells["clSourceTable"].Value.ToString().Trim() != "")
                            {
                                strSourceTable = dgvRow.Cells["clSourceTable"].Value.ToString();
                            }
                        }
                        //取得目標Table
                        if (dgvRow.Cells["clTargetTable"].Value != null)
                        {
                            if (dgvRow.Cells["clTargetTable"].Value.ToString().Trim() != "")
                            {
                                strTargetTable = dgvRow.Cells["clTargetTable"].Value.ToString();
                            }
                        }

                        //取得Where條件
                        if (dgvRow.Cells["clWhere"].Value != null)
                        {
                            if (dgvRow.Cells["clWhere"].Value.ToString().Trim() != "")
                            {
                                strWhere = " " + dgvRow.Cells["clWhere"].Value.ToString();
                            }
                        }
                        #region 產生來源資料查詢命令
                        strGenResult = GenExpSourceCommand(strColumns, strSourceClass, strSourceTable, strWhere);
                        strSelectCommand = strGenResult.Substring(0, strGenResult.IndexOf('|')).Trim();
                        strTargetColumns = strGenResult.Substring(strGenResult.IndexOf('|') + 1).Trim();

                        switch (strSourceClass)
                        {
                            case "MS-SQL":
                                dtResult = GetMSSQLResult(strSelectCommand, sqlMSSourceConn).Tables["Result"];
                                break;
                            case "MySQL":
                                dtResult = GetMySQLResult(strSelectCommand, sqlMySourceConn).Tables["Result"];
                                break;
                            case "Oracle":
                                dtResult = GetOracleResult(strSelectCommand, sqlOraSourceConn).Tables["Result"];
                                break;
                            case "PostgreSQL":
                                dtResult = GetPostgreSQLResult(strSelectCommand.ToString(), sqlPsgSourceConn).Tables["Result"];
                                break;
                        }
                        #endregion
                        //產生匯出資料語法
                        if (dtResult.Rows.Count > 0)
                        {
                            //Progess bar
                            intProgress = 0;
                            pgsForm.pbExport.Value = 0;
                            pgsForm.pbExport.Maximum = dtResult.Rows.Count;

                            if (dgvRow.Cells["clTargetTable"].Value != null)
                            {
                                strTargetTable = dgvRow.Cells["clTargetTable"].Value.ToString();
                                //strFileCount = (intDataCount / intMaxData).ToString();  //切分檔案

                                //撰寫實體檔案
                                if (intMaxData == 1)
                                {
                                    //無設定最大筆數                                
                                    Utility.WriteFile(strPath + ".sql", string.Format("/*======================================================={0}======================================================*/", strTargetTable));
                                    Utility.WriteFile(strPath + ".sql", string.Format("raiserror('Now Insert {0} Datas .... ', 1, 1)", strTargetTable));
                                    Utility.WriteFile(strPath + ".sql", "Go");
                                    Utility.WriteFile(strPath + ".sql", "  ");
                                }
                                else
                                {
                                    //有設定最大筆數                                
                                    if (strFileCount == "0")
                                        strFileCount = "";

                                    Utility.WriteFile(strPath + strFileCount + ".sql", string.Format("/*======================================================={0}======================================================*/", strTargetTable));
                                    Utility.WriteFile(strPath + strFileCount + ".sql", string.Format("raiserror('Now Insert {0} Datas .... ', 1, 1)", strTargetTable));
                                    Utility.WriteFile(strPath + strFileCount + ".sql", "Go");
                                    Utility.WriteFile(strPath + strFileCount + ".sql", "  ");
                                }
                            }

                            //勾選清除目標資料表
                            if (dgvRow.Cells["clTableDel"].Value != null && Convert.ToBoolean(dgvRow.Cells["clTableDel"].Value) == false)
                            {
                                strDeleteCommand = string.Format("Delete From {0} ", strTargetTable);

                                if (dgvRow.Cells["clWhere"].Value != null)
                                {
                                    if (dgvRow.Cells["clWhere"].Value.ToString().Trim() != "")
                                    {
                                        strDeleteCommand += "Where 1=1 And " + dgvRow.Cells["clWhere"].Value.ToString();
                                    }
                                }
                                //撰寫實體檔案
                                if (intMaxData == 1)
                                {
                                    //無設定最大筆數                                
                                    Utility.WriteFile(strPath + ".sql", strDeleteCommand + ";");
                                    Utility.WriteFile(strPath + ".sql", "Go");
                                }
                                else
                                {
                                    //有設定最大筆數                                
                                    if (strFileCount == "0")
                                        strFileCount = "";

                                    Utility.WriteFile(strPath + strFileCount + ".sql", strDeleteCommand + ";");
                                    Utility.WriteFile(strPath + strFileCount + ".sql", "Go");
                                }
                            }

                            //處理筆數上限似資料匯出
                            foreach (DataRow dr in dtResult.Rows)
                            {
                                foreach (DataColumn dc in dtResult.Columns)
                                {
                                    if (strValues == "")
                                    {
                                        strValues = ConvertMySQLDataType(dr[dc].ToString().Trim(), dc.DataType.ToString());
                                    }
                                    else
                                    {
                                        strValues += "," + ConvertMySQLDataType(dr[dc].ToString().Trim(), dc.DataType.ToString());
                                    }
                                }

                                //撰寫實體檔案
                                if (intMaxData == 1)
                                {
                                    //無設定最大筆數
                                    Utility.WriteFile(strPath + ".sql", string.Format("Insert Into {0} ({1}) Values ({2}) ", strTargetTable, strTargetColumns, strValues));
                                }
                                else
                                {
                                    //有設定最大筆數                                
                                    if (strFileCount == "0")
                                        strFileCount = "";
                                    Utility.WriteFile(strPath + strFileCount + ".sql", string.Format("Insert Into {0} ({1}) Values ({2}) ", strTargetTable, strTargetColumns, strValues));
                                }
                                intDataCount += 1;
                                intProgress += 1;
                                pgsForm.pbExport.Value = intProgress;  //變化Progess的狀態
                                Application.DoEvents();
                                strFileCount = (intDataCount / intMaxData).ToString();  //切分檔案
                                strValues = ""; //清除已經存在的資料                            
                            }

                            //撰寫實體檔案
                            if (intMaxData == 1)
                            {
                                //無設定最大筆數
                                Utility.WriteFile(strPath + ".sql", "Go");
                            }
                            else
                            {
                                //有設定最大筆數                                
                                if (strFileCount == "0")
                                    strFileCount = "";
                                Utility.WriteFile(strPath + strFileCount + ".sql", "Go");
                            }
                        }
                    }
                    Application.DoEvents();
                }

                MessageBox.Show("匯出完成");
            }
            catch (Exception ex)
            {
                MessageBox.Show("匯出失敗");
                ErrorHandler.WriteErrorLog("Form1.ExportToMySQLData", ex);
            }
            finally
            {
                pgsForm.Close();
            }
        }

        private string ConvertMySQLColumnType(string strSourceCol, string strTargetCol, string strType)
        {    
            //特殊欄位型態處理
            if (strType.IndexOf("enum") != -1)
            {
                strType = "ENUM";
            }            
            if (strType.IndexOf("char") != -1)
            {
                strType = "CHAR";
            }
            if (strType.IndexOf("float") != -1)
            {
                strType = "FLOAT";
            }

            switch (strType.ToUpper())
            {
                case "DATETIME":
                    return "Date_Format(" + strSourceCol + ",'%Y-%m-%d %H:%i:%s') AS " + strTargetCol;
                case "TIMESTAMP":
                    return "Date_Format(" + strSourceCol + ",'%Y-%m-%d %H:%i:%s') AS " + strTargetCol;
                default:
                    return strSourceCol + " AS " + strTargetCol;
            }
        }

        private string ConvertMySQLDataType(string strData, string strType)
        {
            if (strData == "")
            {
                return "Null";
            }
            switch (strType.ToUpper())
            {
                case "STRING":
                    return "'" + strData.ToString().Replace("'", "''") + "'";
                case "INT32":
                    return strData.ToString().Trim();
                case "DECIMAL":
                    return strData.ToString().Trim();
                default:
                    return "'" + strData.ToString().Replace("'", "''") + "'";
            }
        }
        #endregion  //End of MySQL

        #region Oracle
        public DataSet GetOracleResult(string strSQL, OracleConnection conn)
        {
            DataSet dsData = new DataSet();
            try
            {
                using (OracleDataAdapter sqlAdapter = new OracleDataAdapter(strSQL, conn))
                {
                    if (conn.State == ConnectionState.Closed)  //判斷連線狀態
                    {
                        conn.Open();
                        blSourceConnStatus = true;
                    }
                    sqlAdapter.Fill(dsData, "Result");
                }

                return dsData;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private DataTable ShowOracleTableList(OracleConnection sqlConn , string strDBName)
        {
            try
            {
                string strGetOracleTableList = "SELECT TABLE_NAME AS TableName FROM ALL_TABLES Where OWNER Not IN ('SYS', 'XDB', 'SYSTEM', 'CTXSYS', 'MDSYS') ";
                return OracleUtility.GetSQLResult(strGetOracleTableList, sqlConn);
            }
            catch (Exception ex)
            {
                ErrorHandler.WriteErrorLog("Form1.ShowOracleTableList", ex);
                return null;
            }
        }

        private DataTable ShowOracleColumnList(OracleConnection sqlConn, string strTableName)
        {
            try
            {
                string strGetOracleColumnList = "Select COLUMN_NAME,DATA_TYPE From ALL_TAB_COLUMNS Where TABLE_NAME ='" + strTableName + "' Order by COLUMN_NAME";
                return OracleUtility.GetSQLResult(strGetOracleColumnList, sqlConn);
            }
            catch (Exception ex)
            {
                ErrorHandler.WriteErrorLog("Form1.ShowOracleColumnList", ex);
                return null;
            }
        }

        private void ShowOracleTargetColumnList(OracleConnection sqlConn, string strTableName)
        {
            try
            {
                string strGetOracleColumnList = "Select COLUMN_NAME From ALL_TAB_COLUMNS Where TABLE_NAME ='" + strTableName + "' Order by COLUMN_NAME";
                this.clTargetColumn.DisplayMember = "COLUMN_NAME";
                this.clTargetColumn.DataSource = OracleUtility.GetSQLResult(strGetOracleColumnList, sqlConn);
                Application.DoEvents();
            }
            catch (Exception ex)
            {
                ErrorHandler.WriteErrorLog("Form1.ShowOracleTargetColumnList", ex);
            }
        }

        private void ExportToOracleData(string strPath, string subFileName)
        {
            ProgressForm pgsForm = new ProgressForm();
            int intDataCount = 0;
            int intMaxData = 0;
            int intProgress = 0;
            string strGenResult = "";
            string strSourceTable = "";
            string strTargetColumns = "";
            string strWhere = "";
            string strTargetTable = "";
            string strDeleteCommand = "";
            string strValues = "";
            string strFileCount = "";
            string[] strColumns = null;
            string[,] strColumnResults = new string[dgvExportList.Rows.Count, 2];
            string strSelectCommand = "";

            try
            {
                //命令字串處理
                strTargetColumns = "";

                //取得每個檔案最大筆數
                if (Int32.Parse(txtMaxCount.Text.Trim()) > 0)
                {
                    intMaxData = Int32.Parse(txtMaxCount.Text.Trim());
                }
                else
                {
                    intMaxData = 1;
                }

                //取得欄位對應字串陣列                
                pgsForm.WindowState = FormWindowState.Normal;
                pgsForm.pbExport.Minimum = 0;
                pgsForm.Show();
                foreach (DataGridViewRow dgvRow in dgvExportList.Rows)
                {
                    if (dgvRow.Cells["clExport"].Value != null && (bool)dgvRow.Cells["clExport"].Value == true)
                    {
                        strSelectCommand = "";
                        strSourceTable = "";
                        strTargetColumns = "";
                        strDeleteCommand = "";
                        strValues = "";

                        DataTable dtResult = null;

                        //取得欄位對應
                        if (dgvRow.Cells["clMappingData"].Value != null)
                        {
                            strColumns = dgvRow.Cells["clMappingData"].Value.ToString().Split('|');
                        }
                        //取得來源Table
                        if (dgvRow.Cells["clSourceTable"].Value != null)
                        {
                            if (dgvRow.Cells["clSourceTable"].Value.ToString().Trim() != "")
                            {
                                strSourceTable = dgvRow.Cells["clSourceTable"].Value.ToString();
                            }
                        }
                        //取得目標Table
                        if (dgvRow.Cells["clTargetTable"].Value != null)
                        {
                            if (dgvRow.Cells["clTargetTable"].Value.ToString().Trim() != "")
                            {
                                strTargetTable = dgvRow.Cells["clTargetTable"].Value.ToString();
                            }
                        }

                        //取得Where條件
                        if (dgvRow.Cells["clWhere"].Value != null)
                        {
                            if (dgvRow.Cells["clWhere"].Value.ToString().Trim() != "")
                            {
                                strWhere = " " + dgvRow.Cells["clWhere"].Value.ToString();
                            }
                        }
                        #region 產生來源資料查詢命令
                        strGenResult = GenExpSourceCommand(strColumns, strSourceClass, strSourceTable, strWhere);
                        strSelectCommand = strGenResult.Substring(0, strGenResult.IndexOf('|')).Trim();
                        strTargetColumns = strGenResult.Substring(strGenResult.IndexOf('|') + 1).Trim();

                        switch (strSourceClass)
                        {
                            case "MS-SQL":
                                dtResult = GetMSSQLResult(strSelectCommand, sqlMSSourceConn).Tables["Result"];
                                break;
                            case "MySQL":
                                dtResult = GetMySQLResult(strSelectCommand, sqlMySourceConn).Tables["Result"];
                                break;
                            case "Oracle":
                                dtResult = GetOracleResult(strSelectCommand, sqlOraSourceConn).Tables["Result"];
                                break;
                            case "PostgreSQL":
                                dtResult = GetPostgreSQLResult(strSelectCommand.ToString(), sqlPsgSourceConn).Tables["Result"];
                                break;
                        }
                        #endregion
                        //產生匯出資料語法
                        if (dtResult.Rows.Count > 0)
                        {
                            //Progess bar
                            intProgress = 0;
                            pgsForm.pbExport.Value = 0;
                            pgsForm.pbExport.Maximum = dtResult.Rows.Count;

                            if (dgvRow.Cells["clTargetTable"].Value != null)
                            {
                                strTargetTable = dgvRow.Cells["clTargetTable"].Value.ToString();

                                //撰寫實體檔案
                                if (intMaxData == 1)
                                {
                                    //無設定最大筆數                                
                                    Utility.WriteFile(strPath + ".sql", string.Format("/*======================================================={0}======================================================*/", strTargetTable));
                                    Utility.WriteFile(strPath + ".sql", "  ");
                                }
                                else
                                {
                                    //有設定最大筆數                                
                                    if (strFileCount == "0")
                                        strFileCount = "";

                                    Utility.WriteFile(strPath + strFileCount + ".sql", string.Format("/*======================================================={0}======================================================*/", strTargetTable));
                                    Utility.WriteFile(strPath + strFileCount + ".sql", "  ");
                                }
                            }

                            //勾選清除目標資料表
                            if (dgvRow.Cells["clTableDel"].Value != null && Convert.ToBoolean(dgvRow.Cells["clTableDel"].Value) == false)
                            {
                                strDeleteCommand = string.Format("Delete From {0} ", strTargetTable);

                                if (dgvRow.Cells["clWhere"].Value != null)
                                {
                                    if (dgvRow.Cells["clWhere"].Value.ToString().Trim() != "")
                                    {
                                        strDeleteCommand += "Where 1=1 And " + dgvRow.Cells["clWhere"].Value.ToString();
                                    }
                                }
                                //撰寫實體檔案
                                if (intMaxData == 1)
                                {
                                    //無設定最大筆數                                
                                    Utility.WriteFile(strPath + ".sql", strDeleteCommand + ";");
                                }
                                else
                                {
                                    //有設定最大筆數                                
                                    if (strFileCount == "0")
                                        strFileCount = "";

                                    Utility.WriteFile(strPath + strFileCount + ".sql", strDeleteCommand + ";");
                                }
                            }

                            //處理筆數上限似資料匯出
                            foreach (DataRow dr in dtResult.Rows)
                            {
                                foreach (DataColumn dc in dtResult.Columns)
                                {
                                    if (strValues == "")
                                    {
                                        switch (dc.DataType.ToString().ToUpper())
                                        {
                                            case "SYSTEM.DATETIME":
                                                strValues = ConvertOracleDataType(DateTime.Parse(dr[dc].ToString()), dc.DataType.ToString());
                                                break;
                                            default:
                                                strValues = ConvertOracleDataType(dr[dc].ToString().Trim(), dc.DataType.ToString());
                                                break;
                                        }
                                        
                                    }
                                    else
                                    {
                                        switch (dc.DataType.ToString().ToUpper())
                                        {
                                            case "SYSTEM.DATETIME":
                                                strValues += "," + ConvertOracleDataType(DateTime.Parse(dr[dc].ToString()), dc.DataType.ToString());
                                                break;
                                            default:
                                                strValues += "," + ConvertOracleDataType(dr[dc].ToString().Trim(), dc.DataType.ToString());
                                                break;
                                        }
                                        
                                    }
                                }

                                //撰寫實體檔案
                                if (intMaxData == 1)
                                {
                                    //無設定最大筆數
                                    Utility.WriteFile(strPath + ".sql", string.Format("Insert Into {0} ({1}) Values ({2}) ;", strTargetTable, strTargetColumns, strValues));
                                }
                                else
                                {
                                    //有設定最大筆數                                
                                    if (strFileCount == "0")
                                        strFileCount = "";
                                    Utility.WriteFile(strPath + strFileCount + ".sql", string.Format("Insert Into {0} ({1}) Values ({2}) ;", strTargetTable, strTargetColumns, strValues));
                                }
                                intDataCount += 1;
                                intProgress += 1;
                                pgsForm.pbExport.Value = intProgress;  //變化Progess的狀態
                                Application.DoEvents();
                                strFileCount = (intDataCount / intMaxData).ToString();  //切分檔案
                                strValues = ""; //清除已經存在的資料                            
                            }

                            //撰寫實體檔案
                            if (intMaxData != 1)                            
                            {
                                //有設定最大筆數                                
                                if (strFileCount == "0")
                                    strFileCount = "";
                            }
                        }
                    }
                }

                MessageBox.Show("匯出完成");
            }
            catch (Exception ex)
            {

                MessageBox.Show("匯出失敗");
                ErrorHandler.WriteErrorLog("Form1.ExportToMSSQLData", ex);
            }
            finally
            {
                pgsForm.Close();
            }
        }

        private string ConvertOracleColumnType(string strSourceCol, string strTargetCol, string strType)
        {
            return strSourceCol + " AS " + strTargetCol;

            //switch (strType.ToUpper())
            //{
            //    case "VARCHAR2":
            //        return strSourceCol + " AS " + strTargetCol;
            //    case "NUMBER":
            //        return strSourceCol + " AS " + strTargetCol;
            //    case "FLOAT":
            //        return strSourceCol + " AS " + strTargetCol;
            //    case "INT":
            //        return strSourceCol + " AS " + strTargetCol;
            //    case "TIMESTAMP(0)":
            //        return strSourceCol + " AS " + strTargetCol;
            //    case "TIMESTAMP":                    
            //        return strSourceCol + " AS " + strTargetCol;
            //    default:
            //        return strSourceCol + " AS " + strTargetCol;
            //}
        }

        private string ConvertOracleDataType(string strData, string strType)
        {
            if (strData == "")
            {
                return "Null";
            }
            switch (strType.ToUpper())
            {
                case "SYSTEM.STRING":
                    return "'" + strData.ToString().Replace("'","''") + "'";
                case "SYSTEM.DECIMAL":
                    return strData.ToString().Trim();
                default:
                    return "'" + strData.ToString().Replace("'", "''") + "'";
            }
        }

        private string ConvertOracleDataType(DateTime dtData, string strType)
        {            
            return "TO_TIMESTAMP('" + dtData.ToString("yyyy-MM-dd HH:mm:ss") + "', 'YYYY-MM-DD HH24:MI:SS')";            
        }
        #endregion  //End of Oracle

        private string CheckSource()
        {
            try
            {
                string strResult = "";

                if (cbSourceClass.SelectedIndex <= 0)
                {
                    strResult = "請選擇來源資料庫類別";
                    cbSourceClass.Focus();
                    return strResult;
                }
                else
                {
                    if (txtSourceIP.Text.Trim() == "")
                    {
                        strResult = "來源資料庫IP為必填";
                        txtSourceIP.Focus();
                        return strResult;
                    }

                    if (txtSourceDBName.Text.Trim() == "")
                    {
                        strResult = "來源資料庫名稱為必填";
                        txtSourceDBName.Focus();
                        return strResult;
                    }

                    if (txtSourceID.Text.Trim() == "")
                    {
                        strResult = "來源資料庫帳號為必填";
                        txtSourceID.Focus();
                        return strResult;
                    }
                }
                return strResult;
            }
            catch (Exception ex)
            {
                ErrorHandler.WriteErrorLog("Form1.CheckSource", ex);
                return "發生不明錯誤!";
            }
        }

        private string CheckTarget()
        {
            try
            {
                string strResult = "";
                if (cbTargetClass.SelectedIndex <= 0)
                {
                    strResult = "請選擇目標資料庫類別";
                    cbTargetClass.Focus();
                    return strResult;
                }
                else
                {
                    if (txtTargetIP.Text.Trim() == "")
                    {
                        strResult = "目標資料庫IP為必填";
                        txtTargetIP.Focus();
                        return strResult;
                    }

                    if (txtTargetDBName.Text.Trim() == "")
                    {
                        strResult = "目標資料庫名稱為必填";
                        txtTargetDBName.Focus();
                        return strResult;
                    }

                    if (txtTargetID.Text.Trim() == "")
                    {
                        strResult = "目標資料庫帳號為必填";
                        txtTargetID.Focus();
                        return strResult;
                    }
                }
                return strResult;
            }
            catch (Exception ex)
            {
                ErrorHandler.WriteErrorLog("Form1.CheckTarget", ex);
                return "發生不明錯誤!";
            }
        }

        private void LockSourceForm()
        {
            try
            {
                cbSourceClass.Enabled = false;
                txtSourceIP.Enabled = false;
                txtSourcePort.Enabled = false;
                txtSourceID.Enabled = false;
                txtSourceDBName.Enabled = false;
                txtSourcePWD.Enabled = false;
                btnExportXML.Enabled = true;
                btnImportXML.Enabled = true;
                btnSourceConnTest.Text = "連線中…";
                //dgvExportList.Columns["clSourceTable"].DataPropertyName = "TableName";
            }
            catch (Exception ex)
            {
                ErrorHandler.WriteErrorLog("Form1.LockSourceForm", ex);
            }
        }

        private void UnLockSourceForm()
        {
            try
            {
                cbSourceClass.Enabled = true;
                txtSourceIP.Enabled = true;
                txtSourcePort.Enabled = true;
                txtSourceID.Enabled = true;
                txtSourceDBName.Enabled = true;
                txtSourcePWD.Enabled = true;
                btnExportXML.Enabled = false;
                btnImportXML.Enabled = false;
                btnSourceConnTest.Text = "建立來源連線";
                Application.DoEvents();
            }
            catch (Exception ex)
            {
                ErrorHandler.WriteErrorLog("Form1.UnLockSourceForm", ex);
            }
        }

        private void LockTargetForm()
        {
            try
            {
                cbTargetClass.Enabled = false;
                cbTargetTable.Enabled = true;
                cbTargetTable.ValueMember = "TableName";
                cbTargetTable.DisplayMember = "TableName";
                cbTargetTable.SelectedIndex = -1;
                txtTargetIP.Enabled = false;
                txtTargetPort.Enabled = false;
                txtTargetID.Enabled = false;
                txtTargetDBName.Enabled = false;
                txtTargetPWD.Enabled = false;
                btnTargetConnTest.Text = "連線中…";
            }
            catch (Exception ex)
            {
                ErrorHandler.WriteErrorLog("Form1.LockTargetForm", ex);
            }
        }

        private void UnLockTargetForm()
        {
            try
            {
                cbTargetClass.Enabled = true;
                cbTargetTable.DataSource = null;
                cbTargetTable.ValueMember = "TableName";
                cbTargetTable.DisplayMember = "TableName";
                cbTargetTable.Enabled = false;
                txtTargetIP.Enabled = true;
                txtTargetPort.Enabled = true;
                txtTargetID.Enabled = true;
                txtTargetDBName.Enabled = true;
                txtTargetPWD.Enabled = true;
                btnTargetConnTest.Text = "建立目標連線";
            }
            catch (Exception ex)
            {
                ErrorHandler.WriteErrorLog("Form1.UnLockTargetForm", ex);
            }
        }
        
        private string GenMappingColumn()
        {
            string strExportColumns = "";
            string strSourceColumn = "";
            string strTargetColumn = "";
            string strSourceColType = "";

            try
            {
                //預設所有欄位均匯出
                foreach (DataGridViewRow dgRow in dgvColumnMapping.Rows)
                {
                    //處理下拉欄位空白的問題
                    if (dgRow.Cells["clExpColumn"].Value != null)
                    {
                        if ((bool)dgRow.Cells["clExpColumn"].Value == true)
                        {
                            if (dgRow.Cells["clSourceColumn"].Value == null)
                            {
                                strSourceColumn = " ";
                            }
                            else
                            {
                                strSourceColumn = dgRow.Cells["clSourceColumn"].Value.ToString();
                            }

                            if (dgRow.Cells["clType"].Value == null)
                            {
                                strSourceColType = " ";
                            }
                            else
                            {
                                strSourceColType = dgRow.Cells["clType"].Value.ToString();
                            }

                            if (dgRow.Cells["clTargetColumn"].Value == null)
                            {
                                strTargetColumn = " ";
                            }
                            else
                            {
                                strTargetColumn = dgRow.Cells["clTargetColumn"].Value.ToString();
                            }

                            if (strExportColumns == "")
                            {

                                strExportColumns += strSourceColumn + "," + strSourceColType + ";" + strTargetColumn;
                            }
                            else
                            {
                                strExportColumns += "|" + strSourceColumn + "," + strSourceColType + ";" + strTargetColumn;
                            }
                        }
                    }
                }
                return strExportColumns;
            }
            catch (Exception ex)
            {
                ErrorHandler.WriteErrorLog("Form1.GenMappingColumn", ex);
                return null;
            }
        }

        private void LocatedTarget(int intRowIndex)
        {
            DataTable dtResult = null;
            try
            {
                if (cbTargetClass.SelectedIndex >= 0 && cbTargetClass.Enabled == false)
                {
                    string strTarget = "";
                    string[] strColumnMapping = null;

                    if (intRowIndex != -1)
                    {
                        intExportIndex = intRowIndex;

                        //設定目標Table下拉式選單的內容
                        if (dgvExportList.Rows[intRowIndex].Cells["clTargetTable"].Value != null && dgvExportList.Rows[intRowIndex].Cells["clTargetTable"].Value.ToString() != "")
                        {
                            strTarget = dgvExportList.Rows[intRowIndex].Cells["clTargetTable"].Value.ToString();
                        }
                        else
                        {
                            strTarget = dgvExportList.Rows[intRowIndex].Cells["clSourceTable"].Value.ToString();
                        }

                        cbTargetTable.SelectedValue = strTarget;

                        //取得欄位對應字串陣列
                        if (dgvExportList.Rows[intRowIndex].Cells["clMappingData"].Value != null)
                        {
                            strColumnMapping = dgvExportList.Rows[intRowIndex].Cells["clMappingData"].Value.ToString().Split('|');
                        }


                        //顯示欄位對應表的內容
                        if (!cbTargetClass.Enabled)
                        {
                            switch (strSourceClass)
                            {
                                case "MS-SQL":
                                    dtResult = ShowMSSQLColumnList(sqlMSSourceConn, strTarget.ToString());
                                    Application.DoEvents();
                                    break;
                                case "MySQL":
                                    dtResult = ShowMySQLColumnList(sqlMySourceConn, strTarget.ToString(), txtSourceDBName.Text.Trim());
                                    dtResult.Columns[0].ColumnName = "COLUMN_NAME";
                                    dtResult.Columns[1].ColumnName = "DATA_TYPE";
                                    dtResult.Columns.Remove("Null");
                                    dtResult.Columns.Remove("Key");
                                    dtResult.Columns.Remove("Default");
                                    dtResult.Columns.Remove("Extra");
                                    Application.DoEvents();
                                    break;
                                case "Oracle":
                                    dtResult = ShowOracleColumnList(sqlOraSourceConn, strTarget.ToString());
                                    Application.DoEvents();
                                    break;
                                case "PostgreSQL":
                                    dtResult = ShowPostgreSQLColumnList(sqlPsgSourceConn, strTarget.ToString());
                                    Application.DoEvents();
                                    break;
                            }
                            dgvColumnMapping.DataSource = dtResult;
                        }

                        if (strColumnMapping != null)
                        {
                            string strSourceColumn = "";
                            string strTargetColumn = "";
                            foreach (string strColumn in strColumnMapping)
                            {
                                strSourceColumn = strColumn.Substring(0, strColumn.IndexOf(',')).Trim();
                                strTargetColumn = strColumn.Substring(strColumn.IndexOf(',') + 1).Trim();
                                foreach (DataGridViewRow dgvRow in dgvColumnMapping.Rows)
                                {
                                    if (dgvRow.Cells["clSourceColumn"].Value.ToString() == strSourceColumn)
                                    {
                                        dgvRow.Cells[0].Value = true;
                                    }
                                }
                            }
                        }
                        else
                        {
                            //預設所有欄位均匯出
                            foreach (DataGridViewRow dgRow in dgvColumnMapping.Rows)
                            {
                                ((DataGridViewCheckBoxCell)dgRow.Cells["clExpColumn"]).Value = true;
                            }
                        }

                        //設定Where條件
                        if (dgvExportList.Rows[intRowIndex].Cells["clWhere"].Value != null)
                        {
                            txtWhere.Text = dgvExportList.Rows[intRowIndex].Cells["clWhere"].Value.ToString();
                        }
                        else
                        {
                            txtWhere.Text = "";
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                ErrorHandler.WriteErrorLog("Form1.LocatedTarget", ex);
            }
        }
        
        private void CleanExpRow(DataGridView senderGrid, int intRowIndex)
        {
            try
            {
                senderGrid.Rows[intRowIndex].Cells["clExport"].Value = false;
                senderGrid.Rows[intRowIndex].Cells["clTargetTable"].Value = "";
                senderGrid.Rows[intRowIndex].Cells["clTableDel"].Value = false;
                senderGrid.Rows[intRowIndex].Cells["clWhere"].Value = "";
                senderGrid.Rows[intRowIndex].Cells["clMappingData"].Value = "";
                //CleanRows(senderGrid, intRowIndex);
                txtWhere.Text = "";
                Application.DoEvents();
            }
            catch (Exception ex)
            {
                ErrorHandler.WriteErrorLog("Form1.CleanExpRow", ex);
            }
        }

        private void ShowRow(DataGridView senderGrid, int intRowIndex)
        {
            try
            {
                DataTable dtResult = null;
                string strTableName = senderGrid.Rows[intRowIndex].Cells["clSourceTable"].Value.ToString();
                switch (strSourceClass)
                {
                    case "MS-SQL":
                        dtResult = MSSQLUtility.GetTable(strTableName, 200, sqlMSSourceConn);
                        break;
                    case "MySQL":
                        dtResult = MySQLUtility.GetTable(strTableName, 200, sqlMySourceConn);
                        break;
                    case "Oracle":
                        dtResult = OracleUtility.GetTable(strTableName, 200, sqlOraSourceConn);
                        break;
                    case "PostgreSQL":
                        dtResult = PostgreSQLUtility.GetTable(strTableName, 200, sqlPsgSourceConn);
                        break;
                }
                ShowForm ResultForm = new ShowForm();
                ResultForm.WindowState = FormWindowState.Normal;
                ResultForm.dgvShowResult.DataSource = dtResult;
                ResultForm.ShowDialog();
                Application.DoEvents();
            }
            catch (Exception ex)
            {
                ErrorHandler.WriteErrorLog("Form1.ShowRow", ex);                
            }
        }

        private void CleanRows(DataGridView senderGrid)
        {
            if (senderGrid.DataSource != null)
            {
                DataTable CleanTable = (DataTable)senderGrid.DataSource;
                CleanTable.Rows.Clear();
                senderGrid.DataSource = CleanTable;
                CleanTable.Dispose();
            }
            
        }

        private void EnableExport()
        {
            if (blSourceConnStatus == true && blTargetConnStatus == true)
            {
                btnExport.Enabled = true;
                btnMapping.Enabled = true;
            }
        }
        
        private string GenExpSourceCommand(string[] strColumns, string strSourceClass, string strSourceTable, string strWhere)
        {
            string strSourceColumns = "";
            string strSourceCol = "";
            string strSourceType = "";
            string strTargetColumns = "";
            string strTargetCol = "";
            string[,] strColumnResults = new string[dgvExportList.Rows.Count, 2];
            string strSelectCommand = "";

            //命令字串處理
            strSourceColumns = "";
            strTargetColumns = "";
            try
            {
                foreach (string RowData in strColumns)
                {
                    strSourceCol = RowData.Substring(0, RowData.IndexOf(',')).Trim();
                    strSourceType = RowData.Substring(RowData.IndexOf(',') + 1, RowData.IndexOf(';') - 1 - RowData.IndexOf(',')).Trim();
                    strTargetCol = RowData.Substring(RowData.IndexOf(';') + 1).Trim();

                    #region 處理查詢欄位格式
                    switch (strSourceClass)
                    {
                        case "MS-SQL":
                            if (strTargetCol == "")
                            {
                                strTargetCol = strSourceCol;
                            }

                            if (strSourceColumns == "")
                            {
                                strSourceColumns += ConvertMSSQLColumnType(strSourceCol, strTargetCol, strSourceType);
                                strTargetColumns += "[" + strTargetCol + "]";
                            }
                            else
                            {
                                strSourceColumns += "," + ConvertMSSQLColumnType(strSourceCol, strTargetCol, strSourceType);
                                strTargetColumns += ",[" + strTargetCol + "]";
                            }
                            break;
                        case "MySQL":
                            if (strTargetCol == "")
                            {
                                strTargetCol = strSourceCol;
                            }

                            if (strSourceColumns == "")
                            {
                                strSourceColumns += ConvertMySQLColumnType(strSourceCol, strTargetCol, strSourceType);
                                strTargetColumns += strTargetCol ;
                            }
                            else
                            {
                                strSourceColumns += "," + ConvertMySQLColumnType(strSourceCol, strTargetCol, strSourceType);
                                strTargetColumns += "," + strTargetCol ;
                            }
                            break;
                        case "Oracle":
                            if (strTargetCol == "")
                            {
                                strTargetCol = strSourceCol;
                            }

                            if (strSourceColumns == "")
                            {
                                strSourceColumns += ConvertOracleColumnType(strSourceCol, strTargetCol, strSourceType);
                                strTargetColumns += strTargetCol;
                            }
                            else
                            {
                                strSourceColumns += "," + ConvertOracleColumnType(strSourceCol, strTargetCol, strSourceType);
                                strTargetColumns += "," + strTargetCol;
                            }
                            break;
                        case "PostgreSQL":
                            if (strTargetCol == "")
                            {
                                strTargetCol = strSourceCol;
                            }

                            if (strSourceColumns == "")
                            {
                                strSourceColumns += ConvertPostgreSQLColumnType(strSourceCol, strTargetCol, strSourceType);
                                strTargetColumns += strTargetCol;
                            }
                            else
                            {
                                strSourceColumns += "," + ConvertPostgreSQLColumnType(strSourceCol, strTargetCol, strSourceType);
                                strTargetColumns += "," + strTargetCol;
                            }
                            break;
                    }
                    #endregion

                }

                switch (strSourceClass)
                {
                    case "MS-SQL":
                        if (strSourceTable != "")
                        {
                            strSelectCommand += string.Format("Select {0} From {1} ", strSourceColumns, strSourceTable);
                        }

                        if (strWhere.Trim() != "")
                        {
                            strSelectCommand += "Where 1=1 And " + strWhere.Trim();
                        }
                        break;
                    case "MySQL":
                        if (strSourceTable != "")
                        {
                            strSelectCommand += string.Format("Select {0} From {1} ", strSourceColumns, strSourceTable);
                        }

                        if (strWhere.Trim() != "")
                        {
                            strSelectCommand += "Where 1=1 And " + strWhere.Trim();
                        }
                        break;
                    case "Oracle":
                        if (strSourceTable != "")
                        {
                            strSelectCommand += string.Format("Select {0} From {1} ", strSourceColumns, strSourceTable);
                        }

                        if (strWhere.Trim() != "")
                        {
                            strSelectCommand += "Where 1=1 And " + strWhere.Trim();
                        }
                        break;
                    case "PostgreSQL":
                        if (strSourceTable != "")
                        {
                            strSelectCommand += string.Format("Select {0} From \"{1}\" ", strSourceColumns, strSourceTable);
                        }

                        if (strWhere.Trim() != "")
                        {
                            strSelectCommand += "Where 1=1 And " + strWhere.Trim();
                        }
                        break;
                }

                return strSelectCommand +"|" + strTargetColumns;
            }
            catch (Exception ex)
            {
                ErrorHandler.WriteErrorLog("Form1.GenExpSourceCommand", ex);
                return "";
            }
        }

        #endregion
    }
}