Sqlite sorting and sqlite sorting

Sqlite sorting and sqlite sorting

Preface

LZ has been unemployed recently. Sort out the project structure of the latest project-Supply Chain System

Next blog http://www.cnblogs.com/buruainiaaaa/p/6786527.html

The previous article mentioned that the entire system is divided into three sub-systems. This article describes the cache structure.

Sqlite introduction:

SQLite is an in-process database that implements a self-sufficiency, serverless, zero-configuration, and transactional SQL database engine. (Relatively official)

Its official website address https://www.sqlite.org/

LZ intends to talk about Sqlite in his own eyes:

1. Self-sufficiency: sqlite can contain its own tables, attempts, indexes, and transactions.

2. serverless: file-only mode. You do not need to start system services like sqlserver.

3. Zero Configuration: No configuration is required for the file. You only need to configure the path of the db file for ease of use.

4. Transaction: sqlite supports parameterization and transaction rollback.

5. lightweight, cross-platform

However, sqlite does not have any disadvantages. LZ personally believes that the biggest disadvantage is: Concurrent writing.

Sqlite is used for CS-side caching. A full set of business systems will inevitably have multiple subsystems. During sqlite operations, there will certainly be concurrent writes, in this case, write control is required.

Mutex

Here I will not introduce what Mutex is. In short, Mutex can solve the problem of mutual exclusion in the case of multi-process and multi-thread.

private Mutex writerLock = new Mutex(false, "Global\\JiupiSqlLite");

Wait

writerLock.WaitOne(30000)

Release

writerLock.ReleaseMutex();

Use mutex to ensure mutual exclusion of write operations

 

Public class SqliteConnMutex: IDisposable {// <summary> // Mutex lock /// </summary> private Mutex writerLock = new Mutex (false, "Global \ MySqlLite"); // <summary> // mutex lock protection object /// </summary> private SQLiteConnection connection = null; /// <summary> /// get the write operation connection /// </summary> /// <returns> </returns> public SQLiteConnection GetConnection () {try {while (true) {if (writerLock. waitOne (30000) {if (con Nection. state = System. data. connectionState. closed) {connection = new SQLiteConnection (SQLiteHelper. connectionString);} return connection;} Thread. sleep (1000) ;}} catch (Exception ex) {throw ex ;}// the Dispose method public void Dispose () {Dispose (true);} explicitly called by the programmer );} // The Dispose method of protected to ensure that it is not called externally. Protected void Dispose (bool disposing) {if (disposing) {try {writerLock. ReleaseMutex () ;}catch (Exception ex ){}}}}

　　

SQLiteHelper calls sqlite write operations

/// <Summary> /// process lock /// </summary> /// <param name = "SQLString"> </param> /// <returns> </returns> public static int ExecuteMutexSql (string SQLString, SQLiteParameter [] param = null) {SQLiteConnection connection = null; try {using (SqliteConnMutex mutex = new SqliteConnMutex () {connection = mutex. getConnection (); if (connection = null) {throw new Exception ("connection object error");} if (connection = = Null) {throw new Exception ("connection cannot be blank");} using (SQLiteCommand cmd = new SQLiteCommand () {try {if (connection. State! = ConnectionState. open) {connection. open ();} PrepareCommand (cmd, connection, null, SQLString, param); // Thread. sleep (100); int rows = cmd. executeNonQuery (); return rows;} catch (System. data. SQLite. SQLiteException E) {if (connection! = Null & connection. State! = ConnectionState. Closed) {connection. Clone () ;}throw new Exception (E. Message) ;}}} catch (Exception ex) {throw ex ;}}

Encapsulation of sqlite read Operations

/// <Summary> /// execute the query statement, return DataSet /// </summary> /// <param name = "SQLString"> query statement </param> /// <returns> DataSet </returns> public static DataSet query (string SQLString) {using (SQLiteConnection connection = new SQLiteConnection (connectionString) {DataSet ds = new DataSet (); try {connection. open (); SQLiteDataAdapter command = new SQLiteDataAdapter (SQLString, connection); command. fill (ds, "ds");} catch (System. data. SQLite. SQLiteException ex) {throw new Exception (ex. message) ;}return ds ;}}

After testing, you can add data to the same database in multi-thread and multi-process scenarios.

Conclusion:

Attached

Download System. Data. SQLite. dll