如何在 C# 中使用从一个 DataTable 到另一个 DataTable 的 where 条件选择列

How to select Columns using where condition from one DataTable to another in C#

提问by Subin Jacob

In my C# code, I have a source DataTable, and want to query it, storing the results to another DataTable.

在我的 C# 代码中，我有一个 source DataTable，想要查询它，将结果存储到另一个DataTable.

I have A DataTable with stgId, fromdate, todate, colorCode, something1, something2as columns. After querying with the wherecondition I need to remove something1and something2columns and to get the result in another DataTable.

我有一个带有stgId, fromdate, todate, colorCode, something1, something2列的数据表。与查询后，where状态我需要删除something1和something2列，得到的结果在其他DataTable。

Equivalent SQL query would be as shown below

等效的 SQL 查询如下所示

SELECT 
    stgId,
    fromdate,
    todate,
    colorCode 
FROM 
    tblScheduling 
WHERE 
    Mcode='123'

I want to get the result in another DataTable.

我想在另一个DataTable.

EDIT: Update After Answering

编辑：回答后更新

It is possible to get the result as DataRow[]type using where condition like this.

可以DataRow[]使用像这样的where 条件将结果作为类型获取。

DataRow[] results = table.Select("A = 'foo' AND B = 'bar' AND C = 'baz'");

However I wanted the result set as new DataTable.
Quotefrom accepted answer
"Read about LINQ and lambda expression, they will be very useful for you. You can read about them hereand here"

但是我希望结果集作为新的数据表。
引用已接受的答案
“阅读有关 LINQ 和 lambda 表达式的信息，它们对您非常有用。您可以在此处和此处阅读有关它们的信息”

采纳答案by Habib

You can't use CopyToDataTablemethod directly, instead See: How to: Implement CopyToDataTable Where the Generic Type T Is Not a DataRow. After setting up your classes as per the link you can later call the method CopyToDataTablelike:

您不能直接使用CopyToDataTable方法，而是请参阅：如何：实现 CopyToDataTable 其中泛型类型 T 不是 DataRow。根据链接设置类后，您可以稍后调用该方法，CopyToDataTable例如：

var newDataTable = (dt.AsEnumerable()
                     .Where(r=> r.Field<string>("Mcode" == "123")
                     .Select new 
                           {
                           stgId = r.Field<int>("stgId"),
                           fromdate = r.Field<DateTime>("fromdate"),
                           todate = r.Field<DateTime>("todate"),
                           colorCode = r.Field<int>("colorCode")
                           }).CopyToDataTable();

Remember to use the correct type in Fieldextension method.

请记住在Field扩展方法中使用正确的类型。

The above requires following two classes to be setup in your code. *(its from the same MSDN link)

以上需要在您的代码中设置以下两个类。*（来自同一个 MSDN链接）

public static class CustomLINQtoDataSetMethods
{
    public static DataTable CopyToDataTable<T>(this IEnumerable<T> source)
    {
        return new ObjectShredder<T>().Shred(source, null, null);
    }

    public static DataTable CopyToDataTable<T>(this IEnumerable<T> source,
                                                DataTable table, LoadOption? options)
    {
        return new ObjectShredder<T>().Shred(source, table, options);
    }

}

public class ObjectShredder<T>
{
    private System.Reflection.FieldInfo[] _fi;
    private System.Reflection.PropertyInfo[] _pi;
    private System.Collections.Generic.Dictionary<string, int> _ordinalMap;
    private System.Type _type;

    // ObjectShredder constructor.
    public ObjectShredder()
    {
        _type = typeof(T);
        _fi = _type.GetFields();
        _pi = _type.GetProperties();
        _ordinalMap = new Dictionary<string, int>();
    }

    /// <summary>
    /// Loads a DataTable from a sequence of objects.
    /// </summary>
    /// <param name="source">The sequence of objects to load into the DataTable.</param>
    /// <param name="table">The input table. The schema of the table must match that 
    /// the type T.  If the table is null, a new table is created with a schema 
    /// created from the public properties and fields of the type T.</param>
    /// <param name="options">Specifies how values from the source sequence will be applied to 
    /// existing rows in the table.</param>
    /// <returns>A DataTable created from the source sequence.</returns>
    public DataTable Shred(IEnumerable<T> source, DataTable table, LoadOption? options)
    {
        // Load the table from the scalar sequence if T is a primitive type.
        if (typeof(T).IsPrimitive)
        {
            return ShredPrimitive(source, table, options);
        }

        // Create a new table if the input table is null.
        if (table == null)
        {
            table = new DataTable(typeof(T).Name);
        }

        // Initialize the ordinal map and extend the table schema based on type T.
        table = ExtendTable(table, typeof(T));

        // Enumerate the source sequence and load the object values into rows.
        table.BeginLoadData();
        using (IEnumerator<T> e = source.GetEnumerator())
        {
            while (e.MoveNext())
            {
                if (options != null)
                {
                    table.LoadDataRow(ShredObject(table, e.Current), (LoadOption)options);
                }
                else
                {
                    table.LoadDataRow(ShredObject(table, e.Current), true);
                }
            }
        }
        table.EndLoadData();

        // Return the table.
        return table;
    }

    public DataTable ShredPrimitive(IEnumerable<T> source, DataTable table, LoadOption? options)
    {
        // Create a new table if the input table is null.
        if (table == null)
        {
            table = new DataTable(typeof(T).Name);
        }

        if (!table.Columns.Contains("Value"))
        {
            table.Columns.Add("Value", typeof(T));
        }

        // Enumerate the source sequence and load the scalar values into rows.
        table.BeginLoadData();
        using (IEnumerator<T> e = source.GetEnumerator())
        {
            Object[] values = new object[table.Columns.Count];
            while (e.MoveNext())
            {
                values[table.Columns["Value"].Ordinal] = e.Current;

                if (options != null)
                {
                    table.LoadDataRow(values, (LoadOption)options);
                }
                else
                {
                    table.LoadDataRow(values, true);
                }
            }
        }
        table.EndLoadData();

        // Return the table.
        return table;
    }

    public object[] ShredObject(DataTable table, T instance)
    {

        FieldInfo[] fi = _fi;
        PropertyInfo[] pi = _pi;

        if (instance.GetType() != typeof(T))
        {
            // If the instance is derived from T, extend the table schema
            // and get the properties and fields.
            ExtendTable(table, instance.GetType());
            fi = instance.GetType().GetFields();
            pi = instance.GetType().GetProperties();
        }

        // Add the property and field values of the instance to an array.
        Object[] values = new object[table.Columns.Count];
        foreach (FieldInfo f in fi)
        {
            values[_ordinalMap[f.Name]] = f.GetValue(instance);
        }

        foreach (PropertyInfo p in pi)
        {
            values[_ordinalMap[p.Name]] = p.GetValue(instance, null);
        }

        // Return the property and field values of the instance.
        return values;
    }

    public DataTable ExtendTable(DataTable table, Type type)
    {
        // Extend the table schema if the input table was null or if the value 
        // in the sequence is derived from type T.            
        foreach (FieldInfo f in type.GetFields())
        {
            if (!_ordinalMap.ContainsKey(f.Name))
            {
                // Add the field as a column in the table if it doesn't exist
                // already.
                DataColumn dc = table.Columns.Contains(f.Name) ? table.Columns[f.Name]
                    : table.Columns.Add(f.Name, f.FieldType);

                // Add the field to the ordinal map.
                _ordinalMap.Add(f.Name, dc.Ordinal);
            }
        }
        foreach (PropertyInfo p in type.GetProperties())
        {
            if (!_ordinalMap.ContainsKey(p.Name))
            {
                // Add the property as a column in the table if it doesn't exist
                // already.
                DataColumn dc = table.Columns.Contains(p.Name) ? table.Columns[p.Name]
                    : table.Columns.Add(p.Name, p.PropertyType);

                // Add the property to the ordinal map.
                _ordinalMap.Add(p.Name, dc.Ordinal);
            }
        }

        // Return the table.
        return table;
    }
}

回答by ????

foreach (DataRow dr in dataTable1.Rows) {
    if (dr["Mcode"].ToString()=="123")
        dataTable2.Rows.Add(dr.ItemArray);
}

The above example assumes that dataTable1and dataTable2have the same number, type and order of columns.

上面的示例假设dataTable1和dataTable2具有相同的列数、类型和顺序。

Edit 1

编辑 1

You can use clone method to copy structure of existing datatable into another.

您可以使用 clone 方法将现有数据表的结构复制到另一个数据表中。

http://msdn.microsoft.com/en-IN/library/system.data.datatable.clone.aspx

http://msdn.microsoft.com/en-IN/library/system.data.datatable.clone.aspx

Suppose you have a datatable dt1So you can create a clone as follows

假设你有一个数据表，dt1那么你可以创建一个克隆如下

  DataTable dt2  = dt1.Clone();  

and use the above loop as follows

并使用上面的循环如下

foreach (DataRow dr in dt1.Rows) {
    if (dr["Mcode"].ToString()=="123")
        dt2.Rows.Add(dr.ItemArray);
}