在C#&.NET Core(.NET 7)程序开发中使用Npgsql,Dapper,EF Core等不同方式连接和操作PostgreSQL数据库示例教程(推荐阅读)

概述

PostgreSQL(又称Postgres)是如今使用最广泛的数据库之一。根据2022年Stack Overflow的调查，它是专业开发人员使用最多的数据库；在TechEmpower基准测试中是最快的数据库；同时也自称自己是最先进的开源数据库。

有了这么多响亮的句号加持，作为开发者的，是否已对PostgreSQL蠢蠢欲动了呢？

今天，我们将讨论如何将PostgreSQL与我们最喜欢的语言(C#)结合起来。虽然Postgres不是微软的第一方数据库，如SQL Server，但并不意味着在C#编程中不能使用它或者对它的支持不够。

相反，在本文中，你将看到在C#如何使用不同方式来连接并操作PostgreSQL数据库。你将看到如何使用标准数据提供程序库Npgsql在Postgres和C#之间进行交互，并分别使用Dapper和EF Core一起演示。本文将向你展示综合使用所有这些框架的基本操作，如创建表，插入行，删除和更新记录等。

准备工作

在开始本文C#&.NET Core应用程序使用Dapper连接 PostgreSQL数据库之前，你需要准备如下环境及组件：

• PostgreSQL数据库及管理工具(如：pgAdmin)
• .NET Core(.NET 7)运行环境
• 开发工具(Visual Studio 2019及以上版本，建议Visual Studio 2022)或者VS Code
• PostgreSQL示例表创建脚本

本文以简单的员工(employee)信息管理为示例，示例数据表的SQL脚本如下：

注：本示例的PostgreSQL架构(Schema)为dbo，登录用户名为dev，请自行对应你的数据库架构。

在C#中使用Npgsql操作PostgreSQL

为了演示，我们创建一个.NET Core(.NET 7)的控制台应用程序。然后使用dotnet cli命令行安装NpgsqlNuget程序包：

或者，可以使用Visual Studio 2022的Nuget程序包管理工具安装，如下图示：

Npsql包安装完成后，便可以在C#程序中连接PostgreSQL数据库并完成数据表的创建了。

注意：请将PostgreSQL数据库连接字符串对应的配置修改成你的Postgres数据库连接信息。本示例的Postgres数据库信息解释如下：

• Server=192.168.7.22：Postgres数据库服务器地址
• Port=5432：Postgres数据库服务的端口
• User Id=dev：用户名
• Password=123456：登录密码
• Database=test_db：数据库名称

你可以在Postgres的管理工具pgAdmin中看到相应的数据库连接信息，如下：

运行上面的示例程序，结果如下：

Npgsql+ADO.NET篇

使用Npgsql写入数据

上面的代码成功运行之后，测试表employee就成功创建了。

接下来，我们在C#程序中使用Npgsql组件对Postgres数据库进行新增，查询，修改，删除等一系列的操作示例。

向Postgres数据库的dbo.empolyee表写入数据：

执行结果：

pgAmin管理工具中查看数据：

当然，为了防止SQL注入，Npgsql是支持参数命令的。比如，下面的插入语句：

pgAmin管理工具中查看数据结果：

使用Npgsql更新数据

使用Npgsql更新数据表dbo.employee中id=1的login_name,passwod和age这三个字段的数据，如下：

更新结果如下：

使用Npgsql删除数据

使用Npgsql删除数据表dbo.employee中id=1的记录，如下：

执行结果：

使用Npgsql查询数据

使用Npgsql查询数据表dbo.employee中的所有记录。

首先，创建一个Employee.cs类，在其中编写如下的属性：

运行结果：

Dapper篇

如果你不熟悉Dapper，它是一个简单的对象映射器，允许你使用常规SQL查询数据库并将它们映射到C#对象。它介于ADO.NET和ORM之间，并不是一个完全意义上的ORM。

要在C#&.NET Core(.NET 7)等程序项目中使用Dapper，需要安装Dapper的Nuget程序包，比如.NET CLI命令dotnet add package Dapper或者使用Visual Studio 2022中集成的Nuget程序包管理工具安装，如下图：

同样的，Dapper的Nuget程序包安装完成后，我们便可以使用它来进行Postgres数据库的连接和交互操作了。

Dapper写入数据

由于Dapper与Npgsql相比作了一些方法的封装，所以在使用Dapper时不需要我们再提供Command对象，也不需要调用Command的方法。要执行SQL语句只需要调用Dapper的扩展方法，比如：Exectue(),ExecuteAsync()等等。以下是向dbo.employee表中写入一条新的员工数据：

Dapper查询Postgres数据

相比于Npgsql，Dapper查询数据显示容易很多，Dapper提供了查询单条数据，以及查询数据集合的方法，比如QueryFirst(),QueryFirstOrDefault(),QuerySingle(),Query<T>(),QueryMultiple()等等方法。以下是查询dbo.employee表中的所有员工数据集合：

运行结果：

请注意：，这里虽然把Postgres数据库中的dbo.employee查询出来了，但细心的你会发现，其中的：LoginName,CreatedAt属性的值并没有被正确映射出来。

这是因为Postgres数据库的表dbo.employee字段使用的是小写+下划线的命名规则，而C#中Employee类的属性名使用的是CamelCase的命名规则。默认情况下Dapper只会作Postgres字段名与C#实体属性名相同的映射，而像login_name是不是自动映射成LoginName的。
所以，上面示例得到查询结果集合中的LoginName与CreatedAt这两个属性的值是C#的类型默认值。

要解决这个问题，方式也有多种。比如，我们可以在写SQL脚本时显式地指定字段别名，如下：

运行结果：

此外，如果你觉得每个字段都去指定别名比较麻烦，则可以指定Dapper的MatchNamesWithUnderscores为true，如下：

运行结果：

Dapper更新Postgres数据

与执行插入(INSERT)语句类似，执行更新语句同样调用Dapper的Execute()扩展方法，示例如下：

Dapper删除Postgres数据

与执行插入(INSERT)语句类似，执行删除语句同样调用Dapper的Execute()扩展方法，只是执行的语句不同而已，示例如下：

以上就是C#&.NET Core(.NET 7)应用程序中使用Dapper操作Postgres数据库的示例。

EF Core篇

EF Core是微软官方发布的.NET平台的ORM，它支持主流的数据库，如：SQL Server,MySQL,Postgres,SQLite等。要在C#项目中使用EF Core操作Postgres数据库，需要安装Npgsql.EntityFrameworkCore.PostgreSQLNuget程序包。安装方式可以是.NET CLI，也可以通过Visual Studio 2022集成的Nuget程序包管理工具安装。

Npgsql.EntityFrameworkCore.PostgreSQLNuget程序包安装完成后，EF Core操作数据需要创建一个数据库上下文类，本文名为EmployeeDbContext，继承自命名空间Microsoft.EntityFrameworkCore下的DbContext类，如下：

请注意：在此数据库连接上下文指定了AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true);。否则，如果遇到timestamp without time zone的字段，会报错：”Cannot write DateTime with Kind=Local to PostgreSQL type ‘timestamp with time zone’, only UTC is supported.”

