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

于 2025-05-21 18:46:59 发布
阅读量851 收藏 6
点赞数 17
分类专栏： C#开发基础 文章标签： 数据库 windows .net c#
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/houbincarson/article/details/148096525
版权

在这里插入图片描述

一、前言

欢迎关注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 : class
        where C : DBContextConnection<T>, new()
    {
        #region Member Variables
        private C _entities;
        #endregion
        #region Property
        protected C Context
        {
            get { return _entities; }
            set { _entities = value; }
        }
        #endregion
        #region Constructor
        public BaseService()
        {
            _entities   = new C();
            Database.SetInitializer<C>(null);
        }

        #endregion

        #region Generic Functions for CRUD with EF Implemented

        public 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 Implemented


        public 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 Methods

        private 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 T
                obj = Activator.CreateInstance<T>();
                foreach (DataColumn column in row.Table.Columns)
                {
                    columnName = column.ColumnName;
                    //Get property with same columnName
                    PropertyInfo prop = obj.GetType().GetProperty(columnName);
                    try
                    {
                        //Get value for the column
                        object value = (row[columnName].GetType() == typeof(DBNull))? null : row[columnName];

                        value  = ConvertToDataType(prop.PropertyType, value.ToString());

                        //Set property value
                        prop.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 模式实现的简化版本。

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

dotnet研习社

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值