建一数据库连接之架构于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

数据

之联接

模式

查询

命令

此库也

结论