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();
}