告別重復代碼！C#泛型倉儲模式讓你的數據訪問層瞬間優雅10倍

作者：iamrick 2025-08-26 02:22:00

如果你正面臨這些痛點，那么今天這篇文章將徹底改變你的編程思維！?我將手把手教你構建一個基于SqlSugar的泛型倉儲模式，讓你的數據訪問層從此告別重復，擁抱優雅。

你是否還在為每個實體類都要寫一套增刪改查代碼而煩惱？是否厭倦了在不同Service層看到幾乎相同的數據操作邏輯？

如果你正面臨這些痛點，那么今天這篇文章將徹底改變你的編程思維！ 我將手把手教你構建一個基于SqlSugar的泛型倉儲模式，讓你的數據訪問層從此告別重復，擁抱優雅。

什么是泛型倉儲模式？為什么它如此重要？

傳統方式的痛點分析

在沒有使用倉儲模式之前，我們的代碼通常是這樣的：

public class UserService
{
    public async Task<List<User>> GetUsersAsync()
    {
        // 重復的數據庫操作代碼
        using var db = new SqlSugar.SqlSugarClient(config);
        return await db.Queryable<User>().ToListAsync();
    }

    public async Task<bool> AddUserAsync(User user)
    {
        // 又是重復的代碼...
        using var db = new SqlSugar.SqlSugarClient(config);
        return await db.Insertable(user).ExecuteCommandAsync() > 0;
    }
}

publicclass ProductService
{
    public async Task<List<Product>> GetProductsAsync()
    {
        using var db = new SqlSugar.SqlSugarClient(config);
        return await db.Queryable<Product>().ToListAsync();
    }
}

看到了嗎？這種寫法的問題顯而易見：

代碼重復率極高
修改邏輯需要改動多處
維護成本呈指數級增長
難以進行統一的日志記錄和性能監控

解決方案：構建通用泛型倉儲模式

Nuget 安裝以下庫

SqlSugarCore
Microsoft.Extensions.DependencyInjection
Microsoft.Extensions.Logging
Microsoft.Extensions.Logging.Console

實體類

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using SqlSugar;

namespace AppSQLBaseRepository
{
    [SugarTable("sys_user")]
    publicclass User
    {
        /// <summary>
        /// 用戶ID - 主鍵
        /// </summary>
        [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
        publicint Id { get; set; }

        /// <summary>
        /// 用戶名 - 唯一索引
        /// </summary>
        [SugarColumn(Length = 50, IsNullable = false)]
        [Required(ErrorMessage = "用戶名不能為空")]
        [StringLength(50, ErrorMessage = "用戶名長度不能超過50個字符")]
        publicstring UserName { get; set; }

        /// <summary>
        /// 密碼哈希值
        /// </summary>
        [SugarColumn(Length = 256, IsNullable = false)]
        [Required(ErrorMessage = "密碼不能為空")]
        publicstring PasswordHash { get; set; }

        /// <summary>
        /// 郵箱地址
        /// </summary>
        [SugarColumn(Length = 100, IsNullable = true)]
        [EmailAddress(ErrorMessage = "郵箱格式不正確")]
        [StringLength(100, ErrorMessage = "郵箱長度不能超過100個字符")]
        publicstring Email { get; set; }

        /// <summary>
        /// 手機號碼
        /// </summary>
        [SugarColumn(Length = 20, IsNullable = true)]
        [Phone(ErrorMessage = "手機號碼格式不正確")]
        [StringLength(20, ErrorMessage = "手機號碼長度不能超過20個字符")]
        publicstring PhoneNumber { get; set; }

        /// <summary>
        /// 真實姓名
        /// </summary>
        [SugarColumn(Length = 50, IsNullable = true)]
        [StringLength(50, ErrorMessage = "真實姓名長度不能超過50個字符")]
        publicstring RealName { get; set; }

        /// <summary>
        /// 頭像URL
        /// </summary>
        [SugarColumn(Length = 500, IsNullable = true)]
        [StringLength(500, ErrorMessage = "頭像URL長度不能超過500個字符")]
        publicstring Avatar { get; set; }

        /// <summary>
        /// 用戶狀態 (0:禁用 1:啟用)
        /// </summary>
        [SugarColumn(IsNullable = false)]
        publicint Status { get; set; } = 1;

        /// <summary>
        /// 是否刪除 (0:未刪除 1:已刪除) - 用于邏輯刪除
        /// </summary>
        [SugarColumn(IsNullable = false)]
        publicbool IsDeleted { get; set; } = false;

        /// <summary>
        /// 創建時間
        /// </summary>
        [SugarColumn(IsNullable = false)]
        public DateTime CreateTime { get; set; } = DateTime.Now;

        /// <summary>
        /// 更新時間
        /// </summary>
        [SugarColumn(IsNullable = true)]
        public DateTime? UpdateTime { get; set; }

        /// <summary>
        /// 刪除時間
        /// </summary>
        [SugarColumn(IsNullable = false)]
        public DateTime DeleteTime { get; set; }

        /// <summary>
        /// 最后登錄時間
        /// </summary>
        [SugarColumn(IsNullable = true)]
        public DateTime? LastLoginTime { get; set; }

        /// <summary>
        /// 創建人
        /// </summary>
        [SugarColumn(IsNullable = true)]
        publicstring? CreatedBy { get; set; }

        /// <summary>
        /// 更新人
        /// </summary>
        [SugarColumn(IsNullable = true)]
        publicstring? UpdatedBy { get; set; }


        /// <summary>
        /// 刪除人
        /// </summary>
        [SugarColumn(IsNullable = true)]
        publicstring? DeleteBy { get; set; }

        /// <summary>
        /// 備注信息
        /// </summary>
        [SugarColumn(Length = 500, IsNullable = true)]
        [StringLength(500, ErrorMessage = "備注信息長度不能超過500個字符")]
        publicstring Remark { get; set; }
    }
}

核心接口設計

首先，我們定義一個通用的倉儲接口：

其實Sqlsugar自帶的已經很牛了。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

namespace AppSQLBaseRepository
{

