//Update from V0 (empty) to V1
private bool _UpdateV1()
{
if (_Connection == null || _Version >= 1)
return false;
using (SQLiteTransaction transaction = _Connection.BeginTransaction())
{
try
{
foreach (string file in _FilesV1)
{
string filePath = Path.Combine(CSettings.ProgramFolder, file);
if (!_AddImageToCreditsDB(filePath, transaction))
{
transaction.Rollback();
return false;
}
}
using (SQLiteCommand command = new SQLiteCommand(_Connection))
{
command.Transaction = transaction;
command.CommandText = "Update Version SET Value=@Value)";
command.Parameters.Add("@Value", DbType.Int32).Value = 1;
command.ExecuteNonQuery();
}
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
return false;
}
}
return true;
}
public bool TransactionRollBack()
{
if (_SqlConnection.State != System.Data.ConnectionState.Open || _SqlCommand.Connection == null || _SqlTransaction == null)
{
throw new Exception("Connection or transaction not initialised");
}
try
{
_SqlTransaction.Rollback();
}
catch (Exception ex)
{
LogFault("Transaction Rollback failed.", ex, false);
}
_SqlCommand.Transaction = null;
try
{
_SqlTransaction.Dispose();
}
catch
{
}
_SqlTransaction = null;
return(true);
}
public void UpdateAllAthleteResults(int athleteID, List <Result> results)
{
Mono.Data.Sqlite.SqliteConnection conn = null;
Mono.Data.Sqlite.SqliteTransaction trans = null;
try
{
conn = openDbConnection();
trans = conn.BeginTransaction();
var command = conn.CreateCommand();
command.Transaction = trans;
command.CommandText = "DELETE FROM [Result] WHERE AthleteID=" + athleteID;
command.ExecuteNonQuery();
command.Dispose();
foreach (var result in results)
{
result.AthleteID = athleteID;
addResult(conn, trans, result);
}
trans.Commit();
}
catch
{
trans.Rollback();
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
public void DeleteAllData()
{
Mono.Data.Sqlite.SqliteConnection conn = null;
Mono.Data.Sqlite.SqliteTransaction trans = null;
try
{
conn = openDbConnection();
trans = conn.BeginTransaction();
this.executeNonqueryCommand("DELETE FROM [Athlete]", conn, trans);
this.executeNonqueryCommand("DELETE FROM [Result]", conn, trans);
this.executeNonqueryCommand("DELETE FROM [Score]", conn, trans);
trans.Commit();
}
catch (Exception exc)
{
if (trans != null)
{
trans.Rollback();
}
throw exc;
}
finally
{
if (conn != null)
{
conn.Close();
}
}
new DatabaseSetup().CreateEmptyAthleteRecord();
}
public void UpdateAllScores(List <Score> scores)
{
Mono.Data.Sqlite.SqliteConnection conn = null;
Mono.Data.Sqlite.SqliteTransaction trans = null;
try
{
conn = openDbConnection();
trans = conn.BeginTransaction();
var command = conn.CreateCommand();
command.Transaction = trans;
command.CommandText = "DELETE FROM [Score]";
command.ExecuteNonQuery();
command.Dispose();
foreach (var score in scores)
{
this.addScore(conn, trans, score);
}
trans.Commit();
}
catch (Exception exc)
{
trans.Rollback();
throw exc;
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
public void Rollback(Enlistment enlistment)
{
SqliteConnection cnn = _transaction.Connection;
cnn._enlistment = null;
try
{
_transaction.Rollback();
enlistment.Done();
}
finally
{
Cleanup(cnn);
}
}
/// <summary>
/// Executing SQL statements
/// </summary>
/// <param name="DB">Database</param>
/// <param name="SQLs">SQL statement</param>
/// <returns>Returns the number of rows affected</returns>
public static int Command(string DB, params string[] SQLs)
{
int result = 0;
if (File.Exists(DB) && SQLs != null)
{
using (SQLiteConnection con = new SQLiteConnection(@"Data Source=" + DB))
{
con.Open();
using (SQLiteTransaction trans = con.BeginTransaction())
{
try
{
using (SQLiteCommand cmd = new SQLiteCommand(con))
{
foreach (string SQLstr in SQLs)
{
cmd.CommandText = SQLstr;
result += cmd.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
trans.Rollback(); //There was an error, roll back
result = -1;
}
finally
{
try{
trans.Commit();
}catch { }
}
}
con.Close();
}
}
return(result);
}
public void ProcessData(IEnumerable<IEntity> data, ProcessMode mode, SqliteTransaction tran = null)
{
bool inTran = tran != null;
var toRemoveFromCache = new List<Guid>();
try
{
foreach (IEnumerable<IEntity> lst in GetBlock(data.GetEnumerator()))
{
if (!inTran)
tran = ActiveConnection.BeginTransaction();
ProcessAllInternal(lst, mode, tran, mode == ProcessMode.LocalChanges);
if (!inTran)
tran.Commit();
if (mode == ProcessMode.ServerChanges)
{
foreach (ISqliteEntity e in lst)
toRemoveFromCache.Add(e.EntityId);
}
GC.Collect();
}
Cache.Clear(toRemoveFromCache);
}
catch
{
if (tran != null && !inTran)
tran.Rollback();
throw;
}
}
public void SaveSecurity(int myAthleteID, bool userWantsToBeGuest, DateTime timeAthleteCreated)
{
Mono.Data.Sqlite.SqliteConnection conn = null;
Mono.Data.Sqlite.SqliteTransaction trans = null;
try
{
conn = openDbConnection();
int myOldAthleteID = this.getMyAthleteID(conn);
bool myOldAthleteRecordAlreadyExists = int.Parse(this.executeScalarCommand("SELECT COUNT(*) FROM Athlete WHERE AthleteID=" + myOldAthleteID, conn, null).ToString()) == 1;
bool myNewAthleteRecordAlreadyExists = int.Parse(this.executeScalarCommand("SELECT COUNT(*) FROM Athlete WHERE AthleteID=" + myAthleteID, conn, null).ToString()) == 1;
trans = conn.BeginTransaction();
// update Singular row
var command = conn.CreateCommand();
command.Transaction = trans;
command.CommandText = "UPDATE Singular SET MyAthleteID=" + myAthleteID + ", UserWantsToBeGuest=" + (userWantsToBeGuest ? "1" : "0");
//command.CommandText = "UPDATE Singular SET AccessToken=@AccessToken, MyAthleteID=" + myAthleteID + ", UserWantsToBeGuest=" + (userWantsToBeGuest ? "1" : "0");
//command.Parameters.Add(new SqliteParameter() { ParameterName = "@AccessToken", Value = Crypto.Encrypt(accessToken, "$EFK#$RF!#$#SDFwefasdWE@") });
command.ExecuteNonQuery();
command.Dispose();
if (myAthleteID != myOldAthleteID)
{
// create Athlete row
if (myNewAthleteRecordAlreadyExists == false)
{
createAhlete(myAthleteID, timeAthleteCreated, conn, trans);
}
// move results and scores
command = conn.CreateCommand();
command.Transaction = trans;
command.CommandText = "UPDATE Result SET AthleteID=" + myAthleteID + " WHERE AthleteID=" + myOldAthleteID;
command.ExecuteNonQuery();
command.CommandText = "UPDATE Score SET AthleteAID=" + myAthleteID + " WHERE AthleteAID=" + myOldAthleteID;
command.ExecuteNonQuery();
command.CommandText = "UPDATE Score SET AthleteBID=" + myAthleteID + " WHERE AthleteBID=" + myOldAthleteID;
command.ExecuteNonQuery();
if (myOldAthleteRecordAlreadyExists == true)
{
this.executeNonqueryCommand("DELETE FROM [Athlete] WHERE AthleteID=" + myOldAthleteID, conn, trans);
}
}
trans.Commit();
}
catch (Exception exc)
{
if (trans != null)
{
trans.Rollback();
}
throw exc;
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}