LINQ to dataset: CopyToDataTable()

Question

Hi All,

I want to query a datatable (dt) and load a 2nd dt with the resultant collection of datarows. Fine - we have the CopyToDataTable() extn mthd for exactly that purpose. However it is constrained to enumerate only over DataRows, which means that I cannot return anything else e.g. a collection of anonymous types. So - how can I modify the values in the datarows?

Eg I have a dt with 3 columns: MyPK, VARCHAR01, VARCHAR02.

Foreach row, if VARCHAR01 or VARCHAR02 has the value "" (i.e. String.Empty) I want to replace that with NULL (which the underlying type allows).

I would do this as follows:

var MyCleanedDatarows =
      from o in ds.Tables["dt"].AsEnumerable()
      select new {
            MyPK = o.Field<string>("MyPK"),
            VARCHAR01 = (o.Field<string?>("VARCHAR01") == "" ? NULL : o.Field<string?>("VARCHAR01") ),
             VARCHAR02 = (o.Field<string?>("VARCHAR02") == "" ? NULL : o.Field<string?>("VARCHAR02") )
           };

...but then I cant use CopyToDataTable() to get back to a dt. I'm thinking I need to modify the datarows before invoking select operator, but I dont know how to achieve that. Any help/thoughts would be v.greatfully recieved.

Thanks in advance,

Tamim.

Answer 1

Take a look at this approach, in MSDN documentation. http://msdn.microsoft.com/en-us/library/bb669096.aspx