• LINQ Extension Method to convert LIST to DATATABLE in C#

    Posted on May 17, 2012 by in C#, Dotnet


    If you want to extend the functionality of an existing class without modifying the type, extension methods is your solution. Even though you create them as static methods you can invoke them as instance methods.  You can extend the existing code without fear of breaking existing functionality.  This would be extremely useful when you are working on some thirdpartycode. You can write extension methods that apply to interfaces as well. Imagine Microsoft adding all those LINQ methods to ILIST, they would have ended up by modifying the whole implementation.

    In this article, I am going to create a very useful extension method for converting any object that implements IEnumerable<T> interface (such as List<T>, Stack<T>, LinkedList<T>, Queue<T>, Dictionary<TKey, Value> and Hashset<T>) to a DataTable. I don’t want to use CopyToDataTable because it doesn’t handle anonymous types.

    How to create a simple extension method?

    1. Create a new static class. It should be accessible to the client.
    2. Create a new static method in above class.
    3. The first parameter of the method specifies the type that the method operates on; it must be preceded with the “this” modifier.
    4. Wherever you want to use this method, add a namespace reference to that class
    5. You can invoke the extension method as if you were calling any instance method

    This extension method takes IEnumerable<T> as a method argument, which supports a simple iteration over a collection of a specified type and returns DataTable object.

    public static DataTable AsDataTable<T>(this IEnumerable<T> list)
                where T : class
     {}
    

    The columns of the output table is nothing but the public properties exposed by the object type. We use reflection to fetch all public properties and create columns accordingly.

    DataTable dtOutput = new DataTable("tblOutput");
    PropertyInfo[] properties = list.FirstOrDefault().GetType().
        GetProperties(BindingFlags.Public | BindingFlags.Instance);
    foreach (PropertyInfo propertyInfo in properties)
        dtOutput.Columns.Add(propertyInfo.Name, propertyInfo.PropertyType);
    

    Each item from the collection would make a row in the output table. We make one row from all the public properties of the item and add it to the output table.

    DataRow dr;
    //iterate through all the objects in the list and add them 
    //as rows to the table
    foreach (T t in list)
    {
        dr = dtOutput.NewRow();
        //iterate through all the properties of the current object 
        //and set their values to data row
        foreach (PropertyInfo propertyInfo in properties)
        {
            dr[propertyInfo.Name] = propertyInfo.GetValue(t, null);
        }
        dtOutput.Rows.Add(dr);
    }
    

    ExtensioMethod.cs

    public static class ExtensionMethods
    {
    
    public static DataTable AsDataTable<T>(this IEnumerable<T> list)
        where T : class
    {
        DataTable dtOutput = new DataTable("tblOutput");
    
        //if the list is empty, return empty data table
        if (list.Count() == 0)
            return dtOutput;
    
        //get the list of  public properties and add them as columns to the
        //output table            
        PropertyInfo[] properties = list.FirstOrDefault().GetType().
            GetProperties(BindingFlags.Public | BindingFlags.Instance);
        foreach (PropertyInfo propertyInfo in properties)
            dtOutput.Columns.Add(propertyInfo.Name, propertyInfo.PropertyType);
    
        //populate rows
        DataRow dr;
        //iterate through all the objects in the list and add them 
        //as rows to the table
        foreach (T t in list)
        {
            dr = dtOutput.NewRow();
            //iterate through all the properties of the current object 
            //and set their values to data row
            foreach (PropertyInfo propertyInfo in properties)
            {
                dr[propertyInfo.Name] = propertyInfo.GetValue(t, null);
            }
            dtOutput.Rows.Add(dr);
        }
        return dtOutput;
    }
    }
    

    Example:
    I am going to create a simple console application to test this extenstion method. In this example, we create a list of departments and will try to convert them to a datatable using this extenstion method.

    Department.cs

    public class Department
    {
        private int departmentID;
        private string departmentName;
    
        public int DepartmentID
        {
            get{
                return departmentID;
            }
            set{
                departmentID = value;
            }
        }
        public string DepartmentName
        {
            get{
                return departmentName;
            }
            set{
                departmentName = value;
            }
        }
    }
    

    In the Main method, I have created a list of department objects.

    List<Department> departments = new List<Department>();
    
    Department dept = new Department();
    dept.DepartmentID = 1;
    dept.DepartmentName = "Computer Science";
    departments.Add(dept);
    
    dept = new Department();
    dept.DepartmentID = 2;
    dept.DepartmentName = "Computer Engineering";
    departments.Add(dept);
    
    dept = new Department();
    dept.DepartmentID = 3;
    dept.DepartmentName = "Electrical Engineering";
    departments.Add(dept);
    

    I am interested in finding out all departments whose names contain “computer”. I am using LINQ to query the list and then using AsDataTable extension method to convert it to DataTable.

    var query = from department in departments
                where department.DepartmentName.Contains("Computer")
                select department;
    
    DataTable table = query.AsDataTable();
    

    I am simply iterating through the table  to print out all its rows and columns.

    foreach (DataRow dr in table.Rows)
    {
        foreach(DataColumn dc in table.Columns)
        {
            Console.WriteLine("{0} : {1}", dc.ColumnName, dr[dc]);
        }
        Console.WriteLine();
    }
    

    Be Sociable, Share!
      Post Tagged with ,

    Written by

    Software architect with over 10 years of proven experience in designing & developing n-tier and web based software applications, for Finance, Telecommunication, Manufacturing, Internet and other Commercial industries. He believes that success depends on one's ability to integrate multiple technologies to solve a simple as well as complicated problem.

    View all articles by

    Email : [email protected]

    Leave a Reply