Generic Field and SetField Methods (LINQ to DataSet)

LINQ to DataSet provides extension methods to the DataRow class for accessing column values: the Field method and the SetField method. These methods provide easier access to column values for developers, especially regarding null values. The DataSet uses Value to represent null values, whereas LINQ uses the nullable type support introduced in the .NET Framework 2.0. Using the pre-existing column accessor in DataRow requires you to cast the return object to the appropriate type. If a particular field in a DataRow can be null, you must explicitly check for a null value because returning Value and implicitly casting it to another type throws an InvalidCastException. In the following example, if the IsNull method was not used to check for a null value, an exception would be thrown if the indexer returned Value and tried to cast it to a String.

' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)

Dim products As DataTable = ds.Tables("Product")

Dim query = _
    From product In products.AsEnumerable() _
    Where product!Color IsNot DBNull.Value AndAlso product!Color = "Red" _
    Select New With _
       { _
           .Name = product!Name, _
           .ProductNumber = product!ProductNumber, _
           .ListPrice = product!ListPrice _
       }

For Each product In query
    Console.WriteLine("Name: " & product.Name)
    Console.WriteLine("Product number: " & product.ProductNumber)
    Console.WriteLine("List price: $" & product.ListPrice & vbNewLine)
Next

// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);

DataTable products = ds.Tables["Product"];

var query =
    from product in products.AsEnumerable()
    where !product.IsNull("Color") &&
        (string)product["Color"] == "Red"
    select new
    {
        Name = product["Name"],
        ProductNumber = product["ProductNumber"],
        ListPrice = product["ListPrice"]
    };

foreach (var product in query)
{
    Console.WriteLine("Name: {0}", product.Name);
    Console.WriteLine("Product number: {0}", product.ProductNumber);
    Console.WriteLine("List price: ${0}", product.ListPrice);
    Console.WriteLine("");
}

The Field method provides access to the column values of a DataRow and the SetField sets column values in a DataRow. Both the Field method and SetField method handle nullable types, so you do not have to explicitly check for null values as in the previous example. Both methods are generic methods, also, so you do not have to cast the return type.

The following example uses the Field method.

' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)

Dim products As DataTable = ds.Tables("Product")

Dim query = _
    From product In products.AsEnumerable() _
    Where product.Field(Of String)("Color") = "Red" _
    Select New With _
       { _
           .Name = product.Field(Of String)("Name"), _
           .ProductNumber = product.Field(Of String)("ProductNumber"), _
           .ListPrice = product.Field(Of Decimal)("ListPrice") _
       }

For Each product In query
    Console.WriteLine("Name: " & product.Name)
    Console.WriteLine("Product number: " & product.ProductNumber)
    Console.WriteLine("List price: $ " & product.ListPrice & vbNewLine)
Next

// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);

DataTable products = ds.Tables["Product"];

var query =
    from product in products.AsEnumerable()
    where product.Field<string>("Color") == "Red"
    select new
    {
        Name = product.Field<string>("Name"),
        ProductNumber = product.Field<string>("ProductNumber"),
        ListPrice = product.Field<Decimal>("ListPrice")
    };

foreach (var product in query)
{
    Console.WriteLine("Name: {0}", product.Name);
    Console.WriteLine("Product number: {0}", product.ProductNumber);
    Console.WriteLine("List price: ${0}", product.ListPrice);
    Console.WriteLine("");
}

Note that the data type specified in the generic parameter T of the Field method and the SetField method must match the type of the underlying value. Otherwise, an InvalidCastException exception will be thrown. The specified column name must also match the name of a column in the DataSet, or an ArgumentException will be thrown. In both cases, the exception is thrown at run time during the enumeration of the data when the query is executed.

The SetField method itself does not perform any type conversions. This does not mean, however, that a type conversion will not occur. The SetField method exposes the ADO.NET 2.0 behavior of the DataRow class. A type conversion could be performed by the DataRow object and the converted value would then be saved to the DataRow object.

See Also

Reference

DataRowExtensions