将查询结果转换为数据表 [英] convert query result to datatable

问题描述

如何使用CopyToDataTable()将查询结果转换为DataTable；
我的查询是:

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();

在CopyToDataTable上显示了一个错误，指出没有来自
的隐式转换 DataView.Personaldetail(数据库中的DataView->应用程序名称，数据库中的personaldetail->表名)到System.data.DataRow

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

推荐答案

，但是如果select返回的元素是DataRowView 类型，则使用Row 属性DataRowView 可能会有所帮助，如下所示:

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();

我根本不使用CopyToDataTable，因为它似乎无法处理匿名类型.相反，"Linq扩展"中提供了一个很好的解决方案.有几种来源，它们之间可能会有很大差异.

我比其他任何人都经常使用的扩展名如下:

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>

在这两种情况下，select都在选择列名(前称，姓)，更改列类型(性别)或仅按原样镜像列(dob)上完成相同的工作.扩展方法将为您管理类型，但请注意可为空的类型.您可能需要为null添加额外的处理.

另外，您也可以只使用.Select()或选择i，所有列都将按原样镜像在数据表中.


PS:
我无法告诉您，.NET4.0中我有多喜欢扩展方法.我什至想出了最微不足道的理由:)

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 :)

public static bool AsBool(this string boolString)
{    return boolString.Equals("T");    }

这篇关于将查询结果转换为数据表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！