Monday, February 20, 2017

Converting a DataTable to objects in C#

By Steve Endow

Let's say you have a C# Dynamics GP integration that retrieves Customer records from a staging table and you need to convert each row to a Customer object so that you can more easily work with the data.

You could loop through the rows in your DataTable, read every field in the row, and assign each field value to each corresponding object property.  But that would be tedious, particularly if your object has dozens of properties.  And it's very low value coding.

After having done this a few times, I got sick of all of the typing and figured there had to be a better way.  Of course there is.  Fortunately a very smart person posted some code that handles this task very well, using Reflection.

https://codereview.stackexchange.com/questions/30714/converting-datatable-to-list-of-class

It works incredibly well and requires all of 1 or 2 lines of code to use.

I made a few different variations, allowing me to use it with a DataTable, DataRow, and DataRow arrays.


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using System.Data;

namespace TSM.Integration.Library
{
    static class ObjectMapper
    {
        ///

        /// Converts a DataTable to a list with generic objects
        ///
        /// Generic object
        /// DataTable
        /// List with generic objects
        public static List DataTableToList(this DataTable table) where T : class, new()
        {
            //From: https://codereview.stackexchange.com/questions/30714/converting-datatable-to-list-of-class

            try
            {
                List list = new List();

                foreach (var row in table.AsEnumerable())
                {
                    T obj = new T();

                    foreach (var prop in obj.GetType().GetProperties())
                    {
                        try
                        {
                            PropertyInfo propertyInfo = obj.GetType().GetProperty(prop.Name);
                            propertyInfo.SetValue(obj, Convert.ChangeType(row[prop.Name], propertyInfo.PropertyType), null);
                        }
                        catch
                        {
                            continue;
                        }
                    }

                    list.Add(obj);
                }

                return list;
            }
            catch
            {
                return null;
            }
        }


        public static T DataRowToList(this DataRow row) where T : class, new()
        {
            //Variant of DataTableToList code from: https://codereview.stackexchange.com/questions/30714/converting-datatable-to-list-of-class

            try
            {
                List list = new List();

                T obj = new T();

                foreach (var prop in obj.GetType().GetProperties())
                {
                    try
                    {
                        PropertyInfo propertyInfo = obj.GetType().GetProperty(prop.Name);
                        propertyInfo.SetValue(obj, Convert.ChangeType(row[prop.Name], propertyInfo.PropertyType), null);
                    }
                    catch
                    {
                        continue;
                    }
                }

                return obj;
            }
            catch
            {
                return null;
            }
        }


        public static List DataRowArrayToList(this DataRow[] dataRowArray) where T : class, new()
        {
            //Variant of DataTableToList code from: https://codereview.stackexchange.com/questions/30714/converting-datatable-to-list-of-class

            try
            {
                List list = new List();

                foreach (var row in dataRowArray.AsEnumerable())
                {
                    T obj = new T();

                    foreach (var prop in obj.GetType().GetProperties())
                    {
                        try
                        {
                            PropertyInfo propertyInfo = obj.GetType().GetProperty(prop.Name);
                            propertyInfo.SetValue(obj, Convert.ChangeType(row[prop.Name], propertyInfo.PropertyType), null);
                        }
                        catch
                        {
                            continue;
                        }
                    }

                    list.Add(obj);
                }

                return list;
            }
            catch
            {
                return null;
            }
        }

    } 
}



You can also find him on Twitter, YouTube, and Google+










No comments: