Clever Geek Handbook

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

Basically, the idea is to map data from one DataTable to another. The first DT has 20 different columns in one row from which I am creating an array, and there is another DT with thousands of rows, two columns each. I need to select all these rows in the second DT that are among all 20 different variables in the array (so I go through the rows in the first table).

Can I do this in one request?

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[]));

It is so clear that this is the latter. I'm not sure if this is possible.

I am new here and I am new to C #. Thank you for your help.

+3


source to share


3 answers


You can 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 a string from Array

that looks like this: "1,3,4,5" and then checks in SQL if the value of Col1 or Col2 is in the array.

+1


source


You can convert your data table to enumerable and filter the data using LINQ.

Something like that:



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

;

+2


source


Both of the above approaches work well. Not knowing if your DataSets are indeed strongly typed (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]));
+1


source






More articles:


All Articles