public static string setSqlLimit(string vODBC, string vCurrSql) { string vSql = RemoveSqlLimit(vCurrSql); string vTypeODBC = In2SqlSvcODBC.getODBCProperties(vODBC, "DBType"); if (vTypeODBC.ToUpper().Contains("VERTICA")) { if (vSql.ToUpper().Contains("LIMIT") == false) { vSql = vSql + Environment.NewLine + "/*`*/ LIMIT " + In2SqlRibbon.vRowCount + " /*`*/ "; } } else if (vTypeODBC.ToUpper().Contains("MSSQL")) { if (vSql.ToUpper().Contains("TOP") == false) { vSql = vSql.Replace("select", "SELECT"); vSql = vSql.Replace("Select", "SELECT"); Regex rgx = new Regex("SELECT"); vSql = rgx.Replace(vSql, "SELECT /*`*/ TOP(" + In2SqlRibbon.vRowCount + ") /*`*/ ", 1); } } return(vSql); }
private void GetODbcRecords(TreeNode nodeToAddTo, int vIsUI = 0) { try { foreach (var vCurrCloudList in in2sqlSvcCloud.vCloudList) { string vv = vCurrCloudList.CloudName; } if (vIsUI == 0) { foreach (var vCurrvODBCList in In2SqlSvcODBC.vODBCList) { in2SqlRightPaneTreeTables.setODBCTreeLineSimple(nodeToAddTo, vCurrvODBCList.OdbcName); } return; } if (vIsUI == 1) { foreach (var vCurrvODBCList in In2SqlSvcODBC.vODBCList) { In2SqlSvcODBC.checkOdbcStatus(vCurrvODBCList.OdbcName); in2SqlRightPaneTreeTables.setODBCTreeLineComplex(nodeToAddTo, vCurrvODBCList.OdbcName, vCurrvODBCList.OdbcName); } return; } } catch (Exception er) { In2SqlSvcTool.ExpHandler(er, "GetODbcRecords"); } }
private void WF01BTTest_Click(object sender, EventArgs e) { var vCurrODBC = In2SqlSvcODBC.vODBCList.Find(item => item.OdbcName == vODBCName); vCurrODBC.Login = WF01TBLogin.Text; vCurrODBC.Password = WF01TBPassword.Text; In2SqlSvcODBC.ChangeOdbcValue(vODBCName, vCurrODBC); In2SqlSvcODBC.checkOdbcStatus(vODBCName); vCurrODBC = In2SqlSvcODBC.vODBCList.Find(item => item.OdbcName == vODBCName); DialogResult result; if (vCurrODBC.ConnStatus == 1) { WF01BTOk.Enabled = true; result = MessageBox.Show("Test passed".ToString()); in2SqlRegistry.setLocalValue(vODBCName, "Login", vCurrODBC.Login); in2SqlRegistry.setLocalValue(vODBCName, "Password", vCurrODBC.Password); } else { result = MessageBox.Show(vCurrODBC.ConnErrMsg); } In2SqlSvcTool.RunGarbageCollector(); }
public static void updateTables(string vDNS = "") { In2SqlSvcTool.CurrentTableRecords vCTR = In2SqlSvcTool.getCurrentSql(); if (vCTR.TypeConnection.Contains("CLOUD")) { System.Windows.Forms.MessageBox.Show("Update cloud is not support"); } if (vDNS == "") { vDNS = getOdbcNameFromCell(); } int vId = vInsertList.FindIndex(item => item.DSNName == vDNS); if (vId < 0) { return; } int vRecCount = 0; using (OdbcConnection conn = new OdbcConnection(In2SqlSvcODBC.getODBCProperties(vInsertList[vId].DSNName, "DSNStr"))) { conn.ConnectionTimeout = 5; conn.Open(); foreach (var vInsert in vInsertList[vId].SqlUpdate) { vRecCount = vRecCount + 1; if ((vInsert == "") == false) { In2SqlSvcTool.addSqlLog(conn.ToString(), vInsert); using (OdbcCommand cmnd = new OdbcCommand(vInsert, conn)) try { isRefresh = true; cmnd.ExecuteNonQuery(); } catch (Exception e) { System.Windows.Forms.MessageBox.Show(e.Message); } } } vInsertList[vId].SqlUpdate.RemoveRange(0, vInsertList[vId].SqlUpdate.Count); deleteUpdateList(vId); } MessageBox.Show(" updated records: " + vRecCount, " update count r"); }
public static void getColumnsandIndexes(TreeNodeMouseClickEventArgs e) { try { String vNodeTag = e.Node.Parent.Parent.Text + '.' + e.Node.Text; var vCurrObjProp = In2SqlSvcODBC.vObjProp.Find(item => item.ObjName == vNodeTag); if (vCurrObjProp.objColumns == null) { In2SqlSvcODBC.vObjProp.AddRange(In2SqlSvcODBC.getObjectProperties(e.Node.Parent.Parent.Text, e.Node.Text)); vCurrObjProp = In2SqlSvcODBC.vObjProp.Find(item => item.ObjName == vNodeTag); } if (vCurrObjProp.objColumns != null) { if (vCurrObjProp.objColumns.Count > 0) { e.Node.Nodes.Clear(); foreach (var vCurrColumn in vCurrObjProp.objColumns) { TreeNode vNodeColumn = new TreeNode(vCurrColumn.ToString(), 14, 14); vNodeColumn.Tag = vNodeTag + '.' + vCurrColumn + "_clm"; e.Node.Nodes.Add(vNodeColumn); } if (e.Node.Tag.ToString().Contains("$TABLE$")) { e.Node.Tag = vNodeTag + ".TABLE"; TreeNode vNodeIndexFolder = new TreeNode("Indexes".ToString(), 12, 12); vNodeIndexFolder.Tag = vNodeTag + "_idx"; e.Node.Nodes.Add(vNodeIndexFolder); foreach (var vCurrIndx in vCurrObjProp.objIndexes) { TreeNode vNodeIndx = new TreeNode(vCurrIndx.ToString(), 13, 13); vNodeIndx.Tag = vNodeTag + '.' + vCurrIndx + "_idx"; vNodeIndexFolder.Nodes.Add(vNodeIndx); } } else { e.Node.Tag = vNodeTag + ".VIEW"; } } } } catch (Exception er) { In2SqlSvcTool.ExpHandler(er, "getColumnsandIndexes "); } }
private static void initSQlObjects(ref In2SqlSvcODBC.OdbcProperties vCurrOdbc) { try { if (vCurrOdbc.Tables == null) { vCurrOdbc.Tables = new List <In2SqlSvcODBC.SqlObjects>(); } if (vCurrOdbc.Views == null) { vCurrOdbc.Views = new List <In2SqlSvcODBC.SqlObjects>(); } if (vCurrOdbc.SQLProgramms == null) { vCurrOdbc.SQLProgramms = new List <In2SqlSvcODBC.SqlObjects>(); } if (vCurrOdbc.SQLFunctions == null) { vCurrOdbc.SQLFunctions = new List <In2SqlSvcODBC.SqlObjects>(); } if (vCurrOdbc.Tables.Count == 0) { vCurrOdbc.Tables.AddRange(In2SqlSvcODBC.getTableList(vCurrOdbc.OdbcName)); } if (vCurrOdbc.Views.Count == 0) { vCurrOdbc.Views.AddRange(In2SqlSvcODBC.getViewList(vCurrOdbc.OdbcName)); } if (vCurrOdbc.SQLProgramms.Count == 0) { vCurrOdbc.SQLProgramms.AddRange(In2SqlSvcODBC.getSQLProgrammsList(vCurrOdbc.OdbcName)); } if (vCurrOdbc.SQLFunctions.Count == 0) { vCurrOdbc.SQLFunctions.AddRange(In2SqlSvcODBC.getSQLFunctionsList(vCurrOdbc.OdbcName)); } } catch (Exception er) { In2SqlSvcTool.ExpHandler(er, "initSQlObjects"); } }
private void RegularObjecteMenu_Click(object sender, EventArgs e) { if (sender.ToString().Contains("PivotTable")) { intSqlVBAEngine.createPivotTable(miSelectNode.Parent.Parent.Text, miSelectNode.Text); } else if (sender.ToString().Contains("Table")) { if (miSelectNode.Parent.Parent.Tag.ToString().Contains("Cloud")) { In2SqlVBAEngineCloud.createExTable(miSelectNode.Parent.Parent.Text, miSelectNode.Text, null); } else { intSqlVBAEngine.createExTable(miSelectNode.Parent.Parent.Text, miSelectNode.Text); } } /* fix me */ else if (sender.ToString().Contains("generate CSV")) { if (miSelectNode.Parent.Parent.Tag.ToString().ToUpper().Contains("ODBC")) { In2SqlSvcODBC.dumpOdbctoCsv( miSelectNode.Parent.Parent.Text , "select * from " + miSelectNode.Text , In2SqlSvcCsv.getFirstFolder() + miSelectNode.Text + ".csv"); } else if (sender.ToString().Contains("Chart")) { MessageBox.Show(string.Concat("You have Clicked '", sender.ToString(), "' Menu"), "Menu Items Event", MessageBoxButtons.OK, MessageBoxIcon.Information); } else if (sender.ToString().Contains("Editor")) { MessageBox.Show(string.Concat("You have Clicked '", sender.ToString(), "' Menu"), "Menu Items Event", MessageBoxButtons.OK, MessageBoxIcon.Information); } else if (sender.ToString().Contains("Properties")) { MessageBox.Show(string.Concat("You have Clicked '", sender.ToString(), "' Menu"), "Menu Items Event", MessageBoxButtons.OK, MessageBoxIcon.Information); } } }
public static void createPivotTable(string vODBC, string vTableName, string vSql = "") { var vCurrWorkSheet = SqlEngine.currExcelApp.ActiveSheet; var vCurrWorkBook = SqlEngine.currExcelApp.ActiveWorkbook; var vActivCell = SqlEngine.currExcelApp.ActiveCell; string vDSN; vDSN = "ODBC;" + In2SqlSvcODBC.getODBCProperties(vODBC, "DSNStr"); if (vCurrWorkSheet != null & vDSN.Length > 1 & vTableName.Length > 1) { var vDes3 = vCurrWorkSheet.Cells(vActivCell.Row, vActivCell.Column); if ((vDes3.Value == null) == false) { var xlNewSheet = vCurrWorkBook.Worksheets.Add(); vDes3 = xlNewSheet.Cells[1, 1]; } if (vSql == "") { vSql = prepareSQL(vODBC, vTableName, null, 1); } var connections = vCurrWorkBook.Connections.Add2( Name: "cn " + vTableName , Description: vSql , ConnectionString: vDSN , CommandText: vSql , lCmdtype: Excel.XlCmdType.xlCmdSql); var vQT = vCurrWorkBook.PivotCaches().Create( SourceType: Excel.XlPivotTableSourceType.xlExternal , SourceData: connections , Version: 6).CreatePivotTable( TableDestination: vDes3 , TableName: "pvt " + vTableName , ReadData: false , DefaultVersion: 6); vQT.Name = vODBC + " " + vTableName; In2SqlSvcTool.addSqlLog(vSql); vQT.RefreshTable(); GetSelectedTab(); return; } System.Windows.Forms.MessageBox.Show(" Please select empty area in Excel data grid"); }
public static void createExTable(string vODBC, string vTableName, string vCurrSql = null) { var vCurrWorkSheet = SqlEngine.currExcelApp.ActiveSheet; var vCurrWorkBook = SqlEngine.currExcelApp.ActiveWorkbook; var vActivCell = SqlEngine.currExcelApp.ActiveCell; SqlEngine.currExcelApp.SheetChange += CurrExcelApp_SheetChange; string vDSN; vDSN = "ODBC;" + In2SqlSvcODBC.getODBCProperties(vODBC, "DSNStr"); if (vActivCell != null & vDSN.Length > 1 & vTableName.Length > 1) { if (vActivCell.Value == null) { if (vActivCell.ListObject == null) { string vSql = prepareSQL(vODBC, vTableName, vCurrSql); var connections = vCurrWorkBook.Connections.Add( Name: "In2Sql|" + vODBC + "|" + vTableName , Description: "ODBC|" + vODBC + "|" + vSql , ConnectionString: vDSN , CommandText: vSql , lCmdtype: Excel.XlCmdType.xlCmdSql); Microsoft.Office.Interop.Excel.ListObject table = vCurrWorkSheet.ListObjects.Add( SourceType: Excel.XlListObjectSourceType.xlSrcQuery , Source: connections , Destination: vCurrWorkSheet.Cells(vActivCell.Row, vActivCell.Column)); table.Name = "In2Sql|" + vODBC + "|" + vTableName; table.Comment = vTableName; objRefreshHistory(table); GetSelectedTab(); return; } } } System.Windows.Forms.MessageBox.Show(" Please select empty area in Excel data grid"); }
public static void getTablesAndViews(TreeNodeMouseClickEventArgs e) { e.Node.Nodes.Clear(); string vCurrOdbcName = e.Node.Text; In2SqlSvcODBC.checkOdbcStatus(vCurrOdbcName); var vCurrODBC = In2SqlSvcODBC.vODBCList.Find(item => item.OdbcName == vCurrOdbcName); try { if ((vCurrODBC.ConnStatus == 0)) { return; } if (vCurrODBC.ConnStatus < 0) { e.Node.ImageIndex = 7; e.Node.SelectedImageIndex = 7; e.Node.Tag = "ODBC%"; TreeNode vNodeErrRecord = new TreeNode(vCurrODBC.ConnErrMsg, 99, 99); e.Node.Nodes.Add(vNodeErrRecord); return; } if (vCurrODBC.ConnStatus == 1) { e.Node.ImageIndex = 2; e.Node.SelectedImageIndex = 2; e.Node.Tag = "ODBC#"; TreeNode vNodeTableFolder = new TreeNode("Tables".ToString(), 3, 3); vNodeTableFolder.Tag = vCurrODBC.OdbcName + "_tf"; e.Node.Nodes.Add(vNodeTableFolder); initSQlObjects(ref vCurrODBC); foreach (var vCurrTable in vCurrODBC.Tables) { TreeNode vNodeTable = new TreeNode(vCurrTable.Name, 4, 4); vNodeTable.Tag = vCurrODBC.OdbcName + "|" + vCurrTable.Name + "|$TABLE$"; vNodeTableFolder.Nodes.Add(vNodeTable); TreeNode vNodeColumnTbl = new TreeNode(" ".ToString(), 99, 99); vNodeColumnTbl.Tag = vCurrODBC.OdbcName + "." + vCurrTable.Name; vNodeTable.Nodes.Add(vNodeColumnTbl); } TreeNode vNodeViewFolder = new TreeNode("Views".ToString(), 5, 5); vNodeViewFolder.Tag = vCurrODBC.OdbcName + "_vf"; e.Node.Nodes.Add(vNodeViewFolder); foreach (var vCurrView in vCurrODBC.Views) { TreeNode vNodeView = new TreeNode(vCurrView.Name, 6, 6); vNodeView.Tag = vCurrODBC.OdbcName + "." + vNodeView.Name + "|$VIEW$"; vNodeViewFolder.Nodes.Add(vNodeView); TreeNode vNodeColumnVw = new TreeNode(" ".ToString(), 99, 99); vNodeColumnVw.Tag = vCurrODBC.OdbcName + "." + vNodeView.Name; vNodeView.Nodes.Add(vNodeColumnVw); } TreeNode vNodeFunctionFolder = new TreeNode("Functions".ToString(), 10, 10); vNodeFunctionFolder.Tag = vCurrODBC.OdbcName + "_fn"; e.Node.Nodes.Add(vNodeFunctionFolder); foreach (var vCurrFunc in vCurrODBC.SQLFunctions) { TreeNode vNodeView = new TreeNode(vCurrFunc.Name, 9, 9); vNodeView.Tag = vCurrODBC.OdbcName + "." + vCurrFunc.Name; vNodeFunctionFolder.Nodes.Add(vNodeView); } TreeNode vNodeExecFolder = new TreeNode("Procedures".ToString(), 8, 8); vNodeExecFolder.Tag = vCurrODBC.OdbcName + "_pr"; e.Node.Nodes.Add(vNodeExecFolder); foreach (var vCurrProced in vCurrODBC.SQLProgramms) { TreeNode vNodeView = new TreeNode(vCurrProced.Name, 11, 11); vNodeView.Tag = vCurrODBC.OdbcName + "|" + vCurrProced.Name; vNodeExecFolder.Nodes.Add(vNodeView); } return; } } catch (Exception er) { In2SqlSvcTool.ExpHandler(er, "treeODBC_NodeMouseClick 1 "); } }
public static void dumpOdbctoCsv(string vOdbcName, string vSqlCommand, string vCsvFile) { try { int i = 0; string DsnConn = In2SqlSvcODBC.getODBCProperties(vOdbcName, "DSNStr"); if (DsnConn == null | DsnConn == "") { MessageBox.Show("Please make the connection by expand list on the left pane ", "sql run event", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } using (OdbcConnection conn = new System.Data.Odbc.OdbcConnection()) { using (OdbcCommand cmnd = new OdbcCommand(vSqlCommand, conn)) { conn.ConnectionString = DsnConn; conn.ConnectionTimeout = 5; conn.Open(); In2SqlSvcTool.addSqlLog(vOdbcName, vSqlCommand); OdbcDataReader rd = cmnd.ExecuteReader(); object[] output = new object[rd.FieldCount]; using (var textWriter = new StreamWriter(@vCsvFile)) { var writer = new CsvWriter(textWriter, CultureInfo.InvariantCulture); writer.Configuration.Delimiter = ","; writer.Configuration.ShouldQuote = (field, context) => true; for (int j = 0; j < rd.FieldCount; j++) { output[j] = rd.GetName(j); writer.WriteField(rd.GetName(j)); } writer.NextRecord(); while (rd.Read()) { rd.GetValues(output); writer.WriteField(output); writer.NextRecord(); i++; } conn.Close(); conn.Dispose(); } } } MessageBox.Show("Export completed. \n\r File name is " + vCsvFile + " \n\r Row count:" + i, "csv export", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception e) { if (e.HResult != -2147024809) { In2SqlSvcTool.ExpHandler(e, "dumpOdbctoCsv"); } } }