IBE.SQL.EliteDBIO.ImportEDCDData C# (CSharp) Method

ImportEDCDData() public method

public ImportEDCDData ( frmDataIO enImportTypes, String importFile ) : void
enImportTypes frmDataIO
importFile String
return void
        public void ImportEDCDData(frmDataIO.enImportTypes enImportTypes, String importFile)
        {
            try
            {
                String sqlBaseString = "";
                String sqlString = "";
                Int32 dataParts = 0;
                List<String> CSV_Strings = new List<String>();
                String headerDefinition = "";
                Int32 changed = 0;
                Int32 errors = 0;
                Int32 counter = 0;
                Int32 counter2 = 0;
                ProgressEventArgs eva;

                switch (enImportTypes)
                {
                    case frmDataIO.enImportTypes.EDCD_Commodity:
                        headerDefinition = "id,category,name,average";
                        sqlBaseString    = "INSERT INTO tbCommodityBase" +
                                           " (id, category, name, average)" +
                                           " VALUES ({0}, {1}, {2}, {3}) " +
                                           " ON DUPLICATE KEY UPDATE " +
                                           " id          = Values(id)," +
                                           " category    = Values(category)," +
                                           " name        = Values(name)," +
                                           " average     = Values(average);";
                        break;

                    case frmDataIO.enImportTypes.EDCD_Outfitting:
                        headerDefinition = "id,symbol,category,name,mount,guidance,ship,class,rating,entitlement";
                        sqlBaseString    = "INSERT INTO tbOutfittingBase" +
                                           " (id, symbol, category, name, mount, guidance, ship, class, rating, entitlement)" +
                                           " VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}) " +
                                           " ON DUPLICATE KEY UPDATE " +
                                           " id          = Values(id)," +
                                           " symbol      = Values(symbol)," +
                                           " category    = Values(category)," +
                                           " name        = Values(name)," +
                                           " mount       = Values(mount)," +
                                           " guidance    = Values(guidance)," +
                                           " ship        = Values(ship)," +
                                           " class       = Values(class)," +
                                           " rating      = Values(rating)," +
                                           " entitlement = Values(entitlement);";
                        break;

                    case frmDataIO.enImportTypes.EDCD_Shipyard:
                        headerDefinition = "id,symbol,name";
                        sqlBaseString    = "INSERT INTO tbShipyardBase" +
                                           " (id, symbol, name)" +
                                           " VALUES ({0}, {1}, {2}) " +
                                           " ON DUPLICATE KEY UPDATE " +
                                           " id          = Values(id)," +
                                           " symbol      = Values(symbol)," +
                                           " name        = Values(name);";
                        break;

                    default:
                        break;
                }

                dataParts      = headerDefinition.Split(new char[] { ',' }).ToList().Count;
                var reader     = new StreamReader(File.OpenRead(importFile));
                string header  = reader.ReadLine();

                sendProgressEvent(new ProgressEventArgs() { Info="reading data from file " + Path.GetFileName(importFile) + " ...", AddSeparator=true });

                if (header.StartsWith(headerDefinition))
                {
                    
                    do
                    {
                        CSV_Strings.Add(reader.ReadLine());
                        counter++;

                        if(sendProgressEvent(new ProgressEventArgs() { Info="reading data from file " + Path.GetFileName(importFile) + " ...",  CurrentValue=counter }))
                            break;

                    } while (!reader.EndOfStream);

                    reader.Close();

                    if(!sendProgressEvent(new ProgressEventArgs() { Info="reading data from file..." + Path.GetFileName(importFile) + " ...",  CurrentValue=counter, TotalValue=counter, ForceRefresh=true }))
                    {
                        // gettin' some freaky performance
                        Program.DBCon.Execute("set global innodb_flush_log_at_trx_commit=2");

                        sendProgressEvent(new ProgressEventArgs() { Info="importing data from file " + Path.GetFileName(importFile) + " ...",  CurrentValue=0, TotalValue=counter, ForceRefresh=true });

                        foreach (String csvString in CSV_Strings)
                        {
                            List<String> csvParts = csvString.Split(new char[] {','}).ToList();

                            counter2++;

                            if(csvParts.Count == dataParts)
                            {
                                try
                                {
                                    switch (enImportTypes)
                                    {
                                        case frmDataIO.enImportTypes.EDCD_Commodity:
                                            sqlString = String.Format(sqlBaseString, 
                                                                        csvParts[0], 
                                                                        DBConnector.SQLAEscape(csvParts[1]), 
                                                                        DBConnector.SQLAEscape(csvParts[2]), 
                                                                        String.IsNullOrEmpty(csvParts[3]) ? "null" : csvParts[3]);
                                            break;

                                        case frmDataIO.enImportTypes.EDCD_Outfitting:
                                            sqlString = String.Format(sqlBaseString, 
                                                                        csvParts[0], 
                                                                        DBConnector.SQLAEscape(csvParts[1]), 
                                                                        DBConnector.SQLAEscape(csvParts[2]), 
                                                                        DBConnector.SQLAEscape(csvParts[3]), 
                                                                        DBConnector.SQLAEscape(csvParts[4]), 
                                                                        DBConnector.SQLAEscape(csvParts[5]), 
                                                                        DBConnector.SQLAEscape(csvParts[6]), 
                                                                        DBConnector.SQLAEscape(csvParts[7]), 
                                                                        DBConnector.SQLAEscape(csvParts[8]), 
                                                                        DBConnector.SQLAEscape(csvParts[9]));
                                            break;

                                        case frmDataIO.enImportTypes.EDCD_Shipyard:
                                            sqlString = String.Format(sqlBaseString, 
                                                                        csvParts[0], 
                                                                        DBConnector.SQLAEscape(csvParts[1]), 
                                                                        DBConnector.SQLAEscape(csvParts[2]));
                                            break;
                                    }


                                    changed += Program.DBCon.Execute(sqlString);
                                }
                                catch (Exception ex)
                                {
                                    errors++;
                                    sendProgressEvent(new ProgressEventArgs() { Info="error while importing line <" + counter2 + "> : " + csvString, CurrentValue=-1, TotalValue=-1, NewLine=true });
                                }
                            }
                            else
                            {
                                    errors++;
                                    sendProgressEvent(new ProgressEventArgs() { Info="error while importing line <" + counter2 + "> : " + csvString, CurrentValue=-1, TotalValue=-1, NewLine=true });
                            }

                            if(sendProgressEvent(new ProgressEventArgs() {Info="importing data from file..." + Path.GetFileName(importFile) + " ...", CurrentValue=counter2, TotalValue=counter }))
                                break;
                        }

                        // gettin' some freaky performance
                        Program.DBCon.Execute("set global innodb_flush_log_at_trx_commit=1");

                        sendProgressEvent(new ProgressEventArgs() {Info="importing data from file " + Path.GetFileName(importFile) + " ...", CurrentValue=counter2, TotalValue=counter, ForceRefresh=true});
                        sendProgressEvent(new ProgressEventArgs() {Info="new entries = " + changed + ", errors = " + errors, NewLine = true });
                    }
                }

            }
            catch (Exception ex)
            {
                // gettin' some freaky performance
                Program.DBCon.Execute("set global innodb_flush_log_at_trx_commit=1");

                throw new Exception("Error while importing EDCD data", ex);
            }
                    
        }