/// <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;
}
}
}
private SQLiteCommand _BuildSelectCommand(string tableName, List <DBPredicate> predicates, string combiner)
{
try
{
m_sqlConnection.Open();
SQLiteCommand command = m_sqlConnection.CreateCommand();
SQLiteCommandBuilder builder = new SQLiteCommandBuilder();
command.CommandText = $"SELECT * FROM {builder.QuoteIdentifier(tableName)} WHERE";
for (int i = 0; i < predicates.Count; i++)
{
command.CommandText += $" {builder.QuoteIdentifier(predicates[i].Column)} {_ParseOperator(predicates[i].Operator)} @value{i}";
command.Parameters.AddWithValue($"@value{i}", predicates[i].Value);
if (i < predicates.Count - 1)
{
command.CommandText += $" {combiner}";
}
}
command.CommandText += ";";
m_sqlConnection.Close();
return(command);
}
catch (Exception crap)
{
m_sqlConnection.Close();
throw crap;
}
}
/// <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;
}
}
}
/// <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>
/// Inserts multiple rows into a table
/// </summary>
/// <param name="tableName">table to insert into</param>
/// <param name="values">a list of rows to insert. each row must have parameters matching the number and order of columns in the table</param>
/// <returns>the number of rows that were inserted</returns>
public int Insert <T>(string tableName, List <T> values) where T : DBDataContainer <T>
{
SQLiteCommandBuilder builder = new SQLiteCommandBuilder();
string query = $"INSERT INTO {builder.QuoteIdentifier(tableName)} VALUES";
for (int a = 0; a < values.Count; a++)
{
query += "(";
for (int i = 0; i < values[a].PropertyCount(); i++)
{
query += $"{values[a][i]}";
if (i < values[a].PropertyCount() - 1)
{
query += ", ";
}
}
query += ")";
if (a < values.Count - 1)
{
query += ", ";
}
}
query += ";";
return(ExecuteNonQuery(query));
}
public string QuoteIdentifier(string identifier)
{
if (_builder == null)
{
_builder = new SQLiteCommandBuilder();
}
return(_builder.QuoteIdentifier(identifier));
}
/// <summary>
/// Inserts a new row into a table
/// </summary>
/// <param name="collisionAction">what to do if a row already exists</param>
/// <param name="tableName">table to insert into</param>
/// <param name="values">the data for the row to insert. each row must have parameters matching the number and order of columns in the table</param>
/// <returns>the number of rows that were inserted</returns>
public int Insert <T>(DBCollisionAction collisionAction, string tableName, T value) where T : DBDataContainer <T>
{
lock ( m_databaseLock )
{
try
{
m_sqlConnection.Open();
SQLiteCommandBuilder builder = new SQLiteCommandBuilder();
int result = 0;
using (SQLiteCommand command = m_sqlConnection.CreateCommand())
{
if (collisionAction == DBCollisionAction.IGNORE)
{
command.CommandText = $"INSERT OR IGNORE";
}
else
{
command.CommandText = $"INSERT OR REPLACE";
}
command.CommandText += $" INTO {builder.QuoteIdentifier(tableName)} VALUES("; //NULL will make sqlite generate a primary key automatically
for (int i = 0; i < value.PropertyCount(); i++)
{
if (value[i] == "NULL")
{
command.CommandText += "NULL";
}
else
{
command.CommandText += $"@value{i}";
command.Parameters.AddWithValue($"@value{i}", value[i]);
}
if (i < value.PropertyCount() - 1)
{
command.CommandText += ", ";
}
}
command.CommandText += ");";
result = command.ExecuteNonQuery();
}
m_sqlConnection.Close();
return(result);
}
catch (Exception crap)
{
m_sqlConnection.Close();
throw crap;
}
}
}
/// <summary>
/// inserts a row into a table
/// </summary>
/// <param name="collisionAction">what to do if a row already exists</param>
/// <param name="tableName">table to insert into</param>
/// <param name="values">a list of column-value pairs to insert</param>
public void Insert(DBCollisionAction collisionAction, string tableName, List <DBParameter> values)
{
lock ( m_databaseLock )
{
try
{
m_sqlConnection.Open();
SQLiteCommandBuilder builder = new SQLiteCommandBuilder();
using (SQLiteCommand command = m_sqlConnection.CreateCommand())
{
if (collisionAction == DBCollisionAction.IGNORE)
{
command.CommandText = $"INSERT OR IGNORE";
}
else
{
command.CommandText = $"INSERT OR REPLACE";
}
command.CommandText += $" INTO {builder.QuoteIdentifier(tableName)}";
string queryColumns = "(";
string queryValues = "(";
for (int i = 0; i < values.Count(); i++)
{
queryColumns += values[i].Column;
queryValues += values[i].Value;
if (i < values.Count() - 1)
{
queryColumns += ", ";
queryValues += ", ";
}
}
queryValues += ")";
queryColumns += ")";
command.CommandText += queryColumns + " VALUES" + queryValues + ";";
command.ExecuteNonQuery();
}
m_sqlConnection.Close();
}
catch (Exception crap)
{
m_sqlConnection.Close();
throw crap;
}
}
}
/// <summary>
/// efficiently deletes multiple rows from a table
/// </summary>
/// <param name="tableName">the name of the table to remove from</param>
/// <param name="predicates">each list contains the predicates for the removal of a single row</param>
public void DeleteTransacted(string tableName, List <List <DBPredicate> > predicates)
{
lock ( m_databaseLock )
{
try
{
m_sqlConnection.Open();
SQLiteCommandBuilder builder = new SQLiteCommandBuilder();
using (SQLiteTransaction t = m_sqlConnection.BeginTransaction())
{
using (SQLiteCommand command = m_sqlConnection.CreateCommand())
{
for (int a = 0; a < predicates.Count; a++)
{
command.CommandText = $"DELETE FROM { builder.QuoteIdentifier(tableName)} WHERE(";
for (int i = 0; i < predicates[a].Count; i++)
{
command.CommandText += $"{predicates[a][i].Column} {_OperatorToString(predicates[a][i].Operator)} @value{i}";
command.Parameters.AddWithValue($"@value{i}", predicates[a][i].Value);
if (i < predicates[a].Count - 1)
{
command.CommandText += " AND ";
}
}
command.CommandText += ");";
command.ExecuteNonQuery();
}
}
t.Commit();
}
m_sqlConnection.Close();
}
catch (Exception crap)
{
m_sqlConnection.Close();
throw crap;
}
}
}
/// <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);
}
}
private void _AppendPredicates(SQLiteCommand command, List <DBPredicate> predicates, string conjunction)
{
if (command == null)
{
return;
}
SQLiteCommandBuilder builder = new SQLiteCommandBuilder();
int valueIndex = command.Parameters.Count;
for (int i = 0; i < predicates.Count; i++)
{
command.CommandText += $"{builder.QuoteIdentifier(predicates[i].Column)} {_OperatorToString(predicates[i].Operator)} @value{valueIndex}";
command.Parameters.AddWithValue($"@value{valueIndex}", predicates[i].Value);
if (i < predicates.Count - 1)
{
command.CommandText += $" {conjunction} ";
}
valueIndex++;
}
}
/// <summary>
/// performs a single 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="conjunction">defines how the predicates should be joined in the query</param>
/// <param name="predicates">the predicates for the update operation, all of which must be true for the operation to succeed</param>
public void Update(string tableName, List <DBPredicate> values, List <DBPredicate> predicates, DBConjunction conjunction = DBConjunction.AND)
{
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(";
_AppendPredicates(command, predicates, conjunction.ToString());
command.CommandText += ");";
Debug.WriteLine("Executing update query: " + command.CommandText);
command.ExecuteNonQuery();
}
m_sqlConnection.Close();
}
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);
}
}
/// <summary>
/// Turn a datatable into a table in the temporary database for the connection
/// </summary>
/// <param name="cnn">The connection to make the temporary table in</param>
/// <param name="table">The table to write out</param>
/// <param name="dest">The temporary table name to write to</param>
private void DataTableToTable(SQLiteConnection cnn, DataTable table, string dest)
{
StringBuilder sql = new StringBuilder();
SQLiteCommandBuilder builder = new SQLiteCommandBuilder();
using (SQLiteCommand cmd = cnn.CreateCommand())
using (DataTable source = new DataTable())
{
sql.AppendFormat(CultureInfo.InvariantCulture, "CREATE TEMP TABLE {0} (", builder.QuoteIdentifier(dest));
string separator = String.Empty;
SQLiteConnectionFlags flags = cnn.Flags;
foreach (DataColumn dc in table.Columns)
{
DbType dbtypeName = SQLiteConvert.TypeToDbType(dc.DataType);
string typeName = SQLiteConvert.DbTypeToTypeName(cnn, dbtypeName, flags);
sql.AppendFormat(CultureInfo.InvariantCulture, "{2}{0} {1} COLLATE NOCASE", builder.QuoteIdentifier(dc.ColumnName), typeName, separator);
separator = ", ";
}
sql.Append(")");
cmd.CommandText = sql.ToString();
cmd.ExecuteNonQuery();
cmd.CommandText = String.Format("SELECT * FROM TEMP.{0} WHERE 1=2", builder.QuoteIdentifier(dest));
using (SQLiteDataAdapter adp = new SQLiteDataAdapter(cmd))
{
builder.DataAdapter = adp;
adp.Fill(source);
foreach (DataRow row in table.Rows)
{
object[] arr = row.ItemArray;
source.Rows.Add(arr);
}
adp.Update(source);
}
}
}
/// <summary>
/// Turn a datatable into a table in the temporary database for the connection
/// </summary>
/// <param name="cnn">The connection to make the temporary table in</param>
/// <param name="table">The table to write out</param>
/// <param name="dest">The temporary table name to write to</param>
private void DataTableToTable(SQLiteConnection cnn, DataTable table, string dest)
{
StringBuilder sql = new StringBuilder();
SQLiteCommandBuilder builder = new SQLiteCommandBuilder();
using (SQLiteCommand cmd = cnn.CreateCommand())
using (DataTable source = new DataTable())
{
sql.AppendFormat(CultureInfo.InvariantCulture, "CREATE TEMP TABLE {0} (", builder.QuoteIdentifier(dest));
string separator = String.Empty;
SQLiteConnectionFlags flags = cnn.Flags;
foreach (DataColumn dc in table.Columns)
{
DbType dbtypeName = SQLiteConvert.TypeToDbType(dc.DataType);
string typeName = SQLiteConvert.DbTypeToTypeName(cnn, dbtypeName, flags);
sql.AppendFormat(CultureInfo.InvariantCulture, "{2}{0} {1} COLLATE NOCASE", builder.QuoteIdentifier(dc.ColumnName), typeName, separator);
separator = ", ";
}
sql.Append(")");
cmd.CommandText = sql.ToString();
cmd.ExecuteNonQuery();
cmd.CommandText = String.Format("SELECT * FROM TEMP.{0} WHERE 1=2", builder.QuoteIdentifier(dest));
using (SQLiteDataAdapter adp = new SQLiteDataAdapter(cmd))
{
builder.DataAdapter = adp;
adp.Fill(source);
foreach (DataRow row in table.Rows)
{
object[] arr = row.ItemArray;
source.Rows.Add(arr);
}
adp.Update(source);
}
}
}
