Sqllite installation and operation in C #

about SQLite

SQLite is a lightweight database for local data storage.

Let's talk about the pros, it occupies a very low resource, in the embedded device requires hundreds of K of memory is enough, as a lightweight database, his processing speed is fast enough, the capacity level of support is T-class, Independent: no additional dependency; open source; support multiple languages;

My use

In the project development, it is necessary to do data synchronization. Because the database real-time data synchronization, the need to record the update time, system logs and so on data; Of course, you can also choose to write INI and XML and other configuration files to resolve, but all such as database readability is not high.

installation

1. Refer to. NET Driver Http://system.data.sqlite.org/index.html/doc/trunk/www/index.wiki

These three files, in a project, can be used to create database query data operations after the reference.

2. Use the VS provided package management tool NuGet for project references.

NuGet Package Management Tools

Search for the corresponding package of SQLite installation, after the download is complete, it is automatically referenced in the project.

Use

Create a database

1 // Create a database 2 sqliteconnection.createfile ("database.sqlite");

manipulating databases

//Create a connection stringSqliteconnection conn =NewSqliteconnection ("Data Source=database.sqlite; version=3;");//This is the database login passwordConn. SetPassword ("1234");//Open DatabaseConn. Open ();stringquery ="CREATE TABLE table1 (ID INTEGER, name VARCHAR)";//Create commandSqlitecommand cmd =NewSqlitecommand (query, conn);//Execute Commandcmd. ExecuteNonQuery ();//Freeing ResourcesConn. Close ();

Inserting data

New Sqliteconnection ("Data source=database.sqlite; version=3; " ); Conn. Open (); string " INSERT INTO table1 (id,name) VALUES (1, ' xiaoming ') "  New  sqlitecommand (query, conn); cmd. ExecuteNonQuery (); Conn. Close (); cmd. Dispose ();

Querying data

using New Sqliteconnection ("Data source=database.sqlite; version=3; " ) {    Conn. Open ();     string " SELECT * FROM table1 " ;     New Sqlitecommand (query, conn);     New sqlitedataadapter (cmd);     New DataTable ();    Da. Fill (DT);}

Visualization Tools

Sqlitestudio Visualization Tool https://sqlitestudio.pl

Connecting to a database

Tabular

Set the primary key, which has been self-increasing.

The primary key self-increment type must be an integer type

Other 1. Sqlite. NET drive Set database read password

. NET driver, provides a separate password and login password

using New Sqliteconnection ("Data source=database.sqlite; version=3; " ) {    Conn. Open ();     // Set Database Password    Conn. ChangePassword ("123456");    Conn. Clone ();}

Log in to a database with a password

using(Sqliteconnection conn =NewSqliteconnection ("Data Source=database.sqlite; version=3;") {Conn. SetPassword ("123456"); Conn.    Open (); stringquery ="SELECT * FROM table1"; Sqlitecommand cmd=NewSqlitecommand (query, conn); Sqlitedataadapter da=Newsqlitedataadapter (CMD); DataTable DT=NewDataTable (); Da.    Fill (DT); Conn. Clone ();}

Password correct query successful

Password Error Query exception

There is a password setting error, the database state is still open after opening the database, but the query after the exception cannot be queried.

After using the dotnet driver to set the password, it appears that the other frame driver cannot be turned on.

Sqllite installation and operation in C #