Install and operate SQLite in C,

Install and operate SQLite in C,
SQLite Introduction

SQLite is a lightweight database for local data storage.

Let's talk about the advantages. It occupies very low resources and requires several hundred KB of memory in embedded devices. As a lightweight database, it can process fast enough; supported capacity levels are T-level; independent: no additional dependencies; open source; support for multiple languages;

My purpose

Data Synchronization is required during project development. Because the real-time data synchronization of databases requires recording the update time, system logs, and other data. Of course, you can also choose to write configuration files such as ini and xml to solve the problem, however, the database is not readable.

Install

1. Reference. NET driver http://system.data.sqlite.org/index.html/doc/trunk/www/index.wiki

 

These three files can be referenced in the project to create a database to query data.

 

2. Use the package management tool Nuget provided by vs for project reference.

Nuget package management tool

Search for the SQLite package to install. After the package is downloaded, it is automatically referenced in the project.

Use

Create a database

 

1 // create a Database 2 SQLiteConnection. CreateFile ("Database. sqlite ");

 

Database Operations

// Create the connection string SQLiteConnection conn = new SQLiteConnection ("Data Source = Database. sqlite; Version = 3; "); // This is the database logon password conn. setPassword ("1234"); // open the database conn. open (); string query = "create table table1 (id INTEGER, name VARCHAR)"; // create Command SQLiteCommand cmd = new SQLiteCommand (query, conn ); // Execute Command cmd. executeNonQuery (); // release resource conn. close ();

Insert data

SQLiteConnection conn = new SQLiteConnection ("Data Source = Database. sqlite; Version = 3; "); conn. open (); string query = "insert into table1 (id, name) values (1, 'xiaoming ')"; SQLiteCommand cmd = new SQLiteCommand (query, conn); cmd. executeNonQuery (); conn. close (); cmd. dispose ();

Query data

using (SQLiteConnection conn = new SQLiteConnection("Data Source=Database.sqlite;Version=3;")){    conn.Open();    string query = "select * from table1";    SQLiteCommand cmd = new SQLiteCommand(query, conn);    SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);    DataTable dt = new DataTable();    da.Fill(dt);}

 

Visualization tools

SQLiteStudio visual tools https://sqlitestudio.pl

 

Connect to database

Query table

Set the primary key, which has been auto-incrementing.

The primary key auto-increment type must be INTEGER.

 

Other 1. SQLite. NET driver set database Read Password

In the. net driver, a separate password and logon password are provided.

Using (SQLiteConnection conn = new SQLiteConnection ("Data Source = Database. sqlite; Version = 3; ") {conn. open (); // set the Database Password conn. changePassword ("123456"); conn. clone ();}

Log on to the database with a password

using (SQLiteConnection conn = new SQLiteConnection("Data Source=Database.sqlite;Version=3;")){    conn.SetPassword("123456");    conn.Open();    string query = "select * from table1";    SQLiteCommand cmd = new SQLiteCommand(query, conn);    SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);    DataTable dt = new DataTable();    da.Fill(dt);    conn.Clone();}

The password is queried correctly.

An error occurred while querying the password.

Another problem is that the password is set incorrectly. After the database is opened, the database status remains open, but an exception occurs after the query and the database cannot be queried.

After you use the dotnet driver to set the password, it seems that the driver cannot be enabled using other frameworks.

FQA

1. Frequent Insert of large amounts of data is particularly slow. What should I do?

　　The solution is to use transactions to Insert data.

The explanation given by SQLite is that the normal execution of Insert occupies one IO for each execution, and the use of transaction execution only occupies one IO until the end of Insert;

Execute transaction Insert Code

private bool QueryTran(List<string> queryList){    SQLiteConnection conn = new SQLiteConnection("Data Source=DataBase;Version=3;");    SQLiteCommand cmd = conn.CreateCommand();    conn.Open();    SQLiteTransaction tran = conn.BeginTransaction();    bool check = false;    try    {        foreach (string item in queryList)        {            cmd.CommandText = item;            cmd.ExecuteNonQuery();        }        tran.Commit();        check = true;    }    catch (Exception ex)    {        tran.Rollback();        check = false;        throw ex;    }    finally    {        conn.Close();    }    return check;}