DataTable Operators
In addition to the DataRow-specific operators in the DataRowExtensions class, there is a need for some DataTable-specific operators. These operators are defined in the System.Data.Entity.dll assembly, in the static System.Data.DataTableExtensions class.
AsEnumerable
I am guessing that you are probably surprised to see the AsEnumerable operator here. In fact, you may be surprised to learn that there is an AsEnumerable operator specifically for the DataTable class that returns a sequence of DataRow objects. If so, I am pleased because it means you were not wondering throughout this whole chapter why I hadn't mentioned it yet. After all, I have called it in virtually every example.
Yes, if you look in the System.Data.DataTableExtensions static class, you will see there is an AsEnumerable operator. The purpose of this operator is to return a sequence of type IEnumerable<DataRow> from a DataTable object.
Prototypes
The AsEnumerable operator has one prototype I will cover.
Example. The AsEnumerable Prototype
public static IEnumerable<DataRow> AsEnumerable ( this DataTable source ); |
This operator when called on a DataTable object returns a sequence of DataRow objects. This is typically the first step of performing a LINQ to DataSet query on a DataSet object's DataTable. By calling this operator, you can obtain a sequence, an IEnumerable<T> where T happens to be a DataRow, thereby allowing you to call the many LINQ operators that may be called on an IEnumerable<T> type sequence.
Examples
There is no shortage of examples in this chapter. Since calling the AsEnumerable operator is the first step to perform a LINQ to DataSet query, virtually every example in this chapter is calling the AsEnumerable operator. Therefore, there is no need to provide one here.
CopyToDataTable<DataRow>
Now that you know how to query and modify the DataColumn values of a DataRow, you might just be interested in getting that sequence of modified DataRow objects into a DataTable. The CopyToDataTable operator exists for this very purpose.
Prototypes
The CopyToDataTable operator has two prototypes I cover.
This first prototype is called on an IEnumerable<DataRow> and returns a DataTable. This is used to create a new DataTable object from a sequence of DataRow objects.
Example. The First CopyToDataTable Prototype
public static DataTable CopyToDataTable<T> ( this IEnumerable<T> source ) where T : DataRow; |
The first prototype establishes original versions for each field for you automatically without you needing to call the AcceptChanges method.
The second prototype is called on an IEnumerable<DataRow> of the source DataTable to update an already existing destination DataTable based on the LoadOption value specified.
Example. The Second CopyToDataTable Prototype
public static void CopyToDataTable<T> ( this IEnumerable<T> source, DataTable table, LoadOption options ) where T : DataRow; |
The value of the LoadOption argument passed informs the operator whether the original column values only should be changed, the current column values only should be changed, or both. This is helpful for managing the DataTable's changes. The following are the available values for LoadOption:
OverwriteChanges: Both the current value and original value will be updated for each column.
PreserveChanges: Only the original value will be updated for each column.
Upsert: Only the current value will be updated for each column.
This LoadOption argument has now created a bit of a problem, though. Notice that the description of each possible value refers to updating the values of a column. This, of course, means updating the columns of a record already in the destination DataTable. How would the CopyToDataTable operator possibly know which record already in the destination DataTable corresponds to a record in the source DataTable? In other words, when it tries to copy a record from the source DataTable to the destination DataTable and has to honor the LoadOption parameter, how does it know whether it should just add the record from the source DataTable or update an already existing record in the destination DataTable? The answer is that it doesn't, unless it is aware of primary key fields in the DataTable.
Therefore, for this prototype of the CopyToDataTable operator to work properly, the destination DataTable object must have the appropriate fields specified as the primary key fields. Without specifying primary keys, this prototype will result in appending all the records from the source DataTable to the destination DataTable.
There is one additional complication to be cognizant of when working with this prototype of the operator. Since by using this prototype you are possibly interested in original versus current version values of fields, do not forget that with this prototype of the CopyToDataTable operator, a field doesn't have an original version unless the AcceptChanges method has been called. Attempting to access the original version when one does not exist causes an exception to be thrown. However, you can call the HasVersion method on each DataRow object before attempting to access the original version to determine if there is an original version to prevent this type of exception.
Examples
As an example of the first CopyToDataTable operator prototype, I will simply modify a field in a DataTable, create a new DataTable from the modified DataTable by calling the CopyToDataTable operator, and then display the contents of the new DataTable. Listing 10-17 is the code.
Example. Calling the First Prototype of the CopyToDataTable Operator
Student[] students = {
new Student { Id = 1, Name = "Joe Rattz" },
new Student { Id = 7, Name = "Anthony Adams" },
new Student { Id = 13, Name = "Stacy Sinclair" },
new Student { Id = 72, Name = "Dignan Stephens" }
};
DataTable dt1 = GetDataTable(students);
Console.WriteLine("Original DataTable:");
foreach (DataRow dataRow in dt1.AsEnumerable())
{
Console.WriteLine("Student Id = {0} is {1}", dataRow.Field<int>("Id"),
dataRow.Field<string>("Name"));
}
(from s in dt1.AsEnumerable()
where s.Field<string>("Name") == "Anthony Adams"
select s).Single<DataRow>().SetField("Name", "George Oscar Bluth");
DataTable newTable = dt1.AsEnumerable().CopyToDataTable();
Console.WriteLine("{0}New DataTable:", System.Environment.NewLine);
foreach (DataRow dataRow in newTable.AsEnumerable())
{
Console.WriteLine("Student Id = {0} is {1}", dataRow.Field<int>("Id"),
dataRow.Field<string>("Name"));
} |
As I said, first I create a DataTable from my array of students as I typically do in the previous examples. I then display the contents of that DataTable to the console. Next, I modify the Name field in one of the DataRow objects. Then I create a new DataTable by calling the CopyToDataTable operator. Last, I display the contents of the newly created DataTable.
Are you ready for the final countdown? Poof!
Original DataTable: Student Id = 1 is Joe Rattz Student Id = 7 is Anthony Adams Student Id = 13 is Stacy Sinclair Student Id = 72 is Dignan Stephens
New DataTable: Student Id = 1 is Joe Rattz Student Id = 7 is George Oscar Bluth Student Id = 13 is Stacy Sinclair Student Id = 72 is Dignan Stephens
As you can see, not only do I have data in the new DataTable, it is the modified version, just as you would expect.
For the next example, I want to demonstrate the second prototype of the CopyToDataTable operator. As you may recall, I mentioned that for the LoadOption argument to work properly, primary keys must be established on the destination DataSet. For this example, I will not establish those so you can see the behavior. Because this example is a little more complex, I describe this one as I go. Listing 10-18 is the code.
Example. Calling the Second Prototype of the CopyToDataTable Operator When Primary Keys Are Not Established
Student[] students = {
new Student { Id = 1, Name = "Joe Rattz" },
new Student { Id = 7, Name = "Anthony Adams" },
new Student { Id = 13, Name = "Stacy Sinclair" },
new Student { Id = 72, Name = "Dignan Stephens" }
};
DataTable dt1 = GetDataTable(students);
DataTable newTable = dt1.AsEnumerable().CopyToDataTable(); |
There is little new so far. I created what will be my source DataTable from the students array. I created my destination DataTable by calling the CopyToDataTable operator on the source DataTable. Notice that because I called the first prototype of the CopyToDataTable operator, I do not need to call the AcceptChanges method on the destination DataTable. This is important to be cognizant of because, in the next segment of code, I reference the original version of the Name field. If it were not for the fact that the first prototype of the CopyToDataTable operator establishes the original versions of fields for you, an exception will be thrown since the original version would not exist.
Console.WriteLine("Before upserting DataTable:");
foreach (DataRow dataRow in newTable.AsEnumerable())
{
Console.WriteLine("Student Id = {0} : original {1} : current {2}",
dataRow.Field<int>("Id"),
dataRow.Field<string>("Name", DataRowVersion.Original),
dataRow.Field<string>("Name", DataRowVersion.Current));
}There is nothing of significance here except that I reference the original version of the Name field in the record, and no exception is thrown when doing so because this prototype of the CopyToDataTable operator established the original version for me.
(from s in dt1.AsEnumerable()
where s.Field<string>("Name") == "Anthony Adams"
select s).Single<DataRow>().SetField("Name", "George Oscar Bluth");
dt1.AsEnumerable().CopyToDataTable(newTable, LoadOption.Upsert);This is the most exciting code segment of this example. Notice that I change the value of the Name field for one of the records in the source DataTable using the SetField<T> operator. Next, I call the CopyToDataTable operator specifying that a LoadOption.Upsert type of copy should occur, meaning update only the current version. This causes a problem, though, in that since I have called the second CopyToDataTable operator prototype, which doesn't establish original versions for records inserted into the database, and I haven't called the AcceptChanges method, if I attempt to access the original versions on inserted records, an exception will be thrown. I will have to use the HasVersion method to prevent this from happening if any records are inserted. Since I have not specified any primary keys, I know that all of the records in the source table will be inserted into the destination table.
Console.WriteLine("{0}After upserting DataTable:", System.Environment.NewLine);
foreach (DataRow dataRow in newTable.AsEnumerable())
{
Console.WriteLine("Student Id = {0} : original {1} : current {2}",
dataRow.Field<int>("Id"),
dataRow.HasVersion(DataRowVersion.Original) ?
dataRow.Field<string>("Name", DataRowVersion.Original) : "-does not exist-",
dataRow.Field<string>("Name", DataRowVersion.Current));
}In this code segment, I merely display the DataTable content to the console. Now, the interesting thing about this example is that since I do not specify any primary keys for the destination table, when the copy occurs, no records will be deemed the same, so all the copied records from the source DataTable will be appended to the destination DataTable.
Also, notice that I only access the original version of the field's data if the HasVersion method returns true indicating that there is an original version. Here are the results:
Before upserting DataTable: Student Id = 1 : original Joe Rattz : current Joe Rattz Student Id = 7 : original Anthony Adams : current Anthony Adams Student Id = 13 : original Stacy Sinclair : current Stacy Sinclair Student Id = 72 : original Dignan Stephens : current Dignan Stephens After upserting DataTable: Student Id = 1 : original Joe Rattz : current Joe Rattz Student Id = 7 : original Anthony Adams : current Anthony Adams Student Id = 13 : original Stacy Sinclair : current Stacy Sinclair Student Id = 72 : original Dignan Stephens : current Dignan Stephens Student Id = 1 : original -does not exist- : current Joe Rattz Student Id = 7 : original -does not exist- : current George Oscar Bluth Student Id = 13 : original -does not exist- : current Stacy Sinclair Student Id = 72 : original -does not exist- : current Dignan Stephens
Notice that several records are now duplicated because I don't specify any primary keys in the destination DataTable. Even the record I actually updated is in the DataTable twice now.
You may be wondering, since I made such a big deal about calling the HasVersion method since the AcceptChanges method was not called, why not just call the AcceptChanges method? You could do that, but if you did, all of the fields' current version values would have become their original version values, and you would not have been able to tell which records had changed. For these examples, I want the original version values and current version values to be distinguishable when a record is changed.
The solution to the problem in the previous example is to specify the primary keys for the destination DataTable. Listing 10-19 is the same example as the previous, except this time I specify the primary keys.
Example. Calling the Second Prototype of the CopyToDataTable Operator When Primary Keys Are Established
Student[] students = {
new Student { Id = 1, Name = "Joe Rattz" },
new Student { Id = 7, Name = "Anthony Adams" },
new Student { Id = 13, Name = "Stacy Sinclair" },
new Student { Id = 72, Name = "Dignan Stephens" }
};
DataTable dt1 = GetDataTable(students);
DataTable newTable = dt1.AsEnumerable().CopyToDataTable();
newTable.PrimaryKey = new DataColumn[] { newTable.Columns[0] };
Console.WriteLine("Before upserting DataTable:");
foreach (DataRow dataRow in newTable.AsEnumerable())
{
Console.WriteLine("Student Id = {0} : original {1} : current {2}",
dataRow.Field<int>("Id"),
dataRow.Field<string>("Name", DataRowVersion.Original),
dataRow.Field<string>("Name", DataRowVersion.Current));
}
(from s in dt1.AsEnumerable()
where s.Field<string>("Name") == "Anthony Adams"
select s).Single<DataRow>().SetField("Name", "George Oscar Bluth");
dt1.AsEnumerable().CopyToDataTable(newTable, LoadOption.Upsert);
Console.WriteLine("{0}After upserting DataTable:", System.Environment.NewLine);
foreach (DataRow dataRow in newTable.AsEnumerable())
{
Console.WriteLine("Student Id = {0} : original {1} : current {2}",
dataRow.Field<int>("Id"),
dataRow.HasVersion(DataRowVersion.Original) ?
dataRow.Field<string>("Name", DataRowVersion.Original) : "-does not exist-",
dataRow.Field<string>("Name", DataRowVersion.Current));
} |
The only difference between this example and the previous is that I add the line setting the primary key on the new DataTable named newTable. Here are the results:
Before upserting DataTable: Student Id = 1 : original Joe Rattz : current Joe Rattz Student Id = 7 : original Anthony Adams : current Anthony Adams Student Id = 13 : original Stacy Sinclair : current Stacy Sinclair Student Id = 72 : original Dignan Stephens : current Dignan Stephens After upserting DataTable: Student Id = 1 : original Joe Rattz : current Joe Rattz Student Id = 7 : original Anthony Adams : current George Oscar Bluth Student Id = 13 : original Stacy Sinclair : current Stacy Sinclair Student Id = 72 : original Dignan Stephens : current Dignan Stephens
Now this is more like it. Notice that now, the student whose Id is 7 had the name "Anthony Adams" but now his name is "George Oscar Bluth". This is exactly what I am looking for.