这EmployeeDbContext中，我们指定了数据集DbSet为Employee，重写了OnConfiguring()方法，在其方法内部指定了Postgres数据库的连接字符串。

此外，还需要对Empolyee.cs类进行配置，主要设置适用于EF Core的相关特性，如下：

以上的EmployeeDbContext.cs和Employee.cs处理好后，就可以使用EF Core对Postgres数据库进行交互操作了。

EF Core对Postgres写入数据

EF Core操作Postgres一般都是DbContext的实例进行的，写入数据则是向上下文的对应数据集Add()一个实体对象。如下示例是向dbo.employee数据表写入一条员工记录：

EF Core对Postgres查询数据

EF Core的查询操作通常是对数据集进行的LINQ操作，如下是查询dbo.employee表中的所有员工数据：

EF Core对Postgres更新数据

EF Core的查询操作中，如果要更新某条记录的多个字段的值，通过是先找到(Find())这条记录，然后设置要更新属性的值，最后通过SaveChanges()来保存更改，示例如下：

EF Core对Postgres删除数据

EF Core删除数据的做法通常是通过数据库连接上下文中的数据集的Remove()方法来实现，示例如下：

总结

好了，以上就是C#&.NET Core(.NET 7)程序中分别使用Npgsql，Dapper，EF Core等不同组件、ORM实现的与Postgres数据进行连接与交互的实例，希望对你的C#&.NET Core程序开发有所帮助。

我是Rector，专注C#&.NET开发，关注我，解锁更多开发技巧。