How to include all elements of an array in a SQL query?

Question

Basically, the idea is to match data from one DataTable with another. In the first DT, there are 20 different columns in one row that I create an array from, and there is another DT with thousands of rows, two columns each. I need to select all of those rows in the second DT that are found among all of the 20 different variables in the array (so, I go row by row in the first table).

Can I do this in one query?

for (int x = 0; x < 20; x++) //this fills up the array from the 20 columns of dt1
{
   numbers[x] = Convert.ToInt16(dt1.Rows[i]["n" + (x+1)]);
}
var filtered = dt2.Select("Col1 = " + (any of the numbers[]) + " AND Col2 = " + (any of the numbers[]));

So clearly the line in question is the last one. I'm not sure if it's possible to do so.

I'm new here and I'm new to C# as well. Thank you for your help.

Just to clarify, you're wanting to iterate over each column in DT1 (20 columns), and match each one against BOTH columns of each row in DT2? If there's a match in either of the 2 columns, project into a new DataTable ("filtered" DT2)? — thesheps
– thesheps, Commented Feb 13, 2013 at 8:48

Raúl Roa · Accepted Answer · 2013-02-13 08:49:53Z

2

You could convert your data table into an enumerable and filter the data with LINQ.

Something like this:

var filtered = dt2.AsEnumerable().Where(m => numbers.Contains(m.Col1) && numbers.Contains(m.Col2));

answered Feb 13, 2013 at 8:49

Raúl Roa

12.5k13 gold badges51 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

suntime Over a year ago

While this is more elegant (especially in C#), the solution from gzaxx was much more appropriate for me. Thank you for your quick response Raúl, I appreciate your help!

gzaxx · Accepted Answer · 2013-02-13 08:51:29Z

1

You could use this SQL on DataTable:

var numbersAsString = numbers.Select(x => x.ToString()).Aggregate((x,y) => x + "," + y);
var filtered = dt2.Select("Col1 in (" + numbersAsString  + ") AND Col2 in (" + numbersAsString  + ")");

First you create string from your Array that looks like this: '1,3,4,5' and then checks in SQL if Col1 or Col2 value is in the array.

answered Feb 13, 2013 at 8:51

gzaxx

17.6k2 gold badges40 silver badges55 bronze badges

2 Comments

suntime Over a year ago

I chose this approach, because SQL was better in this scenario and it became the final solution. Thank you so much gzaxx! :)

abc Over a year ago

If you'd create a string-array: String.Join(",",numbersStr) would do the first line easier ;)

thesheps · Accepted Answer · 2013-02-13 09:05:11Z

1

Both of the above approaches work well. Without knowing whether or not your DataSets are strongly-typed, however (and using a single query instead of requiring dt1 to be projected into an array):

var filtered = dt2.AsEnumerable().Where(row => dt1.Rows[0].ItemArray.Contains(row[0]) ||
                                               dt1.Rows[0].ItemArray.Contains(row[1]));

answered Feb 13, 2013 at 9:05

thesheps

6554 silver badges11 bronze badges

1 Comment

thesheps Over a year ago

No problem suntime, I'm happy we could help! :)

Collectives™ on Stack Overflow

How to include all elements of an array in a SQL query?

3 Answers 3

1 Comment

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related