.Net Core 中无 ORM 的数据访问

如今，对象关系映射库 (ORM) 的使用非常普遍，很少有人质疑它们的用途。

这是有充分理由的。在过去，你会看到 SQL 代码随处可见。经常会发现用户输入直接与 SQL 语句连接在一起，从而为 SQL 注入攻击打开了方便之门（比如Bobby Tables）。

尽管使用 ORM 有很多好处，但也有一些不太好的地方。首先是性能，它比较差（有时甚至差很多）。

除了性能之外，还有一些其他问题，虽然它们算不上缺点，但会对 ORM 的使用体验产生负面影响。这些问题都与 ORM 隐藏了大量关于如何检索和保存数据的细节有关。人们常常因为没有意识到这些细节而自食其果。

仅举几个例子，即使这可能不是一个好主意（例如 Web 应用程序）也会使用延迟加载，或者由于触发数据获取的机制（这里特别考虑实体框架）有时并不明显而导致的 N+1 问题。

我并非主张不要使用 ORM，完全不是。只是我的看法是，如今大多数在 .NET 生态系统中工作的人如果不使用 Entity Framework，就无法在数据库中检索和创建记录。

这很遗憾，因为使用原生框架并不难，而且可能比设置 Entity Framework 更快。我一直在一些小型项目中采用这种方法，并且我确信不用 Entity Framework 比用 Entity Framework 更快地完成工作（设置 EF 可能很麻烦）。

这篇博文的目的是向你展示如何使用.Net Core 中提供的“原始”数据访问机制（ADO.NET），以及 Entity Framework 的替代方案 Dapper（Stack Overflow 也使用 Dapper）。Dapper 有时被描述为 ORM，但正如我们将看到的，它更像是一个“对象映射器”。

在 .NET Core 中使用 ADO.NET 进行 CRUD

要在不使用 Entity Framework Core 的情况下进行数据访问，您只需掌握三个概念。这三个概念是连接、命令和数据读取器。

Connection对象表示与数据库的连接。您必须使用与要交互的数据库对应的特定连接对象。例如，对于PostgreSQL ，我们会使用；对于 MySql ，我们会使用；对于 SQL Server ，我们会使用。您懂的。所有这些连接类型都实现了IDbConnection接口。NpgsqlConnectionMysqlConnectionSqlConnection

您需要为要使用的数据库安装正确的 Nuget 包，例如对于 Postgres，包名称很简单：（Npgsql一种简单的记忆方法是，N - 代表 .Net，pgsql - 代表 PostGreSQL）。

要创建连接对象，我们需要一个连接到想要交互的数据库的连接字符串。例如，对于 Postgres 中名为“example”、用户为“johnDoe”的数据库，我们可以这样创建连接：

var connection = new NpgsqlConnection("User ID=johnDoe;Password=thePassword;Host=localhost;Database=example;Port=5432");

有关如何创建这些连接字符串的信息，一个很好的资源是https://www.connectionstrings.com/。

创建连接对象后，要真正连接到数据库，我们需要调用Open它：

connection.Open();

连接对象都是IDisposable，所以你应该丢弃它们。因此，通常在 using 块内创建连接：

using (var connection = new NpgsqlConnection("User ID=johnDoe;Password=thePassword;Host=localhost;Database=example;Port=5432"))
{
    connection.Open();
    //use the connection here
}

这就是您开始所需要的一切。

创建记录

要创建记录，我们需要使用命令。命令是数据库中执行操作所需的所有内容的容器。

创建命令的最简单方法是向连接对象询问一个命令：

using(var command = connection.CreateCommand()) 
{
    //use command here
}

然后，通过属性指定要执行的 SQL CommandText，并在属性中指定 SQL 中参数的值Parameters。例如，如果要向people名为的表中添加一条记录first_name，其列为last_name，age则应如下所示：

command.CommandText = "insert into people (first_name, last_name, age) values (@firstName, @lastName, @age)";
command.Parameters.AddWithValue("@firstName", "John");
command.Parameters.AddWithValue("@lastName", "Doe");
command.Parameters.AddWithValue("@age", 38);

