用 133 行代码构建数据库连接框架

Entity Framework 是 .NET 开发者流行的数据库连接选择。它使用起来相当简单，但，如果我告诉你，我们可以在 ASP.NET 之上创建一个连接框架，允许我们完全控制我们编写的 SQL 呢？最棒的是，它需要的代码量与配置 Entity Framework 相当。

在这篇文章中，我将向你展示如何将 ASP.NET 最小 API 连接到本地 SQLite 数据库 Entity Framework。我们仍然会将表映射到类中，从而允许我们在 C# 中与我们的数据进行交互。我将包含您需要的每一行代码，那么让我们开始吧.

数据

我们首先要做的是设置一个数据库。我决定使用 SQLite，因为我以前从来没有机会用过。确保你已经安装了 SQLite 并且已经连接到一个数据库。（你也可以使用以下方法连接到 MySQL、SQL Server 或 Postgres 数据库，但本文将专注于 SQLite。）

通过我们的数据库，我们将跟踪学生信息和成绩。首先，连接到SQLite并创建一个数据库，我们称之为Students.db，然后创建一个students表和一个grades表.

sqlite3 Students.db

sqlite> CREATE TABLE students (
...>        id INTEGER PRIMARY KEY,
...>        name TEXT,
...>        school TEXT
...> );
sqlite> CREATE TABLE grades (
...>        id INTEGER PRIMARY KEY,
...>        scored INTEGER,
...>        out_of INTEGER,
...>        student_id INTEGER,
...>        FOREIGN KEY (student_id) REFERENCES students(id)
...> );

将这些两个表的定义扔进ChatGPT，让它为这两个表编写一些示例数据。这不是必要的，但一旦我们完成，它将使你的API更有趣。完成后，我们就可以开始了。

连接

我们不使用 Entity Framework，而是移除那层抽象层，改用 ADO.NET。这些都是 EF 在其实现中使用的库。微软很贴心地把它们打包成了 NuGet 包供我们使用。在项目的根目录下运行以下命令：

dotnet add package Microsoft.Data.Sqlite

安装完成后，我们可以开始将我们的应用程序连接到数据库。首先，获取您的连接字符串。每个数据库提供者都有自己的格式，但我相信您可以找到并自行配置。Sqlite的格式如下：

"Data Source=path/to/database_file.db"

让应用程序访问此内容最好的方式是通过依赖注入提供的配置对象。我写了一篇文章关于如何设置它如果你需要帮助。或者你可以决定做一个无法无天的牛仔，硬编码它...我不是你妈。

终于到了写代码的时候了。为了与我们的数据库交互，我们需要一个类和一个匹配的接口，它将提供与整个应用程序的连接。我们将它命名为，有点缺乏想象力，DatabaseConnectionProvider。

// DatabaseConnectionProvider.cs
using Microsoft.Data.Sqlite;

namespace Students.Repository.SQL;

public class DatabaseConnectionProvider : IDatabaseConnectionProvider
{
    private readonly string _connectionString;

    public DatabaseConnectionProvider(IConfiguration config)
    {
        // Don't you hard code it, cowboy...
        _connectionString = config["ConnectionString"]!;
        if (string.IsNullOrEmpty(_connectionString))
        {
            throw new InvalidOperationException("Connection string not found.");
        }
    }
}

由于我们稍后将把这个提供者注入到其他类中，我们将创建一个接口，并包含我们将创建的第一个方法的预览！

// IDatabaseConnectionProvider.cs
namespace Students.Abstractions;

public interface IDatabaseConnectionProvider
{
    List<T> GetRecords<T>();
}

现在回到我们的提供者类中，在这个GetRecords方法内部创建数据库连接.

// DatabaseConnectionProvider.cs 
// after DatabaseConnectionProvider(IConfigration)

    public List<T> GetRecords()
    {
        using (var connection = new SqliteConnection(_connectionString))
        {
            connection.Open();
            var command = connection.CreateCommand();
            command.CommandText = "SELECT * FROM students;";

            var reader = command.ExecuteReader();
            while (reader.Read())
            {
                Console.WriteLine(reader["name"].ToString())
            }
            connection.Close();
            return new List<T>();
        }
    }

该SqliteConnection类由我们之前安装的那个NuGet包提供。这为我们提供了数据库交互的基础构件：连接、发送命令以及读取结果.

这不是我们的最终GetRecords实现，但它将会工作，你可以测试一下（如果你在数据库中输入了一些示例值）。当你运行它时，该方法会创建 Sqlite 数据库连接并发出一个 SELECT * 命令来返回所有学生。我们得到一个 DataReader，这也是之前 NuGet 包提供的一个，我们可以用它来读取每一行中的 "name" 列，然后在使用前销毁连接。

