/// <summary>
/// This method executes a query using the given execution type and command
/// behavior and returns the results.
/// </summary>
/// <param name="commandText">
/// The text of the command to be executed.
/// </param>
/// <param name="executeType">
/// The execution type for the command. This is used to determine which method
/// of the command object to call, which then determines the type of results
/// returned, if any.
/// </param>
/// <param name="commandBehavior">
/// The command behavior flags for the command.
/// </param>
/// <param name="connection">
/// The connection used to create and execute the command.
/// </param>
/// <param name="args">
/// The SQL parameter values to be used when building the command object to be
/// executed, if any.
/// </param>
/// <returns>
/// The results of the query -OR- null if no results were produced from the
/// given execution type.
/// </returns>
public static object Execute(
string commandText,
SQLiteExecuteType executeType,
CommandBehavior commandBehavior,
SQLiteConnection connection,
params object[] args
)
{
SQLiteConnection.Check(connection);
using (SQLiteCommand command = connection.CreateCommand())
{
command.CommandText = commandText;
if (args != null)
{
foreach (object arg in args)
{
SQLiteParameter parameter = arg as SQLiteParameter;
if (parameter == null)
{
parameter = command.CreateParameter();
parameter.DbType = DbType.Object;
parameter.Value = arg;
}
command.Parameters.Add(parameter);
}
}
switch (executeType)
{
case SQLiteExecuteType.None:
{
//
// NOTE: Do nothing.
//
break;
}
case SQLiteExecuteType.NonQuery:
{
return(command.ExecuteNonQuery(commandBehavior));
}
case SQLiteExecuteType.Scalar:
{
return(command.ExecuteScalar(commandBehavior));
}
case SQLiteExecuteType.Reader:
{
return(command.ExecuteReader(commandBehavior));
}
}
}
return(null);
}
/// <summary>
/// Executes passed SQL string using appropriate SQLiteExecuteType.
/// </summary>
/// <param name="sql">SQL string to execute.</param>
/// <param name="executeType">Type of execution to use.</param>
private async void ExecuteSql(string sql, SQLiteExecuteType executeType = SQLiteExecuteType.Default)
{
try
{
// Open connection, if necessary.
if (Connection.State != System.Data.ConnectionState.Open)
{
await Connection.OpenAsync();
}
// Create command from passed SQL string.
var command = new SQLiteCommand(sql, Connection);
// Check executeType parameter.
switch (executeType)
{
case SQLiteExecuteType.Default:
var rowsAffected = await command.ExecuteNonQueryAsync();
// Output number of affected rows.
Logging.Log($"Query complete: {rowsAffected} row(s) affected.");
break;
case SQLiteExecuteType.Reader:
var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
// Retrieve values for each read row.
var values = new object[reader.FieldCount - 1];
reader.GetValues(values);
// Output values.
Logging.Log(values);
}
break;
case SQLiteExecuteType.Scalar:
await command.ExecuteScalarAsync();
break;
case SQLiteExecuteType.None:
break;
default:
// Throw exception if executeType value is something unexpected.
throw new ArgumentOutOfRangeException(nameof(executeType), executeType, null);
}
}
catch (System.Data.Linq.DuplicateKeyException exception)
{
// Output expected DuplicateKeyException.
Logging.Log(exception);
}
catch (SQLiteException exception)
{
// Output unexpected SQLiteException.
Logging.Log(exception, false);
}
}
/// <summary>
/// This method creates a new connection, executes the query using the given
/// execution type, closes the connection, and returns the results. If the
/// connection string is null, a temporary in-memory database connection will
/// be used.
/// </summary>
/// <param name="commandText">
/// The text of the command to be executed.
/// </param>
/// <param name="executeType">
/// The execution type for the command. This is used to determine which method
/// of the command object to call, which then determines the type of results
/// returned, if any.
/// </param>
/// <param name="connectionString">
/// The connection string to the database to be opened, used, and closed. If
/// this parameter is null, a temporary in-memory databse will be used.
/// </param>
/// <param name="args">
/// The SQL parameter values to be used when building the command object to be
/// executed, if any.
/// </param>
/// <returns>
/// The results of the query -OR- null if no results were produced from the
/// given execution type.
/// </returns>
public static object Execute(
string commandText,
SQLiteExecuteType executeType,
string connectionString,
params object[] args
)
{
return(Execute(
commandText, executeType, CommandBehavior.Default,
connectionString, args));
}
/// <summary>
/// This method creates a new connection, executes the query using the given
/// execution type and command behavior, closes the connection, and returns
/// the results. If the connection string is null, a temporary in-memory
/// database connection will be used.
/// </summary>
/// <param name="commandText">
/// The text of the command to be executed.
/// </param>
/// <param name="executeType">
/// The execution type for the command. This is used to determine which method
/// of the command object to call, which then determines the type of results
/// returned, if any.
/// </param>
/// <param name="commandBehavior">
/// The command behavior flags for the command.
/// </param>
/// <param name="connectionString">
/// The connection string to the database to be opened, used, and closed. If
/// this parameter is null, a temporary in-memory databse will be used.
/// </param>
/// <param name="args">
/// The SQL parameter values to be used when building the command object to be
/// executed, if any.
/// </param>
/// <returns>
/// The results of the query -OR- null if no results were produced from the
/// given execution type.
/// </returns>
public static object Execute(
string commandText,
SQLiteExecuteType executeType,
CommandBehavior commandBehavior,
string connectionString,
params object[] args
)
{
if (connectionString == null)
{
connectionString = DefaultConnectionString;
}
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
connection.Open();
using (SQLiteCommand command = connection.CreateCommand())
{
command.CommandText = commandText;
if (args != null)
{
foreach (object arg in args)
{
if (arg is SQLiteParameter)
{
command.Parameters.Add((SQLiteParameter)arg);
}
else
{
command.Parameters.Add(new SQLiteParameter(DbType.Object, arg));
}
}
}
switch (executeType)
{
case SQLiteExecuteType.None:
{
//
// NOTE: Do nothing.
//
break;
}
case SQLiteExecuteType.NonQuery:
{
return(command.ExecuteNonQuery(commandBehavior));
}
case SQLiteExecuteType.Scalar:
{
return(command.ExecuteScalar(commandBehavior));
}
case SQLiteExecuteType.Reader:
{
return(command.ExecuteReader(commandBehavior));
}
}
}
}
return(null);
}
/// <summary>
/// This method creates a new connection, executes the query using the given
/// execution type and command behavior, closes the connection unless a data
/// reader is created, and returns the results. If the connection string is
/// null, a temporary in-memory database connection will be used.
/// </summary>
/// <param name="commandText">
/// The text of the command to be executed.
/// </param>
/// <param name="executeType">
/// The execution type for the command. This is used to determine which method
/// of the command object to call, which then determines the type of results
/// returned, if any.
/// </param>
/// <param name="commandBehavior">
/// The command behavior flags for the command.
/// </param>
/// <param name="connectionString">
/// The connection string to the database to be opened, used, and closed. If
/// this parameter is null, a temporary in-memory databse will be used.
/// </param>
/// <param name="args">
/// The SQL parameter values to be used when building the command object to be
/// executed, if any.
/// </param>
/// <returns>
/// The results of the query -OR- null if no results were produced from the
/// given execution type.
/// </returns>
public static object Execute(
string commandText,
SQLiteExecuteType executeType,
CommandBehavior commandBehavior,
string connectionString,
params object[] args
)
{
SQLiteConnection connection = null;
try
{
if (connectionString == null)
{
connectionString = DefaultConnectionString;
}
using (connection = new SQLiteConnection(connectionString))
{
connection.Open();
using (SQLiteCommand command = connection.CreateCommand())
{
command.CommandText = commandText;
if (args != null)
{
foreach (object arg in args)
{
SQLiteParameter parameter = arg as SQLiteParameter;
if (parameter == null)
{
parameter = command.CreateParameter();
parameter.DbType = DbType.Object;
parameter.Value = arg;
}
command.Parameters.Add(parameter);
}
}
switch (executeType)
{
case SQLiteExecuteType.None:
{
//
// NOTE: Do nothing.
//
break;
}
case SQLiteExecuteType.NonQuery:
{
return(command.ExecuteNonQuery(commandBehavior));
}
case SQLiteExecuteType.Scalar:
{
return(command.ExecuteScalar(commandBehavior));
}
case SQLiteExecuteType.Reader:
{
bool success = true;
try
{
//
// NOTE: The CloseConnection flag is being added here.
// This should force the returned data reader to
// close the connection when it is disposed. In
// order to prevent the containing using block
// from disposing the connection prematurely,
// the innermost finally block sets the internal
// no-disposal flag to true. The outer finally
// block will reset the internal no-disposal flag
// to false so that the data reader will be able
// to (eventually) dispose of the connection.
//
return(command.ExecuteReader(
commandBehavior | CommandBehavior.CloseConnection));
}
catch
{
success = false;
throw;
}
finally
{
//
// NOTE: If an exception was not thrown, that can only
// mean the data reader was successfully created
// and now owns the connection. Therefore, set
// the internal no-disposal flag (temporarily)
// in order to exit the containing using block
// without disposing it.
//
if (success)
{
connection._noDispose = true;
}
}
}
}
}
}
}
finally
{
//
// NOTE: Now that the using block has been exited, reset the
// internal disposal flag for the connection. This is
// always done if the connection was created because
// it will be harmless whether or not the data reader
// now owns it.
//
if (connection != null)
{
connection._noDispose = false;
}
}
return(null);
}
/// <summary>
/// This method creates a new connection, executes the query using the given
/// execution type and command behavior, closes the connection unless a data
/// reader is created, and returns the results. If the connection string is
/// null, a temporary in-memory database connection will be used.
/// </summary>
/// <param name="commandText">
/// The text of the command to be executed.
/// </param>
/// <param name="executeType">
/// The execution type for the command. This is used to determine which method
/// of the command object to call, which then determines the type of results
/// returned, if any.
/// </param>
/// <param name="commandBehavior">
/// The command behavior flags for the command.
/// </param>
/// <param name="connectionString">
/// The connection string to the database to be opened, used, and closed. If
/// this parameter is null, a temporary in-memory databse will be used.
/// </param>
/// <param name="args">
/// The SQL parameter values to be used when building the command object to be
/// executed, if any.
/// </param>
/// <returns>
/// The results of the query -OR- null if no results were produced from the
/// given execution type.
/// </returns>
public static object Execute(
string commandText,
SQLiteExecuteType executeType,
CommandBehavior commandBehavior,
string connectionString,
params object[] args
)
{
SQLiteConnection connection = null;
try
{
if (connectionString == null)
connectionString = DefaultConnectionString;
using (connection = new SQLiteConnection(connectionString))
{
connection.Open();
using (SQLiteCommand command = connection.CreateCommand())
{
command.CommandText = commandText;
if (args != null)
{
foreach (object arg in args)
{
if (arg is SQLiteParameter)
command.Parameters.Add((SQLiteParameter)arg);
else
command.Parameters.Add(new SQLiteParameter(DbType.Object, arg));
}
}
switch (executeType)
{
case SQLiteExecuteType.None:
{
//
// NOTE: Do nothing.
//
break;
}
case SQLiteExecuteType.NonQuery:
{
return command.ExecuteNonQuery(commandBehavior);
}
case SQLiteExecuteType.Scalar:
{
return command.ExecuteScalar(commandBehavior);
}
case SQLiteExecuteType.Reader:
{
bool success = true;
try
{
//
// NOTE: The CloseConnection flag is being added here.
// This should force the returned data reader to
// close the connection when it is disposed. In
// order to prevent the containing using block
// from disposing the connection prematurely,
// the innermost finally block sets the internal
// no-disposal flag to true. The outer finally
// block will reset the internal no-disposal flag
// to false so that the data reader will be able
// to (eventually) dispose of the connection.
//
return command.ExecuteReader(
commandBehavior | CommandBehavior.CloseConnection);
}
catch
{
success = false;
throw;
}
finally
{
//
// NOTE: If an exception was not thrown, that can only
// mean the data reader was successfully created
// and now owns the connection. Therefore, set
// the internal no-disposal flag (temporarily)
// in order to exit the containing using block
// without disposing it.
//
if (success)
connection._noDispose = true;
}
}
}
}
}
}
finally
{
//
// NOTE: Now that the using block has been exited, reset the
// internal disposal flag for the connection. This is
// always done if the connection was created because
// it will be harmless whether or not the data reader
// now owns it.
//
if (connection != null)
connection._noDispose = false;
}
return null;
}
/// <summary>
/// This method creates a new connection, executes the query using the given
/// execution type, closes the connection, and returns the results. If the
/// connection string is null, a temporary in-memory database connection will
/// be used.
/// </summary>
/// <param name="commandText">
/// The text of the command to be executed.
/// </param>
/// <param name="executeType">
/// The execution type for the command. This is used to determine which method
/// of the command object to call, which then determines the type of results
/// returned, if any.
/// </param>
/// <param name="connectionString">
/// The connection string to the database to be opened, used, and closed. If
/// this parameter is null, a temporary in-memory databse will be used.
/// </param>
/// <param name="args">
/// The SQL parameter values to be used when building the command object to be
/// executed, if any.
/// </param>
/// <returns>
/// The results of the query -OR- null if no results were produced from the
/// given execution type.
/// </returns>
public static object Execute(
string commandText,
SQLiteExecuteType executeType,
string connectionString,
params object[] args
)
{
return Execute(
commandText, executeType, CommandBehavior.Default,
connectionString, args);
}
/// <summary>
/// This method creates a new connection, executes the query using the given
/// execution type and command behavior, closes the connection, and returns
/// the results. If the connection string is null, a temporary in-memory
/// database connection will be used.
/// </summary>
/// <param name="commandText">
/// The text of the command to be executed.
/// </param>
/// <param name="executeType">
/// The execution type for the command. This is used to determine which method
/// of the command object to call, which then determines the type of results
/// returned, if any.
/// </param>
/// <param name="commandBehavior">
/// The command behavior flags for the command.
/// </param>
/// <param name="connectionString">
/// The connection string to the database to be opened, used, and closed. If
/// this parameter is null, a temporary in-memory databse will be used.
/// </param>
/// <param name="args">
/// The SQL parameter values to be used when building the command object to be
/// executed, if any.
/// </param>
/// <returns>
/// The results of the query -OR- null if no results were produced from the
/// given execution type.
/// </returns>
public static object Execute(
string commandText,
SQLiteExecuteType executeType,
CommandBehavior commandBehavior,
string connectionString,
params object[] args
)
{
if (connectionString == null)
connectionString = DefaultConnectionString;
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
connection.Open();
using (SQLiteCommand command = connection.CreateCommand())
{
command.CommandText = commandText;
if (args != null)
{
foreach (object arg in args)
{
if (arg is SQLiteParameter)
command.Parameters.Add((SQLiteParameter)arg);
else
command.Parameters.Add(new SQLiteParameter(DbType.Object, arg));
}
}
switch (executeType)
{
case SQLiteExecuteType.None:
{
//
// NOTE: Do nothing.
//
break;
}
case SQLiteExecuteType.NonQuery:
{
return command.ExecuteNonQuery(commandBehavior);
}
case SQLiteExecuteType.Scalar:
{
return command.ExecuteScalar(commandBehavior);
}
case SQLiteExecuteType.Reader:
{
return command.ExecuteReader(commandBehavior);
}
}
}
}
return null;
}
public static object Execute(string commandText, SQLiteExecuteType executeType, CommandBehavior commandBehavior, string connectionString, params object[] args)
{
object obj;
SQLiteConnection sQLiteConnection = null;
try
{
if (connectionString == null)
{
connectionString = SQLiteCommand.DefaultConnectionString;
}
SQLiteConnection sQLiteConnection1 = new SQLiteConnection(connectionString);
sQLiteConnection = sQLiteConnection1;
using (sQLiteConnection1)
{
sQLiteConnection.Open();
using (SQLiteCommand sQLiteCommand = sQLiteConnection.CreateCommand())
{
sQLiteCommand.CommandText = commandText;
if (args != null)
{
object[] objArray = args;
for (int i = 0; i < (int)objArray.Length; i++)
{
object obj1 = objArray[i];
SQLiteParameter sQLiteParameter = obj1 as SQLiteParameter;
if (sQLiteParameter == null)
{
sQLiteParameter = sQLiteCommand.CreateParameter();
sQLiteParameter.DbType = DbType.Object;
sQLiteParameter.Value = obj1;
}
sQLiteCommand.Parameters.Add(sQLiteParameter);
}
}
switch (executeType)
{
case SQLiteExecuteType.None:
{
break;
}
case SQLiteExecuteType.NonQuery:
{
obj = sQLiteCommand.ExecuteNonQuery(commandBehavior);
return(obj);
}
case SQLiteExecuteType.Scalar:
{
obj = sQLiteCommand.ExecuteScalar(commandBehavior);
return(obj);
}
case SQLiteExecuteType.Reader:
{
bool flag = true;
try
{
try
{
obj = sQLiteCommand.ExecuteReader(commandBehavior | CommandBehavior.CloseConnection);
return(obj);
}
catch
{
flag = false;
throw;
}
}
finally
{
if (flag)
{
sQLiteConnection._noDispose = true;
}
}
break;
}
default:
{
goto case SQLiteExecuteType.None;
}
}
}
}
return(null);
}
finally
{
if (sQLiteConnection != null)
{
sQLiteConnection._noDispose = false;
}
}
return(obj);
}
