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 string prepareCloudQuery(string vCloudName, string vCurrSql)
{
if (vCurrSql == null | vCloudName == null | vCurrSql == "" | vCloudName == "")
{
return("");
}
CloudProperties vCurrCloud = in2sqlSvcCloud.vCloudList.Find(item => item.CloudName == vCloudName);
if (vCurrCloud.CloudName == null)
{
return("");
}
vCurrSql = In2SqlVBAEngineCloud.setSqlLimit(vCurrCloud.CloudType, vCurrSql);
if (vCurrCloud.CloudType.Contains("CloudCH"))
{
vCurrSql = vCurrSql.Replace("FORMAT CSVWithNames", "") + " FORMAT CSVWithNames";
}
In2SqlSvcTool.addSqlLog(vCloudName, vCurrSql);
return(prepareCloudQuery_int(vCurrCloud.Url, vCurrSql, vCurrCloud.Login, vCurrCloud.Password));
}
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 objRefreshHistory(Microsoft.Office.Interop.Excel.ListObject vCurrObject, int vIsUndoList = 1)
{
// SqlEngine.currExcelApp.EnableEvents = false;
vCurrObject.QueryTable.CommandText = setSqlLimit(getOdbcNameFromObject(vCurrObject.QueryTable.Connection), vCurrObject.QueryTable.CommandText);
In2SqlSvcTool.addSqlLog(vCurrObject.QueryTable.CommandText);
objRefresh(vCurrObject);
if (vIsUndoList == 1)
{
In2SqlSvcUndoManagment.addToUndoList(vCurrObject.Name, vCurrObject.QueryTable.CommandText);
}
}
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 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");
}
}
}