我们已连接！花点时间庆祝.

模型

现在我们已经可以从数据库中获取数据，为了能够处理这些数据，我们需要做一些基础工作。对于每个创建我们希望在代码中使用的记录的数据库表，我们需要使用类在C#中创建一个模型。

在这个小示例项目中，我们只需要两个。

// Student.cs
public class Student
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public string School { get; set; } = string.Empty;
}

// Grade.cs
public class Grade
{
    public int Id { get; set; }
    public int Scored { get; set; }
    public int OutOf { get; set; }
    public int StudentId { get; set; }
}

其中这些模型代表表中的一行。目前，它们是悲伤且空旷的。好吧，也许不是悲伤，但肯定空旷。但是，现在我们有一个问题。我们如何让DataReader来输出我们刚刚创建的这些模型？

一旦我们进入读取循环，我们可以通过名称访问所有列，并手动将它们分配给模型属性。这样是可以工作的，但我们不得不将我们的方法重命名为GetStudentRecords，因为当查找时尝试查询成绩会抛出异常"name"或者"school"列。

那么我们就可以创建一个新的GetGradeRecords，当然，我们会重写我们原始方法中的所有逻辑，除了我们构建和返回模型的地方。

相反，我们需要将解析的责任从我们的DatabaseConnectionProvider中卸载。我们不必让我们的提供者负责读取数据，而是可以让每个模型负责知道如何通过给每个模型提供一个Parse方法来构建自己。

// In Student.cs 
    public string School { get; set; } = string.Empty;

    public Student Parse(IDataReader reader)
    {
        Id = reader.ParseInt("id");
        Name = reader.ParseString("name");
        School = reader.ParseString("school");

        return this;
    }
}

// In Grade.cs
    public int StudentId { get; set; }

    public Grade Parse(IDataReader reader)
    {
        Id = reader.ParseInt("id");
        Scored = reader.ParseInt("scored");
        OutOf = reader.ParseInt("out_of");
        StudentId = reader.ParseInt("student_id");

        return this;
    }
}

如果你在输入，可能已经注意到ParseInt和ParseString是红色的。为了稍微清理一下语法，我在DataReader类上添加了几个静态扩展方法。你可以在一个新文件中添加这些内容。

using System.Data;

namespace Students.Repository.SQL;

public static class ReaderExtensions()
{
    public static int ParseInt(this IDataReader reader, string columnName)
    {
        return reader.GetInt32(reader.GetOrdinal(columnName));
    }

    public static string ParseString(this IDataReader reader, string columnName)
    {
        return reader.GetString(reader.GetOrdinal(columnName));
    }
}

现在我们只需要让DatabaseConnectionProvider知道我们的模型有这个解析方法。我们将使用泛型来实现这一点，但我们也需要定义一个接口来泛型地暴露这个方法...

// ISqlDataParser.cs
using System.Data;

namespace Students.Abstractions;

public interface ISqlDataParser<T>
{
    T Parse(IDataReader reader);
}

...并确保我们的模型继承它.

// Student.cs
public class Student : ISqlDataParser<Student>

// Grade.cs
public class Grade : ISqlDataParser<Grade>

现在，让我们更新我们的GetRecords声明，以完成我们的Parse方法的可用性.

// In DatabaseConnectionProvider
// Replace public List<T> GetRecords()
public List<T> GetRecords() where T : ISqlDataParser<T>, new()

这句话的意思是，我们只能调用GetRecords<T>，如果为T插入的类型实现了ISqlDataParser 接口并且有一个无参数的构造函数.

现在我们可以安全地访问 Parse，可以在 GetRecords<T> 中使用.

// In DatabaseConnectionProvider.cs
// In GetRecords<T>
// Replace everything after:  var reader = command.ExecuteReader();

        var returnList = new List<T>();
        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                returnList.Add(new T().Parse(reader));
            }
        }
        connection.Close();
        return returnList;
    }
}

查询

目前，我们处于一个有趣的状态。GetRecords 方法是 可用的 理论上解析无限数据类型，但它永远不会。为什么？因为我们已将查询硬编码到发送到数据库的命令对象中。

类似于我们之前对 Parse 方法所做的工作，我们需要去除 DatabaseConnectionProvider 选择它应该运行的查询的责任。这意味着我们将不得不将查询移入参数中，但，在那里我们遇到了一个问题。

如果只在方法中添加一个string参数，那么我们就无法访问Command对象。

那又怎样？

