/// <summary>
/// Execute a SQLiteCommand (that returns a 1x1 resultset) against the specified SQLiteTransaction
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int orderCount = (int)ExecuteScalar(trans, CommandType.Text, "Select count(Order) from TableTransaction where ProdId=?", new SQLiteParameter("@prodid", 24));
/// </remarks>
/// <param name="transaction">A valid SQLiteTransaction</param>
/// <param name="commandType">The CommandType (TableDirect, Text)</param>
/// <param name="commandText">The T-SQL command</param>
/// <param name="commandParameters">An array of SQLiteParamters used to execute the command</param>
/// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
public static object ExecuteScalar(SQLiteTransaction transaction, CommandType commandType, string commandText, params SQLiteParameter[] commandParameters)
{
if( transaction == null ) throw new ArgumentNullException( "transaction" );
if( transaction != null && transaction.Connection == null ) throw new ArgumentException( "The transaction was rollbacked or commited, please provide an open transaction.", "transaction" );
// Create a command and prepare it for execution
SQLiteCommand cmd = new SQLiteCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, (SQLiteConnection)transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
// Execute the command & return the results
object retval = cmd.ExecuteScalar();
// Detach the SQLiteParameters from the command object, so they can be used again
cmd.Parameters.Clear();
return retval;
}
// If it's an Insert we fetch the @@Identity value and stuff it in the proper column
protected void OnRowUpdated(object sender, RowUpdatedEventArgs e)
{
try
{
if(e.Status == UpdateStatus.Continue && e.StatementType == StatementType.Insert)
{
TransactionMgr txMgr = TransactionMgr.ThreadTransactionMgr();
string[] identityCols = this.GetAutoKeyColumns().Split(';');
SQLiteCommand cmd = new SQLiteCommand();
foreach(string col in identityCols)
{
cmd.CommandText = "SELECT last_insert_rowid()";
// We make sure we enlist in the ongoing transaction, otherwise, we
// would most likely deadlock
txMgr.Enlist(cmd, this);
object o = cmd.ExecuteScalar(); // Get the Identity Value
txMgr.DeEnlist(cmd, this);
if(o != null)
{
e.Row[col] = o;
}
}
e.Row.AcceptChanges();
}
}
catch {}
}
/// <summary>
/// Execute a SQLiteCommand (that returns a 1x1 resultset) against the specified SQLiteConnection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int orderCount = (int)ExecuteScalar(conn, CommandType.Text, "Select count(Order) from TableTransaction where ProdId=?", new SQLiteParameter("@prodid", 24));
/// </remarks>
/// <param name="connection">A valid SQLiteConnection</param>
/// <param name="commandType">The CommandType (TableDirect, Text)</param>
/// <param name="commandText">The T-SQL command</param>
/// <param name="commandParameters">An array of SQLiteParamters used to execute the command</param>
/// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>
public static object ExecuteScalar(SQLiteConnection connection, CommandType commandType, string commandText, params SQLiteParameter[] commandParameters)
{
if( connection == null ) throw new ArgumentNullException( "connection" );
// Create a command and prepare it for execution
SQLiteCommand cmd = new SQLiteCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, connection, (SQLiteTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection );
// Execute the command & return the results
object retval = cmd.ExecuteScalar();
// Detach the SQLiteParameters from the command object, so they can be used again
cmd.Parameters.Clear();
if( mustCloseConnection )
connection.Close();
return retval;
}