예제 #1
0
        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);
        }
예제 #2
0
        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");
        }
예제 #3
0
        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");
        }
예제 #4
0
        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");
        }
예제 #5
0
        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");
                }
            }
        }