Edit

Generic Field and SetField Methods (LINQ to DataSet)

  • Article

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 DBNull.Value to represent null values, whereas LINQ uses the Nullable and Nullable<T> types. 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 DBNull.Value and implicitly casting it to another type throws an InvalidCastException. In the following example, if the DataRow.IsNull method was not used to check for a null value, an exception would be thrown if the indexer returned DBNull.Value and tried to cast it to a String.

// 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("");
}

' 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

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 value 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.
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("");
}

' 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

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 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