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.

What does your spreadsheet look like? Does it have any blank rows at top? What is your connection string? What's the smallest amount of code you need to reproduce it that we could look it? (incl. sample sheet) — Will, Aug 19 '11 at 10:58

score 82 · Accepted Answer · edited Jun 24 '16 at 09:13

82

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

edited Jun 24 '16 at 09:13

Tassisto

9,877
28
100
157

answered Aug 19 '11 at 10:18

Hassan

2,603
2
19
18

Marking this as the answer as it led me to work out what was going on. Turns out that I'd when using Quick Watch, I typed the wrong column id. Typing the correct column id returned null even though there was data in the row. I'm not entirely sure why it was returning null, but I'm guessing it has something to do with interpreting data types, because this was the only row with a non-null value, and replacing null with 0 in all the other rows made it return the correct value of this row too. – VaticanUK Aug 22 '11 at 14:23
2

Look at Jimmys answer. It is easier and does the job. I had no luck with this one, using .NET Framework 4.7.2. – Bernd Schuhmacher Mar 18 '20 at 14:17

score 74 · Answer 2 · answered Jun 19 '14 at 19:27

74

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

answered Jun 19 '14 at 19:27

Jimmy

2,805
6
43
57

4

If the data entry is NULL I believe an error will be thrown when ToString is called. – Zorgarath Jun 30 '16 at 19:18
1

@Zorgarath, true fact. You'll also get an error if `table` has no rows. – Jimmy Jul 06 '16 at 13:38
1

Thanks for this. I knew this was possible but i couldn't remember the syntax and you can't see it by evaluating – John Lord Feb 12 '19 at 17:44

score 9 · Answer 3 · answered Mar 15 '17 at 23:33

9

I find it easier to access it by doing the following:

        for (int i = 0; i < Table.Rows.Count-1; i++) //Looping through rows
        {
            var myValue = Table.Rows[i]["MyFieldName"]; //Getting my field value

        }

answered Mar 15 '17 at 23:33

Gabriel G

680
8
7

sll · Answer 4 · 2011-08-19T11:02:24.927

Hint

DataTable table = new DataTable();
table.Columns.Add("Column#1", typeof(int));
table.Columns.Add("Column#2", typeof(string));
table.Rows.Add(5, "Cell1-1");
table.Rows.Add(130, "Cell2-2");

EDIT: Added more

string cellValue = table.Rows[0].GetCellValueByName<string>("Column#2");

public static class DataRowExtensions
{
    public static T GetCellValueByName<T>(this DataRow row, string columnName)
    {
        int index = row.Table.Columns.IndexOf(columnName);
        return (index < 0 || index > row.ItemArray.Count()) 
                  ? default(T) 
                  : (T) row[index];        
    }
}

Abraham Roy · Answer 5 · 2016-04-22T17:33:10.790

On top of what Jimmy said, you can also make the select generic by using Convert.ChangeType along with the necessary null checks:

public T GetColumnValue<T>(DataRow row, string columnName)
  {
        T value = default(T);
        if (row.Table.Columns.Contains(columnName) && row[columnName] != null && !String.IsNullOrWhiteSpace(row[columnName].ToString()))
        {
            value = (T)Convert.ChangeType(row[columnName].ToString(), typeof(T));
        }

        return value;
  }

score 2 · Answer 6 · answered Jan 04 '19 at 06:51

You can get the column value in VB.net

Dim row As DataRow = fooTable.Rows(0)
Dim temp = Convert.ToString(row("ColumnName"))

And in C# you can use Jimmy's Answer, just be careful while converting it to ToString(). It can throw null exception if the data is null instead Use Convert.ToString(your_expression) to avoid null exception reference

score 0 · Answer 7 · answered Jul 20 '18 at 01:54

0

for (int i=0;i < Table.Rows.Count;i++)
{
      Var YourValue = Table.Rows[i]["ColumnName"];
}

answered Jul 20 '18 at 01:54

Himadripatel1986

1
1

score 0 · Answer 8 · answered Jan 16 '20 at 11:13

0

Be careful on datatype. If not match it will throw an error.

var fieldName = dataRow.Field<DataType>("fieldName");

answered Jan 16 '20 at 11:13

bulbul bd

166
1
1
9

score 0 · Answer 9 · edited Apr 16 '21 at 19:49

0

Simple solution: Assume sqlDt contains the DataTable, then this will give you the content of the column named "aaa" in row is:

Dim fldContent = sqlDte.Rows(iz).ItemArray(sqlDte.Columns.Item("aaa").Ordinal)
Console.WriteLine("aaa = " & fldContent)

Edited code formatting

edited Apr 16 '21 at 19:49

Connor Stoop

1,574
1
12
26

answered Apr 16 '21 at 07:43

Eckart Matthias

1

DataRow: Select cell value by a given column name

9 Answers9

Linked