概述

AS-SQLite是一个.NET Core库,因此这也是一个跨平台库。

AS-SQLite对System.Data.SQLite.Core进行了封装,可以让你轻松地操作SQLite数据库。

项目主页:https://github.com/ADD-SP/AS-SQLite

如果有疑问可以在本文评论区回复或者在项目中提issue。

效果对比

创建学生表

CREATE TABLE Student (
    ID          TEXT,
    Name        TEXT,
    IsGraduated BOOLEAN,
    CONSTRAINT PrimaryKey PRIMARY KEY (
        ID
    )
    ON CONFLICT ABORT
);

使用System.Data.SQLite.Core

SQLiteConnection connection = new SQLiteConnection();
connection.ConnectionString = "Data Source=database.sqlite;Version=3;";
connection.Open();
SQLiteCommand command = connection.CreateCommand();
command.CommandText = "CREATE TABLE Student (" + 
    "ID          TEXT," + 
    "Name        TEXT," + 
    "IsGraduated BOOLEAN," + 
    "CONSTRAINT PrimaryKey PRIMARY KEY (" + 
    "    ID" + 
    ")" + 
    "ON CONFLICT ABORT" + 
    ");";
_ = command.ExecuteNonQuery();
connect.Close();

使用AS-SQLite

定义学生表的结构:

class Student : DataBaseTable
{
    public string ID { get; set; }
    public string Name { get; set; }
    public bool IsGraduated { get; set; }
}

创建表

SQLiteOperation operation = new SQLiteOperation("database.sqlite");
operation.OpenConnection();
Student student = new Student();
// 创建一个表级约束
TableConstraint constraint = new TableConstraint();
// 约束名称
constraint.Name = "PrimaryKey";
// 约束类型
constraint.Type = ConstraintType.PrimaryKey;
// 当违反约束时的操作
constraint.OnConflict = ConflictOperation.Abort;
// 添加被约束的列
constraint.Columns.Add(Student.GetColumn("ID"));
// 将约束添加到表中
Student.AddConstraint(constraint);
// 创建表
operation.ExecCommand(out var _, student.GetCreateCommand());
operation.CloseConnection();

查询学生表

查询所有已经毕业的学生的全部信息

使用System.Data.SQLite.Core

