public static void Backup(string sourceConnectionString, string appDataDirectory) { var now = DateTime.UtcNow; var backupDirectory = Path.Combine(appDataDirectory, "Backup"); if (!Directory.Exists(backupDirectory)) Directory.CreateDirectory(backupDirectory); if (HasBackupForToday(now, backupDirectory)) return; var backupFile = "{0}.sqlite".F(now.ToString("yyyy-MM-dd_hh-mm-ss")); backupFile = Path.Combine(backupDirectory, backupFile); Logger.I("Database backup to {0}", backupFile); string backupConnectionString = "data source={0}".F(backupFile); using (var backupConnection = new SQLiteConnection(backupConnectionString)) using (var sourceConnection = new SQLiteConnection(sourceConnectionString)) { backupConnection.Open(); sourceConnection.Open(); sourceConnection.BackupDatabase(backupConnection, "main", "main", -1, null, 0); } DeleteOldBackups(backupDirectory, 5); }
/// <summary> /// Builds database and run SQL. /// </summary> private static void BuildAndRunSQL() { if (_dataSet != null && _dataSet.Tables.Count > 0 && _dataSet.Tables[0].Columns.Count > 0) { var columns = _dataSet.Tables[0].Columns.Count; var rows = _dataSet.Tables[0].Rows.Count; var columnNames = new List<string>(columns); var tableName = SanitizeString(_options.TableName); for (var i = 0; i < columns; i++) { var oldName = _dataSet.Tables[0].Columns[i].ColumnName; var newName = SanitizeString(oldName); if (_options.Verbose && oldName != newName) { WriteInfo(string.Format("Column {0} renamed to {1}", oldName, newName)); } columnNames.Add(newName); } using (var connection = new SQLiteConnection("data source=" + _options.SaveTo)) { connection.Open(); using (var cmdCreateTable = new SQLiteCommand(connection)) { cmdCreateTable.CommandText = "CREATE TABLE IF NOT EXISTS " + SanitizeString(_options.TableName) + " (" + string.Join(" TEXT,", columnNames.ToArray()) + " TEXT);"; cmdCreateTable.ExecuteNonQuery(); } using (var cmdInsertValues = new SQLiteCommand(connection)) { var transaction = connection.BeginTransaction(); cmdInsertValues.Transaction = transaction; var sbQuery = new StringBuilder(); for (var i = 1; i < rows; i++) { sbQuery.AppendLine("INSERT INTO " + tableName + " VALUES ("); for (var j = 0; j < columns; j++) { sbQuery.Append("'" + _dataSet.Tables[0].Rows[i].ItemArray[j].ToString().SafeReplace("'", "''") + "'"); if (j < columns - 1) { sbQuery.Append(","); } } sbQuery.Append(");"); } cmdInsertValues.CommandText = sbQuery.ToString(); cmdInsertValues.ExecuteNonQuery(); transaction.Commit(); } if (_options.Verbose) { WriteInfo(string.Format("Data loaded in {0} milliseconds", _stopwatch.ElapsedMilliseconds)); } if (!string.IsNullOrWhiteSpace(_options.SQL)) { _stopwatch.Restart(); using (var command = new SQLiteCommand(connection)) { command.CommandText = _options.SQL; Console.WriteLine(command.ExecuteScalar()); } if (_options.Verbose) { WriteInfo(string.Format("Queries run in {0} milliseconds", _stopwatch.ElapsedMilliseconds)); } } if (_options.Console) { var appPath = Path.GetDirectoryName(Process.GetCurrentProcess().MainModule.FileName); var sqliteExecutable = appPath + "\\sqlite3.exe"; if (File.Exists(sqliteExecutable)) { string dbPath; if (_options.SaveTo == ":memory:") { var tempPath = appPath + "\\temp.db"; if (File.Exists(tempPath)) { File.Delete(tempPath); } var tempConnection = new SQLiteConnection("data source=" + tempPath); tempConnection.Open(); connection.BackupDatabase(tempConnection, "main", "main", -1, null, 0); dbPath = tempPath; } else { dbPath = _options.SaveTo; } var sqliteProcess = new Process { StartInfo = { FileName = sqliteExecutable, Arguments = dbPath } }; sqliteProcess.StartInfo.UseShellExecute = false; sqliteProcess.StartInfo.RedirectStandardOutput = true; sqliteProcess.OutputDataReceived += (sender, args) => Console.WriteLine(args.Data); sqliteProcess.Start(); sqliteProcess.BeginOutputReadLine(); sqliteProcess.WaitForExit(); } else { ExitFailure("An error occured launching sqlite3 shell"); } } } } else { ExitFailure("Source File is empty"); } }
private void Backup() { try { using (var src = new SQLiteConnection(SrcZip)) using (var dst = new SQLiteConnection(DstZip)) using (DisposeWatch.Start(e => Console.WriteLine($"Backup Completed in {e.TotalMilliseconds} ms"))) { src.Open(); dst.Open(); Console.WriteLine("Start Copy"); src.BackupDatabase(dst, "main", "main", -1, null, 0); Console.WriteLine("End Copy"); } } catch (Exception ex) { Console.WriteLine(); Console.WriteLine(); Console.WriteLine(); Console.WriteLine("Couldn't copy"); Console.WriteLine(ex.Message); } }
private void SaveTo(SQLiteConnection source, SQLiteConnection destination) { bool saving = source.DataSource == ""; Stopwatch sw = new Stopwatch(); sw.Start(); source.BackupDatabase(destination, "main", "main", 1, this.SQLiteBackupCallback, 10); sw.Stop(); Logger.Log("Database " + ((saving) ? "backed up" : "loaded") + " in " + sw.Elapsed); return; /*SQLiteCommand cmdSource = new SQLiteCommand(source); cmdSource.CommandText = "Select * FROM sqlite_master WHERE type=='table'"; SQLiteDataReader masterReader = cmdSource.ExecuteReader(); while (masterReader.Read()) { //sqlite_sequence, NameValueCollection nvc = masterReader.GetValues(); if (nvc["name"] != "sqlite_sequence") { Stopwatch s = new Stopwatch(); s.Start(); SQLiteCommand cmdDest = new SQLiteCommand(destination); cmdDest.CommandText = "DROP TABLE IF EXISTS " + nvc["name"]; cmdDest.ExecuteNonQuery(); cmdDest.CommandText = nvc["sql"]; cmdDest.ExecuteNonQuery(); SQLiteCommand cmdSrc = new SQLiteCommand(source); cmdSrc.CommandText = "SELECT * FROM " + nvc["name"] + " ORDER BY _ROWID_"; SQLiteDataReader dataReader = cmdSrc.ExecuteReader(); while (dataReader.Read()) { NameValueCollection nvcRow = dataReader.GetValues(); string insert = "INSERT INTO " + nvc["name"] + " VALUES ("; bool valid = false; for (int i = 0; i < nvcRow.Keys.Count; i++) { insert += "\"" + nvcRow[i] + "\""; if (i + 1 != nvcRow.Keys.Count) insert += ", "; if (nvcRow[i] != "") valid = true; } if (valid) { cmdDest.CommandText = insert + ")"; cmdDest.ExecuteNonQuery(); } } s.Stop(); Logger.Log("Table " + nvc["name"] + ((saving)?" saved (":" loaded (") + s.Elapsed + ")"); } }*/ }
// ------------------------------------------------------------------- // ----------------------- DATABASE FUNCTIONS ------------------------ // ------------------------------------------------------------------- private void AllTablesToMemory() { // first get the FILE DB to memory using (var source = new SQLiteConnection(_connectionpath)) using (var destination = new SQLiteConnection("Data Source=:memory:")) { source.Open(); destination.Open(); source.BackupDatabase(destination, "main", "main", -1, null, 0); // get all the Table Names string query = "SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY 1"; DataTable tablenames = readTable(query, destination); List<string> names = new List<string>(); foreach (DataRow row in tablenames.Rows) { names.Add(row.ItemArray[0].ToString()); } // get all the Table Data foreach (var name in names){ DataTable tmpdata = readTable("SELECT * FROM " + name, destination); _data.Add(name, tmpdata); } } }
//CreateParams backup of db internal static void BackupDB() { originalDatabase = new SQLiteConnection(SQLiteConnectionString); backupDatabase = new SQLiteConnection(backupDB); originalDatabase.Open(); backupDatabase.Open(); originalDatabase.BackupDatabase(backupDatabase, "main", "main", -1, null, -1); originalDatabase.Close(); backupDatabase.Close(); }
/// <summary> /// Create a backup of database /// </summary> /// <param name="connection">Connection to database which we want to backup</param> /// <param name="backupName">Name of the new database</param> /// <returns>true if successful; false otherwise</returns> private bool BackupDatabase(SQLiteConnection connection, string backupName) { try { string dataSource = Path.Combine( Path.GetDirectoryName(LibraryConfiguration.Instance.StoragePath) ?? String.Empty, backupName); var connectionStringBuilder = new SQLiteConnectionStringBuilder() { DataSource = dataSource, Version = 3, //Set page size to NTFS cluster size = 4096 bytes PageSize = 4096, CacheSize = 10000, JournalMode = SQLiteJournalModeEnum.Wal, Pooling = true, ForeignKeys = true, LegacyFormat = false, FailIfMissing = false }; using (var backupConnection = new SQLiteConnection(connectionStringBuilder.ToString())) { backupConnection.Open(); connection.BackupDatabase(backupConnection, "main", "main", -1, null, -1); } return true; } catch (Exception ex) { _log.Error("Unable to backup database.", ex); return false; } }
/// <summary>备份数据库 /// </summary> /// <param name="isFileToMemory">是否是文件数据库备份到内存数据库; /// isFileToMemory为true指的是从文件数据库导入到当前内存数据库; /// isFileToMemory为false指的是从当前内存数据库导出到文件数据库。 /// </param> private void BackupDatabase(bool isFileToMemory) { using (SQLiteConnection dbfileConnection = GetCon(false)) { memoryConn = GetCon(true); //如果连接是关闭状态就打开 if (dbfileConnection.State == ConnectionState.Closed) { dbfileConnection.Open(); } if (memoryConn.State == ConnectionState.Closed) { memoryConn.Open(); } if (isFileToMemory) { dbfileConnection.BackupDatabase(memoryConn, "main", "main", -1, null, 0); //memoryConn = dbmemConnection; useInMemory = true; } else { memoryConn.BackupDatabase(dbfileConnection, "main", "main", -1, null, 0); } } }
public override void Initialize(String conString) { if (InMemory) { try { SQLiteConnection sourceConnection = new SQLiteConnection(conString); SQLiteConnection destinationConnection = new SQLiteConnection("Data Source=:memory:"); sourceConnection.Open(); destinationConnection.Open(); sourceConnection.BackupDatabase(destinationConnection, "main", "main", -1, Callback, 0); sourceConnection.Close(); destinationConnection.Close(); Connection = destinationConnection; } catch (Exception e) { Console.WriteLine(e.StackTrace); } } else { Connection = new SQLiteConnection(conString); } }
async static void BuildDatabase(string fileName, IEnumerable<Entry> entries) { try { ResourceExtractor.ExtractResourceToFile("awagame.sqlite.dll", "SQLite.Interop.dll"); } catch { Console.WriteLine("[ERROR] Can not write SQLite library."); } try { File.Delete(fileName); } catch (IOException) { } finally { SQLiteConnection.CreateFile(fileName); } SQLiteConnection database = new SQLiteConnection("Data Source=:memory:;Version=3;"); //use a memory database for speed purposes SQLiteConnection disk = new SQLiteConnection("Data Source=" + fileName + ";Version=3;"); database.Open(); using (var sqlCommand = new SQLiteCommand(@"CREATE TABLE IF NOT EXISTS roms( gamename TEXT, romname TEXT, size TEXT, crc TEXT, md5 TEXT, sha1 TEXT PRIMARY KEY, romID TEXT, datName TEXT, datSource TEXT, datDate TEXT )", database)) { sqlCommand.ExecuteNonQuery(); } foreach (Entry gameEntry in entries) { using (var sqlCommand = new SQLiteCommand(@"INSERT OR IGNORE INTO roms VALUES( @gamename, @romname, @size, @crc, @md5, @sha1, @romID, @datName, @datSource, @datDate)", database)) { sqlCommand.Parameters.AddWithValue("@gamename", gameEntry.GameName); sqlCommand.Parameters.AddWithValue("@romname", gameEntry.RomFileName); sqlCommand.Parameters.AddWithValue("@size", gameEntry.RomSize); sqlCommand.Parameters.AddWithValue("@crc", gameEntry.HashCRC32); sqlCommand.Parameters.AddWithValue("@md5", gameEntry.HashMD5); sqlCommand.Parameters.AddWithValue("@sha1", gameEntry.HashSHA1); sqlCommand.Parameters.AddWithValue("@romID", gameEntry.OpenVGDB_RomID); sqlCommand.Parameters.AddWithValue("@datName", gameEntry.DatName); sqlCommand.Parameters.AddWithValue("@datSource", gameEntry.DatSource); sqlCommand.Parameters.AddWithValue("@datDate", gameEntry.DatDate); await sqlCommand.ExecuteNonQueryAsync(); if (Program.Verbose) { await Console.Out.WriteLineAsync(String.Format("[INFO] Added ROM record {0} with game record {1} (SHA {2})", gameEntry.RomFileName, gameEntry.GameName, gameEntry.HashSHA1)); } } } disk.Open(); Console.WriteLine("[INFO] Saving Database to " + fileName); try { database.BackupDatabase(disk, "main", "main", -1, null, 0); } catch { Console.WriteLine("[ERROR] Could not save SQLite file (File in use?)."); } Console.WriteLine("Saved to " + fileName); disk.Close(); database.Close(); }