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 } }