Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
How to convert query result into DataTable by using CopyToDataTable();
my query is:
var query = from i in dbContext.Personaldetails
                        where i.ID == 1
                        select i;
            DataTable table = query.CopyToDataTable();
it is showin an error at the CopyToDataTable that "There is no implicit conversion from
DataView.Personaldetail(DataView->Application name,personaldetail->table name in database) to System.data.DataRow
Posted 15-May-12 23:35pm
Edited 16-May-12 0:46am
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

I can't say exactly, but if the element returned by select is of DataRowView type then using Row property of DataRowView may be helpful as shown below:
 
DataTable table = (from i in dbContext.Personaldetails
                        where i.ID == 1
                        select i.Row).CopyToDataTable();
  Permalink  
Comments
losmac at 16-May-12 11:41am
   
Great 5!
VJ Reddy at 16-May-12 12:34pm
   
Thank you, losmac :)
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

I don't use the CopyToDataTable at all because it doesn't seem to be able to handle anonymous types. Instead there is a great solution in "Linq Extensions". There are several sources and they can differ greatly.
 
The extension I use more often than any other is as follows:
 
 
using System.Data;
using System.Collections.Generic;
using System.Text;
using System.Linq;
using System.Reflection;
    public 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);
            try
            {
                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);
                }
            }
            catch (Exception ex)
            {
                return new DataTable();
            }
            return table;
        }
}
</t></t>
 
With this extention you can set the names of the columns you want to include such as :
 
//I prefer this format of extensable queries, but it doesn't matter
DataTable table = dbContext.Personaldetails
                    .Where(i=>i.ID==1)
                    .Select(i=>
                      Forename = i.forename,
                      Surname = i.surname,
                      Sex = i.is_male?"Male":"Female"
                      dob)
                    .AsDataTable();
 
//or in your preferred format:
IQueriable<personaldetails> query = from i in dbContext.Personaldetails
                                    where i.ID == 1
                                    select new(){
                                      Forename = i.forename,
                                      Surname = i.surname,
                                      Sex = i.is_male?"Male":"Female"
                                      dob)
                                    });
DataTable table = query.AsDataTable();
</personaldetails>
 
In both those cases the select does the same job of electing column names (Forename, Surname), changing column types(Sex) or just mirroring the column as is (dob). The extention method will manage the types for you but beware of nullable types. You may have to add extra handling in for nulls.
 
Alternatively you can just use .Select() or select i and all of the columns will be mirrored in the Datatable "as-is".
 

PS:
I can't tell you how much I love extention methods in .NET4.0. I even come up with the most petty extentions just cos I can Smile | :)
 
public static bool AsBool(this string boolString)
{    return boolString.Equals("T");    }
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web03 | 2.8.150305.1 | Last Updated 16 May 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100