connection.Open();
SQLiteCommand command = connection.CreateCommand();
command.CommandText = "SELECT * FROM Student WHERE IsGraduated=false;"
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read()) {
  Console.WriteLine("ID: " + reader["ID"].ToString() +"\tName: " + reader["Name"].ToString();
}
connect.Close();

使用AS-SQLite

operation.OpenConnection();
var rows = from row in operation.Select<Student>(student.CreateSelectCommand())
                where (bool)row["IsGraduated"] == false
                select row;
foreach (var item in rows) {
   Console.WriteLine("ID: " + item["ID"] +"\tName: " + item["Name"]);
}

使用指南

注意事项:本库中表名和列名均大小写敏感,请注意。

文档中所有的例子均以下列三张表为基础

CREATE TABLE Student (
    ID          TEXT,
    Name        TEXT,
    IsGraduated BOOLEAN,
    CONSTRAINT PrimaryKey PRIMARY KEY (
        ID
    )
    ON CONFLICT ABORT
);

CREATE TABLE Course (
    ID   TEXT,
    Name TEXT,
    CONSTRAINT PrimaryKey PRIMARY KEY (
        ID
    )
    ON CONFLICT ABORT
);

CREATE TABLE SC (
    StudentID TEXT,
    CourseID  TEXT,
    Score     INTERGER,
    CONSTRAINT PrimaryKey PRIMARY KEY (
        StudentID,
        CourseID
    )
    ON CONFLICT ABORT,
    CONSTRAINT ForeignKeyStudent FOREIGN KEY (
        StudentID
    )
    REFERENCES Student (ID) ON UPDATE CASCADE
                            ON DELETE CASCADE,
    CONSTRAINT ForeignKeyCourse FOREIGN KEY (
        CourseID
    )
    REFERENCES Course (ID) ON UPDATE SET NULL
                           ON DELETE SET DEFAULT
);

创建数据库表

定义表结构

class Course : DataBaseTable {
    public string ID { get; set; }
    public string Name { get; set; }
}

class Student : DataBaseTable {
    public string ID { get; set; }
    public string Name { get; set; }
    public bool IsGraduated { get; set; }
}

class SC : DataBaseTable {
      public string StudentID { get; set; }
    public string CourseID { get; set; }
    public int Score { get; set; }
}
  • DataBaseTable是本库中定义的一个基类,所有的表都要继承这个类。

添加表级约束

  • TableConstraint是本库中定义的一个类,可以定义大多数表级约束:
    • 主键约束
    • 外键约束
    • 唯一约束
    • 非空约束
    • 违反约束时的操作

添加主键约束

Student表为例:

  • 约束名:PrimaryKey
  • 约束类型:主键约束
  • 被约束列:ID
  • 违反约束时的操作:Abort
TableConstraint constraint = new TableConstraint();
// 约束名称
constraint.Name = "PrimaryKey";
// 约束类型
constraint.Type = ConstraintType.PrimaryKey;
// 当违反约束时的操作
constraint.OnConflict = ConflictOperation.Abort;
// 添加被约束的列
constraint.Columns.Add(student.GetColumn("ID"));
// 添加约束
student.AddConstraint(constraint);

定义非空约束/唯一约束

唯一约束的定义方式和非空约束类似。

Student表为例:

  • 约束名:NotNull
  • 约束类型:非空约束
  • 被约束列:Name
  • 违反约束时的操作:Rollback
TableConstraint constraint = new TableConstraint();
// 约束名称
constraint.Name = "NotNull";
// 约束类型
constraint.Type = ConstraintType.NotNull;
// 当违反约束时的操作
constraint.OnConflict = ConflictOperation.Rollback;
// 添加被约束的列
constraint.Columns.Add(student.GetColumn("Name"));
// 添加约束
student.AddConstraint(constraint);

定义外键约束:

SC表为例:

  • 约束名:ForeignKeyStudent
  • 约束类型:外键约束
  • 被约束列:StudentID
  • 参照表:Student
  • 参照列:ID
  • 更新时的操作:Cascade
  • 删除时的操作:Cascade
TableConstraint constraint = new TableConstraint();
constraint.Name = "ForeignKeyStudent";
constraint.Type = ConstraintType.ForeignKey;
constraint.Columns.Add(SC.GetColumn("StudentID"));
// 当外键更新时的操作
constraint.OnUpdate = UpdateOperation.Cascade;
// 当外键删除时的操作
constraint.OnDelete = DeleteOperation.Cascade;
constraint.RefDataBaseTable = Student;
constraint.RefColumn = Student.GetColumn("ID");
sc.AddConstraint(constraint);

创建表:

以学生表为例

// 操作路径为“database.sqlite”的数据库
SQLiteOperation operation = new SQLiteOperation("database.sqlite");
// 打开数据库连接
operation.OpenConnection();
// 获取表创建语句
string command = student.GetCreateCommand();
// 执行语句
operation.ExecCommand(out Exception exception, command);
// 关闭连接
operation.CloseConnection();

插入数据

数据转换规则

C#中的数据类型会按照下表转化为数据库支持的类型

C#数据类型 数据库数据类型
string TEXT
bool BOOLEAN
int interger
double DOUBLE

向Student表中插入如下数据

ID Name IsGraduated
000111 小明 false
000333 小李 true
Dictionary<string, object> keyValues = new Dictionary<string, object>();
keyValues["ID"] = "000111";
keyValues["Name"] = "小明";
keyValues["IsGraduated"] = false;
string command = student.GetInsertCommand(keyValues);
bool result = Operation.ExecCommand(out Exception exception, command);

keyValues.Clear();
keyValues["ID"] = "000333";
keyValues["Name"] = "小李";
keyValues["IsGraduated"] = true;
string command = student.GetInsertCommand(keyValues);
result &= Operation.ExecCommand(out Exception exception, command);

查询数据

单表查询

查询学生表中所有已经毕业的学生

var query = from row in operation.Select<Student>(student.GetSelectCommand())
                where (bool)row["IsGraduated"] == true
                select row;

foreach (var item in query)
{
    Console.WriteLine(string.Format("ID: {0}\tName: {1}", item["ID"], item["Name"]));
}

查询学生表中所有叫小明且没有毕业的学生

var query = from row in operation.Select<Student>(student.GetSelectCommand())
                where (bool)row["IsGraduated"] == false && (row["Name"] as string) == "小明"
                select row;

foreach (var item in query)
{
    Console.WriteLine(string.Format("ID: {0}\tName: {1}", item["ID"], item["Name"]));
}

联合查询

查询选修了至少一门课程的学生的学号和姓名

var query = from row in Operation.Select<Student, Course, SC>(student.GetUnionSelectCommand(course, sc))
            where (row["Student"] as TableRow<Student>)["ID"].ToString() == (row["SC"] as TableRow<SC>)["StudentID"].ToString()
                && (row["Course"] as TableRow<Course>)["ID"].ToString() == (row["SC"] as TableRow<SC>)["CourseID"].ToString()
            select row;

foreach (var row in query)
{
    Console.WriteLine(string.Format(
        "ID: {0}\tName: {1}",
        (row["Student"] as TableRow<Student>)["ID"],
        (row["Student"] as TableRow<Student>)["Name"]));
}

子查询

暂不支持

对于本库还不支持的一些增删改查的操作可以用下列方法代替

SQLiteOperation下的

public SQLiteDataReader NativeExecCommand(string commandString);

派生查询

暂不支持

对于本库还不支持的一些增删改查的操作可以用下列方法代替

SQLiteOperation下的

public SQLiteDataReader NativeExecCommand(string commandString);

更新数据

修改学号为000111学生的名字为小张

string command = student.CreateUpdateCommand("Name", "ID=\'000111\'", "小张");
bool result = operation.ExecCommand(out Exception exception, command);

删除数据

删除姓名为小张的学生的数据

string command = student.CreateDeleteCommand("ID=\'000111\'");
bool result = operation.ExecCommand(out Exception exception, command);

删除表

删除学生表

string command = student.CreateDropCommand();
bool result = operation.ExecCommand(out Exception exception, command);

公开类型说明

主要介绍库中公开的各种类的功能,属性和方法说明。可以在代码注释中查看部分信息。

未完待续……