Learning SQLite database with helpful reference links

Introduction 

SQLite is a small, fast, embedded database. This database engine and interface are combined into a single library and can be stored data in a single file. There are set of features with using SQLite database.

• SQLite has a small memory footprint and only a single library is required to access databases, making it ideal for embedded database applications.
• SQLite has been ported to many platforms and runs even on Windows CE and Palm OS.
• SQLite is ACID-compliant, meeting all four criteria - Atomicity, Consistency, Isolation, and Durability.
• SQLite implements a large subset of the ANSI-92 SQL standard, including views, sub-queries and triggers.
• No problem of extra database drivers, ODBC configuration required. Just include the library and the data file with your application.
• SQLite has native language APIs for C/C++, PHP, Perl, Python, Tcl etc. Native API for C# is still not present.

Further more details: http://www.codeproject.com/Articles/22165/Using-SQLite-in-your-C-Application

Basic requirements working with SQLite in C#

Adding a database to your application can be an easy way to store data and settings between sessions for your program, but it is not always feasible to use a server based DBMS to store your database. SQLite is a small, fast, and reliable database which can be used without the end user having to install anything extra (achieved by referencing a single .dll in your project). There are a few things we as developers must do to get started with SQLite:

• Install the .NET provider for SQLite from Sourceforge.net
• Add a reference to System.Data.SQLite to your project (and mark the .dll to be copied locally to your project)
• Optionally Download a SQLite GUI Client and use it to design your DB (Feel free to code it by hand if that is your preference)

Further more details: http://www.dreamincode.net/forums/topic/157830-using-sqlite-with-%23/

Vacuum concept in SQLite 

Vacuum concept of SQLite is getting free disk space when you delete some rows or data from your SQLite databse. The Vacuumm command rebuilds the entire database. There are several reasons an application might do this:

• Unless SQLite  is running in "auto_vacuum=FULL" mode, when a large amount of data is deleted from the database file it leaves behind empty space, or "free" database pages. This means the database file might be larger than strictly necessary. Running VACUUM to rebuild the db reclaims this space and reduces the size of the database file.
• Frequent inserts, updates, and deletes can cause the db file to become fragmented where data for a single table or index is scattered around the database file. Running VACUUM ensures that each table and index is largely stored contiguously within the db file. In some cases, VACUUM may also reduce the number of partially filled pages in the db, reducing the size of the db file further.  
• Normally, the db page_size and whether or not the db supports auto_vacuum must be configured before the db file is actually created.

Further more details: https://blogs.gnome.org/jnelson/2015/01/06/sqlite-vacuum-and-auto_vacuum/