/// <summary>
/// Executes a command against the database that has no output.
/// </summary>
/// <param name="connection">The database connection in which to execute the command.</param>
/// <param name="query">The query to be executed against the database.</param>
internal void ExecuteNonQuery(IDatabaseConnection connection, IDatabaseQuery query)
{
// exhausts the result set to avoid unexecuted statements
using (IDatabaseResult result = this.ExecuteQuery(connection, query))
{
do
{
while (result.Read())
{
}
}while (result.NextResult());
}
}
/// <summary>
/// Executes a command against the database that returns a single result.
/// </summary>
/// <param name="connection">The database connection in which to execute the command.</param>
/// <param name="query">The query to be executed against the database.</param>
/// <param name="managedType">The managed type that the database scalar result should be converted to.</param>
/// <returns>The first column of the first row in the result set, or a null reference if the result set is empty.</returns>
internal object ExecuteScalar(IDatabaseConnection connection, IDatabaseQuery query, Type managedType)
{
object scalar = null;
using (IDatabaseResult result = this.ExecuteQuery(connection, query))
{
if (result.Read())
{
scalar = result.GetValue(0, managedType);
}
}
return(scalar);
}
// In order to prevent SQL injection, validate if a given table exists in databases associated with the connection.
// Since we split online database into 2 databases, namely offline channel database and offline transaction database, therefore for each table,
// it only exists in one database.
private void ValidateTableExistence(IDatabaseConnection connection, string tableName)
{
const string TableNameParameter = "@TableName";
const int IndexOfName = 1;
string queryDatabaseList = "PRAGMA database_list;";
List <string> databaseList = new List <string>();
using (var result = this.ExecuteQuery(connection, new SqlQuery(queryDatabaseList)))
{
while (result.Read())
{
databaseList.Add(result.GetValue <string>(IndexOfName));
}
}
string queryTableListInRegularDbTemplate =
@"SELECT COUNT(*)
FROM [{0}].[SQLITE_MASTER]
WHERE TYPE = 'table' AND NAME = {1} COLLATE NOCASE;";
string queryTableListInTempDbTemplate =
@"SELECT COUNT(*)
FROM [TEMP].[SQLITE_TEMP_MASTER]
WHERE TYPE = 'table' AND NAME = {0} COLLATE NOCASE;";
bool doesTableExist = false;
foreach (string databaseName in databaseList)
{
SqlQuery query = null;
if (string.Compare(databaseName, "temp", StringComparison.OrdinalIgnoreCase) == 0)
{
query = new SqlQuery(string.Format(queryTableListInTempDbTemplate, TableNameParameter));
}
else
{
query = new SqlQuery(string.Format(queryTableListInRegularDbTemplate, databaseName, TableNameParameter));
}
query.Parameters.Add(TableNameParameter, tableName);
int count = 0;
using (IDatabaseResult result = this.ExecuteQuery(connection, query))
{
if (result.Read())
{
count = result.GetValue <int>(0);
}
}
// In current database, table names must be unique.
if (count == 1)
{
doesTableExist = true;
break;
}
}
if (!doesTableExist)
{
throw new ArgumentException(string.Format("Table {0} does not exist in databases associated with the connection.", tableName));
}
}
