public SqliteCommand(string commandText, Mono.Data.Sqlite.SqliteConnection connection, Mono.Data.Sqlite.SqliteTransaction transaction) { }
public static bool ExportMapDataToDB(string sourceFile, string destFile) { bool ret = true; try { if (!File.Exists(destFile)) { ret = CreateEmptyDB(destFile); } if (ret) { using (SQLiteConnection cn1 = new SQLiteConnection()) { #if !MONO cn1.ConnectionString = string.Format("Data Source=\"{0}\";Page Size=32768", sourceFile); #else cn1.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=True,Page Size=32768", sourceFile); #endif cn1.Open(); if (cn1.State == System.Data.ConnectionState.Open) { using (SQLiteConnection cn2 = new SQLiteConnection()) { #if !MONO cn2.ConnectionString = string.Format("Data Source=\"{0}\";Page Size=32768", destFile); #else cn2.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=True,Page Size=32768", destFile); #endif cn2.Open(); if (cn2.State == System.Data.ConnectionState.Open) { using (SQLiteCommand cmd = new SQLiteCommand(string.Format("ATTACH DATABASE \"{0}\" AS Source", sourceFile), cn2)) { cmd.ExecuteNonQuery(); } using (SQLiteTransaction tr = cn2.BeginTransaction()) { try { List <long> add = new List <long>(); using (SQLiteCommand cmd = new SQLiteCommand("SELECT id, X, Y, Zoom, Type FROM Tiles;", cn1)) { using (SQLiteDataReader rd = cmd.ExecuteReader()) { while (rd.Read()) { long id = rd.GetInt64(0); using (SQLiteCommand cmd2 = new SQLiteCommand(string.Format("SELECT id FROM Tiles WHERE X={0} AND Y={1} AND Zoom={2} AND Type={3};", rd.GetInt32(1), rd.GetInt32(2), rd.GetInt32(3), rd.GetInt32(4)), cn2)) { using (SQLiteDataReader rd2 = cmd2.ExecuteReader()) { if (!rd2.Read()) { add.Add(id); } } } } } } foreach (long id in add) { using (SQLiteCommand cmd = new SQLiteCommand(string.Format("INSERT INTO Tiles(X, Y, Zoom, Type, CacheTime) SELECT X, Y, Zoom, Type, CacheTime FROM Source.Tiles WHERE id={0}; INSERT INTO TilesData(id, Tile) Values((SELECT last_insert_rowid()), (SELECT Tile FROM Source.TilesData WHERE id={0}));", id), cn2)) { cmd.Transaction = tr; cmd.ExecuteNonQuery(); } } add.Clear(); tr.Commit(); } catch (Exception exx) { Debug.WriteLine("ExportMapDataToDB: " + exx.ToString()); tr.Rollback(); ret = false; } } using (SQLiteCommand cmd = new SQLiteCommand("DETACH DATABASE Source;", cn2)) { cmd.ExecuteNonQuery(); } } } } } } } catch (Exception ex) { Debug.WriteLine("ExportMapDataToDB: " + ex.ToString()); ret = false; } return(ret); }
public void Write(ExiftoolData exifToolOldValue, ExiftoolData exifToolNewValue, string warning) { int resultRowsAffected = 1; Mono.Data.Sqlite.SqliteTransaction sqlTransaction = null; do { #region If failed to updated data, delete and retry if (resultRowsAffected == -1) { Logger.Error("Delete MediaExiftoolTagsWarning data due to previous application crash for file: " + exifToolNewValue.FullFilePath); try { dbTools.TransactionRollback(sqlTransaction); } catch { } //Delete all extries due to crash. DeleteFileEntryFromMediaExiftoolTagsWarning(new FileEntry(exifToolNewValue.FileDirectory, exifToolNewValue.FileName, exifToolNewValue.FileDateModified)); } #endregion sqlTransaction = dbTools.TransactionBegin(); #region SELECT Warning FROM MediaExiftoolTagsWarning warning = "(Logged: " + DateTime.Now.ToString() + ")\r\n" + warning; string sqlRead = "SELECT Warning FROM MediaExiftoolTagsWarning WHERE " + "FileDirectory = @FileDirectory AND FileName = @FileName AND FileDateModified = @FileDateModified AND " + "OldRegion = @OldRegion AND OldCommand = @OldCommand AND " + "NewRegion = @NewRegion AND NewCommand = @NewCommand"; bool oldRecordFound = false; using (var commandDatabase = new CommonSqliteCommand(sqlRead, dbTools.ConnectionDatabase, sqlTransaction)) { //commandDatabase.Prepare(); commandDatabase.Parameters.AddWithValue("@FileDirectory", exifToolNewValue.FileDirectory); commandDatabase.Parameters.AddWithValue("@FileName", exifToolNewValue.FileName); commandDatabase.Parameters.AddWithValue("@FileDateModified", dbTools.ConvertFromDateTimeToDBVal(exifToolNewValue.FileDateModified)); commandDatabase.Parameters.AddWithValue("@OldRegion", exifToolOldValue.Region); commandDatabase.Parameters.AddWithValue("@OldCommand", exifToolOldValue.Command); commandDatabase.Parameters.AddWithValue("@NewRegion", exifToolNewValue.Region); commandDatabase.Parameters.AddWithValue("@NewCommand", exifToolNewValue.Command); using (CommonSqliteDataReader reader = commandDatabase.ExecuteReader()) { while (reader.Read()) { warning = warning + "\r\n" + dbTools.ConvertFromDBValString(reader["Warning"]); } } oldRecordFound = true; } #endregion #region DELETE FROM MediaExiftoolTagsWarning if (oldRecordFound) { string sqlDelete = "DELETE FROM MediaExiftoolTagsWarning WHERE " + "FileDirectory = @FileDirectory AND FileName = @FileName AND FileDateModified = @FileDateModified AND " + "OldRegion = @OldRegion AND OldCommand = @OldCommand AND " + "NewRegion = @NewRegion AND NewCommand = @NewCommand"; using (var commandDatabase = new CommonSqliteCommand(sqlDelete, dbTools.ConnectionDatabase, sqlTransaction)) { //commandDatabase.Prepare(); commandDatabase.Parameters.AddWithValue("@FileDirectory", exifToolNewValue.FileDirectory); commandDatabase.Parameters.AddWithValue("@FileName", exifToolNewValue.FileName); commandDatabase.Parameters.AddWithValue("@FileDateModified", dbTools.ConvertFromDateTimeToDBVal(exifToolNewValue.FileDateModified)); commandDatabase.Parameters.AddWithValue("@OldRegion", exifToolOldValue.Region); commandDatabase.Parameters.AddWithValue("@OldCommand", exifToolOldValue.Command); commandDatabase.Parameters.AddWithValue("@NewRegion", exifToolNewValue.Region); commandDatabase.Parameters.AddWithValue("@NewCommand", exifToolNewValue.Command); resultRowsAffected = commandDatabase.ExecuteNonQuery(); } } #endregion #region INSERT INTO MediaExiftoolTagsWarning if (resultRowsAffected != -1) { string sqlCommand = "INSERT INTO MediaExiftoolTagsWarning (FileDirectory, FileName, FileDateModified, OldRegion, OldCommand, OldParameter, NewRegion, NewCommand, NewParameter, Warning) " + "Values (@FileDirectory, @FileName, @FileDateModified, @OldRegion, @OldCommand, @OldParameter, @NewRegion, @NewCommand, @NewParameter, @Warning)"; using (var commandDatabase = new CommonSqliteCommand(sqlCommand, dbTools.ConnectionDatabase, sqlTransaction)) { //commandDatabase.Prepare(); commandDatabase.Parameters.AddWithValue("@FileDirectory", exifToolNewValue.FileDirectory); commandDatabase.Parameters.AddWithValue("@FileName", exifToolNewValue.FileName); commandDatabase.Parameters.AddWithValue("@FileDateModified", dbTools.ConvertFromDateTimeToDBVal(exifToolNewValue.FileDateModified)); commandDatabase.Parameters.AddWithValue("@OldRegion", exifToolOldValue.Region); commandDatabase.Parameters.AddWithValue("@OldCommand", exifToolOldValue.Command); commandDatabase.Parameters.AddWithValue("@OldParameter", exifToolOldValue.Parameter); commandDatabase.Parameters.AddWithValue("@NewRegion", exifToolNewValue.Region); commandDatabase.Parameters.AddWithValue("@NewCommand", exifToolNewValue.Command); commandDatabase.Parameters.AddWithValue("@NewParameter", exifToolNewValue.Parameter); commandDatabase.Parameters.AddWithValue("@Warning", warning); resultRowsAffected = commandDatabase.ExecuteNonQuery(); } } #endregion } while (resultRowsAffected == -1 || !dbTools.TransactionCommit(sqlTransaction)); }