C# SQLiteConnection.BackupDatabase方法代码示例

        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);
//.........这里部分代码省略.........

        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();

        }