C# 具有多个条件的数据表选择

Datatable select with multiple conditions

提问by Arnkrishn

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.

我有一个包含 4 列 A、B、C 和 D 的数据表，因此列 A、B 和 C 的特定值组合在数据表中是唯一的。

Objective:To find the value of column D, for a given combination of values for column A, B and C.

目标：对于 A、B 和 C 列的给定值组合，找出 D 列的值。

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.

我想循环数据行集应该这样做。有没有办法使用 Datatable.Select() 来完成这个？更具体地说 - 我可以在选择过滤器中有多个条件，即逻辑 AND 运算符连接 A、B 和 C 列中的每一列的条件。

采纳答案by Michael Petrotta

Yes, the DataTable.Selectmethod supports boolean operators in the same way that you would use them in a "real" SQL statement:

是的，该DataTable.Select方法支持布尔运算符的方式与在“真实”SQL 语句中使用它们的方式相同：

DataRow[] results = table.Select("A = 'foo' AND B = 'bar' AND C = 'baz'");

See DataColumn.Expression in MSDNfor the syntax supported by DataTable's Selectmethod.

有关DataTable方法支持的语法，请参阅MSDN中的DataColumn.ExpressionSelect。

回答by womp

Do you have to use DataTable.Select()? I prefer to write a linq query for this kind of thing.

你必须使用DataTable.Select()吗？我更喜欢为这种事情写一个 linq 查询。

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

回答by JLWarlow

I found that having too many and's would return incorrect results (for .NET 1.1 anyway)

我发现有太多的 and 会返回不正确的结果（无论如何对于 .NET 1.1）

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.

在我的情况下，A 是表中的第 12 个字段，选择实际上忽略了它。

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!

过滤器工作正常！

回答by Guaroa Mendez

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

回答by NNNN

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.

确保 A、B 和 C 列的值组合在数据表中是唯一的。

回答by Amit Agrawal

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

其中 A、B、C 是列名，其中第二个参数用于排序表达式

回答by JAnton

If you really don't want to run into lots of annoying errors (datediff and such can't be evaluated in DataTable.Selectamong other things and even if you do as suggested use DataTable.AsEnumerableyou will have trouble evaluating DateTime fields) do the following:

如果您真的不想遇到很多烦人的错误（datediff 等无法DataTable.Select在其他事情中进行评估，即使您按照建议的使用方式进行评估，您也DataTable.AsEnumerable将无法评估 DateTime 字段），请执行以下操作：

1) Model Your Data (create a class with DataTable columns)

1) 为您的数据建模（创建一个带有 DataTable 列的类）

Example

例子

public class Person
{
public string PersonId { get; set; }
public DateTime DateBorn { get; set; }
}

2) Add this helper class to your code

2) 将此辅助类添加到您的代码中

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:

3）DataTable使用以下代码轻松将您的（dt）转换为对象列表：

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

4) 享受使用 Linq 的乐趣，而无需在row.Field<type>使用时遇到烦人的问题AsEnumerable

Example

例子

var personsBornOn1980 = persons.Where(x=>x.DateBorn.Year == 1980);