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