SQL Server select query with multiple keys for the same column-CodePudding

Consider a table like this:

I have an ASP.NET Web API as a backend.

What I'm trying to achieve is getting all users by name but with different values, either (abanoub) or (mohamed).

I'm using this query

SELECT * 
FROM Users_Table 
WHERE Users_Table.Name = 'abanoub' 
   OR Users_Table.Name = 'mohamed'

but in my backend I'm using the System.Data.SqlClient package so the query looks like this

SELECT * 
FROM Users_Table 
WHERE Users_Table.Name = '@Name0'
   OR Users_Table.Name = '@Name1'

I tried a workaround to add multiple values to the SqlCommand (command.Parameters.AddWithValue) using this code

string query = $"SELECT * FROM Users_Table WHERE ";

foreach (String name in nameKeys)
{
    int index = unitsKeys.IndexOf(name);
    string value = $"@Name{index}";                            
    query  = $" Name='{value}' ";

    command.Parameters.AddWithValue(value, name);

    if (index < (unitsKeys.Count - 1))
    {
        query  = " OR ";
    }
}

SelectAllByUnitKey  = query;

command.CommandText = SelectAllByUnitKey;
command.Connection = connection;

However when I try to execute this command as

SqlDataReader reader = await command.ExecuteReaderAsync()

the reader returns no values and I've tried the query its working fine but I think the values doesn't get bind throw this code

command.Parameters.AddWithValue(value, name);

I don't know what causes this.

If there is another way to retrieve list based on multiple values for the same column name.

Thanks in advance.

CodePudding user response：

It looks like the issue you are experiencing may be due to the fact that you are using single quotes around the parameter placeholders in your query (e.g. '@Name0'). In SQL, single quotes are used to indicate string literals. When you use a single quote around a parameter placeholder, the database engine will treat it as a string literal rather than a parameter. This means that the value of the parameter will not be injected into the query when it is executed.

SELECT * FROM Users_Table WHERE Users_Table.Name = @Name0 OR Users_Table.Name = @Name1

Then, when you add the parameter values to the SqlCommand object, they will be properly injected into the query when it is executed.

It's also a good idea to use the Add method of the SqlParameterCollection class to add parameters to the SqlCommand object, rather than the AddWithValue method. This allows you to specify the data type of the parameter, which can improve the performance of the query by allowing the database engine to use a more efficient execution plan.

Here is an example of how you could modify your code to use the Add method:

string query = "SELECT * FROM Users_Table WHERE";

foreach (string name in nameKeys)

{
    int index = nameKeys.IndexOf(name);
    string value = $"@Name{index}";                            
    query  = $" Users_Table.Name=@Name{index}";

    command.Parameters.Add("@Name"   index, SqlDbType.NVarChar).Value = name;

    if (index < (nameKeys.Count - 1))
    {
        query  = " OR";
    }
}

command.CommandText = query;
command.Connection = connection;