Convert Datatable to Generic List

How to Convert DataTable to Generic List in C#

using System.Reflection;

Then,

public static List<T> BindList<T>(DataTable dt)
{
    // Example 1:
    // Get private fields + non properties
    //var fields = typeof(T).GetFields(BindingFlags.NonPublic | BindingFlags.Instance);

    // Example 2: Your case
    // Get all public fields
    var fields = typeof(T).GetFields();

    List<T> lst = new List<T>();

    foreach (DataRow dr in dt.Rows)
    {
        // Create the object of T
        var ob = Activator.CreateInstance<T>();

        foreach (var fieldInfo in fields)
        {
            foreach (DataColumn dc in dt.Columns)
            {
                // Matching the columns with fields
                if (fieldInfo.Name == dc.ColumnName)
                {
                    // Get the value from the datatable cell
                    object value = dr[dc.ColumnName];

                    // Set the value into the object
                    fieldInfo.SetValue(ob, value);
                    break;
                }
            }
        }

        lst.Add(ob);
    }

    return lst;
}

Example of usage:

DataTable dt1 = SqlHelper.GetTable("select * from employee;");
List<Employee> employees = BindList<Employee>(dt1);

DataTable dt2 = SqlHelper.GetTable("select * from membership;");
List<Membership> lstMembership = BindList<Membership>(dt2);

DataTable dt3 = SqlHelper.GetTable("select * from car order by name;");
List<Car> lstCar = BindList<Car>(dt3);

=====================

Extended Version

Above example assume that the data holds inside the DataTable has the same data type as your Class object's fields.

What if the data is not same as your class object's fields?

Such as null?

So, you might want to extend the method to take care just in case both data type are not the same.

public static List<T> BindList<T>(DataTable dt)
{
    // Example 1:
    // Get private fields + non properties
    //var fields = typeof(T).GetFields(BindingFlags.NonPublic | BindingFlags.Instance);

    // Example 2: Your case
    // Get all public fields
    var fields = typeof(T).GetFields();

    List<T> lst = new List<T>();

    foreach (DataRow dr in dt.Rows)
    {
        // Create the object of T
        var ob = Activator.CreateInstance<T>();

        foreach (var fieldInfo in fields)
        {
            foreach (DataColumn dc in dt.Columns)
            {
                // Matching the columns with fields
                if (fieldInfo.Name == dc.ColumnName)
                {
                    Type type = fieldInfo.FieldType;

                    // Get the value from the datatable cell
                    object value = GetValue(dr[dc.ColumnName], type);

                    // Set the value into the object
                    fieldInfo.SetValue(ob, value);
                    break;
                }
            }
        }

        lst.Add(ob);
    }

    return lst;
}

static object GetValue(object ob, Type targetType)
{
    if (targetType == null)
    {
        return null;
    }
    else if (targetType == typeof(String))
    {
        return ob + "";
    }
    else if (targetType == typeof(int))
    {
        int i = 0;
        int.TryParse(ob + "", out i);
        return i;
    }
    else if (targetType == typeof(short))
    {
        short i = 0;
        short.TryParse(ob + "", out i);
        return i;
    }
    else if (targetType == typeof(long))
    {
        long i = 0;
        long.TryParse(ob + "", out i);
        return i;
    }
    else if (targetType == typeof(ushort))
    {
        ushort i = 0;
        ushort.TryParse(ob + "", out i);
        return i;
    }
    else if (targetType == typeof(uint))
    {
        uint i = 0;
        uint.TryParse(ob + "", out i);
        return i;
    }
    else if (targetType == typeof(ulong))
    {
        ulong i = 0;
        ulong.TryParse(ob + "", out i);
        return i;
    }
    else if (targetType == typeof(double))
    {
        double i = 0;
        double.TryParse(ob + "", out i);
        return i;
    }
    else if (targetType == typeof(DateTime))
    {
        // do the parsing here...
    }
    else if (targetType == typeof(bool))
    {
        // do the parsing here...
    }
    else if (targetType == typeof(decimal))
    {
        // do the parsing here...
    }
    else if (targetType == typeof(float))
    {
        // do the parsing here...
    }
    else if (targetType == typeof(byte))
    {
        // do the parsing here...
    }
    else if (targetType == typeof(sbyte))
    {
        // do the parsing here...
    }
    else if........
    ..................

    return ob;
}

How do you convert a DataTable into a generic list?

If you're using .NET 3.5, you can use DataTableExtensions.AsEnumerable (an extension method) and then if you really need a List<DataRow> instead of just IEnumerable<DataRow> you can call Enumerable.ToList:

IEnumerable<DataRow> sequence = dt.AsEnumerable();

or

using System.Linq;
...
List<DataRow> list = dt.AsEnumerable().ToList();

Datatable to Generic List Conversion

You need to check for DBNull.Value instead, something like this:

if (dr[column.ColumnName] != DBNull.Value)
    pro.SetValue(obj, dr[column.ColumnName], null);

Or:

pro.SetValue(obj, dr[column.ColumnName] == DBNull.Value ? string.Empty : 
      dr[column.ColumnName].ToString(), null);

Convert DataTable to generic List?

That won't be an actual leak, but it could be stressing things unnecessarily...

How many rows are you working over?
Note that reflection is a pain, and that every call to things like GetCustomAttributes can return a new array (so you want to do that once, not once per-property-per-row).

Personally, I'd pre-construct the work I intend to do... something like below.