您应该使用参数，因为这可以防止 SQL 注入攻击。如果您不这样做，而是使用字符串连接的方式，将用户输入的数据转换为 SQL 语句，那么用户输入的内容就会被解释为 SQL 语句。

命令的“执行”方式取决于您期望的结果。例如，如果您添加一条记录，并且不关心任何自动生成的列值（例如新记录的 ID），则可以执行以下操作：

int numberOfUpdatedRows = command.ExecuteNonQuery();

此方法返回已更新/创建的行数。虽然它在插入语句中用处不大，但在更新语句中，这个值可能很有用。

或者，如果您想插入并获取新记录的 ID，您可以更改插入语句的 SQL，以便返回新的 ID。具体操作取决于您使用的数据库，例如，在 Postgres 中，SQL 代码如下insert into people (first_name, last_name, age) values (@firstName, @lastName, @age) returning id。

在 SQL 服务器中它看起来像这样insert into people (first_name, last_name, age) values (@firstName, @lastName, @age); select scope_identity()。

要运行插入并获取新的 ID，您可以使用ExecuteScalar命令中的方法。

该ExecuteScalar方法执行 SQL 并返回第一行第一列的值（作为类型对象），例如在 postgres 中：

command.CommandText = "insert into people (first_name, last_name, age) values (@firstName, @lastName, @age) returning id";
command.Parameters.AddWithValue("@firstName", "Jane");
command.Parameters.AddWithValue("@lastName", "Doe");
command.Parameters.AddWithValue("@age", 37);

var newId = (int)command.ExecuteScalar();

您现在可能会想，这些 SQL 语句需要大量输入。而且，所有这些都没有智能感知。幸好有一些技巧可以解决这个问题。您可以观看我如何从头开始创建一条插入语句，而无需手动输入任何列名。

读

要读取数据，您只需编写 SQL 查询并调用ExecuteReader命令对象中的方法即可。这将返回一个实例，DataReader然后您可以使用该实例检索查询的实际结果。

例如，如果我们想检索people表中的所有记录：

command.CommandText = "select * from people";
DataReader reader = command.ExecuteReader();

现在，获取实际值的方式有点笨拙。肯定不如使用 Entity Framework 那么方便，但正如我在如何使用 Sublime 构建查询的视频中展示的那样，您也可以使用相同的技术来更快地创建此代码。

假设 first_name 和 last_name 是字符串，age 是整数，那么你可以这样迭代所有结果。

command.CommandText = "select * from people";
using(DataReader reader = command.ExecuteReader()) 
{
    while(reader.Read())
    {
        string firstName = reader.GetString(reader.GetOrdinal("first_name"));
        string lastName = reader.GetString(reader.GetOrdinal("last_name"));
        int age = reader.GetInt32(reader.GetOrdinal("age"));

        //do something with firstName, lastName and age

    }
}

GetString、GetIn32、等方法GetBoolean需要一个表示列索引的数字。您可以通过调用来获取该列索引reader.GetOrdinal("columnName")。

更新和删除

更新和删除记录涉及使用正确的 SQL 创建命令，并ExecuteNonQuery在该命令中调用。

例如，如果我们想将人员表中姓氏为“Doe”的所有记录更新为“Smith”，我们可以这样做

command.CommandText = "update people set last_name='Smith' where last_name='Doe'";
int numberOfAffectedRows = command.ExecuteNonQuery();

删除操作非常相似，例如，删除所有没有 last_name 的记录

command.CommandText = "delete from people where last_name is null";
int numberOfAffectedRows = command.ExecuteNonQuery();

交易

使用像 Entity Framework 这样的 ORM 时，您可以免费获得的一件事是，当您持久化更改（即调用.SaveChanges()）时，该更改发生在事务内部，以便所有更改都被持久化或不被持久化。

