/// <summary>
/// performs a single, simple update operation on a table
/// </summary>
/// <param name="tableName">name of the table to perform the updates within</param>
/// <param name="values">the columns to update and the values to update them to</param>
/// <param name="predicate">the predicate for the update operation</param>
public void Update(string tableName, List <DBPredicate> values, DBPredicate predicate)
{
lock ( m_databaseLock )
{
_VerifyConnection();
try
{
m_sqlConnection.Open();
SQLiteCommandBuilder builder = new SQLiteCommandBuilder();
using (SQLiteCommand command = m_sqlConnection.CreateCommand())
{
command.CommandText = $"UPDATE {builder.QuoteIdentifier(tableName)} SET ";
_AppendPredicates(command, values, ",");
command.CommandText += $" WHERE({builder.QuoteIdentifier(predicate.Column)} {_OperatorToString(predicate.Operator)} @predicateValue);";
command.Parameters.AddWithValue("@predicateValue", predicate.Value);
command.ExecuteNonQuery();
}
m_sqlConnection.Close();
}
catch (Exception crap)
{
m_sqlConnection.Close();
throw crap;
}
}
}
/// <summary>
/// deletes a row from a table
/// </summary>
/// <param name="tableName">the name of the table to remove from</param>
/// <param name="predicates">predicates for the removal of a single row</param>
/// <returns>the number of rows that were deleted</returns>
public int Delete(string tableName, DBPredicate predicate)
{
lock ( m_databaseLock )
{
_VerifyConnection();
try
{
m_sqlConnection.Open();
SQLiteCommandBuilder builder = new SQLiteCommandBuilder();
int result = 0;
using (SQLiteCommand command = m_sqlConnection.CreateCommand())
{
command.CommandText = $"DELETE FROM { builder.QuoteIdentifier(tableName)} WHERE({builder.QuoteIdentifier(predicate.Column)} {_OperatorToString(predicate.Operator)} @value0);";
command.Parameters.AddWithValue("@value0", predicate.Value);
result = command.ExecuteNonQuery();
}
m_sqlConnection.Close();
return(result);
}
catch (Exception crap)
{
m_sqlConnection.Close();
throw crap;
}
}
}
/// <summary>
/// Selects all rows from a table where a single predicate is true
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="tableName">name of the table to select from</param>
/// <param name="predicates">predicate which must evaluate to true</param>
/// <returns>the desired row, or null if there were no results</returns>
public T Select <T>(string tableName, DBPredicate predicate, DBDataContainerCreator <T> dataContainerCreator) where T : DBDataContainer <T>
{
lock ( m_databaseLock )
{
_VerifyConnection();
List <T> queryResults = null;
try
{
m_sqlConnection.Open();
using (SQLiteCommand command = m_sqlConnection.CreateCommand())
{
SQLiteCommandBuilder builder = new SQLiteCommandBuilder();
command.CommandText = $"SELECT * FROM {builder.QuoteIdentifier(tableName)} WHERE({builder.QuoteIdentifier(predicate.Column)} {_OperatorToString(predicate.Operator)} @value0);";
command.Parameters.AddWithValue("@value0", predicate.Value);
queryResults = _ExecuteSelectCommand <T>(command, dataContainerCreator);
}
}
catch (Exception crap)
{
m_sqlConnection.Close();
throw crap;
}
finally
{
m_sqlConnection.Close();
}
return((queryResults != null && queryResults.Count > 0) ? queryResults[0] : null);
}
}
