C# and SQL Server multiple values

Question

I'm wondering if its possible to make this easier.

So, I have an arraylist of strings called names and an arraylist of strings called last_name.

When I'm constructing select statement, I want to combine every entry in names with everything in last_name and search in database like:

SELECT * FROM DB WHERE (names='NAMES[0]' AND last_name='LAST_NAME[0]') 
or (names='NAMES[0]' AND last_name='LAST_NAME[1]') 
or (names='NAMES[1]' AND last_name='LAST_NAME[0]') 
or (names='NAMES[1]' AND last_name='LAST_NAME[1]')

This is an example, In my project I have 6 Lists, and I need every combination, and the easiest way was to make for in for in for in for...

Thanks a lot

Your query is not referring to any columns in a table, so it is hard to say that it is searching the database. — Gordon Linoff
– Gordon Linoff, Commented Jun 9, 2017 at 10:59
Hard to see exactly what you want. Putting the lists into a data tables then passing them as a table variables to a stored procedure would allow a set based approach. — Alex K.
– Alex K., Commented Jun 9, 2017 at 11:00
That SQL statement may become verify inefficient if your have lots of names and last names! I personally would pass the lists of names into a stored procedure as a list or table as Alex has said. You can then build the results up in the database. — Wheels73
– Wheels73, Commented Jun 9, 2017 at 11:06

Vivien Sonntag · Accepted Answer · 2017-06-09 11:45:01Z

2

This should work by using a query like this :

SELECT * 
FROM DB
WHERE Name IN (@name1,@name2,@name3) AND LastName IN (@lastName1, @lastName2, @lastName3)

You can build this query in a for-loop, like this :

          var names = new[] {"John", "Peter"};
          var lastnames = new[] { "Doe", "Waylander" };

          var nameParams = "";
          var lastNameParams = "";

          var cnt = 0;
          foreach (var name in names)
          {
            var nameString = "@name" + cnt;
            if (cnt!=0)
            {
              nameParams += ",";
            }
            nameParams += nameString;
            cmd.Parameters.Add(nameString, name);
            cnt++;
          }

          cnt = 0;
          foreach (var lastName in lastnames)
          {
            var lastNameString = "@lastName" + cnt;
            if (cnt != 0)
            {
              lastNameParams += ",";
            }
            lastNameParams += lastNameString;
            cmd.Parameters.Add(lastNameString, lastName);
            cnt++;
          }

          cmd.CommandText = @"SELECT * 
FROM DB
WHERE NAME IN (" + nameParams + @")
        AND LastName IN (" + lastNameParams + ")";
          var result = cmd.ExecuteReader();

The only limitation is the number of parameters (IIRC it is about 1000). Another good alternative would be a stored procedure, like already mentioned in the comments.

edited Jun 9, 2017 at 11:45

answered Jun 9, 2017 at 11:39

Vivien Sonntag

4,6591 gold badge29 silver badges54 bronze badges

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

1 Comment

Paul F. Over a year ago

Thanks a lot! This is the answer I want!

Collectives™ on Stack Overflow

C# and SQL Server multiple values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related