    public interface IBaseRepository<T> where T : class, new()
    {
        #region 查詢操作
        Task<T> GetFirstAsync(Expression<Func<T, bool>> whereExpression);
        Task<List<T>> GetListAsync(Expression<Func<T, bool>> whereExpression);
        Task<PageResult<T>> GetPageListAsync(
            Expression<Func<T, bool>> whereExpression,
            PageModel pageModel);
        #endregion

        #region 新增操作
        Task<bool> AddAsync(T entity);
        Task<bool> AddRangeAsync(List<T> entities);
        #endregion

        #region 更新操作
        Task<bool> UpdateAsync(T entity);
        Task<bool> UpdateAsync(
            Expression<Func<T, T>> updateExpression,
            Expression<Func<T, bool>> whereExpression);
        #endregion

        #region 刪除操作
        Task<bool> DeleteAsync(Expression<Func<T, bool>> whereExpression);
        Task<bool> FakeDeleteAsync(T entity, Action<T> setDeleteAction);
        #endregion

        #region 聚合操作
        Task<bool> IsExistAsync(Expression<Func<T, bool>> whereExpression);
        Task<int> CountAsync(Expression<Func<T, bool>> whereExpression);
        #endregion
    }
}

核心實現：BaseRepository

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Extensions.Logging;
using SqlSugar;

namespace AppSQLBaseRepository
{
    publicclass BaseRepository<T> : IBaseRepository<T> where T : class, new()
    {
        protected readonly ISqlSugarClient Db;
        private readonly ILogger<BaseRepository<T>> _logger;

        public BaseRepository(ISqlSugarClient sqlSugarClient, ILogger<BaseRepository<T>> logger)
        {
            Db = sqlSugarClient;
            _logger = logger;

            // 配置SQL日志 - 開發調試神器
            ConfigureSqlLog();
        }

        /// <summary>
        /// 配置SQL執行日志 - 性能監控和問題排查的利器
        /// </summary>
        private void ConfigureSqlLog()
        {
            Db.Aop.OnLogExecuting = (sql, pars) =>
            {
                // 記錄SQL執行日志，生產環境可以通過配置控制
                string logMessage = $"SQL執行: {sql}";

                // 記錄參數信息（調試時非常有用）
                if (pars?.Length > 0)
                {
                    var paramInfo = string.Join(", ",
                        pars.Select(p => $"{p.ParameterName}={p.Value}"));
                    logMessage += $" | 參數: {paramInfo}";
                }

                _logger.LogDebug(logMessage);
            };
        }

        #region 查詢實現

        publicvirtual async Task<T> GetFirstAsync(Expression<Func<T, bool>> whereExpression)
        {
            try
            {
                return await Db.Queryable<T>().Where(whereExpression).FirstAsync();
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查詢單個實體失敗");
                throw;
            }
        }

        publicvirtual async Task<List<T>> GetListAsync(Expression<Func<T, bool>> whereExpression)
        {
            try
            {
                return await Db.Queryable<T>().Where(whereExpression).ToListAsync();
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查詢實體列表失敗");
                throw;
            }
        }

        /// <summary>
        /// 分頁查詢實現 - 企業級應用的核心功能
        /// </summary>
        publicvirtual async Task<PageResult<T>> GetPageListAsync(
            Expression<Func<T, bool>> whereExpression,
            PageModel pageModel)
        {
            try
            {
                // ?? 使用SqlSugar的RefAsync獲取總數，一次查詢搞定！
                RefAsync<int> totalCount = 0;

                var list = await Db.Queryable<T>()
                    .Where(whereExpression)
                    .ToPageListAsync(
                        pageModel.PageIndex,
                        pageModel.PageSize,
                        totalCount);

                returnnew PageResult<T>
                {
                    PageIndex = pageModel.PageIndex,
                    PageSize = pageModel.PageSize,
                    TotalCount = totalCount,
                    Data = list
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "分頁查詢失敗");
                throw;
            }
        }

        #endregion

        #region 新增實現

        publicvirtual async Task<bool> AddAsync(T entity)
        {
            try
            {
                return await Db.Insertable(entity).ExecuteCommandAsync() > 0;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "添加實體失敗");
                throw;
            }
        }

