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

Source: Internet
Author: User
Tags sqlite sqlite database

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.
  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.
  } public sqliteconnection getconnection () {return getconnection (default_name);
  public void Releaseconn () {releaseconn (default_name);

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

The code to invoke is as follows:

Sqliteconnection conn = null;
  conn = Sqliteconn.getinstance (). Getconnection ();
  Write your own code here
  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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.