Command对象就是我们要添加的地方SqlParameters。此时我们唯一的选择可能是手动使用字符串插值来添加值。强迫我们的提供者的用户以这种方式构建查询不仅是不礼貌的，而且也是一个潜在的安全漏洞。微软已经（希望）已经做了大量工作来保护这个Command对象免受SQL注入攻击。无论我们进行何种清理，将参数添加到正确的属性上Command 是前进的最智能方式。

为了保持我们提供者的方法模块化，我们需要将其抽象成一个类，该类可以与我们所创建的任何模型一起使用。它将用参数值的占位符包装我们的预期查询，并包含一个我们的参数的字典。我们将称之为 DataCallSettings（命名很困难）。

// DataCallSettings.cs
namespace Students.Models;

public class DataCallSettings
{
    public string SqlCommand { get; set; }
    public Dictionary<string, object> Parameters { get; set; } = new();

    public DataCallSettings(string command)
    {
        SqlCommand = command;
    }

    public void AddParameter(string name, object value)
    {
        Parameters[name] = value;
    }

}

这个类现在是唯一参数到GetRecords<T>。这比传递位置参数要好得多的开发者体验。这是一个简单的实现，但如果我们决定将其扩展以支持存储过程、重试逻辑、事务或缓存，我们可以做到这一点而不破坏我们现有的代码库。

看看我们做了所有更改后的完成方法。

// In DatabaseConnectionProvider.cs

public List<T> GetRecords<T>(DataCallSettings dcs) where T : ISqlDataParser<T>, new()
{
    using (var connection = new SqliteConnection(_connectionString))
    {
        connection.Open();
        var command = connection.CreateCommand();
        command.CommandText = dcs.SqlCommand;

        foreach (var key in dcs.Parameters.Keys)
        {
            command.Parameters.AddWithValue(key, dcs.Parameters[key]);
        }

        var returnList = new List<T>();
        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                returnList.Add(new T().Parse(reader));
            }
        }
        connection.Close();

        return returnList;
    }
}

额外奖励，我还会给你两行代码，让我们能够使用之前所有的努力获取单条记录。

public T GetRecord<T>(DataCallSettings dcs) where T : ISqlDataParser<T>, new()
=> GetRecords<T>(dcs).FirstOrDefault() ?? new();

命令

有时候你可能需要向数据库发起一个不会返回任何结果的查询。所以，我们的数据库接口并不完全。我们需要构建一个方法，允许我们发送数据库查询而不在最后创建一个空模型。它看起来会非常类似于我们的 GetRecords 方法，但我们会使用 .ExecuteNonQuery() 方法，我们会把它叫做 Execute。 创意...我知道。

// In DatabaseConnectionProvider.cs
// After GetRecord<T>

public int Execute(DataCallSettings dcs)
{
    using (var connection = new SqliteConnection(_connectionString))
    {
        connection.Open();
        var command = connection.CreateCommand();
        command.CommandText = dcs.SqlCommand;

        foreach (var key in dcs.Parameters.Keys)
        {
            command.Parameters.AddWithValue(key, dcs.Parameters[key]);
        }

        var result = command.ExecuteNonQuery();
        connection.Close();
        return result;
    }
}

这很好，但我们同时在GetRecords和Execute之间重复了一些逻辑。让我们把它提取到一个新的BuildCommand方法中.

// In DatabaseConnectionProvider.cs
// After Execute()

private SqliteCommand BuildCommand(DataCallSettings dcs, SqliteConnection connection)
{
    var command = connection.CreateCommand();
    command.CommandText = dcs.SqlCommand;

    foreach (var key in dcs.Parameters.Keys)
    {
        command.Parameters.AddWithValue(key, dcs.Parameters[key]);
    }

    return command;
}

然后重构这两个方法以使用它.

// In DatabaseConnectionProvider.cs

// In GetRecords<T>
    connection.Open();
    var command = BuildCommand(dcs, connection);

    var returnList = new List<T>();

// In Execute()
    connection.Open();
    var command = BuildCommand(dcs, connection);

    var result = command.ExecuteNonQuery();

啊，好多了。但我们还没完成。让我们再添加一个我们实践中会经常使用的方法。当我们执行一个INSERT操作时，数据库会设置标识列，所以让我们确保我们能把它取回来

// In DatabaseConnectionProvider.cs
// Below Execute()

public int ExecuteWithIdentity(DataCallSettings dcs)
{
    if (!dcs.SqlCommand.StartsWith("INSERT"))
    {
        throw new InvalidOperationException();
    }

    using (var connection = new SqliteConnection(_connectionString))
    {
        connection.Open();
        dcs.SqlCommand = $"{dcs.SqlCommand} SELECT last_insert_rowid();";
        var command = BuildCommand(dcs, connection);

        var identity = Convert.ToInt32(command.ExecuteScalar());
        connection.Close();
        return identity;
    }
}

