65.9K
CodeProject 正在变化。 阅读更多。
Home

SQLite 助手 (C#)

adriancs
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.79/5 (80投票s)

2014年3月19日

公共领域

5分钟阅读

viewsIcon

257582

downloadIcon

13109

简化 C# 和 SQLite 之间的使用。

引言

SQLite 是一个开源的、嵌入式的、跨平台（Windows、iOS、Android、Linux）的数据库引擎。它无需安装，并且在客户端计算机上使用时无需任何配置。

我写了一个小类，名为 SQLiteHelper，旨在简化 C# 中 SQLite 的使用。

先决条件

这个小类 (SQLiteHelper.cs) 构建在 System.Data.SQLite.DLL 之上。必须将此 DLL 添加到您的项目中作为引用。

下载： https://system.data.sqlite.org

更改日志

2014 年 3 月 27 日 - V1.2

  • 为 "Select"、"Execute" 和 "ExecuteScalar" 方法添加了参数支持。

2014 年 3 月 22 日 - V1.1

  • 新增：参数化 SQL 执行支持
  • 新方法：更新表结构
  • 新方法：LastInsertRowId
  • 新方法：GetTableList
  • 新方法：ShowDatabase
  • 新方法：AttachDatabase, DetachDatabase
  • 修改：ExecuteScalar 应用泛型。
  • 演示应用已更新

简化函数列表

  1. GetTableStatus
  2. GetTableList
  3. GetColumnStatus
  4. CreateTable
  5. UpdateTableStructure
  6. BeginTransaction, Commit, Rollback
  7. Select
  8. Execute
  9. ExecuteScalar
  10. 转义
  11. Insert
  12. 更新
  13. LastInsertRowId
  14. RenameTable
  15. CopyAllData
  16. DropTable
  17. ShowDatabase
  18. AttachDatabase, DetachDatabase

开始使用

请在类的顶部添加此 using 语句

using System.Data.SQLite;

在使用 SQLiteHelper 之前，必须初始化 SQLiteConnection 和 SQLiteCommand。

示例

using (SQLiteConnection conn = new SQLiteConnection("data source=C:\\data"))
{
    using (SQLiteCommand cmd = new SQLiteCommand())
    {
        cmd.Connection = conn;
        conn.Open();
 
        SQLiteHelper sh = new SQLiteHelper(cmd);
 
        // do something...

        conn.Close();
    }
}

1. GetTableStatus

获取数据库中所有表的全部信息。

DataTable dt = sh.GetTableStatus();

示例结果

type 名称 tbl_name rootpage sql
table sqlite_sequence sqlite_sequence 3 CREATE TABLE sqlite_sequence(name,seq)
table person2 person2 5 CREATE TABLE "person2"(
id integer primary key autoincrement,
name text,
tel text,
email text,
job text,
remarks text)
table player player 4 CREATE TABLE `player`(
id integer primary key autoincrement,
lvl integer,
weaponid integer,
teamid integer,
location text,
team_name text,
remarks text)
table product product 6 CREATE TABLE "product"(
id integer primary key autoincrement,
name text,
qty integer)

2. GetTableList

获取数据库中表的列表。

DataTable dt = sh.GetTableList();

3. GetColumnStatus

获取特定表中所有列的信息。

// Get column's information from table "person"
DataTable dt = sh.GetColumnStatus("person");

示例结果

cid 名称 type notnull dflt_value pk
0 id 整数 0   1
1 lvl 整数 0   0
2 weaponid 整数 0   0
3 teamid 整数 0   0
4 location 文本 0   0
5 team_name 文本 0   0
6 remarks 文本 0   0

4. CreateTable

创建表。

示例表结构：Person

列名 数据类型 主键 自动递增 非空 默认值
id int true true    
名称 文本        
membershipid int        
level decimal       5.5 
SQLiteTable tb = new SQLiteTable("person");
 
tb.Columns.Add(new SQLiteColumn("id", true));
tb.Columns.Add(new SQLiteColumn("name"));
tb.Columns.Add(new SQLiteColumn("membershipid", ColType.Integer));
tb.Columns.Add(new SQLiteColumn("level", ColType.Decimal, false, false, "5.5"));
 
sh.CreateTable(tb);

5. UpdateTableStructure

顾名思义，它用于更新表的结构。您可能添加了新列，或删除了某些列。此方法可帮助您进行更新。

后台代码中的过程

  • 假设旧表名为：person
  • 该类创建一个临时表（名为：person_temp），具有您定义的新结构。
  • 将 person 的所有行复制到 person_temp。
  • 删除 person 表。
  • 将 person_temp 表重命名为 person。

代码示例

SQLiteTable tb = new SQLiteTable();
tb.Columns.Add(new SQLiteColumn("id", true));
tb.Columns.Add(new SQLiteColumn("name"));
tb.Columns.Add(new SQLiteColumn("sku"));
tb.Columns.Add(new SQLiteColumn("code"));
tb.Columns.Add(new SQLiteColumn("category"));
tb.Columns.Add(new SQLiteColumn("remarks"));

sh.UpdateTableStructure("person", tb);

6. BeginTransaction, Commit, Rollback

什么是事务？

默认情况下，发送到 SQLite 数据库引擎的每个 SQL 查询都在事务中进行。引擎会自动 BEGIN 一个事务，并在结束时 COMMIT。COMMIT 类似于“使其生效”。

如果我们发送 3 个 SQL 查询（INSERT、UPDATE、DELETE 等），则会发生 3 个事务。根据 [SQLite 官方文档 - 常见问题解答]

"...一个事务通常需要两次完整的磁盘旋转，在 7200RPM 的磁盘驱动器上，每秒最多只能处理约 60 个事务..."

这意味着，对于 7200RPM 的硬盘，我们每秒最多可以执行 60 次 INSERT（或 UPDATE、DELETE 等）。

但是，如果我们手动发出 BEGIN TRANSACTION，所有查询都将被包装在单个事务中，然后 SQLite 可以每秒执行大量查询。有人提到他每秒可以执行 1000 万次查询，在 [stackoverflow.com]，但这同样取决于您使用的硬盘速度。

使用 SQLiteHelper 的代码示例

sh.BeginTransaction();
 
try
{
    // INSERT.....
    // INSERT.....
    // UPDATE....
    // ... skip for another 50,000 queries....
    // DELETE....
    // UPDATE...
    // INSERT.....

    sh.Commit();
}
catch
{
    sh.Rollback();
}

ROLLBACK 在上面的示例中表示取消事务。在该特定事务中发送到 SQLite 数据库的所有查询都将被取消。

7. Select

以 DataTable 格式返回查询结果。

  • Select(string sql)
  • Select(string sql, Dictionary<string, object> dicParameters = null)
  • Select(string sql, IEnumerable<SQLiteParameter> parameters = null)

示例 1

DataTable dt = sh.Select("select * from person order by id;");

示例 2（支持参数）

var dic = new Dictionarystring, object();
dic["@aaa"] = 1;
dic["@bbb"] = 1;
DataTable dt = sh.Select("select * from member where membershipid = @aaa and locationid = @bbb;", dic);

示例 3（支持参数）

DataTable dt = sh.Select("select * from member where membershipid = @aaa and locationid = @bbb;",
    new SQLiteParameter[] { 
        new SQLiteParameter("@aaa", 1),
        new SQLiteParameter("@bbb", 1)
    });

8. Execute

执行单个 SQL 查询。

  • Execute(string sql)
  • Execute(string sql, Dictionary<string, object> dicParameters = null)
  • Execute(string sql, IEnumerable<SQLiteParameter> parameters = null)

示例

sh.Execute("insert into person(name)values('hello');");

9. ExecuteScalar

以特定数据类型返回第一行第一列的结果。

  • ExecuteScalar(string sql)
  • ExecuteScalar(string sql, Dictionary<string, object> dicParameters = null)
  • ExecuteScalar(string sql, IEnumerable<SQLiteParameter> parameters = null)
  • ExecuteScalar<datatype>(string sql)
  • ExecuteScalar<datatype>(string sql, Dictionary<string, object> dicParameters = null)
  • ExecuteScalar<datatype>(string sql, IEnumerable<SQLiteParameter> parameters = null)

示例

string a = sh.ExecuteScalar<string>("select 'Hello!';");

int b = sh.ExecuteScalar<int>("select 1000;");

decimal c = sh.ExecuteScalar<decimal>("select 4.4;");

DateTime d = sh.ExecuteScalar<DateTime>("select date('now');");

byte[] e = sh.ExecuteScalar<byte[]>("select randomblob(16);");

10. Escape

转义文本值的字符串序列，以避免 SQL 注入或生成无效的 SQL 语法。

sh.Execute("insert into person(name) values('" + Escape(input) + "');");

11. Insert

插入新行数据。所有数据都将作为参数在后台添加。此方法也支持 blob (byte[]) 值。

var dic = new Dictionary<string, object>();
dic["name"] = "John";
dic["membershipid"] = 1;
dic["level"] = 6.8;
 
sh.Insert("person", dic);

12. Update

更新行。所有数据都将作为参数在后台添加。此方法也支持 blob (byte[]) 值。

示例 1：带单个条件更新（where id = 1）

var dicData = new Dictionary<string, object>();
dicData["name"] = "no name";
dicData["membershipid"] = 0;
dicData["level"] = 5.5;
 
sh.Update("person", dicData, "id", 1);

示例 2：带多个条件更新（where membership = 1 and level = 5.5 and teamid = 1）

var dicData = new Dictionary<string, object>();
dicData["name"] = "no name";
dicData["status"] = 0;
dicData["money"] = 100;
dicData["dateregister"] = DateTime.MinValue;
 
var dicCondition = new Dictionary<string, object>();
dicCondition["membershipid"] = 1;
dicCondition["level"] = 5.5;
dicCondition["teamid"] = 1;
 
sh.Update("person", dicData, dicCondition);

13. LastInsertRowId

获取最后生成的 ID（自动递增）。

sh.Insert("person", dicData);
long id = sh.LastInsertRowId();

14. RenameTable

重命名表。

sh.RenameTable("person", "person_backup");

15. CopyAllData

将所有数据从一个表复制到另一个表。

sh.CopyAllData("person", "person_new");

复制之前，SQLiteHelper 将扫描两个表以匹配列。只有两个表中都存在的列才会被复制。

16. DropTable

删除表，即删除一个表。

sh.DropTable("person");

17. ShowDatabase

显示已附加的数据库。

DataTable dt = sh.ShowDatabase();

18. AttachDatabase, DetachDatabase

附加或分离数据库。

sh.AttachDatabase("C:\\data2013.sq3", "lastyeardb");
sb.DetachDatabase("lastyeardb");

好了，各位！欢迎评论。

祝您编码愉快 微笑 | :)

替代方案

最后，我将介绍其他您在开发 C#、VB.NET 和 SQLite 应用程序时可能考虑使用的工具。

1. SQLite.NET

SQLite.NET 旨在让 .NET 环境中的 SQLite 操作非常容易。它是一个开源的、极简的库，允许 .NET 和 Mono 应用程序在 [http://www.sqlite.org SQLite 3 数据库] 中存储数据。它用 C# 编写，并且可以简单地编译到您的项目中。它最初是为了与 iPhone 上的 MonoTouch 配合使用而设计的，但后来发展到支持所有平台（Mono for Android、.NET、Silverlight、WP7、WinRT、Azure 等）。

2. System.Data.SQLite.EF6

历史

  • 2014 年 3 月 27 日 - V1.2 发布
  • 2014 年 3 月 22 日 - V1.1 发布
  • 2014 年 3 月 19 日 - 初步工作
© . All rights reserved.