Asp.net core 少走弯路系列教程（八）数据库 CRUD 增删改查学习

前言

新人学习成本很高，网络上太多的名词和框架，全部学习会浪费大量的时间和精力。

新手缺乏学习内容的辨别能力，本系列文章为新手过滤掉不适合的学习内容（比如多线程等等），让新手少走弯路直通罗马。

作者认为新人应该先打好基础，不要直接学习框架，例如先掌握 SQL 再使用 EFCore 框架。

作者只传授数年内不会变化的知识，让新手学习快速进入跑道受益终身。

分享使我快乐，请务必转发给同学，朋友，让大家都少走一些弯路！！

本文主要讲解关系型数据库的使用，学会常用的 SQL 语句，了解 SQL 注入，以及如何在 Asp.net core WebApi 中使用进行 CRUD 增删改查。

关系型数据库主流有的：mysql、postgresql、sqlserver、oracle 等等，为了方便学习本文主要以 SQLite 本地数据库以例，其他数据库的使用方法基本相似。

如何使用 SQLite 数据库

安装数据库管理工具：Navicat Permium

创建好数据库后，双击左侧 test.db 即可打开。

右击左侧 Tables -> New Table 创建新表。

每个表必须设置一个主键字段（唯一），编辑好字段，以及类型之后， ctrl + s 保存表。

点击如何按钮创建 SQL 运行窗口：

输入 SELECT 'hello world' 执行：

SQL 运算符

算术运算符：+ - * / %
比较运算符：= != <> > < >= <=
逻辑运算符：AND OR

SQL Insert 语句

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)
VALUES (value1, value2, value3,...valueN)

自增列不需要指定插入，如上面创建好的表 table01：

INSERT INTO table01 (name)
VALUES ('name01')；

SELECT last_insert_rowid();

SELECT last_insert_rowid() 返回插入的 id 值。

批量插入：

INSERT INTO table01 (name)
VALUES ('name01'), ('name02'), ('name03')

使用一个表来填充另一个表：

INSERT INTO table01 (column1, column2, ... columnN)
   SELECT column1, column2, ...columnN 
   FROM table02
   [WHERE condition]

SQL Select 语句的基本语法如下：

SELECT column1, column2, columnN FROM table_name

在这里，column1, column2...是表的字段，他们的值即是您要获取的。如果您想获取所有可用的字段，那么可以使用下面的语法：

SELECT * FROM table_name

排序：

SELECT * FROM table_name ORDER BY column_1, column_2 DESC

返回前 10 条记录：

SELECT * FROM table_name LIMIT 10

返回第 10-20 条记录：

SELECT * FROM table_name LIMIT 10 OFFSET 10

SQL Where 子句

WHERE 子句用于指定从一个表或多个表中获取数据的条件。如果满足给定的条件，即为真（true）时，则从表中返回特定的值。您可以使用 WHERE 子句来过滤记录，只获取需要的记录。

WHERE 子句不仅可用在 SELECT 语句中，它也可用在 UPDATE、DELETE 语句中。

SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;

SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;

SELECT * FROM COMPANY WHERE AGE IS NULL;

SELECT * FROM COMPANY WHERE AGE IS NOT NULL;

SELECT * FROM COMPANY WHERE NAME LIKE 'Ki%';

SELECT * FROM COMPANY WHERE AGE IN (25, 27);

SELECT * FROM COMPANY WHERE AGE NOT IN (25, 27);

SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27;

SELECT AGE FROM COMPANY WHERE EXISTS(SELECT AGE FROM COMPANY WHERE SALARY > 65000);

SELECT * FROM COMPANY WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000 LIMIT 1);

GROUP BY 子句用于与 SELECT 语句一起使用，来对相同的数据进行分组。在 SELECT 语句中，GROUP BY 子句放在 WHERE 子句之后，放在 ORDER BY 子句之前。

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0
8           Paul        24          Houston     20000.0
9           James       44          Norway      5000.0
10          James       45          Texas       5000.0

SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME

产生以下结果：

NAME        SUM(SALARY)
----------  -----------
Allen       15000
David       85000
James       20000
Kim         45000
Mark        65000
Paul        40000
Teddy       20000

HAVING 子句允许指定条件来过滤将出现在最终结果中的分组结果。WHERE 子句在所选列上设置条件，而 HAVING 子句则在由 GROUP BY 子句创建的分组上设置条件。

SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY name HAVING count(name) > 2

产生以下结果：

NAME        SUM(SALARY)
----------  -----------
James       20000

DISTINCT 关键字与 SELECT 语句一起使用，来消除所有重复的记录，并只获取唯一一次记录。

SELECT DISTINCT name FROM COMPANY

产生以下结果：

NAME
----------
Paul
Allen
Teddy
Mark
David
Kim
James

SQL Join 语句

