dotConnect for SQLite Documentation
Devart.Data.SQLite Namespace / SQLiteSnapshot Class
Members Example

In This Topic
    SQLiteSnapshot Class
    In This Topic
    Syntax
    'Declaration
     
    Public Class SQLiteSnapshot 
       Implements System.IComparable(Of SQLiteSnapshot) 
    public class SQLiteSnapshot : System.IComparable<SQLiteSnapshot>  
    Example
    public new void SnapshotTest() {
    
            string dbName = "wal_autotest.db";
    
            string walDBConStr = String.Format("DataSource =\"{0}\";JournalMode=\"WAL\";Pooling=false", dbName);
            SQLiteConnection dbConnection = new SQLiteConnection(walDBConStr);
            TryCreateDatabase(dbName, walDBConStr, dbConnection);
    
            dbConnection.Open();
            DropForSnapshot(walDBConStr);
            InsertForSnapshot(walDBConStr);
    
            string schema = "main";
            SQLiteSnapshot snapshot = SQLiteSnapshot.Get(dbConnection); // snapshot with 1 row exists
    
            long cnt1 = GetCountSnapshot(dbConnection); // returns 1
    
            using (var trans = dbConnection.BeginTransaction(SQLiteTransactionMode.Deferred)) {
    
                    var snap1 = InsertForSnapshot(walDBConStr); //insert new row and returns last snapshot
    
                    snap1.Open(schema, trans);
                    long cnt22 = GetCountSnapshot(dbConnection); // returns 2
    
                    var snap2 = InsertForSnapshot(walDBConStr); 
    
                    long cnt222 = GetCountSnapshot(dbConnection); // returns 2
    
                    snap2.Open(trans); // apply last snapshot to the current transaction on default("main") shcema
                    long cnt3 = GetCountSnapshot(dbConnection); // currently transaction can read latest data. returns 3
    
                    int compare = snap1.CompareTo(snap2);
                    var equal = snap1.Equals(snap2);
    
                    trans.Rollback();
            }
    
            var dbConnection2 = new SQLiteConnection(walDBConStr);
            dbConnection2.Open();
    
            InsertForSnapshot(walDBConStr);
    
            SQLiteSnapshot.Recover(dbConnection2); //Make all snapsots in WAL file available for current connection
    
            using (var trans = dbConnection2.BeginTransaction(SQLiteTransactionMode.Deferred)) {
    
                    long cnt_4 = GetCountSnapshot(dbConnection2); // returns 4. latest snapshot by default
    
                    if (cnt_4 != 4)
                            throw new Exception("should be 3 rows");
    
                    snapshot.Open(trans); // apply first snapshot to the current transaction
                    long cnt_1 = GetCountSnapshot(dbConnection2); // returns 1. because snapshot points to the state, when 1 row was inserted
    
                    trans.Rollback();
            }
    
            dbConnection.Close();
            dbConnection2.Close();
    }
    
    private static bool TryCreateDatabase(string dbName, string walDBConStr, SQLiteConnection dbConnection) {
    
    
            bool isInsert = false; 
            if (!File.Exists(dbName)) {
    
                    File.WriteAllText(dbName, "");
                    isInsert = true;
            }
          
            if (isInsert) {
                    dbConnection.Open();
    
                    SQLiteCommand cmd = (SQLiteCommand)dbConnection.CreateCommand();
                    cmd.CommandText = "Create table blob_types (id int, f_blob int)";
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
    
                    dbConnection.Close();
            }
    
            return isInsert;
    }
    
    private long GetCountSnapshot(SQLiteConnection dbConnection) {
            SQLiteCommand cmd = (SQLiteCommand)dbConnection.CreateCommand();
            cmd.CommandText = "select count(id) from blob_types";
            var ob = cmd.ExecuteScalar();
            cmd.Dispose();
    
            return (long)ob;
    }
    
    private void DropForSnapshot(string walDBConStr) {
    
            SQLiteConnection dbConnection = new SQLiteConnection(walDBConStr);
            dbConnection.Open();
            using (var trans = dbConnection.BeginTransaction(IsolationLevel.ReadCommitted)) {
                    //result = trans.GetSnapshot();
    
                    SQLiteCommand cmd = (SQLiteCommand)dbConnection.CreateCommand();
                    cmd.CommandText = "delete from blob_types";
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
    
                    trans.Commit();
            }
    }
    
    private SQLiteSnapshot InsertForSnapshot(string walDBConStr) {
    
            SQLiteSnapshot result = null;
            SQLiteConnection dbConnection = new SQLiteConnection(walDBConStr);
            dbConnection.Open();
            using (var trans = dbConnection.BeginTransaction(IsolationLevel.ReadCommitted)) {
                    //result = trans.GetSnapshot();
    
                    SQLiteCommand cmd = (SQLiteCommand)dbConnection.CreateCommand();
                    cmd.CommandText = "insert into blob_types (id, f_blob) values (1, :blob)";
                    cmd.Parameters.Add(CreateParameter("blob", 1));
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
    
                    trans.Commit();
            }
    
            using (var trans = dbConnection.BeginTransaction(IsolationLevel.ReadCommitted)) {
                    result = SQLiteSnapshot.Get(trans);
            }
    
            dbConnection.Close();
    
            return result;
    }
    Inheritance Hierarchy

    System.Object
       Devart.Data.SQLite.SQLiteSnapshot

    See Also