65.9K
CodeProject 正在变化。 阅读更多。
Home

使用Entity Framework返回多个结果集或DataSet

<b>Dipon<font color="orange">Roy</font></b>
starIconstarIconstarIconstarIconstarIcon

5.00/5 (4投票s)

2024年6月29日

CPOL

2分钟阅读

viewsIcon

8746

downloadIcon

71

使用Entity Framework将数据选择为DataSet对象，并将结果映射到实体列表

背景

曾经，我们的应用程序需要进行许多数据库读取调用，以加载不同的数据列表来渲染特定的视图。结构上数据不同，但在逻辑上，它们都在数据库端执行着一些共同的操作，这增加了总体的执行时间。因此，我们的计划是避免多次数据库往返，并一次性获取所有内容。

标题听起来好像我们正在使用 Entity Framework 完成所有事情，但实际上，整个过程将在幕后使用 ADO.NET 和一些自定义映射器完成。此解决方案适用于使用单个 ORM（如 Entity Framework）的应用程序。从解决方案的角度来看，我们将在这里

  1. 在数据库中运行 SQL 查询，使用 Entity Framework 连接。
  2. 从查询结果填充 DataSet。
  3. 从 DataSet 中获取单独的 DataTable。
  4. 将每个 DataTable 映射到预期的实体列表 List<T>。

Entity Framework 方法

在这里，我们将为 DbContext 创建扩展方法，考虑 Entity Framework 和 Entity Framework Core。扩展方法需要两个参数

  • SQL 查询字符串
  • 可选的 DbParameter 对象，用于参数化查询

根据 SQL 查询，数据库将返回单个/多个结果表。在使用此扩展方法时，我们应该确保查询返回预期表集/集（带或不带行）。否则，我们必须相应地使用映射器。

实体框架

using System.Data;
using System.Data.Common;
using System.Data.Entity;

public static class DbContextExtensions
{
    public static DataSet DataSet(this DbContext context, string sqlQuery, params DbParameter[] parameters)
    {
        return context.Database.DataSet(sqlQuery, parameters);
    }

    public static DataSet DataSet(this Database database, string sqlQuery, params DbParameter[] parameters)
    {
        DataSet data = new DataSet();
        DbConnection connection = database.Connection;
        DbProviderFactory dbFactory = DbProviderFactories.GetFactory(connection);
        using (var cmd = dbFactory.CreateCommand())
        {
            cmd.Connection = connection;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = sqlQuery;
            if (parameters != null)
            {
                foreach (var item in parameters)
                {
                    cmd.Parameters.Add(item);
                }
            }
            using (DbDataAdapter adapter = dbFactory.CreateDataAdapter())
            {
                adapter.SelectCommand = cmd;
                adapter.Fill(data);
            }
        }
        return data;
    }
}

Entity Framework Core

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.Common;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;

public static class DbContextExtensions
{
    public static DataSet DataSet(this DbContext context, string sqlQuery, params DbParameter[] parameters)
    {
        return context.Database.DataSet(sqlQuery, parameters);
    }

    public static DataSet DataSet(this DatabaseFacade database, string sqlQuery, params DbParameter[] parameters)
    {
        DataSet data = new DataSet();
        DbConnection connection = database.GetDbConnection();
        DbProviderFactory dbFactory = DbProviderFactories.GetFactory(connection);
        using (var cmd = dbFactory.CreateCommand())
        {
            cmd.Connection = connection;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = sqlQuery;
            if (parameters != null)
            {
                foreach (var item in parameters)
                {
                    cmd.Parameters.Add(item);
                }
            }
            using (DbDataAdapter adapter = dbFactory.CreateDataAdapter())
            {
                adapter.SelectCommand = cmd;
                adapter.Fill(data);
            }
        }
        return data;
    }
}

DataTable 到 Entity List<T> 映射器

DataSet 将包含单个/多个结果集或表。此辅助方法将帮助我们将 DataTable 映射到实体列表 List<T>。此映射过程取决于

  • 实体属性和表的列的名称
  • 属性和列的数据类型

如果它们相同，则列的值将被映射到属性。大多数情况下，我将测试公共属性。

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;


public static class DataTableExtensions
{
    /*Converts DataTable To List*/
    public static List<TSource> ToList<TSource>(this DataTable dataTable) where TSource : new()
    {
        var dataList = new List<TSource>();

        const BindingFlags flags = BindingFlags.Public | BindingFlags.Instance | BindingFlags.NonPublic;
        var objFieldNames = (from PropertyInfo aProp in typeof(TSource).GetProperties(flags)
                             select new { aProp.Name, Type = Nullable.GetUnderlyingType(aProp.PropertyType) ?? aProp.PropertyType }).ToList();
        var dataTblFieldNames = (from DataColumn aHeader in dataTable.Columns
                                 select new { Name = aHeader.ColumnName, Type = aHeader.DataType }).ToList();
        var commonFields = objFieldNames.Intersect(dataTblFieldNames).ToList();

        foreach (DataRow dataRow in dataTable.AsEnumerable().ToList())
        {
            var aTSource = new TSource();
            foreach (var aField in commonFields)
            {
                PropertyInfo propertyInfos = aTSource.GetType().GetProperty(aField.Name);
                var value = dataRow[aField.Name] == DBNull.Value ? null : dataRow[aField.Name]; //if database field is nullable
                propertyInfos.SetValue(aTSource, value, null);
            }
            dataList.Add(aTSource);
        }
        return dataList;
    }
}

