SQL where clause with multiple “OR” conditions dynamically-CodePudding

I have a c# windows application where I get data from SQL database.

I need to write an sql query with where clause having multiple “OR” conditions dynamically.

Select * from table 
where 
(Name = @name and Account = @account)
OR
(Name = @name and Account = @account)
OR
(Name = @name and Account = @account)
OR
(Name = @name and Account = @account)
OR
……..

Here number of OR statement can vary based on the number of rows in the data table.

How can write a sql query that can use OR statements dynamically?

CodePudding user response：

E.g.

var names = new[] {"name1", "name2", "name3"};
var accounts = new[] {"account1", "account2", "account3"};
var conditions = new List<string>();
var command = new SqlCommand();

for (var i = 0; i < names.Length; i  )
{
    conditions.Add($"(Name = @Name{i} AND Account = @Account{i})");
    command.Parameters.Add($"@Name{i}", SqlDbType.VarChar, 50).Value = names[i];
    command.Parameters.Add($"@Account{i}", SqlDbType.VarChar, 50).Value = accounts[i];
}

command.CommandText = $"SELECT * FROM MyTable WHERE {string.Join(" OR ", conditions)}";

This still uses parameters so it still avoids the possibility of SQL injection but it also allows you to build the query dynamically.