Best way to get a single value from a DataTable?

Question

I have a number of static classes that contain tables like this:

using System;
using System.Data;
using System.Globalization;

public static class TableFoo
{
    private static readonly DataTable ItemTable;

    static TableFoo()
    {
        ItemTable = new DataTable("TableFoo") { Locale = CultureInfo.InvariantCulture };
        ItemTable.Columns.Add("Id", typeof(int));
        ItemTable.Columns["Id"].Unique = true;
        ItemTable.Columns.Add("Description", typeof(string));
        ItemTable.Columns.Add("Data1", typeof(int));
        ItemTable.Columns.Add("Data2", typeof(double));

        ItemTable.Rows.Add(0, "Item 1", 1, 1.0);
        ItemTable.Rows.Add(1, "Item 2", 1, 1.0);
        ItemTable.Rows.Add(2, "Item 3", 2, 0.75);
        ItemTable.Rows.Add(3, "Item 4", 4, 0.25);
        ItemTable.Rows.Add(4, "Item 5", 1, 1.0);
    }

    public static DataTable GetItemTable()
    {
        return ItemTable;
    }

    public static int Data1(int id)
    {
        DataRow[] dr = ItemTable.Select("Id = " + id);
        if (dr.Length == 0)
        {
            throw new ArgumentOutOfRangeException("id", "Out of range.");
        }

        return (int)dr[0]["Data1"];
    }

    public static double Data2(int id)
    {
        DataRow[] dr = ItemTable.Select("Id = " + id);
        if (dr.Length == 0)
        {
            throw new ArgumentOutOfRangeException("id", "Out of range.");
        }

        return (double)dr[0]["Data2"];
    }
}

Is there a better way of writing the Data1 or Data2 methods that return a single value from a single row that matches the given id?

Update #1:

I have created an extension method that seems quite nice:

public static T FirstValue<T>(this DataTable datatable, int id, string fieldName)
{
    try
    {
        return datatable.Rows.OfType<DataRow>().Where(row => (int)row["Id"] == id).Select(row => (T)row[fieldName]).First();
    }
    catch
    {
        throw new ArgumentOutOfRangeException("id", "Out of range.");
    }
}

My Data1 method then becomes:

public static int Data1(int id)
{
    return ItemTable.FirstValue<int>(id, "Data1");
}

and Data2 becomes:

public static double Data2(int id)
{
    return ItemTable.FirstValue<double>(id, "Data2");
}

Thanks to all your responses but especially to Anthony Pegram who gave the very nice single line of LINQ & Lamda code.

Answer 1

Have you considered using Linq (to DataSets)? With Linq expressions you wouldn't need those Data1 and Data2 functions at all since the lookup and filtering could happen in a single line of code.

Example added:

Shooting from the hip here, so please take it with a grain of salt (not near an IDE:)

DataTable itemTbl = GetItemTable().AsEnumerable();

double dt1 = ((From t In itemTbl Where t.Id = <your_id> Select t).First())["Data1"];

That's two lines of code but you could easily wrap the getting of the Enumerable.

Answer 2

I'm a little suspicious of your architecture, but never mind that. If you want a function that returns the first value of the first row of a datatable that it will get somehow, and you want it strongly typed, I think the function below will be an improvement. It would allow you to have just one function, reusable for different types. To use it you would have lines of code like:

int intValue = TableFoo.FirstValueOrDefault<int32>(7);
decimal decValue = TableFoo.FirstValueOrDefault<decimal>(7);

and if you feel like it:

string strValue = TableFoo.FirstValueOrDefault<string>(7);
int? nintValue = TableFoo.FirstValueOrDefault<int?>(7);

The function handles any type you generically give it, strings, other value types, nullable types, reference types. If the field is null, the function returns the "default" value for that type ("" for string). If it absolutely can't do the conversion, because you asked for an impossible conversion, it will throw an error. I've made it an extension method on the datarow type (called ValueOrDefault), and this sucker is really handy.

I adapted this data-tool extension method of mine for your situation. I'm in a VB shop, and I just don't have time to re-write the whole thing in C#, but you could do that easily enough.

Public Shared Function FirstValueOrDefault(Of T) (ByVal Int ID) As T
    Dim r as datarow = ItemTable.Select("Id = " + id.ToString());
    If r.IsNull(0) Then
        If GetType(T) Is GetType(String) Then
            Return CType(CType("", Object), T)
        Else
            Return Nothing
        End If
    Else
        Try
            Return r.Field(Of T)(0)
        Catch ex As Exception
            Return CType(r.Item(0), T)
        End Try
    End If
End Function

Answer 3

I have a number of static classes that contain tables like this

Just out of curiosity. Why?