返回 DataTable 并使用 Entity Framework

• 下载源代码 - 8.9 KB

背景

在应用程序的特定情况下，我们需要以 DataTable 对象的形式返回结果。该应用程序使用 Oracle 数据库和 Entity Framework EDMX。我们需要从未映射的共享视图中选择数据，并使用现有的 DB 上下文实例。为此，我实际上编写了一个 DbContext 对象的扩展方法，如下所示。

扩展方法

我们将为 DbContext 创建扩展方法，同时考虑 Entity Framework 和 Entity Framework Core。这些扩展方法将使用以下内容调用：

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

让我们开始编写代码。

实体框架

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

public static class DbContextExtensions
{
    /*
     * need
        Only EntityFramework
     */
    public static DataTable DataTable(this DbContext context, string sqlQuery, 
                                      params DbParameter[] parameters)
    {
        DataTable dataTable = new DataTable();
        DbConnection connection = context.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(dataTable);
            }
        }
        return dataTable;
    }
}

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;

public static class DbContextExtensions
{
    /*
     * need
        Microsoft.EntityFrameworkCore
        Microsoft.EntityFrameworkCore.Relational
     */
    public static DataTable DataTable(this DbContext context, 
           string sqlQuery, params DbParameter[] parameters)
    {
        DataTable dataTable = new DataTable();
        DbConnection connection = context.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(dataTable);
            }
        }
        return dataTable;
    }
}

我尝试将代码放在一个公共项目中，它实际上依赖于 Microsoft.EntityFrameworkCore.Relational。默认情况下，它会自动包含在任何提供程序 DLL 中，例如 Microsoft.EntityFrameworkCore.SqlServer。

使用扩展方法

使用常规查询

var db = new MopDb();
DataTable allUser = db.DataTable("SELECT * FROM [dbo].[tbl_test_role]");

使用参数化查询

var db = new MopDb();

/*stored procedure*/
DataTable searchUser = db.DataTable(
    "EXEC sp_test_role @name = @paramName",
    new SqlParameter("paramName", SqlDbType.NVarChar) { Value = "sa" }
);

/*select query*/
DataTable likeUser = db.DataTable(
    "SELECT * FROM [dbo].[tbl_test_role] WHERE [name] LIKE '%' + @paramName +'%'",
    new SqlParameter("paramName", SqlDbType.NVarChar) { Value = "a" }
);

我们正在执行存储过程和查询，我相信函数也可以工作。

不同数据库的 DbParameter 名称

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

管理 NULL 值

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

SqlParameter 级别

C#

int? isActive = 1;
var param = new SqlParameter("paramIsActive", SqlDbType.Bit) { Value = isActive ?? (object)DBNull.Value, IsNullable = true }

扩展方法

在方法开始时添加了 foreach 循环

C#

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

使用带有 SQL-Server Db 的源代码

Db 对象

创建 Db 对象

CREATE TABLE [dbo].[tbl_test_role] (
    [id]   INT           IDENTITY (1, 1) NOT NULL,
    [name] NVARCHAR (50) NOT NULL,
    [details] NVARCHAR (150) NULL,
    PRIMARY KEY CLUSTERED ([id] ASC)
);
INSERT INTO [dbo].[tbl_test_role] (name)
VALUES ('admin'), ('sa'), ('user');


CREATE PROCEDURE sp_test_role @name nvarchar(30)
AS
BEGIN
    SELECT * FROM [dbo].[tbl_test_role]
    WHERE [name] = @name;
END;

如果需要，删除 Db 对象

DROP TABLE [dbo].[tbl_test_role];
DROP PROCEDURE sp_test_role;

解决方案和项目

这是一个 Visual Studio 2017 解决方案

• WithEf 是 .NET Framework 4.5
• WithEfCore 是 .NET Core 2.2

更改连接字符串

App.config 在 WithEf 中

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

appsettings.json 在 WithEfCore 中

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

其他选项

这不使用实体，事实上，它绕过实体使用传统的 SQL。Entity Framework 应该在数据库和本地数据之间维护状态。在这种情况下，我们可以使用这段代码将选定的数据列表转换为 DataTable，DataTable 和 List 之间的转换。

限制

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

接下来是什么？

在处理这篇文章时，我发现了一件事，叫做 LINQ to SQL ObjectQuery.ToTraceString()。

将要使用它。

历史

• 2020 年 4 月 5 日：初始版本