英文:
SQL where clause with multiple “OR” conditions dynamically
问题
我有一个C# Windows应用程序，从SQL数据库中获取数据。
我需要编写一个带有多个“OR”条件的动态WHERE子句的SQL查询。
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
…….
这里的OR语句的数量可以根据数据表中的行数变化而变化。
如何编写一个可以动态使用OR语句的SQL查询？
英文:
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?
答案1
得分: 3
例如：
```c#
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)}";
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论