I am using ADO.NET for querying the SQL Server database. I am trying to get items if present on the table.

My query is executing but returning nothing even if there is.

Here is my code:

public List<string> GetRecords(List<string> itemList)
{
    itemList.Add("100");
    string list = string.Join(",", itemList.Select(x => string.Format("'{0}'", x)));
    string query = @"SELECT Id FROM Employees WHERE Id In (@list)";

        using (SqlCommand sqlCommand = new SqlCommand(query,connection))
        {
                sqlCommand.Parameters.AddWithValue("@list", list);
                sqlDataReader = sqlCommand.ExecuteReader();
                while (sqlDataReader.Read())
                {
                    employeeList.Add(Convert.ToString(database.Sanitise(sqlDataReader, "Id")));
                }
        }
}

There are three items in the list the employee with ID=100 is available in the table but the other two's are not. but still the query returning nothing.

SQL profiler showing me this query:

exec sp_executesql N'SELECT
                            Id
                        FROM
                            Employees
                        WHERE
                            Id In (@list)',N'@list nvarchar(29)',@list=N'''50'',''23'',''100'''

CodePudding user response：

SQL Server will not interpret your concatenated list as actual code. It remains data always, so it's just one big text string of numbers. That is never going to match a single row.

Instead, use a Table Valued Parameter.

First create a table type in your database, I usually keep a few useful ones around.

CREATE TYPE dbo.IdList (Id int PRIMARY KEY);

Then create a DataTable and pass it as a parameter.

public List<string> GetRecords(List<string> itemList)
{
    var table = new DataTable { Columns = {
        { "Id", typeof(int) },
    } };

    foreach (var id in itemList)
        table.Rows.Add(id);

    const string query = @"
SELECT e.Id
FROM Employees e
WHERE e.Id IN (SELECT l.Id FROM @list l);
";
    using (var connection = new SqlConnection(YourConnString))  // always create and dispose a new connection
    using (var sqlCommand = new SqlCommand(query,connection))
    {
        sqlCommand.Parameters.Add(new SqlParameter("@list", SqlDbType.Structured) {
            Value = dt,
            TypeName = "dbo.IdList",
        });
        connection.Open();
        using (var sqlDataReader = sqlCommand.ExecuteReader())
        {
            while (sqlDataReader.Read())
            {
                employeeList.Add((string)sqlDataReader["Id"]);
            }
        }
    }
}

Note also:

• using on all SQL objects.
• Do not cache a connection object. Create when you need it, dispose with using.
• I don't know what your Sanitize function does, but it probably doesn't work. Sanitizing database values correctly is hard, you should always use parameterization.
• AddWithValue is a bad idea. Instead specify the parameter types (and lengths/precision) explicitly.