Datatable select with multiple conditions

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.

--------------Solutions-------------

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.

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("A") == 1
&& row.Field("B") == 2
&& row.Field("C") == 3
select row.Field("D");

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.

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

Category:c# Time:2010-01-02 Views:0

Related post

  • How to insert variables with a select with multiple conditions SQL Server 2011-02-11

    How to insert variables with a select with multiple conditions, suppose A function returns a temp table, and i want to populate other table, but what is the query to do something like INSERT INTO @table (La, Lb, Qaa, Qab,Qbb) SELECT items FROM dbo.SO

  • Entity framework - select by multiple conditions in same column - Many to Many 2012-04-21

    I asked very similar question here Entity framework - select by multiple conditions in same column - referenced table Now relationship is Many to Many. So I have 3 tables: order, items and orderItem (items is a Junction table) Again.. I want to selec

  • Entity framework - select by multiple conditions in same column - referenced table 2012-04-20

    Example scenario: Two tables: order and orderItem, relationship One to Many. I want to select all orders that have at least one orderItem with price 100 and at least one orderItem with price 200. I can do it like this: var orders = (from o in kontext

  • Array select with multiple conditions ruby 2009-12-18

    I can do: @items = @items.select {|i| i.color == 'blue'} @items = @items.select {|i| i.color == 'blue' || i.color == 'red'} What if I am given an unknown amount of colors and I want to select them all? i.e. ['red','blue','green','purple'] # or ['blue

  • XPATH select random number of nodes AND have multiple conditions 2011-11-17

    I need to select a random number of product details from XML and have multiple conditions. The below selects 8 products, but they are not random - how to make this random? $randomProducts = $prod_xml->xpath("/products/product[position()< 9]");

  • How to write SQL SELECT INNER JOIN with multiple conditions (with LIKE) query to LINQ to SQL 2011-05-30

    I've been looking up how to do this, and I found something close, but not quite what I'm looking for. I wonder if this might help others as well, but I could really use the help. I've got a pretty simple SELECT statement I need to convert into LINQ t

  • Which is better? DataTable.Select() or Linq for filtering objects? 2011-03-05

    I'm storing a collection of objects of custom class type. I've given the type below. public class AnonymousClient { private string ipAddress, city, country, category; private Guid id; } I may have to get the objects filtered based on city, country, c

  • DataTable.Select vs DataTable.rows.Find vs foreach vs Find(Predicate)/Lambda 2009-03-09

    I have a DataTable/collection that is cached in memory, I want to use this as a source to generate results for an auto complete textbox (using AJAX of course). I am evaluating various options to fetch the data quickly. The number of items in the coll

  • Datatable select method ORDER BY clause 2009-08-13

    HI, I 'm trying to sort the rows in my datatable using select method. I know that i can say datatable.select("col1='test'") which in effect is a where clause and will return n rows that satisfy the condition. I was wondering can i do the following da

  • DataTable Select vs LINQ Select 2009-09-14

    Any advice on when DataTable.Select should be used versus LINQ Select when dealing with an in-memory DataTable? I find LINQ syntax easier and more powerful, but I'm not sure if there are performance or other issues which make a DataTable select prefe

  • Zend Framework: How to do a DB select with multiple params? 2009-12-04

    I'm just wondering what the syntax is to do a db select in Zend Framework where two values are true. Example: I want to find if a user is already a member of a group: $userId = 1; $groupId = 2; $db = Zend_Db_Table::getDefaultAdapter(); $select = new

  • Join tables multiple conditions - compare dates - Linq 2010-01-21

    I'm trying to do a join on two tables based on multiple conditions, the problem is I'm not able to compare the date fields. The date is stored in datetime format in DB and I want all the records on a particular date, when I do this in as shown in the

  • How to specify multiple conditions and the type of condition using Zend_Db_Table 2010-05-11

    I have a function in my model that I need to use multiple conditions when querying. Additionally I would like to also have partial matches. I currently have: public function searchClient($search_term) { $rows = $this->fetchAll( $this->select()

  • LINQ multiple condition on "on" clause 2010-06-07

    I have a query which have multiple conditions on on clause SELECT * FROM CATALOGITEM with (nolock) INNER JOIN CATALOG with (nolock) ON CATALOGITEM.catalog_id = CATALOG.catalog_id and not(catalog.catalog_id = 21) AND NOT(catalog.catalog_id = 20) INNER

  • Multiple conditional Statment error, What's the problem 2010-11-04

    I get this error... Parse error: syntax error, unexpected '{', expecting '(' in /home/content/s/k/y/skyview09/html/clients/Cheryl/admin/admin.php on line 122 when trying to run a page with multiple conditional statements (if, elseif, else). The php t

  • matching multiple rows of a table to multiple conditions 2011-03-22

    I created a loop like below to check in the condition matched with the rows in table. If they match, the rowname is printed. if they don't match, nothing is happend. condition <- c(0,0,1,1) id <- apply(table, 1, function(i) sum(i[1:length(table

  • Why does DataTable.Select() return the wrong rows? 2011-03-31

    The DataTable.Select() function returns the wrong rows with a filter like this... "booleanColumn1 AND booleanColumn2 AND GuidColumn1 = '00000000-0000-0000-0000-000000000000')" Making virtually any alteration to this format fixes it (see example). Usi

  • VB.Net Datatable Select MID function 2011-05-19

    I'm trying to extract all rows from a datatable where "CODE" follows the pattern "Z##A". I tried the following to no avail: Dim floods() As DataRow = arqTable.Select("mid(code,1,1)='Z' and isnumeric(mid(code,2,2)) and mid(code,4,1)='A'") An error ret

  • Selecting across multiple tables with UNION 2011-05-20

    Not sure why this is happening; I reworked my database from one user table to multiple user tables (divided per role): tblStudents, tblTeachers, tblAdmin When logging in, I didn't want to run three queries to check if the user exists somewhere in my

Copyright (C) pcaskme.com, All Rights Reserved.

processed in 0.662 (s). 13 q(s)