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