当前位置： 首页 > java >正文

使用泛型服务基类简化Entity Framework对数据库访问逻辑

java 2025/8/18 23:22:08

在这里插入图片描述

一、前言

欢迎关注dotnet研习社，今天我们讨论的主题是使用泛型服务基类简化EntityFramework对数据库访问逻辑。

在系统开发中，数据访问逻辑往往会重复大量的增删改查（CRUD）操作。为了提升开发效率和统一编码规范，我们可以借助泛型和抽象类的组合，封装通用的数据服务基类。
本文将介绍一个基于 Entity Framework 的通用服务基类：BaseService<C, T>，并讲解其用法及设计理念。

二、设计目标

BaseService<C, T> 旨在：

  • 封装常用的 CRUD 操作；
  • 支持泛型实体类型 TEntity 和数据上下文类型 DbContext；
  • 支持原生 SQL语句 / 存储过程操作；
  • 提供常见辅助方法如 DataTable 转换等；
  • 统一释放资源，实现 IDisposable。

三、接口定义概览

public interface IBaseService<T> : IDisposable where T : class
{// 数据连接验证bool IsConnectionValid();// 新增操作void Add(T entity);void Add(List<T> entity);// 更新操作void Update(T entity);void Update(List<T> entity);// 删除操作void Delete(T entity);void Delete(List<T> entity);// 按主键获取实体T GetById(long Id);T GetById(Guid Id);T GetById(params object[] paramValue);// 查询操作IEnumerable<T> GetAll();IEnumerable<T> GetMany(Func<T, bool> where);// 提交变更void Save();// DataTable 与实体类互转DataTable ConvertToDataTable(IList<T> model);IList<T> ConvertToList(DataTable dtTable);// 存储过程执行相关DataTable ExecuteSelectDataTableStoredProc(string commandName, string tableName, params object[] param);DataSet ExecuteSelectDataSetStoredProc(string commandName, Dictionary<string, object> param);int ExecuteSelectCount(string commandName, params object[] param);void ExecuteStoredProcedure(string commandName, params object[] param);void ExecuteStoredProcedure(string commandName, Dictionary<string, string> param);void ExecuteStoredProcedure(string commandName, Dictionary<string, object> param);IEnumerable<T> ExecuteSelectStoredProc(string commandName, Dictionary<string, string> param);// 参数映射辅助方法Dictionary<string, string> CreateParameters(DataRow dr);// 表结构读取DataTable GetSchema(string tableName);// 数据库连接获取DbConnection GetConection();
}

