Getting Started with SQLite

What is SQLite

SQLite is a very lightweight relational database system that supports most SQL92 standards. SQLite does not need to install the settings before use, no process to start, stop or configure, and most of the other SQL database engine as a separate server process, the program uses some kind of internal process communication (typically TCP/IP), to complete the task of sending requests to the server and receiving query results, SQLite does not use this way of working. When using SQLite, the program that accesses the database reads and writes directly from the database file on disk without intermediate server processes. Using SQLite generally requires only a single DLL, so you can use all of its features.

The main application scenarios for SQLite are databases for mobile phone applications and small desktop software.

Install using SQLite

The official SQLite for Http://www.sqlite.org/download.html, which offers a variety of versions of SQLite, I chose to download the version named Sqlite-shell-win32-x86-3080500.zip. After the download is directly extracted to the disk, you can see the decompression only sqlite3.exe this file.

Next, you need to add sqlite to the PATH environment variable (adding environment variables is to make it easier to use sqlite), right-click My Computer-Properties-Advanced system settings-environment variables, find the path in the system variable, add the extracted folder directory to the back (note the folder directory, For example, my native directory E:\Tools\sqlite). Open cmd, enter Sqlite3, if the following message pops up, it means success.

SQLite Common operations

1. Create a new database file

> Command line go to the folder location where you want to create the db file

> Create a database file with a command: sqlite3 the DB file name to create

> Using commands to view attached database files:. databases

2. Open an established database file

> Command line enter the folder location of the db file you want to open

> Open an established DB file using the command line: Sqlite3 file name (Note: If the file name does not exist, a new db file will be created)

3. View Help Commands

> Command line Input sqlite3, go to sqlite3 command line interface

> input. Help view common commands

Using the SQLite management tool

Although the shell script provides a very powerful function, but it is not easy to use, fortunately, SQLite has a lot of open source and excellent dbms!

Here I will use a software called Sqlitespy, the official website address is http://www.yunqa.de/delphi/doku.php/products/sqlitespy/index, this software is green free installation version, Unzip to run directly on it.

As you can see, the interface layout of Sqlitespy is very similar to SQL Server, it is very convenient to operate, and it is not going to be described in detail here. (One thing to know is that you can create and use a SQLite database simply by using this software, without having to associate with the shell tools mentioned above)

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

SQLite provides DLLs for C # calls, for Http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki, note according to the. NET The framework version downloads the corresponding components. As long as the System.Data.SQLite.dll component is introduced in the project, the database operation can be implemented. Because SQLite.dll realized the interface of ADO, so familiar with the people of ADO SQLite.dll is also very fast. The structure of the Demo database table is:

 
CREATE TABLE hero
(
    hero_id   INT          NOT NULL PRIMARY KEY,
    hero_name NVARCHAR(10) NOT NULL
);

The comparison needs to be noted that the database connection string, SQLite uses the connection string is relatively simple, just write the database file reference path is possible. Demo is a console application, adding and deleting the instance code of the check and change 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,
                                      ‘Shaman’
                                  ) ";
                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 = ‘Thief’
                                   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 ();
        }
    }
}

Getting Started with SQLite