What is the fastest way to fill a simple object from a DataTable?

Question

I was just wondering is there a faster or more efficient method to convert a DataTable to an object?

The method I am currently using is this:

public class Job {
    int JobID { get; set; }
    decimal JobCost { get; set; }

    Job(DataRow dr)
    {
        ID = Convert.ToInt32(dr["ID"]);
        if(dr["JobCost "] != DBNull.Value)
            JobCost = Convert.ToDecimal(dr["DelAmt"]);
    }
}

public static List<Job> FillObjects()
{
    DataTable dtJobs = JobController.GetJobTable();

    foreach (DataRow dr in dtJobs.Rows)
    {
        jobs.Add(new Job(dr));          
    }

    return jobs
}

This is an obviously simplified example, however this gets rather slow with many rows in the data table and many properties in the object. Is there a faster or more efficient method for doing something like this?

Thanks in advance for any replies!

Presumably the items in the datatable are actually ints and decimals. You should be *casting* them, not calling `Convert`. A cast is *far* simpler of an operation. Other than that, there really isn't any option other than going through every row, pulling out every single value, and assigning it to a field of an object. — Servy, Feb 05 '15 at 17:58
The items are *mostly* ints and decimals but there isnt always an exact relationship between the Database type and the variable type in code. (Disclamer: This is not my code, I have inherited the system). Some DB fields are strings that need to be converted to ints or datetimes, etc. — user3241130, Feb 05 '15 at 18:17
Then you should really fix that problem; storing numbers as strings in the database is going to cause any number of different types of problems, one of which is of course that all of your database queries need to spend the time to parse all of the query results. — Servy, Feb 05 '15 at 18:20
I agree. Unfortunately that ones going to be a hard sell to management. They are very much of "if it aint broke" mentality. Thanks again. — user3241130, Feb 05 '15 at 18:45

score 0 · Answer 1 · answered Feb 05 '15 at 18:03

0

Without utilizing Entity Framework your options are limited to something like you've already developed or using Linq to put them into their objects such as:

dtJobs.AsEnumerable().Select(x=> new Job{
                                   ID = x.Field<int>("ID"),
                                   JobCost = x.Field<Decimal>("DelAmt")
});

This will return an enumerable of Job objects, that you can then use to do whatever. One caveat with your current set up is that this will not allow for nulls to be returned. If you wanted to do this use a nullable decimal field and handle the null else where.

answered Feb 05 '15 at 18:03

aminner

359
3
10

Would using LINQ not be slower for this type of operation? I know about nullable types, but this is in an already existing system. Nullable types are not compatible with a lot of the existing function calls as you cannot pass an int? as an int. There would be a lot of rewriting to get that done. – user3241130 Feb 05 '15 at 18:19
@user3241130 It's not going to make a significant difference one way or the other. – Servy Feb 05 '15 at 18:19
Fair enough. Thanks for your answer. – user3241130 Feb 05 '15 at 18:28

tsells · Answer 2 · 2015-02-06T01:02:30.237

-1

If order is not important - you can do this using a parallel for each loop over the data rows. This will be much faster to process. One must be careful doing this on a server application though.

Note: I updated to lock on the list when adding to it. You can also use a collection from the concurrent collections instead of using a lock.

  class Program
{
    static void Main(string[] args)
    {
        var sync = new Object();
        var dt = new DataTable();  // fill data
        var jobs = new List<Job>();
        Parallel.ForEach(dt.AsEnumerable(), row =>
        {
            var job = JobFactory.GetJob(row);
            lock (sync)
            {
                jobs.Add(job);
            }
        });
    }
}

public class JobFactory
{
    public static Job GetJob(DataRow d)
    {
        var j = new Job();

        // do processing here from data row to fill job object
        return j;
    }
}

public class Job
{

}

edited Feb 06 '15 at 01:02

answered Feb 05 '15 at 18:15

tsells

2,751
1
18
20

`List` is not designed to be accessed from multiple threads at the same time. – Servy Feb 05 '15 at 18:21
There's no point in parallelizing it if you're just going to synchronize the majority of the work. The end result will almost certainly be *slower* than a serialized solution. – Servy Feb 06 '15 at 15:09
That really depends on how long it takes to load up each object from a data row versus how long the thread has to wait to dump the result in the tree. The only way to prove either theory is to test against the problem you are trying to solve. I have found in some instances that is slower, but others it's faster. YMMV!!!! – tsells Feb 06 '15 at 18:44

What is the fastest way to fill a simple object from a DataTable?

2 Answers2