Copying LINQ Query Results to DataTables

Copying LINQ Query Results to DataTables

Creating a new or populating an existing DataTable with Expression. CopyToDataTable() extension

method is an alternative to the Expression. AsDataView() method. Unlike a DataView() , new

DataTable s you create have no relation to existing objects by default, but you can specify inserting/

updating an existing table by using one of CopyToDataTable() ’ s two overloads:

C# 3.0

public static DataTable CopyToDataTable < T >

(this IEnumerable < T > source) where T : DataRow

public static void CopyToDataTable < T >

(this IEnumerable < T > source,

DataTable table,

LoadOption options) where T : DataRow

public static void CopyToDataTable < T >

(this IEnumerable < T > source,

DataTable table,

LoadOption options,

FillErrorEventHandler errorHandler) where T : DataRow

If you supply the DataTable as table , it must be empty or match the DataRow < T > produced by the

query. The last overload lets you specify a custom error handler for exceptions when filling DataTable s.

The following table describes the three members of the LoadOptions enumeration for the extension

method ’ s two overloads:

Copying Typed DataRows

Like the AsDataView() method, the CopyToDataTable() extension method only accepts

IEnumerable < DataRow > types. Neither method handles anonymous types generated by Select() or

SelectMany() projections, which are the subject of the next section.

After you apply the filter expression to generate a sequence and create the data table with code like the

following, you name it and add it to the DataSet ’ s Tables collection. Preserving updates requires

adding code to create DataAdapter at runtime.

Adding a DataAdapter at run time is beyond the scope of this chapter. Bill Vaughn ’ s “ Weaning Developers

from the CommandBuilder ” article from the MSDN Library ( http://msdn2.microsoft.com/en - us/

library/ms971491.aspx ) has detailed instructions for creating a DataAdapter with VB.

C# 3.0

private void btnApplyFilter_Click(object sender, System.EventArgs e)

{

// ...

var FilteredOrders = from o in dsNwindT.Orders

where o.GetOrder_DetailsRows().Sum(d = > d.Quantity *

d.UnitPrice * (Decimal)(1 - d.Discount)) >

Convert.ToDecimal(txtValue.Text)

select o;

DataTable dtOrders = FilteredOrders.CopyToDataTable();

dtOrders.TableName = “OrdersCopy”;

dsNwindT.Tables.Add(dtOrders);

bsOrders.DataSource = dsNwindT;

bsOrders.DataMember = “OrdersCopy”;

bsOrders.ResetBindings(false);

}

The preceding code is part of the click event handler for the Apply Filter button and represents the code

that runs when you select the DataTable option.

If your LINQ query doesn ’ t generate an anonymous type, it ’ s simpler to apply the AsDataView() rather

than the CopyToDataTable() method.