} // end method
/// <summary>
/// Selects the max value of the specified table and column.
/// </summary>
/// <param name="table">
/// The name of the table from which to query.
/// </param>
/// <param name="column">
/// The name of the column for which to select the max value.
/// </param>
/// <returns>
/// The maximum value of the column in the table
/// or 0 if the value is null or a non-integer.
/// </returns>
protected virtual long GetMaxValue(IDbCommand command, string table, string column) {
if (command == null)
using (var con = openConnection()) {
if (con == null)
return 0;
using (var cmd = con.CreateCommand())
return GetMaxValue(cmd, table, column);
} // end using
command.CommandText = "SELECT MAX(" + column + ") FROM " + table;
object result = command.ExecuteScalar();
return DataTool.AsLong(result) ?? 0;
} // end method
} // end method
private void WaitForValidateLockingRecord() {
bool exists = false;
while (!exists)
using (var con = openConnection())
using (var cmd = con.CreateCommand()) {
cmd.CommandText = "SELECT COUNT(*) FROM " + BackingTableName + Environment.NewLine +
"WHERE TableName = " + cmd.FormatParameter("TableName") + Environment.NewLine +
"AND ColumnName = " + cmd.FormatParameter("ColumnName");
cmd.AddParameter("TableName", LockKey);
cmd.AddParameter("ColumnName", LockKey);
var count = cmd.ExecuteScalar();
exists = (DataTool.AsLong(count) ?? 0) == 1;
Thread.Sleep(50);
} // end using
} // end method
} // end method
private void ValidateLocking() {
object sequenceObject;
SetupValidateLockingRecord();
WaitForValidateLockingRecord();
using (var con = openConnection())
using (var cmd = con.CreateCommand()) {
cmd.AddParameter("TableName", LockKey);
cmd.AddParameter("ColumnName", LockKey);
var thread = new Thread(() => {
GetNextValues(LockKey, LockKey, 1);
});
using (var transaction = con.BeginTransaction()) {
cmd.CommandText = "UPDATE " + BackingTableName + Environment.NewLine +
"SET SequenceValue = SequenceValue + 1" + Environment.NewLine +
"WHERE TableName = " + cmd.FormatParameter("TableName") + Environment.NewLine +
"AND ColumnName = " + cmd.FormatParameter("ColumnName");
cmd.Transaction = transaction;
cmd.ExecuteNonQuery();
thread.Start();
Thread.Sleep(300);
cmd.CommandText = "SELECT SequenceValue " + Environment.NewLine +
"FROM " + BackingTableName + Environment.NewLine +
"WHERE TableName = " + cmd.FormatParameter("TableName") + Environment.NewLine +
"AND ColumnName = " + cmd.FormatParameter("ColumnName");
sequenceObject = cmd.ExecuteScalar();
transaction.Commit();
} // end using
thread.Join();
cmd.CommandText = "DELETE FROM " + BackingTableName + Environment.NewLine +
"WHERE TableName = " + cmd.FormatParameter("TableName") + Environment.NewLine +
"AND ColumnName = " + cmd.FormatParameter("ColumnName");
cmd.ExecuteNonQuery();
} // end using
var sequence = DataTool.AsLong(sequenceObject) ?? -1;
if (sequence == -1)
throw new DataException("The SequenceValue column of " + BackingTableName +
" does not appear to be an integer type.");
if (sequence > 1)
throw new DataException("The database or the table " + BackingTableName +
" does not support locking.");
} // end method
} // end method
/// <summary>
/// Gets the next value in the specified sequence and increases the seed by
/// the specified amount. For example, if the seed is currently 10 and
/// increaseBy is 5, the value returned is 11 and the value returned from
/// the next call will be 16.
/// </summary>
/// <param name="table">
/// The name of the table from which to query.
/// </param>
/// <param name="column">
/// The name of the column for which to select the max value.
/// </param>
/// <param name="increaseBy">
/// The amount to increase the sequence by (affecting the next call).
/// </param>
/// <returns>
/// The next value in the specified sequence.
/// </returns>
public virtual long GetNextValues(string table, string column, int increaseBy) {
if (increaseBy < 1)
throw new ArgumentException("The increaesBy parameter must be greater than 0.", "increaseBy");
long sequence;
if (IsBackingTablePresent) {
using (var con = openConnection())
using (var transaction = con.BeginTransaction())
using (var cmd = con.CreateCommand()) {
cmd.AddParameter("TableName", table);
cmd.AddParameter("ColumnName", column);
cmd.CommandText = "UPDATE " + BackingTableName + Environment.NewLine +
"SET SequenceValue = SequenceValue + " + increaseBy + Environment.NewLine +
"WHERE TableName = " + cmd.FormatParameter("TableName") + Environment.NewLine +
"AND ColumnName = " + cmd.FormatParameter("ColumnName");
cmd.Transaction = transaction;
bool loop = true;
while (loop)
try {
cmd.ExecuteNonQuery();
loop = false;
} catch (OleDbException e) {
// Instead of waiting (like every other database in the world)
// MS Access throws this exception. Simulate waiting by
// repeating the query until it works.
if (e.Message == "Could not update; currently locked.")
Thread.Sleep(100);
else
throw;
} // end try-catch
cmd.CommandText = "SELECT SequenceValue " + Environment.NewLine +
"FROM " + BackingTableName + Environment.NewLine +
"WHERE TableName = " + cmd.FormatParameter("TableName") + Environment.NewLine +
"AND ColumnName = " + cmd.FormatParameter("ColumnName");
var result = cmd.ExecuteScalar();
if (result == null || result == DBNull.Value) {
sequence = GetMaxValue(cmd, table, column) + increaseBy;
cmd.CommandText = "INSERT INTO " + BackingTableName + Environment.NewLine +
"(TableName, ColumnName, SequenceValue)" + Environment.NewLine +
"VALUES (" + cmd.FormatParameter("TableName") + ", " +
cmd.FormatParameter("ColumnName") + ", " +
cmd.FormatParameter("SequenceValue") + ")";
cmd.AddParameter("SequenceValue", sequence);
cmd.ExecuteNonQuery();
} else
sequence = DataTool.AsLong(result) ?? -1;
transaction.Commit();
} // end using
} else lock (sequences) {
var key = table + '.' + column;
if (sequences.ContainsKey(key))
sequence = sequences[key];
else
sequence = GetMaxValue(null, table, column);
sequence += increaseBy;
sequences[key] = sequence;
} // end else-lock
return sequence - increaseBy + 1;
} // end method
