Return DataTable And Using Entity Framework

• Download source - 8.9 KB

Background

At a particular situation in an application, we had to get back the results in the form of a DataTable object. The application was using the Oracle database and Entity Framework EDMX. We had to select data from unmapped shared views with an existing DB context instance. To do so, I actually had written a little extension method for DbContext object as explained below.

Extension Method

We are going to create extension methods for DbContext considering both Entity Framework and Entity Framework Core. The extension methods will be invoked with:

• SQL query string
• Optional DbParameter objects, in case of use of parameterized query

Let's start writing the code.

Entity Framework

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

I tried to put the code to a common project, it actually depends on Microsoft.EntityFrameworkCore.Relational. By default, it is auto including with any provider DLL like Microsoft.EntityFrameworkCore.SqlServer.

Using the Extension Method

Using Regular Query

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

Using Parameterized Query

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" }
);

We are executing both stored procedures and queries, I believe functions will also work.

DbParameter Names for Different DB

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

Manage NULL Value

If we want to pass NULL value as parameterized query, we can mange things at two places

SqlParameter Level

C#

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

Extension Method

Added foreach loop just at the start of the method

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

Use Source Code With SQL-Server Db

Db Objects

Create Db Objects

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;

Drop Db Objects If Needed

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

Solution And Projects

It is a Visual Studio 2017 solution:

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

Change Connection Strings

App.config at 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 at WithEfCore:

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

Other Options

This does not use an entity, in fact, it goes around the entity using traditional SQL. Entity Framework supposed to maintain the state between the database and local data. In that case, we can convert a selected data list to a DataTable using this piece of code, Conversion Between DataTable and List in C#.

Limitations

The code may throw unexpected errors for untested inputs. If any, just let me know.

What is Next?

While working with the post, I found a thing called LINQ to SQL ObjectQuery.ToTraceString().

Going to work with it.

History

• 5^th April, 2020: Initial version