C # methods to solve SQLite concurrency exception problems (using read-write locks) _c# Tutorial

This article illustrates the method of C # to solve the problem of sqlite concurrency anomalies. Share to everyone for your reference, specific as follows:

when you use C # to access SQLite, you often encounter problems with multiple threads concurrency that cause sqlite database corruption .

SQLite is a file-level database whose locks are at the file level : Multiple threads can read at the same time, but only one thread is written at a time. Android provides a Sqliteopenhelper class that joins the Java lock mechanism for invocation. However, similar functionality is not available in C #.

The author uses read-write lock (ReaderWriterLock) to achieve the goal of multithread secure access.

Using System;
Using System.Collections.Generic;
Using System.Text;
Using System.Data.SQLite;
Using System.Threading;
Using System.Data;
  Namespace DataAccess {/////////////////public sealed class Sqliteconn {private bool m_disposed; 
  private static dictionary<string, sqliteconnection> Connpool = new dictionary<string, sqliteconnection> ();
  private static dictionary<string, readerwriterlock> rwl = new dictionary<string, readerwriterlock> ();
  private static readonly Sqliteconn instance = new Sqliteconn ();
  private static string default_name = "local"; #region init//Use a single example to resolve problems with initialization and destruction private Sqliteconn () {rwl.
    ADD ("Local", New ReaderWriterLock ()); RWL.
    ADD ("DB1", New ReaderWriterLock ());
    Connpool.add ("Local", Createconn ("\\local.db"));
    Connpool.add ("DB1", Createconn ("\\db1.db"));
  Console.WriteLine ("INIT finished"); private static Sqliteconnection Createconn (String dbname) {sqliteconnection _conn = new SqlitEconnection ();
      try {string pstr = "PWD";
      Sqliteconnectionstringbuilder connstr = new Sqliteconnectionstringbuilder (); ConnStr.
      DataSource = Environment.currentdirectory + dbname; _conn. ConnectionString = ConnStr.
      ToString (); _conn.
      SetPassword (PSTR); _conn.
      Open ();
    return _conn; The catch (Exception exp) {Console.WriteLine ("===conn CREATE err====\r\n{0}", exp.
      ToString ());
    return null;
    } #endregion #region Destory//manual control destroy to ensure data integrity public void Dispose () {Dispose (true); Gc.
  SuppressFinalize (this); } protected void Dispose (bool disposing) {if (!m_disposed) {if (disposing) {//release
        Managed resources Console.WriteLine ("Close Local DB connection ...");
      Closeconn ();
    }//Release unmanaged m_disposed = true;
  } ~sqliteconn () {Dispose (false); public void Closeconn () {foreach (keyvaluepair<string, SqlitecOnnection> item in Connpool) {sqliteconnection _conn = Item.
      Value; String _connname = Item.
      Key; if (_conn!= null && _conn. State!= connectionstate.closed) {try {_conn.
          Close (); _conn.
          Dispose ();
          _conn = null;
        Console.WriteLine ("Connection {0} Closed.", _connname); catch (Exception exp) {Console.WriteLine ("Critical exception: Unable to close connection for local db {0}.")
          ", _connname); Exp.
        ToString ();
        finally {_conn = null;
  #endregion #region getconn public static Sqliteconn getinstance () {return instance;
    Public sqliteconnection getconnection (string name) {sqliteconnection _conn = Connpool[name];
        try {if (_conn!= null) {Console.WriteLine ("Try Get LOCK"); Lock, until the release, the other thread can not get conn Rwl[name].
        AcquireWriterLock (3000);
Console.WriteLine ("LOCK get");        return _conn; } catch (Exception exp) {Console.WriteLine ("===get CONN err====\r\n{0}", exp.
    StackTrace);
  return null;
      public void Releaseconn (string name) {try {//Free Console.WriteLine ("Release LOCK"); Rwl[name].
    ReleaseLock (); The catch (Exception exp) {Console.WriteLine ("===release CONN err====\r\n{0}", exp.
    StackTrace);
  } public sqliteconnection getconnection () {return getconnection (default_name);
  public void Releaseconn () {releaseconn (default_name);

 } #endregion}////////////////////////

The code to invoke is as follows:

Sqliteconnection conn = null;
Try
{
  conn = Sqliteconn.getinstance (). Getconnection ();
  Write your own code here
}
finally
{
  sqliteconn.getinstance (). Releaseconn ();
}

It is noteworthy that each time the connection is requested, it must be released using the Releaseconn method, otherwise the other threads will no longer be connected.

For security purposes, the most restrictive read-write lock restrictions (that is, unreadable when writing) are enabled in the tool class written by the author. If the data is read frequently, readers can also develop a method of getting a read-only connection to improve performance.

Test passed under winxp/win7/win8/win8.1 32/64 bit.

Read more about C # Interested readers can view the site topics: "C # Programming Thread Usage Tips summary", "C # Operation Excel Skills Summary", "C # XML file Operation Tips Summary", "C # Common control usage Tutorial", "WinForm Control Usage Summary", "C # tutorial on data structure and algorithms, summary of C # array manipulation techniques, and an introductory course on C # object-oriented programming

I hope this article will help you with C # programming.