asp.net under SQLite (Lightweight best database) principle Analysis and Development application _ Basic Application

Overview

SQLite Introduction

Since the advent of commercial applications decades ago, databases have become a major component of software applications. are very critical to the database management system, they also become very large and occupy a considerable amount of system resources, increasing the complexity of management. With the gradual modularization of software applications, a new type of database is more adaptable than a large and complex traditional database management system. The embedded database runs directly in the application process, providing a 0 configuration (zero-configuration) run mode and a very low resource footprint.
SQLite is an open source embedded relational database, which was made in 2000 by D. Richard Hipp Released, its cost of reducing application management data, SQLite portability, easy to use, very small, efficient and reliable.
SQLite are embedded in applications that use it, and they share the same process space rather than a single process. Externally, it's not like an RDBMS, but inside the process, it's a complete, self-contained database engine.

One of the great benefits of embedded databases is that you don't need a network configuration or management within your program. Because the client and server are running in the same process space. SQLite database permissions are only dependent on the file system and do not have the concept of a user account. SQLite has database-level locking and no network server. It requires a small amount of memory and other overhead, which is suitable for embedded devices. All you have to do is compile it correctly into your program.

Architecture (architecture)

The SQLite uses a modular design consisting of three subsystems, including 8 separate modules.

The

interface (Interface)
interface consists of the SQLite C API, that is, whether it is a program, a scripting language, or a library file, which ultimately interacts with the SQLite (we usually use more odbc/ JDBC will eventually be converted to the corresponding C API call.
Compiler (Compiler)
in the compiler, the word breaker (tokenizer) and the parser (Parser) perform a syntax check on SQL and then convert it to a layered, hierarchical data structure that is more easily processed---syntax tree , and then passes the syntax tree to the code generator (generator) for processing. The code generator generates an assembly code for SQLite based on it, which is finally executed by the virtual machine (dummy Machine). The most central part of the
virtual Machine
architecture is the virtual machine, or the virtual database engine engine,vdbe. Similar to Java virtual machines, it interprets the execution byte code. The VDBE byte code consists of 128 opcode (opcodes), which are mainly concentrated in database operations. Each of its instructions is used to perform specific database operations (such as opening a cursor on a table) or to prepare the stack space for these operations (such as pressing parameters). In summary, all of these directives are designed to meet the requirements of the SQL command (for the VM, which is described in detail later). The
back-end (back-end)
driven by the B-tree (b-tree), the page cache (pages Cache,pager), and the operating system interface (that is, system calls). B-tree and page cache manage the data together. The main function of B-tree is indexing, which maintains the complex relationships between pages to quickly find the data needed. The main function of pager is to pass the page between B-tree and disk through the OS interface.

SQLite features (SQLite ' Features and philosophy)
0 configuration (Zero Configuration)
Portable (portability):
It is running on Windows,linux,bsd,mac OS X and some commercial UNIX systems, such as the Sun's SOLARIS,IBM AIX, and it can also work under many embedded operating systems, such as Qnx,vxworks,palm OS, Symbin and Windows CE.
Compactness:
SQLite is designed to be lightweight and self-contained. One header file, one library, and you ' re relational, no external database server required
Simple (simplicity)
Flexible (flexibility)
Reliable (reliability):
SQLite's core is about 30,000 lines of standard C code, which are modular and easy to read.

Transaction (Transaction)

Cycle of transactions (Transaction lifecycles)
Two things are worth noting between programs and transactions:
A, which objects run under the transaction-this is directly related to the API.
B, the life cycle of the transaction, when it starts, when it ends, and when it starts to affect the other connections (which is important for concurrency)--it involves the concrete implementation of the SQLite.
A connection (connection) can contain multiple (statement), and each connection has a b-tree and a pager associated with the database. Pager plays an important role in the connection because it manages transactions, locks, memory caches, and is responsible for crash recovery (crash recovery). When you write a database, remember the most important thing: at any time, only one transaction executes a connection. These answer the first question.
Generally speaking, the life of a business is similar to statement, you can also manually end it. By default, transactions are automatically committed, but you can also manually submit them via Begin..commit. The next question is the lock.

The following points are noteworthy for this figure:
A, a transaction can start in the unlocked,reserved or exclusive three states. By default, starts at unlocked.
B, unlocked in the white box, PENDING, shared, and reserved can exist at the same time as a database.
C, starting with the gray pending, things get tighter, meaning that the transaction wants the exclusion lock (EXCLUSIVE) (Note the difference from the white box).
Although the lock has so many states, but physically speaking, there are only two situations: reading and writing business.

Readers can download the SQLite 3.3.4 version from http://www.sqlite.org/
CMD Enter command line

To Create a database file:
>sqlite3 d:\test.db Carriage return
It generates a test.db in D disk.
And it SQLite3 the test.db.

Use. Help to see what the orders are.
>.HELP Carriage return can

See how many tables have been created
>.tables

look at the table structure
>.schema Table Name

look at the database currently hanging
>.database

If you want to output the query to a file
>.output file name
> Query statements;

Print the results of the query on screen
>.output stdout

Output the table structure, and the index will also output
> Dump table name
Exit
>.exit or. Quit

from http://sqlite.phxsoftware.com/ Download Ado.net driver.
Installation is downloaded, System.Data.SQLite.dll exists in the installation directory
All we need to do is copy this file to the reference directory and add the reference to the SQLite database operation.
All Ado.net objects begin with SQLite, such as Sqliteconnection.
The connection string only needs the following method
Data source=d:\test.db or datasource=test.db--are applied to directories that are automatically found with applications or. Net
The rest is very simple ~ ~

SQL syntax
DDL syntax is very embarrassing because it used to be SQL Server or iseries.
Create a table for a single primary key
CREATE TABLE [Admin] (
[UserName] [nvarchar] (PRIMARY KEY not NULL,
[Password] [nvarchar] () not NULL,
[Rank] [smallint] Not NULL,
[MailServer] [nvarchar] () not NULL,
[Mailuser] [nvarchar] () not NULL,
[MailPassword] [nvarchar] () not NULL,
[Mail] [nvarchar] () Not NULL
) ;
Create a table with multiple primary key
CREATE TABLE [Codedetail] (
[Cdtype] [nvarchar] (ten) Not NULL,
[Cdcode] [nvarchar] () not NULL,
[CdString1]   [ntext] Not NULL,
[CdString2]   [ntext] Not NULL,
[CdString3]   [ntext] Not NULL,
PRIMARY KEY (Cdtype,cdcode)
) ;
Create an index
CREATE INDEX [Ix_account] on [Account] ([Ischeck], [UserName]);
You can also view and so on.
 

SQLite Paging Query

Writing 1:

SELECT * from TABLE1 LIMIT OFFSET 20;

Writing 2:

SELECT * from TABLE1 LIMIT 20, 20;

compression of SQLite files
After many data deletion, inserting data, updating data, the database volume increases, but the actual effective data quantity is very small, then the database needs to be compressed, sorted, and removed from the physical file. Call the SQL command:

VACUUM

The realization of vacuum

Data insertion and update

Replace the INSERT, Update command with Replace. Performs an insert if a condition record is not met, and an update is performed if a condition record is met.

Copy Code code as follows:

REPLACE into TABLE1 (col1, col2, col3) VALUES (Val1, VAL2,VAL3);

The effect of Insert or replace into and replace into is the same as the above sentence can also be written

Copy Code code as follows:

Insert or Replace into TABLE1 (col1, col2, col3) VALUES (Val1, VAL2,VAL3);

Character encoding Conversion
Sqlite3 's source code, provides Utf8tounicode (), UnicodeToUtf8 (), Mbcstounicode (), Unicodetombcs (), Sqlite3_win32_mbcs_to_utf8 (), Utf8tombcs () and other 8 functions for the conversion of characters between different encodings, but not listed in the Sqlite3.def, sqlite3.h file, that is not public. In these functions, you use the MultiByteToWideChar (), WideCharToMultiByte () two functions to implement the conversion between characters.
Development example

Copy Code code as follows:

Using System;
Using System.Data;
Using System.Data.SQLite;
Using System.Collections.Generic;
Using System.IO;
Namespace Filesystemwatcthrdemo.datahelper
{
public class Sqlitehelper
{
<summary>
ConnectionString Sample: DATASOURCE=TEST.DB3; Pooling=true; Failifmissing=false
</summary>
public static string ConnectionString
{
Get
{
return @ "Data source=" +databasepath+ ";";
}
Set {throw new NotImplementedException ();}
}
public static string DatabasePath
{
get {return "springyang.db";};
}
private static Object lockobject = new Object ();
private static void PrepareCommand (Sqlitecommand cmd, sqliteconnection conn, string cmdtext, List<sqliteparameter > Parameters)
{
IF (Conn. State!= ConnectionState.Open)
Conn. Open ();
Cmd. Parameters.clear ();
Cmd. Connection = conn;
Cmd.commandtext = Cmdtext;
Cmd.commandtype = CommandType.Text;
Cmd.commandtimeout = 30;
foreach (var parameter in parameters)
{
Cmd. Parameters.Add (parameter);
}
}
public static DataSet executequery (string cmdtext, list<sqliteparameter> parameters)
{
Lock (LockObject)
{
using (sqliteconnection conn = new Sqliteconnection (ConnectionString))
{
using (Sqlitecommand command = new Sqlitecommand ())
{
DataSet ds = new DataSet ();
PrepareCommand (Command, Conn, cmdtext, parameters);
Sqlitedataadapter da = new Sqlitedataadapter (command);
Da. Fill (DS);
return DS;
}
}
}
}
public static int ExecuteNonQuery (string cmdtext, list<sqliteparameter> parameters)
{
Lock (LockObject)
{
using (sqliteconnection conn = new Sqliteconnection (ConnectionString))
{
using (Sqlitecommand command = new Sqlitecommand ())
{
PrepareCommand (Command, Conn, cmdtext, parameters);
return command. ExecuteNonQuery ();
}
}
}
}
public static Sqlitedatareader ExecuteReader (string cmdtext, list<sqliteparameter> parameters)
{
Lock (LockObject)
{
Sqliteconnection conn = new sqliteconnection (ConnectionString);
Sqlitecommand command = new Sqlitecommand ();
PrepareCommand (Command, Conn, cmdtext, parameters);
Sqlitedatareader sqlitedatareader = command. ExecuteReader ();
return sqlitedatareader;
}
}
public static Object ExecuteScalar (string cmdtext, list<sqliteparameter> parameters)
{
Lock (LockObject)
{
using (sqliteconnection conn = new Sqliteconnection (ConnectionString))
{
using (Sqlitecommand command = new Sqlitecommand ())
{
PrepareCommand (Command, Conn, cmdtext, parameters);
return command. ExecuteScalar ();
}
}
}
}
public static void CreateDatabase ()
{
if (! File.exists (DatabasePath))
Sqliteconnection.createfile (DatabasePath);
CreateTable ();
}
public static void CreateTable ()
{
ExecuteNonQuery (Codedetailtabale, NULL);
}
private static string Codedetailtabale
{
Get
{
return @ "CREATE TABLE [Codedetail]" (
[Cdtype] [nvarchar] (ten) Not NULL,
[Cdcode] [nvarchar] () not NULL,
[CdString1] [ntext] Not NULL,
[CdString2] [ntext] Not NULL,
[CdString3] [ntext] Not NULL,
PRIMARY KEY (Cdtype,cdcode)
) ;";
}
}
}
}

Example explanation
A, use to own defined lock private static object lockobject = new Object ();
B, close the operation after the connection is used. Use using
C, CREATE DATABASE command: Sqliteconnection.createfile (DatabasePath);
Finally, I'll explain the classic usage of insert or Replace into.

Copy Code code as follows:

Insert or Replace into User (ID, name,age) Select old.id,new. Name,new. Age from
(SELECT ' Spring Yang ' as Name, ' the ' as ' age ') as the New Left join (select Id,name from User where Name = ' Spring Yang ') as old On old. Name = new. Name