DataRow: Select cell value by a given column name

I have a problem with a DataRow that I'm really struggling with.

The datarow is read in from an Excel spreadsheet using an OleDbConnection.

If I try to select data from the DataRow using the column name, it returns DBNull even though there is data there.

But it's not quite that simple.

datarow.Table.Columns[5].ColumnName returns "my column".
datarow["my column"] returns DBNull.
datarow[5] returns 500.
datarow[datarow.Table.Columns[5].ColumnName] returns DBNull. (just to make sure its not a typo!)

I could just select things from the datarow using the column number, but I dislike doing that since if the column ordering changes, the software will break.

Solution 1:

Which version of .NET are you using? Since .NET 3.5, there's an assembly System.Data.DataSetExtensions, which contains various useful extensions for dataTables, dataRows and the like.

You can try using

row.Field<type>("fieldName");

if that doesn't work, you can do this:

DataTable table = new DataTable();
var myColumn = table.Columns.Cast<DataColumn>().SingleOrDefault(col => col.ColumnName == "myColumnName");
if (myColumn != null)
{
    // just some roww
    var tableRow = table.AsEnumerable().First();
    var myData = tableRow.Field<string>(myColumn);
    // or if above does not work
    myData = tableRow.Field<string>(table.Columns.IndexOf(myColumn));
}

Solution 2:

This must be a new feature or something, otherwise I'm not sure why it hasn't been mentioned.

You can access the value in a column in a DataRow object using row["ColumnName"]:

DataRow row = table.Rows[0];
string rowValue = row["ColumnName"].ToString();