DataRow: Select cell value by a given column name

Question

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.

Answer 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)); } 

Answer 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();