【转载】+【原创】C#版SQLite的备份和还原

红线以内是转载：http://simon-hardy.com/Blog/Home/Details/backup-and-restore-from-sqlitenet

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Backup from SQLite.Net

Published: 02/09/2013 09:00         Last Modified: 08/03/2014 10:48         Views: 242    

            Without a server process to run maintenance tasks, applications that use SQLite to store data must perform those tasks themselves. Backup and restore is the most commonly needed of these tasks. So I'll cover how I do that here.        

Context

   

Backup

API Support

SQLite.net provides an API method on a SQLiteConnection object to perform the SQLite backup operation.
In order to call this method you need to pass the following parameters:

   
• destination - An open SQLiteConnection for the destination database;   
• destinationName - "main" to backup to the main database file, "temp" to backup to the temporary database file, or the name specified after the AS keyword in an ATTACH statement for an attached database;   
• sourceName - "main" to backup from the main database file, "temp" to backup from the temporary database file, or the name specified after the AS keyword in an ATTACH statement for an attached database;   
• pages - the number of pages on disk to back up with every iteration of the algorithm, -1 will backup the whole database in one iteration;   
• callback - a function that is called between every iteration, returns true to continue, or false to stop the algorithm;   
• retryMilliseconds - number of milliseconds to wait before retrying a failed iteration of the algorithm.

Extending with IObservable<T> and IObserver<T>

The following class wraps the iterative algorithm within an IObservable<SqliteBackupEvent> object. The SqliteBackupEvent class just contains the properties returned to the callback:

   

public class SqliteBackup : IObservable<SqliteBackupEvent>
{
    private readonly List<IObserver<SqliteBackupEvent>> _observers;

    public SqliteBackup()
    {
        _observers = new List<IObserver<SqliteBackupEvent>>();
    }

    public void Execute(string sourceConnectionString, string destinationConnectionString, int pagesToBackupInEachStep)
    {
        try
        {
            using (var srcConnection = new SQLiteConnection(sourceConnectionString))
            using (var destConnection = new SQLiteConnection(destinationConnectionString))
            {
                srcConnection.Open();
                destConnection.Open();

                // Need to use the "main" names as specified at http://www.sqlite.org/c3ref/backup_finish.html#sqlite3backupinit
                srcConnection.BackupDatabase(destConnection, "main", "main", pagesToBackupInEachStep, Callback, 10);

                destConnection.Close();
                srcConnection.Close();
            }
        }
        catch (Exception ex)
        {
            foreach (var observer in _observers)
                observer.OnError(ex);
        }

        foreach (var observer in _observers)
            observer.OnCompleted();
    }

    protected virtual bool Callback(SQLiteConnection srcConnection, string srcName, SQLiteConnection destConnection, string destName,
                                    int pages, int remaining, int pageCount, bool retry)
    {
        var @event = new SqliteBackupEvent(pages, remaining, pageCount, retry);

        foreach (var observer in _observers)
            observer.OnNext(@event);

        return true;
    }

    public IDisposable Subscribe(IObserver<SqliteBackupEvent> observer)
    {
        if (!_observers.Contains(observer))
            _observers.Add(observer);
        return new Unsubscriber(_observers, observer);
    }

    private class Unsubscriber : IDisposable
    {
        private readonly List<IObserver<SqliteBackupEvent>> _observers;
        private readonly IObserver<SqliteBackupEvent> _observer;

        public Unsubscriber(List<IObserver<SqliteBackupEvent>> observers, IObserver<SqliteBackupEvent> observer)
        {
            this._observers = observers;
            this._observer = observer;
        }

        public void Dispose()
        {
            if (_observer != null && _observers.Contains(_observer))
                _observers.Remove(_observer);
        }
    }
}

For completeness the SqliteBackupEvent class should be:

public class SqliteBackupEvent
    {
        public int Pages { get; private set; }
        public int Remaining { get; private set; }
        public int PageCount { get; private set; }
        public bool Retry { get; private set; }

        public SqliteBackupEvent(int pages, int remaining, int pageCount, bool retry)
        {
            Pages = pages;
            Remaining = remaining;
            PageCount = pageCount;
            Retry = retry;
        }
    }

This can be used to update a GUI or some other form of output, such as logging, with the status of the backup operation:

   

public class ConsoleWriterObserver : IObserver<SqliteBackupEvent>
{
    public void OnNext(SqliteBackupEvent value)
    {
        Console.WriteLine("{0} - {1} - {2} - {3}", value.Pages, value.PageCount, value.Remaining, value.Retry);
    }

    public void OnError(Exception error)
    {
        Console.WriteLine(error.Message);
    }

    public void OnCompleted()
    {
        Console.WriteLine("Complete");
    }
}

The use of these classes in your appilcation then becomes something like:

   1:  const string srcConnectionString = @"Data Source="".\data.db"";Version=3;";

   2:  const string destConnectionString = @"Data Source="".\newdata.db"";Version=3;";

   3:   

   4:  var backup = new SqliteBackup();

   5:   

   6:  var unsubscriber = backup.Subscribe(new ConsoleWriterObserver());

   7:   

   8:  backup.Execute(srcConnectionString, destConnectionString, 50);

   9:   

  10:  unsubscriber.Dispose();

  11:   

  12:  Console.ReadLine();

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

一下是根据自己的实际情况写的一个简易版的备份和还原

备份数据库：

        public void BackUpDateBase()
        {
            string destDBFileName = configMng.backUpFileName;
            int index = destDBFileName.IndexOf('.');
            string destDBFileNameProcess = destDBFileName.Insert(index, "_process");
            SQLiteConnection cnDest = new SQLiteConnection(@"Data Source=" + configMng.backUpPath + @"\" + destDBFileNameProcess);
            SQLiteConnection cnSource = new SQLiteConnection(@"Data Source=..\DataBase\" + configMng.backUpFileName);
            try
            {
                cnDest.Open();
                cnSource.Open();

                cnSource.BackupDatabase(cnDest, "main", "main", -1, null, 0);
                cnDest.Close();
                cnSource.Close();

                if (File.Exists(configMng.backUpPath + @"\" + destDBFileNameProcess))
                {
                    destDBFileName = destDBFileName.Insert(index, DateTime.Now.ToString("yyyyMMddHHmmss"));
                    File.Move(configMng.backUpPath + @"\" + destDBFileNameProcess, configMng.backUpPath + @"\" + destDBFileName);
                }
            }
            catch(Exception e)
            {
                LogHelper.WiteLog("SQLiteBackUpService backup faild!");
                if (cnDest.State == ConnectionState.Open)
                {
                    cnDest.Close();
                }
                if (cnSource.State == ConnectionState.Open)
                {
                    cnSource.Close();
                }

            }

        } 

 红色代码部分就是用于数据库的备份的，这时SQLite提供的在线备份，好处是备份的同时不会影响数据库的使用，当然这个函数也可以用于数据库的还原，

这个函数后几个参数可以实现备份时的进度反馈，

例如下面的用法，就能反映进度：

cnSource.BackupDatabase(cnDest, "main", "main", 2, Callback, -1);



        protected bool Callback(SQLiteConnection srcConnection, string srcName, SQLiteConnection destConnection, string destName,
                                       int pages, int remaining, int pageCount, bool retry)
        {
            process = 100 *(pageCount - remaining) / pageCount;
            if (remaining <= pages)
            {
                isRestore = false;
                process = 100;
            }
            return true;
        }

上述代码中BackupDatabase（）函数在备份的过程中就能回调Callback函数，他调用的频率是由第四个参数决定的，当前为2表示每备份2页就调用callback，

在callback函数中，pagecount就是当前备份的数据库的总页数，remaining就是还没备份的，pages就是前面的2（备份的频率）。