C # access the SQLite database instance

C # access the SQLite database instance

SQLite is a lightweight relational database system that supports most SQL92 standards. SQLite does not need to be installed and configured before use, and does not need to start, stop, or configure processes. Most other SQL database engines are used as separate server processes, the program uses an internal process for Communication (typically TCP/IP) to send requests to the server and receive query results. SQLite does not use this method. When SQLite is used, the Database Access Program reads and writes directly from the database file on the disk without intermediate server processes. To use SQLite, you only need to bring a dll to use all of its functions.

Major application scenarios of SQLite include databases for mobile apps and databases for small desktop software.

Some useful SQLite commands

Display table structure:

Sqlite>. schema [table]

Obtain all tables and views:

Sqlite>. tables

Obtain the index list of the specified table:

Sqlite>. indices [table]

Export the database to an SQL file:

Sqlite>. output [filename]

Sqlite>. dump

Sqlite>. output stdout

Import a database from an SQL file:

Sqlite>. read [filename]

Format output data to CSV format:

Sqlite>. output plugin filename.csv]

Sqlite>. separator,

Sqlite> select * from test;

Sqlite>. output stdout

Import data from a CSV file to a table:

Sqlite> create table newtable (id integer primary key, value text );

Sqlite>. import into filename.csv] newtable

Back up the database:

/* Usage: sqlite3 [database]. dump> [filename] */

Sqlite3 mytable. db. dump> backup. SQL

Restore database:

/* Usage: sqlite3 [database] <[filename] */

Sqlite3 mytable. db <backup. SQL

Install and use SQLite

Sqlite is the official release version. After the download, it is directly decompressed to the volume. You can see that only the sqlite3.exe file is available after the download.

Next, add sqlite to the path environment variable (to make it easier to use sqlite), right-click my computer, choose Properties> advanced system Settings> environment variable, find the Path in the system variable and add the decompressed folder directory to the end (note the folder directory, for example, my local directory E: \ Tools \ sqlite ). Open cmd and enter sqlite3. If the following message is displayed, it indicates the operation is successful.

Common sqlite operations

1. Create a database file

> Run the following command to enter the folder where the db file is to be created:

> Run the following command to create a database file: name of the database file to be created by sqlite3:

> Use the command to view the attached database file:. databases

2. Open the created database file

> Run the command to enter the folder location of the db file to be opened.

> Use the command line to open the created db file: sqlite3 file name (Note: if the file name does not exist, a new db file will be created)

3. View help commands

> Enter sqlite3 in the command line and go to the sqlite3 command line interface.

> Enter. help to view Common commands.

Use sqlite management tools

Although shell scripts provide powerful functions, they are not easy to use. Fortunately, sqlite has many open-source and excellent DBMS!

Here I will use a software called SQLiteSPY. The official website address is.

As you can see, the interface layout of SQLiteSpy is very similar to that of SQLServer, which is easy to operate. I will not go on to detail it here. (One thing you need to know is that you can create and use sqlite databases simply by using this software without having to associate it with the shell tool mentioned above)

C # Use System. Data. SQLite. dll to access the database

SQLite provides a dll for C # calls. It downloads the corresponding components for http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki, .net framework. In the project, you only need to introduce the System. Data. SQLite. dll component to implement database operations. Since SQLite. dll implements the ADO. NET interface, it is very fast for people familiar with ADO. NET to get started with SQLite. dll. The DEMO database table structure is:

Create table hero

(

Hero_id int not null primary key,

Hero_name NVARCHAR (10) not null );

Note that it is a database connection string. The connection string used by SQLite is relatively simple. You only need to write the Reference Path of the database file. DEMO is a console application. The code for adding, deleting, querying, and modifying instances is as follows:

Using System; using System. collections. generic; using System. linq; using System. text; using System. threading. tasks; using System. data. common; using System. data. SQLite; namespace ConsoleApp

{Class Program

{Static readonly string DB_PATH = "Data Source = E:/database/sqlite/arena. db"; static void Select ()

{Using (SQLiteConnection con = new SQLiteConnection (DB_PATH ))

{

Con. Open (); string sqlStr = @ "SELECT *

FROM hero "; using (SQLiteCommand cmd = new SQLiteCommand (sqlStr, con ))

{Using (SQLiteDataReader dr = cmd. ExecuteReader ())

{While (dr. Read ())

{

Console. WriteLine (dr ["hero_id"]. ToString () + dr ["hero_name"]);

}

}

}

}

} Static void Insert ()

{Using (SQLiteConnection con = new SQLiteConnection (DB_PATH ))

{

Con. Open (); string sqlStr = @ "insert into hero

VALUES

(

1,

'Shares'

) "; Using (SQLiteCommand cmd = new SQLiteCommand (sqlStr, con ))

{

Cmd. ExecuteNonQuery ();

}

}

} Static void Update ()

{Using (SQLiteConnection con = new SQLiteConnection (DB_PATH ))

{

Con. Open (); string sqlStr = @ "UPDATE hero

SET hero_name = 'thieves'

WHERE hero_id = 1 "; using (SQLiteCommand cmd = new SQLiteCommand (sqlStr, con ))

{

Cmd. ExecuteNonQuery ();

}

}

} Static void Delete ()

{Using (SQLiteConnection con = new SQLiteConnection (DB_PATH ))

{

Con. Open (); string sqlStr = @ "delete from hero"; using (SQLiteCommand cmd = new SQLiteCommand (sqlStr, con ))

{

Cmd. ExecuteNonQuery ();

}

}

} Static void Main (string [] args)

{

Insert ();

Select ();

Update ();

Select ();

Delete ();

}

}

}