Join 子句用于结合两个或多个数据库中表的记录，比如常用的 LEFT JOIN、INNER JOIN、RIGHT JOIN

SELECT a.*, b.* FROM table01 a
INNER JOIN table02 b ON b.id = a.id
WHERE a.id > 0

SQL Update 语句

UPDATE COMPANY SET ADDRESS = 'Texas', SALARY = 20000.00 WHERE ID = 6;

SQL Delete 语句

DELETE FROM COMPANY WHERE ID = 7;

在 Asp.net core WebApi 中使用 SQLite

需要了解 ado.net 几大对象，任何数据库都必须实现它们：

DbConnection：数据库连接对象
DbCommand：数据库SQL命令对象
DbParameter：SQL命令参数对象

在我们上一篇文章的项目里安装 sqlite ado.net 类库：

编写 Insert_table01、Select_table01 两个 HTTP 接口代码：

[HttpPost("Insert_table01")]
public int Insert(string name)
{
    using (var conn = new SqliteConnection("data source=d:/code/test01.db"))
    {
        conn.Open();
        var cmd = conn.CreateCommand();
        //cmd.CommandText = $"insert into table01(name) values('{name}'); select last_insert_rowid();";
        //注意：上面 SQL 注入漏洞，解决办法使用 SQL 参数化对象
        cmd.CommandText = $"insert into table01(name) values(@name); select last_insert_rowid();";
        cmd.Parameters.AddWithValue("@name", name);
        var id = int.Parse(cmd.ExecuteScalar().ToString());
        return id;
    }
}

[HttpPost("Select_table01")]
public List<object> Select()
{
    var list = new List<object>();
    using (var conn = new SqliteConnection("data source=d:/code/test01.db"))
    {
        conn.Open();
        var cmd = conn.CreateCommand();
        cmd.CommandText = $"select * from table01";
        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                list.Add(new { id = reader.GetInt32(0), name = reader.GetString(1) });
            }
        }
    }
    return list;
}

实际项目中，大可不必使用上述代码，但是必须要了解他们，DbCommand 常用的执行命令方法：

ExecuteScalar 返回第一行第一列（一般用于 INSERT）
ExecuteReader 返回记录读取对象（一般用于 SELECT）
ExecuteNonQuery 返回执行SQL受影响的行（一般用于 UPDATE/DELETE）

使用 ORM 操作 SQLite 数据库

这里介绍作者的开源作者 FreeSql，它是一个支持 .NETFramework 4.0+ 之后所有 dotnet 版本的 ORM（包括 .net core），支持十几种数据库。

这里为什么要介绍 FreeSql，出于私心，也出于爱心，虽然 EFCore 官方 ORM 好用，但还是会有部分人吐槽，主要原因：

版本更新节凑太快，前后不兼容问题
只对自家 sqlserver 支持比较友好，社区 mysql、postgresql 也支持得不错
对国产数据库支持不友好

作者在写本系列的一个愿望，是希望新手少走弯路直通罗马，这是出于爱心。作者不反对学习 EFCore，如果有条件等有了一些基础再学习更好。

使用 FreeSql 需要先安装对应的 nuget 包，因为我们操作的是 Sqlite 数据库，所以只需要安装 FreeSql.Provider.Sqlite ：

在 Program.cs 中以 单例注入 IFreeSql 对象：

using FreeSql;
using FreeSql.Internal;
using System.Diagnostics;

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddControllers();
builder.Services.AddSwaggerGen();
builder.Services.AddSingleton(provider =>
{
    var fsql = new FreeSqlBuilder()
        .UseConnectionString(DataType.Sqlite, "data source=d:/code/test01.db")
        .UseNameConvert(NameConvertType.ToLower) //实体类 -> 表（小写）
        .UseNoneCommandParameter(true)
        .UseAutoSyncStructure(true) //CRUD 时自动创建表
        .UseMonitorCommand(cmd => Console.WriteLine(cmd.CommandText + "\r\n"))
        .Build();
    return fsql;
});

var app = builder.Build();

编辑实体体 Table02：

using FreeSql.DataAnnotations;

public class Table02
{
    [Column(IsPrimary = true, IsIdentity = true)]
    public int Id { get; set; }
    public string Name { get; set; }
}

编写 Insert_table02、Select_table02 两个 HTTP 接口代码：

readonly IFreeSql _fsql;
public WeatherForecastController(IFreeSql fsql)
{
    _fsql = fsql;
}

[HttpPost("Insert_table02")]
public long Insert2(string name)
{
    var id = _fsql.Insert(new Table02 { Name = name }).ExecuteIdentity();
    return id;
}

[HttpPost("Select_table02")]
public List<Table02> Select2()
{
    return _fsql.Select<Table02>().ToList();
}

F5 运行使用 Swagger 测试效果如下：