C#/SQL Reusing parameter for list of values

Question

How do I generate SqlCommand for list of parameters?
How to properly reuse same parameter with new OR clause?

public class InputList
{
    public string col1 { get; set; }
    public string col2 { get; set; }
}

public void Exec(IList<InputList> list)
{
   try
   {
       using (var conn = new SqlConnection(Settings.Default.DBConnStr))
       using (var cmd = conn.CreateCommand())
       {
           conn.Open();
           cmd.CommandType = CommandType.Text;

           // want to use SqlParameter to prevent sql injection
           var clause = list
                           .Select(x => $"(col1 = {x.col1} AND col2 = {x.col2}) OR ")
                           .Aggregate((curr, next) => curr + " " + next);
           // TODO: remove trainling OR

           cmd.CommandText =
               $@"SELECT *
                  FROM T
                  WHERE {clause}";

           // TODO: execute reader
        }
   }
   catch()
}

I have to iterate list of objects to compile WHERE clause

SELECT *
FROM T
WHERE
(col1 = @col1 AND col2 = @col2) OR
(col1 = @col1 AND col2 = @col2) OR
...
(col1 = @col1 AND col2 = @col2)

Could you, please, elaborate the question? What is list of parameters in your case? Which columns are involved in filtering (the query in the question doesn't have a good filter) etc. — Dmitrii Bychenko
– Dmitrii Bychenko, Commented Sep 12, 2018 at 9:42
For more than a few values, you're generally better off sticking your data in a table-valued parameter, which avoids dynamic SQL altogether. — Jeroen Mostert
– Jeroen Mostert, Commented Sep 12, 2018 at 10:05

Stanislav Verjikovskiy · Accepted Answer · 2018-09-13 10:11:33Z

0

I couldn't find solution for my initial request, but table-valued parameters (TVP) works well for my case.
Thanks to @JeroenMostert for reminding about TVP

I had to rework my query and move clauses from WHERE into INNER JOIN section
SELECT * FROM T INNER JOIN @TVP TVP ON TVP.col1 = T.col1 AND TVP.col2 = T.col2

This won't work if you aren't allowed to create TVP in database

answered Sep 13, 2018 at 10:11

Stanislav Verjikovskiy

3438 silver badges17 bronze badges

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

Collectives™ on Stack Overflow

C#/SQL Reusing parameter for list of values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related