SqlSugar实战：.NET 8环境下PostgreSQL数据库CRUD操作全流程（附源码）

SqlSugar与PostgreSQL实战.NET 8环境下的高效数据操作指南在当今快速迭代的开发环境中选择一套高效、稳定的ORM框架对.NET开发者而言至关重要。SqlSugar作为国内广受欢迎的轻量级ORM解决方案以其简洁的API设计和卓越的性能表现赢得了大量开发者的青睐。特别是在.NET 8环境下SqlSugar与PostgreSQL的结合为开发者提供了一种既强大又灵活的数据访问方式。PostgreSQL作为功能最强大的开源关系数据库之一其丰富的特性和出色的扩展能力使其成为企业级应用的热门选择。本文将带您深入探索如何在.NET 8项目中利用SqlSugar框架实现与PostgreSQL数据库的无缝交互从环境配置到CRUD操作再到高级查询技巧为您呈现一套完整的实战解决方案。1. 环境准备与基础配置1.1 创建.NET 8项目并安装必要依赖首先我们需要创建一个新的.NET 8项目。打开命令行工具执行以下命令dotnet new console -n SqlSugarDemo cd SqlSugarDemo接下来通过NuGet包管理器安装SqlSugar核心包dotnet add package SqlSugarCore由于我们要连接PostgreSQL数据库还需要安装PostgreSQL的.NET数据提供程序dotnet add package Npgsql1.2 配置数据库连接在项目中创建一个Database文件夹然后添加一个DbManager.cs文件用于管理数据库连接using SqlSugar; namespace SqlSugarDemo.Database { public static class DbManager { private static SqlSugarScope _db; public static SqlSugarScope Db _db ?? Initialize(); private static SqlSugarScope Initialize() { return new SqlSugarScope(new ConnectionConfig() { ConnectionString Hostlocalhost;Port5432;Databasetestdb;Usernamepostgres;Passwordyourpassword, DbType DbType.PostgreSQL, IsAutoCloseConnection true, InitKeyType InitKeyType.Attribute }, db { // 配置AOP db.Aop.OnLogExecuting (sql, pars) { Console.WriteLine(sql); }; }); } } }这个配置类使用了SqlSugar的SqlSugarScope它是线程安全的单例模式适合在大多数应用场景中使用。IsAutoCloseConnection设置为true表示每次操作完成后自动关闭连接而InitKeyType设置为Attribute表示我们将在实体类中使用特性来配置主键等元数据。1.3 创建实体模型为了更好地组织代码我们创建一个Models文件夹然后添加一个简单的用户模型using SqlSugar; namespace SqlSugarDemo.Models { [SugarTable(users)] public class User { [SugarColumn(ColumnName id, IsPrimaryKey true, IsIdentity true)] public int Id { get; set; } [SugarColumn(ColumnName name)] public string Name { get; set; } [SugarColumn(ColumnName age)] public int Age { get; set; } [SugarColumn(ColumnName email)] public string Email { get; set; } [SugarColumn(ColumnName created_at)] public DateTime CreatedAt { get; set; } DateTime.Now; } }在这个实体类中我们使用了SugarTable特性来指定表名使用SugarColumn特性来配置各个列的属性。IsPrimaryKey标记主键IsIdentity表示这是一个自增字段。2. 基础CRUD操作实现2.1 插入数据让我们从最基本的插入操作开始。在Services文件夹下创建一个UserService.cs文件using SqlSugarDemo.Database; using SqlSugarDemo.Models; namespace SqlSugarDemo.Services { public class UserService { private readonly SqlSugarScope _db; public UserService() { _db DbManager.Db; } public int AddUser(User user) { return _db.Insertable(user).ExecuteCommand(); } public int AddUsers(ListUser users) { return _db.Insertable(users).ExecuteCommand(); } } }这个服务类提供了两个插入方法AddUser用于插入单个用户AddUsers用于批量插入多个用户。ExecuteCommand方法会执行SQL并返回受影响的行数。提示SqlSugar的插入操作非常灵活除了基本的插入外还支持插入后返回自增ID、只插入指定列等多种操作方式。2.2 查询数据查询是数据库操作中最常用的功能之一。让我们扩展UserService类添加一些查询方法public ListUser GetAllUsers() { return _db.QueryableUser().ToList(); } public User GetUserById(int id) { return _db.QueryableUser().Where(u u.Id id).First(); } public ListUser GetUsersByAgeRange(int minAge, int maxAge) { return _db.QueryableUser() .Where(u u.Age minAge u.Age maxAge) .OrderBy(u u.Age) .ToList(); } public dynamic GetUserNamesAndEmails() { return _db.QueryableUser() .Select(u new { u.Name, u.Email }) .ToList(); }这些方法展示了SqlSugar查询的基本用法简单的全表查询带条件的单条查询范围查询与排序只选择特定列的投影查询2.3 更新数据更新操作同样简单直观。在UserService中添加以下方法public int UpdateUser(User user) { return _db.Updateable(user).ExecuteCommand(); } public int UpdateUserName(int id, string newName) { return _db.UpdateableUser() .SetColumns(u u.Name newName) .Where(u u.Id id) .ExecuteCommand(); } public int IncrementUserAge(int id) { return _db.UpdateableUser() .SetColumns(u u.Age u.Age 1) .Where(u u.Id id) .ExecuteCommand(); }这里展示了三种更新方式更新整个实体只更新特定字段基于原值的增量更新2.4 删除数据最后我们来实现删除操作public int DeleteUser(int id) { return _db.DeleteableUser().Where(u u.Id id).ExecuteCommand(); } public int DeleteUsersByAge(int age) { return _db.DeleteableUser().Where(u u.Age age).ExecuteCommand(); }删除操作同样支持条件删除可以删除单条记录也可以批量删除符合条件的多条记录。3. 高级查询与性能优化3.1 分页查询在实际应用中分页查询是非常常见的需求。SqlSugar提供了非常便捷的分页方法public (ListUser, int) GetUsersPaged(int pageNumber, int pageSize) { var total 0; var list _db.QueryableUser() .ToPageList(pageNumber, pageSize, ref total); return (list, total); }这个方法返回当前页的数据列表和总记录数非常适合前端分页展示。SqlSugar的分页查询会自动优化生成的SQL在PostgreSQL中使用LIMIT和OFFSET实现高效分页。3.2 复杂条件查询SqlSugar的查询条件构建非常灵活支持复杂的逻辑组合public ListUser SearchUsers(string keyword, int? minAge, int? maxAge) { var query _db.QueryableUser(); if (!string.IsNullOrEmpty(keyword)) { query query.Where(u u.Name.Contains(keyword) || u.Email.Contains(keyword)); } if (minAge.HasValue) { query query.Where(u u.Age minAge.Value); } if (maxAge.HasValue) { query query.Where(u u.Age maxAge.Value); } return query.ToList(); }这种动态构建查询条件的方式在实际业务场景中非常有用可以根据不同的参数组合生成不同的查询。3.3 联表查询PostgreSQL强大的关系型功能与SqlSugar的联表查询能力结合可以轻松处理复杂的数据关系。假设我们有一个Department表与User表存在一对多关系[SugarTable(departments)] public class Department { [SugarColumn(IsPrimaryKey true, IsIdentity true)] public int Id { get; set; } public string Name { get; set; } } // 在User模型中添加部门ID [SugarColumn(ColumnName department_id)] public int DepartmentId { get; set; } // 导航属性 [Navigate(NavigateType.OneToOne, nameof(DepartmentId))] public Department Department { get; set; }现在我们可以执行联表查询public ListUser GetUsersWithDepartments() { return _db.QueryableUser() .Includes(u u.Department) .ToList(); }Includes方法会自动加载关联的部门信息生成高效的JOIN查询。3.4 性能优化技巧在使用SqlSugar与PostgreSQL时有几个性能优化的关键点批量操作对于大量数据的插入、更新使用批量操作可以显著提高性能。public void BulkInsertUsers(ListUser users) { _db.FastestUser().BulkCopy(users); }异步操作SqlSugar全面支持异步操作可以避免阻塞线程。public async TaskListUser GetUsersAsync() { return await _db.QueryableUser().ToListAsync(); }查询缓存对于不经常变动的数据可以使用SqlSugar的缓存功能。public ListUser GetCachedUsers() { return _db.QueryableUser() .WithCache() .ToList(); }SQL调优利用SqlSugar的AOP功能记录SQL执行情况进行针对性优化。// 在DbManager的Initialize方法中添加 db.Aop.OnLogExecuting (sql, pars) { Console.WriteLine(sql); // 输出执行的SQL // 可以在这里记录慢查询 };4. 事务处理与错误管理4.1 基本事务使用数据库事务是保证数据一致性的关键机制。SqlSugar提供了简单的事务APIpublic bool TransferDepartment(int userId, int newDepartmentId) { try { _db.BeginTran(); // 更新用户部门 _db.UpdateableUser() .SetColumns(u u.DepartmentId newDepartmentId) .Where(u u.Id userId) .ExecuteCommand(); // 这里可以添加其他相关操作 // 例如更新部门人数统计等 _db.CommitTran(); return true; } catch { _db.RollbackTran(); return false; } }4.2 使用事务作用域.NET 8提供了更现代化的事务管理方式可以与SqlSugar结合使用using var transaction new TransactionScope(TransactionScopeAsyncFlowOption.Enabled); try { // 执行多个数据库操作 userService.UpdateUser(user1); userService.UpdateUser(user2); transaction.Complete(); } catch { // 事务会自动回滚 }4.3 错误处理最佳实践在与PostgreSQL交互时合理的错误处理非常重要public int SafeUpdateUser(User user) { try { return _db.Updateable(user).ExecuteCommand(); } catch (SqlSugar.SqlSugarException ex) { // 处理SqlSugar特定异常 Console.WriteLine($SQL执行错误: {ex.Message}); return -1; } catch (Npgsql.PostgresException pex) { // 处理PostgreSQL特定异常 Console.WriteLine($PostgreSQL错误[{pex.SqlState}]: {pex.Message}); return -2; } catch (Exception ex) { // 处理其他异常 Console.WriteLine($未知错误: {ex.Message}); return -99; } }4.4 重试机制对于可能因并发或临时问题导致失败的操作可以实现简单的重试机制public int UpdateWithRetry(User user, int maxRetries 3) { int retries 0; while (retries maxRetries) { try { return _db.Updateable(user).ExecuteCommand(); } catch { retries; if (retries maxRetries) throw; Thread.Sleep(100 * retries); // 指数退避 } } return 0; }在实际项目中根据具体需求可能需要更复杂的重试策略如考虑不同的错误类型、设置最大退避时间等。