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