SQLite memory database

SQLite introduction 1. SQLite is an embedded database that implements a large subset of the SQL92 standard. it combines database engines and interfaces in a database, and is famous for storing all data in a single file. I think SQLite functions are between MySQL and PostgreSQL to some extent. however, in terms of performance, SQLite is often 2-3 times faster (or even more

SQLite introduction
I. SQLite is an embedded database that implements a large subset of the SQL92 standard. it combines database engines and interfaces in a database, and is famous for storing all data in a single file. I think SQLite functions are between MySQL and PostgreSQL to some extent. in terms of performance, SQLite is usually 2-3 times faster (or even more ). this benefits the highly-adjusted internal architecture because it removes the communication between the server and the client and the server.

II. the impressive feature is that you can place your entire database system in it. with very efficient memory organization, SQLite only needs to maintain a small size in a small memory, far smaller than any other database system. these features make it a very convenient tool in tasks that require efficient database application.

Advantages of SQLite
I. in addition to speed and efficiency, SQLite has many other advantages that make it an ideal solution for many tasks. because SQLite databases are simple files, it is not necessary for a management team to take the time to construct a complex permission structure to protect users' databases. because the permission is automatically implemented through the file system. this also means that (the size of the database space is only related to the environment and has nothing to do with itself) there are no special rules to understand the user's disk space. users can benefit from creating any number of databases they want and their absolute control over these databases.

II. the fact that a database is a file can be easily moved between servers using SQLite. SQLite also removes the waiting process that requires a large amount of memory and other system resources. this is true even when the database is used in a large amount.

Create a memory database

1. due to business needs, I use the SQLite memory database to create a cache, and all data in the IIS memory database is lost. I wonder if SQLite can be used for distributed memory data management.

2. Add System. Data. SqlClient reference. for the assembly, visit http://sqlite.phxsoftware.com/to download it.

3. create a memory database, Data Source =: memory:; Version = 3; this connection string has been tossing for a long time. of course, you can also use files to store Data here. you only need to modify Data Source =: memory: Data Source = Cache. db is OK! Of course, this table needs to be created in Global.

Code
SQLiteConnection conn = (SQLiteConnection) newSQLiteConnection ("Data Source =: memory:; Version = 3; Cache Size = 3000; New = True; Pooling = False; Max Pool Size = 100; longNames = 0; Timeout = 1000; NoTXN = 0; SyncPragma = NORMAL; StepAPI = 0 ");
Conn. Open ();
IDbCommand dbcmd = conn. CreateCommand ();
// Create a memory table
String SQL = "CREATE TABLE [AvTable] ([ID] INTEGER PRIMARY KEY NOTNULL, [CityPair] VARCHAR (6) NULL, [FlightNo] VARCHAR (50) NULL, [FlightDate] VARCHAR (10) NULL, [CacheTime] VARCHAR (2000) NULL, [AVNote] VARCHAR (2000) NULL )";
Dbcmd. CommandText = SQL;
Dbcmd. Connection = conn;
Dbcmd. ExecuteNonQuery ();
SQLiteConn = conn;
 

Global attribute. after you open the database connection, create a table and assign this attribute to it. later, use this connection for operation data.

Public static SQLiteConnection SQLiteConn {get; set ;}
Use SQL statements to operate the table as follows. SQL statements represent SQL statements.

String SQL = "Update AVTable Set CityPair = '" + av. cityPair + "', CacheTime ='" + DateTime. now. toString () + "', AVNote ='" + av. AVNote + "'Where FlightNo = '" + av. flightNo + "'andflightdate = '" + av. flightDate + "'";
IDbCommand dbcmd = MemoryDatabse. SQLiteConn. CreateCommand ();
Dbcmd. CommandText = SQL;
Dbcmd. Connection = MemoryDatabse. SQLiteConn;
Int count = dbcmd. ExecuteNonQuery ();

The query syntax of SQLite is different from that of SQL SERVER.

Extract data from avcache Table 11-20.

Select * From avcache Limit 9 Offset 10; the preceding statements indicate that data is obtained From the avcache table, 10 rows are skipped, and 9 rows are taken.

Another method is to extract 9 pieces of data from 10.

Select * from avcache limit10, 9

Syntax

SQL = "select * from avcache where" + condition + "order by" + sorting + "limit" + how many records to display + "offset" + how many records to skip;

Example

Select * from avcache limit 15 offset 20 indicates that 15 records are selected by skipping 20 records from the avcache table.

Since I have recently started to learn SQLite, it is inevitable that I write something rough. I hope you can correct it and provide a better way!