        /// <summary>
        /// 批量新增 - 大數據量操作的性能優化
        /// </summary>
        publicvirtual async Task<bool> AddRangeAsync(List<T> entities)
        {
            try
            {
                // ?? 批量插入，性能比逐條插入快10倍以上！
                return await Db.Insertable(entities).ExecuteCommandAsync() > 0;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "批量添加實體失敗");
                throw;
            }
        }

        #endregion

        #region 更新實現

        publicvirtual async Task<bool> UpdateAsync(T entity)
        {
            try
            {
                return await Db.Updateable(entity).ExecuteCommandAsync() > 0;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "更新實體失敗");
                throw;
            }
        }

        publicvirtual async Task<bool> UpdateAsync(
            Expression<Func<T, T>> updateExpression,
            Expression<Func<T, bool>> whereExpression)
        {
            try
            {
                return await Db.Updateable<T>()
                    .SetColumns(updateExpression)
                    .Where(whereExpression)
                    .ExecuteCommandAsync() > 0;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "條件更新失敗");
                throw;
            }
        }

        #endregion

        #region 刪除實現

        publicvirtual async Task<bool> DeleteAsync(Expression<Func<T, bool>> whereExpression)
        {
            try
            {
                return await Db.Deleteable<T>().Where(whereExpression).ExecuteCommandAsync() > 0;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "刪除實體失敗");
                throw;
            }
        }

        /// <summary>
        /// 邏輯刪除 - 推薦做法，保證數據安全
        /// </summary>
        publicvirtual async Task<bool> FakeDeleteAsync(T entity, Action<T> setDeleteAction)
        {
            try
            {
                // ?? 執行自定義的刪除邏輯設置
                setDeleteAction(entity);
                return await UpdateAsync(entity);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "邏輯刪除失敗");
                throw;
            }
        }

        #endregion

        #region 聚合操作實現

        publicvirtual async Task<bool> IsExistAsync(Expression<Func<T, bool>> whereExpression)
        {
            try
            {
                return await Db.Queryable<T>().Where(whereExpression).AnyAsync();
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "檢查實體存在性失敗");
                throw;
            }
        }

        publicvirtual async Task<int> CountAsync(Expression<Func<T, bool>> whereExpression)
        {
            try
            {
                return await Db.Queryable<T>().Where(whereExpression).CountAsync();
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "統計實體數量失敗");
                throw;
            }
        }

        #endregion
    }
}

實戰應用：看看效果如何

分頁模型定義

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace AppSQLBaseRepository
{
    /// <summary>
    /// 分頁請求模型
    /// </summary>
    publicclass PageModel
    {
        publicint PageIndex { get; set; } = 1;
        publicint PageSize { get; set; } = 10;
    }

    /// <summary>
    /// 分頁結果模型
    /// </summary>
    publicclass PageResult<T>
    {
        publicint PageIndex { get; set; }
        publicint PageSize { get; set; }
        publicint TotalCount { get; set; }
        public List<T> Data { get; set; }

        /// <summary>
        /// 總頁數
        /// </summary>
        publicint TotalPages => (int)Math.Ceiling((double)TotalCount / PageSize);
    }
}

