public static void RibbonPivotExcel() { var vActivCell = SqlEngine.currExcelApp.ActiveCell; if (vActivCell.ListObject == null) { MessageBox.Show(" Please, select cell from the table", " Refresh error"); return; } In2SqlSvcTool.CurrentTableRecords vCTR = In2SqlSvcTool.getCurrentSql(); if (vCTR.TypeConnection.Contains("ODBC")) { string vSql = RemoveSqlLimit(vActivCell.ListObject.QueryTable.CommandText); createPivotTable(getOdbcNameFromCell(), In2SqlSvcTool.GetHash(vSql), vSql); } if (vCTR.TypeConnection.Contains("CLOUD")) { SqlEngine.currExcelApp.SendKeys("%NVT"); } GetSelectedTab(); }
public static void Redo() { try { var vActivCell = SqlEngine.currExcelApp.ActiveCell; if ((vActivCell.ListObject == null) == false) { In2SqlSvcTool.CurrentTableRecords vCTR = In2SqlSvcTool.getCurrentSql(); string vSql = In2SqlSvcUndoManagment.getLastSqlActionRedo(vActivCell.ListObject.Name); if ((vSql == null) == false) { vCTR.Sql = vSql; tableRefresh(vCTR, 0); } GetSelectedTab(); return; } MessageBox.Show(" Please, select cell from the table", " Refresh error"); } catch { MessageBox.Show(" Please, select cell from the table", " Refresh error"); } GetSelectedTab(); }
public static void tableRefresh(In2SqlSvcTool.CurrentTableRecords vCTR, int vIsUndoList = 1) { var vActivCell = SqlEngine.currExcelApp.ActiveCell; if (vCTR.TypeConnection.Contains("ODBC")) { vActivCell.ListObject.QueryTable.CommandText = vCTR.Sql; objRefreshHistory(vActivCell.ListObject, vIsUndoList); if (vIsUndoList == 1) { In2SqlSvcUndoManagment.addToUndoList(vActivCell.ListObject.Name, vCTR.Sql); } } if (vCTR.TypeConnection.Contains("CLOUD")) { In2SqlVBAEngineCloud.createExTable( vCTR.CurrCloudName , vCTR.TableName , vCTR.Sql , 1 , vCTR.CurrCloudExTName); In2SqlSvcTool.addSqlLog(vCTR.Sql); if (vIsUndoList == 1) { In2SqlSvcUndoManagment.addToUndoList(vCTR.CurrCloudExTName, vCTR.Sql); } } }
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 In2SqlWF04EditQuery() { vCTR = In2SqlSvcTool.getCurrentSql(); if (vCTR.CurrCloudExTName != "") { SqlEngine.currExcelApp.ActiveSheet.ListObjects(vCTR.CurrCloudExTName).Range().Select(); } InitializeComponent(); SqlEditor.Language = FastColoredTextBoxNS.Language.SQL; SqlEditor.Text = vCTR.Sql; this.Text = "Sql Edit: " + vCTR.TableName; SqlEditor_TextChanged(null, null); }
public static void RibbonRefresh() { try { //eeee isRefresh = true; var vActivCell = SqlEngine.currExcelApp.ActiveCell; In2SqlSvcTool.CurrentTableRecords vCTR = In2SqlSvcTool.getCurrentSql(); if (vCTR.TypeConnection.Contains("ODBC")) { if ((vActivCell.ListObject != null)) { objRefreshHistory(vActivCell.ListObject); isRefresh = false; return; } if ((vActivCell.PivotTable != null)) { vActivCell.PivotTable.RefreshTable(); isRefresh = false; return; } } if (vCTR.TypeConnection.Contains("CLOUD")) { In2SqlVBAEngineCloud.createExTable( vCTR.CurrCloudName , vCTR.TableName , vCTR.Sql , 1 , vCTR.CurrCloudExTName); isRefresh = false; return; } isRefresh = false; MessageBox.Show(" Please, select cell from the table", " Refresh error"); } catch { isRefresh = false; MessageBox.Show(" Please, select cell from the table", " Refresh error"); } }
public static void RibbonRemoveOnly() { var vCurrWorkSheet = SqlEngine.currExcelApp.ActiveSheet; var vActivCell = SqlEngine.currExcelApp.ActiveCell; In2SqlSvcTool.CurrentTableRecords vCTR = In2SqlSvcTool.getCurrentSql(); if (vActivCell != null) { vCTR.Sql = vCTR.Sql + Environment.NewLine + " \t and " + vCurrWorkSheet.Cells(vActivCell.ListObject.Range.Row, vActivCell.Column).Value + " <> '" + vActivCell.Value + "'"; tableRefresh(vCTR); return; } GetSelectedTab(); }
public static void RibbonRefreshAll() { try { vInsertList = new List <InsertList>(); var vCurrWorkBook = SqlEngine.currExcelApp.ActiveWorkbook; foreach (Microsoft.Office.Interop.Excel.Worksheet vCurrWorkSheet in vCurrWorkBook.Sheets) { foreach (Microsoft.Office.Interop.Excel.ListObject vTable in vCurrWorkSheet.ListObjects) { In2SqlSvcTool.CurrentTableRecords vCTR = In2SqlSvcTool.getCurrentSql(); if (vCTR.TypeConnection.Contains("ODBC")) { objRefreshHistory(vTable); continue; } if (vCTR.TypeConnection.Contains("CLOUD")) { In2SqlVBAEngineCloud.createExTable( vCTR.CurrCloudName , vCTR.TableName , vCTR.Sql , 1 , vCTR.CurrCloudExTName); isRefresh = false; return; } } foreach (var vTable in vCurrWorkSheet.PivotTables()) { vTable.RefreshTable(); } } } catch { } GetSelectedTab(); }