.net程序使用sqlite3和sqlite3简介

.Net 程序使用 SQLITE3 和 SQLite3 简介 什么是 SQLite？ SQLite（）是一个嵌入式的数据库管理系统，符合大部分的 SQL 92 标准， 它本身仅仅是一个数百 KB 的动态链接库。它使用一个文件作为存放全部数据的场所。在应 用程序中使用的话不需要任何的配置，十分方便。 何时使用？ SQLite 的优点是零配置，对程序所在的计算机没有任何要求只要有一个 dll 就可以， 因此它尤其适合于作为个人应用程序的数据库，放在托管服务器上的访问流量不十分巨大 的网站的数据库，据 SQLite 官网的说明，对于日点击量小于 10 万的网站，都是合适的 日点击率超过 10 万的网站是不多的。 SQLite 不提供表级别的锁定，一旦有某个进程有写操作或者启用了事物，就会锁住整个数 据库，从而挂起其它进程，因此它不适合于高并发的场合，也不适合 C/S 程序。 如何使用？ SQLite 本身提供了一套 C/C+API 的说明，另外，还有一个命令行工具。不过，使用都不 很方便。要直接操作 SQLite 数据库，可以使用一个叫做 SQLiteExpert 的工具，它的 Pers onal 版本是可以免费得到的。要在.NET 程序中使用 SQLite，当然要一个 ADO.NET 的驱动 ，这个驱动很容易获得，而且不止一个版本，比较常用的是 SQLite.NET，它集成了 ADO.NE T 驱动和 VS.NET 的设计器支持。 安装了 SQLite.NET 之后，在 VS 的 Server 面板中添加数据库连接，就会有 Sqlite 的选项 ： 如果在页面上配置了 SqlDataSource，会在 webConfig 中自动生成一个连接字符串： SQLite 的连接字符串很简单，只有一个属性是必填的，那就是 data source，data source 后面跟上数据库文件的路径就可以，但是如果在托管服务器上，我们无权知道文件所在的 绝对路径。可以使用 ADO.NET 提供的另一种写法： 其中 |DataDirectory|表示网站的 App_Data 目录。这个方法不仅适合于 Sqlite3，对于需 要加载文件的数据库，例如 Access 或者通过 SqlExpress 附加的 SQL Server 数据库文件都 是适用的。 不同点 （1）数据类型 SQLite 与其他常见的 DBMS 的最大不同是它对数据类型的支持。其他常见的 DBMS 通常支持 强类型的数据，也就是每一列的类型都必须预先指定，但是 SQLite 采用的是弱类型的字段 。实际上，其内部仅有下列五种存储类型： NULL: 表示一个 NULL 值 INTEGER: 用来存储一个整数,根据大小可以使用 1,2,3,4,6,8 位来存储. REAL: IEEE 浮点数 TEXT: 按照字符串来存储 BLOB: 按照二进制值存储,不做任何改变. 要注意,这些类型是值本身的属性,而不是列的属性. 但是为了和其他 DBMS（以及 SQL 标准）兼容，在其 create table 语句中可以指定列的类 型，为此,SQLite 有个列相似性的概念(Column Affinity). 列相似性是列的属性,SQLite 有以下几种列相似性: TEXT: TEXT 列使用 NULL,TEXT 或者 BLOB 存储任何插入到此列的数据,如果数据是数字,则 转换为 TEXT. NUMERIC: NUMERIC 列可以使用任何存储类型,它首先试图将插入的数据转换为 REAL 或 INTE GER 型的,如果成功则存储为 REAL 和 INTEGER 型,否则不加改变的存入. INTEGER:和 NUMERIC 类似,只是它将可以转换为 INTEGER 值都转换为 INTEGER，如果是 REAL 型，且没有小数部分，也转为 INTEGER REAL: 和 NUMERIC 类型 只是它将可以转换为 REAL 和 INTEGER 值都转换为 REAL. NONE:不做任何改变的尝试. SQLite 根据 create table 语句来决定每个列的列相似性.规则如下(大小写均忽略): 1. 如果数据类型中包括 INT,则是 INTEGER 2. 如果数据类型中包括 CHAR,CLOB,TEXT 则是 TEXT 3. 如果数据类型中包括 BLOB,或者没有指定数据类型,则是 NONE 4. 如果数据类型中包括 REAL,FLOA 或者 DOUB,则是 REAL 5. 其余的情况都是 NUMERIC 由上可知,对于 sqlite 来说 char,varchar,nchar,nvarchar 等都是等价的，且后面最大长 度也是没有意义的。但是对于其他 DBMS 却不是相同的。另外，列相似性仅仅是向 Sqlite 提出了一个存储数据的建议，即使实际存储的数据类型和列相似性不一致，SQLite 还是可 以成功插入的，下面给出一个例子来说明下以上论述，注意，这个例子需要在 SQLite 的命 令行下运行，如果在 SQLite Expert 工具下执行，SQLite 会进行一些额外的处理。 如下图，创建一个新表，两列的类型分别是 int 和 varchar，但是还是可以插入其他类型 的数据，并且可以正确读出。 要注意 SQLite 的这种特性可能会给 SQLite 的 ADO 驱动造成一些麻烦，因为.NET 都是强类 型的语言，必须把数据库中的字段转换为合适的类型，所以在插入数据的时候，还是应该 严格的按照 create table 中的定义插入数据。 （2）自增列 在 SQL Server 中，只需要指定 identity(1,1)就可以设定自增列，但是在 SQLite 中不支 持这样做。在 SQLite 中，任何一张表都有一个字段类型是 Integer，且是自增的，这个列 是作为 B 树的索引的，它的名字是 ROWID，如下图所示： test2 表虽然只有一列，但是 ROWID 列还是存在的。在程序中对任何一张表都可以使用 ROW ID 作为自增列。不过这样可能导致和其他数据库的不兼容，SQLite 中如果一个列的声明类 型是 Integer，并且是主键，那么这个列的名字就成为 ROWID 的别名。注意，声明类型必 须是 Integer，而不能是 int 或 bigint 之类。例如： 注意上面例子的最后 3 条语句，它显示了 SQLite 默认的自增列算法是在当前表中最大的数 再加 1，这样可能导致的结果是 ID 被重复使用当最后一条数据被删除的时候。这与 SQ L Server 的 Identity 列的行为是不一致的，例如： SQL Server 会记住每一次插入的序号，哪怕它已经被删除了。要实现 SQL Server 这样的 效果，需要使用 autoincrement 关键字。如下例所示： 不过 autoincrement 关键字不被 SQL Server 支持（我不知道 SQL 92 标准中是否有此关键 字），同样 SQL Server 的 indentity 关键字在 SQLite 中也无法使用，因为 SQLite 只要 求声明类型必须是 integer 才可以启用自增列。所以，我想不出什么方法能使建库的脚本 能够不加修改的被两种数据库使用。 (3) 日期函数 Sqlite 的日期函数比较有特色，它的使用本质上是调用 C 的库函数 strftime,基本使用方 法如下： (4) 不被支持的特性 用户自定义函数，存储过程 外键的约束（不过可以通过自定义触发器来替代） right out join , full out join grant revoke 1. 存储二进制数据 SQLite 提供的绑定二进制参数接口函数为: int sqlite3_bind_blob(sqlite3_stmt*, int, const void*, int n, void(*)(void*); 我们希望使用的是一套经过封装的 COM 接口，将上面这个函数封装为 COM 接口的形式 BindParaByIndex( LONG index, VARIANT val); 使用 VARIANT 变量来传递二进制数据，可以使用到它的一个 SAFEARRAY 指针，它保存了二进制 数据的地址和二进制数据的字节长度。 在我们的 COM 接口中可以这样进行调用原始接口： Sqlite3_bind_blob(m_pStmt,val.parray,val.parray-rsground- cElement,SQLITE_TRANSIENT); 构造一个例子测试我们的接口： BYTE Data = 0x01,0x02,0x03,0x04,0x05; CComSafeArray *pcsfa; CComSafeArrayBound bound1; bound0.SetCount(5); bound0.SetLowerBound(0); pcsfa = new CComSafeArray(bound,1); for(LONG i = 0; i SetAt(i,Datai); _variant_t variant; variant.vt = VT_ARRAY | VT_UI1; variant.parray = pcsfa-m_psa; 将五个字节的数据封装到 VARIANT 变量中，然后调用相应的接口，将它们存储到数据库中，然后调 用下面的读取二进制接口，将数据读取出来，看是否读取的数据和存储的数据一致. 2. 读取二进制数据 读取二进制参数需要用到下面两个 SQLite 提供的 API： const void *sqlite3_column_blob(sqlite3_stmt*, int iCol); int sqlite3_column_bytes(sqlite3_stmt*, int iCol); 访问也通过 COM 接口来实现： GetBlobData(LONG index, VARIANT* pval); 如何将原始接口读出来的数据封装到 VARIANT 结构中去呢，网上这方面的参考资料好少，差了不少 资料，发现网上有不上 SAFEARRAY 的实现方案，但是我一一试了一下没有一个可以将二进制数读 入 SAFEARRAY 结构的，Mentor 给我推荐了一个 CcomSafeArray 类，这个类成功实现了数据的 存储。 CComVariant cVal; int nLen = sqlite3_column_bytes(m_pStmt,nIndex); const void* pcvData = (const void*)sqlite3_column_blob(m_pStmt,nIndex); BYTE* pData = new BYTEnLen; memcpy(pData,pcvData,nLen); CComSafeArray *pcsfa; CComSafeArrayBound bound1; bound0.SetCount(nLen); bound0.SetLowerBound(0); pcsfa = new CComSafeArray(bound,1); for(LONG i = 0; i SetAt(i,pDatai); cVal = pcsfa-m_psa; cVal.vt = VT_ARRAY | VT_UI1; delete pData; cVal.Detach(pVal); OK,现在可以通过下面的代码来测试是否成功读取了所有的二进制数据。测试代码如下： _variant_t val; val = GetBlobData(nIndex); /nIndex 表示 BLOB 类型数据的索引值 byte buf5; if(val.vt = (VT_UI1|VT_ARRAY) for(LONG index = 0; index 准备语句（prepared statement）对象 typedef struct sqlite3_stmt sqlite3_stmt; 准备语句（prepared statement）对象一个代表一个简单 SQL 语句对象的实例，这个对 象通常被称为“准备语句”或者“编译好的 SQL 语句”或者就直接称为 “语句”。 语句对象的生命周期经历这样的过程： 使用 sqlite3_prepare_v2 或相关的函数创建这个对象 使用 sqlite3_bind_*()给宿主参数（host parameters）绑定值 通过调用 sqlite3_step 一次或多次来执行这个 sql 使用 sqlite3reset()重置这个语句，然后回到第 2 步，这个过程做 0 次或多次 使用 sqlite3_finalize()销毁这个对象 在 sqlite 中并没有定义 sqlite3_stmt 这个结构的具体内容，它只是一个抽象类型，在使 用过程中一般以它的指针进行操作，而 sqlite3_stmt 类型的指针在实际上是一个指向 Vdbe 的结构体得指针 宿主参数(host parameters) 在传给 sqlite3_prepare_v2()的 sql 的语句文本或者它的变量中，满足如下模板的文字将 被替换成一个参数： ? ?NNN，NNN 代表数字 :VVV，VVV 代表字符 VVV $VVV 在上面这些模板中，NNN 代表一个数字， VVV 代表一个字母数字标记符（例如:222 表 示名称为 222 的标记符），sql 语句中的参数（变量）通过上面的几个模板来指定，如 “select ? from ? “这个语句中指定了两个参数，sqlite 语句中的第一个参数的索引值是 1，这就知道这个语句中的两个参数的索引分别为 1 和 2，使用”?” 的话会被自动给予索引 值，而使用”?NNN”则可以自己指定参数的索引值，它表示这个参数的索引值为 NNN。”: VVV”表示一个名为”VVV” 的参数，它也有一个索引值，被自动指定。 可以使用 sqlite3_bind_*()来给这些参数绑定值 3 sqlite3_setp() 这个过程用于执行有前面 sqlite3_prepare 创建的准备语句。这个语句执行到结果的第 一行可用的位置。继续前进到结果的第二行的话，只需再次调用 sqlite3_setp()。继续调用 sqlite3_setp()知道这个语句完成，那些不返回结果的语句（如： INSERT，UPDATE，或 DELETE）， sqlite3_step()只执行一次就返回 函数定义 int sqlite3_step(sqlite3_stmt*); 返回值 函数的返回值基于创建 sqlite3_stmt 参数所使用的函数，假如是使用老版本的接口 sqlite3_prepare()和 sqlite3_prepare16()，返回值会是 SQLITE_BUSY， SQLITE_DONE， SQLITE_ROW， SQLITE_ERROR 或 SQLITE_MISUSE，而 v2 版本的接口 sqlite3_prepare_v2() 和 sqlite3_prepare16_v2()则会同时返回这些结果码和扩展结果码。 对所有 V 以及其前面的所有版本，需要在 sqlite3_step()之后调用 sqlite3_reset()， 在后续的 sqlite3_ step 之前。如果调用 sqlite3_reset 重置准备语句失败，将会导致 sqlite3_ step 返回 SQLITE_MISUSE，但是在 V3. 6.23.1 以后，sqlite3_step() 将会自动调用 sqlite3_reset。 int sqlite3_reset(sqlite3_stmt *pStmt); sqlite3_reset 用于重置一个准备语句对象到它的初始状态，然后准备被重新执行。所有 sql 语句变量使用 sqlite3_bind*绑定值，使用 sqlite3_clear_bindings 重设这些绑定。 Sqlite3_reset 接口重置准备语句到它代码开始的时候。sqlite3_reset 并不改变在准备语句上 的任何绑定值，那么这里猜测，可能是语句在被执行的过程中发生了其他的改变，然后这 个语句将它重置到绑定值的时候的那个状态。 4 sqlite3_column() 这个过程从执行 sqlite3_step()执行一个准备语句得到的结果集的当前行中返回一个列。 每次 sqlite3_step 得到一个结果集的列停下后，这个过程就可以被多次调用去查询这个行 的各列的值。对列操作是有多个函数，均以 sqlite3_column 为前缀 const void *sqlite3_column_blob(sqlite3_stmt*, int iCol); int sqlite3_column_bytes(sqlite3_stmt*, int iCol); int sqlite3_column_bytes16(sqlite3_stmt*, int iCol); double sqlite3_column_double(sqlite3_stmt*, int iCol); int sqlite3_column_int(sqlite3_stmt*, int iCol); sqlite3_int64 sqlite3_column_int64(sqlite3_stmt*, int iCol); const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol); const void *sqlite3_column_text16(sqlite3_stmt*, int iCol); int sqlite3_column_type(sqlite3_stmt*, int iCol); sqlite3_value *sqlite3_column_value(sqlite3_stmt*, int iCol); 说明 第一个参数为从 sqlite3_prepare 返回来的 prepared statement 对象的指针，第二参数 指定这一行中的想要被返回的列的索引。最左边的一列的索引号是 0，行的列数可以使用 sqlite3_colum_count()获得。 这些过程会根据情况去转换数值的类型，sqlite 内部使用 sqlite3_snprintf()去自动进行 这个转换，下面是关于转换的细节表： 内部类型 请求的类型 转换 NULL INTEGER 结果是 0 NULL FLOAT 结果是 0.0 NULL TEXT 结果是 NULL NULL BLOB 结果是 NULL INTEGER FLOAT 从整形转换到浮点型 INTEGER TEXT 整形的 ASCII 码显示 INTEGER BLOB 同上 FLOAT INTEGER 浮点型转换到整形 FLOAT TEXT 浮点型的 ASCII 显示 FLOAT BLOB 同上 TEXT INTEGER 使用 atoi() TEXT FLOAT 使用 atof() TEXT BLOB 没有转换 BLOB INTEGER 先到 TEXT，然后使用 atoi BLOB FLOAT 先到 TEXT，然后使用 atof BLOB TEXT 如果需要的话添加 0 终止符 注：BLOB 数据类型是指二进制的数据块，比如要在数据库中存放一张图片，这张图片就会 以二进制形式存放，在 sqlite 中对应的数据类型就是 BLOB int sqlite3_column_bytes(sqlite3_stmt*, int iCol)int sqlite3_column_bytes16(sqlite3_stmt*, int iCol)两个函数返回对应列的内容的字节数，这个字节数不包括后面类型转换过程中加上 的 0 终止符。 下面是几个最安全和最简单的使用策略 先 sqlite3_column_text() ，然后 sqlite3_column_bytes() 先 sqlite3_column_blob()，然后 sqlite3_column_bytes() 先 sqlite3_column_text16()，然后 sqlite3_column_bytes16() 5 sqlite3_finalize int sqlite3_finalize(sqlite3_stmt *pStmt); 这个过程销毁前面被 sqlite3_prepare 创建的准备语句，每个准备语句都必须使用这个 函数去销毁以防止内存泄露。 在空指针上调用这个函数没有什么影响，同时可以准备语句的生命周期的任一时刻调 用这个函数：在语句被执行前，一次或多次调用 sqlite_reset 之后，或者在 sqlite3_step 任 何调用之后不管语句是否完成执行 6 sqlite3_close 这个过程关闭前面使用 sqlite3_open 打开的数据库连接，任何与这个连接相关的准备 语句必须在调用这个关闭函数之前被释放 二使用举例 ? #include “stdafx.h“ #include “sqlite3.h“ static int callback(void *NotUsed, int argc, char *argv, char *azColName) int i; for(i=0; iargc; i+) printf(“%s = %s/n“, azColNamei, argvi ? argvi : “NULL“); printf(“/n“); return 0; #define CHECK_RC(rc,szInfo,szErrMsg,db) if(rc!=SQLITE_OK) / printf(“%s error!/n“,szInfo);/ printf(“%s/n“,szErrMsg); / sqlite3_free(szErrMsg); / sqlite3_close(db); / return 0; int _tmain(int argc, _TCHAR* argv) sqlite3 *db; char *dbPath=“f:/test.db“; char *szErrMsg = 0; int rc= sqlite3_open(dbPath, CHECK_RC(rc,“open database“,db); char *szSql=“create table UserInfo(ID int primary key , UserName char, PassWord char);“; rc=sqlite3_exec(db,szSql,0,0, CHECK_RC(rc,“create table“,szErrMsg,db); rc=sqlite3_exec(db,“insert into UserInfo(ID,UserName,PassWord) values(1,kfqcome,123456)“,0,0, CHECK_RC(rc,“insert info“,szErrMsg,db); rc=sqlite3_exec(db,“insert into UserInfo(ID,UserName,PassWord) values(2,miss wang,654321)“,0,0, CHECK_RC(rc,“insert info“,szErrMsg,db); szSql=“select * fr