} // 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