四、类定义与泛型约束

 public class DBContextConnection<T> : DbContext where T : class{public DBContextConnection() : base("name = Connection"){}public DbSet<T> Model { get; set; }}

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.Data.Entity;
using System.Data.Entity.ModelConfiguration;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;namespace DataService.Implementations
{public abstract class BaseService<C, T> : EntityTypeConfiguration<T>, IBaseService<T>, IDisposable where T : classwhere C : DBContextConnection<T>, new(){#region Member Variablesprivate C _entities;#endregion#region Propertyprotected C Context{get { return _entities; }set { _entities = value; }}#endregion#region Constructorpublic BaseService(){_entities   = new C();Database.SetInitializer<C>(null);}#endregion#region Generic Functions for CRUD with EF Implementedpublic virtual bool IsConnectionValid(){bool isDBExist  = false;try{if (this.Context.Database.Exists()){isDBExist   = true;}}catch(Exception){isDBExist   = false;}return isDBExist;}public virtual void Add(T entity){_entities.Set<T>().Add(entity);Save();}public virtual void Add(List<T> items){foreach (T item in items){_entities.Set<T>().Add(item);}Save();}public virtual void Update(T entity){_entities.Entry(entity).State = EntityState.Modified;Save();}public virtual void Update(List<T> items){foreach (T item in items){_entities.Entry(item).State = EntityState.Modified;}Save();}public virtual void Delete(T entity){_entities.Entry(entity).State = EntityState.Deleted;Save();}public virtual void Delete(List<T> items){foreach (T item in items){_entities.Entry(item).State = EntityState.Deleted;}Save();}public virtual T GetById(long id){return _entities.Set<T>().Find(id);}public virtual T GetById(Guid id){return _entities.Set<T>().Find(id);}public virtual T GetById(params object[] paramValue){return _entities.Set<T>().Find(paramValue);}public virtual IEnumerable<T> GetAll(){IEnumerable<T> query = _entities.Set<T>();return query;}public DataTable ConvertToDataTable(IList<T> model){PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));DataTable                    dtTable    = new DataTable (typeof(T).Name);foreach (PropertyDescriptor  prop in properties){if (!typeof(T).GetProperty(prop.Name).GetAccessors()[0].IsVirtual){dtTable.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);}}foreach (T item in model){DataRow row = dtTable.NewRow();foreach (PropertyDescriptor prop in properties){if (!typeof(T).GetProperty(prop.Name).GetAccessors()[0].IsVirtual){row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;}}dtTable.Rows.Add(row);}return dtTable;}public IList<T> ConvertToList (DataTable dtTable){if(dtTable == null){return null;}IList<DataRow> rows  = new  List<DataRow>();foreach (DataRow row in dtTable.Rows){rows.Add(row);}return ConvertTo(rows);}public virtual IEnumerable<T> GetMany(Func<T, bool> where){return _entities.Set<T>().Where(where).ToList();}public virtual void Save(){_entities.SaveChanges();}private bool disposed = false;protected virtual void Dispose(bool disposing){if (!this.disposed)if (disposing)_entities.Dispose();this.disposed = true;}public void Dispose(){Dispose(true);}#endregion#region  Generic Functions for CRUD Operations using SP Implementedpublic virtual void ExecuteStoredProcedure(string commandName, params object[] param){SqlParameter[] sqlParam    = AddParameters(param);_entities.Database.ExecuteSqlCommand(commandName, sqlParam);}public virtual void ExecuteStoredProcedure(string commandName, Dictionary<string,string> param){SqlParameter[] sqlParam     = AddParameters(param);int rowsChanged             = _entities.Database.ExecuteSqlCommand(commandName, sqlParam);}public virtual IEnumerable<T> ExecuteSelectStoredProc(string commandName, Dictionary<string, string> param){SqlParameter[] sqlParam = AddParameters(param);IEnumerable<T> entities = _entities.Database.SqlQuery<T>(commandName, sqlParam);return entities;}public  virtual T ExecuteSelectEntityStoredProc(string commandName, params object[] param){var entities = _entities.Database.SqlQuery<T>(commandName, param).FirstOrDefault();return entities;}[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100")]public virtual DataTable ExecuteSelectDataTableStoredProc(string commandName, string tableName, params object[] param){var       con = ((SqlConnection)_entities.Database.Connection);DataTable dt  = new DataTable(tableName);using (SqlCommand cmd = new SqlCommand(commandName, con)){if (con != null && con.State == ConnectionState.Closed){con.Open();}AddParameters(cmd, param);using (SqlDataAdapter adapter = new SqlDataAdapter(cmd)){adapter.Fill(dt);}}return dt;}[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100")]public virtual void ExecuteStoredProcedure(string commandName, Dictionary<string, object> param){var con = ((SqlConnection)_entities.Database.Connection);using (SqlCommand cmd = new SqlCommand(commandName, con)){if (con != null && con.State == ConnectionState.Closed){con.Open();}AddParameters3(cmd, param);cmd.ExecuteNonQuery();}}[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100")]public virtual DataSet ExecuteSelectDataSetStoredProc(string commandName, Dictionary<string, object> param){var       con = ((SqlConnection)_entities.Database.Connection);DataSet   dt  = new DataSet();using (SqlCommand cmd = new SqlCommand(commandName, con)){if (con != null && con.State == ConnectionState.Closed){con.Open();}AddParameters3(cmd, param);using (SqlDataAdapter adapter = new SqlDataAdapter(cmd)){adapter.Fill(dt);}}return dt;}public virtual int ExecuteSelectCount(string commandName,params object [] param){int            count    = 0 ;SqlParameter[] sqlParam = AddParameters(param);count = _entities.Database.SqlQuery<int>(commandName,sqlParam).ToList().FirstOrDefault();return count;}public virtual void ExecuteInsertUpdateUsingDataTable(DataTable dtInput, string commandName){}public Dictionary<string, string> CreateParameters(DataRow dr){// Locals.string                    value = null;Dictionary<string,string> dict  = new Dictionary<string, string>();foreach (DataColumn item in dr.Table.Columns){if(item.DataType == typeof(DateTime)  && DBNull.Value !=  dr[item.ColumnName]){value = ((DateTime)dr[item.ColumnName]).ToString("MM-dd-yyyy HH:mm:ss.fff");}else{value = dr[item.ColumnName].ToString();}dict.Add(item.ColumnName, value);}return dict;}[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100")]public DataTable GetSchema(string tableName){// Locals.DataTable    table  = null;DbCommand    cmd    = null;DbDataReader reader = null;if (_entities.Database.Connection.State != ConnectionState.Open){_entities.Database.Connection.Open();}table = new DataTable(tableName);using (cmd = _entities.Database.Connection.CreateCommand()){// This query is safe as it is the tableName which is the dynhamic param.cmd.CommandText = "SELECT TOP 0 * FROM [" + tableName + "]";reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly);table  = new DataTable(tableName);table.Load(reader);reader.Dispose();}return table;}public DbConnection GetConection(){return _entities.Database.Connection;}#endregion#region Private Methodsprivate void AddParameters(SqlCommand cmd, params object[] param ){// Locals.int          index    = 0;SqlParameter sqlParam = null;foreach (var item in param){sqlParam        = new SqlParameter (string.Format("@{0}",index),item);cmd.Parameters.Add(sqlParam);index++;}}private SqlParameter[] AddParameters(Dictionary<string,string> param){// Locals.SqlParameter[] sqlParam = new SqlParameter[param.Count];int            index    = 0;foreach (var item in param){sqlParam[index]                =  new SqlParameter();sqlParam[index].ParameterName  =  string.Format("@{0}", item.Key);sqlParam[index].Value          = item.Value;index++;}  return sqlParam;}private void AddParameters3(SqlCommand cmd, Dictionary<string, object> param){// Locals.SqlParameter sqlParam = null;foreach (var item in param){sqlParam                = new SqlParameter();sqlParam.ParameterName  = item.Key;sqlParam.Value          = item.Value;cmd.Parameters.Add(sqlParam);}}private SqlParameter[] AddParameters(params object[] param){// Locals.SqlParameter[] sqlParam = new SqlParameter[param.Length];int            index    = 0;foreach (var pValue in param){sqlParam[index]                =  new SqlParameter();sqlParam[index].ParameterName  =  string.Format("@{0}", index);sqlParam[index].Value          = pValue;index++;}  return sqlParam;}private IList<T> ConvertTo(IList<DataRow> rows){// Locals.IList<T> list = null;if (rows != null){list = new List<T>();foreach (DataRow row in rows){T item = CreateItem(row);list.Add(item);}}return list;}private  T CreateItem(DataRow row){// Locals.string columnName    = string.Empty;T      obj           = default(T);if (row != null){//Create the instance of type Tobj = Activator.CreateInstance<T>();foreach (DataColumn column in row.Table.Columns){columnName = column.ColumnName;//Get property with same columnNamePropertyInfo prop = obj.GetType().GetProperty(columnName);try{//Get value for the columnobject value = (row[columnName].GetType() == typeof(DBNull))? null : row[columnName];value  = ConvertToDataType(prop.PropertyType, value.ToString());//Set property valueprop.SetValue(obj, value, null);}catch (Exception ex){throw ex;//TODO}}}return obj;}private object ConvertToDataType(Type dataType,string propValue)  {// Locals.object returnType;if (dataType.IsGenericType && dataType.GetGenericTypeDefinition() == typeof(Nullable<>)){if (String.IsNullOrEmpty(propValue))returnType = null;else if(dataType.GetGenericArguments()[0] == typeof(Guid)){returnType = ConvertToGuid(propValue);}else{returnType = Convert.ChangeType(propValue, dataType.GetGenericArguments()[0]);}}else if(dataType == typeof(Guid)){returnType  = ConvertToGuid(propValue);}else{returnType = Convert.ChangeType(propValue, dataType);}return returnType;}private Guid ConvertToGuid(string value){Guid uidType;Guid.TryParse(value, out uidType);return uidType;}#endregion}
}

泛型约束说明：

  • T 必须为类（通常是实体模型）；
  • C 必须继承自 DBContextConnection<T>，并具有无参构造函数；

该类继承自 EntityTypeConfiguration<T>，意味着它还可以作为 Fluent API 的配置类使用。

五、核心功能详解

1. EF 标准 CRUD 操作

public virtual void Add(T entity) { ... }
public virtual void Update(T entity) { ... }
public virtual void Delete(T entity) { ... }
public virtual T GetById(long id) { ... }
public virtual IEnumerable<T> GetAll() { ... }

这些方法封装了最常见的数据操作，使得派生类可以直接使用，避免重复编写 DbSet<T> 访问代码。

支持单个实体和集合操作：

Add(List<T> items)
Update(List<T> items)
Delete(List<T> items)

2. 存储过程操作支持

对于复杂查询或事务操作，可以使用 ExecuteStoredProcedure 系列方法调用数据库中的存储过程。

示例：

ExecuteStoredProcedure("sp_UpdateEntity", new Dictionary<string, string> {{ "Id", "123" }, { "Name", "New Name" }
});

支持 DataTable/DataSet 返回：

DataTable dt = ExecuteSelectDataTableStoredProc("sp_GetData", "ResultTable", paramArray);
DataSet ds   = ExecuteSelectDataSetStoredProc("sp_GetMultiTable", paramDict);

3. 数据转换工具

在处理报表导出或中间层数据转换时，常需在实体对象与 DataTable 之间转换：

public DataTable ConvertToDataTable(IList<T> model)
public IList<T> ConvertToList(DataTable dtTable)

这些方法通过反射自动生成列与实体之间的映射，省去了人工手动转换的麻烦。

4. 辅助方法

public DataTable GetSchema(string tableName)
public DbConnection GetConection()
  • GetSchema 获取数据库中某张表的列结构；
  • GetConection 便于手动控制连接或与 ADO.NET 结合使用。

5. 连接有效性检测

public virtual bool IsConnectionValid()

此方法可用于健康检测，适合部署环境下用于判断数据库是否可连接。

6. 资源释放

类实现了 IDisposable 接口，保证上下文在使用完后及时释放：

public void Dispose()
{Dispose(true);
}

推荐使用 using 语法自动释放：

using (var svc = new YourEntityService())
{var list = svc.GetAll();
}

六、示例程序用法

在这里插入图片描述

  1. 定义实体和上下文
[Table("User")]
public class User
{public int Id { get; set; }public string Name { get; set; }public DateTime CreateTime { get; set; }
}public class UserDbContext : DBContextConnection<User>
{// 可根据需要扩展
}
  1. 实现具体服务

引入EntityFramework

<packages><package id="EntityFramework" version="6.5.1" targetFramework="net48" />
</packages>

数据库连接配置

<?xml version="1.0" encoding="utf-8"?>
<configuration><configSections><section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" /></configSections><startup><supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.8" /></startup><entityFramework><providers><provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" /></providers></entityFramework><connectionStrings><add name="Connection"connectionString="Server=localhost;Database=TestDB;Trusted_Connection=True;TrustServerCertificate=True;"providerName="System.Data.SqlClient" /></connectionStrings>
</configuration>

public class UserService : BaseService<UserDbContext, User>
{// 可扩展自定义方法
}
  1. 使用服务进行CRUD操作
using (var userService = new UserService())
{// 新增var user = new User { Name = "张三", CreateTime = DateTime.Now };userService.Add(user);// 查询var allUsers = userService.GetAll();foreach (var item in allUsers){Console.WriteLine(item.Name);}// 更新user.Name = "张三2";userService.Update(user);// 删除userService.Delete(user);// 按条件查询var filtered = userService.GetMany(u => u.Name.Contains("三"));
}

在这里插入图片描述

  1. 调用存储过程
// 假设有存储过程 sp_UpdateUser
var param = new Dictionary<string, string>
{{ "Id", "1" },{ "UserName", "李四" }
};
userService.ExecuteStoredProcedure("sp_UpdateUser", param);// 查询存储过程返回的DataTable
DataTable dt = userService.ExecuteSelectDataTableStoredProc("sp_GetUsers", "UserTable", new object[] { });
  1. 实体与DataTable互转
// 实体转DataTable
DataTable table = userService.ConvertToDataTable(allUsers.ToList());
// DataTable转实体列表
IList<User> userList = userService.ConvertToList(table);

七、总结

通过 BaseService<C, T> 的设计，我们能有效地：

  • 减少重复代码；
  • 标准化服务接口；
  • 提高系统的可维护性与可测试性；

它是一种 Clean Architecture 中常见的 Repository 模式实现的简化版本。

http://www.xdnf.cn/news/7978.html

相关文章：

  • 基于JDBC的信息管理系统，那么什么是JDBC呢？什么又是DAO类？
  • Python输出与输入
  • windows服务器部署jenkins工具（二）
  • 在linux部署定时执行Kettle任务
  • 领麦微红外测温传感器：即热式饮水机测温应用
  • I.MX6U Mini开发板通过GPIO口测试光敏传感器
  • 无人机电子防抖技术要点概述！
  • 无人机集成毫米波雷达与双目视觉的融合感知系统深度解析
  • 全碳化硅功率模块开关瞬态特性及损耗研究
  • Java学习教程（附电子书资料50+册）
  • 多模态大模型
  • 将YOLO训练进程放至后台的方法-nohup
  • Oracle BUFFER CACHE内存不足的优化思路
  • 【信息系统项目管理师】第13章：项目资源管理 - 38个经典题目及详解
  • SEO关键词优化与长尾词布局
  • Powershell及命令行文章合集（不定期更新）
  • [Docker排查] 镜像拉取/容器启动/网络不通？Docker常见错误与解决方案
  • ERP 与 WMS 对接深度解析：双视角下的业务与技术协同
  • Linux系统之----文件及缓冲区
  • 实验4 插入数据
  • ETL工具部署的5个注意事项
  • 风车聊天室nodejs环境即可无需数据库
  • 适合初学者的 Blender和怎么下载 Blender格式模型
  • 项目优先级不清，如何合理分配资源？
  • Baumer工业相机堡盟工业相机的工业视觉如何进行药板缺陷检测
  • LeetCode数组-移除元素
  • 力扣热题——零数组变换 ||
  • C++（26）： 标准库 ＜iterator＞
  • 使用亮数据代理IP+Python爬虫批量爬取招聘信息训练面试类AI智能体（实战指南）
  • 百度地图的地铁图API所有城市的城市名和citycode的对照关系列表