Note that if I was doing this lots, I'd either switch to HyperDescriptor, or if .NET 3.5 was an option, maybe a compiled Expression. Since DataTable isn't strongly typed, HyperDescriptor would be a logical next step (for performance) after the below...

sealed class Tuple<T1, T2>
{
    public Tuple() {}
    public Tuple(T1 value1, T2 value2) {Value1 = value1; Value2 = value2;}
    public T1 Value1 {get;set;}
    public T2 Value2 {get;set;}
}
public static List<T> Convert<T>(DataTable table)
    where T : class, new()
{
    List<Tuple<DataColumn, PropertyInfo>> map =
        new List<Tuple<DataColumn,PropertyInfo>>();

    foreach(PropertyInfo pi in typeof(T).GetProperties())
    {
        ColumnAttribute col = (ColumnAttribute)
            Attribute.GetCustomAttribute(pi, typeof(ColumnAttribute));
        if(col == null) continue;
        if(table.Columns.Contains(col.FieldName))
        {
            map.Add(new Tuple<DataColumn,PropertyInfo>(
                table.Columns[col.FieldName], pi));
        }
    }

    List<T> list = new List<T>(table.Rows.Count);
    foreach(DataRow row in table.Rows)
    {
        if(row == null)
        {
            list.Add(null);
            continue;
        }
        T item = new T();
        foreach(Tuple<DataColumn,PropertyInfo> pair in map) {
            object value = row[pair.Value1];
            if(value is DBNull) value = null;
            pair.Value2.SetValue(item, value, null);
        }
        list.Add(item);
    }
    return list;        
}

Convert DataTable to Generic List

because table is 2D matrix, so we should return 2D List:

public List<List<string >> retListTable()
    {

        DataTable dt = new DataTable();
        adap.Fill(dt);

        List<List<string>> lstTable = new List<List<string>>();

        foreach (DataRow row in dt.Rows)
        {
            List<string> lstRow = new List<string>();
            foreach (var item in row.ItemArray )
            {
                lstRow.Add(item.ToString().Replace("\r\n", string.Empty));
            }
            lstTable.Add(lstRow );
        }

        return lstTable ;

    }

Convert DataTable to Generic List in C#

You could actually shorten it down considerably. You can think of the Select() extension method as a type converter. The conversion could then be written as this:

List<Cards> target = dt.AsEnumerable()
    .Select(row => new Cards
    {
        // assuming column 0's type is Nullable<long>
        CardID = row.Field<long?>(0).GetValueOrDefault(),
        CardName = String.IsNullOrEmpty(row.Field<string>(1))
            ? "not found"
            : row.Field<string>(1),
    }).ToList();

Fastest way to convert datatable to generic list

Do you know the structure of the DataTable and the typed object ahead of time? You could use a delegate to do the mapping. If you don't (i.e. all you know is a Type and properties) there are ways of accelerating dynamic member access (such as HyperDescriptor).

Either way, consider an iterator block; that way you don't have to buffer the objects an entire second time; of course, if you are only dealing with smallish rowcounts this isn't an issue.

Can you clarify any of those points? I can add a lot more detail...

At the simplest, what is wrong with:

DataTable table = new DataTable {
    Columns = {
        {"Foo", typeof(int)},
        {"Bar", typeof(string)}
     }
};
for (int i = 0; i < 5000; i++) {
    table.Rows.Add(i, "Row " + i);
}

List<MyType> data = new List<MyType>(table.Rows.Count);
foreach (DataRow row in table.Rows) {
    data.Add(new MyType((int)row[0], (string)row[1]));
}

(the problems in the above might steer the right approach...)

Convert Datatable to generic IEnumerableT

You could create an extension method that convert it for you. Given you have properties on the query that matches the properties on your generic T type, you could use reflection to perform it! For sample (see the comments on the code):

public static class DataTableExtensions
{
    public static IEnumerable<T> ToGenericList<T>(this DataTable dataTable)
    {
        var properties = typeof(T).GetProperties().Where(x => x.CanWrite).ToList();

        var result = new List<T>();

        // loop on rows
        foreach (DataRow row in dataTable.Rows)
        {
            // create an instance of T generic type.
            var item = Activator.CreateInstance<T>();

            // loop on properties and columns that matches properties
            foreach (var prop in properties)
                foreach (DataColumn column in dataTable.Columns)                    
                    if (prop.Name == column.ColumnName)
                    {
                        // Get the value from the datatable cell
                        object value = row[column.ColumnName];

                        // Set the value into the object
                        prop.SetValue(item, value);
                        break;
                    }

            result.Add(item);
        }

        return result;
    }
}

And given you have a model like this:

public class Student 
{
    public int Id { get; set; } 
    public string Code { get; set; } 
    public string FirstName { get; set; } 
    public string LastName { get; set; } 
    // other possible properties
}

You can use the extension method like this:

protected async Task<IEnumerable<T>> QuerySqlCmdReadRows<T>(string sqlCommand)
{
    using (var con = new SqlConnection(Connection.ToString()))
    {
        con.Open();
        using (var cmd = con.CreateCommand())
        {
            cmd.CommandText = sqlCommand;

            DataTable dtResult = new DataTable();

            using (var reader = await cmd.ExecuteReaderAsync())
               dtResult.Load(reader);

            return dtResult.ToGenericList<T>();
        }
    }
}

// just and sample
var students = QuerySqlCmdReadRows<Students>("select Id, Code, FirstName, LastName from Students");

---

Related Topics