好了，请敲鼓庆祝。

我们做到了！我们手工创建了一种与数据库交互的方式，这种方式是可扩展且易于使用的.

仓库

现在我们已经完成了这个数据库界面的创建，我想快速带大家了解一下如何使用它。我日常使用的一种设计模式是仓库设计模式。这是一个很酷炫（也必须承认有点让人困惑）的名字，我们可以用它来命名一个封装了我们的数据传输逻辑的文件，从而减少模块之间的耦合。

添加一个单独的层使我们能够在最终将此数据发送到某个前端时忽略数据库的实现细节。所以，让我们创建一个 StudentsRepository.

// StudentsRepository.cs
using Students.Abstractions;
using Students.Models;

namespace Students.Repository.SQL;

public class StudentsRepository : IStudentsRepository
{

}

这里就是接口，包括我们即将创建的方法.

using Students.Models;

namespace Students.Abstractions;

public interface IStudentsRepository
{
    Student GetStudent(int id);
    List<Student> GetStudents();
    int SaveStudent(Student student);
    bool DeleteStudent(int id);
}

然后，在我们的代码库中，我们将注入我们的DatabaseConnectionProvider。但请先确保它在你的Program.cs文件中已注册。如果你需要帮助，这里有一篇文章，介绍了如何在ASP.NET中实现DI。

现在完成实现很简单，只需调用适当的提供者方法即可DataCallSettings 实例化一个 SQL 查询。与其一步步讲解，我直接把整个文件包含在下面，这样你就能看到它将如何显示。

// in StudentsRepository.cs

public class StudentsRepository : IStudentsRepository
{
    private readonly IDatabaseConnectionProvider _provider;

    StudentsRepository(IDatabaseConnectionProvider provider)
    {
        _provider = provider;
    }

    public Student GetStudent(int id)
    {
        var dcs = new DataCallSettings("SELECT * FROM students WHERE Id = @Id;");
        dcs.AddParameter("Id", id);
        return _provider.GetRecord<Student>(dcs);
    }

    public List<Student> GetStudents()
    {
        var dcs = new DataCallSettings("SELECT * FROM students;");
        return _provider.GetRecords<Student>(dcs);
    }

    public int SaveStudent(Student student)
        => student.Id == 0 // new student
            ? InsertStudent(student)
            : UpdateStudent(student);

    private int InsertStudent(Student student)
    {
        var dcs = new DataCallSettings("INSERT INTO students (name, school) VALUES (@Name, @School);");
        AddStudentParameters(dcs, student);
        return _provider.ExecuteWithIdentity(dcs);
    }

    private int UpdateStudent(Student student)
    {
        var dcs = new DataCallSettings("UPDATE students SET name = @Name, school = @School WHERE id = @Id;");
        dcs.AddParameter("Id", student.Id);
        AddStudentParameters(dcs, student);
        return _provider.Execute(dcs) > 0
            ? student.Id
            : 0;
    }

    public bool DeleteStudent(int id)
    {
        var dcs = new DataCallSettings("DELETE FROM students WHERE id = @Id");
        dcs.AddParameter("Id", id);
        return _provider.Execute(dcs) > 0;
    }

    private void AddStudentParameters(DataCallSettings dcs, Student student)
    {
        if (student.IsNew) { dcs.AddParameter("Id", student.Id); }
        dcs.AddParameter("Name", student.Name);
        dcs.AddParameter("School", student.School);
    }
}

结论

好吧，我承认，如果你使用 Entity Framework，这段代码可能比你需要的要多。但是……它不是那么更多了。我发现自己在具有这种透明度和灵活性的应用程序中工作要比在一个进行映射魔法的应用程序中舒适得多。对我来说，这是一个简单的权衡。

这个实现并不聪明，也不复杂，而且易于使用，即使需要一点时间来适应。在过去的两年里，我编程时，我处理的大部分错误都是由数据格式不正确引起的。也许这是我的技能问题，但它让我对我的工作中的应用数据处理产生了一些偏执。

使用这个模式，它完全由我处理。如果有问题，我知道那是我写的（也就是说，那是我可以修复的）。我创建了数据库，我写了SQL查询。既然我负责这个系统，我想知道它是如何处理我的数据的。

这并不是说ORM不好。我可能不太擅长使用它们（见我之前提到的技能问题）。但是，如果你从未尝试过使用ADO.NET，我希望你觉得你有工具开始使用。

推荐订阅源

DEV Community

数据

连接

模型

查询

命令

仓库

结论