check if values are in datatable

Question

I have an array or string:

private static string[] dataNames = new string[] {"value1", "value2".... };

I have table in my SQL database with a column of varchar type. I want to check which values from the array of string exists in that column.

I tried this:

public static void testProducts() {
            string query = "select * from my table"
            var dataTable = from row in dt.AsEnumerable()
                            where String.Equals(row.Field<string>("columnName"), dataNames[0], StringComparison.OrdinalIgnoreCase)
                            select new { 
                                Name = row.Field<string> ("columnName")
                            };
            foreach(var oneName in dataTable){
                Console.WriteLine(oneName.Name);
            }
        }

that code is not the actual code, I am just trying to show you the important part

That code as you see check according to dataNames[index]

It works fine, but I have to run that code 56 times because the array has 56 elements and in each time I change the index

is there a faster way please?

the Comparison is case insensitive

Answer 1

Passing a list of values is surprisingly difficult. Passing a table-valued parameter requires creating a T-SQL data type on the server. You can pass an XML document containing the parameters and decode that using SQL Server's convoluted XML syntax.

Below is a relatively simple alternative that works for up to a thousand values. The goal is to to build an in query:

select col1 from YourTable where col1 in ('val1', 'val2', ...)

In C#, you should probably use parameters:

select col1 from YourTable where col1 in (@par1, @par2, ...)

Which you can pass like:

var com = yourConnection.CreateCommand();
com.CommandText = @"select col1 from YourTable where col1 in (";
for (var i=0; i< dataNames.Length; i++)
{
    var parName = string.Format("par{0}", i+1);
    com.Parameters.AddWithValue(parName, dataNames[i]);
    com.CommandText += parName;
    if (i+1 != dataNames.Length)
        com.CommandText += ", ";
}
com.CommandText += ");";
var existingValues = new List<string>();
using (var reader = com.ExecuteReader())
{
    while (read.Read())
        existingValues.Add(read["col1"]);
}

Given the complexity of this solution I'd go for Max' or Tim's answer. You could consider this answer if the table is very large and you can't copy it into memory.

Answer 2

try to use contains should return all value that you need

var data = from row in dt.AsEnumerable()
                   where dataNames.Contains(row.Field<string>("columnName"))
                   select new
                   {
                       Name = row.Field<string>("columnName")
                   };

Answer 3

First, you should not filter records in memory but in the datatabase.

But if you already have a DataTable and you need to find rows where one of it's fields is in your string[], you can use Linq-To-DataTable.

For example Enumerable.Contains:

var matchingRows = dt.AsEnumerable()
    .Where(row => dataNames.Contains(row.Field<string>("columnName"), StringComparer.OrdinalIgnoreCase));

foreach(DataRow row in matchingRows)
    Console.WriteLine(row.Field<string>("columnName"));

Here is a more efficient (but less readable) approach using Enumerable.Join:

var matchingRows = dt.AsEnumerable().Join(dataNames,
    row => row.Field<string>("columnName"),
    name => name,
    (row, name) => row,
    StringComparer.OrdinalIgnoreCase);

Answer 4

Sorry I don't have a lot of relevant code here, but I did a similar thing quite some time ago, so I will try to explain.

Essentially I had a long list of item IDs that I needed to return to the client, which then told the server which ones it wanted loaded at any particular time. The original query passed the values as a comma separated set of strings (they were actually GUIDs). Problem was that once the number of entries hit 100, there was a noticeable lag to the user, once it got to 1000 possible entries, the query took a minute and a half, and when we went to 10,000, lets just say you could boil the kettle and drink your tea/coffee before it came back.

The answer was to stick the values to check directly into a temporary table, where one row of the table represented one value to check against. The temporary table was keyed against the user who performed the search, so this meant other users searches wouldn't become corrupted with each other, and when the user logged out, then we knew which values in the search table could be removed.

Depending on where this data comes from will depend on the best way for you to load the reference table. But once it is there, then your new query will look something like:-

SELECT Count(t.*), rt.dataName
FROM table t
RIGHT JOIN referenceTable rt ON tr.dataName = t.columnName
WHERE rt.userRef = @UserIdValue
GROUP BY tr.dataName

The RIGHT JOIN here should give you a value for each of your reference table values, including 0 if the value did not appear in your table. If you don't care which one don't appear, then changing it to an INNER JOIN will eliminate the zeros.

The WHERE clause is to ensure that your search only returns the unique items that you are looking for at the moment - the design should consider that concurrent access will someday occur here (even if it doesn't at the moment), so writing something in to protect it is advisable.