asp.net下SQLite(轻量级最佳数据库) 原理分析和开发应用
概述
SQLite介绍
自几十年前出现的商业应用程序以来,数据库就成为软件应用程序的主要组成部分。正与数据库管理系统非常关键一样,它们也变得非常庞大,并占用了相当多的系统资源,增加了管理的复杂性。随着软件应用程序逐渐模块模块化,一种新型数据库会比大型复杂的传统数据库管理系统更适应。嵌入式数据库直接在应用程序进程中运行,提供了零配置(zero-configuration)运行模式,并且资源占用非常少。
SQLite是一个开源的嵌入式关系数据库,它在2000年由D. Richard Hipp发布,它的减少应用程序管理数据的开销,SQLite可移植性好,很容易使用,很小,高效而且可靠。
SQLite嵌入到使用它的应用程序中,它们共用相同的进程空间,而不是单独的一个进程。从外部看,它并不像一个RDBMS,但在进程内部,它却是完整的,自包含的数据库引擎。
嵌入式数据库的一大好处就是在你的程序内部不需要网络配置,也不需要管理。因为客户端和服务器在同一进程空间运行。SQLite 的数据库权限只依赖于文件系统,没有用户帐户的概念。SQLite 有数据库级锁定,没有网络服务器。它需要的内存,其它开销很小,适合用于嵌入式设备。你需要做的仅仅是把它正确的编译到你的程序。
架构(architecture)
SQLite采用了模块的设计,它由三个子系统,包括8个独立的模块构成。
接口(Interface)
接口由SQLite C API组成,也就是说不管是程序、脚本语言还是库文件,最终都是通过它与SQLite交互的(我们通常用得较多的ODBC/JDBC最后也会转化为相应C API的调用)。
编译器(Compiler)
在编译器中,分词器(Tokenizer)和分析器(Parser)对SQL进行语法检查,然后把它转化为底层能更方便处理的分层的数据结构---语法树,然后把语法树传给代码生成器(code generator)进行处理。而代码生成器根据它生成一种针对SQLite的汇编代码,最后由虚拟机(Virtual Machine)执行。
虚拟机(Virtual Machine)
架构中最核心的部分是虚拟机,或者叫做虚拟数据库引擎(Virtual Database Engine,VDBE)。它和Java虚拟机相似,解释执行字节代码。VDBE的字节代码由128个操作码(opcodes)构成,它们主要集中在数据库操作。它的每一条指令都用来完成特定的数据库操作(比如打开一个表的游标)或者为这些操作栈空间的准备(比如压入参数)。总之,所有的这些指令都是为了满足SQL命令的要求(关于VM,后面会做详细介绍)。
后端(Back-End)
后端由B-树(B-tree),页缓存(page cache,pager)和操作系统接口(即系统调用)构成。B-tree和page cache共同对数据进行管理。B-tree的主要功能就是索引,它维护着各个页面之间的复杂的关系,便于快速找到所需数据。而pager的主要作用就是通过OS接口在B-tree和Disk之间传递页面。
SQLite的特点(SQLite's Features and Philosophy)
零配置(Zero Configuration)
可移植(Portability):
它是运行在Windows,Linux,BSD,Mac OS X和一些商用Unix系统,比如Sun的Solaris,IBM的AIX,同样,它也可以工作在许多嵌入式操作系统下,比如QNX,VxWorks,Palm OS, Symbin和Windows CE。
Compactness:
SQLite是被设计成轻量级,自包含的。one header file, one library, and you're relational, no external database server required
简单(Simplicity)
灵活(Flexibility)
可靠(Reliability):
SQLite的核心大约有3万行标准C代码,这些代码都是模块化的,很容易阅读。
事务(Transaction)
事务的周期(Transaction Lifecycles)
程序与事务之间有两件事值得注意:
A、哪些对象在事务下运行——这直接与API有关。
B、事务的生命周期,即什么时候开始,什么时候结束以及它在什么时候开始影响别的连接(这点对于并发性很重要)——这涉及到SQLite的具体实现。
一个连接(connection)可以包含多个(statement),而且每个连接有一个与数据库关联的B-tree和一个pager。Pager在连接中起着很重要的作用,因为它管理事务、锁、内存缓存以及负责崩溃恢复(crash recovery)。当你进行数据库写操作时,记住最重要的一件事:在任何时候,只在一个事务下执行一个连接。这些回答了第一个问题。
一般来说,一个事务的生命和statement差不多,你也可以手动结束它。默认情况下,事务自动提交,当然你也可以通过BEGIN..COMMIT手动提交。接下来就是锁的问题。
关于这个图有以下几点值得注意:
A、一个事务可以在UNLOCKED,RESERVED或EXCLUSIVE三种状态下开始。默认情况下在UNLOCKED时开始。
B、白色框中的UNLOCKED, PENDING, SHARED和 RESERVED可以在一个数据库的同一时存在。
C、从灰色的PENDING开始,事情就变得严格起来,意味着事务想得到排斥锁(EXCLUSIVE)(注意与白色框中的区别)。
虽然锁有这么多状态,但是从体质上来说,只有两种情况:读事务和写事务。
读者可以从http://www.sqlite.org/下载SQLite 3.3.4的版本
Cmd 进入命令行
>SQLite3 d:\test.db 回车
就生成了一个test.db在d盘。
这样同时也SQLite3挂上了这个test.db
用.help可以看看有什么命令
>.help 回车即可
看看有创建了多少表
>.tables
看表结构
>.schema 表名
看看目前挂的数据库
>.database
如果要把查询输出到文件
>.output 文件名
> 查询语句;
把查询结果用屏幕输出
>.output stdout
把表结构输出,同时索引也会输出
> .dump 表名
退出
>.exit 或者.quit
从http://sqlite.phxsoftware.com/下载Ado.net驱动。
下载了安装,在安装目录中存在System.Data.SQLite.dll
我们只需要拷贝这个文件到引用目录,并添加引用即可对SQLite数据库操作了
所有的Ado.net对象都是以SQLite开头的,比如SQLiteConnection
连接串只需要如下方式
Data Source=d:\test.db 或者DataSource=test.db--应用在和应用程序或者.net能够自动找到的目录
剩下的就很简单了~~
SQL语法
由于以前用SQLServer或者ISeries,所以DDL的语法很汗颜
创建一个单个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
) ;
创建一个多个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)
) ;
创建索引
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 文件的压缩
在多次删除数据、插入数据、更新数据后,数据库体积增大,但实际有效数据量很小,则需要对数据库进行压缩、整理,把已经删除的数据从物理文件中移除。调用一下SQL命令即可:
VACUUM
VACUUM的实现
数据插入与更新
使用REPLACE替代INSERT、UPDATE命令。在无满足条件记录,则执行Insert,有满足条件记录,则执行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);
字符编码转换
sqlite3的源码中,提供了utf8ToUnicode()、unicodeToUtf8()、mbcsToUnicode()、unicodeToMbcs()、sqlite3_win32_mbcs_to_utf8 ()、utf8ToMbcs ()等8个函数进行字符在不同编码间的转换,但未在sqlite3.def、sqlite3.h文件中列出,即未对外公开。这些函数中,都使用了MultiByteToWideChar()、WideCharToMultiByte()两个函数实现字符间转换。
开发示例
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、使用完连接后都进行关闭操作。使用了using
C、创建数据库命令:SQLiteConnection.CreateFile(DataBasePath);
最后再讲解个Insert or Replace into的经典用法
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
相关文章
- 这篇文章主要介绍了C#简单访问SQLite数据库的方法,涉及SQLite数据库的下载、安装及使用C#连接、查询SQLIte数据库的相关技巧,需要的朋友可以参考下...2020-06-25
- 本文给大家介绍的是C#中嵌入SQLite数据库的简单方法,十分的方便也很实用,有需要的小伙伴可以参考下。...2020-06-25
- 这篇文章主要为大家详细介绍了C#创建SQLite控制台应用程序,具有一定的参考价值,感兴趣的小伙伴们可以参考一下...2020-06-25
- Sqlite 常用函数,在实际开发中,经常遇的到。...2020-07-11
- 这篇文章主要介绍了C#解决SQlite并发异常问题的方法,通过使用读写锁达到多线程安全访问,进而解决SQLite并发异常的问题,具有一定参考借鉴价值,需要的朋友可以参考下...2020-06-25
- 这篇文章主要介绍了C#操作SQLite数据库之读写数据库的方法,简单分析了C#针对SQLite数据库的读写及显示等操作相关技巧,具有一定参考借鉴价值,需要的朋友可以参考下...2020-06-25
- SQLite,是一款轻型的数据库,用于本地的数据储存。其优点有很多,下面通过本文给大家介绍SQLite在C#中的安装与操作技巧,感兴趣的的朋友参考下吧...2020-06-25
- 这篇文章主要给大家介绍了关于C# SQLite执行效率优化的相关资料,文中通过示例代码介绍的非常详细,对大家学习或者使用C# SQLite具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧...2020-06-25
- 增删改查操作,其中增删改操作被称为数据操作语言 DML,相对来说简单一点。 查操作相对来说复杂一点,涉及到很多子句,所以这篇先讲增删改操作,以例子为主,后面再讲查操作...2020-07-11
- db.execsql(sql); 或者db.insert()、db.delete()、db.update(),并且包括数据表的创建和删除等等也可以通过execsql实现 //创建表 代码如下 复制代码 ...2016-11-25
- 这篇文章主要介绍了C# SQLite事务操作方法,简单分析了使用SQL代码及C#代码实现事务功能的操作技巧,需要的朋友可以参考下...2020-06-25
- 这篇文章主要介绍了C# SQlite操作方法,较为详细的讲述了SQLite的下载与C#针对SQLite的连接、创建、与执行SQL语句等操作相关技巧,需要的朋友可以参考下...2020-06-25
- 这篇文章主要介绍了C#/.Net 中快速批量给SQLite数据库插入测试数据,本文直接给出实例代码,需要的朋友可以参考下...2020-06-25
- 这篇文章主要介绍了sqlite时间戳转时间、时间转时间戳的方法,需要的朋友可以参考下...2020-07-11
- 这篇文章主要介绍了SQLite教程(二):C/C++接口简介,本文讲解了C/C++接口概述、核心对象和接口、参数绑定等内容,需要的朋友可以参考下...2020-07-11
- sqlite 数据库连接类就是利用了php 与sqlite进行连接操作。 代码如下 复制代码 ...2016-11-25
- sqlite的官网 sqlite.org/index.html SQLite 作为一个SQL数据库引擎,是由C语言实现的,又小又快,具有高可靠性且功能齐全。 作为嵌入式数据库,在移动设备中使用非常广泛且方便。本文讲解如何使用python操作sqlite数据库...2021-06-23
- 这篇文章主要介绍了Ionic2调用本地SQlite实例,小编觉得挺不错的,现在分享给大家,也给大家做个参考。一起跟随小编过来看看吧...2017-04-27
- 这篇文章主要介绍了C#中增加SQLite事务操作支持与使用方法,结合实例形式分析了C#中针对SQLite事务操作的添加及使用技巧,需要的朋友可以参考下...2020-06-25
- 这篇文章主要介绍了SQLite教程(四):内置函数,本文讲解了聚合函数、核心函数、日期和时间函数、,需要的朋友可以参考下...2020-07-11