How to Convert DataRow to an Object

Question

I created a DataRow on my project:

DataRow datarow;

I want to convert this DataRow to any Type of Object. How could I do it?

Answer 1

This is a pretty cool way I use it.

    public static T ToObject<T>(this DataRow dataRow)
    where T : new()
    {
        T item = new T();

        foreach (DataColumn column in dataRow.Table.Columns)
        {
            PropertyInfo property = GetProperty(typeof(T), column.ColumnName);

            if (property != null && dataRow[column] != DBNull.Value && dataRow[column].ToString() != "NULL")
            {
                property.SetValue(item, ChangeType(dataRow[column], property.PropertyType), null);
            }
        }

        return item;
    }

    private static PropertyInfo GetProperty(Type type, string attributeName)
    {
        PropertyInfo property = type.GetProperty(attributeName);

        if (property != null)
        {
            return property;
        }

        return type.GetProperties()
             .Where(p => p.IsDefined(typeof(DisplayAttribute), false) && p.GetCustomAttributes(typeof(DisplayAttribute), false).Cast<DisplayAttribute>().Single().Name == attributeName)
             .FirstOrDefault();
    }

    public static object ChangeType(object value, Type type)
    {
        if (type.IsGenericType && type.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
        {
            if (value == null)
            {
                return null;
            }

            return Convert.ChangeType(value, Nullable.GetUnderlyingType(type));
        }

        return Convert.ChangeType(value, type);
    }

Answer 2

I Have found one solution for my application.

    // function that creates an object from the given data row
    public static T CreateItemFromRow<T>(DataRow row) where T : new()
    {
        // create a new object
        T item = new T();

        // set the item
        SetItemFromRow(item, row);

        // return 
        return item;
    }

    public static void SetItemFromRow<T>(T item, DataRow row) where T : new()
    {
        // go through each column
        foreach (DataColumn c in row.Table.Columns)
        {
            // find the property for the column
            PropertyInfo p = item.GetType().GetProperty(c.ColumnName);

            // if exists, set the value
            if (p != null && row[c] != DBNull.Value)
            {
                p.SetValue(item, row[c], null);
            }
        }
    }

This will map your DataRow to ViewModel, Like below.

Your_ViewModel model = CreateItemFromRow<Your_ViewModel>(row);

Answer 3

class Person{
public string FirstName{get;set;}
public string LastName{get;set;}
}

Person person = new Person();
person.FirstName = dataRow["FirstName"] ;
person.LastName = dataRow["LastName"] ;

or

Person person = new Person();
person.FirstName = dataRow.Field<string>("FirstName");
person.LastName = dataRow.Field<string>("LastName");

Answer 4

Similar to some of the previous approaches, I created this extension method for DataRow which takes an argument object to be populated. Main difference is that in addition to populating object's Properties, it also populates Fields of given object. This should also work for simpler structures (Though I only tested on objects).

public static T ToObject<T>( this DataRow dataRow )
     where T : new() {
    T item = new T();
    foreach( DataColumn column in dataRow.Table.Columns ) {
        if( dataRow[column] != DBNull.Value ) {
            PropertyInfo prop = item.GetType().GetProperty( column.ColumnName );
            if( prop != null ) {
                object result = Convert.ChangeType( dataRow[column], prop.PropertyType );
                prop.SetValue( item, result, null );
                continue;
            }
            else {
                FieldInfo fld = item.GetType().GetField( column.ColumnName );
                if( fld != null ) {
                    object result = Convert.ChangeType( dataRow[column], fld.FieldType );
                    fld.SetValue( item, result );
                }
            }
        }
    }
    return item;
}

You can put this code in your current class or in a global static class. It needs following namespaces...

using System;
using System.Data;
using System.Reflection;

Usage is as simple as...

MyClassName obj = dataRow.ToObject<MyClassName>()

Answer 5

Here is an extension method that would allow you to convert a DataRow to a given object.

public static class DataRowExtensions
{
    public static T Cast<T>(this DataRow dataRow) where T : new()
    {
        T item = new T();

        IEnumerable<PropertyInfo> properties = item.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public)
                                                             .Where(x => x.CanWrite);

        foreach (DataColumn column in dataRow.Table.Columns)
        {
            if (dataRow[column] == DBNull.Value)
            {
                continue;
            }

            PropertyInfo property = properties.FirstOrDefault(x => column.ColumnName.Equals(x.Name, StringComparison.OrdinalIgnoreCase));

            if (property == null)
            {
                continue;
            }

            try
            {
                Type t = Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType;

                object safeValue = (dataRow[column] == null) ? null : Convert.ChangeType(dataRow[column], t);

                property.SetValue(item, safeValue, null);
            }
            catch
            {
                throw new Exception($"The value '{dataRow[column]}' cannot be mapped to the property '{property.Name}'!");
            }

        }

        return item;
    }
}

