Convert to new DataTable format?

Question

I am using .NET 3.5 and need a conversion below to select a new result in a DataTable. Is there something for this or does anyone know a way that can do this?

var contentList = (from item in this.GetData().Cast<IContent>() select new { Title = item.GetMetaData("Title"), Street = item.GetMetaData("Street"), City = item.GetMetaData("City"), Country = item.GetMetaData("Country") }); 

Answer 1

The simple and simple thing is to use reflection:

 var records = (from item in this.GetData().Cast<IContent>() select new { Title = "1", Street = "2", City = "3", Country = "4" }); var firstRecord = records.First(); if (firstRecord == null) return; var infos = firstRecord.GetType().GetProperties(); DataTable table = new DataTable(); foreach (var info in infos) { DataColumn column = new DataColumn(info.Name, info.PropertyType); table.Columns.Add(column); } foreach (var record in records) { DataRow row = table.NewRow(); for (int i = 0; i < table.Columns.Count; i++) row[i] = infos[i].GetValue(record); table.Rows.Add(row); } 

The code may not work, but should give you a general idea. First, you get the Infos property from an anonymous type and use this metadata to create a datatable schema (fill columns). Then you use this information to get values ​​from each object.

Answer 2

Here is one general solution without reflecting properties. Use the extension method as shown below

  public static DataTable ConvertToDataTable<TSource>(this IEnumerable<TSource> records, params Expression<Func<TSource, object>>[] columns) { var firstRecord = records.First(); if (firstRecord == null) return null; DataTable table = new DataTable(); List<Func<TSource, object>> functions = new List<Func<TSource, object>>(); foreach (var col in columns) { DataColumn column = new DataColumn(); column.Caption = (col.Body as MemberExpression).Member.Name; var function = col.Compile(); column.DataType = function(firstRecord).GetType(); functions.Add(function); table.Columns.Add(column); } foreach (var record in records) { DataRow row = table.NewRow(); int i = 0; foreach (var function in functions) { row[i++] = function((record)); } table.Rows.Add(row); } return table; } 

And invoke the same when the parameters will be the column name in the order you want.

 var table = records.ConvertToDataTable( item => item.Title, item => item.Street, item => item.City ); 

Answer 3

There is a CopyToDataTable extension method that does this for you. He lives in System.Data.DataSetExtensions.dll

Answer 4

Try the following:

 // Create your datatable. DataTable dt = new DataTable(); dt.Columns.Add("Title", typeof(string)); dt.Columns.Add("Street", typeof(double)); // get a list of object arrays corresponding // to the objects listed in the columns // in the datatable above. var result = from item in in this.GetData().Cast<IContent>() select dt.LoadDataRow( new object[] { Title = item.GetMetaData("Title"), Street = item.GetMetaData("Street"), }, false); // the end result will be a set of DataRow objects that have been // loaded into the DataTable. 

Original article for sample code: Converting an Anonymous Type Generated by LINQ to DataTable

EDIT: common pseudo code:

 void LinqToDatatable(string[] columns, Type[] datatypes, linqSource) { for loop { dt.columns.add(columns[i], datatypes[i]); } //Still thinking how to make this generic.. var result = from item in in this.GetData().Cast<IContent>() select dt.LoadDataRow( new object[] { string[0] = item.GetMetaData[string[0]], string[1] = item.GetMetaData[srring[1] }, false); } 

Answer 5

You can convert the result of a list to datatable using the following function

  public static DataTable ToDataTable<T>(IEnumerable<T> values) { DataTable table = new DataTable(); foreach (T value in values) { if (table.Columns.Count == 0) { foreach (var p in value.GetType().GetProperties()) { table.Columns.Add(p.Name); } } DataRow dr = table.NewRow(); foreach (var p in value.GetType().GetProperties()) { dr[p.Name] = p.GetValue(value, null) + ""; } table.Rows.Add(dr); } return table; } 

Answer 6

  public static DataTable ListToDataTable<T>(this IList<T> data) { DataTable dt = new DataTable(); PropertyDescriptorCollection props = TypeDescriptor.GetProperties(typeof(T)); for (int i = 0; i < props.Count; i++) { PropertyDescriptor prop = props[i]; dt.Columns.Add(prop.Name, prop.PropertyType); } object[] values = new object[props.Count]; foreach (T t in data) { for (int i = 0; i < values.Length; i++) { values[i] = props[i].GetValue(t); } dt.Rows.Add(values); } return dt; } 

After you make your choice, you can .ToList().ListToDataTable() . This uses reflection by ComponentModel and is (theroetically) faster than System.Reflection.