Click here to Skip to main content
15,919,434 members
Articles / Web Development / ASP.NET / ASP.NET Core

Return DataTable Using Entity Framework

Rate me:
Please Sign up or sign in to vote.
4.89/5 (7 votes)
5 Apr 2020CPOL2 min read 42.7K   336   9   8
Select data as DataTable object for a database using Entity Framework
To get back the results in the form of a DataTable object in an application using Oracle database and Entity Framework EDMX, I needed to select data from unmapped shared views with an existing DB context instance. To do this, I wrote a small extension method for DbContext object as explained in this post.

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

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

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

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

Using Parameterized Query

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

Use Source Code With SQL-Server Db

Db Objects

Create Db Objects

SQL
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

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

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>

appsettings.json at WithEfCore:

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

  • 5th April, 2020: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Bangladesh Bangladesh
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralMy vote of 5 Pin
Eric Ouellet13-Feb-24 3:38
professionalEric Ouellet13-Feb-24 3:38 
AnswerRe: My vote of 5 Pin
DiponRoy14-Feb-24 2:18
DiponRoy14-Feb-24 2:18 
QuestionExcellent! Pin
EdTheBurger2-Aug-22 0:13
EdTheBurger2-Aug-22 0:13 
AnswerRe: Excellent! Pin
DiponRoy2-Aug-22 0:23
DiponRoy2-Aug-22 0:23 
QuestionHelpful extension but can you help with this error? Pin
Kraig_mn14-Oct-20 4:32
Kraig_mn14-Oct-20 4:32 
AnswerRe: Helpful extension but can you help with this error? Pin
Kraig_mn14-Oct-20 5:31
Kraig_mn14-Oct-20 5:31 
QuestionThanks for sharing! Pin
Carsten V2.05-Apr-20 22:29
Carsten V2.05-Apr-20 22:29 
AnswerRe: Thanks for sharing! Pin
DiponRoy5-Apr-20 23:24
DiponRoy5-Apr-20 23:24 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.