/// <summary>
/// Asynchronously creates the <paramref name="record"/> in the data store.
/// </summary>
/// <param name="record">The record to insert (ISQLRecord)</param>
/// <returns>Task(bool) whether the function executed without exception.</returns>
public async Task <bool> CreateAsync(ISQLRecord record)
{
// Try casting the record to a MapRecord, throw an argument exception if it fails.
try
{
MapRecord temp = (MapRecord)record;
}
catch
{
throw new ArgumentException(Constants.MapCreateInvalidArgument);
}
MapRecord mapRecord = (MapRecord)record;
IDictionary <string, object> recordData = mapRecord.GetData();
// Get the connection inside a using statement to properly dispose/close.
using (MySqlConnection connection = new MySqlConnection(MapSQLConnection))
{
connection.Open();
// Construct the sql string .. start by inserting into the table name
string sqlString = $"INSERT INTO {Constants.MapDAOTableName} (";
foreach (KeyValuePair <string, object> pair in recordData)
{
sqlString += $"{pair.Key},";
}
// Remove the last comma, add the VALUES keyword
sqlString = sqlString.Remove(sqlString.Length - 1);
sqlString += ") VALUES (";
// Loop through the data once again, but instead of constructing the string with user input, use
// @PARAM0, @PARAM1 parameters to prevent against sql injections from user input.
int count = 0;
foreach (KeyValuePair <string, object> pair in recordData)
{
sqlString += $"@PARAM{count},";
count++;
}
// Remove the last comma and add the last ) and ;
sqlString = sqlString.Remove(sqlString.Length - 1);
sqlString += ");";
// Get the command object inside a using statement to properly dispose/close.
using (MySqlCommand command = new MySqlCommand(sqlString, connection))
{
count = 0;
// Loop through the data again to add the parameter values to the corresponding @PARAMs in the string.
foreach (KeyValuePair <string, object> pair in recordData)
{
command.Parameters.AddWithValue($"@PARAM{count}", pair.Value);
count++;
}
// Asynchronously execute the non query.
await command.ExecuteNonQueryAsync().ConfigureAwait(false);
}
return(true);
}
}
/// <summary>
/// Update the <paramref name="record"/> in the data store based on the values that are not null inside it.
/// </summary>
/// <param name="record">The record containing the information to update (ISQLRecord)</param>
/// <returns>Task (bool) whether the function executed without exception.</returns>
public async Task <bool> UpdateAsync(ISQLRecord record)
{
// Try casting the record to a MapRecord, throw an argument exception if it fails.
try
{
MapRecord temp = (MapRecord)record;
}
catch
{
throw new ArgumentException(Constants.MapUpdateInvalidArgument);
}
// Get the record data.
MapRecord userRecord = (MapRecord)record;
IDictionary <string, object> recordData = userRecord.GetData();
// Get the connection inside a using statement to properly dispose/close.
using (MySqlConnection connection = new MySqlConnection(MapSQLConnection))
{
// Open the connection.
connection.Open();
// Construct the sql string to update the table name where..
string sqlString = $"UPDATE {Constants.MapDAOTableName} SET ";
// Loop through the record data.
int count = 0;
foreach (KeyValuePair <string, object> pair in recordData)
{
// Check if the value at the hash column is contained within the table, throw an argument
// exception if it doesn't exist.
if (pair.Key == Constants.MapDAOHashColumn)
{
if (!await CheckHashExistenceAsync((string)pair.Value).ConfigureAwait(false))
{
throw new ArgumentException(Constants.MapUpdateDNE);
}
}
// Update only the values where the record value is not null (string == null, numeric == -1).
// Again, use parameters to prevent against sql injections.
if (pair.Key != Constants.MapDAOHashColumn)
{
if (pair.Value is int)
{
if ((int)pair.Value != -1)
{
sqlString += $"{pair.Key} = @PARAM{count},";
}
}
if (pair.Value is string)
{
if (pair.Value != null)
{
sqlString += $"{pair.Key} = @PARAM{count},";
}
}
if (pair.Value is long)
{
if ((long)pair.Value != -1)
{
sqlString += $"{pair.Key} = @PARAM{count},";
}
}
}
count++;
}
// Remove the last comma and identify the record by its hash column.
sqlString = sqlString.Remove(sqlString.Length - 1);
sqlString += $" WHERE {Constants.MapDAOHashColumn} = '{recordData[Constants.MapDAOHashColumn]}';";
// Get the command inside a using statement to properly dispose/close.
using (MySqlCommand command = new MySqlCommand(sqlString, connection))
{
// Loop through the record data again to add values to the parameters.
count = 0;
foreach (KeyValuePair <string, object> pair in recordData)
{
if (pair.Key != Constants.MapDAOHashColumn)
{
if (pair.Value is int)
{
if ((int)pair.Value != -1)
{
command.Parameters.AddWithValue($"@PARAM{count}", pair.Value);
}
}
if (pair.Value is string)
{
if (pair.Value != null)
{
command.Parameters.AddWithValue($"@PARAM{count}", pair.Value);
}
}
if (pair.Value is long)
{
if ((long)pair.Value != -1)
{
command.Parameters.AddWithValue($"@PARAM{count}", pair.Value);
}
}
}
count++;
}
// Execute the non query asynchronously.
await command.ExecuteNonQueryAsync().ConfigureAwait(false);
}
return(true);
}
}
