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.
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
{
/// <summary>
/// Converts datatable to list<T> dynamically
/// </summary>
/// <typeparam name="T">Class name</typeparam>
/// <param name="dataTable">data table to convert</param>
/// <returns>List<T></returns>
public static List<T> ToList<T>(this DataTable dataTable) where T : new()
{
var dataList = new List<T>();
//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<PropertyInfo>().
Select(item => new
{
Name = item.Name,
Type = Nullable.GetUnderlyingType(item.PropertyType) ?? item.PropertyType
}).ToList();
//Read Datatable column names and types
var dtlFieldNames = dataTable.Columns.Cast<DataColumn>().
Select(item => 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 => 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;
}
/// <summary>
/// Convert DateTime to string
/// </summary>
/// <param name="datetTime"></param>
/// <param name="excludeHoursAndMinutes">if true it will execlude time from datetime string. Default is false</param>
/// <returns></returns>
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<Person> persons = Extensions.ToList<Person>(dt);
4) have fun using Linq without the annoying row.Field<type> bit you have to use when using AsEnumerable
Example
var personsBornOn1980 = persons.Where(x=>x.DateBorn.Year == 1980);
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!
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());
}
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.
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<int>("A") == 1
&& row.Field<int>("B") == 2
&& row.Field<int>("C") == 3
select row.Field<string>("D");
Dim dr As DataRow()
dr = dt.Select("A="& a & "and B="& b & "and C=" & c,"A",DataViewRowState.CurrentRows)
Where A,B,C are the column names where second parameter is for sort expression
Source: Stackoverflow.com