/// <summary>
/// Selects all rows and columns from the database
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="tableName">name of the table to select from</param>
/// <returns>a list of data containers of the specified type containing the results, or null if there were no results</returns>
public List <T> SelectAll <T>(string tableName, DBDataContainerCreator <T> dataContainerCreator) where T : DBDataContainer <T>
{
lock ( m_databaseLock )
{
_VerifyConnection();
try
{
m_sqlConnection.Open();
List <T> results = new List <T>();
using (SQLiteCommand command = m_sqlConnection.CreateCommand())
{
SQLiteCommandBuilder builder = new SQLiteCommandBuilder();
command.CommandText = $"SELECT * FROM {builder.QuoteIdentifier(tableName)};";
results = _ExecuteSelectCommand <T>(command, dataContainerCreator);
}
m_sqlConnection.Close();
return(results);
}
catch (Exception crap)
{
m_sqlConnection.Close();
throw crap;
}
}
}
public List <T> _ExecuteSelectCommand <T>(SQLiteCommand command, DBDataContainerCreator <T> dataContainerCreator) where T : DBDataContainer <T>
{
_VerifyConnection();
List <T> resultContainers = new List <T>();
SQLiteDataReader result = command.ExecuteReader();
if (result.HasRows == false)
{
return(resultContainers);
}
while (result.Read())
{
T container = dataContainerCreator.Create();
for (int i = 0; i < result.FieldCount; i++)
{
container[result.GetName(i)] = result.GetValue(i).ToString();
}
resultContainers.Add(container);
}
return(resultContainers);
}
/// <summary>
/// Selects all rows from a table where all of a list of predicates are true
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="tableName">name of the table to select from</param>
/// <param name="predicates">list of predicates, all of which must evaluate to true</param>
/// <param name="predicateConjunction">how the list of predicates should be joined when performing the query</param>
/// <returns>a list of data containers, each containing one row of the results, or null if there were no results</returns>
public List <T> Select <T>(string tableName, List <DBPredicate> predicates, DBDataContainerCreator <T> dataContainerCreator, DBConjunction predicateConjunction) where T : DBDataContainer <T>
{
lock ( m_databaseLock )
{
_VerifyConnection();
List <T> queryResults = new List <T>();
try
{
m_sqlConnection.Open();
using (SQLiteCommand command = m_sqlConnection.CreateCommand())
{
SQLiteCommandBuilder builder = new SQLiteCommandBuilder();
command.CommandText = $"SELECT * FROM {builder.QuoteIdentifier(tableName)} WHERE ";
_AppendPredicates(command, predicates, predicateConjunction.ToString());
command.CommandText += ";";
queryResults = _ExecuteSelectCommand <T>(command, dataContainerCreator);
}
}
catch (Exception crap)
{
m_sqlConnection.Close();
throw crap;
}
finally
{
m_sqlConnection.Close();
}
return(queryResults);
}
}
/// <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);
}
}
