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

User · Accepted Answer

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 lt type gt   fieldName      if that doesn t work  you can do this   DataTable table   new DataTable    var myColumn   table Columns Cast lt DataColumn gt    SingleOrDefault col   gt  col ColumnName     myColumnName    if  myColumn    null           just some roww     var tableRow   table AsEnumerable   First        var myData   tableRow Field lt string gt  myColumn          or if above does not work     myData   tableRow Field lt string gt  table Columns IndexOf myColumn

User · Answer

Be careful on datatype  If not match it will throw an error   var fieldName   dataRow Field lt DataType gt   fieldName

User · Answer

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 lt T gt  DataRow row  string columnName              T value   default T           if  row Table Columns Contains columnName   amp  amp  row columnName     null  amp  amp   String IsNullOrWhiteSpace row columnName  ToString                           value    T Convert ChangeType row columnName  ToString    typeof T                       return value

User · Answer

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

User · Answer

I find it easier to access it by doing the following           for  int i   0  i  lt  Table Rows Count-1  i      Looping through rows                       var myValue   Table Rows i   MyFieldName      Getting my field value

User · Answer

for  int i 0 i  lt  Table Rows Count i            Var YourValue   Table Rows i   ColumnName

User · Answer

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 lt string gt   Column 2     public static class DataRowExtensions       public static T GetCellValueByName lt T gt  this DataRow row  string columnName                int index   row Table Columns IndexOf columnName           return  index  lt  0    index  gt  row ItemArray Count                         default T                        T  row index

User · Answer

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

[c#] DataRow: Select cell value by a given column name

Examples related to c#

Examples related to .net

Examples related to excel

Examples related to datarow