C # using SQLite database

This is just a case of not installing, only referencing DLLs in your project.
Download the zip package with the static word from the SQLite website, for example, I downloaded sqlite-netfx40-static-binary-win32-2010-1.0.94.0.zip.
After decompression can get a lot of files, which also contains the installation file Install.exe, but can not install, if you want to use SQLite, minimum two dll:system.data.sqlite.dll,sqlite.interop.dll required. If you need to use LINQ or entityframework, you'll need System.Data.SQLite.Linq.dll and System.Data.SQLite.EF6.dll.

Without considering Linq and entityframework, you only need to refer to System.Data.SQLite.dll, and then make sure that you can find SQLite.Interop.dll in the root directory of the program.

Place a SQLite database file at the root of the program (the database file path can actually be arbitrary). The sqliteconnection,sqlitecommand,sqlitedataadapter is provided in the System.Data.SQLite.

private void InitSqlite ()
{
     SQLiteConnection conn = new SQLiteConnection ("Data Source = test.db3");
     conn.Open ();
     var table = conn.GetSchema ("tables");
     // Get the table structure in the database file
     TableDataGrid.ItemsSource = table.DefaultView;

     var cmd = new SQLiteCommand (@ "select * from Demo", conn);
     SQLiteDataAdapter adapter = new SQLiteDataAdapter (cmd);
     DataSet ds = new DataSet ();
     adapter.Fill (ds);
     // Get the column structure of the table, get it by querying all
     ColumnDataGrid.ItemsSource = ds.Tables [0] .DefaultView;
     cmd.Dispose ();
     conn.Close ();
}

Similarly, executing the SQL statement that creates the database is also possible

private void CreateTable()
{
    SQLiteConnection conn = new SQLiteConnection ("Data Source=test.db3");
    conn.Open();
    var sqlStr = @"create table Demo([Id] int IDENTITY (1,1) PRIMARY KEY,[Pinyin] varchar(50) null)" ;
    SQLiteCommand cmd= new SQLiteCommand (sqlStr,conn);
    cmd.ExecuteNonQuery();
    cmd.Dispose();
    conn.Close();
}

SQLite database compression and MDB database, the SQLite database does not reclaim the control by default, after a large amount of data deletion, the volume of the database file will not change, to compress the database file, you can perform a VACUUM command. VACUUM will reorganize the database from the beginning. This will use the database with an empty "free list", and the database file will be minimal.

private void Compact()
{
    SQLiteConnection conn = new SQLiteConnection ( "Data Source=test.db3");
    conn.Open();
    SQLiteCommand cmd = new SQLiteCommand( "VACUUM" , conn);
    cmd.ExecuteNonQuery();
    cmd.Dispose();
    conn.Close();
}

C # using SQLite database