How can I increase datatable performance?

Question

1.00/5 (1 vote)

See more:

I am using datatable and it takes too long to add datatable. How can I edit the datatable so that it doesn't run slowly or what can I use instead of the datatable?

How can I edit this code works very slow?

What I have tried:

public System.Data.DataTable Excels()
{
var employeeInf=myentities.Employee.Select(x=>new {
   ID=x.ID,
   Name=x.Name,
   Surname=x.Surname,
   Branch=x.Branch
}).ToList();

var dataTable=new System.Data.DataTable();
dataTable.Columns.Add("ID");
dataTable.Columns.Add("Name");
dataTable.Columns.Add("Surname");
dataTable.Columns.Add("Branch");

int i=1;

foreach(var t in employeeInf9
{
   var newRow=dataTable.NewRow();
   newRow[ID]=t.ID;
   newRow[ID]=t.Name;
   newRow[ID]=t.Surname;
   newRow[ID]=t.Branch;

  dataTable.Rows.Add(newRow);  i++;

}

Posted 19-May-20 10:29am

Member 14169626

Updated 21-May-20 8:26am

Garth J Lancaster

v2

Add a Solution

3 solutions

Add a Solution

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

Richard Deeming · Answer 1 · 2020-05-21T08:27:00

Here's a solution for Entity Framework 6, cobbled together from a couple of StackOverflow posts:
c# - Returning datatable using entity framework - Stack Overflow[^] - doesn't work with parameterized queries;
How can I get the parameters of an Entity Framework query? - Stack Overflow[^] - shows how to access the query parameters;

C#

using System;
using System.Data;
using System.Data.Common;
using System.Data.Entity;
using System.Data.Entity.Core.EntityClient;
using System.Data.Entity.Core.Objects;
using System.Data.Entity.Infrastructure;

namespace EntityFramework
{
    public static class DbContextExtensions
    {
        private static class ReflectionCache<T>
        {
            public static readonly PropertyInfo InternalQuery = typeof(DbQuery<T>).GetProperty("InternalQuery", BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public);
        }
        
        public static ObjectQuery<T> GetObjectQuery<T>(this IQueryable<T> source)
        {
            if (source is null) throw new ArgumentNullException(nameof(source));
            if (!(source is DbQuery<T> query)) throw new NotSupportedException();
            
            var internalQuery = ReflectionCache<T>.InternalQuery.GetValue(query, null);
            var objectQueryProperty = internalQuery.GetType().GetProperty("ObjectQuery", BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public);
            return (ObjectQuery<T>)objectQueryProperty.GetValue(internalQuery, null);
        }
        
        private static DbCommand ToDbCommand<T>(IQueryable<T> source, out DbProviderFactory dbFactory)
        {
            if (source is null) throw new ArgumentNullException(nameof(source));
            
            var objectQuery = source.GetObjectQuery();
            
            var connection = objectQuery.Context.Connection;
            if (connection is EntityConnection ec) connection = ec.StoreConnection;
            
            dbFactory = DbProviderFactories.GetFactory(connection);
            
            var command = dbFactory.CreateCommand();
            command.Connection = connection;
            command.CommandType = CommandType.Text;
            command.CommandText = objectQuery.ToTraceString();
            
            foreach (var parameter in objectQuery.Parameters)
            {
                var dbParameter = command.CreateParameter();
                dbParameter.ParameterName = parameter.Name;
                dbParameter.Value = parameter.Value;
                command.Parameters.Add(dbParameter);
            }
            
            return command;
        }
        
        public static DbCommand ToDbCommand<T>(this IQueryable<T> source)
        {
            return ToDbCommand(source, out _);
        }
        
        public static DataTable ToDataTable<T>(this IQueryable<T> source)
        {
            if (source is null) throw new ArgumentNullException(nameof(source));
            
            using (var command = ToDbCommand(source, out var dbFactory))
            using (var adapter = dbFactory.CreateDataAdapter())
            {
                adapter.SelectCommand = command;
                
                var dt = new DataTable();
                adapter.Fill(dt);
                return dt;
            }
        }
    }
}

With this helper class in place, your code becomes:

C#

public DataTable Excels()
{
    var employeeInf = myentities.Employee.Select(x => new
    {
        x.ID,
        x.Name,
        x.Surname,
        x.Branch
    }); // NB: No "ToList" call here.
    
    return employeeInf.ToDataTable();
}

Maciej Los · Answer 2 · 2020-05-19T20:42:00

Solution 2

I'd suggest to use DataTableExtensions.CopyToDataTable Method (System.Data) | Microsoft Docs[^] with DataTable.LoadDataRow Method (System.Data) | Microsoft Docs[^]
See:

C#

using System.Data;

public DataTable EmployeesDT
{
    DataTable dt =new DataTable();
    dt.Columns.Add("ID");
    dt.Columns.Add("Name");
    dt.Columns.Add("Surname");
    dt.Columns.Add("Branch");


    dt = employeeInf=myentities.Employee
        .Select(x=> dt.LoadDataRow(new object[]{x.ID, x.Name, x.Surname, x.Branch}, false))
        .CopyToDataTable();
    //done!

    return dt;
}

Posted 19-May-20 20:42pm

Maciej Los

Comments

Richard Deeming 21-May-20 13:44pm

I'm not convinced. :)

LoadDataRow creates a new DataRow and adds it to dt.

CopyToDataTable then creates a new DataTable instance, clones each row returned by the Select, and adds the cloned row to the new DataTable instance.

You then store the new DataTable in the dt variable, throwing away the old instance.

That's effectively loading the data twice, and temporarily using twice the memory.

Maciej Los 21-May-20 15:56pm

Well...
You can't load data into datatable without creating datatable previously. Neither without using CopyToDataTable() method.
;)

Richard Deeming 21-May-20 16:01pm

But you don't have to load it twice. :)

DataTable dt =new DataTable();
dt.Columns.Add("ID");
dt.Columns.Add("Name");
dt.Columns.Add("Surname");
dt.Columns.Add("Branch");

foreach (var item in myentities.Employee.Select(x => new { x.ID, x.Name, x.Surname, x.Branch }))
{
    dt.LoadDataRow(new object[] { item.ID, item.Name, item.Surname, item.Branch }, false);
}

return dt;

Maciej Los 21-May-20 16:15pm

OK. I got it!
Thanks, Richard.

Gerry Schmitz · Answer 3 · 2020-05-19T19:10:00

Solution 1

Looks like you're updating the same column in your for loop. Without a "record count" no one can tell what is "slow". And "ToList" here is redundant. You can try specifying "no tracking".

Posted 19-May-20 19:10pm

Gerry Schmitz

How can I increase datatable performance?

3 solutions

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0

How can I increase datatable performance?

3 solutions

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us