private static void ProcessAndUploadDataSet(ReddDataSet dataSet, PerformanceResult performanceResult, int bulkSize)
{
using (MySqlConnection insertConnection = new MySqlConnection(ConnectionString))
{
insertConnection.Open();
var sw = Stopwatch.StartNew();
var rows = new List <string>();
int pos = 0;
foreach (var line in File.ReadLines(dataSet.FilePath))
{
var split = line.Split(' ');
var date = FromUnixTime(long.Parse(split[0]));
rows.Add($"({split[1]}, {dataSet.Id},{date.Day}, {date.Month})");
if (++pos % bulkSize == 0)
{
StoreDataOnServer(rows, insertConnection);
}
}
//Store the remaining rows
StoreDataOnServer(rows, insertConnection);
performanceResult.InsertTimeMs = sw.ElapsedMilliseconds;
performanceResult.NumberOfNewRows = dataSet.LineCount;
}
}
public static PerformanceResult BenchmarkDataSet(ReddDataSet dataSet, int numOfRepetitions = 5)
{
var performanceResult = new PerformanceResult {
DataSet = dataSet
};
ProcessAndUploadDataSet(dataSet, performanceResult, 100000);
BenchmarkServerCalculation(performanceResult, numOfRepetitions);
return(performanceResult);
}
public static void LoadCsvDataIntoSqlServer(ReddDataSet dataSet)
{
// This should be the full path
var createdCount = 0;
using (var textFieldParser = new TextFieldParser(dataSet.FilePath))
{
textFieldParser.TextFieldType = FieldType.Delimited;
textFieldParser.Delimiters = new[] { " " };
var connectionString = "Server=localhost;Database=load_profiles";
var dataTable = new DataTable(_tableName);
// Add the columns in the temp table
dataTable.Columns.Add("meterId");
dataTable.Columns.Add("timestamp");
dataTable.Columns.Add("power");
using (var sqlConnection = new SqlConnection(connectionString))
{
sqlConnection.Open();
// Create the bulk copy object
var sqlBulkCopy = new SqlBulkCopy(sqlConnection)
{
DestinationTableName = _tableName
};
// Setup the column mappings, anything ommitted is skipped
sqlBulkCopy.ColumnMappings.Add("meterId", "meterId");
sqlBulkCopy.ColumnMappings.Add("timestamp", "timestamp");
sqlBulkCopy.ColumnMappings.Add("power", "power");
// Loop through the CSV and load each set of 100,000 records into a DataTable
// Then send it to the LiveTable
while (!textFieldParser.EndOfData)
{
dataTable.Rows.Add(dataSet.Id, textFieldParser.ReadFields());
createdCount++;
if (createdCount % _batchSize == 0)
{
InsertDataTable(sqlBulkCopy, sqlConnection, dataTable);
break;
}
}
// Don't forget to send the last batch under 100,000
InsertDataTable(sqlBulkCopy, sqlConnection, dataTable);
sqlConnection.Close();
}
}
}
