将查询结果转换为数据表

问题描述

如何使用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

推荐答案

DataTable table = (from i in dbContext.Personaldetails
                        where i.ID == 1
                        select i.Row).CopyToDataTable();

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");    }

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