TheChaseMan's Frenetic SoapBox - Saturday, June 06, 2009 Entries

Saturday, June 06, 2009 #

Interested in writing a LINQ query to fill a DataTable? Normally I don't condone writing ugly code but let's have some fun. In this example, an anonymous type array is created followed by a DataTable. Then a LINQ query that includes a generic Func delegate encapsulating a lambda expression which is invoked to create a new DataRow based on the DataTable structure and then fill it with the properties from the anonymous type. The results (IEnumerable) are then added to the DataTable and then the DataTable is serialized to the Console output stream. In the words of Dilbert: if this is still too readable I can photocopy it and then run it through the fax machine a few times...

using System;

using System.Data;

using System.Linq;

namespace App {

    class Program {

        static void Main(string[] args) {

            var pretendThisIsADatabaseTable = new[] { new { Name = "Steve", Age = 33 }, new { Name = "Doug", Age = 34 } };

            //Create empty destination data table

            DataTable personTable = new DataTable();

            personTable.TableName = "PersonTable";

            personTable.Columns.Add("Name", typeof(string));

            personTable.Columns.Add("Age", typeof(int));

            //Query anonymous type array as DataRow objects

            var results = from person in pretendThisIsADatabaseTable

                          select new Func<DataRow, string, int, DataRow>(

                              (DataRow row, string name, int age) => {

                                  row["Name"] = name;

                                  row["Age"] = age;

                                  return row;

                              }

                          )

                          .Invoke(personTable.NewRow(), person.Name, person.Age);

            //put rows into data table

            results.ToList().ForEach(row => personTable.Rows.Add(row));

            personTable.WriteXml(Console.Out);

        }

    }

}

EDIT: everything above is pure cheese. Here's a real solution via my old co-worker Shawn :)

class Program
    {
        static void Main(string[] args)
        {
            var data = new[] {
                            new {FirstName="Bill", LastName="Clinton", ID=1, WasPresident=true}
                            , new {FirstName="John", LastName="McCain", ID=2, WasPresident=false}
                            , new {FirstName="George", LastName="Bush", ID=2, WasPresident=true}
            };

            DataTable table = (from d in data
                               where d.WasPresident==true
                               select d).AsDataTable();

            table.WriteXml(Console.Out);
        }
    }

Extension method below is required for the sugar above (AsDataTable)

static class Extensions
    {
        public static DataTable AsDataTable<T>(this IEnumerable<T> enumberable)
        {
            DataTable table = new DataTable("Generated");

            T first = enumberable.FirstOrDefault();
            if (first == null)
                return table;

            PropertyInfo[] properties = first.GetType().GetProperties();
            foreach (PropertyInfo pi in properties)
                table.Columns.Add(pi.Name, pi.PropertyType);

            foreach (T t in enumberable)
            {
                DataRow row = table.NewRow();
                foreach (PropertyInfo pi in properties)
                    row[pi.Name]=t.GetType().InvokeMember(pi.Name,BindingFlags.GetProperty,null, t, null);
                table.Rows.Add(row);
            }

            return table;
        }
    }

posted @ 9:20 AM | Feedback (0)