此映射过程不依赖于属性/列计数。这意味着我们没有强制 C# 模型类实现表/结果集的所有列。我们可以有任意数量的属性，但只有相似的属性才会根据名称和数据类型进行映射。此映射器实际上没有任何依赖性，我们可以使用任何其他库来提高效率。

数据模型

C# 实体

public class RoleModel
{
    public int Id { get; set; }
    public string Name { get; set; }
    public bool IsActive { get; set; }
}

数据库表

DROP TABLE IF EXISTS [dbo].[tb_Test_Role];
GO
CREATE TABLE [dbo].[tb_Test_Role] (
    [Id]   INT IdENTITY (1, 1) NOT NULL,
    [Name] NVARCHAR (50) NOT NULL,
    [IsActive] BIT NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

如果我们从这个表中选择所有内容，结构将类似于 C# 模型 RoleModel

数据库存储过程

DROP PROCEDURE IF EXISTS [dbo].[sp_Test_Role];
GO
CREATE TABLE [dbo].[tb_Test_Role] (
    [Id]   INT IdENTITY (1, 1) NOT NULL,
    [Name] NVARCHAR (50) NOT NULL,
    [IsActive] BIT NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);
GO
CREATE OR ALTER PROCEDURE [dbo].[sp_Test_Role](
    @Name VARCHAR(MAX) = ''
)
AS
BEGIN
/*return data in two different set*/
SELECT * FROM [tb_Test_Role] WHERE IsActive = 0 AND [Name] LIKE '%' + @Name +'%';    --inactive roles
SELECT * FROM [tb_Test_Role] WHERE IsActive = 1 AND [Name] LIKE '%' + @Name +'%';    --active roles
END

该过程选择了两次，产生两个结果集，每个集合都类似于 C# 模型 RoleModel

Data

GO
TRUNCATE TABLE [dbo].[tb_Test_Role]
INSERT INTO [dbo].[tb_Test_Role] ([Name], IsActive)
VALUES ('admin 0', 0), ('sa 0', 0), ('user 0', 0);
INSERT INTO [dbo].[tb_Test_Role] ([Name], IsActive)
VALUES ('admin 1', 1), ('sa 1', 1), ('user 1', 1);
GO


SELECT * FROM [dbo].[tb_Test_Role];
GO
EXEC sp_Test_Role '';

使用扩展方法

单个结果集/表

/*Query returning single result set or table*/
var db = new MopDb();
DataTable allRole = db.DataSet("SELECT * FROM [dbo].[tb_Test_Role]").Tables[0];
List<RoleModel> allRoleList = allRole.ToList<RoleModel>();

多个结果集/表

/*Stored procedure returning multiple result sets or tables*/
var db = new MopDb();
DataSet searchRole = db.DataSet(
    "EXEC [dbo].[sp_Test_Role] @paramName",
    new SqlParameter("paramName", SqlDbType.NVarChar) { Value = "" }
);
DataTable inactiveRole = searchRole.Tables[0];
DataTable activeRole = searchRole.Tables[1];
List<RoleModel> inactiveRoleList = inactiveRole.ToList<RoleModel>();
List<RoleModel> activeRoleList = activeRole.ToList<RoleModel>();

不同数据库的 DbParameter 名称

  • SqlServer: SqlParameter
  • Oracle: OracleParameter
  • MySql: MySqlParameter
  • PostgreSql: NpgsqlParameter

管理 NULL 值

如果我们要将 NULL 值作为参数化查询传递，我们可以在两个地方进行管理

SqlParameter 级别

int? isActive = 1;
DataSet filterRole = db.DataSet(
    "EXEC [dbo].[sp_Get_Roles] @paramIsActive",
    new SqlParameter("paramIsActive", SqlDbType.Bit) { Value = isActive ?? (object)DBNull.Value, IsNullable = true }
);

扩展方法

在方法的开头添加 foreach 循环

public static DataSet DataSet(this Database database, string sqlQuery, params DbParameter[] parameters)
{
    /*added to manage null values*/
    foreach (var parameter in parameters.Where(x => x.Value == null)) 
    {
        parameter.Value = DBNull.Value;
    }
}

其他

解决方案和项目

这是一个 Visual Studio 2022 解决方案

  • WithEf 是 .NET Framework 4.8
  • WithEfCore 是 .NET 6.0

期望此代码可以从 .NET Framework 4.5 和 .NET Core 2.2 开始工作

连接字符串

WithEf 中的 App.config

XML

<connectionStrings>
  <add name="MopDbConnection" connectionString="Data Source=10.10.15.13\DB002;
   Initial Catalog=TESTDB; PASSWORD=dhaka; USER ID=FSTEST;"
   providerName="System.Data.SqlClient" />
</connectionStrings>

WithEfCore 中的 appsettings.json

JavaScript

"ConnectionStrings": {
  "MopDbConnection": "server=10.10.15.13\\DB002;database=TESTDB;
                      user id=FSTEST;password=dhaka"
}

限制

该代码对于未经测试的输入可能会抛出意外错误。如果有，请告诉我。

历史

  • 2024 年 6 月 29 日：初始版本
© . All rights reserved.