Datatable select with multiple conditions

Question

I have a datatable with 4 columns A  B  C and D such that a particular combination of values for column A  B and C is unique in the datatable   Objective  To find the value of column D  for a given combination of values for column A  B and C   I guess looping over the set of data rows should do it  Is there a way to use Datatable Select   to accomplish this  To be more specific - can I have multiple conditions in the select filter i e  a logical AND operator connecting conditions for each of the columns A  B and C

User · Answer

Dim dr As DataRow     dr   dt Select  A   amp  a  amp   and B   amp  b  amp   and C    amp  c  A  DataViewRowState CurrentRows    Where A B C are the column names where second parameter is for sort expression

User · Answer

Yes  the DataTable Select method supports boolean operators in the same way that you would use them in a  real  SQL statement   DataRow   results   table Select  A    foo  AND B    bar  AND C    baz       See DataColumn Expression in MSDN for the syntax supported by DataTable s Select method

User · Answer

I found that having too many and s would return incorrect results  for  NET 1 1 anyway   DataRow   results   table Select  A    foo  AND B    bar  AND C    baz  and D   fred  and E    marg        In my case A was the 12th field in a table and the select was effectively ignoring it   However if I did  DataRow   results   table Select  A    foo  AND  B    bar  AND C    baz  and D   fred  and E    marg         The filter worked correctly

User · Answer

protected void FindCsv                 string strToFind    2            importFolder     C  Documents and Settings gmendez Desktop             fileName    CSVFile csv            connectionString    Driver  Microsoft Text Driver    txt    csv   Dbq   importFolder   Extended Properties Text HDR No FMT Delimited           conn   new OdbcConnection connectionString            System Data Odbc OdbcDataAdapter  da   new OdbcDataAdapter  select   from      fileName        conn           DataTable dt   new DataTable            da Fill dt            dt Columns 0  ColumnName    id            DataRow   dr   dt Select  id     strToFind            Response Write dr 0  0  ToString     dr 0  1  ToString     dr 0  2  ToString     dr 0  3  ToString     dr 0  4  ToString     dr 0  5  ToString

User · Answer

Try this  I think  this is one of the simple solutions   int rowIndex   table Rows IndexOf table Select  A    foo  AND B    bar  AND C    baz    0    string strD  Convert ToString table Rows rowIndex   D       Make sure combination of values for column A  B and C is unique in the datatable

User · Answer

If you really don t want to run into lots of annoying errors  datediff and such can t be evaluated in DataTable Select among other things and even if you do as suggested use DataTable AsEnumerable you will have trouble evaluating DateTime fields  do the following   1  Model Your Data  create a class with DataTable columns   Example  public class Person   public string PersonId   get  set    public DateTime DateBorn   get  set        2  Add this helper class to your code  public static class Extensions        lt summary gt      Converts datatable to list lt T gt  dynamically      lt  summary gt       lt typeparam name  T  gt Class name lt  typeparam gt       lt param name  dataTable  gt data table to convert lt  param gt       lt returns gt List lt T gt  lt  returns gt  public static List lt T gt  ToList lt T gt  this DataTable dataTable  where T   new         var dataList   new List lt T gt            Define what attributes to be read from the class     const BindingFlags flags   BindingFlags Public   BindingFlags Instance         Read Attribute Names and Types     var objFieldNames   typeof T  GetProperties flags  Cast lt PropertyInfo gt             Select item   gt  new                       Name   item Name              Type   Nullable GetUnderlyingType item PropertyType     item PropertyType            ToList           Read Datatable column names and types     var dtlFieldNames   dataTable Columns Cast lt DataColumn gt             Select item   gt  new               Name   item ColumnName              Type   item DataType            ToList         foreach  DataRow dataRow in dataTable AsEnumerable   ToList                  var classObj   new T             foreach  var dtField in dtlFieldNames                        PropertyInfo propertyInfos   classObj GetType   GetProperty dtField Name                var field   objFieldNames Find x   gt  x Name    dtField Name                if  field    null                                 if  propertyInfos PropertyType    typeof DateTime                                         propertyInfos SetValue                      classObj  ConvertToDateTime dataRow dtField Name    null                                     else if  propertyInfos PropertyType    typeof int                                         propertyInfos SetValue                      classObj  ConvertToInt dataRow dtField Name    null                                     else if  propertyInfos PropertyType    typeof long                                         propertyInfos SetValue                      classObj  ConvertToLong dataRow dtField Name    null                                     else if  propertyInfos PropertyType    typeof decimal                                         propertyInfos SetValue                      classObj  ConvertToDecimal dataRow dtField Name    null                                     else if  propertyInfos PropertyType    typeof String                                         if  dataRow dtField Name  GetType      typeof DateTime                                                 propertyInfos SetValue                          classObj  ConvertToDateString dataRow dtField Name    null                                             else                                               propertyInfos SetValue                          classObj  ConvertToString dataRow dtField Name    null                                                                           dataList Add classObj             return dataList     private static string ConvertToDateString object date        if  date    null          return string Empty       return HelperFunctions ConvertDate Convert ToDateTime date       private static string ConvertToString object value        return Convert ToString HelperFunctions ReturnEmptyIfNull value       private static int ConvertToInt object value        return Convert ToInt32 HelperFunctions ReturnZeroIfNull value       private static long ConvertToLong object value        return Convert ToInt64 HelperFunctions ReturnZeroIfNull value       private static decimal ConvertToDecimal object value        return Convert ToDecimal HelperFunctions ReturnZeroIfNull value       private static DateTime ConvertToDateTime object date        return Convert ToDateTime HelperFunctions ReturnDateTimeMinIfNull date         public static class HelperFunctions    public static object ReturnEmptyIfNull this object value        if  value    DBNull Value          return string Empty      if  value    null          return string Empty      return value    public static object ReturnZeroIfNull this object value        if  value    DBNull Value          return 0      if  value    null          return 0      return value    public static object ReturnDateTimeMinIfNull this object value        if  value    DBNull Value          return DateTime MinValue      if  value    null          return DateTime MinValue      return value         lt summary gt      Convert DateTime to string      lt  summary gt       lt param name  datetTime  gt  lt  param gt       lt param name  excludeHoursAndMinutes  gt if true it will execlude time from datetime string  Default is false lt  param gt       lt returns gt  lt  returns gt  public static string ConvertDate this DateTime datetTime  bool excludeHoursAndMinutes   false        if  datetTime    DateTime MinValue                if  excludeHoursAndMinutes              return datetTime ToString  yyyy-MM-dd            return datetTime ToString  yyyy-MM-dd HH mm ss fff              return null        3  Easily convert your DataTable  dt  to a List of objects with following code   List lt Person gt  persons   Extensions ToList lt Person gt  dt     4  have fun using Linq without the annoying row Field lt type gt  bit you have to use when using AsEnumerable  Example  var personsBornOn1980   persons Where x  gt x DateBorn Year    1980

User · Answer

Do you have to use DataTable Select     I prefer to write a linq query for this kind of thing    var dValue   from row in myDataTable AsEnumerable                where row Field lt int gt   A      1                      amp  amp  row Field lt int gt   B      2                      amp  amp  row Field lt int gt   C      3              select row Field lt string gt   D

[c#] Datatable select with multiple conditions

Examples related to c#

Examples related to select

Examples related to datatable