/// <summary>
/// Executes a database query in the opened database.
/// </summary>
/// <param name="communicator">The <see cref="AccessComm"/> to execute the query in.</param>
private void QueryDB(ref AccessComm communicator)
{
//Loops as long as the user doesn't type "quit"
while (true)
{
//Informs the user that a database has to be opened for this command to work
if (communicator?.IsDisposed == null || communicator?.IsDisposed == true)
{
Console.WriteLine("Please open a database first to execute queries.");
break;
}
Console.WriteLine("Write \"quit\" to return and quit entering SQL queries.");
//Reads the user query
Console.Write("Query: ");
string query = Console.ReadLine()?.Trim();
//Returns to caller
if (query?.ToLowerInvariant().Equals("quit") ?? false)
{
break;
}
//Tries to execute the query
Console.WriteLine("Executing query...");
var res = new QueryResult();
try
{
res = communicator.ExecuteQuery(query).GetAwaiter().GetResult();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
//Outputs if the query was successful and how many database records were affected
Console.WriteLine($"Query executed. Status: {(res.Success ? "Success" : "Failed")}\n" +
$"Records affected: {res.RecordsAffected}");
//Checks if the query returned some data
if (res.ReturnedRows?.Count <= 0 || res.ReturnedRows == null)
{
Console.WriteLine();
continue;
}
//Displays the returned data
Console.WriteLine();
Console.WriteLine("Query returned data:");
Console.WriteLine(string.Join(", ", res.ColumnNames));
for (int y = 0; y < res.ReturnedRows.Count; y++)
{
Console.WriteLine(string.Join(", ", res.ReturnedRows[y]));
}
Console.WriteLine();
}
}
/// <summary>
/// Wrapper method to execute and log a SQL query.
/// </summary>
/// <param name="query">The query that should be executed.</param>
/// <returns>The query results.</returns>
public static async Task <QueryResult> ExecuteQuery(string query)
{
QueryResult queryRes = await _accessDB.ExecuteQuery(query);
lock (DBPassword)
{
Log.Write("Executed SQL query:", 1, TraceEventType.Information, true);
Log.Write($"Query: {queryRes.ExecutedQuery}\nSuccess: {queryRes.Success}", 2, null);
}
return(queryRes);
}