業務層使用示例

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace AppSQLBaseRepository
{
    publicclass UserService
    {
        private readonly IBaseRepository<User> _userRepository;

        public UserService(IBaseRepository<User> userRepository)
        {
            _userRepository = userRepository;
        }

        /// <summary>
        /// 獲取活躍用戶列表 - 一行代碼搞定！
        /// </summary>
        public async Task<List<User>> GetActiveUsersAsync()
        {
            return await _userRepository.GetListAsync(
                user => user.Status == 1 && user.IsDeleted == false);
        }

        /// <summary>
        /// 分頁獲取用戶 - 企業級應用標配
        /// </summary>
        public async Task<PageResult<User>> GetUsersPageAsync(PageModel pageModel)
        {
            return await _userRepository.GetPageListAsync(
                user => user.IsDeleted == false,
                pageModel);
        }

        /// <summary>
        /// 批量導入用戶 - 性能優化實踐
        /// </summary>
        public async Task<bool> BatchImportUsersAsync(List<User> users)
        {
            return await _userRepository.AddRangeAsync(users);
        }

        /// <summary>
        /// 邏輯刪除用戶 - 數據安全第一
        /// </summary>
        public async Task<bool> SoftDeleteUserAsync(User user)
        {
            return await _userRepository.FakeDeleteAsync(user, entity =>
            {
                entity.IsDeleted = true;
                entity.DeleteTime = DateTime.Now;
                entity.DeleteBy = "System"; 
            });
        }
    }
}

依賴注入配置

在Program.cs或Startup.cs中注冊服務：

using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using SqlSugar;

namespace AppSQLBaseRepository
{
    internal class Program
    {
        static async Task Main(string[] args)
        {
            // 服務容器
            var services = new ServiceCollection();

            // 添加日志服務
            services.AddLogging(builder =>
            {
                builder.AddConsole();
                builder.SetMinimumLevel(LogLevel.Information);
            });

            // 注冊SqlSugar
            services.AddSingleton<ISqlSugarClient>(provider =>
            {
                returnnew SqlSugarClient(new ConnectionConfig
                {
                    ConnectionString = "Server=localhost;Database=dbtest;User Id=sa;Password=123;TrustServerCertificate=true;",  // 應從配置文件讀取
                    DbType = DbType.SqlServer,
                    IsAutoCloseConnection = true
                });
            });

            // 注冊泛型倉儲
            services.AddScoped(typeof(IBaseRepository<>), typeof(BaseRepository<>));

            // 注冊業務服務
            services.AddScoped<UserService>();

            // 構建服務提供者
            var serviceProvider = services.BuildServiceProvider();

            // 實際使用UserService
            using (var scope = serviceProvider.CreateScope())
            {
                var userService = scope.ServiceProvider.GetRequiredService<UserService>();
                // 調用 userService 方法
                var list = await userService.GetUsersPageAsync(new PageModel { PageIndex = 1, PageSize = 10 });
                foreach (var item in list.Data)
                {
                    Console.WriteLine(item.UserName);
                    Console.WriteLine(item.Email);
                    Console.WriteLine(item.PhoneNumber);
                    Console.WriteLine(item.RealName);
                }
            }
        }
    }
}

圖片

進階技巧與最佳實踐

1. 性能優化建議

// ? 推薦：使用批量操作
await _repository.AddRangeAsync(largeUserList);

// ? 避免：循環單條操作
foreach(var user in largeUserList)
{
    await _repository.AddAsync(user); // 性能殺手！
}

2. 常見坑點提醒

?? 注意事項：

始終使用異步方法，提升應用吞吐量
大數據量操作時優先考慮批量方法
生產環境記得關閉SQL日志輸出
合理使用表達式樹，避免過于復雜的Lambda表達式

3. 擴展性設計

// ?? 金句：如果需要特殊業務邏輯，繼承BaseRepository即可
public class UserRepository : BaseRepository<User>, IUserRepository
{
    public UserRepository(ISqlSugarClient client, ILogger<UserRepository> logger) 
        : base(client, logger) { }

    // 添加用戶特有的業務方法
    public async Task<List<User>> GetUsersByDepartmentAsync(int departmentId)
    {
        return await Db.Queryable<User>()
            .Where(u => u.DepartmentId == departmentId)
            .OrderBy(u => u.CreateTime)
            .ToListAsync();
    }
}

總結：三大核心收益

通過實施這套泛型倉儲模式，你將獲得：

開發效率提升80%告別重復代碼，專注業務邏輯
代碼質量飛躍統一的數據訪問模式，降低bug率
維護成本驟降一處修改，全局生效，擴展性極強

收藏級代碼模板已經為你準備好了！ 這套方案已在多個企業級項目中驗證，能夠顯著提升團隊的開發效率和代碼質量。

責任編輯：武曉燕來源：技術老小子

C#泛型倉儲模式