asp.net下SQLite(輕量級最佳數(shù)據(jù)庫) 原理分析和開發(fā)應(yīng)用
概述
SQLite介紹
自幾十年前出現(xiàn)的商業(yè)應(yīng)用程序以來，數(shù)據(jù)庫就成為軟件應(yīng)用程序的主要組成部分。正與數(shù)據(jù)庫管理系統(tǒng)非常關(guān)鍵一樣，它們也變得非常龐大，并占用了相當(dāng)多的系統(tǒng)資源，增加了管理的復(fù)雜性。隨著軟件應(yīng)用程序逐漸模塊模塊化，一種新型數(shù)據(jù)庫會(huì)比大型復(fù)雜的傳統(tǒng)數(shù)據(jù)庫管理系統(tǒng)更適應(yīng)。嵌入式數(shù)據(jù)庫直接在應(yīng)用程序進(jìn)程中運(yùn)行，提供了零配置（zero-configuration）運(yùn)行模式，并且資源占用非常少。
SQLite是一個(gè)開源的嵌入式關(guān)系數(shù)據(jù)庫，它在2000年由D. Richard Hipp發(fā)布，它的減少應(yīng)用程序管理數(shù)據(jù)的開銷，SQLite可移植性好，很容易使用，很小，高效而且可靠。
SQLite嵌入到使用它的應(yīng)用程序中，它們共用相同的進(jìn)程空間，而不是單獨(dú)的一個(gè)進(jìn)程。從外部看，它并不像一個(gè)RDBMS，但在進(jìn)程內(nèi)部，它卻是完整的，自包含的數(shù)據(jù)庫引擎。
嵌入式數(shù)據(jù)庫的一大好處就是在你的程序內(nèi)部不需要網(wǎng)絡(luò)配置，也不需要管理。因?yàn)榭蛻舳撕头?wù)器在同一進(jìn)程空間運(yùn)行。SQLite 的數(shù)據(jù)庫權(quán)限只依賴于文件系統(tǒng)，沒有用戶帳戶的概念。SQLite 有數(shù)據(jù)庫級鎖定，沒有網(wǎng)絡(luò)服務(wù)器。它需要的內(nèi)存，其它開銷很小，適合用于嵌入式設(shè)備。你需要做的僅僅是把它正確的編譯到你的程序。
架構(gòu)(architecture)
SQLite采用了模塊的設(shè)計(jì)，它由三個(gè)子系統(tǒng)，包括8個(gè)獨(dú)立的模塊構(gòu)成。
接口(Interface)
接口由SQLite C API組成，也就是說不管是程序、腳本語言還是庫文件，最終都是通過它與SQLite交互的(我們通常用得較多的ODBC/JDBC最后也會(huì)轉(zhuǎn)化為相應(yīng)C API的調(diào)用)。
編譯器(Compiler)
在編譯器中，分詞器（Tokenizer）和分析器(Parser)對SQL進(jìn)行語法檢查，然后把它轉(zhuǎn)化為底層能更方便處理的分層的數(shù)據(jù)結(jié)構(gòu)---語法樹，然后把語法樹傳給代碼生成器(code generator)進(jìn)行處理。而代碼生成器根據(jù)它生成一種針對SQLite的匯編代碼，最后由虛擬機(jī)(Virtual Machine)執(zhí)行。
虛擬機(jī)(Virtual Machine)
架構(gòu)中最核心的部分是虛擬機(jī)，或者叫做虛擬數(shù)據(jù)庫引擎(Virtual Database Engine,VDBE)。它和Java虛擬機(jī)相似，解釋執(zhí)行字節(jié)代碼。VDBE的字節(jié)代碼由128個(gè)操作碼(opcodes)構(gòu)成，它們主要集中在數(shù)據(jù)庫操作。它的每一條指令都用來完成特定的數(shù)據(jù)庫操作(比如打開一個(gè)表的游標(biāo))或者為這些操作?？臻g的準(zhǔn)備(比如壓入?yún)?shù))。總之，所有的這些指令都是為了滿足SQL命令的要求(關(guān)于VM，后面會(huì)做詳細(xì)介紹)。
后端(Back-End)
后端由B-樹(B-tree)，頁緩存(page cache，pager)和操作系統(tǒng)接口(即系統(tǒng)調(diào)用)構(gòu)成。B-tree和page cache共同對數(shù)據(jù)進(jìn)行管理。B-tree的主要功能就是索引，它維護(hù)著各個(gè)頁面之間的復(fù)雜的關(guān)系，便于快速找到所需數(shù)據(jù)。而pager的主要作用就是通過OS接口在B-tree和Disk之間傳遞頁面。
SQLite的特點(diǎn)(SQLite's Features and Philosophy)
零配置(Zero Configuration)
可移植(Portability)：
它是運(yùn)行在Windows,Linux,BSD,Mac OS X和一些商用Unix系統(tǒng)，比如Sun的Solaris,IBM的AIX，同樣，它也可以工作在許多嵌入式操作系統(tǒng)下，比如QNX,VxWorks,Palm OS, Symbin和Windows CE。
Compactness：
SQLite是被設(shè)計(jì)成輕量級，自包含的。one header file, one library, and you're relational, no external database server required
簡單(Simplicity)
靈活(Flexibility)
可靠(Reliability)：
SQLite的核心大約有3萬行標(biāo)準(zhǔn)C代碼，這些代碼都是模塊化的，很容易閱讀。
事務(wù)(Transaction)
事務(wù)的周期(Transaction Lifecycles)
程序與事務(wù)之間有兩件事值得注意：
A、哪些對象在事務(wù)下運(yùn)行——這直接與API有關(guān)。
B、事務(wù)的生命周期，即什么時(shí)候開始，什么時(shí)候結(jié)束以及它在什么時(shí)候開始影響別的連接（這點(diǎn)對于并發(fā)性很重要）——這涉及到SQLite的具體實(shí)現(xiàn)。
一個(gè)連接（connection）可以包含多個(gè)(statement)，而且每個(gè)連接有一個(gè)與數(shù)據(jù)庫關(guān)聯(lián)的B-tree和一個(gè)pager。Pager在連接中起著很重要的作用，因?yàn)樗芾硎聞?wù)、鎖、內(nèi)存緩存以及負(fù)責(zé)崩潰恢復(fù)(crash recovery)。當(dāng)你進(jìn)行數(shù)據(jù)庫寫操作時(shí)，記住最重要的一件事：在任何時(shí)候，只在一個(gè)事務(wù)下執(zhí)行一個(gè)連接。這些回答了第一個(gè)問題。
一般來說，一個(gè)事務(wù)的生命和statement差不多，你也可以手動(dòng)結(jié)束它。默認(rèn)情況下，事務(wù)自動(dòng)提交，當(dāng)然你也可以通過BEGIN..COMMIT手動(dòng)提交。接下來就是鎖的問題。
關(guān)于這個(gè)圖有以下幾點(diǎn)值得注意：
A、一個(gè)事務(wù)可以在UNLOCKED，RESERVED或EXCLUSIVE三種狀態(tài)下開始。默認(rèn)情況下在UNLOCKED時(shí)開始。
B、白色框中的UNLOCKED, PENDING, SHARED和 RESERVED可以在一個(gè)數(shù)據(jù)庫的同一時(shí)存在。
C、從灰色的PENDING開始，事情就變得嚴(yán)格起來，意味著事務(wù)想得到排斥鎖(EXCLUSIVE)（注意與白色框中的區(qū)別）。
雖然鎖有這么多狀態(tài)，但是從體質(zhì)上來說，只有兩種情況：讀事務(wù)和寫事務(wù)。
讀者可以從http://www.sqlite.org/下載SQLite 3.3.4的版本
Cmd 進(jìn)入命令行
>SQLite3 d:\test.db 回車
就生成了一個(gè)test.db在d盤。
這樣同時(shí)也SQLite3掛上了這個(gè)test.db
用.help可以看看有什么命令
>.help 回車即可
看看有創(chuàng)建了多少表
>.tables
看表結(jié)構(gòu)
>.schema 表名
看看目前掛的數(shù)據(jù)庫
>.database
如果要把查詢輸出到文件
>.output 文件名
> 查詢語句；
把查詢結(jié)果用屏幕輸出
>.output stdout
把表結(jié)構(gòu)輸出，同時(shí)索引也會(huì)輸出
> .dump 表名
退出
>.exit 或者.quit
從http://sqlite.phxsoftware.com/下載Ado.net驅(qū)動(dòng)。
下載了安裝，在安裝目錄中存在System.Data.SQLite.dll
我們只需要拷貝這個(gè)文件到引用目錄，并添加引用即可對SQLite數(shù)據(jù)庫操作了
所有的Ado.net對象都是以SQLite開頭的，比如SQLiteConnection
連接串只需要如下方式
Data Source=d:\test.db 或者DataSource=test.db--應(yīng)用在和應(yīng)用程序或者.net能夠自動(dòng)找到的目錄
剩下的就很簡單了~~
SQL語法
由于以前用SQLServer或者ISeries，所以DDL的語法很汗顏
創(chuàng)建一個(gè)單個(gè)Primary Key的table
CREATE TABLE [Admin] (
[UserName] [nvarchar] (20) PRIMARY KEY NOT NULL ,
[Password] [nvarchar] (50) NOT NULL ,
[Rank] [smallint] NOT NULL ,
[MailServer] [nvarchar] (50) NOT NULL ,
[MailUser] [nvarchar] (50) NOT NULL ,
[MailPassword] [nvarchar] (50) NOT NULL ,
[Mail] [nvarchar] (50) NOT NULL
) ;
創(chuàng)建一個(gè)多個(gè)Primary Key的table
CREATE TABLE [CodeDetail] (
[CdType] [nvarchar] (10) NOT NULL ,
[CdCode] [nvarchar] (20) NOT NULL ,
[CdString1] [ntext] NOT NULL ,
[CdString2] [ntext] NOT NULL ,
[CdString3] [ntext] NOT NULL,
PRIMARY KEY (CdType,CdCode)
) ;
創(chuàng)建索引
CREATE INDEX [IX_Account] ON [Account]([IsCheck], [UserName]);
還可以視圖等等。
SQLite 分頁查詢
寫法1:
SELECT * FROM TABLE1 LIMIT 20 OFFSET 20 ;
寫法2:
SELECT * FROM TABLE1 LIMIT 20 , 20;
SQLite 文件的壓縮
在多次刪除數(shù)據(jù)、插入數(shù)據(jù)、更新數(shù)據(jù)后，數(shù)據(jù)庫體積增大，但實(shí)際有效數(shù)據(jù)量很小，則需要對數(shù)據(jù)庫進(jìn)行壓縮、整理，把已經(jīng)刪除的數(shù)據(jù)從物理文件中移除。調(diào)用一下SQL命令即可：
VACUUM
VACUUM的實(shí)現(xiàn)
數(shù)據(jù)插入與更新
使用REPLACE替代INSERT、UPDATE命令。在無滿足條件記錄，則執(zhí)行Insert，有滿足條件記錄，則執(zhí)行UPDATE。
REPLACE INTO TABLE1(col1, col2, col3) VALUES(val1, val2,val3);
Insert or Replace Into 和Replace Into 的效果是一樣的上面這句話也可以這樣寫
Insert or Replace INTO TABLE1(col1, col2, col3) VALUES(val1, val2,val3);
字符編碼轉(zhuǎn)換
sqlite3的源碼中，提供了utf8ToUnicode()、unicodeToUtf8()、mbcsToUnicode()、unicodeToMbcs()、sqlite3_win32_mbcs_to_utf8 ()、utf8ToMbcs ()等8個(gè)函數(shù)進(jìn)行字符在不同編碼間的轉(zhuǎn)換，但未在sqlite3.def、sqlite3.h文件中列出，即未對外公開。這些函數(shù)中，都使用了MultiByteToWideChar()、WideCharToMultiByte()兩個(gè)函數(shù)實(shí)現(xiàn)字符間轉(zhuǎn)換。
開發(fā)示例
using System;
using System.Data;
using System.Data.SQLite;
using System.Collections.Generic;
using System.IO;
namespace FileSystemWatcthrDemo.DataHelper
{
public class SqLiteHelper
{
/// <summary>
/// ConnectionString樣例：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] (10) NOT NULL ,
[CdCode] [nvarchar] (20) NOT NULL ,
[CdString1] [ntext] NOT NULL ,
[CdString2] [ntext] NOT NULL ,
[CdString3] [ntext] NOT NULL,
PRIMARY KEY (CdType,CdCode)
) ;";
}
}
}
}
示例講解
A、使用到自己定義的鎖private static object lockObject = new object();
B、使用完連接后都進(jìn)行關(guān)閉操作。使用了using
C、創(chuàng)建數(shù)據(jù)庫命令：SQLiteConnection.CreateFile(DataBasePath);
最后再講解個(gè)Insert or Replace into的經(jīng)典用法
Insert or Replace INTO User(ID, Name,Age) Select old.ID,new.Name,new.Age From
(select 'Spring Yang' as Name, '25' as Age) as new left join (Select ID,Name from User where Name = 'Spring Yang' ) as old on old.Name = new.Name
相關(guān)文章
這篇文章介紹了MVC中的Action方法的返回類型，文中通過示例代碼介紹的非常詳細(xì)。對大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2022-03-03
asp.net平臺下C#實(shí)現(xiàn)Socket通信
這篇文章介紹了asp.net平臺下C#實(shí)現(xiàn)Socket通信的方法，文中通過示例代碼介紹的非常詳細(xì)。對大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2022-01-01
使用方法很簡單，對于需要使用asp.net下timer控件的使用是個(gè)不錯(cuò)的例子2008-10-10
本文主要介紹Literal的使用方法，并簡單列出和Label控件之間的區(qū)別，希望能幫助大家選擇使用。2016-04-04
這篇文章介紹了.net中的Span<T>類和Memory<T>類，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2022-07-07
這篇文章介紹了Blazor路由與頁面導(dǎo)航開發(fā)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2022-01-01
ASP.NET2.0+SQL Server2005構(gòu)建多層應(yīng)用
ASP.NET2.0+SQL Server2005構(gòu)建多層應(yīng)用...2006-12-12
ASP.NET?Core開發(fā)環(huán)境安裝配置
這篇文章介紹了ASP.NET?Core開發(fā)環(huán)境安裝配置，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2022-02-02
這篇文章介紹了ASP.NET?MVC框架，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2022-02-02
這篇文章介紹了ASP.NET Core中的Razor頁面使用視圖組件的方法，文中通過示例代碼介紹的非常詳細(xì)。對大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2022-02-02