值得庆幸的是，使用 ADO.NET 创建事务非常简单。以下是一个例子，我们在一个数据库事务中添加了一个新用户，删除了另一个用户，并更新了另一个用户：

using (var transaction = connection.BeginTransaction())
{
    var insertCommand = connection.CreateCommand();
    insertCommand.CommandText = "insert into people (first_name) values (@first_name)";
    insertCommand.Parameters.AddWithValue("@first_name", "Jane Smith");
    insertCommand.ExecuteNonQuery();

    var deleteCommand = connection.CreateCommand();
    deleteCommand.CommandText = "delete from people where last_name is null";
    deleteCommand.ExecuteNonQuery();

    var updateCommand = connection.CreateCommand();
    updateCommand.CommandText = "update people set first_name='X' where first_name='Y'";
    updateCommand.ExecuteNonQuery();

    transaction.Commit();
}

创建事务最简单的方法是从连接对象中请求一个事务。事务的创建依赖于IsolationLevel 。虽然我们这里没有指定隔离级别（将使用特定数据库的默认隔离级别），但您应该查看可用的隔离级别列表，并选择适合您需求的级别。

创建事务后，我们可以像之前一样执行所有操作，最后调用.Commit()事务。如果.Commit()在调用之前出现任何问题，所有更改都会回滚。

从数据库获取元数据

这虽然有点题外，但了解一下还是很有用的。使用 ADO.NET 可以提取数据库的元数据。例如，所有列名及其类型构成一个特定的表。

以下代码片段显示了如何获取特定数据库表的所有列名和数据类型：

command.CommandText = "select * from people";
using(var reader = command.ExecuteReader()) 
{
    var columnSchema = reader.GetColumnSchema();
    foreach(var column in columnSchema)
    {
        Console.WriteLine($"{column.ColumnName} {column.DataTypeName}");
    }
}

使用 Dapper

与仅使用 ADO.NET 相比，使用Dapper也同样简单，而且性能方面差别不大。

如果您不熟悉 Dapper，它是StackExchange的一个项目，它为 StackExchange 系列网站（StackOverflow、SuperUser、AskUbuntu等）提供支持。

要使用 Dapper，你需要安装一个 Nuget 包，Dapper该包的名称要与目标数据库对应的 Nuget 包名称一致。例如，对于 Postgres：

$ dotnet add package Npgsql
$ dotnet add package Dapper

Dapper 向您的连接对象添加了一些扩展方法，即Query<T>和Execute。

Query<T>允许你运行查询并将结果映射到你在泛型参数中指定的类型。例如，你可以这样获取 people 表中的所有记录：

using Dapper; //you need this to get the extension methods on the connection object
//...

using (var connection = new NpgsqlConnection("theConnectionString"))
{
    IEnumerable<Person> people = connection.Query<Person>("select * from people");                        
}

即使你只期望一条记录，该Query<T>方法也始终会返回IEnumerable<T>。例如，你可以这样做来插入一个新的人员并获取新的 ID：

int newId = connection.Query<int>("insert into people (first_name, last_name, age) values (@FirstName, @LastName, @Age) returning id", new {
    FirstName = "John",
    LastName = "Doe",
    Age = "40"
}).FirstOrDefault();

在上面的示例中，我们为该Query方法提供了两个参数：第一个是 SQL 语句，第二个是一个匿名对象，其属性名称与 SQL 语句中的参数匹配。您也可以使用类的实例（例如new Person { ... }）。

如果您不关心任何结果，例如只想删除一条记录，则可以使用该Execute方法，它将返回数据库中受影响的记录数。例如，如果您想删除所有 last_name 为空的记录：

var numberOfDeletedRecords = connection.Execute("delete from person where last_name is null");

这只是对 Dapper 的简单介绍，如果您有兴趣了解更多信息，该项目的 github 页面是一个很好的资源。

希望这篇博文能为您提供足够的信息，帮助您了解如何在 .Net Core 中使用 ORMless。请在评论区分享您的想法。

文章来源：https://dev.to/ruidfigueiredo/orm-less-data-access-in-net-core-23ac