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();
}
private static void initCloudObjects(ref in2sqlSvcCloud.CloudProperties vCurrCloudObj)
{
try
{
if (vCurrCloudObj.Tables == null)
{
vCurrCloudObj.Tables = new List <in2sqlSvcCloud.CloudObjects>();
}
if (vCurrCloudObj.Views == null)
{
vCurrCloudObj.Views = new List <in2sqlSvcCloud.CloudObjects>();
}
if (vCurrCloudObj.SQLProgramms == null)
{
vCurrCloudObj.SQLProgramms = new List <in2sqlSvcCloud.CloudObjects>();
}
if (vCurrCloudObj.SQLFunctions == null)
{
vCurrCloudObj.SQLFunctions = new List <in2sqlSvcCloud.CloudObjects>();
}
if (vCurrCloudObj.Tables.Count == 0)
{
vCurrCloudObj.Tables.AddRange(in2sqlSvcCloud.getCloudTableList(vCurrCloudObj.CloudName));
}
if (vCurrCloudObj.Views.Count == 0)
{
vCurrCloudObj.Views.AddRange(in2sqlSvcCloud.getCloudViewList(vCurrCloudObj.CloudName));
}
}
catch (Exception er)
{
In2SqlSvcTool.ExpHandler(er, "initCloudObjects");
}
}
private static IEnumerable <CloudObjects> getCloudObjectList(string vSqlURL)
{
List <String> vObjects = new List <String>();
vObjects.AddRange(In2SqlSvcTool.HttpGetArray(vSqlURL));
int i = 0;
foreach (var vCurrObj in vObjects)
{
i += 1;
if (i < 2)
{
continue;
}
CloudObjects vObj = new CloudObjects();
vObj.Name = vCurrObj.ToString().Replace('"', ' ').Trim();
vObj.idTbl = vIdtbl;
vIdtbl = vIdtbl + 1;
yield return(vObj);
}
}
public static IEnumerable <String> SqlReadDataValue(string vOdbcName, string queryString = "")
{
var vCurrODBC = In2SqlSvcODBC.vODBCList.Find(item => item.OdbcName == vOdbcName);
using
(OdbcConnection conn = new System.Data.Odbc.OdbcConnection())
{
using (OdbcCommand cmnd = new OdbcCommand(queryString, conn))
{
try
{
vCurrODBC.DSNStr = "DSN=" + vOdbcName;
if (vCurrODBC.Login != null)
{
vCurrODBC.DSNStr = vCurrODBC.DSNStr + ";Uid=" + vCurrODBC.Login + ";Pwd=" + vCurrODBC.Password + ";";
}
conn.ConnectionString = vCurrODBC.DSNStr;
conn.ConnectionTimeout = 5;
conn.Open();
}
catch (Exception e)
{
In2SqlSvcTool.ExpHandler(e, "In2SqlSvcODBC.ReadData", queryString);
conn.Close();
conn.Dispose();
yield break;
}
OdbcDataReader rd = cmnd.ExecuteReader();
while (rd.Read())
{
yield return(rd["value"].ToString());//.Split(',').ToList(); ;
}
conn.Close();
conn.Dispose();
}
}
}
private void treeODBC_NodeMouseClick(object sender, TreeNodeMouseClickEventArgs e)
{
try
{
miSelectNode = treeODBC.GetNodeAt(e.X, e.Y);
if (e.Node.Tag != null)
{
if (e.Button == MouseButtons.Left)
{
expand_action(e);
}
else
{
contextMenu(e);
}
}
}
catch (Exception er)
{
In2SqlSvcTool.ExpHandler(er, "treeODBC_NodeMouseClick ");
}
}
private void WF09BTTest_Click(object sender, EventArgs e)
{
string vSqlURL;
vSqlURL = in2sqlSvcCloud.prepareCloudQuery_int(tbURL.Text, tbSQL.Text, tbLogin.Text, tbPassword.Text);
vSqlURL = In2SqlSvcTool.HttpGet(vSqlURL);
if (vSqlURL.Length < 2)
{
MessageBox.Show("Test Failed");
return;
}
MessageBox.Show("Test Passed ");
WF09BTOk.Enabled = true;
vSqlURL = "Cloud" + vConnType + '.' + tbName.Text;
in2SqlRegistry.setLocalValue(vSqlURL, "Url", tbURL.Text);
in2SqlRegistry.setLocalValue(vSqlURL, "Login", tbLogin.Text);
in2SqlRegistry.setLocalValue(vSqlURL, "Password", tbPassword.Text);
}
private void SqlEngine_Shutdown(object sender, System.EventArgs e)
{
currExcelApp = null;
In2SqlSvcTool.RunGarbageCollector();
}
public static void setODBCTreeLineComplex(TreeNode nodeToAddTo, string vCurrvListOdbcName, string VCurrOdbcName)
{
try
{
var vCurrODBC = In2SqlSvcODBC.vODBCList.Find(item => item.OdbcName == vCurrvListOdbcName);
if ((vCurrODBC.ConnStatus == 0))
{
setODBCTreeLineSimple(nodeToAddTo, vCurrvListOdbcName);
return;
}
if (vCurrODBC.ConnStatus < 0)
{
TreeNode vNodeDatabase = new TreeNode(vCurrODBC.OdbcName, 7, 7);
nodeToAddTo.Nodes.Add(vNodeDatabase);
vNodeDatabase.Tag = "ODBC%";
TreeNode vNodeTable = new TreeNode(vCurrODBC.ConnErrMsg, 99, 99);
vNodeDatabase.Nodes.Add(vNodeTable);
return;
}
if ((vCurrODBC.ConnStatus == 1) & vCurrvListOdbcName.Contains(VCurrOdbcName) & vCurrvListOdbcName.Length == VCurrOdbcName.Length)
{
initSQlObjects(ref vCurrODBC);
}
if (vCurrODBC.ConnStatus == 1 & (vCurrODBC.Tables.Count == 0 & vCurrODBC.Views.Count == 0))
{
TreeNode vNodeDatabase = new TreeNode(vCurrODBC.OdbcName, 2, 2);
nodeToAddTo.Nodes.Add(vNodeDatabase);
vNodeDatabase.Tag = "ODBC#";
return;
}
if (vCurrODBC.ConnStatus == 1 & (vCurrODBC.Tables.Count > 0 | vCurrODBC.Views.Count > 0))
{
TreeNode vNodeDatabase = new TreeNode(vCurrODBC.OdbcName, 2, 2);
nodeToAddTo.Nodes.Add(vNodeDatabase);
vNodeDatabase.Tag = "ODBC#";
if (vCurrODBC.Tables.Count > 0)
{
TreeNode vNodeTableFolder = new TreeNode("Tables".ToString(), 3, 3);
vNodeTableFolder.Tag = vCurrODBC.OdbcName + "tf";
vNodeDatabase.Nodes.Add(vNodeTableFolder);
foreach (var vCurrTable in vCurrODBC.Tables)
{
TreeNode vNodeTable = new TreeNode(vCurrTable.Name, 4, 4); // vNodeTable.Tag = vCurrTable.Name;
vNodeTableFolder.Nodes.Add(vNodeTable);
}
}
if (vCurrODBC.Views.Count > 0)
{
TreeNode vNodeViewFolder = new TreeNode("Views".ToString(), 5, 5);
vNodeViewFolder.Tag = vCurrODBC.OdbcName + "vf";
vNodeDatabase.Nodes.Add(vNodeViewFolder);
foreach (var vCurrView in vCurrODBC.Views)
{
TreeNode vNodeView = new TreeNode(vCurrView.Name, 6, 6); // vNodeTable.Tag = vCurrTable.Name;
vNodeViewFolder.Nodes.Add(vNodeView);
}
}
}
}
catch (Exception er)
{
In2SqlSvcTool.ExpHandler(er, "setODBCTreeLineComplex");
}
}
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 void ExecMenuButton(Office.IRibbonControl vControl)
{
var vActivCell = SqlEngine.currExcelApp.ActiveCell;
try
{
In2SqlSvcTool.RunGarbageCollector();
switch (vControl.Id)
{
case "ExecConnManager":
showSQlMAnPane();
ActivateTab();
break;
case "ODBCManager":
In2SqlSvcTool.RunCmdLauncher("odbcad32");
ActivateTab();
break;
case "OdbcProp":
showODBCProp();
ActivateTab();
break;
case "BackOutl":
showOutlinePane();
ActivateTab();
break;
case "SqlEdit":
showEditPane();
break;
case "KeepOnly":
intSqlVBAEngine.RibbonKeepOnly();
ActivateTab();
break;
case "RemoveOnly":
intSqlVBAEngine.RibbonRemoveOnly();
ActivateTab();
break;
case "Retrieve":
intSqlVBAEngine.RibbonRefresh();
ActivateTab();
break;
case "RetrieveAll":
intSqlVBAEngine.RibbonRefreshAll();
ActivateTab();
break;
case "EditQuery":
showSqlEdit();
ActivateTab();
break;
case "PivotExcel":
intSqlVBAEngine.RibbonPivotExcel();
ActivateTab();
break;
case "Undo":
intSqlVBAEngine.Undo();
ActivateTab();
break;
case "Redo":
intSqlVBAEngine.Redo();
ActivateTab();
break;
// ()
case "UpdateDataAll":
intSqlVBAEngine.updateTablesAll();
ActivateTab();
break;
case "UpdateData":
intSqlVBAEngine.updateTables();
ActivateTab();
break;
//
case "PowerPivotMM":
intSqlVBAEngine.runPowerPivotM();
//intSqlVBAEngine.checkTableName();
ActivateTab();
break;
case "Options":
intSqlVBAEngine.runSqlProperties();
//intSqlVBAEngine.checkTableName();
ActivateTab();
break;
case "TableProp":
intSqlVBAEngine.runTableProperties();
break;
case "About":
showAbout();
/* var frmshowAbout = new in2SqlWF09CloudConnectionEditor();
* frmshowAbout.Show();
* ActivateTab();*/
break;
//RightTaskPane
default:
/* string caption = "Information message";
* MessageBoxButtons buttons = MessageBoxButtons.YesNo;
* DialogResult result;
*
* // Displays the MessageBox.
* result = MessageBox.Show(vControl.Id, caption, buttons);
*/
break;
}
// In2SqlSvcTool.RunGarbageCollector();
}
catch (Exception e)
{
In2SqlSvcTool.ExpHandler(e, "ExecMenuButton");
}
}
private void treeExcelOtl_MouseClick(object sender, TreeNodeMouseClickEventArgs e)
{
try
{
miSelectNode = treeExcelOtl.GetNodeAt(e.X, e.Y);
if (e.Node.Tag != null)
{
if (e.Node.Tag.ToString().Contains("excel"))
{
if (e.Button == MouseButtons.Right)
{
contextMenuExcelRoot = createMenu(
e
, new String[] { "Refresh" /*, "Sort", "Create Outline", "Create Task" */ }
, rootOutline_Click
, contextMenuExcelRoot);
return;
}
else
{
RefreshExcel(e.Node);
return;
}
}
if (e.Node.Tag.ToString().Contains("ExBook"))
{
if (e.Button == MouseButtons.Left)
{
for (int i = 1; i <= SqlEngine.currExcelApp.Workbooks.Count; i++)
{
var vCurrBook = SqlEngine.currExcelApp.Workbooks[i];
if (vCurrBook.Name.ToString().Contains(e.Node.Text))
{
vCurrBook.Activate();
return;
}
}
return;
}
}
if (e.Node.Tag.ToString().Contains("ExList"))
{
if (e.Button == MouseButtons.Right)
{
contextMenuExSheet = createMenu(
e
, new String[] { "Refresh" /*, "Copy", "Rename", "Delete"*/ }
, ExcelActions_Click
, contextMenuExSheet);
return;
}
else
{
for (int i = 1; i <= SqlEngine.currExcelApp.ActiveWorkbook.Sheets.Count; i++)
{
var vCurrSheet = SqlEngine.currExcelApp.ActiveWorkbook.Sheets[i];
if (vCurrSheet.Name.ToString().Contains(e.Node.Text))
{
vCurrSheet.Activate();
return;
}
}
return;
}
}
}
}
catch (Exception er)
{
In2SqlSvcTool.ExpHandler(er, "treeExcelOtl_MouseClick 2 ");
}
}
public static void createExTable(string vCurrCloudName, string vTableName, string vCurrSql = null, int isReplace = 0, string vOldTableName = "")
{
var vCurrWorkSheet = SqlEngine.currExcelApp.ActiveSheet;
var vCurrWorkBook = SqlEngine.currExcelApp.ActiveWorkbook;
if (isReplace == 1)
{
vCurrWorkSheet.ListObjects(vOldTableName).Range().Select();
}
var vActivCell = SqlEngine.currExcelApp.ActiveCell;
SqlEngine.currExcelApp.ScreenUpdating = false;
if (vCurrSql == null)
{
vCurrSql = "SELECT \n\t * \n FROM \n\t " + vTableName + "\n where 1=1 ";
}
string vConnURL = in2sqlSvcCloud.prepareCloudQuery(vCurrCloudName, vCurrSql);
if ((isReplace == 0) & ((vActivCell.ListObject != null) | (vActivCell.Value != null)))
{
MessageBox.Show(" Please select empty area in Excel data grid");
return;
}
if (isReplace == 1)
{
if (vActivCell.ListObject != null)
{
try
{
if (vOldTableName == "")
{
vActivCell.ListObject.Delete();
}
else
{
vCurrWorkSheet.ListObjects(vOldTableName).Delete();
}
}
catch
{
}
}
}
if (vActivCell != null & vConnURL.Length > 1 & vTableName.Length > 1)
{
string vTempFile = "TEXT;" + In2SqlSvcTool.writeHttpToFile(vConnURL);
var xlQueryTable = vCurrWorkSheet.QueryTables.Add(
Connection: vTempFile
, Destination: vActivCell
);
xlQueryTable.Name = vCurrCloudName + "|" + vTableName;
xlQueryTable.FieldNames = true;
xlQueryTable.RowNumbers = false;
xlQueryTable.FillAdjacentFormulas = false;
xlQueryTable.PreserveFormatting = true;
xlQueryTable.Connection = vTempFile;
xlQueryTable.RefreshOnFileOpen = false;
xlQueryTable.RefreshStyle = Excel.XlCellInsertionMode.xlInsertDeleteCells;
xlQueryTable.SavePassword = false;
xlQueryTable.SaveData = true;
xlQueryTable.AdjustColumnWidth = true;
xlQueryTable.RefreshPeriod = 0;
xlQueryTable.TextFilePromptOnRefresh = false;
xlQueryTable.TextFileStartRow = 1;
xlQueryTable.TextFileConsecutiveDelimiter = false;
xlQueryTable.TextFileTabDelimiter = true;
xlQueryTable.TextFileCommaDelimiter = true;
xlQueryTable.TextFileSemicolonDelimiter = true;
xlQueryTable.TextFileOtherDelimiter = "|";
xlQueryTable.TextFileSpaceDelimiter = false;
xlQueryTable.SourceDataFile = vCurrCloudName + "|" + vCurrSql;
xlQueryTable.Refresh(true);
vTempFile = vTempFile.Replace("TEXT;", "");
In2SqlSvcTool.deleteFile(vTempFile);
var qtAddress = xlQueryTable.ResultRange.Address;
xlQueryTable.Delete();
//, Selection, , xlYes
var xlTable = vCurrWorkSheet.ListObjects.Add(
SourceType: Excel.XlListObjectSourceType.xlSrcRange
, Source: vCurrWorkSheet.Range(qtAddress)
, XlListObjectHasHeaders: Excel.XlYesNoGuess.xlYes);
string vExTName = vOldTableName;
if (vExTName == "")
{
vExTName = vCurrCloudName + "|" + vTableName + '|' + DateTime.Now.ToString("YYYYMMDDTHHmmss");
}
try
{
vCurrWorkSheet.ListObjects(vExTName).Delete();
}
catch { }
xlTable.Name = vExTName;
xlTable.Comment = "CLOUD|" + vCurrCloudName + "|" + vCurrSql;
xlTable.TableStyle = "TableStyleLight13";
intSqlVBAEngine.GetSelectedTab();
SqlEngine.currExcelApp.ScreenUpdating = true;
intSqlVBAEngine.GetSelectedTab();
return;
}
}
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");
}
}
}
