Convert DataTable to Generic List

Have you ever had to work with a datatable and then need to convert it to a generic list? I run into this when I am converting older application or working with Oracle DBs and would like to use linq to work with the result set. Now on the surface this does not look hard right? You create a datatable and fill it with data and do something like datable.AsEnumerator().ToList() and there you go, you have a  generic list of the datarows. But what if you have a custom object that you would like to use instead of datarows.

How do you do that? Well I google several things and found Javier G. Lozano’s Great Example and some others that would work very good. Of course I was not satisfied with that, oh no I had to take it up a level and ask what if I wanted the property names different. Well that is where reflection & custom attributes comes in.

Let’s say my sql query returns a datatable with the following columns:

• Art_ID
• CMR
• Client_Number
• Artwork_Number

But my object has the following properties:

• ID
• CMR
• ClientNumber
• ArtNumber

They do not match. We could loop through the datatable and fill a list that way, but it is not very reusable.

Step 1:

Create custom attribute class:

[AttributeUsage(AttributeTargets.Property,AllowMultiple = true)]
public class ArtWorkName : Attribute
{
public ArtworkName(string fieldname)
{
FieldName = fieldname;
}
public string FieldName { get; set; }
} Step 2:

Add the custom attributes to your properties:
public class myArtwork
{
[ArtworkName("Art_ID")]
public int ID { get; set; }

public string CMR { get; set; }

[ArtworkName("Client_Number")]
public string ClientNumber { get; set; }

[ArtworkName("Artwork_Number")]
public string ArtNumber { get; set; }
}
Step 3:
Create an extension class:
public static IList ConvertTo(this DataTable dt)
{
IList list = null;

if (dt.Rows.Count>0)
{
list = new List();

foreach (DataRow row in dt.Rows)
{
T item = Activator.CreateInstance();

var properties = typeof(T).GetProperties();

foreach (var p in properties)
{
var fieldnames = p.GetCustomAttributes(typeof(ArtworkName), false);

if (fieldnames.Count() > 0)
{
var fn = fieldnames[0] as ArtworkName;

p.SetValue(item, row[fn.FieldName], null);

}
else
{
if (dt.Columns.Contains(p.Name))
{
p.SetValue(item, row[p.Name], null);
}
}

}

list.Add(item);
}
}

return list;
}

}
Step 3 explained some:

1. Get all the properties of your object:
   var properties = typeof(T).GetProperties();
2. Loop through the collection of properties:
   foreach (var p in properties)
3. Get the custom attribute for the current property:
   var fieldnames = p.GetCustomAttributes(typeof(ArtworkName), false);
4. Get the field name from the custom attribute: *Note: Since my custom atribute had only one element, I know it would be the first one.
   var fn = fieldnames[0] as ArtworkName;
5. Populate the property change with the current row value:
   p.SetValue(item, row[fn.FieldName], null);
6. Repeat

Here is the whole thing:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace TestDTToList
{
public class myArtwork
{
[ArtworkName("Art_ID")]
public int ID { get; set; }

public string CMR { get; set; }

[ArtworkName("Client_Number")]
public string ClientNumber { get; set; }

[ArtworkName("Artwork_Number")]
public string ArtNumber { get; set; }
}

[AttributeUsage(AttributeTargets.Property,AllowMultiple = true)]
public class ArtWorkName : Attribute
{
public ArtworkName(string fieldname)
{
FieldName = fieldname;
}
public string FieldName { get; set; }
}

public static class DataTableHelper
{

public static IList ConvertTo(this DataTable dt)
{
IList list = null;

if (dt.Rows.Count>0)
{
list = new List();

foreach (DataRow row in dt.Rows)
{
T item = Activator.CreateInstance();

var properties = typeof(T).GetProperties();

foreach (var p in properties)
{
var fieldnames = p.GetCustomAttributes(typeof(ArtworkName), false);

if (fieldnames.Count() > 0)
{
var fn = fieldnames[0] as ArtworkName;

p.SetValue(item, row[fn.FieldName], null);

}
else
{
if (dt.Columns.Contains(p.Name))
{
p.SetValue(item, row[p.Name], null);
}
}

}

list.Add(item);
}
}

return list;
}

}
}