And you can use the above extension method like so

foreach (DataRow row in dataTable.Rows)
{
    SomeClassType obj = row.Cast<SomeClassType>();
    // do something with your object
}

Answer 6

Given Converter<TIn, TOut> is a delegate, then the following should work:

List<Person> personList = new List<Person>();

personList = ConvertDataRowToList(ds, (row) => {
    return new Person
    {
        FirstName = row["FirstName"],
        LastName  = row["LastName"]
        // Rest of properties should assign here...
    };
});

https://learn.microsoft.com/en-us/dotnet/api/system.converter-2

Answer 7

Apart from the manual method Avi shows, you can use a mapping system like AutoMapper to do the transformation for you. This is particularly useful in the case where you have a lot of columns/properties to map.

Check out this article on how to use AutoMapper to convert a DataTable to a list of objects.

Answer 8

DataRow has a property ItemArray, which contains an array of object values. You can work with this array and create any custom type with the values from your DataRow.

Answer 9

With less complications ;), two steps will solve the task: 1. cast to dictionary (ToDictionary). 2. map dictionary to entity (MapToEntity).

    public static IDictionary<string, object> ToDictionary(
        this DataRow content
        )
    {
        var values = content.ItemArray;
        var columns = content
            .Table
            .Columns
            .Cast<DataColumn>()
            .Select(x => x.ColumnName);
        return values
            .Select((v, m) => new { v, m })
            .ToDictionary(
                x => columns.ElementAt(x.m)
                , x => (x.v == DBNull.Value ? null : x.v)
             );
    }
    public static T MapToEntity<T>(
        this IDictionary<string, object> source
        )
        where T : class, new()
    {
        // t - target
        T t_object = new T();
        Type t_type = t_object.GetType();

        foreach (var kvp in source)
        {
            PropertyInfo t_property = t_type.GetProperty(kvp.Key);
            if (t_property != null)
            {
                t_property.SetValue(t_object, kvp.Value);
            }
        }
        return t_object;
    }

...and the usage would be:

DataRow dr = getSomeDataRow(someArgs);
ABC result = dr.ToDictionary()
  .MapToEntity<ABC>();

Answer 10

You could convert the whole Data table into a list Object like the code below. Of course, you can take the specific object which you want with the index or the field value.

    /// <summary>
    /// convert a datatable to list Object
    /// </summary>
    /// <typeparam name="T">object model</typeparam>
    /// <param name="dataTable"></param>
    /// <returns>ex ussage: List<User> listTbl = CommonFunc.convertDatatblToListObj<User>(dataTable);</returns>
    public static List<T> convertDatatableToListObject<T>(DataTable dataTable)
    {
        List<T> res = new List<T>();
        try
        {
            string tblJson = JsonConvert.SerializeObject(dataTable);

            res = JsonConvert.DeserializeObject<List<T>>(tblJson);
        }
        catch (Exception ex)
        {
            string exStr = ex.Message;
        }
        return res;
    }

Answer 11

With these changes worked fine for me, for fields int, long, int? and long?

// function that creates an object from the given data row
public static T CreateItemFromRow<T>(DataRow row) where T : new()
{
    // create a new object
    T item = new T();

    // set the item
    SetItemFromRow(item, row);

    // return 
    return item;
}

public static void SetItemFromRow<T>(T item, DataRow row) where T : new()
{
    // go through each column
    foreach (DataColumn c in row.Table.Columns)
    {
        // find the property for the column
        PropertyInfo p = item.GetType().GetProperty(c.ColumnName);

        // if exists, set the value
        if (p != null && row[c] != DBNull.Value)
        {
            if (p.PropertyType.Name == "Int64")
            {
                p.SetValue(item, long.Parse(row[c].ToString()), null);
            }
            else if (p.PropertyType.Name == "Int32")
            {
                p.SetValue(item, int.Parse(row[c].ToString()), null);
            }
            else if (p.PropertyType.FullName.StartsWith("System.Nullable`1[[System.Int32"))
            {
                p.SetValue(item, (int?)int.Parse(row[c].ToString()), null);
            }
            else if (p.PropertyType.FullName.StartsWith("System.Nullable`1[[System.Int64"))
            {
                p.SetValue(item, (long?)long.Parse(row[c].ToString()), null);
            }
            else
            {
                p.SetValue(item, row[c], null);
            }
        }
    }
}