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(); } } }
/// <summary> /// You have to hold the CoverMutex when calling this! /// </summary> private void _CommitCovers() { if (_TransactionCover == null) { return; } _TransactionCover.Commit(); _TransactionCover.Dispose(); _TransactionCover = null; }
public void Commit(Enlistment enlistment) { SqliteConnection cnn = _transaction.Connection; cnn._enlistment = null; try { _transaction.IsValid(true); _transaction.Connection._transactionLevel = 1; _transaction.Commit(); 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); }
//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); }
/// <summary> /// Converts a USDX 1.01 or CMD 1.01 database to Vocaluxe format /// </summary> /// <param name="filePath">Database file path</param> /// <returns>True if succeeded</returns> private bool _ConvertFrom101(string filePath) { using (var connection = new SQLiteConnection()) { connection.ConnectionString = "Data Source=" + filePath; try { connection.Open(); } catch (Exception) { return(false); } using (var command = new SQLiteCommand(connection)) { command.CommandText = "PRAGMA table_info(US_Scores);"; bool dateExists = false; using (SQLiteDataReader reader = command.ExecuteReader()) { //Check for column Date while (reader.Read()) { for (int i = 0; i < reader.FieldCount; i++) { if (reader.GetName(i) == "name") { if (reader.GetString(i) == "Date") { dateExists = true; } break; } } } } //This is a USDX 1.01 DB command.CommandText = !dateExists ? "INSERT INTO Scores (SongID, PlayerName, Score, LineNr, Date, Medley, Duet, Difficulty) SELECT SongID, Player, Score, '0', '0', '0', '0', Difficulty from US_Scores" : "INSERT INTO Scores (SongID, PlayerName, Score, LineNr, Date, Medley, Duet, Difficulty) SELECT SongID, Player, Score, '0', Date, '0', '0', Difficulty from US_Scores"; command.ExecuteNonQuery(); command.CommandText = "INSERT INTO Songs SELECT ID, Artist, Title, TimesPlayed from US_Songs"; command.ExecuteNonQuery(); // convert from CP1252 to UTF8 var scores = new List <SData>(); var songs = new List <SData>(); Sqlite3.sqlite3 oldDB; int res = Sqlite3.sqlite3_open(filePath, out oldDB); if (res != Sqlite3.SQLITE_OK) { CLog.Error("Error opening Database: " + filePath + " (" + Sqlite3.sqlite3_errmsg(oldDB) + ")"); } else { var stmt = new Sqlite3.Vdbe(); res = Sqlite3.sqlite3_prepare_v2(oldDB, "SELECT id, Artist, Title FROM Songs", -1, ref stmt, 0); if (res != Sqlite3.SQLITE_OK) { CLog.Error("Error query Database: " + filePath + " (" + Sqlite3.sqlite3_errmsg(oldDB) + ")"); } else { //Sqlite3.sqlite3_step(Stmt); Encoding utf8 = Encoding.UTF8; Encoding cp1252 = Encoding.GetEncoding(1252); while (Sqlite3.sqlite3_step(stmt) == Sqlite3.SQLITE_ROW) { var data = new SData { Id = Sqlite3.sqlite3_column_int(stmt, 0) }; byte[] bytes = Sqlite3.sqlite3_column_rawbytes(stmt, 1); data.Str1 = bytes != null?utf8.GetString(Encoding.Convert(cp1252, utf8, bytes)) : "Someone"; bytes = Sqlite3.sqlite3_column_rawbytes(stmt, 2); data.Str2 = bytes != null?utf8.GetString(Encoding.Convert(cp1252, utf8, bytes)) : "Someone"; songs.Add(data); } Sqlite3.sqlite3_finalize(stmt); } stmt = new Sqlite3.Vdbe(); // ReSharper disable ConvertIfStatementToConditionalTernaryExpression if (!dateExists) { // ReSharper restore ConvertIfStatementToConditionalTernaryExpression res = Sqlite3.sqlite3_prepare_v2(oldDB, "SELECT id, PlayerName FROM Scores", -1, ref stmt, 0); } else { res = Sqlite3.sqlite3_prepare_v2(oldDB, "SELECT id, PlayerName, Date FROM Scores", -1, ref stmt, 0); } if (res != Sqlite3.SQLITE_OK) { CLog.Error("Error query Database: " + filePath + " (" + Sqlite3.sqlite3_errmsg(oldDB) + ")"); } else { //Sqlite3.sqlite3_step(Stmt); Encoding utf8 = Encoding.UTF8; Encoding cp1252 = Encoding.GetEncoding(1252); while (Sqlite3.sqlite3_step(stmt) == Sqlite3.SQLITE_ROW) { var data = new SData { Id = Sqlite3.sqlite3_column_int(stmt, 0) }; byte[] bytes = Sqlite3.sqlite3_column_rawbytes(stmt, 1); data.Str1 = bytes != null?utf8.GetString(Encoding.Convert(cp1252, utf8, bytes)) : "Someone"; if (dateExists) { data.Ticks = _UnixTimeToTicks(Sqlite3.sqlite3_column_int(stmt, 2)); } scores.Add(data); } Sqlite3.sqlite3_finalize(stmt); } } Sqlite3.sqlite3_close(oldDB); SQLiteTransaction transaction = connection.BeginTransaction(); // update Title and Artist strings foreach (SData data in songs) { command.CommandText = "UPDATE Songs SET [Artist] = @artist, [Title] = @title WHERE [ID] = @id"; command.Parameters.Add("@title", DbType.String, 0).Value = data.Str2; command.Parameters.Add("@artist", DbType.String, 0).Value = data.Str1; command.Parameters.Add("@id", DbType.Int32, 0).Value = data.Id; command.ExecuteNonQuery(); } // update player names foreach (SData data in scores) { if (!dateExists) { command.CommandText = "UPDATE Scores SET [PlayerName] = @player WHERE [id] = @id"; } else { command.CommandText = "UPDATE Scores SET [PlayerName] = @player, [Date] = @date WHERE [id] = @id"; command.Parameters.Add("@date", DbType.Int64, 0).Value = data.Ticks; } command.Parameters.Add("@player", DbType.String, 0).Value = data.Str1; command.Parameters.Add("@id", DbType.Int32, 0).Value = data.Id; command.ExecuteNonQuery(); } transaction.Commit(); //Delete old tables after conversion command.CommandText = "DROP TABLE US_Scores;"; command.ExecuteNonQuery(); command.CommandText = "DROP TABLE US_Songs;"; command.ExecuteNonQuery(); } } return(true); }
/// <summary> /// Converts a USDX 1.1 database into the Vocaluxe format /// </summary> /// <param name="filePath">Database file path</param> /// <returns>True if succeeded</returns> private bool _ConvertFrom110(string filePath) { using (var connection = new SQLiteConnection()) { connection.ConnectionString = "Data Source=" + filePath; try { connection.Open(); } catch (Exception) { return(false); } using (var command = new SQLiteCommand(connection)) { //The USDX database has no column for LineNr, Medley and Duet so just fill 0 in there command.CommandText = "INSERT INTO Scores (SongID, PlayerName, Score, LineNr, Date, Medley, Duet, Difficulty) SELECT SongID, Player, Score, '0', Date, '0', '0', Difficulty from US_Scores"; command.ExecuteNonQuery(); command.CommandText = "INSERT INTO Songs SELECT ID, Artist, Title, TimesPlayed from US_Songs"; command.ExecuteNonQuery(); var scores = new List <SData>(); var songs = new List <SData>(); command.CommandText = "SELECT id, PlayerName, Date FROM Scores"; SQLiteDataReader reader = command.ExecuteReader(); if (reader != null && reader.HasRows) { while (reader.Read()) { var data = new SData { Id = reader.GetInt32(0), Str1 = reader.GetString(1) }; Int64 ticks = 0; try { ticks = reader.GetInt64(2); } catch {} data.Ticks = _UnixTimeToTicks((int)ticks); scores.Add(data); } reader.Close(); } command.CommandText = "SELECT id, Artist, Title FROM Songs"; reader = command.ExecuteReader(); if (reader != null && reader.HasRows) { while (reader.Read()) { var data = new SData { Id = reader.GetInt32(0), Str1 = reader.GetString(1), Str2 = reader.GetString(2) }; songs.Add(data); } } if (reader != null) { reader.Dispose(); } SQLiteTransaction transaction = connection.BeginTransaction(); // update Title and Artist strings foreach (SData data in songs) { command.CommandText = "UPDATE Songs SET [Artist] = @artist, [Title] = @title WHERE [ID] = @id"; command.Parameters.Add("@title", DbType.String, 0).Value = data.Str2; command.Parameters.Add("@artist", DbType.String, 0).Value = data.Str1; command.Parameters.Add("@id", DbType.Int32, 0).Value = data.Id; command.ExecuteNonQuery(); } // update player names foreach (SData data in scores) { command.CommandText = "UPDATE Scores SET [PlayerName] = @player, [Date] = @date WHERE [id] = @id"; command.Parameters.Add("@player", DbType.String, 0).Value = data.Str1; command.Parameters.Add("@date", DbType.Int64, 0).Value = data.Ticks; command.Parameters.Add("@id", DbType.Int32, 0).Value = data.Id; command.ExecuteNonQuery(); } transaction.Commit(); //Delete old tables after conversion command.CommandText = "DROP TABLE IF EXISTS us_scores;"; command.ExecuteNonQuery(); command.CommandText = "DROP TABLE IF EXISTS us_songs;"; command.ExecuteNonQuery(); command.CommandText = "DROP TABLE IF EXISTS us_statistics_info;"; command.ExecuteNonQuery(); command.CommandText = "DROP TABLE IF EXISTS us_users_info;"; command.ExecuteNonQuery(); command.CommandText = "DROP TABLE IF EXISTS us_webs;"; command.ExecuteNonQuery(); command.CommandText = "DROP TABLE IF EXISTS us_webs_stats;"; command.ExecuteNonQuery(); //This versioning is not used in Vocaluxe so reset it to 0 command.CommandText = "PRAGMA user_version = 0"; command.ExecuteNonQuery(); } } return(true); }
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(); } } }