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